With the introduction of Azure Database for MySQL, I’ve seen a lot of interest and questions from customers on how they can move their existing Web App from using ClearDB as their MySQL database provider over to Azure Database for MySQL. If you’re not using ClearDB, but rather MySQL In-App as your provider, and want to move over to Azure Database for MySQL, a great blog has already been written on this that you should check out. For this blog, I’ll be migrating my Wordpress website’s database from ClearDB to Azure Database for MySQL, as well as updating my Web App to point to the new database server.
Preparing for the migration
I’ll be using MySQL Workbench as the tool to do the data migration. Of course you can use other common tools or CLI as well. First, download MySQL Workbench. Once you’ve downloaded and installed MySQL Workbench, you will need to create a connection to your ClearDB database in order to kick-off the migration. To create the connection, you’ll need some information about your ClearDB database.
In the Azure Portal, open up your ClearDB database and click on the Properties task on the left navigation pane. Keep this open as you’ll need this for the next step in creating your MySQL Workbench connection.
Now open MySQL Workbench and create a new connection by clicking on the + icon at the top of the home screen. In the Setup New Connection screen, give your connection a name (this can be anything – I choose “My ClearDB Database”), and then switch back over to your browser where you have your ClearDB database properties open and copy the HOSTNAME, USERNAME, and PASSWORD into the respective fields of MySQL Workbench. Note that to enter the password in MySQL Workbench, you’ll need to click on the Store in Vault… button first.
Once this is done, save your connection and create another new connection for your Azure Database for MySQL. If you haven’t created an Azure Database for MySQL yet, refer to our documentation Quickstart on how to do so. Similar to ClearDB, you’ll need to get the hostname, username, and password of your Azure Database for MySQL through the portal. But before we do that, we’ll need to configure control access to your Azure Database for MySQL server.
Open your Azure Database for MySQL Server in the Azure Portal and click on the Connection Security setting on the left navigation pane. For simplicity in the migration process, I have disabled SSL connectivity by clicking on the Enforce SSL connection toggle switch to Disabled. With regards to firewall rules, you can either click on the + Add My IP icon at the top of the screen which will add your local IP address to the firewall, or in my case I’ve created a firewall rule that allows all IP addresses access to my server for the time being. Later, when I configure my Web App to connect to my database server, I’ll add the appropriate IP addresses and remove this rule. For more information on configuring SSL connectivity for your server, check out our documentation.
Now that you have configured access to your Azure Database for MySQL server, you can continue to create a new MySQL Workbench connection just as you did for your ClearDB database server. Open your Azure Database for MySQL server and in the main Essentials dashboard, you’ll see that the Server Name and Server admin login name are in the main page. Your password is not exposed here. If you don’t remember what your password is for your server, you can always reset it using the Reset Password option in the upper left corner of the Essentials pane. With this information, create another connection in MySQL Workbench for your Azure Database for MySQL server the same way you did for your ClearDB database.
Migrate your database
Open MySQL Workbench and click on Database and then Migration Wizard from the drop-down to start your database migration.
In the migration wizard you’ll be asked to select your source and target database servers. For your source, choose the ClearDB database server connection you created first, and for the destination choose the Azure Database for MySQL Server you just finished creating.
Continue through the wizard until you get to the screen that asks you which schemas you want to migrate. Make sure to only migrate the schema that is applicable to your application. In this case of a Wordpress application, there is only one schema which is applicable. The name of your schema should be similar as ClearDB randomly generates the schema name.
Once you select your source schema to migrate, the remainder of your migration should be the defaults selected in the migration wizard. The time it takes to migrate your database should not be long, even for larger databases assuming your Azure Database for MySQL server is in the same region as your source ClearDB database.
Configuring your Azure Web App
Now that your database has been migrated, you’ll need to connect your Web App to your Azure Database for MySQL. You’ll need to both update your Web App as well as the firewall rules of your Azure Database for MySQL if you choose to restrict access to your database to your Web App exclusively. We’ll start with this, so open up your Web App in the Azure portal. On the left navigation pane, select Properties and note the Outbound IP Addresses. These will be the specific IP addresses that you will create firewall rules for in your Azure Database for MySQL.
Open up your Azure Database for MySQL server and create firewall rules for each IP address to allow access for your Web App to the server. This is the same process as described above for adding firewall rules.
Now go back to your Azure Web App and open up the Application settings on the left navigation pane, and then scroll down to the Connection strings section of the main pain. You will now need to modify your connection string to point to the new database server. You can simply click on the string value and edit it directly. You will need to replace all of the values except the Database value, as you migrated the database (schema) intact from ClearDB which preserves the database name. In my case, my original connection string was as follows:
Database=acsm_8cb9eb8d372ebbd;Data Source=us-cdbr-azure-west-b.cleardb.com;User Id=b8c2e429e67ac2;Password=47bd9069
After I updated it to point to my new Azure Database for MySQL Server, it looks like this:
Database=acsm_8cb9eb8d372ebbd;Data Source=jasonsnewserver.mysql.database.azure.com;User Id=jason@jasonsnewserver;Password=MyPassword12
Make sure to click the Save button at the top of the screen, and that’s all you need to do. You’re now using Azure Database for MySQL on your existing Web App. Congratulations!
Jason - JasonMA_MSFT