Configure Munin to graph MySQL on CentOS 5/6 with Virtualmin

Configure Munin to graph MySQL on CentOS 5/6 with Virtualmin

munin-mysqlIf you’re running a CentOS 5/6 VPS/Server with Virtualmin and installed Munin to graph the system stats, you’ll mostly likely notice that the generated graphs do not include MySQL.

This is expected behaviour since Virtualmin asks you to define a root password for your MySQL server (so it isn’t left unsecured out in the open). Unfortunately, by default, Munin tries to read MySQL data using the root account and no password and fails.

But there’s an easy way to fix this:

Enter your Virtualmin administration interface and navigate to Webmin > Servers > MySQL Database Server. Create a new user munin with a safe password (remember it, you’ll need it later), set it to only log in from localhost and add the Superuser permission.

Then navigate to Database Permissions and add the previously created munin user on database mysql, again only for localhost and only grant the Select table data permission.

Moving on, open the /etc/munin/plugin-conf.d/munin-node file in your favourite editor and add (or edit if already exists):
[mysql*]
env.mysqladmin /usr/bin/mysqladmin
env.mysqluser munin
env.mysqlpassword your-previously-defined-password

If you’re using innodb tables,  you will need to create the /etc/munin/plugin-conf.d/mysql_innodb file and this in it to ignore error about it (which Munin will most likely generate):
[mysql_innodb]
env.warning 0
env.critical 0

Now it’s time to test if the plugin works. Run in the console:

munin-node-configure --suggest | grep mysql

You should get something like

mysql_                     | yes  | yes (+bin_relay_log +commands       and a lot of other blabber

If you’re missing any required Perl modules, you’ll see a | no | no and the needed modules’ names. Install them and then try again.

If all is well, it’s time to activate the plugin by running

ln -s /usr/share/munin/plugins/mysql_* /etc/munin/plugins

You can also obtain the shell commands to activate the plugin directly calling munin-node-configure with:

munin-node-configure --suggest --shell


Finally restart munin-node

service munin-node restart

Then just wait 10-20 minutes for your graphs to show up.

5 Comments

  1. munin-node-configure –suggest | grep mysql
    i have

    mysql_ | no | yes (+bin_relay_log +commands +connections +files_tables +innodb_bpool +innodb_bpool_act +innodb_insert_buf +innodb_io +innodb_io_pend +innodb_log +innodb_rows +innodb_semaphores +innodb_tnx +myisam_indexes +network_traffic +qcache +qcache_mem +replication +select_types +slow +sorts +table_locks +tmp_tables)

    what i can do
    please help me

    kosisenam
      1. when i type tailf /var/log/munin-node/munin-node.log
        i have:
        2016/03/23-09:57:40 [3731] Error output from mysql_:
        2016/03/23-09:57:40 [3731] Unknown graph at /etc/munin/plugins/mysql_ line 833.
        2016/03/23-09:57:40 [3731] Service ‘mysql_’ exited with status 255/0.
        2016/03/23-09:57:40 [3731] Error output from mysql_isam_space_:
        2016/03/23-09:57:40 [3731] Enter password: /usr/bin/mysqlshow: Access denied for user ‘munin’@’localhost’ (using password: NO)
        2016/03/23-09:57:40 [3731] Service ‘mysql_isam_space_’ exited with status 1/0.
        2016/03/23-09:58:56 CONNECT TCP Peer: “[192.168.5.12]:54001” Local: “[192.168.5.11]:4949”
        2016/03/23-09:59:58 [4044] Service ‘mysql_slowqueries’ timed out.
        2016/03/23-10:01:00 [4044] Service ‘mysql_threads’ timed out.

        what i can do?please help me

        kosisenam
  2. Hi,

    I noticed that for having the pluging working, i needed to add
    env.mysqlopts -umunin -pyour-previously-defined-password
    to the [mysql*] section
    After adding that the plugin worked
    It looks now like
    [mysql*]
    env.mysqladmin /usr/bin/mysqladmin
    env.mysqluser munin
    env.mysqlpassword your-previously-defined-password
    env.mysqlopts -umunin -pyour-previously-defined-password

    Kind regards

    Tore

    Tore

Leave a Reply