Azure Database for MySQL flexible servers are best suited for:
- Application developments require community version of MySQL with better control and customizations.
- Production workloads with same-zone, zone-redundant high availability and managed maintenance windows
The benefits of Azure Database for MySQL include:
- Ease of deployments, simplified scaling, and low database management overhead for functions such as backups, high availability, security, and monitoring.
- A simplified development experience.
- Enterprise grade security, compliance, and privacy.
In this post, I’ll explain how to migrate a MySQL database from DigitalOcean to Azure Database for MySQL - Flexible Server using two methods:
- The MySQL command line client and mysqldump command-line utility.
- An instance of Azure Database Migration Service (DMS).
Prerequisites
Before beginning the migration process, be sure that the following prerequisites are in place:
- A managed MySQL database on DigitalOcean including some existing data.
- An Azure account. If you don’t have one, create an Azure free account, which includes 12 months free usage of Azure Database for MySQL - Flexible Server with Burstable 1 vCore, as well as several other Azure services.
- An Azure Database for MySQL flexible server.
- A target database on this MySQL flexible server. You can create one using the Azure portal.
- The following migration tools (to demonstrate both migration methods):
- MySQL command line client and mysqldump command-line utility
- An instance of Azure Database Migration Service (DMS). You can create a DMS instance by following the instructions in the Quickstart to provision Azure DMS using Azure Portal.
Important: To avoid any potential compatibility issues when exporting and importing the database, be sure that source and destination systems are running the same version of MySQL. If you do need to upgrade across MySQL versions, then:
- Dump/export the lower version database into a higher version of MySQL in a separate environment.
- Before attempting to migrate into an Azure Database for MySQL flexible server, at the command line, run mysql_upgrade.
Migrate using the MySQL command line tool
When you use the MySQL command line tool to perform the migration, you first need to note some details about the DigitalOcean cluster. Then you are ready to perform the dump and restore.
Get DigitalOcean managed MySQL database information
Before migrating the database, from your DigitalOcean MySQL database cluster, gather the:
- Username and password of the Admin.
- Database server name (hostname)
- Port (the server instance may not be using port 3306 )
- Database name
In addition, be sure to download the CA certificate locally. Managed databases on DigitalOcean require a secure connection over SSL.
In the DigitalOcean dashboard, navigate to the database Settings tab, and then locate Trusted sources section. Verify that the client computer that will run the MySQL command line tool to perform the migration has access to the database. It is also recommended to review and consider Performance recommendations before migration.
Dump and restore DigitalOcean MySQL database
To dump an existing MySQL database on the local on-premises server or in a virtual machine, run the following command:
mysqldump --ssl-ca=/path/to/CA-file -P[port] -u [username] -p[pass] [databasename] > [backupfile.sql]
The parameters to provide are:
- [uname] The database username
- [pass] The database password (note there's no space between -p and the password)
- [dbname] The database name
- [backupfile.sql] The filename of the database backup
- [--opt] The mysqldump option
To restore the data from the dump file into an Azure Database for MySQL flexible server database, using a mysql command line client, run the following command:
mysql -h [hostname] -u [username] -p[pass] [database_to_restore] < [backupfile.sql]
The hostname will be the name of an Azure Database for MySQL flexible server, as mydemomysqlserver.mysql.database.azure.com. For the username and password, use the server administrator’s user credentials.
When the process is complete, you will have successfully restored the data into a database on the target Azure Database for MySQL flexible server.
Note: You can also use the import and export wizards in MySQL workbench to migrate your DigitalOcean MySQL database to Azure Database for MySQL.
Migrate using Azure DMS from DigitalOcean MySQL database
In addition to mysqldump, you can also use Azure Database Migration Service (DMS) to migrate your DigitalOcean MySQL instance to Azure MySQL. Azure DMS is a first-party Azure service designed to deliver a seamless migration experience via the Azure portal or Azure DMS PowerShell. With Azure DMS, you can migrate selected databases or an entire instance either offline (Standard tier) or with minimum downtime (Premium tier). While the Azure DMS Standard tier is free to use, the Premium tier is only free for 6 months from the time of creation.
- To provision an instance of Azure DMS, see the article Create and configure a migration project in Azure DMS to migrate your DigitalOcean MySQL instance to...
- Then, on the Select source screen in Configure migration project, specify the connection details of your DigitalOcean MySQL instance IP address and Port number to start the migration using DMS.
Post-migration activities
Whether you performed the migration by using mysqldump or Azure DMS, there are a few common activities to take care of after the migration is complete, including:
- Creating logins, roles and permissions to meet application requirements.
- Recreating all the triggers on the target database as extracted during the pre-migration step.
- Sanity testing the application against the target database to certify the migration.
Conclusion
You can easily migrate your MySQL database to Azure Database for MySQL - Flexible Server using the detail provided above. In addition, don’t forget to take advantage of an Azure free account, which provides a 12 months' free offer for Azure Database for MySQL - Flexible Server.
For more information, see the Azure Database for MySQL - Flexible Server documentation. 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!