This post has been republished via RSS; it originally appeared at: Microsoft Data Migration Blog articles.
We are pleased to announce preview of MySQL Server - Replicate Changes in Azure Database Migration Service. With 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 their end customers.
How Replicate Changes Scenario Works
The current implementation is based on streaming the binlog changes from the source server and applying them to the target. It is similar to Data-in replication, but it is easier to setup, and it does not require the 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 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 Bin log is not always turned on by default and should be checked to see if it is enabled before starting the migration. You can determine whether log_bin is enabled on the source by running 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 are targeting 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 2 days to start. Post 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 will use this value in our replicate changes migration to begin streaming the incoming changes.
Then start a new Replicate changes activity:
Select source:
Select your 8.0 Flex target, it filters out single servers in 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. Verify that the binlog position is specified correctly if you get these kinds of errors.
Ensure that you select the same database and tables that you selected for your offline migration.
Give an activity name and start migration
Monitor your migration and see the progress to determine when you can cutover with minimal downtime.
Once your target is almost caught up with the source server, you can stop all the incoming transactions coming to the source database and wait until all the pending transactions have been applied to the target database. This can be confirmed by running the query 'SHOW MASTER STATUS;' on the source server and comparing that position to the position of the last committed binlog event (displayed under Migration Progress). Once the two positions are the same, the target has caught up on all changes and you can start cutover.
Limitations
- When performing replicate changes migration, name of the database on the target server must be same as the name on source server.
- We only support ROW binlog format.
- We only support migration to Azure MySQL Flexible servers.
- Currently we only support migration to target server 8.0.
- It will only replicate DDL changes, DML changes are not supported. Do not make any schema changes to source while replication is in progress.
- MySQL Migration tutorials will be updated in the future and currently replicate changes migration preview is not covered in the tutorials.
In future releases, we will include support for MySQL 5.7, single server as target, integrated online scenarios (offline+ catchup), so stay tuned!