MySQL Replication - Error 1236 - Inconsistency in the replicated databases

Publication : 28 avril 2020

This error 1236 ("Got fatal error 1236 from master when reading data from binary log") means that there is inconsistency in the file "mysql-bin.index" of the slave server.
This is part of the MySQL Replication series.

If the replication of your database server does not work anymore, then this could be the error. You can see this error message in the status of your slave server.

mysql> show slave status \G
*************************** 1. row ***************************
[...]
Slave_IO_State:
Master_Host: 32.XXXX
Master_User: duplizierer
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000603
Read_Master_Log_Pos: 510772
Relay_Log_File: mysql-relay-bin.000017
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 dex file'
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: d6fb7551-5834-11e7-98d3-00224d7cbe03
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
[...]
1 row in set (0.06 sec)

The solution for this problem is not complicated. You have to reload your "master_log_file" and reset the slave.

In your master server

First of all you have to stop outside access to your database server during this configuration to avoid data inconsistency

Then, check the status of your master server.

mysql> show master status \G
*************************** 1. row ***************************
File: mysql-bin.001202
Position: 1017657
Binlog_Do_DB: 
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

Please remember the value of "position" (here: 1017657) and the value of "file" (here: mysql-bin.001202), which will be used later in the slave server.

Create a backup (dump) of your MySQL database server:

sudo mysqldump -u root -p --single-transaction --add-drop-database --all-databases > /database_master_dump.sql

If you replicate only a certain database, then use this command:

sudo mysqldump -u root -p --single-transaction --add-drop-database --databases MY-DATABASE > /database_master_dump.sql

In your slave server

1. Login into your MySQL server and stop your slave server

stop slave;

2. Reset your slave server

reset slave;

3. Now logout of your MySQL server and import your database backup:

mysql -u root -p < /database_master_dump.sql

4. Login in to your MySQL server again and run this command:

mysql> change master to master_log_file='mysql-bin.001202', master_log_pos=1017657;

You have to enter the values that where displayed in the status of the master server.

5. Now start your slave server

start slave;

Finally, now you can allow full access to your database server of your master server.

You can check the status of your slave server to know if you fixed the inconsistency.

show slave status \G

If you have several slave servers, then repeat the steps from 1 to 5 again.

How to avoid this error?

Inconsistency can happen in rare cases, but you can lower the chances for this type of error by allowing only read access to your replicated databases in your slave server.

More about errors in MySQL:
https://dev.mysql.com/doc/refman/8.0/en/problems.html