mycheckpoint

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.

Demonstration Graph from mycheckpoint

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 = mycheckpoint@server.com
smtp_to = admin@server.com
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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s