Here’s a quick HOWTO for mycheckpoint. It explains very briefly how to:

• Download
• Install
• Setup
• Create a defaults file
• Monitor
• Generate human reports
• Generate HTML reports
• Setup alert conditions
• View pending alerts
• Setup & monitor custom queries
• Skip alerts
• Skip email notifications
• Report a bug

Download

Get mycheckpoint from the Google Code mycheckpoint project’s homepage. Look to the right.

Download either:

• deb package (Debian, Ubuntu, etc.)
• tar.gz package (python installer)
• python script file

Read more…

Install

deb package:

sudo dpkg -i mycheckpoint-NNN-1.deb

Python installer:

tar xzfv mycheckpoint-NNN.tar.gz
cd mycheckpoint-NNN
sudo python setup.py install

Python script:

chmod +x mycheckpoint-NNN
mv mycheckpoint-NNN /usr/bin/mycheckpoint

mycheckpoint requires the MySQL-Python driver. On Debian/Ubuntu:

sudo apt-get install python-mysqldb

On RedHat/CentOS:

sudo yum install MySQL-python

Read more…

Setup

Each monitored node needs a schema (database) of its own. mycheckpoint must have all privileges on that schema:

CREATE DATABASE mycheckpoint;
GRANT ALL PRIVILEGES ON mycheckpoint_host1.* TO 'mycheckpoint_user'@'localhost' IDENTIFIED BY 'some_password';

To allow mycheckpoint to monitor master status, rpelication status and processlist, also provide with:

GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO 'mychckpoint_user'@'localhost'

Create a defaults file

Create a file in the following format:

[mycheckpoint]
smtp_host = mail.my-server-company.com
smtp_from = monitor@my-server-company.com
smtp_to = dba@my-server-company.com

[client]
host=localhost
socket=/tmp/mysql.sock
user=root
password=
port=3306

The [client] section follows standard MySQL notation.

If not instructed otherwise, mycheckpoint will look for /etc/mycheckpoint.cnf. Or you can place it anywhere you like and use the –defaults-file option.

Read more…

Monitor

Use full connection specification:

mycheckpoint --host=127.0.0.1 --port=3306 --user=mcp_user --password=123456 --database=mycheckpoint_host1

Use defaults file:

mycheckpoint --defaults-file=/home/admin/.mycheckpoint.cnf

Write down /etc/mycheckpoint.cnf; run:

mycheckpoint

Monitor local host, write to central server:

mycheckpoint --host=centralserver --port=3306 --user=mcp_user --password=123456 --database=mycheckpoint_host1 --monitored-host=localhost --monitored-socket=/tmp/mysql.sock

Monitor remote hosts

mycheckpoint --host=myhost --port=3306 --user=mcp_user --password=123456 --database=mycheckpoint_host1 --monitored-host=host1 --monitored-port=3306

Read more… [1], [2]

Generate human reports

Generate latest report:

mysql> SELECT report FROM sv_report_human_sample ORDER BY id DESC LIMIT 1\G

Show hourly report in given time range:

mysql> SELECT report FROM sv_report_human_hour WHERE ts BETWEEN '2010-03-17 10:00:00' AND '2010-03-17 14:00:00';

Read more…

Generate HTML reports

From within MySQL

mysql> SELECT html FROM sv_report_html_brief;
mysql> SELECT html FROM sv_report_html;
mysql> SELECT html FROM sv_report_html_24_7;
mysql> SELECT html FROM sv_custom_html_brief;

Using mysql command line client (you may need to supply your mysql login/password to execute):

mysql mycheckpoint --silent --raw --execute="SELECT html FROM sv_report_html_brief" > /tmp/mycheckpoint_brief_report.html
firefox /tmp/mycheckpoint_brief_report.html

mysql mycheckpoint --silent --raw --execute="SELECT html FROM sv_report_html_brief" > /var/www/mycheckpoint_brief_report.html

Sending by email:

mycheckpoint --defaults-file=/etc/mycheckpoint.cnf email_brief_report

or

mycheckpoint --defaults-file=/etc/mycheckpoint.cnf --smtp-from=monitor@my-server-company.com --smtp-to=dba@my-server-company.com --smtp-host=mail.my-server-company.com email_brief_report

Read more…

Setup alert conditions

mysql> INSERT INTO alert_condition (condition_eval, description, alert_delay_minutes)
         VALUES ('seconds_behind_master > 60', 'Slave lags too far behind', 10);

mysql> INSERT INTO alert_condition (condition_eval, description, alert_delay_minutes)
         VALUES ('(seconds_behind_master > 300) AND (HOUR(ts) NOT BETWEEN 2 AND 4)', 'Slave lags too far behind', 0);

mysql> INSERT INTO alert_condition (condition_eval, description, alert_delay_minutes)
         VALUES ('os_datadir_mountpoint_usage_percent > 90', 'datadir mount point is over 90%', 30);

Read more…

View pending alerts

mysql> SELECT * FROM alert_pending_view;

Or, generate HTML report:

mysql> SELECT html FROM alert_pending_html_view \G

Same, from command line:

mysql --defaults-file=/etc/mycheckpoint.cnf --silent --raw --execute="SELECT html FROM alert_pending_html_view" > /tmp/mycheckpoint_pending_alerts.html && firefox /tmp/mycheckpoint_pending_alerts.html

Read more…

Setup & monitor custom queries

Create a new custom query:

INSERT INTO
 custom_query (custom_query_id, enabled, query_eval, description, chart_type, chart_order)
 VALUES (0, 1, 'SELECT COUNT(*) FROM store.shopping_cart WHERE is_pending=1', 'Number of pending carts', 'value', 0);

Read custom values:

SELECT id, ts, created_tmp_tables_psec, custom_0, custom_0_time, custom_1_psec FROM sv_sample WHERE ts >= NOW() - INTERVAL 1 HOUR;

Get chart for custom values:

SELECT custom_1_psec FROM sv_report_chart_sample\G

Generate HTML report for custom values:

SELECT html FROM sv_custom_html_brief;
SELECT html FROM sv_custom_html;

Read more…

Skip alerts

Execute:

 mycheckpoint --skip-alerts

Skip email notifications

Execute:

 mycheckpoint --skip-emails

Report a bug

mycheckpoint is hosted on Google Code. Please report bugs on the Issues page.