MySQL/MariaDB: репликация — fatal error 1236

Автор: | 01/03/2016
Нет комментариев
 

mysql_logo_2Имеется два MySQL сервера, с master-master репликацией.

В логах появились записи с ошибкой fatal error 1236:

160226 10:15:40 [Note] Slave SQL thread initialized, starting replication in log ‘mysql-bin.000210’ at position 107, relay log ‘/var/log/mysql/relay.000628’ position: 4
160226 10:15:40 [Note] Slave I/O thread: connected to master ‘[email protected]:3306’,replication started in log ‘mysql-bin.000210’ at position 107
160226 10:15:40 [ERROR] Error reading packet from server: Could not find first log file name in binary log index file ( server_errno=1236)
160226 10:15:40 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file’, Error_code: 1236
160226 10:15:40 [Note] Slave I/O thread exiting, read up to log ‘mysql-bin.000210’, position 107

Статус репликации так же сообщает об ошибке и указывает на тот же бин-лог и ту же позицию:

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 172.**.**.51
                  Master_User: replicator
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000210
          Read_Master_Log_Pos: 107
               Relay_Log_File: relay.000629
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000210
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
                           ...
                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: 2
1 row in set (0.00 sec)

Хотя должен был использоваться лог 000215:

# ls -l /var/log/mysql/mysql-bin.*
-rw-rw---- 1 mysql adm 126 Feb 26 09:56 /var/log/mysql/mysql-bin.000213
-rw-rw---- 1 mysql adm 126 Feb 26 10:15 /var/log/mysql/mysql-bin.000214
-rw-rw---- 1 mysql adm 107 Feb 26 10:15 /var/log/mysql/mysql-bin.000215
-rw-rw---- 1 mysql adm  96 Feb 26 10:15 /var/log/mysql/mysql-bin.index

А лога 000210 уже давно нет, так как:

mysql> SHOW VARIABLES WHERE Variable_Name='expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 10    |
+------------------+-------+
1 row in set (0.00 sec)

Для исправления — указываем MySQL другой файл лога и позицию:

STOP SLAVE;
CHANGE MASTER TO MASTER_LOG_POS = 0;
CHANGE MASTER TO MASTER_LOG_FILE = 'mysql-bin.000215';
START SLAVE;

Готово.

Ссылки по теме

http://firelitdesign.blogspot.com

https://www.percona.com