Azure DMS - MySQL Replicate Changes now in preview

[Guest SriniKdata]

We're pleased to announce preview of MySQL Server - Replicate Changes in Azure Database Migration Service. With a replicate changes migration, in conjunction with our offline scenario with “Enable Transactional Consistency", businesses can migrate their databases to Azure while the databases continue to be operational. In other words, migrations can be completed with minimum downtime for critical applications, limiting the impact to service level availability and inconvenience to their end customers.

 

 

 

How the Replicate Changes scenario works

 

 

 

 

The current implementation is based on streaming binlog changes from the source server and applying them to the target server. Similar to Data-in replication, this is easier to set up and doesn't require a physical connection between the source and the target servers.

 

 

 

Binlog can be sent by the server as a stream that contains binary data as documented here MySQL Internals Manual :: 14.9 Replication Protocol. The client can specify the initial log position to start the stream with. The log position is described by the log file name, the position within that file, and optionally GTID (Global Transaction ID) if gtid mode is enabled at the source.

 

The data changes are sent as "row" events, which contain data for individual rows (prior and/or after the change depending on the operation type, which is insert, delete, update). The row events are then applied in their raw format using a BINLOG statement: MySQL 8.0 Reference Manual :: 13.7.8.1 BINLOG Statement.

 

 

Prerequisites

 

 

To complete the replicate changes migration successfully, ensure that the following prerequisites are in place:

 

• Use the MySQL command line tool of your choice to determine whether log_bin is enabled on the source server. The Binlog is not always turned on by default, so verify that it is enabled before starting the migration. To determine whether log_bin is enabled on the source server, run the command: SHOW VARIABLES LIKE 'log_bin’
  
• Ensure that the user has “REPLICATION_APPLIER” or “BINLOG_ADMIN” permission on target server for applying the bin log.
  
• Ensure that the user has “REPLICATION SLAVE” permission on target server.
  
• Ensure that the user has “REPLICATION CLIENT” and “REPLICATION SLAVE” permission on source server for reading and applying the bin log.
  
• Run an offline migration scenario with “Enable Transactional Consistency" to get the bin log file and position.
  
• If you're targeting a replicate changes migration, configure the binlog_expire_logs_seconds parameter on the source server to ensure that binlog files are not purged before the replica commits the changes. We recommend at least two days to start. After a successful cutover, the value can be reset.
  

 

 

 

Getting started with Replicate Changes

 

 

 

 

You must run an offline migration scenario with “Enable Transactional Consistency" to get the bin log file and position to start replicating the incoming changes. The DMS portal UI shows the binary log filename and position aligned to the time the locks were obtained on the source for the consistent snapshot. We'll use this value in our replicate changes migration to begin streaming the incoming changes.

 

 

 

 

 

 

Now, start a new Replicate Changes activity.

 

 

 

 

 

 

Select the source.

 

 

 

 

 

 

 

 

Select your the flexible server target running version 8.0; servers running Single Server are filtered from the dropdown.

 

 

 

 

 

 

Enter the bin log position provided by the offline scenario.

 

 

 

Note: MySQL does not validate the initial binlog position and it will attempt to stream the binlog from any position it is told to. If an invalid value is specified, the server may report an unrelated error. For example, it may suggest increasing the "max_allowed_packet" value or even say that master is out of disk space. If you end up getting these types of errors, verify that the binlog position is specified correctly.

 

 

 

 

 

 

Ensure that you select the same database and tables that you selected for your offline migration.

 

 

 

 

 

 

 

 

 

In the Activity name text box, specify a name, and then select Start migration.

 

 

 

 

 

 

Monitor your migration and view the progress to determine when you can cutover with minimal downtime.

 

 

 

 

 

 

 

 

When your target is almost caught up with the source server, stop all incoming transactions to the source database, and then wait until all pending transactions have been applied to the target database. To confirm that the target database is up-to-date, on the source server, run the query 'SHOW MASTER STATUS;', and then compare that position to the position of the last committed binlog event (displayed under Migration Progress). When the two positions are the same, the target has caught up with all changes and you can start cutover.

 

 

 

 

 

 

Limitations

 

• When performing a replicate changes migration, the name of the database on the target server must be same as the name on source server.
  
• Preview support is limited to the ROW binlog format.
  
• Preview support is limited to migrations to Azure Database for MySQL - Flexible Server.
  
• Currently, preview support is limited to migrations to target servers running MySQL version 8.0.
  
• This feature only replicates DDL changes; replicating DML changes is not supported. Do not make any schema changes to the source while replication is in progress.
  
• Currently, the steps for performing a replicate changes migration are not provided in our MySQL documentation, However, we will update the MySQL migration tutorials in the future.
  

 

 

 

In future releases, we'll include support for MySQL 5.7, using a single server as the target, and integrated online scenarios (offline+ catchup), so stay tuned!

 

Continue reading...