Tutorial — MySQL DB from Self Hosted to Managed on Azure
Azure has a hosted MySQL service that allows you to offload the operational overhead of your database. By providing you with management for:
- High availability
- Pay-as-you-go pricing
- Elastic scale
- Security, including encryption
- Point-in-time restores
You can check out all the features on the Azure website to get a complete listing of all the services that are part of the managed MySQL on Azure.
Migrating my database
Today’s tutorial will take a database I have been using on a self-hosted database and migrate it to Azure Database for MySQL. I already have access to the old environment the database source is on. I’m going to be using Azure for development now so I am not quite concerned with a live migration, so using a dump and restore method is going to be fine.
I’ve used the standard MySQL dump method and then compressed the file into a tar.gz:
# Remote Database Server $ dump database mysqldump --databases rabdb -uroot -p -hdbserver01.oldhost.com > rabdb.sql # Compress database output $ tar czvf rabsql.tar.gz rabdb.sql
My data is now exported to a SQL file and compressed. I will now copy it to my local computer. For the sake of this post, I will skip this, but it’s just a quick download over sftp I performed.
Decompress your tar.gz on your local computer:
# Local Workstation
# decompress SQL file: $ tar xvfz rabsql.tar.gz
Create an Azure MySQL Server
In this case, I am going to use the azure-cli utility to create my new database. The documentation for both the CLI and the Portal-based creation are well documented on the Azure docs site here.
First, I will create a resource group, you can use these command locally with “az” or by using the Azure Cloud Shell. Feel free to select the region in which you’d like your data hosted in, in this case I am using westus.
# Cloud Shell $ az group create --name rab --location westus
This output lets us know that the group in the specific region we requested has been created. Next we can create the actual MySQL database.
# Cloud Shell $ az mysql server create --resource-group rab --name rabdata --location westus --admin-user rabadmin --admin-password D3nsb32ZdC --sku-name GP_Gen5_2 --version 5.7
Don’t worry about seeing my password there, I have already deleted all this and it’s a random password. Secondly, I didn’t create my “admin user” as root because this username is not permitted by the Azure MySQL service.
When the service is done provisioning after several minutes, I’ll see the following output from the Cloud Shell:
The metadata in this template provides you with all the details associated with my newly-created MySQL database server.
I won’t be using SSL for this connection, but by default it’s enabled for connections to MySQL. I can disable this in the Cloud Shell like so:
# Cloud Shell $ az mysql server update --resource-group rab --name rabdata --ssl-enforcement Disabled
Now I need to open the firewall to this database so I can restore my data. This command contains the IP address I will access the database from:
# Cloud Shell $ az mysql server firewall-rule create --resource-group rab --server rabdata --name AllowMyIP --start-ip-address 10.0.0.4 --end-ip-address 10.0.0.4
The firewall rule I needed is generated; time to get my connection details and then upload my data. I will run this command with a grep for the fully qualified domain name of the MySQL server:
# Cloud Shell $ az mysql server show --resource-group rab --name rabdata | grep fullyQualifiedDomainName
Looks like I am ready to restore my database. Back over to my local computer, this time using the credentials I created when I set up our Azure MySQL database server. The restore command is pretty simple to execute, but be certain to notice the quoted username. It includes the name of the database we are specifically accessing:
# Local Workstation $ mysql -h rabdata.mysql.database.azure.com "-urabadmin@rabdata" -p < rabdb.sql
The data will take about as long as the size of the database takes to transfer from your local workstation to Azure. You’ll need to take into account the CPU usage on your computer as well as the bandwidth available on your network when considering the length of time taken for the restore.
When completed with the restore, you can then login to your MySQL server and validate your data is there:
The rivekab2_wrdp database is my db’s logical name from the SQL file. I am now free to start working with the data for my application. I can migrate the remaining portions of the app and have it served from an Azure VM or the Azure App Service.
Conclusion
It’s not hard to get a MySQL server started, but who wants to manage it all on your own? Have Azure handle all of your management! Get started using the Azure free trial and you’ll see all the benefits of using the best cloud available to developers and ops teams.
Originally published at jaydestro.org on September 10, 2018.
