Generating MySQL stats/graphs in Munin
Somewhere between Munin 1.2.x and the 1.4.6 that I'm running now, MySQL stats got a lot more complicated. I only found this out when moving from CentOS 4 to CentOS 6 after a system failure. My munin and mumin-node come from EPEL.
In order to make them work, I had to:
- manually install the perl-Cache-Cache RPM from EPEL since it isn't pulled in automatically as a dependancy
- create a special mysql user with the global permissions super and process, plus select on mysql.*
- configure munin-node as so in /etc/munin/plugins/munin-node:
[mysql*]
env.mysqluser munin
user root
Before this, I would get errors like
2011/09/29-00:00:12 [26340] Error output from mysql_commands:
2011/09/29-00:00:12 [26340] DBD::mysql::st execute failed: Access denied; you need the PROCESS privilege for this operation at /etc/munin/plugins/mysql_commands line 958.
2011/09/29-00:00:12 [26340] Service 'mysql_commands' exited with status 255/0.2011/09/29-21:05:11 [13346] Error output from mysql_qcache:
2011/09/29-21:05:11 [13346] DBD::mysql::st execute failed: Access denied; you need the SUPER privilege for this operation
at /etc/munin/plugins/mysql_qcache line 980.
2011/09/29-21:05:11 [13346] Service 'mysql_qcache' exited with status 255/0.
I still get errors about the replication plugin, but since I'm not doing replication, I don't really care.
Older
Unlike the source (linked below), my issue was that when I ran
munin-node-configure --suggest, I got the output:
mysql_bytes | no | [could not connect to mysql]
mysql_isam_space_ | no |
mysql_queries | no | [could not connect to mysql]
mysql_slowqueries | no | [could not connect to mysql]
mysql_threads | no | [could not connect to mysql]
This procedure worked for me.
- Create a MySQL user with a password that is NOT granted privilege to any DB. Simply create the user.
- In the file /etc/munin/plugin-conf.d/munin-node put this:
[mysql*]
user root
env.mysqladmin /usr/bin/mysqladmin
env.mysqlopts -u [MySQL_usr] -p[MySQL_usrpassword]
- Then Save. Where [MySQL_usr] is a valid MySQL user and [MySQL_usrpassword] is it’s password. Note that there is NO space between -p and the password. This is critical.
(
Source)