Plugin:MySQL

MySQL plugin
Type:	read
Callbacks:	config, read
Status:	supported
First version:	3.6
Copyright:	2006–2010 Florian Forster 2008 Mirko Buffoni 2009 Doug MacEachern 2009 Sebastian Harl 2009 Rodolphe Quiédeville
License:	GPLv2
Manpage:	collectd.conf(5)
List of Plugins

Description

The MySQL plugin connects to an MySQL-database and issues a SHOW STATUS command periodically. The command returns the server status variables, many of which are collected. The plugin has successfully been tested with the MySQL versions 4 and 5.

If you're looking for a way to create statistics by executing and interpreting SQL statements, take a look at the DBI plugin.

If you hoped to find a way to store data in a MySQL database, you're unfortunately out of luck since that is not possible (yet). It does not exist, because we didn't come up with suitable concept yet. You can help us by adding your thoughts and ideas to the “Store To RDBMS” page. Actually writing the code is not the issue.

Synopsis

Version 4.7 and later:

<Plugin "mysql">
  <Database "foo">
    Host "hostname"
    User "username"
    Password "password"
    Port 3306
    # Since version 5.0:
    #Port "3306"
    MasterStats true
  </Database>
  
  <Database "bar">
    Host "localhost"
    Socket "/var/run/mysql/mysqld.sock"
    SlaveStats true
    SlaveNotifications true
  </Database>
</Plugin>

Version 4.6 and before:

<Plugin "mysql">
  Host "localhost"
  User "collectd"
  Password "fee4eeCu"
  #Database "db_name"
  #Port "3306"
  #Socket "/path/to/unix/socket"
</Plugin>

SQL required to create a user for collectd:

 CREATE USER 'collectd'@'localhost' IDENTIFIED BY 'Fat4ohse';
 -- Give appropriate permissions
 -- ("GRANT USAGE" is synonymous to "no privileges")
 GRANT USAGE ON *.* TO 'collectd'@'localhost';
 -- Permissions for the MasterStats and SlaveStats options
 GRANT REPLICATION CLIENT ON *.* TO 'collectd'@'localhost';

The new user will only have privileges to connect to the database. Additional privileges are not required.

Collected statistics

Command statistics

These values contain the number of issues per second for various SQL-commands. Counters that are zero (i. e. the command has never been issued since server startup) are ignored, since graphing all commands would create a lot of very uninteresting RRD-files which just eat up space..

Example graph

Handler statistics

The handler statistics gathered basically refer to the operations that can be done on tables or table-rows. This graph can give you some information if your indexes are okay or if you need some work there. Read the server status variables documentation, though it leaves you with lots of questions. At least it left me wondering what was actually going on… ;)

Example graph

Query-cache statistics

MySQL will try to cache queries (i. e. SELECT statements). Information about the query cache can be found on MySQL's homepage.

Example graph

Thread statistics

Information about the threads of the MySQL process(es).

Example graph

Traffic statistics

Information about incoming and outgoing traffic (bytes).

Example graph

Dependencies

• The MySQL library.