Its Nightmare Story struggled a lot to reduce the cut over time with Azure MySQL to AWS Migration MySQL Migration.
- Backup and Restore - Its took 6 hours to complete
- AWS DMS - Text Data truncated and timestamp column data was 6 hours behind from the source
But Nothing helped due to Bugs, features unavailable etc.., But by default replication helped us to work.
Two types replication available in Azure MySQL ,
This is we already discussed with multiple topics in our blog for GCP cloud, also we will explain how to configure GTID based replication from Azure MySQL to AWS RDS in next blog
MySQL GTID Replication and lower_case_table_names
Multi Source Replication from GCP Cloud MySQL to Percona XtraDB Cluster
We are performing binlog position-based replication to cut over the application in this blog
Environment Setup
- Azure MySQL 5.6.4 General Purpose instance launched with minimal configuration
- AWS RDS for MySQL 5.7 launched with customized parameter group
- Network connectivity between Azure PaaS and AWS PaaS Service
Once environment is up and running, modify the below server parameters in both side
Below values needs to update in Both environments,
Azure MySQL:
- replicate_wild_ignore_table=mysql.%,information_schema.%,performance_schema.%,sys.%
- binlog_row_image = FULL
- binlog_expire_logs_seconds = 1440
AWS RDS MySQL:
- time_zone = Same as Azure MySQL
- binlog_format = ROW
- collation and character_set parameters = Same as Azure MySQL
Now both server is ready to configure the replication
Pre-Steps to configure the replication
1.Let’s verify and take a note of binlog position in Azure MySQL
mysql> show master status.
+------------------+----------+--------------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+-----------------+--------------+------------------+-------------------+
| mysql-bin.00865 | 52884741 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
2.Create a replication user with necessary privileges
CREATE ‘repluser’@’%’ IDENTIFIED BY ‘datablogs123’.
GRANT REPLICATION SLAVE ON *.* TO ‘repluser’@’%’;
GRANT REPLICATION CLIENT ON *.* TO ‘repluser’@’%’;
GRANT SELECT, CONNECT ON *.* TO ‘repluser’@’%’;
FLUSH PRIVILEGES;
3.Using mysqldump start the schema backup and followed by data backup
mysqldump -h datablogs.mysql.database.azure.com -u repluser@datablogs -p datablogsprod --no-data --routines --triggers --events > datablogsprodmeta.sql
mysqldump -h datablogs.mysql.database.azure.com -u repluser@datablogs -p datablogsprod --routines --triggers --events --skip-add-drop-table --max_allowed_packet=1024M --single-transaction > datablogsproddata.sql
4.Once backup is completed restore the data into MySQL RDS ,
mysql -h datablogs.c5exr7bs8bax.ap-southeast-1.rds.amazonaws.com -u repluser -p datablogsprod < datablogsprodmeta.sql
mysql -h datablogs.c5exr7bs8bax.ap-southeast-1.rds.amazonaws.com -u repluser -p datablogsprod --max_allowed_packet=1024M < datablogsproddata.sql
Configuring the replication
In Amazon MySQL set the external master with the help of Step 1 binlog file name and binlog position
CALL mysql.rds_stop_replication;
CALL mysql.rds_reset_external_master;
CALL mysql.rds_set_external_master ('datablogs.mysql.database.azure.com', 3306, 'repluser@datablogs', 'repluser', 'mysql-bin.00865', 52884741, 0);
CALL mysql.rds_start_replication;
Once executed start the replication and check the slave status in RDS ,
mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: datablogs.mysql.database.azure.com Master_User: repluser@datablogs Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.00865 Read_Master_Log_Pos: 52884741 Relay_Log_File: relaylog.000004 Relay_Log_Pos: 273 Relay_Master_Log_File: mysql-bin.00865 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: datablogsprod Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: mysql.plugin,mysql.rds_monitor,mysql.rds_sysinfo,innodb_memcache.cache_policies,mysql.rds_history,innodb_memcache.config_options,mysql.rds_configuration,mysql.rds_replication_status Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 52884741 Relay_Log_Space: 19742 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1053643462 Master_UUID: 787858b4-a021-11ea-b894-97f71069e028 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)
Post Validation and troubleshooting steps of Azure MySQL Replication
CALL mysql.rds_stop_replication;CALL mysql.rds_skip_repl_error;CALL mysql.rds_start_replication;
SHOW BINLOG EVENTS IN 'mysql-bin.00865' FROM 52884567 LIMIT 10 \G
Now All set for Azure to AWS data sync , now its ready for application cutover with zero downtime !!!