1、MySQL Monitoring,ChinaUnix網(wǎng)友技術(shù)交流,MySQL Monitoring,一、如何有效監(jiān)控?監(jiān)控的目的 二、基本監(jiān)控解決方案(基礎(chǔ)數(shù)據(jù)收集) 三、高級(jí)解決方案(數(shù)據(jù)庫(kù)健康狀態(tài)，優(yōu)化) 四、MySQL性能相關(guān)交互式監(jiān)控工具 五、相關(guān)資源參考,一 、如何有效監(jiān)控?,一、如何有效監(jiān)控?監(jiān)控的目的? 1 、服務(wù)健康監(jiān)測(cè)（存活，故障告警.） 2 、監(jiān)控系統(tǒng)采集數(shù)據(jù)，依據(jù)數(shù)據(jù)進(jìn)行調(diào)優(yōu),二、 基本監(jiān)控解決方案,二、基本監(jiān)控解決方案(收集系統(tǒng)信息) top 、vmstat 、iostat 、mpstat 、mytop 、dstata 、free 、/proc/. 、mstat 、mt

2、op .命令行工具分析系統(tǒng)資源使用狀況.,三、高級(jí)監(jiān)控解決方案,三、高級(jí)監(jiān)控解決方案 1、Nagios相關(guān):(收集數(shù)據(jù)庫(kù)信息及健康狀態(tài)，對(duì)數(shù)據(jù)庫(kù)調(diào)整優(yōu)化) check_mysql 推薦 nagios-mysql-plugins-0.3 適當(dāng)選擇 check_mysql_health 重點(diǎn)介紹推薦 由于時(shí)間關(guān)系本PPT只重點(diǎn)介紹一下Nagios相關(guān)的插件腳本. 重點(diǎn)介紹一下check_mysql_health監(jiān)控?cái)?shù)據(jù)庫(kù)調(diào)優(yōu)。 2、MySQL Activity Report 基于rrdtool *http:/gert.sos.be/en/projects/mysqlar/ *http:/gert.

3、sos.be/demo/mysqlar/ 演示網(wǎng)站,三、高級(jí)監(jiān)控解決方案,三、高級(jí)監(jiān)控解決方案 3、CACTI * MySQL模板: 4、RRD 參考資料: * http:/vvv.k6p.de/rrd/ * http:/oss.oetiker.ch/rrdtool/rrdworld/ * http:/www.fi.muni.cz/kas/mrtg-rrd/ * /internet/manage/rrdutil/faq/mysql.shtml 5、Munin * jects.linpro.no/ .,Nagios check_m

4、ysql,#cd /usr/local/nagios/libexec/ # ./check_mysql help /查看使用說(shuō)明 . Usage: check_mysql -d database -H host -P port -s socket -u user -p password -S Options: -h, -help Print detailed help screen -V, -version Print version information -H, -hostname=ADDRESS Host name, IP Address, or unix socket (must be

5、 an absolute path) -P, -port=INTEGER Port number (default: 3306) -s, -socket=STRING Use the specified socket (has no effect if -H is used) -d, -database=STRING Check database with indicated name -u, -username=STRING Connect using the indicated username -p, -password=STRING Use the indicated password

6、 to authenticate the connection = IMPORTANT: THIS FORM OF AUTHENTICATION IS NOT SECURE! = Your clear-text password could be visible as a process table entry -S, -check-slave /檢測(cè)Slave狀態(tài). Check if the slave thread is running properly. -w, -warning Exit with WARNING status if slave server is more than

7、INTEGER seconds behind master -c, -critical Exit with CRITICAL status if slave server is more then INTEGER seconds behind master,Nagios check_mysql,示例： Nagios 監(jiān)控服務(wù)器:38 如要監(jiān)控DB服務(wù)器04和Slave DB服務(wù)器23 數(shù)據(jù)庫(kù)授權(quán)：(登陸DB服務(wù)器，進(jìn)行授權(quán)用戶名netseek,密碼linuxtone) mysql grant all privileg

8、es - on *.* - to netseek38 identified by linuxtone; Query OK, 0 rows affected (0.00 sec) mysql flush privileges; Query OK, 0 rows affected (0.00 sec),Nagios check_mysql,示例： 在監(jiān)控機(jī)上. #cd /usr/local/nagios/libexec/ 連接204查看數(shù)據(jù)庫(kù)狀態(tài): #./check_mysql -H 04 -u netseek -p linuxtone Upti

9、me: 2146510 Threads: 1 Questions: 61155591 Slow queries: 325 Opens: 1273 Flush tables: 1 Open tables: 767 Queries per second avg: 28.491 連接數(shù)據(jù)庫(kù)123查看 ./check_mysql -H 23 -u netseek -p linuxtone -S -w 60 -c 600 Uptime: 35349 Threads: 1 Questions: 4022 Slow queries: 0 Opens: 38 Flush tables

10、: 1 Open tables: 32 Queries per second avg: 0.114 Slave IO: Yes Slave SQL: Yes Seconds Behind Master: 0 OK,在命令行下都能正常連接數(shù)據(jù)庫(kù):,Check_mysql Nagios配置,# vi commands.cfg 添加如下： #check_mysql define command command_name check_mysql command_line $USER1$/check_mysql -H $ARG1$ -P $ARG2$ -u $ARG3$ -p $ARG4$ /仔細(xì)看參數(shù)

11、傳遞與上面的命令行對(duì)應(yīng). #check_slave define command command_name check_slave command_line $USER1$/check_mysql -H $ARG1$ -P $ARG2$ -u $ARG3$ -p $ARG4$ -S -w $ARG5$ -c $ARG6$ ,Check_mysql Nagios配置, #vi sh-wt-www-db.cfg 給上海網(wǎng)通兩臺(tái)DB服務(wù)器配置上mysql數(shù)據(jù)庫(kù)檢測(cè): . 在自己定的的配置文件里，添加如下服務(wù)段. #check_mysql define service host_name dbss-m

12、aster service_description check_mysql check_command check_mysql!22!3306!netseek!linuxtone . #check slave define service host_name dbss-slave service_description check_slave check_command check_slave!23!3306!netseek!linuxtone!60!600 . ,Nagios check_mysql,check_mysql監(jiān)控演示效果 Ch

13、eck_mysql S 模塊可以很好的檢測(cè)mysql replication slave的健康狀態(tài). mysqlshow slave stautsG *是否工作 Slave_IO_running: YES Slave_SQL_running: YES *延遲情況 Sencodes_behind_master,nagios-mysql-plugins,選擇性的使用此插件來(lái)配合監(jiān)控工作 # wget http:/www.shinguz.ch/MySQL/nagios-mysql-plugins-0.3.tar.gz # tar zxvf nagios-mysql-plugins-0.3.tar.g

14、z # cd nagios-mysql-plugins-0.3 # chmod a+x * # mv * /usr/local/nagios/libexec 插件解釋: check_db_mysql.pl 檢測(cè)mysql是否運(yùn)行. check_errorlog_mysql.pl 檢測(cè)數(shù)據(jù)庫(kù)下的錯(cuò)誤日志 perf_mysql.pl 收集性能數(shù)據(jù),類似后面要講的check_mysql_health replication相關(guān). check_repl_mysql_cnt_slave_hosts.pl /檢測(cè)復(fù)制相關(guān)。 check_repl_mysql_hearbeat.pl check_repl_m

15、ysql_io_thread.pl check_repl_mysql_read_exec_pos.pl check_repl_mysql_readonly.pl check_repl_mysql_seconds_behind_master.pl check_repl_mysql_sql_thread.pl -,nagios-mysql-plugins,./check_db_mysql.pl -h 04 -u netseek -p linuxtone -port 3306 編寫(xiě)command define command command_name check_db_my

16、sql command_line $USER1$/check_db_mysql.pl -h $ARG1$ -u $ARG2$ -p $ARG3$ -port $ARG4$ . 服務(wù)端配置: . check_command check_db_mysql!04!netseek!linuxtone!3306 .,check_mysql_health,一、安裝check_mysql_health 官方網(wǎng)站:http:/www.consol.de/opensource/nagios/check-mysql-health/ # wget http:/www.consol.de/f

17、ileadmin/opensource/Nagios/check_mysql_health-2.0.3.tar.gz # tar zxvf check_mysql_health-2.0.3.tar.gz # cd check_mysql_health-2.0.3 # ./configure -prefix=/usr/local/nagios -with-nagios-user=nagios - with-nagios-group=nagios -with-perl -with-statefiles-dir=/tmp # make Query OK, 0 rows affected (0.00

18、sec) mysql flush privileges; Query OK, 0 rows affected (0.00 sec) OK - 2 client connection threads | threads_connected=2;10;20,check_mysql_health,三、示例： 在監(jiān)控機(jī)上. #cd /usr/local/nagios/libexec/ 連接123查看數(shù)據(jù)庫(kù)查詢線程緩存命中率狀態(tài): # ./check_mysql_health -hostname 23 -port 3306 -username netseek -password

19、 linuxtone -mode threadcache-hitrate OK - thread cache hitrate 91.30% | thread_cache_hitrate=91.30%;90:;80: thread_cache_hitrate_now=91.30% connections_per_sec=0.00 90: 表示小于90 warning, 80 表示小于80 則critical 查詢命中率 # ./check_mysql_health -hostname 22 -port 3306 -username netseek -password l

20、inuxtone -mode qcache-hitrate WARNING - query cache hitrate 88.19% | qcache_hitrate=88.19%;90:;80: qcache_hitrate_now=78.57% selects_per_sec=0.75,check_mysql_health,三、示例： 在監(jiān)控機(jī)上. 調(diào)整告警閥值: # ./check_mysql_health -hostname 22 -port 3306 -username netseek -password linuxtone -w 80: -c 70: -m

21、ode qcache-hitrate OK - query cache hitrate 88.19% | qcache_hitrate=88.19%;80:;70: qcache_hitrate_now=85.63% selects_per_sec=0.52 連接123數(shù)據(jù)庫(kù)查看鎖表率. # ./check_mysql_health -hostname 23 -port 3306 -username netseek -password linuxtone -mode table-lock-contention OK - table lock contention 0.

22、00% | tablelock_contention=0.00%;1;2 tablelock_contention_now=0.00% 連接123數(shù)據(jù)庫(kù)查看數(shù)據(jù)庫(kù)服務(wù)器當(dāng)前連接數(shù)量: # ./check_mysql_health -hostname 23 -port 3306 -username netseek -password linuxtone -mode threads-connected OK - 2 client connection threads | threads_connected=2;10;20,check_mysql_health,Nagios

23、相關(guān)配置 # vi commands.cfg 添加如下： #check_health_mysql define command command_name check_mysql_health command_line $USER1$/check_mysql_health -hostname $ARG1$ -port $ARG2$ -username $ARG3$ -password $ARG4$ -mode $ARG5$ #vi sh-wt-www-db.cfg 給上海網(wǎng)通兩臺(tái)DB服務(wù)器配置上mysql數(shù)據(jù)庫(kù)檢測(cè): . 在自己定的的配置文件里，添加如下服務(wù)段. define service host_name dbss-slave service_description threads-connected check_command check_mysql_health!23!3306!netseek!linuxtone!threads-connected,check_mysql_health,. define service host_name dbss-slave service_descrip