Handle replication errors on Azure Database for MySQL flexible server

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)

CALL mysql.az_replication_skip_counter;

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:

CALL mysql.az_replication_start;

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