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.
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).
- 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.
- 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.
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).
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.
login as: |username|
Once you’re logged in to your virtual machine, you want to install MySQL.
sudo apt-get install mysql-server
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.
sudo nano /etc/mysql/my.cnf
Scroll down to the bind-address section and change the IP address to the server’s internal IP address identified from the Azure Dashboard.
bind-address = 10.xxx.xxx.xxx
Press Ctrl+X, Ctrl+Y to save the configuration file. Restart the MySQL instance to confirm the configuration changes.
sudo service mysql restart
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.
CREATE DATABASE mydatabase;
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).
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.
CREATE USER 'username'@'%' IDENTIFIED BY 'password';
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.
GRANT ALL ON database.* TO 'username'@'%' IDENTIFIED BY 'password';
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!