Connect WordPress to Custom MySQL with Azure VM

Connect WordPress to Custom MySQL with Azure VM

The title is a bit of a mouthful, but I promise it’s not as crazy as it seems. If you’re anything like me, you became interested in cloud computing and stumbled upon Microsoft’s Azure platform. There are plenty of wizards built directly in to the Azure Dashboard which allow you to automatically create and setup websites, virtual machines, and databases without ever needing to perform extra configuration. However, there are some cases where you might want to setup a custom database for a specific purpose. WordPress is one great example which may require you to setup your own MySQL databases in a virtual machine. In fact, this guide assumes that you already have a WordPress Azure Website setup.

Investigating the “Cheaper” Options

My first project was to migrate an existing WordPress setup and migrate it in its entirety (database included) to Azure. At first glance, this is seems quite simple, but it’s a bit deceiving. When you go through the steps to create a new Azure Website, you should notice that there is a template for a few different WordPress installs. If you follow through with creating the default WordPress “app” (as Microsoft likes to call it), you will be confronted with the options of “Creating a new MySQL database” or “Using an existing MySQL database.” Both options are not very good as you will find out in the later steps.


Microsoft teamed up with a third party (ClearDB) to manage all “auto” MySQL instances. While I’m sure ClearDB is an entirely respectable group of people and has great infrastructure to support users in the Azure ecosystem, what you get for what you pay is a little annoying. Combine this with the limited customization and huge price discrepancies between the tiers, and you’ve got a recipe for sadness. You can see from the prices below that running a WordPress blog on the non-free options can get expensive rather quickly.

Azure3
Honestly, I tried the free tier, but it’s really not meant for production environments (even your simple little blog). You will notice slowdowns in queries, and even plugins complaining about slow response times from the database. Additionally, the maximum connection count is extremely limiting, which caused all sorts of “Connection could not be made” errors in WordPress.

Creating and Configuring the VM

So what’s our recourse then? How can we get MySQL setup on Azure without paying through the nose for each database? For full details on taking advantage of virtual machine in Azure, check out the pricing page. This article will focus on using the Basic A0 Linux VM (~$13/mo). Not only can you host your own MySQL databases on the virtual machine, but you can also setup your own entire LAMP stack (and more) if you feel so inclined. For the price you pay, it definitely beats out having to deal with the ClearDB option above.

  • Start from the Azure Dashboard, click New > Computer > Virtual Machine > From Gallery.
  • Select the latest Ubuntu Server that you are comfortable with (in my case, it’s 14.04 LTS as of the time of this writing).

Azure4

  • Setup the virtual machine configuration by entering all values to your liking.
    • Select the version release date that you want. This will indicate which release of patches you want to use.
    • Enter a virtual machine name.  You can’t change this later, so make sure it’s meaningful.
    • Select the tier that you want to use (refer to the pricing guide for more information).
    • Choose the size that you want to use (refer to the pricing guide for more information).
    • Enter a user name that you will use to remote in to the virtual machine via SSH.
    • Either upload an SSH certificate or provide a password by which you will login remotely.

Azure5

  • Continue to the next step of setting up your virtual machine configuration
    • Choose to create a new cloud service or use an existing one. Cloud services are containers for virtual machines that can be used as load balancing agents. For example, you could have multiple virtual machines in a single cloud service for some load balancing.
    • Enter a DNS name by which you will connect to your virtual machine from external sources.
    • Select which region / group / network this virtual machine will belong to. This is important because Azure will charge you for any outgoing data from your virtual machine to places that are outside of the region / group / network. For example, transferring data from your virtual machine to your website (both of which are hosted in US EAST) will incur no charges, but transferring data to your local machine is charged. As of this writing, I believe the first 5 GB of transfer beyond the VM’s region is free.
    • Select which storage account will be used to store the VM’s disk on. Be aware that VM disks are stored in blob storage and will incur extra costs.
    • Don’t bother with Availability Sets for now. This would enable you to provide fault tolerance, but only complicates the setup for now.

Azure6
After the VM is done provisioning and creating, you should see an entry like below under the Virtual Machines section of the dashboard. I encourage you to click through the Storage Account associated with the VM as you will be responsible for paying for the storage. The storage account created for my VM started out around 29 GB (which costs about 50 cents per month).

Azure7

Installing and Configuring MySQL

Since we setup a Linux VM, there will be no GUI to rely on. Begin by downloading PuTTY.

In the Host Name (IP Address) section of the PuTTY Configuration, enter the DNS name that you setup on your VM.
Set the port to 22 and the connection type to SSH.
When you login, use the user name and password that you setup on your VM.

Once you’re logged in to your virtual machine, you want to install MySQL.

By default, the MySQL configuration will only listen for connections on localhost. This is OK if we are connecting from a local web server like Apache, but this guide assumes you’re using the Azure Websites WordPress setup. In that case, you will need to modify the MySQL configuration to accept remote connections.

Scroll down to the bind-address section and change the IP address to the server’s internal IP address identified from the Azure Dashboard.

Press Ctrl+X, Ctrl+Y to save the configuration file. Restart the MySQL instance to confirm the configuration changes.

Create a new database which can be used to store your incoming WordPress tables in the following steps. Make sure to replace mydatabase with whatever you want to name your database.

You should now be able to perform the export/import process with MySQL Workbench from your existing WordPress to this new MySQL instance. In my case, I exported all tables from ClearDB and imported all tables to my new MySQL database in the virtual machine.

Connecting Everything Together

Finally everything is setup and configured. The last steps are simply connecting everything together so it all works. We are now going to point the existing WordPress installation to the new MySQL instance in our VM. This requires opening some endpoints, creating an acceptable user in the MySQL database, and changing a WordPress configuration file.

Go to your virtual machine in the Azure Dashboard. Click the Endpoints tab and add an endpoint for your MySQL instance (default is port 3306, but you can change that in your MySQL configuration).

Azure8
Log back in to your virtual machine if you aren’t still in via PuTTY. Log in to your MySQL instance as the ROOT user that you setup during the installation. Create a new user that can be used by WordPress to connect to your new database. When following the below command, make sure to replace username and password with your values.

Grant the necessary privileges for WordPress to perform actions in the database. You can be more restrictive (and probably should) than this example indicates. Again, make sure you replace the database, username, and password with your own values.

And finally, flush out the privileges to make sure that MySQL applies the new values.

The final step requires you to FTP in to your Azure Website WordPress installation and update a .php file containing the database connection information. Begin by downloading FileZilla (or whatever FTP client you like).
Download your Azure Website publish profile from the Azure Dashboard. You see will a link to it on the right hand side when clicking in to your Azure Website. In that publish profile, reference the FTP host, the username, and the password. These are the credentials you use to connect to the FTP. Once connected, navigate to /site/wwwroot/. Edit the wp-config.php file to update the following sections.

Again, make sure to replace these values with your correct settings. You should now be directed to the proper database when navigating the administrator dashboard of your WordPress blog. You’re now running on your own custom database in a virtual machine!

7 thoughts on “Connect WordPress to Custom MySQL with Azure VM

  1. This is great. I actually already did this. I had no problems with my test WordPress site either. However, after I brought over another blog that gets very little traffic, I started getting the dreaded “error establishing a database connection” message.

    I’ve read in a couple places that Azure starts throttling connections at around 100+. Those same articles recommend installing the Persistent Database Connection Updater plugin. I installed that (though I had to make the first modification to the /wp-includes/wp-db.php script myself), verified I have persistent connections now, but I’m still hitting the wall soon after restarting MySQL on my Ubuntu server. I can watch the total connections reach over 100 via MySQL Workbench’s “Client Connections” view (connecting via an SSH tunnel with it). I’m stumped.

    Have you run into this? Do you have any suggestions?

    Thanks,

    -Matt

    https://wordpress.org/plugins/persistent-database-connection-updater/

    1. Hey Matt. Now that you mention it, I have seen that error and could only resolve it by restarting the MySQL service on the VM. Can you link me to the articles you mentioned regarding Azure throttles? I’m not familiar with such limitations but would love to learn more about it.

      1. Sure thing, Justin. I’ve been struggling with this even after taking the steps these articles advise. I’m going to remove the plugin and set my wp-db.php script back to the stock version to see what happens.

        * https://azure.microsoft.com/en-us/documentation/articles/web-sites-php-enterprise-wordpress/#_configure-your-site
        * http://azure.microsoft.com/blog/2014/05/13/how-to-run-wordpress-site-on-azure-websites/ (search for “Use persistent database connections”)

        1. First thing I notice is that the article mentions using ClearDB (default MySQL provider on Azure) as the database. I know that platform has severe connection limitations from personal experience, so I’m not sure it applies to the custom MySQL in a VM solution. I can’t imagine why Azure would even care or be aware of the number of connections being made to the database.

          And looking at the plugin, the description mentions ClearDB explicitly. Maybe that plugin should only be used for ClearDB solutions?

          1. You are correct. I enabled that plugin after having issues. However, I removed it and will see how things go now. I do run into some issues with a select few plugins whenever running WordPress on Windows. It’s usually with plugins that are not considering a non-Linux system with even some simple tasks. My biggest complaint with Windows and PHP is getting at the heart of 500 server errors. I can never figure out the real cause of those errors without shaving off time from my life.

            You mentioned having some issues that require you to restart your MySQL instance. Is that a common occurrence? Have you tweaked max connections and related memory configuration from the defaults?

            Thanks for the article and your help!

          2. I’ve only had this blog running on Azure for about 3 months now. I’d say in that time I’ve had to restart the MySQL service 3 times (so once a month). It’s annoying for sure, but I haven’t had time to dedicate to debugging the problem. In fact, I’ve never actually found any specific error messages that would help diagnose the problem other than generic HTTP 500 (like you said, very vague).

Leave a Reply

Your email address will not be published. Required fields are marked *