MySQL Enterprise Edition QuickFacts as of 2012/12/05 The Enterprise Edition of MySQL (aka Advanced Edition) provides all that its name implies for an organization: full support, monitoring facilities, backup/restore functions, external authentication. It also provides an important new feature: thread pools, to assign resources to applications so that one application does not starve another (as can happen in Apache). Naturally, this edition involves a fee: $5,000 as of 2012/04. MySQL is owned by Oracle Corporation. This QuickFacts supplements the MySQL QuickFacts which describes the basic functions of MySQL, as we've become familiar with in the free version of MySQL. adminpassword Monitor Service Manager install option to specify the MySQL Enterprise Service Manager password for connecting to the MySQL database repository. Default: none adminuser Monitor Service Manager install option to specify the user name to use for connecting to the database repository used by MySQL Enterprise Service Manager. If you install the bundled MySQL server, this user is configured into the new database by the install. If you use an existing MySQL server, specify an existing MySQL username with rights to access the database. (This is a username you would have previously added to your MySQL system via the 'CREATE USER' statement.) The username goes into the mysql/user.MYD table. Default: service_manager Note: This information is stored as plain text in the config.properties file. Advanced Edition The formal name for the commercial edition of MySQL. It can be verified that this is the edition running by examining the MySQL Error Log after restart, expecting to see: Version: '5.5.25a-enterprise- commercial-advanced-log' ... MySQL Enterprise Server - Advanced Edition (Commercial) agentpassword Monitor Agent install option to specify the password to be used with the agentuser userid when communicating with the MySQL Enterprise Service Manager. agentuser Monitor Agent install option to specify the agent username when communicating with the MySQL Enterprise Service Manager' Tomcat server, on its 18080 port. Default: agent Note that agent details are not defined in server options. See also: createaccount; managerhost; managerport; mysqluser; tomcatport apache-tomcat/logs/tomcat.log File within the Monitor's directory which is the Tomcat log. Each line begins with a timestamp in the form YYYY-MM-DD hh:mm:ss,ttt like 2012-09-23 20:56:54,977 where ttt is thousands of a second. apache-tomcat/webapps/ROOT/WEB-INF/ File within the Monitor's directory config.properties containing the repository username (mysql.user variable) and password (mysql.pass). Security: Per Oracle: "You should make sure that the file is secured at the file system level so that it cannot be read by anybody but the administrator or Enterprise monitor." Backup/restore features of EE Is the MySQL Enterprise Backup product (q.v.). Commands Are mostly in /usr/bin/, like /usr/bin/mysql The server one is /usr/sbin/mysqld config.properties file See: apache-tomcat/webapps/ROOT/WEB-INF createaccount Monitor Agent install option to cause creation of the user account specified by the agentuser and agentpassword options to use when the agent connects to the monitored MySQL server. To use this option, you must also specify a suitable root account with privileges to create the new user using the rootuser and rootpassword options. See also: agentuser; agentpassword; rootuser; rootpassword dbhost Monitor Service Manager install option specifying the hostname for the MySQL database. When installing MySQL Enterprise Service Manager to use an existing MySQL server, this should be the hostname of the server that will store the database repository. Default: 127.0.0.1 dbport Monitor Service Manager install option defining the TCP port for the MySQL database used to store MySQL Enterprise Service Manager repository data. If you install the bundled MySQL server, this is the port where the new database listens for connections. If you use an existing MySQL server, specify the port used for connections by that MySQL server. Default: 13306 debugtrace Monitor Service Manager install option defining the filename to use for a debug trace of the installation. Note that this is just for the install, not for the ongoing system. enableagent Monitor Agent install option to enable the agent to monitor a MySQL server. Specify 0 for false, 1 for true. Default: 1 (true) If this option is set to false, the agent is installed without enabling the monitoring of MySQL server. You can use this to configure a proxy service using an agent for collecting Query Analyzer information. /etc/rc.d/init.d/mysql The MySQL start/stop/query init script, as used via the 'service' command (e.g., service mysql status). Must by invoked by superuser. (The developers engineered MySQL such that root needs to start and stop the server. The query function, for example, performs a 'kill -0' on the /var/run/mysqld/mysqld.pid number, being the process group ID, to thereby test the viability of the group of processes - only one of which is run by 'mysql'. The start function expects to be able to boost its Unix resource limits.) /etc/my.cnf The standard MySQL configuration file, for the server and client. The RPM install does not plant this file: it is up to the customer to do so. Extended Edition The colloquial name for the MySQL Advanced Edition. See: Advanced Edition InnoDB As of MySQL 5.5 is the default storage engine for new tables. Install Via RPM. rpm -ivh MySQL-client-advanced-5.5.25a-1.rhel5.x86_64.rpm rpm -ivh MySQL-server-advanced-5.5.25a-1.rhel5.x86_64.rpm See also: Uninstall installdir Monitor Service Manager and Agent install option defining the directory into which to install the software. Conventional server dir name: monitor Conventional client dir name: agent managerhost Monitor Agent install option to specify the hostname or IP address of the MySQL Enterprise Service Manager (the Monitor server) system. Default: none The managerhost and managerport options allow you to reach the system housing the MySQL EE Monitor Service Manager, where access to that Monitor server is then via the agentuser and agentpassword values. managerport Monitor Agent install option to specify the port number of the MySQL Enterprise Service Manager (the Monitor server). Default: 18080 See also: agentuser; agentpassword managersslport Monitor Agent install option to specify the port number of the MySQL Enterprise Service Manager to use for SSL-based communication. MEB MySQL Enterprise Backup. mem Stands for MySQL Enterprise Monitor. Is the name of the database that resides in the Monitor server. mode Monitor Service Manager and Agent install option specifying the installation mode: text Perform the install with an interactive dialog, in textual mode. (The Enterprise Dashboard has to be manually started after this kind of install. unattended Perform the install in a non-interactive manner, as in a kickstart. See also: unattendedmodeui Monitor See: MySQL Enterprise Monitor Monitor installer Provided by Oracle as a .bin file (not an RPM). To see all available options, invoke the installer with 'help'. MySQL Enterprise Backup Is the successor to the InnoDB Hot Backup product. Features: - Full, Incremental, Partial backup - Full, Partial restore - Point-In-Time-Recovery MySQL Enterprise Backup does a hot backup of all InnoDB tables. MyISAM and other non-InnoDB tables are backed up last, using the warm backup technique: the database continues to run, but the system is in a read-only state during that phase of the backup. During hot and warm backups, information about the structure of the database is retrieved automatically through a database connection. For a cold backup, you must specify file locations through configuration files or command-line options. Command: mysqlbackup which performs both backups and restores. Each set of backup data includes a configuration file, backup-my.cnf, generated by the mysqlbackup command. The file contains a minimal set of configuration parameters, to record the settings that apply to this backup data. Subsequent operations, such as the apply-log process, read options from this file to determine how the backup data is structured. See also: mysqlbackup; Restore database MySQL Enterprise Backup, install Available as an RPM or a tar file. Installs into /opt/mysql/meb-3.7/. The RPM file name is like: meb-3.8.0-rhel5.x86_64.rpm 'rpm -qlp' on it shows: /opt /opt/mysql /opt/mysql/meb-3.8 /opt/mysql/meb-3.8/README.txt /opt/mysql/meb-3.8/bin/ibbackup /opt/mysql/meb-3.8/bin/innobackup /opt/mysql/meb-3.8/bin/mysqlbackup /opt/mysql/meb-3.8/manual.html /opt/mysql/meb-3.8/mysql-html.css Thus, as the manual says, /opt/mysql/bin needs to be in your PATH for its commands to be implicitly found and executed when simply invoked by name. MySQL Enterprise Monitor Latest release: 2.3 as of mid 2012 Consists of a server piece: MySQL Enterprise Service Manager and a client piece: MySQL Enterprise Service Agent The agent can be used to monitor any MySQL server from version 4.0.x through 5.6.x (whether Enterprise or Community version server). Installer is a .bin file (not an RPM). Requires Tomcat and Java. Support: Production support is not currently available for MySQL Enterprise Monitor (Service Manager component) in virtualized environments (VMware).\ Password is supposedly stored in apache-tomcat/webapps/ROOT/WEB-INF (q.v.). MySQL Enterprise Monitor databases Installation of the Monitor (server portion Service Manager) results in the following MySQL database and tables being established in the MySQL data dir: mem db.opt dc_p_double dc_p_long dc_p_string graph_dc_schedules graph_schedules graph_series_v2 graphs graph_tags graph_variables_v2 group_members_v2 group_names hilo_sequence_iia hilo_sequence_inventory_attributes hilo_sequence_inventory_instances hilo_sequence_inventory_namespaces hilo_sequence_inventory_types hilo_sequence_rule_alarms hilo_sequence_rule_eval_results inventory_attributes inventory_instance_attributes inventory_instances inventory_instance_tags inventory_namespaces inventory_types log_db_actions loghistogram_data map_entries migration migration_migration_state migration_migration_status_servers migration_state migration_status_data_collection migration_status_servers migration_status_servers_migration_state migration_status_servers_migration_status_data_collection mos_service_requests resource_bundle resource_bundle_map rule_alarms rule_dc_schedules rule_eval_results rule_eval_result_vars rule_schedule_email_targets rule_schedules rules rule_tags rule_thresholds rule_variables schema_version_v2 statement_data statement_examples statement_explain_data statement_summaries statement_summary_data system_maps tags target_email user_form_defaults user_preferences users user_tags whats_new_entries MySQL Enterprise Monitor install The software is provided as an installer.bin file (not an RPM), one each for the server (Service Manager) and client (Agent) elements. MySQL Enterprise Service Agent The client part of the Enterprise Monitor. Installation: Provided as an install.bin file (not an RPM). The install is commonly interactive, but can be performed in "unattended" mode by invoking the installer with option '--mode unattended' plus all response values as options. Control: service mysql-monitor-agent start|stop|restart|status Passwords end up in files agent/etc/mysql-monitor-agent.ini agent/etc/instances/mysql/agent-instance.ini Uninstall: cd agent ./uninstall [is interactive; will shutdown services] MySQL Enterprise Service Manager The server part of the Enterprise Monitor. Installation: Provided as an install.bin file (not an RPM). Uninstall: cd monitor ./uninstall [is interactive; will shutdown services] Is built on top of Tomcat and Java. (Java and Tomcat are bundled in?) Installed via a .bin file (not an RPM). The install is commonly interactive, but can be performed in "unattended" mode by invoking the installer with option '--mode unattended' plus all response values as options. Configuration file: monitor/apache-tomcat/conf/server.xml Control: service mysql-monitor-server start|stop|status|restart whose status output will be: MySQL Enterprise MySQL is not running MySQL Enterprise Tomcat is running where the install used a pre-existing MySQL database installation, but allowed the embedded Tomcat to install. Where you installed MySQL outside the Monitor package, you would want to manage the tomcat server only, which you would do via: service mysql-monitor-server start|stop|status|restart tomcat You can report on sessions to the system via the Linux command: netstat --protocol inet mysql_installation_type Monitor Service Manager install option to determine if the bundled MySQL server should be installed: bundled Yes, install and use MySQL server that is bundled in the installer. existing Use an existing (customer supplied) MySQL server. mysqlbackup MySQL EE command for backing up and restoring databases - principally InnoDB types. --compress Can be used for full backups but not incrementals. See: MySQL Enterprise Backup; Restore mysqlconnmethod Monitor Agent install option to specify the connection method to use to connect to MySQL. Values: tcpip TCP/IP (across systems) socket Unix socket/named pipe (collocated only) If you specify tcpip, the value of the mysqlport option is used. If you specify socket, the value of the mysqlsocket option is used to connect to the MySQL server to be monitored. mysqlhost Monitor Agent install option to specify the hostname or IP address of the MySQL server to be monitored. Default: 127.0.0.1 (localhost) mysqlpassword Monitor Agent install option to specify the password to use when connecting to the monitored MySQL instance. mysqlport Monitor Agent install option to specify the TCP port to use when connecting to the monitored MySQL server. Is used if mysqlconnmethod=tcp. mysqlsocket Monitor Agent install option to specify the filename of the MySQL socket to use when communicating with the monitored MySQL instance. Is used if mysqlconnmethod=socket. mysqluser Monitor Agent install option to specify the username that the MySQL administrator has established in monitored MySQL database servers for the Monitor connect to in that MySQL server, for the monitor to acquire information as part of the monitoring. The MySQL administrator will have to add this account and define access permissions on each Monitored server. It can be the same on every MySQL server. Default: none The MySQL Enterprise Agent requires a user configured within each MySQL instance that is being monitored, with suitable privileges to collect info about the server, including variable names, replication, and storage engine status information. The agent installer creates a suitable user for you during installation if you supply the installer with a user/password for a privileged user (such as the MySQL root user). This account information is used only during the installation to create the user, and is not written to any file. Doc: Creating a MySQL User Account for the Monitor Agent optionfile Monitor Service Manager or Agent install option specifying the path to the file containing the options definitions to guide the installation, as in a mode=unattended install. Minimum contents for unattended install of the server Service Manager: mode installdir adminpassword Minimum contents for unattended install of the client Agent: mode mysqldir installdir mysqlpassword agentpassword Oracle ownership Oracle owns both MySQL (via acquisition of Sun Microsystems in 2009) and the InnoDB engine (as a result of the 2005 acquistion of Finnish company Innobase OY). Oracle support Will entitle you to phone, e-mail, and web-based support, as well as patches and no-cost upgrade to future releases of the same product. Oracle uses a CSI Number (CPU Support Identification Number) to verify if a customer is eligible to receive Oracle support. The CSI number is also used to identify a customer's account and track service requests. Restore database To initiate the restore process, you run the mysqlbackup command with the copy-back subcommand. The MySQL server must be shut down during the restore process. You can restore all the data for a MySQL server -- multiple databases, each containing multiple tables. Or, you can restore selected databases, tables, or both. Be aware that the restoral may not reinstate the original ownership and file permissions, so be prepared to update those after restore. See also: Backup rootpassword Monitor Agent install option to specify the password for the rootuser. Default: root (no surprise there) rootuser Monitor Agent install option to specify the root account for the installer to use to create the agent user. Default: root (no surprise there) servicename Monitor Agent install option to specify the name for the service in the Unix /etc/init.d/ directory, as used with the 'service' command. Default: mysql-monitor-agent RPM install MySQL's RPM install provides no opportunity to tailor the installation, at the time of 'rpm --install' invocation. Data is installed under the /var/lib/mysql directory - which may be undesirable for your site. The RPM provides no way to alter that. The only way to perform tailoring is to create a /etc/my.conf in advance, specifying the datadir and other elements you want, then perform the RPM install, where the mysql_install_db script invoked by the install will observe the my.cnf specifications and put things where you want them. RPMs MySQL-server-advanced-5.5.25a-1.rhel5 MySQL-client-advanced-5.5.25a-1.rhel5 To query, do like: rpm -qi MySQL-server-advanced Server, start In Linux, the standard method is: service mysql start This method precludes starting the daemon with any server environment variables, because the 'service' command does 'env -i ...' to exclude any prevailing environment variables as it establishes its own, clean set. Like: su nobody "-c mysqld_safe --user=mysql &" Note: In non-EE MySQL, command was service mysqld start See: mysqld Server, stop In Linux, the standard method is: service mysql stop Start-up messages When the MySQL server is started, its error log will have entry: mysqld_safe Starting mysqld daemon with databases from ___DataDir___ Thread Pool As of MySQL 5.5.16, commercial distributions of MySQL include the thread pool plugin. The default thread-handling model in MySQL Server executes statements using one thread per client connection. As more clients connect to the server and execute statements, overall performance degrades. The thread pool plugin provides an alternative thread-handling model designed to reduce overhead and improve performance. The plugin implements a thread pool that increases server performance by efficiently managing statement execution threads for large numbers of client connections. A major object of the facility is to minimize the number of concurrent threads, to prevent opportunities for deadlock. A thread is considered "stalled" if it persists for 60 ms (thread_pool_stall_limit default value), at which point the thread pool permits the thread group to begin executing another SQL statement. On Linux, the plugin requires kernel 2.6.9 or newer. Thread Pool details The thread pool consists of a number of thread groups, each of which manages a set of client connections. As connections are established, the thread pool assigns them to thread groups in round-robin fashion. The number of thread groups is configurable using the thread_pool_size system variable. The default number of groups is 16. The maximum number of threads per group is 4096. Configurable number of thread groups. Each thread group tries to keep the number of executing queries to one or zero. If a query is already executing in the thread group, put connection in wait queue. Waiting connections are put into a high priority queue when a transaction is already started on the connection. Allow another query to execute when the executing query in the thread group is declared as stalled (after a configurable time). In MySQL replication, the thread on the master server that is following the binary log essentially runs forever, and should not prevent other threads from running. Thread Pool performance The Thread Pool is more intended as a means for service guarantees than performance improvement. Customer observations show increased overhead of some 1 - 3% with Thread Pool, when the number of active connections is less than optimal (an InnoDB workload around 32-128 active connections). This overhead perceived to be in the handling of KILL query processing, to do that correctly. As the number of connections increase, performance improvement is evidenced. tmp directory Defined by /etc/my.cnf "tmpdir = ____". Is usually in the data dir. At start-up, mysqld creates files in it having random names like ibqpI4oE. Tomcat The Java-based Web server that is bundled with the Monitor Service Manager installer. The Monitor server installer has tomcat* options governing its install. The Monitor client (agent) does not have any tomcat* options, as it more abstractly contacts the Service Manager (server) via its adminuser spec. Tomcat status As root/superuser, do: service mysql-monitor-server status tomcat Note how we specified tomcat as the specific component, as the last operand on the command line. tomcatport Monitor Service Manager install option to define the TCP port for the MySQL Enterprise Service Manager Tomcat (Java) server. This port is used by MySQL Enterprise Agent and as the port for the interface to the MySQL Enterprise Dashboard. Default: 18080 The Monitor agent will connect to this port using its agentuser and agentpassword values. See also: agentuser tomcatshutdownport Monitor Service Manager install option to define the TCP port for the MySQL Enterprise Service Manager Tomcat shutdown. Default: 18005 tomcatsslport Monitor Service Manager install option to define the SSL TCP/IP port for the Tomcat component. Standard value: 18443 If you don't specify this option in the install, there will be no SSL port. If thus, and you want to add the SSL port later, go into the Monitor directory monitor/apache-tomcat/conf/ to there do 'bkupfile server.xml' and then update the server.xml file to uncomment the "SSL HTTP/1.1 Connector" section, by deleting lines around the Connector tag; then restart Tomcat for it to use that, via: service mysql-monitor-server restart tomcat unattendedmodeui Monitor Agent install option to specify the UI elements to use when performing an unattended installation. The options are: none Show no UI elements during the installation; minimal Show minimal elements during installation; minimalWithDialogs Show minimal UI elements, but include the filled-dialog boxes. Uninstall MySQL server To uninstall the MySQL server RPM, do like: rpm -e MySQL-server-advanced What a MySQL server uninstall does: - /etc/init.d/mysql stop - /sbin/chkconfig --del mysql What it *doesn't* do: - Does not remove the datadir (default /var/lib/mysql/) because that area may house databases created during the lifetime of the MySQL server software. - Does not remove username 'mysql' from /etc/passwd, as it would own the database files in the datadir. A reinstall will not object to such pre-existing data, will not interfere with prevailing datadir content. /var/lock/subsys/mysql The mysql server lock file in RHEL. Is an empty file, owned by root. Is present when mysqld is running; absent when mysqld is shut down. May be present erroneously if mysqld had a start-up problem, in which case it should be manually removed. version Monitor Agent install option to show the version of the installer, when present on the installer.bin command line. Version of MySQL Via query: Command SHOW VARIABLES LIKE 'version'; The "version" variable will have a value like "5.5.25a-enterprise- commercial-advanced-log". Also, command SHOW VARIABLES LIKE 'version_comment'; is more descriptive, like: "MySQL Enterprise Server - Advanced Edition (Commercial)". (Command 'mysqladmin variables' can be used to show all system variables.) VMware environment notes Oracle classifies MySQL support under VMware as on a best effort basis because if an issue is related to the virtual hardware they may require that we reproduce the problem on native hardware. However this disadvantage is outweighed by the advantages of a common virtualization environment. FILES: Monitor: mysqlmonitor-Version-Platform-installer.bin for a new installation mysqlmonitor-version-platform-update-installer.bin for an upgrade installation Server: MySQL-server-advanced-VERSION.x86_64.rpm Client: MySQL-client-advanced-VERSION.x86_64.rpm Development: MySQL-devel-advanced-VERSION.x86_64.rpm Shared: MySQL-shared-advanced-VERSION.x86_64.rpm MySQL-shared-compat-advanced-VERSION.x86_64.rpm Embedded: MySQL-embedded-advanced-VERSION.x86_64.rpm Test: MySQL-test-advanced-VERSION.x86_64.rpm ERROR MESSAGES AND CONDITIONS: ... RESOURCES: Comparison chart: http://www.mysql.com/products/ MySQL documentation: http://docs.oracle.com/cd/E17952_01/index.html Thread Pool reference: http://dev.mysql.com/doc/refman/5.5/en/thread-pool-plugin.html (This is file /usr/csg/QuickFacts/MySQL-EE.QuickFacts)