Tag Archives: MySQL

Migrating blog database from ClearDB to Azure DB for MySQL

Introduction

All of us running our blogs on WordPress have some knowledge of MySQL, which is WordPress’ database back-end. I run my WordPress blog (the one you are reading now) in Azure App Service. We used to be able to create MySQL databases in the Azure portal directly. This was made possible through a partnership between Microsoft and ClearDB. Providing an offering running MySQL in Azure datacenters, but managed by ClearDB. You could see and manage your MySQL resources in the Azure portal, and even pay for your MySQL usage with your Azure credit. This option disappeared some time ago, at which time we all had to start managing our MySQL databases from the ClearDB management portal. At the same time the MySQL resources disappeared from the Azure portal. Since H2 2017 Azure DB has been able to natively support MySQL. This is now a native Azure resource on the same level as regular Azure DB MS SQL. With this new offering I can once again run, manage, and pay for, all my blog resources on Azure. This posts shows how I migrated from ClearDB MySQL to Azure DB for MySQL.

Migration

The steps I followed are available here, and I will not repeat them:

https://azure.microsoft.com/en-us/blog/cleardb-migration-to-azure-database-for-mysql/

There are a few things I did differently.

Database Pricing Tier

I selected the smallest possible database SKU; 1 vCore and 5 GB memory. This seems to be adequate for the traffic my site is receiving.

Database Connection

The Connection strings, under Application settings in the App service did not seem to do anything. After I changed it I started receiving “Error connecting to database” messages in my browser. WordPress stores its database connection settings in wp-config.php, and you need to edit this file. I recommend Kudu. Wp-config.php is in the wwwroot folder. You need to restart your app service after you change it. You will find Kudu under Advanced Tools in your App service. The values you need to change are:

  • DB_USER
  • DB_PASSWORD
  • DB_HOST

Connection Security: Firewall

I recommend to follow the Microsoft blog post author’s setup of allowing only the outbound IP addresses of your Azure App Service in your database firewall rules. You might be tempted to enable the option Allow access to Azure services in the firewall setup, but I recommend against it. This setting allows connections from all Azure public IPs, not just the ones you use. This can potentially open you up to denial of service or other attacks from malicious actors who are also using Azure. If you do decide to use this option, set a good password and monitor your database for connections from IPs you do not use.

Connection Security: SSL

The Microsoft blog post author disables SSL Enforcement on his database, but I wanted to keep that enabled. My WordPress setup did not have the required settings to allow this so I had to add the following to my wp-config.php file:

define( 'MYSQL_CLIENT_FLAGS', MYSQLI_CLIENT_SSL | MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT );

This will add the flag to use SSL for database connections and also turn off verification of the database certificate. With this added to my WordPress config I could enable Enforce SSL connection for my database.

I have seen some posts on Server Fault about how using SSL between your front-end and database impacts performance. I have yet to see some detrimental effects of this setting, but will monitor it in the coming days and update this post with my findings, if any.

Azure DB MySQL databases use public certificates signed by DigiCert. More information about SSL support is available here:

https://docs.microsoft.com/en-us/azure/mysql/howto-configure-ssl

This would suggest that I did not need to disable verification of the database server certificate. I want to investigate if I can disable that setting and have certificate validation enabled. More to come on this topic.

There is also a WordPress plugin to handle database SSL connections.

Conclusion

The database migration was very quick. The site seems to be running fine, and I have yet to find any issues. Please sound off in the comments if you experience anything wrong or strange in the coming weeks. I plan not to make any more changes in the immediate future to make sure any issues are related to the database migration and not something else I did. When all is fine I will delete my ClearDB database. Thanks for reading!

/M