mysql：consider out of disk space on master；，磁盘满了，导致主从同步失败

admin管理员组

文章数量:1794759

mysql：consider out of disk space on master；，磁盘满了，导致主从同步失败

场景： 主主复制，因为磁盘满了，没有及时清理，出现下面的问题: 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

参考：blog.csdn/faye0412/article/details/8448762

本文标签： 主从满了磁盘diskmySQL