MySQL监控主要指标及采集方法

MySQL监控属于DB监控的模块之一，包括采集、展示、监控告警。本文主要介绍MySQL监控的主要指标和采集方法。 MySQL监控和Redis监控的逻辑类似，可参考文章《Redis监控主要指标及采集方法 http://www.linuxidc.com/Linux/2016-11/136783.htm》。 DBA前台添加MySQL监控时系统会调用自动调度平台接口将Mysql监控的加密账户密码和ip端口等信息发送至目标，同时发送采集Agent。一、采集指标和命令 1、MySQL服务运行状态约定所有MySQL服务都必须以ip1（内网ip）来绑定，每个机器只有一个ip1，可以有多个端口，即多个MySQL Server。采集程序读取ip端口信息文件来判断server是否存在。sockParam=`ps aux | grep -P "mysqld.*--port=${port}" | grep -oP " --socket.*.sock"` # 空则获取不到该服务器端口mysql socket配置，请检查mysql配置是否正确
MYSQL="/usr/local/mysql/bin/mysql -hlocalhost --port=${port} ${sockParam} -u${user} -p${password} "
MYSQL_ADMIN="/usr/local/mysql/bin/mysqladmin -hlocalhost --port=${port} ${sockParam} -u${user} -p${password} "
curStatus=`${MYSQL} -e"show global status"` # 空则是获取不到该服务器mysql状态，请检查mysql是否正常运行
if [ -z "${curStatus}" ]
then
portExists=0
else
echo "${curStatus}" >> ${curFile}
portExists=1 2、连接数${MYSQL_ADMIN} processlist -v | wc -l 3、线程数grep "Threads_connected" ${curFile} | awk "{print $2}" 4、慢查询数grep "Slow_queries" ${curFile} | awk -F " " "{print $2}"需要计算两次的慢查询次数得到差值，等于最近1分钟的慢查询次数。上次数据保存在last.cache。 5、打开表数grep "Open_tables" ${curFile} | awk -F " " "{print $2}" 6、每秒执行select数grep "Com_select" ${curFile} | awk -F " " "{print $2}"需要计算两次的慢查询次数得到差值除以时间差，等于最近1分钟的执行数量。上次数据保存在last.cache。 7、每秒执行delete数grep "Com_delete" ${curFile} | grep -v "multi" | awk -F " " "{print $2}"需要计算两次的慢查询次数得到差值除以时间差，等于最近1分钟的执行数量。上次数据保存在last.cache。 8、每秒执行insert数grep "Com_insert" ${curFile} | grep -v "select" | awk -F " " "{print $2}"需要计算两次的慢查询次数得到差值除以时间差，等于最近1分钟的执行数量。上次数据保存在last.cache。 9、每秒执行update数grep "Com_update" ${curFile} | grep -v "multi" | awk -F " " "{print $2}"需要计算两次的慢查询次数得到差值除以时间差，等于最近1分钟的执行数量。上次数据保存在last.cache。 10、每秒钟执行replace数grep "Com_replace" ${curFile} | grep -v "select" | awk -F " " "{print $2}"需要计算两次的慢查询次数得到差值除以时间差，等于最近1分钟的执行数量。上次数据保存在last.cache。 11、每秒钟执行的 Innodb_rows_deletedgrep "Innodb_rows_deleted" ${curFile} | awk -F " " "{print $2}"需要计算两次的慢查询次数得到差值除以时间差，等于最近1分钟的执行数量。上次数据保存在last.cache。 12、每秒钟执行的 Innodb_rows_insertedgrep "Innodb_rows_inserted" ${curFile} | awk -F " " "{print $2}"需要计算两次的慢查询次数得到差值除以时间差，等于最近1分钟的执行数量。上次数据保存在last.cache。 13、每秒钟执行的 Innodb_rows_readgrep "Innodb_rows_read" ${curFile} | awk -F " " "{print $2}"需要计算两次的慢查询次数得到差值除以时间差，等于最近1分钟的执行数量。上次数据保存在last.cache。 14、每秒钟执行的 Innodb_rows_updatedgrep "Innodb_rows_updated" ${curFile} | awk -F " " "{print $2}"需要计算两次的慢查询次数得到差值除以时间差，等于最近1分钟的执行数量。上次数据保存在last.cache。 15、每秒钟执行的 innodb rows totalexpr ${innodbRowsDeletedPS} + ${innodbRowsInsertedPS} + ${innodbRowsReadPS} + ${innodbRowsUpdatedPS}等于前面四个Innodb_rows_*执行次数的总和 16、每秒处理命令数 qpsexpr ${mysqlSelectNumPS} + ${mysqlInsertNumPS} + ${mysqlUpdateNumPS} + ${mysqlDeleteNumPS} + ${mysqlReplaceNumPS}等于前面五个mysql命令Com_*的数量总和 17、每秒接收字节数 KByte/sgrep "Bytes_received" ${curFile} | awk -F " " "{print $2}"需要计算两次的慢查询次数得到差值除以时间差，等于最近1分钟的执行数量，除以1024得到单位KByte/s。上次数据保存在last.cache。 18、每秒发送字节数grep "Bytes_sent" ${curFile} | awk -F " " "{print $2}"需要计算两次的慢查询次数得到差值除以时间差，等于最近1分钟的执行数量，除以1024得到单位KByte/s。上次数据保存在last.cache。 19、可立即获得锁的次数grep "Table_locks_immediate" ${curFile} | awk -F " " "{print $2}"需要计算两次的慢查询次数得到差值，等于最近1分钟的可立即获得锁数量。上次数据保存在last.cache。 20、不可立即获得锁的次数grep "Table_locks_waited" ${curFile} | awk -F " " "{print $2}"需要计算两次的慢查询次数得到差值，等于最近1分钟的不可立即获得锁数量。上次数据保存在last.cache。 21、一行锁定需等待时间grep "Innodb_row_lock_waits" ${curFile} | awk -F " " "{print $2}"需要计算两次的慢查询次数得到差值，等于最近1分钟的一行锁定需等待时间。上次数据保存在last.cache。 22、当前脏页数grep "Innodb_buffer_pool_pages_dirty" ${curFile} | awk -F " " "{print $2}" 23、要求清空的缓冲池页数grep "Innodb_buffer_pool_pages_flushed" ${curFile} | awk -F " " "{print $2}"需要计算两次的慢查询次数得到差值，等于最近1分钟的要求清空的缓冲池页数。上次数据保存在last.cache。 24、Innodb 写入日志字节数 KBytegrep "Innodb_os_log_written" ${curFile} | awk -F " " "{print $2}"需要计算两次的慢查询次数得到差值，等于最近1分钟的写入日志字节数，除以1024得到KByte。上次数据保存在last.cache。 25、占用内存大小 MBytepid=`ps aux | grep "mysqld" | grep -Ev "safe|grep" | awk "{print $2}" `
mem=`cat /proc/${pid}/status | grep "VmRSS" | awk "{print $2}"`
mysqlMem=`echo "scale=2;${mem} / 1024" | bc`除以1024得到MByte 26、handler socket每秒处理数curHsTableLock=`grep "Hs_table_lock" ${curFile} | awk "{print $2}"`
preHsTableLock=`grep "Hs_table_lock" ${preFile} | awk "{print $2}"`
if [ -n "${curHsTableLock}" ]
then
hsQPS=`echo "scale=0;（${curHsTableLock} - ${preHsTableLock}） / ${intervalTime}" | bc`
else
hsQPS=0
fi 27、主从同步和状态#主从信息
#是否为从服务器
slave_running=`grep "Slave_running" ${curFile} | awk "{print $2}"`
if [ "${slave_running}A" = "ONA" ]
then
slaveRunning=1
slaveStatus=`${MYSQL} -e"show slave statusG"`
echo "${slaveStatus}" > ${slaveFile}

slaveIoRunning=`grep "Slave_IO_Running" ${slaveFile} | awk -F ":" "{print $2}"`
slaveSqlRunning=`grep "Slave_SQL_Running" ${slaveFile} | awk -F ":" "{print $2}"` if [ "${slaveIoRunning}A" == "NoA" -o "${slaveSqlRunning}A" == "NoA" ]
then
slaveRunning=3
fi

secondsBehindMaster=`grep "Seconds_Behind_Master" ${slaveFile} | awk -F ":" "{print $2}"`
if [ "${secondsBehindMaster}A" = "NULLA" ]
then
secondsBehindMaster=8888 # 表示主从不同步
fi #是从库时获取主库ip
master=`grep "Master_Host" ${slaveFile} | awk -F ":" "{print $2}"`
masterPort=`grep "Master_Port" ${slaveFile} | awk -F ":" "{print $2}"`
else
master=""
masterPort=""
slaveRunning=0
secondsBehindMaster=10000 # 不用检测
fi注：Seconds_Behind_Master，该值作为判断主从延时的指标，那么它又是怎么得到这个值的呢，同时，它为什么又受到很多人的质疑？Seconds_Behind_Master是通过比较sql_thread执行的event的timestamp和io_thread复制好的 event的timestamp（简写为ts）进行比较，而得到的这么一个差值。我们都知道的relay-log和主库的bin-log里面的内容完全一样，在记录sql语句的同时会被记录上当时的ts，所以比较参考的值来自于binlog，其实主从没有必要与NTP进行同步，也就是说无需保证主从时钟的一致。你也会发现，其实比较真正是发生在io_thread与sql_thread之间，而io_thread才真正与主库有关联，于是，问题就出来了，当主库I/O负载很大或是网络阻塞，io_thread不能及时复制binlog（没有中断，也在复制），而sql_thread一直都能跟上 io_thread的脚本，这时Seconds_Behind_Master的值是0，也就是我们认为的无延时，但是，实际上不是，你懂得。这也就是为什么大家要批判用这个参数来监控数据库是否发生延时不准的原因，但是这个值并不是总是不准，如果当io_thread与master网络很好的情况下，那么该值也是很有价值的。之前，提到 Seconds_Behind_Master这个参数会有负值出现，我们已经知道该值是io_thread的最近跟新的ts与sql_thread执行到的ts差值，前者始终是大于后者的，唯一的肯能就是某个event的ts发生了错误，比之前的小了，那么当这种情况发生时，负值出现就成为可能。 28、检测采集Agent心跳情况本文永久更新链接地址