修復 mysql replication server_errno=1236 筆記－Hello World

修復 mysql replication 失敗筆記

因為 master 資料庫無預警電源異常
導致 mysql 主從同步失敗

show slave status\G;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.x.x
Master_User: repl
Master_Port: 3306
Connect_Retry: 3
Master_Log_File: MYDB-Master101-bin.001031
Read_Master_Log_Pos: 329886971
Relay_Log_File: MYDB-Slave130-relay-bin.003254
Relay_Log_Pos: 98
Relay_Master_Log_File: MYDB-Master101-bin.001031
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB: db1,db2,db3
Replicate_Ignore_DB: mysql,test
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: 329886971
Relay_Log_Space: 98
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
1 row in set (0.00 sec)

slave 資料庫的錯誤訊息
210202 15:39:14 [Note] Slave I/O thread: connected to master 'repl@192.168.x.x:3306', replication started in log 'MYDB-Master101-bin.001031' at position 329886971
210202 15:39:14 [ERROR] Error reading packet from server: Client requested master to start replication from impossible position ( server_errno=1236)
210202 15:39:14 [ERROR] Got fatal error 1236: 'Client requested master to start replication from impossible position' from master when reading data from binary log
210202 15:39:14 [Note] Slave I/O thread exiting, read up to log 'MYDB-Master101-bin.001031', position 329886971

原因:
因為重啟 master 資料庫後，會重新產生一個新檔名的 binlog file
但是 slave 資料庫不知道，還繼續往下要拉資料回來

解決方式
先停掉 slave 同步
slave stop;

重新指向新的bin log
CHANGE MASTER TO MASTER_LOG_FILE='MYDB-Master101-bin.001032',MASTER_LOG_POS=0;

重新同步
slave start;

重新再看一次同步狀態，沒意外的話應該就正常了
show slave status\G;