Openark’s mycheckpoint provides “lightweight, SQL oriented monitoring for MySQL”. It’s lightweight in that it’s easy to install (install from total ignorance took 20 minutes while waiting for a large operation to complete), doesn’t flog the systems it’s monitoring, and is pretty easy on the browser you use to view it too. It’s available for free here.
Following the install guide I installed in ubuntu by downloading the .deb file using lynx, then installing it from the command line:
$cat /etc/mycheckpoint.cnf sudo dpkg -i mycheckpoint-NNN-1.deb
where NNN is the version number in the name of the deb file. I then had to edit the default config file so that it could connect to the database.
[mycheckpoint] smtp_host = smtp.server.com smtp_from = email@example.com smtp_to = firstname.lastname@example.org monitored_port = 3306 purge_days = 60 [client] user=checkpoint password=pickledmonkeyfudge port=3306 host=127.0.0.1
I created the above /etc/mycheckpoint.cnf file using my text editor of choice. It defines the username and password used to connect as a correctly privileged MySQL user. You can see I follow the xkcd school of password strength. Creation of the user for mycheckpoint is pretty straight forward in MySQL.
CREATE DATABASE mycheckpoint; GRANT ALL PRIVILEGES ON mycheckpoint.* TO 'checkpoint'@'localhost' IDENTIFIED BY 'pickledmonkeyfudge'; GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO 'checkpoint'@'localhost'
If done correctly, then invoking the command “mycheckpoint” will use the data stored in /etc/mycheckpoint.cnf to log data into mycheckpoint’s tables. You can cron this, but first you probably want to also cron web access. Mycheckpoint can be used to field http requests, but rather than do that (the server already has apache on it) I think I’ll use cron to generate a html file every at the same time the data is logged.
Additions to my crontab:
*/5 * * * * mycheckpoint */5 * * * * mysql -ucheckpoint -ppickledmonkeyfudge mycheckpoint --silent --raw --execute="SELECT html FROM sv_report_html_brief" > /var/www/mysql/brief_report.html */5 * * * * mysql -ucheckpoint -ppickledmonkeyfudge mycheckpoint --silent --raw --execute="SELECT html FROM sv_report_html" > /var/www/mysql/report.html */5 * * * * mysql -ucheckpoint -ppickledmonkeyfudge mycheckpoint --silent --raw --execute="SELECT html FROM sv_report_html_24_7" > /var/www/mysql/report24_7.html
The above are dead simple mysql requests, mycheckpoint is set up so that each of these tables returns “html” that makes up a dynamically generated report. I’m saving the data into a protected folder accessible from /mysql/ on the database server.