Azure Database for MySQL is a relational database service in the Microsoft cloud that is based on the MySQL Community Edition database engine. MySQL is an advanced, feature-rich, open-source database system that’s popular with developers, used for a wide range of applications, and compatible with the most popular application platforms.
Our flagship offering, Azure Database for MySQL – Flexible Server, is well-positioned to serve as the best platform for hosting your MySQL workloads, offering maximum control and flexibility to run your MySQL servers. We're now focusing all energies and feature investments towards Flexible Server, with Azure Database for MySQL - Single Server on path for retirement on 16 September 2024.
You can use Data-in replication in Azure Database for MySQL – Flexible server to synchronize data in an external MySQL server with an Azure Database for MySQL flexible server. The external server can be running on-premises, in virtual machines, in Azure Database for MySQL - Single Server, or even in a database service hosted by other cloud providers. Using Data-in replication also allows you to achieve minimal down-time for migrations from Single Server to Flexible Server.
In this blog post, I’ll explain how to migrate from Azure Database for MySQL - Single Server to Flexible Server with minimal downtime by using the ‘mysqlshell’ tool, an alternative to the mydumper/myloader tool. Mysqlshell, or ‘mysqlsh’, uses parallelism, which can speed up both the backup and restore process. This tool has shown promising results when used in a variety of scenarios with different database sizes.
Note: MySQLsh can also be used to migrate from VM, on-premises, different cloud vendors to Azure database for MySQL flexible server. However, selecting the best tool to address a specific migration scenario is a critical first step in the database migration journey, so be sure to refer to the blog post Selecting the best tool for migrating to Azure Database for MySQL – Flexible Server to understand the options available for your scenario.
The overall migration process includes the following high-level stages:
- Configuring Data-in replication between the source (Single Server) and target (Flexible Server).
- Validating replication.
- Ensuring successful cutover.
The following sections provide detail about each of these stages.
Note: This article references the term slave, which Microsoft no longer uses. When the term is removed from the software, we'll remove it from this article.
Prerequisites
Before starting this process, be sure that the following prerequisites are in place.
- An Azure Database for MySQL single server (the replication source) and an Azure Database for MySQL flexible server (the target), each running the same MySQL version (5.7 or 8.x).
Note: For information about creating an instance of MySQL – Flexible Server, see Create an Azure Database for MySQL flexible server.
- An Azure VM running Linux that can connect to both the source and target servers. Ensure that the VM has the following client tools installed:
- MySQL client – If it isn’t installed by default, download it from MySQL :: MySQL Community Downloads.
- MySQLsh – Download the mysqlsh client from https://dev.mysql.com/downloads/shell/. For more information about this tool, see MySQL :: MySQL Shell 8.0.
Note: After replication is set up and the migration completes, you can delete this VM.
- A sample database for testing replication. You can download mysqlsampledatabase.zip, and then run the included script on the primary server to create the sample classicmodels database. I’ve created a custom database named ‘db1’ in the primary server for testing this replication.
- Ensure that the user account involved in the replication has ‘replication slave’ privileges for all tables and databases.
- Ensure that the log_bin parameter is set to ‘ON’.
- In the Azure portal, in the server parameters section, set the `binlog_expire_logs_seconds` parameter on the primary server to ensure that binlogs aren’t purged before the replica commits the changes. During the migration, depending on the amount of data, the value can vary from 1 day to 3 days (converted to seconds).
- Ensure that networking is configured such that primary server and replica server can communicate with each other.
- For Public access, on the Networking page, under Firewall rules, ensure that the primary server firewall allows connection from the replica server by verifying that the Allow public access from any Azure service… check box is selected. For more information, in the article Public Network Access for Azure Database for MySQL – Flexible Server, see Firewall rules.
- For Private access, ensure that the replica server can resolve the FQDN of the primary server and connect over the network. To accomplish this, use VNet peering or VNet-to-VNet VPN gateway connection.
Configure Data-in replication between the source (Single Server) and the target (Flexible Server)
As I mentioned earlier, when you want to migrate from Azure Database for MySQL - Single Server to Flexible Server, you’ll likely want the migration to occur with minimal downtime. The best way to accomplish this is to create a replica of the source server, restore it to the target server instance, and then cut-over the application to point to new primary when the replica catches up source and there is no replication lag.
For the purposes of this post, I’m using an Azure Database for MySQL single server, which includes a single database (db1), as the source, and an Azure Database for MySQL flexile server as the target.
To configure Data-in replication between the source (Single Server) and the target (Flexible Server), perform the following steps.
- To connect to mysqlsh prompt, run the following command:
mysqlsh --uri azureuser%40rahulsaha-single1-57@rahulsaha-single1-57.mysql.database.azure.com:3306
- To take a full backup (which will also include the master binlog file number and position) of the source server, at the mysqlsh prompt, run the following command:
util.dumpInstance("/backup/mysqlsh/demo", {threads: 16, showProgress: true})
The following detail displays:MySQL rahulsaha-single1-57.mysql.database.azure.com:3306 ssl JS > util.dumpInstance("/backup/mysqlsh/demo", {threads: 16, showProgress: true}) NOTE: Backup lock is not supported in MySQL 5.7 and DDL changes will not be blocked. The dump may fail with an error if schema changes are made while dumping. Acquiring global read lock Global read lock acquired Initializing - done 2 out of 6 schemas will be dumped and within them 1 table, 0 views. 3 out of 5 users will be dumped. Gathering information - done All transactions have been started Global read lock has been released Writing global DDL files Writing users DDL NOTE: Could not select columns to be used as an index for table `db1`.`tab1`. Chunking has been disabled for this table, data will be dumped to a single file. Running data dump using 16 threads. NOTE: Progress information uses estimated values and may not be accurate. Writing schema metadata - done Writing DDL - done Writing table metadata - done Starting data dump 103% (268.44M rows / ~259.06M rows), 1.33M rows/s, 13.28 MB/s uncompressed, 688.54 KB/s compressed Dump duration: 00:03:35s Total duration: 00:03:36s Schemas dumped: 2 Tables dumped: 1 Uncompressed data size: 2.57 GB Compressed data size: 128.54 MB Compression ratio: 20.0 Rows written: 268435456 Bytes written: 128.54 MB Average uncompressed throughput: 11.94 MB/s Average compressed throughput: 596.41 KB/s - When the backup is completed, verify the contents of the backup inside the backup directory.
The backup files that were created as a result are shown in the following image:
The content of the ‘@.json’ file contains the Binlog file number and position, which can be used later to set up replication, is shown in the following image:
After verifying the contents of the backup, you need to restore it to the target Azure Database for MySQL flexible server.
- To log in to mysqlsh, run the following command:
mysqlsh --uri azureuser@rahulsaha-flex1-57.mysql.database.azure.com:3306
- To restore the backup to the target flexible server, at the mysqlsh prompt, run the following command:
util.loadDump("/backup/mysqlsh/demo", {threads: 16, showProgress: true})
- To set up the replication, create a new user by running the following command:
CREATE USER 'replica_user'@'%' IDENTIFIED WITH mysql_native_password BY 'strong_pass';
- To grant the user the ‘replica slave’ privilege on all tables and databases by running the following commands:
GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'%';
flush privileges;
Note: You can also leverage any existing user with the ‘replica slave’ privilege on all tables and databases.
With the backup/snapshot, user ready, next I will check the network connectivity, a critical component for ensuring that replication can occur between the source single server and the target flexible server. -
Ping the source single server to identify the associated IP address, then note it.
For purposes of this post, the IP address of my source server is 20.81.113.52. -
Ping the target flexible server to identify the associated IP address, then note it.
For purposes of this post, the IP address of the target server is 20.81.113.51. - Next, on the source server, in the Azure portal, under Settings, select Connection security, and then, under Firewall rules, add an entry for the IP address of the target flexible server.
-
On the target server, in the Azure portal, under Settings, select Networking, and then, under Firewall rules, add an entry for the IP address of the source single server.
After assuring that all the connectivity settings are in place, Data-in replication is properly configured from the source server to the target server, you are ready to configure and start replicating the changes from source database to the target server. - To configure the replication, at the mysql prompt, run the following command:
CALL mysql.az_replication_change_master('rahulsaha-single1-57.mysql.database.azure.com', 'replica_user@rahulsaha-single1-57', 'xxx', 3306, 'mysql-bin.000006', 657, '');
The following appears on screen:+--------------------------------------------------------------------------------------------------+ | message | +--------------------------------------------------------------------------------------------------+ | Successfully change the master. Please run "show slave status;" to check the replication status. | +--------------------------------------------------------------------------------------------------+ 1 row in set (3.07 sec) Query OK, 0 rows affected (3.08 sec) -
To start replication, at the mysql prompt, run the following command:
call mysql.az_replication_start;
The following appears on screen:
+------------------------------------------------------------------------------------------------------+ | message | +------------------------------------------------------------------------------------------------------+ | Successfully start the replication. Please run "show slave status;" to check the replication status. | +------------------------------------------------------------------------------------------------------+ 1 row in set (0.06 sec) Query OK, 0 rows affected (0.06 sec) -
While changes are being replicated to the replica DB server, check the (replication lag in seconds, Slave IO running, Slave SQL running, and the position of binlog and relay log) if the replica by running the following command:
mysql> show slave status\G
The following appears on screen:*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: rahulsaha-single1-57.mysql.database.azure.com Master_User: replica_user@rahulsaha-single1-57 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000006 Read_Master_Log_Pos: 3326 Relay_Log_File: relay_bin.000002 Relay_Log_Pos: 2989 Read_Master_Log_Pos: 3326 Relay_Log_File: relay_bin.000002 Relay_Log_Pos: 2989 Relay_Master_Log_File: mysql-bin.000006 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: mysql.%,information_schema.%,performance_schema.%,sys.% Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 3326 Relay_Log_Space: 3190 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: /app/work/primary_ca.pem 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: 2076890702 Master_UUID: b87e5f01-a3cd-11ed-ad86-3041b2bd9acc 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)
Validate replication
After we set up replication, it is important to validate the replication. To validate replication, we can check the ‘slave status’ in replica. Additionally, we can check manually where we can create dummy objects in primary database instance and check the same object being replicated to replica.
Ensure successful cutover
To ensure a successful cutover, validate the replication and verify that there is zero lag. To do so, perform the following steps:
- Configure the appropriate logins and database level permissions in the target Azure Database for MySQL flexible server.
- Stop the writes to the source MySQL single server
- Ensure that the target Azure Database for MySQL flexible server has caught up with the source server by running the show slave status command and viewing the Seconds_Behind_Master value, which should be 0.
- Call the stored procedure mysql.az_replication_stop to stop the replication since all changes on the source have been replicated to the target Azure Database for MySQL flexible server.
- Call mysql.az_replication_remove_master to remove the Data-in Replication configuration.
- Redirect clients and client applications to the target Azure Database for MySQL flexible server.
At this point, the migration is complete. Your applications are connected to the server running Azure Database for MySQL – Flexible Server.
Conclusion
We’ve now set up replication between the Azure Database for MySQL single server and the Azure Database for MySQL flexible server using the MySQL native Backup tool ‘mysqlsh’. Any changes to primary instance in the source single server are replicated to MySQL flexible server by using the native replication technique. Taking advantage of this solution, migration to Azure Database for MySQL - Flexible Server can be achieved from different sources such as VMs, on-premises, other cloud vendors, and Single Server with minimal downtime.
If you have any feedback or questions about the information provided above, please leave a comment below or email us at AskAzureDBforMySQL@service.microsoft.com. Thank you!