Categories
Uncategorized

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.

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

Leave a Reply

Your email address will not be published.