The MySQL Enterprise user – These are the credentials you use to log in to the MySQL Enterprise web site. You will need them in order to acquire the Advisor files and receive updates and, if necessary, acquire a product key.

The MySQL user – For Monitor Agents to report the status of a MySQL server they must have privileges on that server. To perform all functions an agent must have SHOW DATABASES, REPLICATION CLIENT, SUPER, CREATE, and SELECT privileges. In short, the Monitor Agent needs to have read access to all data. Details about this account are given in Section 15.3.3.1, “Creating a MySQL User Account for the Monitor Agent”.

The Repository user – This user is the only user in the user table in the mysql database in the bundled MySQL server. To avoid confusion with monitored MySQL servers, this server is referred to throughout this document as the repository. The repository user can log in from localhost using the password specified during installation and has all privileges on all databases. These credentials are used to create the repository and its tables and to record data in them. During installation the default value for the user name for this role is service_manager. No default password is specified. You can use these credentials to manage the repository from the command line or when using a program such as MySQL Administrator.

During installation the file configuration_report.txt is created. Reference this file for the credentials of the repository manager. After the MySQL Enterprise Service Manager is installed, look for this file in the following directories:

The Root user – This user is the administrator of the dashboard. The first time you log in to the dashboard you must log in as this user. The default user name for this user is admin. There is no default password for this user.

The Agent user – The Monitor Agent needs to report the status of the MySQL server it is monitoring. For this reason it needs to log in to the dashboard. The default user name for this user is agent. There is no default password for this user.

All installations of the Service Manager install the Tomcat and MySQL applications using the same basic set of parameters. The defaults provided by the installation process are designed to be unique so that they do not interfere with existing installations of either product. However, you should check these parameters before installation to ensure that you do not experience any problems.

The common parameters are divided into those applying to the Tomcat server, and the MySQL server (Repository Configuration):

The information that you configure during installation will always be recorded within the configuration_report.txt file within the installation directory for the Service Manager.

On Windows the installation modes are win32 and unattended only. unattended mode is especially useful if you are doing multiple installations. For more information on this topic see Section 15.3.4, “Unattended Installation”.

To install the Service Manager on Windows, find the executable file named mysqlmonitor-version-windows-installer.exe (where version represents the three-part version number).

For instructions on starting the MySQL Enterprise Monitor services under Windows, see Section 15.3.2.5, “Starting/Stopping the MySQL Enterprise Monitor Service on Windows”.

On Mac OS X there are three installation modes osx, text, and unattended. For more information on this topic see Section 15.3.4, “Unattended Installation”. The text mode installation for Mac OS X is identical to text installation under Unix. For text mode installation instructions see Section 15.3.2.4, “Service Manager Installation on Unix”.

Installing the MySQL Enterprise Service Manager on Mac OS X requires an existing installation of Java. The minimum required version is 1.5.0_7. If this version is not installed on your machine you can download it from Apple. This version of Java requires Mac OS X version 10.4.5 as a minimum, so you may need to upgrade your operating system in order to install it.

For reasons of backwards compatibility, Mac OS X is usually installed with multiple versions of Java. When installing in osx mode, version 1.5.0_7 must be the default version. Upon installation, Java 1.5.0_7 sets itself as the default so this is usually not a problem.

If you have changed the default you can reset it or you may install the MySQL Enterprise Service Manager in text mode, setting the environment variables to point to the correct version of Java. To install in text mode, find the installbuilder file in the Contents/MacOS directory immediately below the mysqlmonitor-version-osx-installer.app directory. Installing the MySQL Enterprise Service Manager in text mode is identical to the procedure described in Section 15.3.2.4, “Service Manager Installation on Unix” with the minor differences noted above.

To install using the GUI (osx) installation, follow these instructions:

Your installation should now be complete. To continue with the configuration of MySQL Enterprise Service Manager, see Section 15.3.2.7, “MySQL Enterprise Service Manager Configuration Settings and Advisor Installation”.

To install the Service Manager find the file named mysqlmonitor-version-installer.bin (where version indicates the version number, the OS, and the architecture ). Ensure that this file is executable by typing:

shell> chmod +x mysqlmonitor-version-installer.bin

To install to the default directory (/opt/mysql/enterprise/monitor) you need to be logged in as root. Installing as an unprivileged user installs to the /home/user_name/mysql/enterprise/monitor/ directory.

What follows describes installation from the command line. You may install the Service Manager graphically by running the installer from within a windows manager. In both cases the steps are identical. You may also install the Service Manager in unattended mode. This is especially useful if you are doing multiple installations. For more information on this topic see Section 15.3.4, “Unattended Installation”.

The Enterprise Dashboard will not start up automatically if you perform a text mode installation. For more information on starting and stopping MySQL Enterprise Service Manager, see Section 15.3.2.6, “Starting/Stopping the MySQL Enterprise Monitor Service on Unix and Mac OS X”.

You can choose to start up the MySQL Enterprise Service Manager on installation. The installed services are called:

You can stop or start the services from the Microsoft Management Console Services window. Look for the MySQL Enterprise Tomcat and the MySQL Enterprise MySQL entries.

To start or stop a service, right click it and choose from the options in the pop-up menu.

There is also a menu entry for starting and stopping the services. Navigate to the Program, MySQL, MySQL Enterprise Monitor, Services entry to stop or start the services.

You can also stop or start a service from the command line. To start the Tomcat service type:

shell> sc start MySQLEnterpriseTomcat

or:

shell> net start MySQLEnterpriseTomcat

To stop this service type:

shell> sc stop MySQLEnterpriseTomcat

or:

shell> net stop MySQLEnterpriseTomcat

In similar fashion, you may stop or start the MySQL server from the command line. The service name is MySQLEnterpriseMySQL.

You may also start, stop, and restart a specific service or both services using the mysqlmonitorctl.bat file. To execute this file, go to the command line and navigate to the C:\Program Files\MySQL\Enterprise\Monitor directory. Typing mysqlmonitorctl.bat help produces the following output:

usage: mysqlmonitorctl.bat help
       mysqlmonitorctl.bat (start|stop|restart|install|uninstall)
       mysqlmonitorctl.bat (start|stop|restart) tomcat
       mysqlmonitorctl.bat (start|stop|restart) mysql

help       - this screen
start      - start the service(s)
stop       - stop the service(s)
restart    - restart or start the service(s)
install    - install the service(s)
uninstall  - uninstall the service(s)

To stop a specific service, pass the argument tomcat or mysql in addition to the status change argument. If you wish to change the status of both services, do not specify a service name. You may also uninstall the services using this batch file.

Configuration of the dashboard begins immediately after the Service Manager is installed. To continue a Windows installation skip the next section and go to Section 15.3.2.7, “MySQL Enterprise Service Manager Configuration Settings and Advisor Installation”.

The services incorporated into the MySQL Enterprise Service Manager are:

Should you need to stop, start, or restart the MySQL Enterprise Service Manager call the mysqlmonitorctl.sh file located in the /opt/mysql/enterprise/monitor/ directory on Unix or the /Applications/mysql/enterprise/monitor/ on Mac OS X. To see all the available options navigate to the appropriate directory and type:

shell> /opt/mysql/enterprise/monitor/mysqlmonitorctl.sh help

Executing this script produces the following output:

usage: ./mysqlmonitorctl.sh help
./mysqlmonitorctl.sh (start|stop|status|restart)
./mysqlmonitorctl.sh (start|stop|status|restart) mysql
./mysqlmonitorctl.sh (start|stop|status|restart) tomcat

help       - this screen
start      - start the service(s)
stop       - stop  the service(s)
restart    - restart or start the service(s)
status     - report the status of the service

Using this script you can stop, start, or restart all the Service Manager components. To do this make a call to mysqlmonitorctl.sh start from your start-up script.

To start the service:

shell> ./mysqlmonitorctl.sh start
./mysqlmonitorctl.sh : mysql  started
nohup: redirecting stderr to stdout
Starting mysqld daemon with databases from /opt/mysql/enterprise/monitor/mysql/data/
Using CATALINA_BASE:   /opt/mysql/enterprise/monitor/apache-tomcat
Using CATALINA_HOME:   /opt/mysql/enterprise/monitor/apache-tomcat
Using CATALINA_TMPDIR: /opt/mysql/enterprise/monitor/apache-tomcat/temp
Using JRE_HOME:       /opt/mysql/enterprise/monitor/java

If you try to start the service and it is already running, you will be warned that the services are already running:

shell> ./mysqlmonitorctl.sh start
./mysqlmonitorctl.sh : mysql  (pid 18403) already running
./mysqlmonitorctl.sh : tomcat  (pid 18480) already running

To stop the service:

shell> ./mysqlmonitorctl.sh stop
Using CATALINA_BASE:   /Applications/mysql/enterprise/monitor/apache-tomcat
Using CATALINA_HOME:   /Applications/mysql/enterprise/monitor/apache-tomcat
Using CATALINA_TMPDIR: /Applications/mysql/enterprise/monitor/apache-tomcat/temp
Using JRE_HOME:       /System/Library/Frameworks/JavaVM.framework/Versions/1.5.0/Home
Stopping tomcat service .. [ OK ]
STOPPING server from pid file /Applications/mysql/enterprise/monitor/mysql/data/mysqld.pid
090209 15:37:09  mysqld ended

The restart command is equivalent to executing a stop and then start operation.

This script can also be used to check the status of the Tomcat web server or the MySQL repository.

shell> ./mysqlmonitorctl.sh status
MySQL Network MySQL is running
MySQL Network Tomcat is running

Configuration of the dashboard begins immediately after the MySQL Enterprise Service Manager is installed.

The Enterprise Dashboard is the web-based interface to the Service Manager so the procedure for starting the dashboard is identical for all platforms. From the dashboard you can configure the settings necessary for receiving updates from MySQL Enterprise and for the initial installation of the Advisors.

If you installed the Service Manager using a graphical interface, you have the option of launching the dashboard on the final installation screen (as long as the Launch MySQL Enterprise Monitor Now checkbox is checked).

Otherwise, you can view the dashboard by typing http://localhost:18080/Auth.action into the address bar of your web browser. If you are unsure of the host name and port to use, check the configuration_report.txt file.

Under Windows it is also possible to open the dashboard by choosing the MySQL menu item and finding the MySQL Enterprise Monitor entry. Under this entry choose Start Service Manager.

15.3.2.7.1. Initial Dashboard Log-In

If this is the first time that you have attempted to log in to the dashboard you should see a screen similar to the following:

Figure 15.11. MySQL Enterprise Monitor: Initial Dashboard Log-In

Use this screen to perform the following tasks:

• Install the Advisors

• Set up your MySQL Enterprise credentials

• Create a user name and password for the dashboard administrator

• Create a user name and password for the Monitor Agent

If you have been provided with a MySQL Enterprise Product Key and an Advisors file click the Browse button and locate these files. The advisor file bears the name, AdvisorScript-version.jar and the product key, Subscription-level_date.xml. If you do not allow Internet access from the dashboard you must install the advisors in this way. It is strongly recommended that you install the Advisors at this point, but you may do so later. For instructions on doing this see, Section 15.3.2.7.3, “Installing Advisors After Initial Log-in”. If the product key that you provide is invalid a notification appears and you will be unable to import the advisors.

Note

If you are activating the MySQL Enterprise Monitor using a product key donot enter your MySQL credentials; entering both produces an error message.

If you have Internet access from the dashboard, activate MySQL Enterprise Monitor by supplying your MySQL Enterprise credentials. Enter your email address as the MySQL Enterprise Login and enter and confirm your MySQL Enterprise password. If you specify incorrect credentials, you receive the error message, “Unable to connect to verify credentials.”

In the Create Administrator section of this screen, enter credentials for the dashboard administrator. This creates the root user described in Section 15.3.1.3, “Users Created on First Log-in”. Make note of the user name and password as these credentials are required for any future login.

In the Configure Agent Credentials section of this screen enter the credentials for the agent. This is the agent user also described in Section 15.3.1.3, “Users Created on First Log-in”. The agent needs to log in in order to report its findings. Make note of the agent's credentials; this information is required when installing the agent.

When all the settings have been specified, click the complete setup button. If you log in successfully you should see a message displaying the number of graphs and advisors that have been imported. This number varies depending upon your subscription level.

If importation of the advisor files fails, you will see the message:

Unable to import Advisor Jar. You may download the jar
manually from the Enterprise Portal and import it from the 'Check For Updates' page.

In this case you may download the advisor file from the Enterprise website and install it as described in Section 15.3.2.7.3, “Installing Advisors After Initial Log-in”.

15.3.2.7.5. Outgoing Email Settings

Alert notification via email is a key component of the MySQL Enterprise Monitor Advisor solution. For this reason you may want to immediately configure an SMTP account for at least one recipient.

To do this choose the Settings tab and go to the Global Settings screen by clicking the appropriate link. Here you can configure the email settings. These settings apply to the currently logged-in user.

Find the Outgoing Email Settings on the left of this page.

Figure 15.12. MySQL Enterprise Monitor: Outgoing Email Settings

Ensure that the Enable Email Notifications checkbox is checked and enter information as appropriate.

The default value for the SMTP port is 25. If your mail server runs on a different port simply specify it, separating it from the server name using a colon. For example, if your mail server runs on port 587 enter email.myserver.com:587 into the SMTP Server text box.

Note

An email server must be available for sending email alerts.

The SMTP client uses Transport Layer Security (TLS) if the SMTP server supports it.

If your SMTP server incorrectly indicates that it supports TLS, check the Disable JavaMail TLS/SSL check box.

The email settings page is dealt with in more detail in Section 15.6, “The Settings Page”.

Before setting up an agent to monitor a MySQL server you need to ensure that there is a user account for the agent on that server.

The privileges required for this user account vary depending on the information you wish to gather using the MySQL Enterprise Monitor Agent. The following privileges allow the Monitor Agent to perform its assigned duties without limitation:

For example, the following GRANT statement will give the agent the required SELECT, REPLICATION CLIENT, SHOW DATABASES and SUPER rights:

GRANT SELECT, REPLICATION CLIENT, SHOW DATABASES, SUPER, PROCESS
  ON *.*
  TO  'mysqluser'@'localhost'
  IDENTIFIED BY 'agent_password';

For security reasons, you may wish to limit the CREATE and INSERT privileges to the agent so that it can only create tables within the mysql database:

GRANT CREATE, INSERT
  ON mysql.*
  TO  'mysqluser'@'localhost'
  IDENTIFIED BY 'agent_password';

To enable replication discovery to work, you should also grant the SELECT privilege on the mysql.inventory table for each user with replication privileges on the corresponding replication master. This is required to let the MySQL Enterprise Monitor Agent read the replication master UUID. For example:

GRANT SELECT
  ON mysql.inventory
  TO  'replicationuser'@'%'
  IDENTIFIED BY 'replication_password';

If the agent is unable to access the information from the table then a warning containing this information will be written to the agent log.

In a typical configuration, the agent runs on the same machine as the MySQL server it is monitoring so the host name will be localhost. However, this will change if the agent is running on a machine other than the one that hosts the monitored MySQL server. In this case, change localhost to the appropriate value. For more information about remote monitoring see Section 15.3.3.6.4, “Configuring an Agent to Monitor a Remote MySQL Server”.

To install the MySQL Enterprise Monitor Agent on Windows, double-click the mysqlmonitoragent-version-windows-installer.exe (where version indicates the three-part version number) installer.

You may also install the Monitor Agent in unattended mode. This is especially useful if you are doing multiple installations. For more information on this topic see, Section 15.3.4, “Unattended Installation”.

Once the Monitor Agent is installed, it needs to be started. For information on how to start and stop the Agent, see Section 15.3.3.5.1, “Starting/Stopping the Agent on Windows”.

To install the MySQL Enterprise Monitor Agent on Mac OS X, decompress the mysqlmonitoragent-version-installer.app.zip and then run the mysqlenterpriseagent-version-installer application.

Once the Monitor Agent is installed, it needs to be started. For information on how to start and stop the Agent, see Section 15.3.3.5.2, “Starting/Stopping the Agent on Mac OS X”.

As a prerequisite for installing the MySQL Enterprise Monitor Agent on Linux systems you must have the Linux Standards Base (LSB) initialization functions installed.

To install the agent navigate to the directory that contains the file, mysqlmonitoragent-version-installer.bin (where version indicates the three-part version number, the OS, and the architecture). Ensure that this file is executable by typing:

shell> chmod +x mysqlmonitoragent-version-installer.bin

To install to the default directory (/opt/mysql/enterprise/agent) you need to be logged in as root. Installing as an unprivileged user installs to the /home/user_name/mysql/enterprise/agent directory.

What follows describes installation from the command line. You may install the Monitor Agent graphically by running the installer from within a windows manager. In both cases the steps are identical. You may also install the Monitor Agent in unattended mode. This is especially useful if you are doing multiple installations. For more information on this topic see Section 15.3.4, “Unattended Installation”.

Begin installation from the command line by typing:

shell> ./mysqlmonitoragent-version-installer.bin --mode text

The various options are shown in what follows. Default values are indicated by square brackets; to select them press Enter. Otherwise enter a value of your choosing.

For information on starting the agent, see Section 15.3.3.5.3, “Starting/Stopping the Agent on Unix”.

The MySQL Enterprise Monitor Agent can be started and stopped at any time. When not running, information about the current status of your server will not be available, and MySQL Enterprise Service Manager will provide a warning if an agent and the MySQL server that it monitors is unavailable.

shell> sc start MySQLEnterpriseMonitorAgent

shell> net start MySQLEnterpriseMonitorAgent

shell> sc stop MySQLEnterpriseMonitorAgent

shell> net stop MySQLEnterpriseMonitorAgent

shell> ./mysql-monitor-agent start

shell> ./mysql-monitor-agent stop

shell> kill -TERM PID

shell> ./mysql-monitor-agent status

shell> tail /Applications/mysql/enterprise/agent/log/mysql-monitor-agent.log

Usage: ./mysql-monitor-agent {start|stop|restart|status} [ini-file-name]

shell> ./mysql-monitor-agent start /Applications/mysql/enterprise/agent/etc/new-mysql-monitor-agent.ini

shell> /opt/mysql/enterprise/agent/etc/init.d/mysql-monitor-agent start

shell> /home/<user name>/mysql/enterprise/agent/etc/init.d/mysql-monitor-agent start

shell> ./mysql-monitor-agent stop

shell> kill -TERM PID

shell> ./mysql-monitor-agent status

shell> tail /opt/mysql/enterprise/agent/log/mysql-monitor-agent.log

Usage: ./mysql-monitor-agent {start|stop|restart|status} [ini-file-name]

shell> ./mysql-monitor-agent start /opt/mysql/enterprise/agent/etc/new-mysql-monitor-agent.ini

The MySQL Enterprise Monitor Agent is configured through files located within the etc directory within the directory where you installed the agent.

Configuration is stored in multiple files, according to a predetermined file and directory layout. The primary configuration file contains specific information about the agent and how the agent communicates with MySQL Enterprise Service Manager. The main configuration is located within the mysql-monitor-agent.ini file.

Additional configuration files contain information about the MySQL server that is being monitored. You can configure which directory is used for storing this information within the mysql-monitor-agent.ini file. The default location is the etc/instances directory within the MySQL Enterprise Monitor Agent directory.

The server you want to monitor should have a directory within the specified location, optionally using the name of the server you are monitoring, and within that directory, an agent-instance.ini file. This file contains the configuration information for connecting to the MySQL server, including the host name, port, user credentials and display name.

You can see an example of the file layout of the etc directory:

.
./init.d
./init.d/mysql-monitor-agent
./instances
./instances/agent
./instances/agent/agent-instance.ini
./mysql-monitor-agent.ini

For more information on the configuration of the mysql-monitor-agent.ini file, see Section 15.3.3.6.1, “MySQL Enterprise Monitor Agent (mysql-monitor-agent.ini) Configuration”. For details on the content of the individual MySQL instance configuration files, see Section 15.3.3.6.2, “MySQL Server (agent-instance.ini) Configuration”.

# WARNING - the UUID defined below must be unique for each agent.
#
# To use this .ini file as a template for configuring additional
# agents, do not simply copy and start a new agent without first
# modifying the UUID.
#
# Refer to the documentation for more detailed information and
# instructions.
#
# Version: 20080718_230416_r7011

[mysql-proxy]

plugins=proxy,agent
agent-mgmt-hostname = http://agent:password@monitor-server:18080/heartbeat
mysqld-instance-dir= etc/instances
agent-item-files = share/mysql-proxy/items/quan.lua,share/mysql-proxy/items/items-mysql-monitor.xml
proxy-address=:4040
proxy-backend-addresses = 127.0.0.1:3306
proxy-lua-script        = share/mysql-proxy/quan.lua


agent-uuid = 8770ead5-3632-4b29-a413-4a7c92437e26
log-file = mysql-monitor-agent.log
pid-file=/Applications/mysql/enterprise/agent/mysql-monitor-agent.pid

# WARNING - the displayname defined below must be unique for each
# MySQL server being monitored.
#
# To use this .ini file as a template for configuring additional
# instances to monitor, do not simply copy and start a new agent
# without first modifying the displayname.
#
# Refer to the documentation for more detailed information and
# instructions.
#
# Version: 20080718_230416_r7011

[mysqld]
hostname = 127.0.0.1
port     = 3306
user     = root
password =

socket = /full/path/to/mysql.sock

./init.d
./init.d/mysql-monitor-agent
./instances
./instances/agent
./instances/agent/agent-instance.ini
./mysql-monitor-agent.ini

./init.d
./init.d/mysql-monitor-agent
./instances
./instances/agent
./instances/agent/agent-instance.ini
./instances/mysql2
./instances/mysql2/agent-instance.ini
./instances/mysql-rep
./instances/mysql-rep/agent-instance.ini
./instances/mysql-backup
./instances/mysql-backup/agent-instance.ini
./mysql-monitor-agent.ini

./init.d
./init.d/mysql-monitor-agent
./instances
./instances/agent
./instances/agent/agent-instance.ini
./instances-second/agent
./instances-second/agent/agent-instance.ini
./mysql-monitor-agent.ini
./mysql-second-agent.ini

hostname = http://agent_name:password@localhost:18080/heartbeat

shell> ssh -L 18080:Dashboard_Host:18080 -l user_name -N Dashboard_Host

shell> /opt/mysql/enterprise/agent/bin/mysql-monitor-agent --agent-generate-uuid

shell> /Applications/mysql/enterprise/agent/bin/mysql-monitor-agent --agent-generate-uuid

ee9296d7-f7cd-4fee-8b26-ead884ebf398

The first step in troubleshooting the agent is finding out whether it is running or not. To do this see:

If incorrect credentials are specified for the agent login to the MySQL server that it is monitoring, then the agent will not run on start-up. Log in to the monitored MySQL server and check the agent's credentials. Compare the values of the Host, User, and Password fields in the mysql.user table with the values shown in the [mysqld] section of the etc/instances/mysql/agent-instance.ini. If incorrect credentials are specified in the ini file, simply correct them and restart the agent. Remember, changes to the ini file do not take effect until the agent is restarted.

The agent will not start up if incorrect credentials are specified for the service manager login. Using incorrect credentials for logging in to the service manager creates an entry in the agent log file. For the location of this log file see Agent Log and PID Files.

If the agent starts up but no server appears in the dashboard, check the hostname specified in the [mysql-proxy] portion of the mysql-monitor-agent.ini file. Incorrect credentials, IP address, or port will all cause the MySQL server to fail to appear in the dashboard. Also, ensure that the port specified in this file is not blocked on the machine hosting the MySQL Enterprise Service Manager.

An easy way to confirm that the agent can log in to the service manager is to type http://Dashboard_Host:18080/heartbeat into the address bar of your web browser, substituting the appropriate host name and port. When the HTTP authentication dialog box opens, enter the agent user name and password. If you log in successfully, you should see the following message:

<exceptions>
<error>E1031:  Agent payload parameter NULL.</error>
</exceptions>

If you can log in successfully in the way described above and the agent is running, then there are errors in the mysql-monitor-agent.ini file. Compare the host name, port, agent name, and password found in the ini file with the values you entered into the address bar of your web browser.

If HTTP authentication fails then you are using incorrect credentials for the agent. Attempting to log in to the service manager using incorrect credentials creates an entry in the agent log file. For the location of this log file see Agent Log and PID Files.

If no HTTP authentication dialog box appears, and you are unable to connect at all, then you may have specified an incorrect host name or port. Confirm the values you entered against those described as the Application hostname and port: in the configuration_report.txt file. Failure to connect could also indicate that the port is blocked on the machine hosting the MySQL Enterprise Service Manager.

To check if a blocked port is the problem, temporarily bring down your firewall. If the agent is then able to connect, open up the port specified during installation and restart the agent. If necessary you can monitor outside the firewall using an SSH tunnel. For more information, see Section 15.3.3.6.5, “Monitoring Outside the Firewall with an SSH Tunnel”.

You can also check the agent error log file to help determine any problems. An error such as the following might indicate a blocked port:

(critical) connection to  merlin-server
'http://agent:[email protected]:18080/heartbeat' failed:
"connect() timed out!" error.

For the location of the agent error log file see, Agent Log and PID Files.

Setting the log-level entry in your ini file is also a good debugging technique. For more information on this subject see, Section 15.3.3.6.1, “MySQL Enterprise Monitor Agent (mysql-monitor-agent.ini) Configuration”.

Running the agent from the command line sometimes displays errors that fail to appear in the log file or on the screen when the agent is started from a menu option. To start the agent from the command line see the instructions given at the start of this section.

If you have more than one agent running on the same machine, the UUID must be unique and the log-file and pid-file values must be different. For more information, see Section 15.3.3.6.2, “MySQL Server (agent-instance.ini) Configuration”.

If the agent is not running on the same machine that hosts the MySQL server it is monitoring, then you must ensure that the correct host is specified for the agent account. The correct port, typically 3306, must also be open for remote login. For more information about remote monitoring see, Section 15.3.3.6.4, “Configuring an Agent to Monitor a Remote MySQL Server”.

To view the available options for the monitor installer or for the agent installer, at the command line type the executable file name along with the help option.

--help                         Display the list of valid options

--version                      Display product information

--optionfile <optionfile>      Installation option file

                               Default:

--mode <mode>                  Installation mode

                               (Windows)Default: win32
                               (Unix)Default: gtk
                               (Mac OS X)Default: osx
                               (Windows)Allowed: win32 unattended
                               (Unix)Allowed: gtk text xwindow unattended
                               (Mac OS X)Allowed: osx text unattended

--debugtrace <debugtrace>      Debug filename
                               Default:

--installer-language <installer-language> Language selection
                               Default:
                               Allowed: en  jp

--installdir <installdir>      Installation directory
                               (Windows)Default:C:\Program »
                               Files\MySQL\Enterprise\Monitor
                               (Unix)Default:/opt/mysql/enterprise/monitor/
                               (Mac OS X)Default:/Applications/mysql/enterprise/monitor/


--tomcatport <tomcatport>      Tomcat Server Port
                               Default: 18080

--tomcatshutdownport <tomcatshutdownport> Tomcat Shutdown Port
                               Default: 18005

--tomcatsslport <tomcatsslport>Tomcat SSL Port
                               Default: 18443

--usessl <usessl>              Should communication between the Dashboard »
                                      and Service Manager be encrypted?
                               Default: 0


--adminuser <adminuser>        Repository Username
                               Default: service_manager


--adminpassword <adminpassword>Password
                               Default:

--dbport <dbport>              Bundled MySQL Database Port
                               Default: 13306

--help                         Display the list of valid options

--version                      Display product information
                               Default:

--optionfile <optionfile>      Installation option file
                                Default:

--mode <mode>                  Installation mode
                               (Windows)Default: win32
                               (Unix)Default: gtk
                               (Mac OS X)Default: osx
                               (Windows)Allowed: win32 unattended
                               (Unix)Allowed: gtk text xwindow unattended
                               (Mac OS X)Allowed: osx text unattended

--debugtrace <debugtrace>      Debug filename
                               Default:

--installer-language <installer-language> Language selection
                               Default:
                               Allowed: en  jp

--installdir <installdir>      Installation directory
                               (Windows)Default: C:\Program Files\MySQL\Enterprise\Agent
                               (Unix)Default:/opt/mysql/enterprise/agent
                               (Mac OS X)Default:/Applications/mysql/enterprise/agent

--mysqlhost <mysqlhost>         MySQL hostname or IP address
                                Default: 127.0.0.1

--checkmysqlhost <checkMysqlhost>Validation of MySQL hostname or IP address
                                Default: yes

--mysqlport <mysqlport>         MySQL port on 127.0.0.1
                                Default: 3306

--mysqluser <mysqluser>         User name on 127.0.0.1:3306
                                Default:

--mysqlpassword <mysqlpassword> Password for mysqluser on 127.0.0.1:3306
                                Default:

--managerhost <managerhost>     Hostname or IP address
                                Default: 127.0.0.1

--tomcatport <tomcatport>       Port on 127.0.0.1
                                Default: 18080

--agentuser <agentuser>         Agent username on 127.0.0.1:18080
                                Default: agent

--agentpassword <agentpassword> Agent password for agent on 127.0.0.1:18080
                                Default:

--servername <servername>       Hostname to display
                                Default:

For unattended installation on Windows, create an option file named options.server.txt. The following is an example of what the contents of an option file might be.

debugtrace=C:\Program Files\MySQL\Enterprise\install.debugtrace.log
mode=unattended
installdir=C:\Program Files\MySQL\Enterprise
tomcatport=8080
tomcatshutdownport=8005
tomcatsslport=8443
adminpassword=myadminpassword
dbport=3300

This file identifies a directory and file name for a log file, sets the mode to unattended, and uses the installdir option to specify an installation directory. The meaning of the other options is fairly self-evident.

Ensure that the monitor installer file and the options file are in the same directory and, if you saved the options file as options.server.txt, you can invoke an unattended installation from the command line by typing:

  C:\ mysqlmonitor-version-windows-installer.exe --optionfile options.server.txt

You can install the MySQL Enterprise Monitor Agent in exactly the same fashion. Create an agent option file and call the agent installer using the optionfile option.

As a minimum for the agent installation, you must specify the mode (if not specified at the command line), mysqluser, installdir, mysqlpassword, installdir, managerhost, and agentpassword options. Create a file containing these values and use it with the optionfile option for unattended agent installation.

If you wish, you can create one script that calls both the Service Manager and the Monitor Agent programs passing appropriate optionfile options.

For unattended installation on Unix, create an option file named options.server.txt. The following is an example of what the contents of an option file might be for installation on Unix.

debugtrace=/opt/mysql/enterprise/install.debugtrace.monitor.log
mode=unattended
installdir=/opt/mysql/enterprise/monitor
tomcatport=8080
tomcatshutdownport=8005
tomcatsslport=8443
adminpassword=myadminpassword
dbport=3300

This file identifies a directory and file name for a log file, sets the mode to unattended, and uses the installdir option to specify an installation directory. The meaning of the other options is fairly self-evident.

Ensure that the monitor installer file and the options file are in the same directory and, if you saved the options file as options.server.txt, you can invoke an unattended installation from the command line by typing:

  shell> mysqlmonitor-version-installer.bin --optionfile options.server.txt

You can install the MySQL Enterprise Monitor Agent in exactly the same fashion. Create an agent option file and call the agent installer using the optionfile option.

As a minimum for the agent installation, you must specify the mode (if not specified at the command line), mysqluser, installdir, mysqlpassword, and agentpassword options. Create a file containing these values and use it with the optionfile option for unattended agent installation.

If you wish, you can create one script that calls both the Service Manager and the Monitor Agent programs passing appropriate optionfile options.

The procedure for unattended agent installation under Mac OS X is identical to the procedure under Unix.

For instructions on starting the services needed by the MySQL Enterprise Service Manager see, Section 15.3.2.5, “Starting/Stopping the MySQL Enterprise Monitor Service on Windows” for Windows and, Section 15.3.2.6, “Starting/Stopping the MySQL Enterprise Monitor Service on Unix and Mac OS X” for Unix and Mac OS X.

For instructions on starting the MySQL Enterprise Monitor Agent see:

If you wish, you can script the startup of these services.

From time to time there may be updates to the MySQL Enterprise Service Manager or the MySQL Enterprise Monitor Agent. This section describes how to perform an update for either of these components.

You cannot use the update installers to change to a different operating system or chip architecture. For example, you cannot update a 32-bit Linux installation to a 64-bit version using an update installer — in cases such as this you must do a fresh installation.

The name of the update file varies but it shows the target operating system and the version the update applies to. If a specific component is being updated it may also appear in the file name. For example, a file named mysqlenterprisemanager-2.0.0-windows-update-installer.exe would indicate a Windows update to MySQL Enterprise Service Manager version 2.0.0.

You may install an update in the same way that you initially installed the service manager or the agent; in win32 or unattended mode on Windows in gtk, text, xwindow, or unattended mode on Unix and in osx, text , or unattended mode on OS X.

Otherwise, updating is a fairly straightforward process. Run the installation file and choose the directory of your current installation and whether or not you wish to back up your current installation. The time required to complete the process varies depending upon the nature of the update.

If you chose to back up your current installation, a directory named backup will be created in the current installation directory. This directory will contain copies of the directory or directories that were replaced during the update. In cases where only specific files are replaced, the backup directory may contain only these files. If you are unhappy with the update simply overwrite the new files or directories with the originals found in the backup directory. Be sure to stop both the MySQL Enterprise Service Manager and MySQL Enterprise Monitor Agent before restoring the original files. You can delete or archive this directory when you are satisfied that the update was successful.

If you choose to back up your current installation, the installer checks that there is adequate disk space for your repository backup. If there is not enough space, you are given the option of choosing another location; you may also choose not to back up the repository.

To update your Advisors see, Section 15.3.2.7.4, “Upgrading and Updating Advisors”.

15.3.6.1.1. Upgrading from MySQL Enterprise Monitor 1.3 to 2.0

15.3.6.1.1.1. Upgrading to MySQL Enterprise Service Manager 2.0

15.3.6.1.1.2. Upgrading to MySQL Enterprise Monitor Agent 2.0

To upgrade your existing installation from MySQL Enterprise Monitor 1.3 to MySQL Enterprise Monitor 2.0, you need to upgrade both your MySQL Enterprise Service Manager and your MySQL Enterprise Monitor Agent on each machine that you are monitoring.

To perform the update process you must use an update installer. This ensures that your current configuration information is migrated to the new version of MySQL Enterprise Service Manager.

Before you start the migration, shutdown your MySQL Enterprise Service Manager and MySQL Enterprise Monitor Agent on each monitored host. Then install the updated MySQL Enterprise Service Manager application to migrate the configuration and data of the main application and repository. Once the new MySQL Enterprise Service Manager is running, you can start to update and migrate each agent.

For more information on upgrading your MySQL Enterprise Service Manager, see Section 15.3.6.1.1.1, “Upgrading to MySQL Enterprise Service Manager 2.0”. For more information on upgrading an MySQL Enterprise Monitor Agent, see Section 15.3.6.1.1.2, “Upgrading to MySQL Enterprise Monitor Agent 2.0”.

15.3.6.1.1.1. Upgrading to MySQL Enterprise Service Manager 2.0

Upgrading MySQL Enterprise Service Manager requires you to use on of the update installers. The update installer performs a number of operations during installation:

• A new database, required to support 2.0 functionality, is created.

• You core dashboard, user, and rule information is migrated from the old database to the new database.

• The core configuration parameters for the MySQL Enterprise Service Manager are migrated from MySQL Enterprise Monitor 1.3 are migrated to MySQL Enterprise Monitor 2.0.

The installation of the new software using the update installer follows this basic sequence:

1. Request the installation language.

2. Confirm the location of the current MySQL Enterprise Service Manager installation.

3. Specify whether you want to keep a copy of the old server, application, and database files.

4. Configure the Tomcat server settings, including whether the new server should support SSL connections from agents.

5. If requested, the application and database information is backed up and upgraded, before the new application is installed.

The installation process is consistent for all platforms. A sample of the process for Max OS X has been provided below:

1. Double click on the update installer. The update installer will have update in the file name. For example, mysqlmonitor-2.0.0.7101-osx-update-installer.app.

2. Confirm the language you want to use when installing the software.

   Figure 15.24. MySQL Enterprise Monitor: Server Update: Language Selection

   

   Click OK

3. You will be presented with an information screen showing the application you are installing. Click Next to continue.

4. Specify, or locate, the previous installation of MySQL Enterprise Service Manager If you installed the server within the default location, the current version of the application should be located automatically.

   Figure 15.25. MySQL Enterprise Monitor: Server Update: Previous Installation

   

5. The installer can keep a backup copy of your existing application, including keeping a complete backup of the data stored within your MySQL Enterprise Monitor repository database.

   Figure 15.26. MySQL Enterprise Monitor: Server Update: Backup of Previous Installation

   

   Specify the location of the backup (default is to use the backup directory within your installation directory). Note that backing up the database in addition to the main application will increase the installation time as the files have to be copied. The larger the size of your repository data, the longer the installation process will take.

6. Specify the Tomcat Server options. The Tomcat Server Port is the default port you will use to access the MySQL Enterprise Dashboard. If you want to support agents using SSL to communicate to MySQL Enterprise Service Manager, you must check the Is SSL support required?

7. Confirm that you want to continue the installation. Once installation has started, the backup of you existing application (and database) will start, although the process may take some time. Wait until the process completes.

8. Once the process has completed you will be provided with a notification of the installation process, including how to uninstall the application if you want to do so in the future. If any errors occurred, they will be reported here.

   Figure 15.27. MySQL Enterprise Monitor: Server Update: Completed installing files

   

9. The installation has now completed. You can automatically start the MySQL Enterprise Service Manager and view the attached Readme file by ensuring the checkboxes on this page are selected.

10. You can now quit the installer.

Once the installation has completed, the first time you login to MySQL Enterprise Dashboard you will be asked to provide your login credentials, if they do not already exist in the server configuration, or to provide a copy of the Advisor jar suitable for your MySQL Enterprise Service Manager version.

Figure 15.28. MySQL Enterprise Monitor: Server Update: Final Setup

MySQL Enterprise Monitor has now been updated. You must update each of your agents to MySQL Enterprise Monitor Agent 2.0 to ensure that they are providing the correct information to MySQL Enterprise Service Manager

15.3.6.1.1.2. Upgrading to MySQL Enterprise Monitor Agent 2.0

To upgrade an agent you should use a update installer. This will migrate your configuration information, simplifying the upgrade process significantly.

Note

The agent log file, mysql-service-agent.log, if it exists, will be retained during the upgrade. A new log file, mysql-monitor-agent.log is used by MySQL Enterprise Monitor Agent 2.0.

The core sequence is the same on all platforms, the update process on Linux is shown below:

1. Start the update installer.

   shell> ./mysqlmonitoragent-2.0.0.7101-linux-glibc2.3-x86-32bit-update-installer.bin

2. Set the language for the installation process.

   Language Selection
   
   Please select the installation language
   [1] English
   [2] Japanese
   Please choose an option [1] :
   

3. Confirm or update the location of the installation directory of the previous version.

   ----------------------------------------------------------------------------
   Welcome to the setup wizard for the MySQL Enterprise Monitor Agent Update
   
   ----------------------------------------------------------------------------
   Please specify the directory that contains the previous installation of
   the MySQL Enterprise Monitor Agent
   
   Installation directory [/opt/mysql/enterprise/agent]:
       

4. Specify whether you want to create a backup of the current application and configuration information, and if so, where the backup directory should be created.

   ----------------------------------------------------------------------------
   Current installation backup
   
   Do you want to create a backup during the update process?
   
   Backup the current installation [Y/n]: Y
   
   
   Backup directory [/opt/mysql/enterprise/agent/patchbackup]:
   
         

5. You will be asked whether you want to enable the Query Analyzer. The Query Analyzer enables you to monitor the execution stateistics for individual queries executed through your MySQL servers. To enable, you must specify the proxy port, MySQL server and MySQL server port that you want to use. If you do not enable Query Analyzer now, you can enable it later. See Section 15.10, “The Query Analyzer Page”.

   ----------------------------------------------------------------------------
   Query Analysis Configuration
   
   MySQL Proxy enables query monitoring and analysis by listening on the port
   specified below for client connections that are then passed through to a
   backend MySQL database server. It is not needed for basic monitoring
   functionality, but is required for query monitoring and analysis.
   
   Visit the following URL for more information:
   https://enterprise.mysql.com/docs/monitor/2.0/en/mem-query-analysis.html
   
   
   
   Enable Proxy (recommended) [Y/n]:
   
   
   Proxy Port [4040]:
   
   Backend Host: 127.0.0.1   (cannot be changed)
   
   Backend Port: 3306   (cannot be changed)
         

6. You are now ready to complete the installation. Confirm that you want to continue.

   ----------------------------------------------------------------------------
   Setup is now ready to begin installing MySQL Enterprise Monitor Agent Update on your computer.
   
   Do you want to continue? [Y/n]:
   
   ----------------------------------------------------------------------------
   Please wait while Setup installs MySQL Enterprise Monitor Agent Update on your computer.
   
    Installing
    0% ______________ 50% ______________ 100%
    #########################################
   
   ----------------------------------------------------------------------------
   Setup has finished installing MySQL Enterprise Monitor Agent Update on your computer.
   
   Restart MySQL Enterprise Monitor Agent now [Y/n]:
   
   View Readme File [Y/n]: n
         

Before connecting your MySQL Enterprise Monitor Agent to your MySQL server you must update the grants for the MySQL Enterprise Monitor Agent. Connect to the MySQL server and run this statement to update the required grants:

GRANT CREATE, INSERT
  ON mysql.*
  TO  'mysqluser'@'localhost'
  IDENTIFIED BY 'agent_password';

Replacing the mysqluser and agent_password parameters with the values used for connecting your agent to your MySQL server.

Once the update agent has communicated with the MySQL Enterprise Service Manager the core information about the agent and the MySQL server it is monitoring will be migrated to the new data format required by MySQL Enterprise Service Manager 2.0. To migrate the existing stored data, see Section 15.4.2, “Migrating 1.3.x Historical Data to MySQL Enterprise Monitor 2.0”.

--help                         Display the list of valid options

--version                      Display product information

--optionfile <optionfile>      Installation option file
                               Default:

--mode <mode>                  Installation mode
                               (Windows)Default: win32
                               (Unix)Default: gtk
                               (Mac OS X)Default: osx
                               (Windows)Allowed: win32 unattended
                               (Unix)Allowed: gtk text xwindow unattended
                               (Mac OS X)Allowed: osx text unattended

--debugtrace <debugtrace>      Debug filename
                               Default:

--installer-language <installer-language> Language selection
                                Default:
                                Allowed: en  jp

--installdir <installdir>      Previous Installation
                               Default:

--createDataBackup <createDataBackup>

                               Default: 1

--backupDir <backupDir>        Backup directory
                               Default:

In some cases you may want to reinstall MySQL Enterprise Monitor rather than updating your current installation. To reinstall rather than update MySQL Enterprise Monitor follow these steps:

To stop the Monitor Agents see:

Instructions for removing the MySQL Enterprise Service Manager and the MySQL Enterprise Monitor Agent are given in Section 15.3.7, “Uninstalling the MySQL Enterprise Monitor”.

This section describes the best practices to employ when changing your MySQL Enterprise Monitor installation.

%server.reachable% == THRESHOLD

%server.reachable% == THRESHOLD  && CURTIME() NOT BETWEEN '22:00:00' AND '23:00:00'

You can rename an existing server without losing the current historical data or configuration information. Renaming the server also allows you to modify the name of the server to be more descriptive according to the server's role within your organization. For example, you may want to rename a server from the default host name to include the department and application for the MySQL server.

To rename a server, click the rename link next to the server. You will be prompted with information about the server, including the host name and registered IP addresses for the agent. Fill in the alternative name that you want to be displayed in the text box at the bottom of the window.

Figure 15.38. MySQL Enterprise Dashboard: Server Renaming

All monitored servers are automatically included in the top level server grouping, All Servers. Other server groupings are replication groups or user-defined groups.

You can create a user-defined group by clicking on the Manage Servers link. Add a group name and then click the create group button. The new group will be displayed immediately.

Replication groups are automatically discovered by MySQL Enterprise Monitor and in this respect differ from user-defined groups. For more information about replication groups see Section 15.11, “The Replication Page”. However, like user-defined groups you can edit the name of a replication group and add other servers to it.

To add to a group, select the add to group link. Choose the server or servers you wish to add and then complete the operation by choosing the add to group button. You can add a server to a group even if the agent is down.

To remove a server from a group expand the server group tree and click the remove from group link. To delete a server altogether see Section 15.6.3.3, “Removing a Server From the Dashboard”.

There are three ways to modify an existing group; by renaming it, adding to it, or removing it. Select the rename link to change the name of a group and add to group to add additional servers. Deleting a group simply requires clicking the remove all from group link. This removes the server group but has no effect on individual servers.

If you no longer wish to monitor a MySQL server you can remove it from the Dashboard. There is no provision for deleting an active server from the Dashboard—to remove a server you must make it inactive by stopping the agent.

For instructions on stopping an agent see:

Once the agent is stopped you may delete the monitored server. Deleting a server simply means that it will no longer show up in the Dashboard.

Remove a server by choosing the Settings tab and then the Manage Servers link. Find the server you wish to remove and delete it by clicking the delete link. Deleting a server from the All Servers group or from any other group will remove it from the Dashboard entirely.

The Subscriptions Warning section on the product information page displays any warnings relative to your subscription. For example, if your subscription has expired you may receive a message such as the following:

Your Subscription Needs to be Updated
* Your Platinum subscription expired 3 days ago on Feb 14, 2008 11:59:59 PM.
If the subscription information on this page is not current, you can
update it by going to the Enterprise Monitor Global Settings page and
providing MySQL Enterprise credentials or by importing a new product
key that you downloaded from http://www.mysql.com/enterprise/download.php.
To update or renew your subscription, please contact your MySQL
Account Representative at [email protected] or visit
http://www.mysql.com/about/contact/renew.html.  After the update or
renewal is complete you can then follow the above instructions for
updating your subscription.

Follow these instructions to update your subscription. If you see this message and your subscription has already been updated, simply click the update button in the Contract Status section of this page. This should update your subscription and remove the warning.

It is particularly important that Notifications be set for the Heat Chart group of rules. This is easily done from the Current Schedule page by clicking the + button beside a rule and then clicking a server.

Doing this opens a window with three tabs—Overview, Settings, and Advanced.

The Overview tab shows which advisor group a rule belongs to, a description of its purpose, and a link to the history of this alert.

In the Settings tab you can adjust the frequency of this rule and also specify a notification group. To select more than one contiguous group press the Shift key and click the desired groups. (Some web browsers may require that you drag your selection.) Noncontiguous selections are made by holding down the Control key and clicking the desired groups.

If you haven't set up global SNMP traps and would like your Network Management System (NMS) to handle events related to a specific rule then check the Use SNMP Traps checkbox. For more information about Simple Network Management Protocol (SNMP) see ???.

The Advanced tab gives detailed information about how this rule is implemented.

Similar existing rules are grouped together in advisor groups.

The built-in advisors are:

The ability to create your own advisor group allows you to create groupings suitable to your circumstances.

You can create your own grouping by simply clicking the create advisor button. Enter an appropriate name and click the add button. The newly created group will appear in the Advisor column.

The newly created advisor is added to the list box of advisors shown in Figure 15.43, “MySQL Enterprise Dashboard: Editing Rules”. You can now use this category of advisors when you create a new rule.

Rules are created using the same screen seen in Figure 15.43, “MySQL Enterprise Dashboard: Editing Rules”. To begin creating a rule from scratch, click the create rule button. However, the simplest way to create a new rule is to copy an existing one. Unlike editing an existing rule, when you copy a rule, every element of that rule is editable.

You can change the rule name, the advisor group that a rule belongs to and you can set your own version number. In Figure 15.43, “MySQL Enterprise Dashboard: Editing Rules”, you have already seen how the threshold and frequency of a rule may be altered.

Most importantly you can alter a rule's expression. Expressions are the core of a MySQL Enterprise Advisor and are used to define the scenario being monitored. An expression can be as simple as a single server parameter or can be quite complex, combining multiple parameters with various mathematical operations.

An expression has two main characteristics:

If an expression evaluates to true for a specific server, an alarm is raised, indicating that a best practice is not being followed. If an expression evaluates to false no alarm is raised because the best practice is indeed being followed.

For example, if having binary logging enabled is considered a best practice for a production server (which we believe it is), then this best practice is being violated if log_bin is OFF. Consequently, the expression for the “Binary Logging Not Enabled” rule is “%log_bin% == OFF”. If this evaluates to 1, an alarm is raised because the best practice is not being followed.

An expression is made up of one or more variables and zero or more mathematical operators. The MySQL Enterprise Monitor uses the MySQL database server's expression parser and evaluator For a complete list of operators and functions see http://dev.mysql.com/doc/refman/5.0/en/functions.html. For a complete list of the built-in variables used when creating rules see http://dev.mysql.com/doc/refman/5.0/en/mysqld-option-tables.html.

Creating an expression is dependent on variables defined in the Variable Assignment frame. This frame links variables used in the expression field with data gathered from the target MySQL server instance—server status variables, operating system status information, and table information. Variable names are associated with elements in the Data Item drop-down list. If you need to define more than one variable simply click the add row button. For a complete listing of the data collection items used in creating rules see The Data Collection Items Used to Create Rules.

The remaining fields determine the information that displays in a notification email or the informational pop-up window associated with each advisor.

When an expression is evaluated variables get replaced by values. For example, part of the expression for the “MyISAM Key Cache Has Sub-Optimal Hit Rate” rule calculates the hit rate as follows:

100-((%Key_reads% / %Key_read_requests%)*100)

If the current value of %Key_reads% is 4522 and the current value of %Key_read_requests% is 125989, the hit ratio assesses to 96.4%:

100 -((4522 / 125989) * 100)

By convention, the Advisors supplied by MySQL use ‘%’ as the delimiter, for example, %Key_reads%. This makes variables more readily identifiable.

In addition to being used in an expression, variables may also be used in the Description, Advice, Action, and Links attributes of a rule. This allows you to report the current value of an expression.

For instance, you can add the message, “The current value of Key_reads is %Key_reads%.” to the Advice text box. When this is displayed on the screen, the value of %Key_reads% is substituted into the text. Supposing %Key_reads% has a value of 4522, the message becomes “The current value of Key_reads is 4522.”

Each expression has a threshold value that triggers an alert. The THRESHOLD keyword is used to associate that value with an alert level—either an Info, Warning, or Critical alert.

For example, the expression for the performance advisor, “Thread Cache Size May Not Be Optimal”, is:

100-((%Threads_created% / %Connections%) * 100) < THRESHOLD

The THRESHOLD is set at 95% for an Info level alert, 85% for a Warning alert, and 75% for a Critical alert; producing alerts of three different levels.

Expressions can be quite simple. The expression for “Binary Logging Not Enabled” (one of the Administration alerts) is:

%log_bin% == THRESHOLD

When the result is OFF, only one alert is triggered—a Warning level alert. In this situation you might think we could just use the expression %log_bin% == "OFF". However, doing this would not test binary logging against a threshold so would not result in an alert.

When you create an expression, think carefully about the conditions under which it should be evaluated and the conditions under which it should not. For example, the expression for the “MyISAM Key Cache Has Sub-Optimal Hit Rate” rule is:

(%Uptime% > 10800) && (%Key_read_requests% > 10000) »
 && (100-((%Key_reads% / %Key_read_requests%) * 100) < THRESHOLD)

The essence of the rule is really: (100-((%Key_reads% / %Key_read_requests% ) * 100) < THRESHOLD). However, when a server is first starting up, it may take a while to reach a state that is representative of normal operations. For example, the key cache and the query cache may need some period of time before they have cached typical application data as opposed to start-up and initialization data. In this case, the first part of the expression, (%Uptime% > 10800), holds off evaluating this expression until the system has been running for 10800 seconds (3 hours).

In addition, if some part of the system is not heavily used an alert may be triggered based on limited data. For example, if your application does not use the MyISAM storage engine, the “MyISAM Key Cache Has Sub-Optimal Hit Rate” rule may be triggered based on very limited use of other MyISAM tables such as the mysql.user table. For this reason, this advisor has a second part— (%Key_read_requests% > 10000)–meaning the rule won't be evaluated unless there is plenty of activity associated with the key cache.

In other circumstances, there may be periods of time during which you don't want a rule to be evaluated—a blackout period. For example, the expression for the “Slave Too Far Behind Master” rule is: %Seconds_Behind_Master% > THRESHOLD. However, suppose you run a backup process between 6 and 7 pm on a replication slave, and it's normal for that slave to get behind the master by an amount more than the THRESHOLD during that time. In that case you don't want to receive an alert because the rule violation is expected. You can achieve this by adding the following to the expression: && CURTIME() NOT BETWEEN '18:00:00' AND '19:00:00' In essence, this means “don't trigger an alert between 18:00:00 and 19:00:00 (6 pm and 7 pm)”.

String values may appear in the Expression or the Thresholds text boxes. In both cases, they must be enclosed within quotation marks. For example, the expression for the “Slave I/O Thread Not Running” rule is:

(%Slave_running% == "ON") && (%Slave_IO_Running% != THRESHOLD)

In similar fashion the Critical Alerts threshold text box is set to a value of "Yes".

When the expression is evaluated, either "OFF" or "ON" will be substituted for %Slave_running%, and "Yes" or "No" for %Slave_IO_Running%, depending on the state of your system. If the slave is running but the I/O thread is not, the expression then becomes:

("ON" == "ON") && ("No" != "Yes")

Without quotation marks this expression would not evaluate to TRUE as it should.

When editing or defining a rule, the text entered in the Problem Description, Advice, Recommended Action, and Links and Further Reading text boxes may be formatted in Wiki format. This allows you to format text and add hyperlinks when creating or editing your own rules.

Find a brief introduction to using Wiki formatting in the following table.

So the following Wiki text:

Replication is a __very nice feature__ of MySQL.  Replication can be very
useful for solving problems in the following areas:
* Data Distribution
* Load Balancing
* Backup and Recovery
You can check replication status and start a slave using the following
commands: SHOW SLAVE STATUS \\\\G\\START SLAVE;
{moreInfo:MySQL Manual: Replication
      FAQ|http://dev.mysql.com/doc/refman/5.0/en/replication-faq.html}

Would be translated into the following HTML markup:

Replication is a <b>very nice feature</b> of MySQL.  Replication can be very
useful for solving problems in the following areas:
<ul>
  <li>Data distribution</li>
  <li>Load Balancing</li>
  <li>Backup and recovery</li>
</ul>You can check replication status and start a slave with the following
commands: SHOW SLAVE STATUS \G;<br/>START SLAVE;
<a href="../mysql-monitor-2.0/http://dev.mysql.com/doc/refman/5.0/en/replication-faq.html"
  target="_blank" >MySQL Manual: Replication FAQ</a>

To find out more about this format go to the wikipedia.org web site.

This section documents the steps required to create a rule. Before attempting to create a rule, please review the preceding sections of this chapter.

This example creates a rule that checks the number of rows in a table. Having 50,000 rows in this table is deemed to warrant a critical alert. Lesser numbers are assigned to informational and warning level alerts.

Begin by navigating to the Advisors tab and clicking the manage rules link. Then choose the create rule button.

Create your custom rule by following these steps:

Once the rule is created it needs to be scheduled against the server that contains the database table you wish to monitor. For instructions on scheduling rules see Section 15.7.2, “Scheduling Rules”.

Section 15.7.4.7, “Creating a New Rule: An Example” shows how to create a custom rule and The Data Collection Items Used to Create Rules shows the data items that can be used in rule creation. However, in some circumstances you may want to create a rule that uses a custom data collection item.

This section describes how to create a custom data collection item. The steps are as follows:

<?xml version="1.0" encoding="utf-8"?>
<classes>
  <class>
    <classname>innodb_min_free</classname>
    <namespace>mysql</namespace>
    <query><![CDATA[SELECT MIN(substring_index(substring_index(table_comment," ",3)," ",-1)/1024/1024)
      as Free FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'InnoDB']]></query>
  </class>
</classes>

[mysql-proxy]
 ...
item-files = items-mysql-monitor.xml,innodb_min_free.xml
 ...

Rather than opening your web browser and blacking out a server by typing entries into the address bar, you can write a script to achieve the same effect. This section documents a sample blackout script that can be run from the command line.

Create the following file and save it as blackout.pl.

  #!/usr/bin/perl
  use LWP 5.64;
  # USAGE: blackout.pl servicemanager:18080 admin password servername:3306 true
  # $ARGV[0] = management server hostname:port
  # $ARGV[1] = management server username
  # $ARGV[2] = management server password
  # $ARGV[3] = mysqld managed instance server name and port
  # $ARGV[4] = blackout state (true/false)
  my $browser = LWP::UserAgent->new;
  $browser->credentials(
    $ARGV[0],
    '',
    $ARGV[1],
    $ARGV[2]
  );
  my $url = URI->new('http://'.$ARGV[0].'/rest');
  $url->query_form( # And here the form data pairs:
    'command' => 'blackout',
    'server_name' => $ARGV[3],
    'blackout_state' => $ARGV[4]
  );
  my $response = $browser->post( $url );
  if (!$response->is_success) {
    die $response->status_line . "\n";
  }

On Unix systems use the chmod +x blackout.pl command to make the file executable.

At the command line enter blackout.pl servicemanager:18080 admin password servername:3306 true.

If you are unsure of the host name and port to use, check the configuration_report.txt file. Be sure to specify the correct port for the Tomcat server. Specify the server you wish to blackout using the name that appears in the Server Tree, being sure to include a colon and port number as shown in the preceding example. Make sure that the user you specify is a "manager". Specifying a user with "dba" rights only will not black out a server and no error will be displayed.

You can confirm that a server is blacked out by looking at the server name in the Dashboard; the name of a blacked out server is greyed. To end the blackout, run the same script, changing the final argument to false.

Generally, changing your MySQL client application is the easiest and recommended method. For example, given a typical structure like the one shown in the figure below, the client application would need to be modified so that it no longer communicated directly with the MySQL server, but to the agent/proxy.

Figure 15.46. MySQL Enterprise Dashboard: Standard Agent/Monitor Topology

You can see an example of the structure when communicating via the agent/proxy below.

Figure 15.47. MySQL Enterprise Dashboard: Query Analyzer Agent/Monitor Topology

To enable query analyzer within your MySQL client application:

shell> mysql --port=4040 --protocol=tcp
shell> mysql --port=4040 --host=127.0.0.1

When enabling Query Analyzer by changing the MySQL Server, you need to shutdown your server, edit the MySQL configuration file, and then restart MySQL. You will also need to change your Agent/proxy configuration so that the Agent/proxy is listening on the original MySQL TCP/IP port. To use this method:

You should now be able to connect to your MySQL server through the MySQL Enterprise Monitor Agent by connecting on the original port:

shell> mysql --host=127.0.0.1