How to Transition ClearDB MySQL to Microsoft Azure MySQL In-App

Running WordPress sites in Azure App Services is easy and cost-effective. WordPress requires a MySQL database. Today, Azure offers the MySQL In-App Service Plan to which provides PHP and everything necessary for WordPress. Very cool! In the days (and years) before the Microsoft Azure MySQL In-App, the easiest option was to use one of the free ClearDB MySQL plans. It wasn’t free. It was a one-time $10 fee. Great deal!!

Recently, the ClearDB free plans were phased out, and a low cost $10/month options was offered as a seamless upgrade to customers. Granted it’s a low price, but, Azure MySQL In-App is free, and my WordPress application is already running in Azure. So, how do I transition from ClearDB to free?

Step One: Backup your existing database

mysqldump –add-drop-table -h YourServersIPOrHostName -u YourUserName -p YourWordPressDB > wordpress-backup.sql

To enable MySQL in-app for the existing app go to the app service, and locate MySQL In-App.

Microsoft Azure mySQL settings

After enabling the Azure MySQL feature, click on the Manage icon on top and it will bring up the phpMyAdmin page. MySQL in-App cannot be accessed remotely.

You can see your ClearDB database because of the connection string in the Application settings under the App Service. Note: The page shown above is from a screenshot taken after the cut over to In-App.

Next, you want to change your connection string to use the new MySQL In-App.

To determine the connection string, use the Console App under development tools.

Microsoft Azure mySQL console app

Type in:

cd \home\data\mysql

more MYSQLCONNSTR_localdb.txt

It will display the connection string for the database.

Then we need to update the Application settings connection string with this string.

Access the application setting’s connection string. Set the Name to MYSQLCONN_localdb, Value to the connection string from the console, and set the database to MySQL from the drop-down options. Save it.

Step Two: Update the WordPress config file as follows:

/*Add at the beginning of the file*/
$connectstr_dbhost = '';
$connectstr_dbname = '';
$connectstr_dbusername = '';
$connectstr_dbpassword = '';

foreach ($_SERVER as $key => $value) {
  if (strpos($key, "MYSQLCONNSTR_localdb") !== 0) {
  $connectstr_dbhost = preg_replace("/^.*Data Source=(.+?);.*$/", "\\1", $value);
  $connectstr_dbname = preg_replace("/^.*Database=(.+?);.*$/", "\\1", $value);
  $connectstr_dbusername = preg_replace("/^.*User Id=(.+?);.*$/", "\\1", $value);
  $connectstr_dbpassword = preg_replace("/^.*Password=(.+?)$/", "\\1", $value);

// ** MySQL settings - You can get this info from your web host ** //
/** The name of the database for WordPress */
define('DB_NAME', $connectstr_dbname);

/** MySQL database username */
define('DB_USER', $connectstr_dbusername);

/** MySQL database password */
define('DB_PASSWORD', $connectstr_dbpassword);

/** MySQL hostname : this contains the port number in this format host:port . Port is not 3306 when using this feature*/
define('DB_HOST', $connectstr_dbhost);

Note: It is best practice for to use the environment variable instead of the actual values.

Step Three: Import your blog data

Next, you need to import your blog data into the MySQL database. Access the new MySQL database from the phpMyAdmin.

Note that my environment variable uses localdb for the database name, if you follow the snippet given, you will need to restore the database to the localdb or you can do change the config and use your database name.

You also may want to make sure that your database collation is ‘utf8_general_ci’ first.

Restore the database, from the MySQL In-App panel>Manage> phpMyAdmin click on the ‘Import’ tab and upload the backed up sql you made previously. Note if your backup is over 8MB you will need to split it up.

Leave a Reply

  • Microsoft SQL Server
  • Microsoft SQL Server
  • Microsoft Office 365
  • Microsoft SharePoint
  • Microsoft Windows Azure