Skip to content

MySQL

ZenPacks.zenoss.MySqlMonitor

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

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

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 the addition of Zenoss Service Dynamics product.

Applications Monitored: MySQL (5.5 - 5.7, 8.0)

Open Source

This ZenPack is developed and supported by Zenoss Inc. Contact Zenoss to request more information regarding this or any other ZenPacks. Click here to view all available Zenoss Open Source ZenPacks.

Git Sources (For Cloning)

Link

Support

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

Releases

Version 3.2.0-Download

  • Released on 2023/04/27
  • Requires PythonCollector ZenPack
  • Compatible with Zenoss Resource Manager 6.x and Zenoss Cloud

Version 3.1.0-Download

  • Released on 2017/12/29
  • Requires PythonCollector ZenPack
  • Compatible with Zenoss Resource Manager 6.x and Zenoss Cloud

Discovery

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

Servers

  • Attributes
    • Percentage of full table scans
    • Slave status
    • Master status
    • Number of databases
  • Tags
    • Name
  • Collections
    • Databases

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.

Monitoring

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.

Server

  • Metrics:

    * 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 * nothing

Databases

  • Metrics
    • Size
    • Data size
    • Index size
    • Table count

Event monitoring

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

Device

  • Critical event:
    • Invalid zMySQLConnectionString property
    • Access denied for user with credentials provided in zMySQLConnectionString property

Server

  • Error event
    • Slave IO/Slave SQL error
    • MySQL server is not accessible
  • Info event
    • A database was dropped
    • A database was added

Database

  • 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.

Usage

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 screenshot 1).

  2. Set the user name, password and port in the appropriate fields of the zMySQLConnectionString property (see screenshot 2). For non-root users please make sure to grant all privileges to avoid access denied errors. Note that a user name is required.

    Note

    For MySQL 8.0 and later versions, the Zenoss monitoring user should be identified with "mysql_native_password" or the default authentication method for MySQL should be set to "Native Pluggable Authentication".

    Note

    For non-root users the following privileges should be applied: SUPER, PROCESS, REPLICATION CLIENT, SHOW VIEW.

  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.

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"}] '')

Monitoring busy MySQL servers affects zenpython daemon. You may change the zMySqlTimeout property to specify how long it should wait for response from MySQL server.

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 in the 'App/MySQL' event class).
  3. Make sure that zencommand daemon is running.

Prerequisites

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

Troubleshooting

If you encounter any issues with Zenpack installation, please make sure that you have restarted Zenoss after installation and that 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 the zenpython daemon in the foreground with the zenpython run -v10 -c command to check for exceptions. If zenpython runs without errors and prints monitoring data to stdout, then you should check if monitoring templates are bound to the components.

Should device modeling fail:

  • make sure, that you have specified the 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.

    GRANT SELECT, REPLICATION CLIENT ON \*.\* TO 'username'@'%' WITH
GRANT OPTION;

    Change % to localhost in the statement above if you are modeling the local MySQL server.

  • check if the firewall allows connections on the 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 -u -p -P

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

Please note that "MySqlDeadlockPlugin" used to detect deadlocks in MySQL requires PROCESS / SUPER privileges (depending on MySQL version. The plugin uses a SQL query like: SHOW ENGINE INNODB STATUS to retrieve this information). For security reasons, this plugin may be disabled.

Note

If you don't see all databases in the components list you should apply the SHOW VIEW privilege to the Zenoss monitoring user on the appropriate MySQL Server.

Migration

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 installation process. The new zMySQLConnectionString property will be constructed using MySQL credentials from the 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)

Changes

3.2.0 (2023-04-27)

  • Rework incremental modeling for MySQL databases. (ZPS-6799)
  • Fix event class setting for MySql datasources. (ZPS-5766)
  • Fix uncorroborated impact relationships. (ZPS-5774)
  • Fix "RemoteError" during monitoring. (ZPS-5831)
  • Fix invalid object maps properties in datasource plugins. (ZPS-7081)
  • Change requested MySql datasource datapoints type to DERIVE. (ZPS-7478)
  • Tested with Zenoss Cloud, Zenoss 6.7.0, and Service Impact 5.6.0

3.1.0 (2017-12-29)

  • Fixed error messages to be more informative. (ZPS-2684)
  • Fix missing clear events. (ZPS-2432)
  • Tested with Zenoss Resource Manager 4.2.5 RPS 743, 5.3.3, 6.1.0

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.