admin管理员组文章数量:1026073
场景：
主主复制，因为磁盘满了，没有及时清理，出现下面的问题:
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ‘binlog truncated in the middle of event; consider out of disk space on master; the first event ‘mysql-bin.000785’ at 753045450, the last event read from ‘./mysql-bin.000785’ at 753045450, the last byte read from ‘./mysql-bin.000785’ at 753045504.’
登录数据库，查看状态：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: ##无法同步，所以没状态
Master_Host: 192.168.199.179 ##master IP
Master_User: root
## 被用于连接主服务器的当前用户
Master_Log_File: mysql-bin.000785
Read_Master_Log_Pos: 753045450
Relay_Log_File: relay-relay-bin.001393
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000785
Slave_IO_Running: No
#I/O线程无法被启动并地连接到主服务器上
Slave_SQL_Running: Yes
master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'binlog truncated in the middle of event; consider outof disk space on master;
the first event 'mysql-bin.000785' at 753045450, the last event read from './mysql-bin.000785' at 753045450, t
he last byte read from './mysql-bin.000785' at 753045504.' #报错
解决办法：
第一步：修复mysql.proc;
mysql> repair table mysql.proc;
+------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------+--------+----------+----------+
| mysql.proc | repair | status | OK |
+------------+--------+----------+----------+
1 row in set (0.01 sec)
第二步：停止从库
mysql> stop slave;
第三步：在主服务器上登录，记录同步点
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000503 ##等会同步的binlog
Position: 219 ##等会同步的位置点
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
第四步：从库重新设定master
mysql> CHANGE MASTER TO MASTER_HOST='192.168.199.179', \
-> MASTER_USER='root', \
->MASTER_PASSWORD='123456', \
->MASTER_LOG_FILE='mysql-bin.000503', \
-> MASTER_LOG_POS=219;
第五步：启动从库
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
第六步：查看结果
mysql> show slave status\G
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.199.179
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000794
Read_Master_Log_Pos: 745696544
Relay_Log_File: relay-relay-bin.000002
Relay_Log_Pos: 2426094
Relay_Master_Log_File: mysql-bin.000794
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
参考：https://blog.csdn/faye0412/article/details/8448762
场景：
主主复制，因为磁盘满了，没有及时清理，出现下面的问题:
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ‘binlog truncated in the middle of event; consider out of disk space on master; the first event ‘mysql-bin.000785’ at 753045450, the last event read from ‘./mysql-bin.000785’ at 753045450, the last byte read from ‘./mysql-bin.000785’ at 753045504.’
登录数据库，查看状态：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: ##无法同步，所以没状态
Master_Host: 192.168.199.179 ##master IP
Master_User: root
## 被用于连接主服务器的当前用户
Master_Log_File: mysql-bin.000785
Read_Master_Log_Pos: 753045450
Relay_Log_File: relay-relay-bin.001393
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000785
Slave_IO_Running: No
#I/O线程无法被启动并地连接到主服务器上
Slave_SQL_Running: Yes
master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'binlog truncated in the middle of event; consider outof disk space on master;
the first event 'mysql-bin.000785' at 753045450, the last event read from './mysql-bin.000785' at 753045450, t
he last byte read from './mysql-bin.000785' at 753045504.' #报错
解决办法：
第一步：修复mysql.proc;
mysql> repair table mysql.proc;
+------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------+--------+----------+----------+
| mysql.proc | repair | status | OK |
+------------+--------+----------+----------+
1 row in set (0.01 sec)
第二步：停止从库
mysql> stop slave;
第三步：在主服务器上登录，记录同步点
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000503 ##等会同步的binlog
Position: 219 ##等会同步的位置点
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
第四步：从库重新设定master
mysql> CHANGE MASTER TO MASTER_HOST='192.168.199.179', \
-> MASTER_USER='root', \
->MASTER_PASSWORD='123456', \
->MASTER_LOG_FILE='mysql-bin.000503', \
-> MASTER_LOG_POS=219;
第五步：启动从库
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
第六步：查看结果
mysql> show slave status\G
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.199.179
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000794
Read_Master_Log_Pos: 745696544
Relay_Log_File: relay-relay-bin.000002
Relay_Log_Pos: 2426094
Relay_Master_Log_File: mysql-bin.000794
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
参考：https://blog.csdn/faye0412/article/details/8448762
版权声明：本文标题：mysql：consider out of disk space on master；，磁盘满了，导致主从同步失败 内容由热心网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://it.en369.cn/jiaocheng/1738335968a1562696.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论