ZenPack:MySQL Database Monitor (Core)

From Zenoss Wiki
(Redirected from ZenPack:MySQLMonitor)

This is the approved revision of this page, as well as being the most recent.
Jump to: navigation, search
Zenoss, Inc.
GNU General Public License, Version 2, or later
ZenPack name
Git sources (for cloning)

Applications Monitored: 

MySQL Database Monitor (Core) ZenPack

This ZenPack provides status and performance monitoring of MySQL.


The ZenPack Catalog has moved to its new home at https://www.zenoss.com/product/zenpacks as of January 17, 2017. The following information may be out of date, and this page will eventually be removed.


This is an Open Source ZenPack developed by Zenoss, Inc. Enterprise support for this ZenPack is available to commercial customers with an active subscription.


Version 3.1.0- Download
Released on 2017/12/29
Requires PythonCollector ZenPack
Compatible with Zenoss Core 4.2.x, Zenoss Core 5.0.x, Zenoss Core 5.1.x, Zenoss Resource Manager 4.2.x, Zenoss Resource Manager 5.0.x, Zenoss Resource Manager 5.1.x, Zenoss Resource Manager 5.x.x
Version 3.0.9- Download
Released on 2017/04/04
Requires PythonCollector ZenPack
Compatible with Zenoss Core 4.2.x, Zenoss Core 5.0.x, Zenoss Core 5.1.x, Zenoss Resource Manager 4.2.x, Zenoss Resource Manager 5.0.x, Zenoss Resource Manager 5.1.x, Zenoss Resource Manager 5.x.x


This ZenPack provides monitoring for MySQL 5.5 and later. Monitoring for the following MySQL entities is provided using twisted.enterprise.adbapi asynchronous framework. See the Usage section for details.

Bulbgraph.png Note: Please restart zenoss after installing this ZenPack. Note that the commercial zenoss version requires ZenPacks.zenoss.Impact, otherwise the DynamicServices panel will be missing.

This Zenpack depends on PythonCollector Zenpack (supported versions >= 1.1) being installed.



The features added by this ZenPack can be summarized as follows. They are each detailed further below.

  • Discovery of MySQL entities.
  • Monitoring of MySQL Server and Database components.
  • Event management and monitoring for certain MySQL states.
  • Optional service impact with addition of Zenoss Service Dynamics product.


The following entities will be discovered through zMySQLConnectionString property you provide. The attributes, tags and collections will be updated on Zenoss' normal remodeling interval which defaults to every 12 hours.

Attributes: Percentage of full table scans, Slave status, Master status, Number of databases
Tags: Name
Collections: Databases
Attributes: Number of tables, Default character set, Default collation
Tags: Name

Note that all the discovered values for the attributes above are shown as of the last modeling time.


The following metrics will be collected every 5 minutes by default. The Average statistic is collected, and the graphed value is per second for anything that resembles a rate.

  • Size
  • Data size
  • Index size
  • Connections
  • Threads connected
  • Max used connections
  • Uptime
  • Aborted clients
  • Aborted connects
  • Bytes received
  • Bytes sent
  • Com create db
  • Com drop db
  • Com alter db
  • Com create table
  • Com alter table
  • Com drop table
  • Com create user
  • Com drop user
  • Com call procedure
  • Com commit
  • Com check
  • Com delete
  • Com delete multi
  • Com execute sql
  • Com flush
  • Com insert
  • Com insert select
  • Com purge
  • Com repair
  • Com replace
  • Com rollback
  • Com select
  • Com update
  • Com update multi
  • Handler commit
  • Handler delete
  • Handler rollback
  • Handler update
  • Handler write
  • Handler read first
  • Handler read key
  • Handler read last
  • Handler read next
  • Handler read prev
  • Handler read rnd
  • Handler read rnd next
  • Handler savepoint
  • Key reads
  • Key writes
  • Key read requests
  • Open files
  • Open streams
  • Open tables
  • Select full join
  • Select full range join
  • Select range
  • Select range check
  • Select scan
  • Size
  • Data size
  • Index size

Event monitoring

According to the following MySQL states this ZenPack will trigger events with an appropriate summary message.

Critical event:
  • Invalid zMySQLConnectionString property,
  • Access denied for user with credentials provided in zMySQLConnectionString property.
Error event:
  • Slave error,
  • MySQL server is not accessible.
Info event:
  • A database was dropped.
Warning event:
  • A table was dropped.
Info event:
  • A table was added.
  • InnoDB Deadlock occurred.

Service Impact

When combined with the Zenoss Service Dynamics product, this ZenPack adds built-in service impact capability for MySQL. The following service impact relationships are automatically added. These will be included in any services that contain one or more of the explicitly mentioned entities.

Service Impact Relationships
  • Device failure impacts the Server component.
  • MySQL Server component impacts Database components.


To start monitoring your MySQL server you will need to setup connection to it as well as bind monitoring templates and modeler plugin to the device or device class containing your MySQL servers.

Use the following steps to start monitoring MySQL using the Zenoss web interface.

  1. Navigate to the Configuration Properties page of the device containing your MySQL servers (see [1]).
  2. Set user name, password and port in the appropriate fields of zMySQLConnectionString property (see [2]). For non-root users please make sure to grant all privileges to avoid access denied errors. Note that user name is required.
  3. Navigate to the Modeler plugins page of the device containing your MySQL servers, add the MySQLCollector modeler plugin and remodel the device. This will automatically find the MySQL servers and databases and begin monitoring them immediately for the previously mentioned metrics.

Bulbgraph.png Note: As the custom widget for MySQL credentials is not compatible with Zenoss 4.1, the zMySQLConnectionString property in this version should be set as a valid JSON list (e.g. [{"user": "root", "passwd": "", "port": "3306"}] )

Legacy method

The latest version of ZenPack uses zenpython daemon for monitoring and automatically binds monitoring templates to MySQL components. However, if you are inclined to use the legacy method for MySQL monitoring (v2.2.0), you should:

  1. Bind the device-level monitoring template MySQL /Server to the target device.
  2. Set zMySqlUsername, zMySqlPassword and zMySqlPort configuration properties (if the credentials are not valid or not set, you will receive the event with in 'App/MySQL' event class).
  3. Make sure that zencommand daemon is running.


If you are using the commercial zenoss version and zends, please make sure to create a symbolic link by executing the following command: ln -s /var/lib/zends/zends.sock /tmp/mysql.sock


If you encounter any issues with Zenpack installation, please make sure that you have restarted zenoss after installation and your target disk has enough free memory and read permissions enabled. In case of any issues when uninstalling this Zenpack, please remove all components first. Note that device monitoring requires zenpython daemon to be constantly running. If you have just enabled monitoring, try pressing Zoom in to see the graphs. In case of any issues you can run zenpython daemon in foreground with zenpython run -v10 -c command and check if there are no exceptions. If zenpython runs without errors and prints monitoring data to stdout, then you should check if monitoring templates are bound to the components.

In case device modeling fails
  • make sure, that you have specified a correct user, password and port to your MySQL user account;
  • grant privileges (see MySQL GRANT Syntax) to non-root users to avoid access denied errors by executing the following command. REPLICATION CLIENT privilege is needed to retrieve MySQL Replication data.
Change '%' to 'localhost' in the statement above if you are modeling the local MySQL server.
  • check if firewall allows connections on mysql port;
  • ensure that you are able to connect from zenoss server to target MySQL server instance by executing the following from the command line:
 mysql -h<host> -u<username> -p<password> -P<port> 

Note that the query select host from mysql.user should have an entry ‘%’ for ‘host’ field to ensure the remote connection.


When migrating from MySqlMonitor ZenPack v2.2.0, you should:

  • Install the new version of the ZP;
  • Restart Zenoss Services;
  • Make sure to leave the old version in place during the install process. The new zMySQLConnectionString property will be constructed using MySQL credentials from older ZenPack version;
  • Bind the modeler plugin as described in the Usage section;
  • Model the device components;
  • Remove the old monitoring template (MySQL /Server) from the device.

Due to different mechanisms of MySQL monitoring, it is not possible to migrate the old RRD data from the previous version of ZenPack. The Graph data will remain and will be updated until the user manually removes the MySQL /Server monitoring template.

Installed Items

Installing this ZenPack will add the following items to your Zenoss system.

Modeler Plugins
  • MySQLCollector
Monitoring Templates
  • MySQLServer(in /Server)
  • MySQLDatabase (in /Server)
  • MySQL (in /Server) (obsolete)
Component Types
  • MySQLServer (on related device)
  • MySQLDatabase (on MySQLServer)


3.1.0 (2017-12-29)
  • Fixed error messages to be more informative. (ZPS-2684)
  • Fix missing clear events. (ZPS-2432)
  • Fix unit tests.
3.0.9 (2017-04-04)
  • Update events and event class mappings to facilitate transforms. (ZPS-500)
3.0.8 (2017-01-16)
  • Give datasource events unique eventKeys to avoid false-clearing. (ZPS-500)
  • Set default timeouts for data collection DB connection. (ZPS-313)
3.0.7 (2015-11-25)
  • Fix datasources to not block zenpython. (ZEN-19220)
3.0.6 (2015-06-18)
  • Added zMySQLTimeout property and timeout for datasource plugins.
3.0.5 (2015-02-05)
  • Restore functionality of deprecated MySqlMonitor datasource. (ZEN-16452)
3.0.4 (2014-12-18)
  • Add support for Zenoss 5x.
  • Move InnoDB Deadlock event to database component, and clear it in 30min.
3.0.3 (2014-11-05)
  • Fix graphpoint sequences to fix graphpoint colors.
  • Close connections pools when done to avoid leaking threads.


Normal Installation (packaged egg)

  1. Download the appropriate egg file for the version of Zenoss you are running.
  2. Ensure you are logged in as the zenoss user:
    $ sudo su - zenoss
  3. Install the ZenPack:
    $ zenpack --install ZenPacks.zenoss.MySqlMonitor-*.egg
  4. Restart these services:
    $ zenoss restart

Developer Mode Installation

In order to do a development mode installation you will want to clone the existing git repository, and then use the --link flag with the zenpack command:

  1. Ensure you are logged in as the zenoss user:
    $ sudo su - zenoss
  2. Start by cloning the upstream repository:
    $ git clone https://github.com/zenoss/ZenPacks.zenoss.MySqlMonitor.git
  3. Next, perform the installation:
    $ zenpack --link --install ZenPacks.zenoss.MySqlMonitor
  4. Finally, restart these serivices:
    $ zenoss restart


Purplemarker.png New: Don't forget to add yourself to the Zenoss User Map!

blog comments powered by Disqus