I am trying to do a MySQL failover, in some situations I endup with broken replication. Any idea what could be the cause of it ?


Needed to perform a host updates on master host. Hence promoting one of the slave as new master and restore replication across all slaves without missing any transactions

Expected: Since replication is current, failover should be quick and have a minimum read write downtime.

  • Lock current master to RO, flush everything in buffer, pending (which appears to be imperfect)
  • Make sure all salves replicated everything
  • Flip master
  • Point all slaves to new master and restore replication

Technical details

MySQL 5.6, one master(RW) and 25 slaves(RO). All slaves current. No network issues, all of them in same region connected over LAN.


# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 384M
innodb_additional_mem_pool_size = 20M

# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 100M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

Connection pool, C3PO. If the MySQL is in RO mode C3PO would not setup any new connection. churns pool till DB cnames resolves to a RW enabled MySQL host. However, there could be existing connections to the old master.

Operational details

To flip the database we perform the below operations sequentially

  1. execute sql statement through python - FLUSH TABLES WITH READ LOCK; set @@global.read_only = ON
  2. Wait till DB is in RO mode by querying select @@global.read_only
  3. Sleep 5 sec to make sure we give time for replication to catch up
  4. Pick one slave to promote, make sure GTID of slave (get slave status) is subset of the master GTID executed (get master status)
  5. On new master execute stop slave, reset slave all, set @@global.read_only = OFF, UNLOCK TABLES
  6. Update DNS of master DB cname, sleep 90 sec for DNS to update cache on hosts
  7. on each slave run below commands
stop slave
CHANGE MASTER TO master_host=<> master_port=<> master_sll_path=<> MASTER_AUTO_POSITION=1, ..
start slave


At times replication would be current and would undergo smooth failover, apart from old master (where we restore through mysql dump). However, sometimes slaves run into the issue of broken replication reporting missing transactions and only approach is to restore through Mysql dump or set the GTID position purged to latest one from new master. Both of them sound like missing transactions

error 1236, 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. Replicate the missing transactions from elsewhere, or provision a new slave from
backup. Consider increasing the master's binary log expiration period. The GTID sets and the missing 
purged transactions are too long to print in this message. For more information, please see the master's
error log or the manual for GTID_SUBTRACT.'


Only possible situation I can think of is old master had some transactions which were not send to slave which is recently promoted to master. These transactions came in after promoting new master and got replicated to all slaves which are connected to old master (till script was sleeping or 90 sec) and recent GTID is not available on new master leading to conclusion that GTID's were purged.

Statements contradicting above theory

  • Database is in RO mode, hence C3PO can't grab new connections and write to DB
  • Even the existing connections can't write to DB since DB is in RO mode and we issued FLUSH TABLES WITH READ LOCK. However, we didn't give enough time for existing transactions to complete ? i.e we didn't wait for flush tables command to complete ? (suspicion)
  • innodb_flush_log_at_trx_commit = 1 which ensures ACID. Hence there is no chance of new transactions pending in buffer which are about to be written to DB and logs.

Not sure anything that can cause broken replication. However, I see a post FLUSH TABLES WITH READ LOCK will not halt writes to InnoDB. unsure why can DB have more transactions while it is in RO mode. Should I do flush tables and Flush Logs as well ? that would be flying blue unsure if it would fix it and even if it does leaves me with uncertainity. Any thoughts from MySQL experts are appreciated.

Fall back approach is to revoke replication for all slaves on old master but even that would lead to missing transactions not being replicated which is the worry here.

  • Look for errant transactions on new master? Does the GTID set between servers the same? Commented Mar 28, 2021 at 23:04
  • Unfortunately we do not have log of the GTID on new master and slaves. Trying to fix it so that it would never end up in this state again if it does we would have logs this time on details. > Does the GTID set between servers the same? I think GTID on slaves are looking for recent transaction on new master and trying to resume from there. Ideally GTID set between servers should have been the same when replication was current. Only thing that could be possible is new transactions on old server while all slaves sync from them and those new transactions missing on new server
    – RobinHood
    Commented Mar 29, 2021 at 14:19
  • Trying to see if there is anything I am missing here. Any possibility of new transactions written to DB or logs (which would have been replicated to slaves from old master) while I locked DB with RO mode and flush tables with read lock
    – RobinHood
    Commented Mar 29, 2021 at 14:20


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.