mysql主从模式与读写分离(磁盘写满导致MySQL复制失败的解决方案)
mysql主从模式与读写分离
磁盘写满导致MySQL复制失败的解决方案目录
- 案例场景
- 解决问题
- 一点总结
案例场景
今天在线上发现一个问题,由于监控没有覆盖到,某台机器的磁盘被写满了,导致线上MySQL主从复制出现问题。问题如下:
|
localhost.(none)>show slave status\G *************************** 1. row *************************** Slave_IO_State: Master_Host: 10.xx.xx.xx Master_User: replica Master_Port: 5511 Connect_Retry: 60 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: relay-bin.001605 Relay_Log_Pos: 9489761 Relay_Master_Log_File: Slave_IO_Running: No Slave_SQL_Running: No Last_Errno: 13121 Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master 's binary log is corrupted (you can check this by running ' mysqlbinlog ' on the binary log), the slave' s relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, the server was unable to fetch a keyring key required to open an encrypted relay log file, or a bug in the master 's or slave' s MySQL code. If you want to check the master 's binary log or slave' s relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. |
于是查看error log,发现error log中的内容如下:
|
2021-03-31T11:34:39.367173+08:00 11 [Warning] [MY-010897] [Repl] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. 2021-03-31T11:34:39.368161+08:00 12 [ERROR] [MY-010596] [Repl] Error reading relay log event for channel '' : binlog truncated in the middle of event; consider out of disk space 2021-03-31T11:34:39.368191+08:00 12 [ERROR] [MY-013121] [Repl] Slave SQL for channel '' : Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master 's binary log is corrupted (you can check this by running ' mysqlbinlog ' on the binary log), the slave' s relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, the server was unable to fetch a keyring key required to open an encrypted relay log file, or a bug in the master 's or slave' s MySQL code. If you want to check the master 's binary log or slave' s relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. Error_code: MY-013121 2021-03-31T11:34:39.368205+08:00 12 [ERROR] [MY-010586] [Repl] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START" . We stopped at log 'mysql-bin.000446' position 9489626 |
从描述中可以看到,error log是比较智能的,发现了磁盘问题,并提示我们需要"consider out of disk space"
解决问题
登录服务器,很快就发现是MySQL所在的服务器磁盘使用率达到100%了,问题原因跟error log中的内容一致。
现在就解决这个问题。基本的思路就是清理磁盘文件,然后重新搭建复制关系,这个过程似乎比较简单,但是实际操作中,在搭建复制关系的时候出现了下面的报错:
|
### 基于gtid的复制,想重新搭建复制关系 localhost.(none)>reset slave; ERROR 1371 (HY000): Failed purging old relay logs: Failed during log reset localhost.(none)>reset slave all ; ERROR 1371 (HY000): Failed purging old relay logs: Failed during log reset |
第一步:因为复制是基于gtid进行的,所以直接记录show slave status的状态后,就可以重新reset slave,并利用change master语句来重建复制关系了。
但是却出现上面的报错,从报错信息看是mysql无法完成purge relay log的操作,这看起来不科学。好吧,既然你自己不能完成purge relay logs的操作,那就让我来帮你吧。
第二步:手工rm -f 删除所有的relay log,发现报错变成了:
|
localhost.(none)>reset slave all ; ERROR 1374 (HY000): I/O error reading log index file |
嗯,好吧,问题没有得到解决。
然后思考了下,既然不能通过手工reset slave 来清理relay log,直接stop
slave 然后change master行不行呢?
第三步:直接stop slave,然后change master,不执行reset slave all的语句,结果如下:
|
localhost.(none)>change master to master_host= '10.13.224.31' , -> master_user= 'replica' , -> master_password= 'eHnNCaQE3ND' , -> master_port=5510, -> master_auto_position=1; ERROR 1371 (HY000): Failed purging old relay logs: Failed during log reset |
得,问题依旧。
第四步:反正复制已经报错断开了,执行个start slave看看,结果戏剧性的一幕出现了:
|
localhost.(none)>start slave; ERROR 2006 (HY000): MySQL server has gone away No connection . Trying to reconnect... Connection id: 262 Current database : *** NONE *** Query OK, 0 rows affected (0.01 sec) localhost.(none)> [root@ ~]# |
执行start slave之后,实例直接挂了。
到这里,复制彻底断开了,从库实例已经挂了。
第五步:看看实例还能不能重启,尝试重启实例,发现实例还能起来。实例重新起来后,查看复制关系,结果如下:
|
localhost.(none)>show slave status\G *************************** 1. row *************************** Slave_IO_State: Queueing master event to the relay log Master_Host: 10.xx.xx.xx Master_User: replica Master_Port: 5511 Connect_Retry: 60 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: relay-bin.001605 Relay_Log_Pos: 9489761 Relay_Master_Log_File: Slave_IO_Running: Yes Slave_SQL_Running: No Last_Errno: 13121 Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master 's binary log is corrupted (you can check this by running ' mysqlbinlog ' on the binary log), the slave' s relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, the server was unable to fetch a keyring key required to open an encrypted relay log file, or a bug in the master 's or slave' s MySQL code. If you want to check the master 's binary log or slave' s relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. Skip_Counter: 0 |
复制关系依旧报错。
第六步:重新reset slave all看看,结果成功了。
|
localhost.(none)>stop slave; Query OK, 0 rows affected (0.00 sec) localhost.(none)>reset slave all ; Query OK, 0 rows affected (0.03 sec) |
第七步:重新搭建复制关系并启动复制
|
localhost.(none)>change master to master_host= '10.xx.xx.xx' , -> master_user= 'replica' , -> master_password= 'xxxxx' , -> master_port=5511, -> master_auto_position=1; Query OK, 0 rows affected, 2 warnings (0.01 sec) localhost.(none)>start slave; Query OK, 0 rows affected (0.00 sec) localhost.(none)>show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.xx.xx.xx Master_User: replica Master_Port: 5511 Connect_Retry: 60 ... Slave_IO_Running: Yes Slave_SQL_Running: Yes |
发现实例的复制关系可以建立起来了。
一点总结
当磁盘写满的情况发生之后,mysql服务无法向元信息表中写数据,relay log也可能已经不完整了,如果直接清理了服务器上的磁盘数据,再去重新change master修改主从复制关系,可能会出现报错,不能直接修复,因为这不是一个正常的主从复制关系断裂场景。
所以,正确的做法应该是:
1、清理服务器的磁盘
2、重启复制关系断开的那个从库
3、重新reset slave all、change master来搭建主从复制关系即可
如果有更好的方法,还请不吝赐教。
以上就是磁盘写满导致MySQL复制失败的解决方案的详细内容,更多关于MySQL复制失败的解决方案的资料请关注开心学习网其它相关文章!
原文链接:https://mp.weixin.qq.com/s/2BdF-HwKDdH9LsLixXGPcg
- oraclemysql知识点(详解Mysql和Oracle之间的误区)
- mysql的innodb引擎数据结构(MySQL InnoDB架构的相关总结)
- mysql单独导出表(mysql实现从导出数据的sql文件中只导入指定的一个表)
- docker运行redis并操作(Docker安装MySQL和Redis的方法步骤)
- 常见的mysql优化策略(MySQL pt-slave-restart工具的使用简介)
- mysql的存储方法(MySQL中的binary类型使用操作)
- python与mysql的联系(MySQL和Python交互的示例)
- mysql 索引怎么实现(Mysql中索引和约束的示例语句)
- mysql的四种关系运算(详解MySQL拼接函数CONCAT的使用心得)
- mysql日常运维(MySQL从库维护经验分享)
- 操作mysql的jdbc(JDBC连接的六步实例代码与mysql连接)
- mysql哪个版本可以下载msi(新手必备之MySQL msi版本下载安装图文详细教程)
- druid数据库连接池原理详解(阿里巴巴Druid,轻松实现MySQL数据库加密!)
- 终于有人将mysql 索引讲清楚了(MySQL 索引的一些细节分享)
- linux系统登录mysql数据库(Linux手动部署远程的mysql数据库的方法详解)
- mysql详细笔记(MySQL的内存表的基础学习教程)
- 他是梁山最早的头目,江湖人称 旱地忽律 ,宋江几乎将其遗忘(他是梁山最早的头目)
- 梁山创始人杜迁,为何不受宋江待见,只排名83位(梁山创始人杜迁)
- 法国面包(法国面包法棍)
- 微信(微信分身)
- 双十二(双十二和双十一哪个划算)
- 佛肚竹盆景的养护之道(佛肚竹盆景的养护之道)
热门推荐
- SQLserver中cube:多维数据集实例详解(SQLserver中cube:多维数据集实例详解)
- Visual Studio中使用正则表达式替换
- mysql日期用法(MySQL DATE_ADD和ADDDATE函数实现向日期添加指定时间间隔)
- 真实的codeigniter错误(Codeigniter里的无刷新上传的实现代码)
- mysql百万数据分页查询优化方案(MySQL单表亿级数据分页怎么优化?)
- sqlserver连接字符串函数(SQL SERVER 2012新增函数之字符串函数FORMAT详解)
- python动态数组原理(Python实现的旋转数组功能算法示例)
- sqlserver触发器编写(SQLSever中的触发器基本语法与作用)
- dedecms图片延迟加载(dedecms获取图片集多张图片实现方法循环输出)
- dedecms参数改不了(dedecms安全设置集合整理)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9