mysql 监控

mysql监控

前几天跟几个人讨论到MySQL监控这块。发现自身有所欠缺，所以最近加班加点研究了一下。写个博文总结一下。

一般来说对于mysql监控，会监控主库、从库的可用性。主从复制状态监控、数据库性能监控、数据库日志监控。

对于可用性的监控，可以通过定期去执行sql实现。这里需要注意的是主库需要监控可写入性。

数据库性能的监控，就是去监控数据库的一些状态值，对这些状态进行直接、间接（不同状态值计算）的监控。这个需要对mysql的一些参数有详细的了解。

重点来说说mysql复制监控这块:

很多人都知道可以通过监控show slave status\G命令输出的Seconds_Behind_Master参数的值来判断，是否有发生主从延时。

同时很多人也知道这个不准确，但是为什么不准确呢？？？？

首先来看看show slave status\G重点项：

• Slave_IO_Running：该参数可作为io_thread的监控项，Yes表示io_thread的和主库连接正常并能实施复制工作，No则说明与主库通讯异常，多数情况是由主从间网络引起的问题；
• Slave_SQL_Running：该参数代表sql_thread是否正常，YES表示正常，NO表示执行失败，具体就是语句是否执行通过，常会遇到主键重复或是某个表不存在。
• Seconds_Behind_Master：从库落后主库的时间；

1. NULL—表示io_thread或是sql_thread有任何一个发生故障，也就是该线程的Running状态是No，而非Yes。
2.  0 — 表示主从复制良好。
3.  正值 — 表示主从已经出现延时，数字越大表示从库落后主库越多。

Seconds_Behind_Master计算方式：

是通过比较sql_thread执行的event的timestamp和io_thread复制好的event的timestamp(简写为ts)进行比较，而得到的这么一个差值。

Seconds_Behind_Master的计算方式可能带来的问题：
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发生了错误，比之前的小了，那么当这种情况发生时，负值出现就成为可能。

所以简单点说，当IO线程有延迟的时候Seconds_Behind_Master这个值是不准的。

那么如何正确查看以及监控MySQL主从？

1、根据主从POS点比较

2、在主库建立监控表（心跳），定时写入（带时间戳）。去从库中查询对应的记录，就可以依据记录内的时间戳信息来确认延时了多少。

第二种方式单独细说。

详细说下第一种：

先说下主从查看延迟的流程。

1. 首先看 Relay_Master_Log_File 和 Master_Log_File 是否有差异；
2. 如果Relay_Master_Log_File 和 Master_Log_File 是一样的话，再来看Exec_Master_Log_Pos 和 Read_Master_Log_Pos 的差异，对比SQL线程比IO线程慢了多少个binlog事件；
3. 如果Relay_Master_Log_File 和 Master_Log_File 不一样，那说明延迟可能较大，需要从MASTER上取得binlog status，判断当前的binlog和MASTER上的差距；

在监控节点上，对MASTER和slave同时发起SHOW BINARY LOGS和SHOW slave STATUS\G的请求，最后判断二者binlog的差异，以及 Exec_Master_Log_Pos 和Read_Master_Log_Pos 的差异。

例如：
在MASTER上执行SHOW BINARY LOGS 的结果是：
+------------------+--------------+
| Log_name | File_size |
+------------------+--------------+
| mysql-bin.000009 | 1073742063 |
| mysql-bin.000010 | 107374193 |
+------------------+--------------+
而在slave上执行SHOW slave STATUS\G 的结果是：

Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 668711237
Relay_Master_Log_File: mysql-bin.000009
slave_IO_Running: Yes
slave_SQL_Running: Yes
***
Exec_Master_Log_Pos: 654409041

***
Seconds_Behind_Master: 3296
***
这时候，slave实际的延迟应该是：
mysql-bin.000009 这个binlog中的binlog position 1073742063 和 slave上读取到的binlog position之间的差异延迟，即：
1073742063 - 654409041 = 419333022 个binlog event
并且还要加上 mysql-bin.000010这个binlog已经产生的107374193个binlog event，共
107374193 + 419333022 = 526707215 个binlog event

   

附：

show slave status 参数详解 

 

Slave_IO_State：等待 master 发生事件

Master_Host：当前的主服务器主机
Master_User：被用于连接主服务器的当前用户
Master_Port：当前的主服务器接口
Connect_Retry：master-connect-retry选项的当前值
Master_Log_File：I/O线程当前正在读取的主服务器二进制日志文件的名称
Read_Master_Log_Pos：在当前的主服务器二进制日志中，I/O线程已经读取的位置
Relay_Log_File：SQL线程当前正在读取和执行的中继日志文件的名称
Relay_Log_Pos：在当前的中继日志中，SQL线程已读取和执行的位置
Relay_Master_Log_File：由SQL线程执行的包含多数近期事件的主服务器二进制日志文件的名称
Slave_IO_Running：I/O线程是否被启动并成功地连接到主服务器上
Slave_SQL_Running：SQL线程是否被启动
Replicate_Do_DB：replicate-do-db选项的当前值
Replicate_Ignore_DB：replicate-ignore-db选项的当前值
Replicate_Do_Table：replicate-do-table选项的当前值
Replicate_Ignore_Table：replicate-ignore-table选项的当前值
Replicate_Wild_Do_Table：replicate-wild-do-table选项的当前值
Replicate_Wild_Ignore_Table：replicate-wild-ignore_table选项的当前值
Last_Errno：最近一次错误码
Last_Error：最近一次错误内容
Skip_Counter：最近被使用的用于SQL_SLAVE_SKIP_COUNTER的值
Exec_Master_Log_Pos：来自主服务器的二进制日志的由SQL线程执行的上一个时间的位置（Relay_Master_Log_File）。在主服务器的二进制日志中的(Relay_Master_Log_File,Exec_Master_Log_Pos)对应于在中继日志中的(Relay_Log_File,Relay_Log_Pos)
Relay_Log_Space：所有原有的中继日志结合起来的总大小
Until_Condition：如果没有指定UNTIL子句，则没有值。如果从属服务器正在读取，直到达到主服务器的二进制日志的给定位置为止，则值为Master。如果从属服务器正在读取，直到达到其中继日志的给定位置为止，则值为Relay
Until_Log_File：用于指示日志文件名，日志文件名和位置值定义了SQL线程在哪个点中止执行
Until_Log_Pos：用于指示日志位置值，日志文件名和位置值定义了SQL线程在哪个点中止执行
Master_SSL_Allowed：如果允许对主服务器进行SSL连接，则值为Yes。如果不允许对主服务器进行SSL连接，则值为No。如果允许SSL连接，但是从属服务器没有让SSL支持被启用，则值为Ignored。
Master_SSL_CA_File：master-ca选项的当前值
Master_SSL_CA_Path：master-capath选项的当前值
Master_SSL_Cert：master-cert选项的当前值
Master_SSL_Cipher：master-cipher选项的当前值
Master_SSL_Key：master-key选项的当前值
Seconds_Behind_Master：本字段是从属服务器“落后”多少的一个指示。当从属SQL线程正在运行时（处理更新），本字段为在主服务器上由此线程执行的最近的一个事件的时间标记开始，已经过的秒数。当此线程被从属服务器I/O线程赶上，并进入闲置状态，等待来自I/O线程的更多的事件时，本字段为零。总之，本字段测量从属服务器SQL线程和从属服务器I/O线程之间的时间差距，单位以秒计。如果主服务器和从属服务器之间的网络连接较快，则从属服务器I/O线程会非常接近主服务器，所以本字段能够十分近似地指示，从属服务器SQL线程比主服务器落后多少。如果网络较慢，则这种指示不准确；从属SQL线程经常会赶上读取速度较慢地从属服务器I/O线程，因此，Seconds_Behind_Master经常显示值为0。即使I/O线程落后于主服务器时，也是如此。换句话说，本列只对速度快的网络有用。即使主服务器和从属服务器不具有相同的时钟，时间差计算也会起作用（当从属服务器I/O线程启动时，计算时间差。并假定从此时以后，时间差保持不变）。如果从属SQL线程不运行，或者如果从属服务器I/O线程不运行或未与主服务器连接，则Seconds_Behind_Master为NULL（意义为“未知”）。举例说明，如果在重新连接之前，从属服务器I/O线程休眠了master-connect-retry秒，则显示NULL，因为从属服务器不知道主服务器正在做什么，也不能有把握地说落后多少。本字段有一个限制。时间标记通过复制被保留，这意味着，如果一个主服务器M1本身是一个从属服务器M0，则来自M1的binlog的任何事件（通过复制来自M0的binlog的事件而产生），与原事件具有相同的时间标记。这可以使MySQL成功地复制TIMESTAMP。但是，Seconds_Behind_Master的缺点是，如果M1也收到来自客户端的直接更新，则值会随机变化，因为有时最近的M1时间来自M0，有时来自直接更新，最近的时间标记也是如此。