Earlier I showed how to set up Munin on Dreamhost to monitor their VPS service (see: Monitor Dreamhost VPS performance, load, etc with munin). Among the available Munin plugin's is one to monitor MySQL. Out of the box it monitors the MySQL on the localhost, but if you have a Dreamhost MySQL VPS it's on a remote host and there are a few special considerations to take care of. The reason for doing this is Munin's data capture is much more comprehensive than the resources page which Dreamhost gives us.
Setting up the Munin MySQL plugin
The methods are adapted from an extensive Munin tutorial linked below.
When Munin is installed via apt-get (see: Monitor Dreamhost VPS performance, load, etc with munin) a default set of plugins are set up in /etc/munin/plugins and a larger set are in /usr/share/munin/plugins/. Setting up a plugin is largely these two steps:
- Add plugin configuration to /etc/munin/plugin-conf.d/munin-node
- Symbolic links from /etc/munin/plugins to the plugin files in /usr/share/munin/plugins/
The MySQL plugin is not set up by default, and its default configuration does not work for Dreamhosts' Debian.
First is to edit plugin-conf.d/munin-node with this configuration change:
env.mysqlopts --user=_____ -p______ --host=mysql.example.com
Here on Dreamhost the MySQL databases are stored on their own domain name. That domain name is up to you, and use whatever you've chosen. The --user= and -p options have to be the same as the one configured for your databases. The official tutorial says to configure a different user with limited access, but I found that to not work.
The env.mysqlopts value is passed to various MySQL commands (like mysqladmin or mysqlshow) and the format of the options are described on the relevant manpages.
The next step is to link the plugins into the plugin directory. You'll have to set up an admin user for your VPS, and perform these commands while su'd.
[ps____]# cd plugins/
[ps____]# ln -s /usr/share/munin/plugins/mysql_bytes mysql_bytes
[ps____]# ln -s /usr/share/munin/plugins/mysql_isam_space_ mysql_mysql_isam_space_
[ps____]# ln -s /usr/share/munin/plugins/mysql_queries mysql_queries
[ps____]# ln -s /usr/share/munin/plugins/mysql_slowqueries mysql_slowqueries
[ps____]# ln -s /usr/share/munin/plugins/mysql_threads mysql_threads
[ps____]# /etc/init.d/munin-node restart
Stopping Munin-Node: done.
Starting Munin-Node: done.