At work we have just recently started moving our data to Azure Cloud. We still have the old on-premise setup running, with databases and web servers.
To make the move to Azure Cloud, I have used a lot of time prepping the terraform scripts to handle our servers, but I have only recently been able to make a copy of the current database (with a whopping 1.7TB of data) and copy it to the cloud.
It took a few days to insert the data into Azure MySQL, so after getting a replication error today, I was not ready to drop the replica database, to insert a fresh dump.
Calling the following select, I could read the error:
select * from performance_schema.replication_applier_status_by_worker;
It was a create table error… The master database had created a new table using MyISAM, which Azure DB does not allow.
Normally, you would increase the global counter, using:
SET GLOBAL sql_slave_skip_counter = N;
Azure does not support this, as it requires SUPER privileges (whatever that means). Thankfully Azure have implemented a set of methods to call, where this one exists: (remember to stop the replication first)
This increases the global counter by 1. Then i took the created table, changed the statement to use innodb instead and ran it on the slave. Then i called:
And the slave skipped the table create statement from the master, and continued with the replication.
Link to Azure docs: https://docs.microsoft.com/en-us/azure/mysql/single-server/reference-stored-procedures
One reply on “Handle replication errors on Azure Database for MySQL flexible server”
Why wouldn’t the table just be present on the slave? Sounds like a PHP error more than an actualy db replication error – shouldn’t this be fixed in code?