Monitoring MySQL

Document created by Chris Sommerstad Employee on Jul 22, 2017Last modified by Sheril Joseph on Aug 14, 2017
Version 2Show Document
  • View in full screen mode

Prerequisites

In order to monitor a MySQL server, SOASTA CloudTest must be able to establish a TCP/IP connection to the server. If you are not sure how to enable this, consult the MySQL reference manual.

If a firewall prevents TCP/IP access, see the topic Monitoring a Server Behind a Firewall and Additional Instructions for Monitoring a MySQL Server behind a Firewallbelow.

In addition, the MySQL account used by CloudTest must have the PROCESS privilege (this can be done using the GRANT statement).

Example;

For example, using:

GRANT PROCESS ON table_name TO soasta_user;
FLUSH PRIVILEGES;

So that, if the soasta user were called "monitor" in the database, and the tables you needed to monitor were: "users", "orders", and "products", it should be:

GRANT PROCESS ON users,orders,products TO monitor;

For more complex setups, involving MANY tables, you can use:

GRANT PROCESS ON *.* TO monitor;

Note: Ensure that the flush privileges query is run after the grant, and then verify the process privilege is there with:

SHOW GRANTS FOR 'monitor';

Supported Versions

SOASTA CloudTest can currently monitor MySQL 4.0 and later.

Steps

  1. In the Central list, select Monitoring Server Groups.

  1. Click the New Monitoring Server Group icon.

  1. The Monitoring Server / Monitor wizard appears. Select Monitor Server and click Next. 

  1. The Name and Connection step is shown. Type a name for the Monitoring Server and optionally provide a description. 

  1. The Resources step is shown. Check "Databases" and click Next.

  1. The Hosts/Device Agents step is shown. Enter the hostname or IP address of the server into the Host field and click Add. Click Next.

  1. The Database(s) step is shown. Choose the Connection Method. You must choose a Conductor if the database type is set to MySQL because the JDBC driver for MySQL is not bundled in CloudTest. Note the “Direct/SQL” connection method does not work for MySQL.

  1. Select MySQL, then enter the TCP/IP port number, the user name, and password (shown above).

The resulting Monitor will have the characteristics shown below.

  1. Click the Test Connection button to test the connection. If the connection is successful, you will see a pop-up containing some information about the MySQL server. If the connection fails, you will see an error message explaining what went wrong.

  1. Click Next. Review the information to be sure it is correct, then check the "Create a new Monitor for this Monitor Server" box and click Finish.

The Monitor appears in the list of existing monitors. Click Next again to proceed to configure this monitor to track various statistics.

  1. At this point you can choose which statistics you are interested in. For each statistic, you can choose a "sample rate" which determines how often that statistic will be collected.
  • The "Commits" statistic tracks the number of database commits per second.
  • The "Rollbacks" statistic tracks the number of database rollbacks per second.
  • The "Total Connections" statistic tracks the number of users connected to the MySQL server. This will never be zero, because monitoring itself uses at least one connection.
  • The "Per Catalog Connections" statistic tracks the number of users connected to a specific MySQL database. If you use this statistic, you must enter the name of the database in the "Arguments" column.

Note: Use the Name (Optional) column to give the metric a user-friendly label that will be used in the widget's title bar whenever this metric appears on a dashboard.

When you are finished, click Next. Review the information to be sure it is correct, then click Finish.

Next steps

You can also combine database, J2EE, and ColdFusion statistics in the same monitor.

Additional Instructions for Monitoring a MySQL Server behind a Firewall

In addition to the instructions described in Monitoring a Server Behind a Firewall, there are some extra required steps for MySQL. Specifically, the MySQL JDBC driver needs to be installed to the SOASTA Conductor application directory.

Steps

  1. The MySQL JDBC can be downloaded from http://dev.mysql.com/downloads/connector/j/. You can use any version of the MySQL JDBC driver, with any file name.

There is a single download for all operating systems.

  1. Unzip the compressed file. This will create a version specific directory. For example, for 5.1.6 the directory will be called:

mysql-connector-java-5.1.6

  1. Inside the version-specific directory (for example, "mysql-connector-java-5.1.6," find the JAR file. For example, mysql-connector-java-5.1.6-bin.jar.
  1. Copy this file to the SOASTA Conductor directory. The exact directory depends on the operating system:
    • On Microsoft Windows, the default installation directory is C:\Program Files\SOASTA Conductor\.
    • On Linux, the default installation directory is Conductor/lib.
    • On Mac OS X, the default installation directory is /Library/Java/Extensions.

 

  1. Re-start SOASTA Conductor if it is currently running.

Attachments

    Outcomes