mysql学习之运行监控

MySQLMonitoring一、如何有效监控?监控的目的二、基本监控解决方案(基础数据收集)三、高级解决方案(数据库健康状态，优化)四、MySQL性能相关交互式监控工具五、相关资源参考MySQLMonitoring一、如何有效监控?监控的目的?1、服务健康监测（存活，故障告警…..）2、监控系统采集数据，依据数据进行调优一、如何有效监控?二、基本监控解决方案(收集系统信息)top、vmstat、iostat、mpstat、mytop、dstata、free、/proc/….、mstat、mtop…….命令行工具分析系统资源使用状况.二、基本监控解决方案三、高级监控解决方案1、Nagios相关:(收集数据库信息及健康状态，对数据库调整优化)check_mysql[推荐]nagios-mysql-plugins-0.3[适当选择]check_mysql_health[重点介绍推荐]＊由于时间关系本PPT只重点介绍一下Nagios相关的插件脚本.重点介绍一下check_mysql_health监控数据库调优。2、MySQLActivityReport基于rrdtool**演示网站三、高级监控解决方案三、高级监控解决方案………3、CACTI*MySQL模板:、RRD参考资料:***~kas/mrtg-rrd/*、Munin*…………..三、高级监控解决方案#cd/usr/local/nagios/libexec/#./check_mysql–help//查看使用说明........Usage:check_mysql[-ddatabase][-Hhost][-Pport][-ssocket][-uuser][-ppassword][-S]Options:-h,--helpPrintdetailedhelpscreen-V,--versionPrintversioninformation-H,--hostname=ADDRESSHostname,IPAddress,orunixsocket(mustbeanabsolutepath)-P,--port=INTEGERPortnumber(default:3306)-s,--socket=STRINGUsethespecifiedsocket(hasnoeffectif-Hisused)-d,--database=STRINGCheckdatabasewithindicatedname-u,--username=STRINGConnectusingtheindicatedusername-p,--password=STRINGUsetheindicatedpasswordtoauthenticatetheconnection==IMPORTANT:THISFORMOFAUTHENTICATIONISNOTSECURE!!!==Yourclear-textpasswordcouldbevisibleasaprocesstableentry-S,--check-slave//检测Slave状态.Checkiftheslavethreadisrunningproperly.-w,--warningExitwithWARNINGstatusifslaveserverismorethanINTEGERsecondsbehindmaster-c,--criticalExitwithCRITICALstatusifslaveserverismorethenINTEGERsecondsbehindmasterNagioscheck_mysql示例：Nagios监控服务器:192.168.169.138如要监控DB服务器192.168.169.204和SlaveDB服务器192.168.169.123数据库授权：(登陆DB服务器，进行授权用户名netseek,密码linuxtone)mysqlgrantallprivileges-on*.*-tonetseek@192.168.169.138identifiedby'linuxtone';QueryOK,0rowsaffected(0.00sec)mysqlflushprivileges;QueryOK,0rowsaffected(0.00sec)Nagioscheck_mysql示例：………………………在监控机上.#cd/usr/local/nagios/libexec/连接204查看数据库状态:#./check_mysql-H192.168.169.204-unetseek-plinuxtoneUptime:2146510Threads:1Questions:61155591Slowqueries:325Opens:1273Flushtables:1Opentables:767Queriespersecondavg:28.491连接数据库123查看./check_mysql-H192.168.169.123-unetseek-plinuxtone-S-w60-c600Uptime:35349Threads:1Questions:4022Slowqueries:0Opens:38Flushtables:1Opentables:32Queriespersecondavg:0.114SlaveIO:YesSlaveSQL:YesSecondsBehindMaster:0OK,在命令行下都能正常连接数据库:Nagioscheck_mysql#vicommands.cfg添加如下：#check_mysqldefinecommand{command_namecheck_mysqlcommand_line$USER1$/check_mysql-H$ARG1$-P$ARG2$-u$ARG3$-p$ARG4$//仔细看参数传递与上面的命令行对应.}#check_slavedefinecommand{command_namecheck_slavecommand_line$USER1$/check_mysql-H$ARG1$-P$ARG2$-u$ARG3$-p$ARG4$-S-w$ARG5$-c$ARG6$}Check_mysqlNagios配置………#vish-wt-数据库检测:......在自己定的的配置文件里，添加如下服务段.#check_mysqldefineservice{host_namedbss-masterservice_descriptioncheck_mysqlcheck_commandcheck_mysql!192.168.169.122!3306!netseek!linuxtone........}#checkslavedefineservice{host_namedbss-slaveservice_descriptioncheck_slavecheck_commandcheck_slave!192.168.169.123!3306!netseek!linuxtone!60!600........}Check_mysqlNagios配置check_mysql监控演示效果Check_mysql–S模块可以很好的检测mysqlreplicationslave的健康状态.mysqlshowslavestauts\G*是否工作Slave_IO_running:YESSlave_SQL_running:YES*延迟情况Sencodes_behind_masterNagioscheck_mysql选择性的使用此插件来配合监控工作#wget*#mv*/usr/local/nagios/libexec插件解释:check_db_mysql.pl检测mysql是否运行.check_errorlog_mysql.pl检测数据库下的错误日志perf_mysql.pl收集性能数据,类似后面要讲的check_mysql_healthreplication相关.check_repl_mysql_cnt_slave_hosts.pl//检测复制相关。check_repl_mysql_hearbeat.plcheck_repl_mysql_io_thread.plcheck_repl_mysql_read_exec_pos.plcheck_repl_mysql_readonly.plcheck_repl_mysql_seconds_behind_master.plcheck_repl_mysql_sql_thread.pl-----------------------------------------------------------------nagios-mysql-plugins./check_db_mysql.pl-h192.168.169.204-unetseek-plinuxtone-port3306编写commanddefinecommand{command_namecheck_db_mysqlcommand_line$USER1$/check_db_mysql.pl-h$ARG1$-u$ARG2$-p$ARG3$-port$ARG4$................}服务端配置:.........check_commandcheck_db_mysql!192.168.169.204!netseek!linuxtone!3306...........nagios-mysql-plugins一、安装check_mysql_health官方网站:://=/usr/local/nagios--with-nagios-user=nagios\--with-nagios-group=nagios--with-perl\--with-statefiles-dir=/tmp#make&&makeinstall注:check_mysql_health(check_mysql_perf的替代方案，官方不再支持check_mysql_perf)详细参见: