mycheckpoint 把玩手记
cacti也可以监控mysql的一些性能参数画出图,但是无法自定义时间或者其他条件产生报表.mycheckpoint 可以作为替代监控mysql性能的工具,并且安装使用都非常简单.图标也比cacti漂亮..
需要的环境. python 2.4 以上 MySQL-python 库,其他就不需要了.
安装包和库
ez_setup.py MYSQL-python
tar xf mycheckpoint-xx.tar.gz
cd mycheckpoint-xx
python setup.py build
python setup.py install
配置:(此处监控2个主机,一个是主一个是从,已经建立同步关系.同步所有数据库)
建立监控环境:
on master：
mysql>create database checke_slave;
mysql>create database checke_master;
mysql> grant all privileges on check_slave.* to checker@'192.168.%.%' identified by '123';flush privileges;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> grant all privileges on check_master.* to checker@'192.168.%.%' identified by '123';flush privileges;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
初始化监控的库和表
on master:
授权监控账号：
mysql> grant replication slave,replication client,super,process on *.* to checker@'192.168.%.%';
建立配置文件(可选项,可以将命令行里的参数写在这个配置文件里.)
/etc/mycheckpoint.cnf
[mycheckpoint]
smtp_host = mail.my-server-company.com
smtp_from = monitor@my-server-company.com
smtp_to = dba@my-server-company.com
monitored_port = 3306
purge_days = 60
#... Any command line option can go here; replace dash ("-") with underscored ("_")
[client]
user=dbmonitor
password=123456
socket=/tmp/mysql.sock
port=3306
host=localhost
*/5 * * * * mycheckpoint --host=192.168.128.129 -u checker -p 123 -S /tmp/mysql.sock --database=check_master
*/5 * * * * mycheckpoint --monitored-host=192.168.128.128 --monitored-user=checker --monitored-password=123 -S /tmp/mysql.sock --monitored-port=3306 --database=check_slave
mycheckpoint --host=192.168.128.129 -u checker -p 123 -S /tmp/mysql.sock --database=check_master http
mysql>create database checke_master;
mysql> grant all privileges on check_slave.* to checker@'192.168.%.%' identified by '123';flush privileges;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> grant all privileges on check_master.* to checker@'192.168.%.%' identified by '123';flush privileges;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
初始化监控的库和表
on master:
mycheckpoint --host=192.168.128.129 -u checker -p 123 -S /tmp/mysql.sock --database=check_master deploy
mycheckpoint --monitored-host=192.168.128.128 --monitored-user=checker --monitored-password=123 -S /tmp/mysql.sock --monitored-port=3306 --database=check_slave deploy
mycheckpoint --monitored-host=192.168.128.128 --monitored-user=checker --monitored-password=123 -S /tmp/mysql.sock --monitored-port=3306 --database=check_slave deploy
授权监控账号：
mysql> grant replication slave,replication client,super,process on *.* to checker@'192.168.%.%';
建立配置文件(可选项,可以将命令行里的参数写在这个配置文件里.)
/etc/mycheckpoint.cnf
[mycheckpoint]
smtp_host = mail.my-server-company.com
smtp_from = monitor@my-server-company.com
smtp_to = dba@my-server-company.com
monitored_port = 3306
purge_days = 60
#... Any command line option can go here; replace dash ("-") with underscored ("_")
[client]
user=dbmonitor
password=123456
socket=/tmp/mysql.sock
port=3306
host=localhost
开始抓取数据.(加入cron里,5分钟一次)
on master:
*/5 * * * * mycheckpoint --host=192.168.128.129 -u checker -p 123 -S /tmp/mysql.sock --database=check_master
*/5 * * * * mycheckpoint --monitored-host=192.168.128.128 --monitored-user=checker --monitored-password=123 -S /tmp/mysql.sock --monitored-port=3306 --database=check_slave
开启展示数据的http server 默认端口为12306 可以用 --http-port 另外指定.
mycheckpoint --host=192.168.128.129 -u checker -p 123 -S /tmp/mysql.sock --database=check_master http
测试访问,(有可能前一个小时左右没有图出来.)
http://192.168.128.129:12306/check_master#Resources
用sql产生自定义报表
mysql> select DML from sv_report_chart_hour ; （按小时的画出的数据库执行DML语句的数据图）
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| DML |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| http://chart.apis.google.com/chart?cht=lc&chs=370x180&chts=303030,12&chtt=Latest+1+days:+Sep+14,+11:00++-++Sep+15,+17:00&chf=c,s,ffffff&chdl=com_select_psec|com_insert_psec|com_delete_psec|com_update_psec|com_replace_psec&chdlp=b&chco=ff8c00,4682b4,9acd32,dc143c,9932cc&chd=s:QQQRQRQQ9oQQQQQQQQQQQQQQQQQhbRR,LLLLLLLLLLLLLLLLLLLLLLLLLLLQMMM,LLLLLLLLLLLLLLLLLLLLLLLLLLLMMMM,BBBBBBBBBBBBBBBBBBBBBBBBBBBHCCC,AAAAAAAAAAAAAAAAAAAAAAAAAAADAAA&chxt=x,y&chxr=1,0,8.22&chxl=0:|15th|&chxs=0,505050,10,0,lt&chg=80.00,25,1,2,43.33,0&chxp=0,43.33 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (1.48 sec)
生成html报表:
mysql -uroot -pxxx checker_master -e "SELECT html FROM sv_report_html_brief" --silent --raw > /tmp/mycheckpoint_report.html
