mysql replication

 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;