Got fatal error 1236 from master when reading data from binary log

Problem

Replikacja bazy MySQL przestała działać. Status pokazuje błąd 1236:

mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: localhost
Master_User: repuser
Master_Port: 23306
Connect_Retry: 60
Master_Log_File: mysql-bin.000042
Read_Master_Log_Pos: 1265611
Relay_Log_File: mephisto-relay-bin.000032
Relay_Log_Pos: 537
Relay_Master_Log_File: mysql-bin.000042
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: 1265611
Relay_Log_Space: 1266062
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: 'binlog truncated in the middle of event; consider out of disk space on master; the first event 'mysql-bin.000042' at 1265327, the last event read from '/var/log/mysql/mysql-bin.000042' at 1265611, the last byte read from '/var/log/mysql/mysql-bin.000042' at 1265664.'
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)

Przyczyna

Przyczyną takiego błędu jest najczęściej nagłe przerwanie pracy mastera, który po awarii rozpoczyna nowy binlog o czym slave nie wie.

Rozwiązanie

Rozwiązaniem jest poinformowanie slave o aktualnym binlogu mastera.

Na początek wyłączamy slave:

mysql> stop slave;
Query OK, 0 rows affected (0.04 sec)

Wprowadzamy aktualne dane binloga. Nazwa przeważnie jest z numerem wyżej niż ten, który pamięta slave. Pozycję binloga zaczynamy od 4:

mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000043', MASTER_LOG_POS=4;
Query OK, 0 rows affected (0.05 sec)

Uruchamiamy replikację:

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

Sprawdzamy status replikacji:

mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: repuser
Master_Port: 23306
Connect_Retry: 60
Master_Log_File: mysql-bin.000043
Read_Master_Log_Pos: 75367
Relay_Log_File: mephisto-relay-bin.000002
Relay_Log_Pos: 75513
Relay_Master_Log_File: mysql-bin.000043
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: 75367
Relay_Log_Space: 75672
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: 1
1 row in set (0.00 sec)

Zrobione.

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *