Percona MySQL 5.6 主主复制环境报错"Got fatal error 1236 from master.."

千家信息网最后更新 2024年11月14日Percona MySQL 5.6 主主复制环境报错"Got fatal error 1236 from master.."Percona MySQL 5.6 主主复制环境报错：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: vsql02
Master_User: mysql_slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqld-bin.000153
Read_Master_Log_Pos: 120
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 284
Relay_Master_Log_File: mysqld-bin.000153
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 120
Relay_Log_Space: 601
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
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: 'Could not find first log file name in binary log index file'
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 22
Master_UUID: d49b3291-289a-11e6-9b9b-2c44fd7f8364
Master_Info_File: /data/dmcdb/mysql/database/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 160809 11:42:02
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
报错原因：
Master端二进制日志被删除，导致Slave端的IO线程无法在Master端读取到指定二进制日志
mysql> show variables like '%log%bin%';
+---------------------------------+----------------------------------------+
| Variable_name | Value |
+---------------------------------+----------------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/binlog/mysqld-bin |
| log_bin_index | /var/lib/mysql/binlog/mysqld-bin.index |
| log_bin_trust_function_creators | ON |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+----------------------------------------+
6 rows in set (0.00 sec)
[root@dmcdb2 ~]# cd /var/lib/mysql/binlog
[root@dmcdb2 binlog]# ls -trl
total 1613364
-rw-r--r-- 1 root root 1651957529 Feb 17 14:29 3962.sql
-rw-rw---- 1 mysql mysql 40 Aug 9 11:16 mysqld-bin.index
-rw-rw---- 1 mysql mysql 174 Aug 9 14:17 mysqld-relay-bin.000001
-rw-rw---- 1 mysql mysql 92 Aug 9 14:17 mysqld-relay-bin.index
-rw-rw---- 1 mysql mysql 461 Aug 9 14:21 mysqld-bin.000155
-rw-rw---- 1 mysql mysql 104500 Aug 9 14:39 mysqld-relay-bin.000002
修复方法：
由于丢失了Master端的二进制日志，只能从现有的二进制日志重新配置复制环境
--Master端
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| mysqld-bin.000155 | 120 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
--Slave端
mysql> change master to master_host='vsql02', master_port=3306, master_user='mysql_slave', master_password='mysql_slave', master_log_file='mysqld-bin.000155', master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: vsql02
Master_User: mysql_slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqld-bin.000155
Read_Master_Log_Pos: 120
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 284
Relay_Master_Log_File: mysqld-bin.000155
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 120
Relay_Log_Space: 458
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 22
Master_UUID: d49b3291-289a-11e6-9b9b-2c44fd7f8364
Master_Info_File: /data/dmcdb/mysql/database/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
--Master端
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)