0

We're moving to a different cloud provider so need to migrate our database with as little downtime as possible.

Here's our current setup:

master
└───slave1
└───slave2    
└───slaveBackup

A dump of slaveBackup was imported into our new master database, newMaster. slaveBackup logs changes to its bin logs using log-slave-updates and binlog_do_db to only log the correct database which is identically named throughout.

To reduce downtime we now want to add newMaster as a slave to slaveBackup, thus creating the following chain while we migrate:

master
└───slave1
└───slave2    
└───slaveBackup
    └───newMaster
        └───newSlave1
        └───newSlave2

newMaster is replicated as intended to newSlave1 and newSlave2, all are identical.

We recorded the bin log file and position on slaveBackup made immediately after the dump and used this to add newMaster as a slave of slaveBackup:

CHANGE MASTER TO
    MASTER_HOST = 'slaveBackup',
    MASTER_USER = 'slave',
    MASTER_PASSWORD = '',
    MASTER_LOG_FILE = 'mariadb-bin.00000X',
    MASTER_LOG_POS = X;

Starting the slave worked perfectly and in time using SHOW SLAVE STATUS\G we could see it catch up to its master.

Here's where it gets weird. Despite the slave newMaster crunching through the bin logs, no rows are being inserted into the new database. What's weirder during this time Seconds_Behind_Master was always 0.

The logs were definitely being processed as the slave user is different on newMaster ('slave'@'X.X.X.X') vs slaveBackup ('slave'@'Y.Y.Y.Y') and a moderation made after the dump of this user caused newMaster to error and required SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; to recover.

Now the slave is fully caught up with master in terms of bin log file and position yet not data has been inserted into its database.

Why is this?

The bin logs on slaveBackup definitely have the correct USE DATABASENAME; statements before each insert/update, hence why I'm completely stumped.

To clarify the replication chain:

master -> slaveBackup -> newMaster -> newSlave1/2
  • It is unclear what steps you took to make newMaster from slaveBackup. And it is unclear which "recorded the bin log file and position" you used. – Rick James Mar 19 '17 at 21:15
  • Thanks @RickJames for the questions. slaveBackup is still very much live, when the dump was made we used the flush logs flag to ensure a new bin log file was created. This file and its position was what was added to newMaster as its master – Ryan Brodie Mar 19 '17 at 21:17
  • Did you stop the slave during the dump? Else you don't know how the binlog and dump align. – Rick James Mar 19 '17 at 22:52
  • We used --flush-logs so the logs were rotated at the time of the dump, in all honesty though ACID isn't our issue right now: why the newMaster churned through the logs yet didn't make any writes is – Ryan Brodie Mar 20 '17 at 7:19
0

One of the server IDs conflicted further down the chain which caused the issue of the bin logs syncing but not replicating

Warning to all, make sure all your server IDs are unique!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.