【Mysql】Mysql 从库每隔一段时间就重启

最新推荐文章于 2025-01-07 17:22:27 发布

转载最新推荐文章于 2025-01-07 17:22:27 发布 · 558 阅读

文章标签：

本文记录了一次MySQL从库复制进程异常停止的问题排查过程。通过zabbix告警和错误日志发现，问题源于服务重启及配置项skip_slave_start的影响，并最终定位到由于主机内存不足触发了OOM killer机制导致MySQL服务被终止。

背景： HuaYun_Interface-SLAVE从库每隔一段时间slave复制进程便停掉，zabbix发送告警短信

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: *****
Master_User: repl
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mysql-bin.000195
Read_Master_Log_Pos: 629275501
Relay_Log_File: mysql-relay-bin.000521
Relay_Log_Pos: 625430128
Relay_Master_Log_File: mysql-bin.000195
Slave_IO_Running: No
Slave_SQL_Running: No

停掉时查看mysql错误日志我们发现端倪

2016-06-23 14:06:56 32289 [Warning] 'proxies_priv' entry '@ root@sh175' ignored in --skip-name-resolve mode.
160623 15:03:49 mysqld_safe Number of processes running now: 0
160623 15:03:49 mysqld_safe mysqld restarted -----发现是mysql服务重启了，而我们配置了skip_slave_start（禁止repl进程自动重启），所以复制进程处于down状态
2016-06-23 15:03:50 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-06-23 15:03:50 0 [Note] /home/data/mysql/bin/mysqld (mysqld 5.6.24-log) starting as process 22569 ...
2016-06-23 15:03:50 22569 [Note] Plugin 'FEDERATED' is disabled.
2016-06-23 15:03:50 22569 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-06-23 15:03:50 22569 [Note] InnoDB: The InnoDB memory heap is disabled
2016-06-23 15:03:50 22569 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-06-23 15:03:50 22569 [Note] InnoDB: Memory barrier is not used
2016-06-23 15:03:50 22569 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-06-23 15:03:50 22569 [Note] InnoDB: Using Linux native AIO
2016-06-23 15:03:50 22569 [Note] InnoDB: Not using CPU crc32 instructions
2016-06-23 15:03:50 22569 [Note] InnoDB: Initializing buffer pool, size = 10.0G
2016-06-23 15:03:51 22569 [Note] InnoDB: Completed initialization of buffer pool

同时我们查看zabbix发送的邮件，有linux主机内存报警通知

告警主机：_HuaYun_Interface-SLAVE
主机IP： ******
告警时间：2016.06.23 15:08:06
告警等级：Average
告警信息：Lack of available memory on server HuaYun_Interface-SLAVE
问题详情：Available memory:163.15 MB
事件ID： 42590

登上主机检查

[root@NEW-APP-MYSQL-SLAVE ~]# dmesg
Out of memory: Kill process 32289 (mysqld) score 966 or sacrifice child
Killed process 32289, UID 498, (mysqld) total-vm:14630648kB, anon-rss:7711284kB, file-rss:360kB

[root@NEW-APP-MYSQL-SLAVE ~]# free -m
total used free shared buffers cached
Mem: 7872 7747 124 0 124 1331
-/+ buffers/cache: 6292 1580
Swap: 2047 56 1991

很明显是linux内存不足而发生了 OOM killer 现象，没办法，加大主机内存吧

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29096438/viewspace-2120841/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29096438/viewspace-2120841/