mysql主库hang判断_mysqd实例服务hang住的检测思路及方案

对于mysql数据库架构为双主复制模式的不少技术朋友都非常困惑，如何准确判断mysqld服务是否能正常提供服务，以及能否自动判断并且进行主机的切换?同时，对mysqld服务的检测机制要求消耗资源少、判断简单且准确、开发和维护成本低等。我们在实际的生产环境检测过程中，也曾经犯过错误，为此写一篇短小的文章，把相关经验、思路、做法分享给大家，为更多的技术朋友起到答疑解惑。

要想做到自动切换提供数据库服务请求的主备服务器关键，就是要确定双主复制架构中的mysql数据库实例是否能正常提供服务请求，最让人头疼的就是mysqld服务出现hang住的情况。那么mysqld服务hang住的时候，会有哪些表象呢?先列出本人及圈内朋友们出现过的情况：

● 不能对数据库中的对象或数据执行修改性操作，但能正常执行查询操作;

● 能对系统数据库(备注：mysql、information_schema)的对象或数据进行查询操作，不能对非系统数据库的对象和数据;

● 只能对虚拟数据库(备注： information_schema)的对象及数据进行查询操作，不能对其他数据库的对象和数据;

● 不能对对任何数据库的对象或数据进行查询操作，但是能执行SHOW PROCESSLIST;

● 不能对对任何数据库的对象或数据进行查询操作，也不能执行SHOW PROCESSLIST，但是可以执行部分SHOW操作，例如：SHOW STATUS;

● 其他，还未发现的状态信息;

针对上述mysqld服务hang住的情况做一个分析及汇总，可以发现其有一些共同特征，总结如下：

● mysqld服务存在，且能ping或telnet;

● 能接受客户端发送过来的请求，但是不继续处理，而是停留在其发生hang住的当下SQL执行的状态;

● 若能执行SHOW PROCESSLIST的话，能看到所有的SQL执行状态停留不变;

● 数据库服务器的LOAD会突然下降，甚至LOAD下降为0，CPU、IO等都会接近没负荷状态;

● 若mysqld服务发生hang住的时候，一般都无法对数据库的对象或数据执行修改性质的操作;

文章开篇描述了mysqld服务hang住的时候，mysqld接受、处理服务请求的情况，以及数据库服务器的状态信息，既然可以发现这些特征，那么对于常用检测mysqld服务是否还活着或者网络是否通的办法：

● ping或telnet mysqld服务的端口;

● 通过执行SHOW 命令;

● 通过执行SELECT查询操作;

上述三类检测办法是否能真正做到准确检测呢?答案是：NO，只能准确监测到mysqld进程是否活着、程序与数据库服务器之间的网络是否畅通，对于mysqld服务能否正常接收和完成处理请求，就无法做到或者部分做到，综合上述分析信息，以及从目前我们将近三年实施效果看，对数据库中的数据进行修改操作，再配合程序对数据修改操作的判断逻辑是最稳妥的方法，详细步骤：

● 检测频率为：每隔10S，对当前提供服务的mysqld数据库实例上的检测表，做一次UPDATE操作，探测数据库实例是否正常提供服务;

● 若上一次数据库实例服务检测操作，没有正常返回更新信息，则每隔1S做一次数据库检测表的UPDATE操作，总共做2次探测;

● 若前两个步骤的数据库实例服务探测结束，当前提供服务的数据库实例服务都没恢复正常，则每隔5MS对数据库检测表再做一次UPDATE操作，总共检测三次，若还是没有正常返回信息，则认定此数据库实例服务不能正常接收服务请求;

用于执行数据库实例服务检测的表结构和UPDATE操作SQL为：

CREATETABLEmonitor_db(

IDSMALLINTUNSIGNEDNOTNULLAUTO_INCREMNET,

CreateDateTIMESTAMPNOTNULLDEFAULT'0000-00-00 00:00:00',PRIMARYKEY(ID)

)ENGINE=InnoDBCHARACTERSET'utf8'COLLATE'utf8_general_ci';INSERTINTOmonitor_dbVALUES(1,NOW()),(2,DATE_ADD(NOW(),INTERVAL-1DAY))

● MySQL5.0及以下版本的UPDATE操作SQL

UPDATE monitor_db SET CreateDate =NOW() WHERE ID=1;

● MySQL5.1及以上版本的UPDATE操作SQL

SET SESSION sql_log_bin=0;

UPDATE monitor_db SET CreateDate=NOW() WHERE ID=1;

备注：

对于支持MIXED、ROW复制模式的版本，必须规避MySQL双主复制过程中，可能出现主从执行更新操作SQL语句的被修改数据不一致的问题，从而导致复制中断，为此我们对数据库实例服务检测的更新操作不记录到二进制日志文件中，也即不会复制到其各自的从服务器。

另外，建议大家把monitor_db表创建到test数据库，或者类似test功能的数据库中，存储引擎建议一定要是：InnoDB，对于检测频率可以根据自己对数据安全性要求，而调整为自己能接受的。

若mysqld服务出现hang住的时候，正常关闭mysqld服务的办法都无效，只有对mysqld服务进程进行操作系统级别的kill -9 操作，然后再启动mysqld服务实例，等待其自动进行回滚操作结束，才算启动成功，建议大家别用mysql5.0.82及前后版本，存在一些BUG，很容易导致出现hang的情况。