Azure MySQL data-in replication

Most managed MySQL offering does not have easy data-in replication process. In case of self managed MySQL instance, it’s fairly 4–5 steps process to perform replication. Here steps varies based on the cloud provider we use. Let’s check how it should be done for Azure MySQL DB.

Server Creation

The Azure Database for MySQL server must be created in the General Purpose or Memory Optimized pricing tiers. If we create Azure Database basic, it does not support replication feature.

Create Azure MySQL Database

Ensure the database created is basic

az mysql server create -l northeurope -g testgroup -n testsvr -u username -p password \
    --sku-name B_Gen5_1 --ssl-enforcement Enabled --minimal-tls-version TLS1_0 --public-network-access Disabled \
    --backup-retention 10 --geo-redundant-backup Enabled --storage-size 51200 \
    --tags "key=value" --version 5.7

Create Azure VM & install Database

https://dev.mysql.com/get/Downloads/MySQLInstaller/mysql-installer-community-5.7.33.0.msi

Get Database server name for restricted access

On Azure Database get hostName. This will be used for the user creation on the VM Database.

mysql -hxxxxx.mysql.database.azure.com -u"dbadmin@xxxx" -p"Mypwd" -A
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 64752
Server version: 5.6.47.0 MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SELECT @@global.redirect_server_host;
+----------------------------------------------------------------+
| @@global.redirect_server_host                                  |
+----------------------------------------------------------------+
| dxxxxxxxxxxx.tr169.indiacentral1-a.worker.database.windows.net |
+----------------------------------------------------------------+
1 row in set (0.01 sec)

Create replication User

This is normal replication process. Create a replication user on source database server.

CREATE USER 'syncuser'@'%' IDENTIFIED BY 'xxxxxxx'; 
GRANT REPLICATION SLAVE ON *.* TO ' syncuser'@'%';

Setup source database

Azure supports innodb storage engine only.

SET GLOBAL lower_case_table_names = 1;
# Change all tables to INNODB 
select 
 CONCAT('ALTER TABLE ',table_schema ,'.', table_name,' ENGINE=INNODB') 
from information_schema.tables tab 
where engine != 'InnoDB' and table_type = 'BASE TABLE' 
      and table_schema not in ('information_schema', 'sys', 'performance_schema','mysql') 
-- and table_schema = 'your database name' 
order by table_schema, table_name;

Get source replication information

show master status; 
+--------------------+----------+--------------+------------------
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |+--------------------+----------+--------------+------------------+
| mysql-bin.000016 |      475 |                  |                       +--------------------+----------+--------------+------------------+
set global read_only=1;

Backup restore to Azure

mysql -h [ azure dns ] -u [azure user] -p [pwd] -e "Create database [database_name]
mysqldump -h [source ip ] -u [user name] –p [password] [database_name]  | mysql -h [ azure dns ] -u [azure user] -p [pwd] [database_name]

Start replication

This is the only change then normal replication. As Azure does not allow you to perform replication with change master command.

CALL mysql.az_replication_change_master('source ip', 'syncuser', 'xxxxxx', 3306, 'mysql-bin.000002', 1066, '');
CALL mysql.az_replication_start;

Trouble shoot

# replicatin status
show slave status;
# stop replication
CALL mysql.az_replication_stop;
# skip replication slave if any error
CALL mysql.az_replication_skip_counter;

Reference:

Configure data-in replication - Azure Database for MySQL