Switching to Azure MySQL In-App from ClearDB

When I first moved my blog to Azure, there is no MySQL provided by Azure themselves, but from their partner ClearDB. But now since Microsoft already announced that they are providing Azure MySQL In-App server, though it is still in preview, I’m switching to Azure MySQL In-App, away from ClearDB.

The reason why I’m moving away from ClearDB is not because it is expensive, for my use case it is very affordable, I’m moving away because even with my Azure credit, I still have to pay for the service separately, making me not able to fully utilize my credit.

Enabling MySQL in-app for the existing app is really simple. Go to the existing app service, and locate the MySQL In App (preview), yes, at the time of writing, the feature is still in preview mode, and not encourage to be use for production, and here I am using it for production, because I’m running a single digit traffic blog.

After enabled the MySQL feature, click on the Manage icon on top, and we will be bring to phpMyAdmin page, since the MySQL current not able to be access remotely. Once in the phpMyAdmin, I can immediately see my database, because I have the connection string in the Application settings under the App Service.

Being impatient to make sure the new MySQL is up before deleting the existing database resource, I bring my blog down and phpMyAdmin can no longer access to the database. So the first thing to do is to change my connection string to use the new MySQL one.

To find the connection string for the new MySQL

go to the Console and do this

cd \home\data\mysql 
more MYSQLCONNSTR_localdb.txt

and it will display the connection string for the database.

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

Set the Name to MYSQLCONN_localdb, Value to the connection string we get via the console, then set to MySQL from the drop down options and save it.

By setting this one, I can then access the phpMyAdmin without problem. After that I have to update the WordPress’s config file with the following:

/*Add at the begining of the file*/

$connectstr_dbhost = '';
$connectstr_dbname = '';
$connectstr_dbusername = '';
$connectstr_dbpassword = '';

foreach ($_SERVER as $key => $value) {
 if (strpos($key, "MYSQLCONNSTR_localdb") !== 0) {
 continue;
 }
 
 $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);

It is the best practice for using the environment variable instead. (Source)

After that I’m restore my old database to the new MySQL instance, note that the environment variable always point to localdb for database name, if follow the snippet given, so if I want to follow the best practice, I will need to restore the database to the localdb instead of my own, or I can do it the traditional way with the config and use my own database.