generally follow this procedure, it's working fine: https://learn.microsoft.com/en-us/azure/mysql/single-server/how-to-data-in-replication
install mydumper on a MySQL source server:
apt-get install mydumper
Turn on binary logging. In the mysqld section, add following line:
log-bin=mysql-bin.log
Restart the server
Set your DB to read only mode:
mysql -uUserName -pPassWord -DDatabaseName <<<"FLUSH TABLES WITH READ LOCK;"
mysql -uUserName -pPassWord -DDatabaseName <<<"SET GLOBAL read_only = 1;"
Check your master status, run this at the beginnig of the running the backup:
mysql -uUserName -pPassWord -DDatabaseName <<<"show master status;"
mysql: [Warning] Using a password on the command line interface can be insecure.
File Position Binlog_Do_DB Binlog_Ignore_DB Executed_Gtid_Set
mysql-bin.000084 522687808
Dump required databases:
mydumper --regex='^(?!(backup|percona|mysql|sys|information_schema|performance_schema))' --host=localhost --user=UserName --password=PassWord --outputdir=backup --rows=500000 --compress --build-empty-files --threads=16 --compress-protocol --kill-long-queries --lock-all-tables -L mydumper-logs.txt
Check again after running the backup:
mysql -uUserName -pPassWord -DDatabaseName <<<"show master status;"
This should have a same value as previous.
When backup is finished unlock tables:
mysql -uUserName -pPassWord -DDatabaseName <<<"SET GLOBAL read_only = OFF;"
mysql -uUserName -pPassWord -DDatabaseName <<<"UNLOCK TABLES;"
Restore databases:
myloader -h 'mysql.mysql.database.azure.com' --user=UserName --password=PassWord --directory=/var/lib/mysql/backup --queries-per-transaction=500 --threads=16 --compress-protocol --verbose=3 -e 2>myloader-logs.txt
Create a user on source server:
CREATE USER 'syncuser'@'%' IDENTIFIED BY 'yourpassword';
GRANT REPLICATION SLAVE ON *.* TO ' syncuser'@'%';
Setup synchronization on destination machine:
CALL mysql.az_replication_change_master('yourVmName.uksouth.cloudapp.azure.com', 'syncuser', 'yourpassword', 3306, 'mysql-bin.000084', 522687808, '');
Check status:
show slave status;
Start synchronization:
CALL mysql.az_replication_start;
Troubleshooting:
Example error: Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000084, end_log_pos 526850979; Error executing row event: 'Table 'Table1' doesn't exist'
Solution: filter this Table1 on Azure portal:
Check for errors:
select * from performance_schema.replication_applier_status_by_worker;
