Tips & Tricks

Move WordPress data from ClearDB MySQL to Azure Database for MySQL Server

For my family I host a WordPress website on Azure (using an Azure App Service). I created it a while ago. At that time, Azure Database for MySQL Server (https://azure.microsoft.com/en-us/services/mysql) was not available yet, so the only choice was to host it at ClearDB (http://w2.cleardb.net/).

movewordpressfrommysqlcleardbtoazuremysql_createwordpresscleardb

I used the free tier from ClearDB which means that you get 20 Mb of database space. If you exceed that, you’ll need to pay.

movewordpressfrommysqlcleardbtoazuremysql_createwpsettings

The Visual Studio Subscription benefits don’t cover these expenses. Recently I started getting messages from ClearDB that my database was reaching size limits. As Azure Database for MySQL Server became available recently, I thought that that would be a good alternative. Using the MySQL Workbench it is possible to transfer an online database.

Of course, good things don’t come for free, so even the MySQL database on Azure needs to be paid for. The cheapest option was 1/4th of a CPU (50 compute units) and 50 Gb of storage, which costs € 15 a month, which is more or less reasonable, because I get € 130 of Azure credits per month based on my Visual Studio Enterprise subscription. Keep in mind that Azure Database for MySQL Server is in preview and as such it is not (yet) supported in production environments!

Now that I made a choice, I needed to migrate my database. I’m a MySQL newbie and my WordPress skills are OK, but for sure I’m not a geek… In the end I made it, so let’s go over the steps I took.

Get the tools

For MySQL a tool is available called “MySQL Workbench”. There is a community edition which you can download for free: https://dev.mysql.com/downloads/workbench/

The installation is straightforward, so I’ll skip explaining how to do that.

Setup an Azure Database for MySQL Server instance

Let’s setup the target MySQL instance in Azure. Login to the Azure Portal, add a new item, search for “MySQL” and choose the option “Azure Database for MySQL (preview)”.

movewordpressfrommysqlcleardbtoazuremysql_addazuremysql

Click the “Create” button and enter the required properties. Ensure that:

  • The region is the same one that your WordPress App Service is running on
  • You make the right selection for the Pricing Tier (I chose Basic, 50 compute units and 50 Gb storage, because the blog I setup doesn’t need to perform)

This action will create a MySQL instance that will be able to host multiple databases.

Restrict database access

When creation of the instance was finished, I had to make sure only me and my WordPress site can access the database. To do this, you need to limit connections through “Connection security”. By adding IP addresses to a list, you limit the access to only these IP addresses. To know which IP address your Azure WordPress site is using you can use the steps outlined here (the IP addresses are actually in the properties of the Azure App Service): https://blogs.msdn.microsoft.com/waws/2017/02/01/how-do-i-determine-the-outbound-ip-addresses-of-my-azure-app-service/

Open the MySQL instance in the Azure Portal and click the “Connection security” tab. From there add the 4 IP addresses your WordPress site is running on and additionally add your own IP address.

movewordpressfrommysqlcleardbtoazuremysql_connectionsecurity

I have not taken the time to make SSL work, but I’m open to suggestions in that area! I did find the two following pages that are of interest:

After saving, verify the connection by starting up the MySQL Workbench. When starting up, create a connection. That is pretty straightforward; the connection details can be found in the “Connection strings” tab in the MySQL Instance the Azure Portal. I used the following settings:

  • Connection method: Standard (TCP/IP)
  • Hostname: [mysql instance name].mysql.database.azure.com
  • Port: 3306
  • Username: [username]@[mysql instance name]
  • I stored the password in the Vault

movewordpressfrommysqlcleardbtoazuremysql_mysqlwbconn

Now click “Test connection” in the “Manage Server Connections” dialog box to verify if it works.

Connect MySQL Workbench to ClearDB database

Having setup my target instance, I moved forward and connected the MySQL Workbench to the ClearDB instance. The settings for this connection can be found in the “Application settings” of the Azure App Service. There is a section specifically for Connectionstrings. I overrode the security message to uncover the connectionstring.

movewordpressfrommysqlcleardbtoazuremysql_wpappsettconnstring

I created a new connection and used the following settings in MySQL Workbench:

  • Connection method: Standard (TCP/IP)
  • Hostname: eu-cdbr-azure-west-a.cloudapp.net (this applies to their Europe West datacenter, different for other datacenters)
  • Port: 3306
  • Username: username from connectionstring
  • I stored the password in the Vault

Again, I tested the connection and it was successful.

Migrating the database

The MySQL Workbench contains a great tool called “Database Migration Wizard”. It assists in duplicating a database including data to another instance. From the MySQL Workbench, click the menu “Database” and select “Migration Wizard”;

movewordpressfrommysqlcleardbtoazuremysql_dbmigrate01

Click “Start Migration” at the bottom of the page. Then the source and target needs to be selected. Use the connections configured before and set the ClearDB connection as source and the Azure MySQL DB as target. Most steps can be run with the defaults in place. The exception are the ones mentioned below;

In the “Schema selections” page do NOT select “information_schema” but only the entry with the name of the database.

movewordpressfrommysqlcleardbtoazuremysql_dbmigrate02

Furthermore it is advisable to set the amount of worker processes to 1 in the “Data Transfer Setup” page, if you have a free tier ClearDB instance. The first time I ran the migration, it stopped halfway, indicating connections were dropping because of resource usage.

movewordpressfrommysqlcleardbtoazuremysql_dbmigrate03

Setup the Azure App Service to use the new database

To change the App Service to use your new database, you’ll need to change your connection string. Retrieve the new connection string from the “Connection strings” blade in your MySQL Instance blade (use the one marked as “Web App”).

The change is very simple. It is the connectionstring that exists in the “Application Settings” blade in the “App Service” service blade in the Azure Portal. Click the link “Show connection string values” and change the Data Source, the User Id and the Password (if applicable) and don’t forget to save. After this action, you might need to restart your Azure App Service. Now you can check if your website still works!

movewordpressfrommysqlcleardbtoazuremysql_dbmigrate04

Important notes

Keep in mind that I followed this procedure for a website that is not really important or frequently visited, so I could afford some downtime. If the case is different for you, it’s good to think about:

  • Having a test environment
  • Database backups and tested disaster recovery scenarios
  • Fallback scenarios (if the migration fails)
  • Putting WordPress in maintenance mode

In my case, I also found the database connection settings in the wp-config.php. If your database connection doesn’t work after changing the connection string, you might try this;

  1. From the Azure App Service, click “Advanced Tools” and then click “Go”
    movewordpressfrommysqlcleardbtoazuremysql_dbmigrate05
  2. It will open a new window with a great power tool, “Kudu” which will give you file system access in your Azure App Service instance. From the menu select “Debug console” and then “PowerShell”
    movewordpressfrommysqlcleardbtoazuremysql_dbmigrate06
  3. In the directory structure, navigate to site > wwwroot
  4. Click the edit button for the file “wp-config.php”
    movewordpressfrommysqlcleardbtoazuremysql_dbmigrate07
  5. Edit the settings which can be seen in the screendump below. In your case, the variables $connectstr_[…] might actually be values
    movewordpressfrommysqlcleardbtoazuremysql_dbmigrate08
  6. Save the file and restart the Azure App Service

I hope this helps other people too!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s