Contents
Why is MySQL replication getting fatal error 1236?
There might be a committed SQL statement or row change (depending on your replication format) on the master that did not make it to the slave because the event is truncated. The solution would be to move the slave thread to the next available binary log and initialize slave thread with the first available position on binary log as below:
Why does replication fail on MySQL slave server?
This error occurs when the slave server required binary log for replication no longer exists on the master database server. In one of the scenarios for this, your slave server is stopped for some reason for a few hours/days and when you resume replication on the slave it fails with the above error.
Why is MySQL no longer requesting binary logs?
When you investigate you will find that the master server is no longer requesting binary logs which the slave server needs to pull in order to synchronize data.
How to avoid corrupted binlogs on the Master?
To avoid corrupted binlogs on the master, enabling sync_binlog=1 on master helps in most cases. sync_binlog=1 will synchronize the binary log to disk after every commit. sync_binlog makes MySQL perform on fsync on the binary log in addition to the fsync by InnoDB.
Why do I get a replication error in MySQL?
This MySQL replication error can be triggered by multiple reasons and I will try to cover all of them. This is a typical error on the slave (s) server. It reflects the problem around max_allowed_packet size. max_allowed_packet refers to single SQL statement sent to the MySQL server as a binary log event from master to slave.
Why is MySQL-bin.000001 purged from Master?
If restoring the database on the slave takes too long, the log file mysql-bin.000001 may be purged from the master by the time you START SLAVE. When you get the error on the slave, run SHOW BINARY LOGS on the master and see if mysql-bin.000001 is gone.
Why is MySQL failing in master / slave configuration?
I have 2 MySql with a master/slave configuration and the replication is failing. The MySql Master crashed and a new register in the mysql-bin.index was created.
What did last IO error 1236 mean on MySQL?
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ‘The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.’
When do I get fatal error 1236 from Master?
“‘Got fatal error 1236 from master when reading data from binary log: “The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.” slave_io thread stop running. ERROR: ‘Error “Table … ‘doesn”t exist” on query.
Why is MySQL not syncing with slave server?
Usually, this caused by sync_binlog <>1 on the master server which means binary log events may not be synchronized on the disk. There might be a committed SQL statement or row change (depending on your replication format) on the master that did not make it to the slave because the event is truncated.
Why is MySQL slave server not accepting bigger packets?
When the MySQL master server tries to send a bigger packet than defined on the slave server, the slave server then fails to accept it and hence the error. In order to alleviate this issue please make sure to have the same value for max_allowed_packet on both slave and master.
What to do when MySQL error on slave server?
When you get the error on the slave, run SHOW BINARY LOGS on the master and see if mysql-bin.000001 is gone. You should also look at the setting for expire_logs_days to make sure the binlog files aren’t being expired too quickly.