This blog is hosted on my Azure subscription, and was using ClearDB, who provides MySQL instances for Azure websites. I quickly found out however that the ClearDB instances are only free up to 20mb, at which point they put a write lock on your database and ask for $120/year. For almost the same price I can add another Extra Small Ubuntu VM to my subscription and run all manner of databases without similar size constraints, but I wasn’t sure how to migrate the database over. Turns out it was pretty easy. So, if you want to move your ClearDB into a new MySQL installation:
Setup a new Ubuntu VM in Azure
I’ll leave the details to you. I use 14.04 LTS and an Extra Small instance. After it is created I use Putty to SSH to the VM.
Install MySQL on the VM
This should be as simple as the below apt command. You’ll be prompted to set a root password.
sudo apt-get install mysql-server
Migrate the Database from ClearDB to Local
So this is the fun part. Go to the Azure portal and find your existing ClearDB connection string. For Azure Websites you can find it under the “Configure” tab when looking at the website. Mine looked like this:
Database=cleardbname;Data Source=us-cdbr-azure-northcentral-a.cleardb.com;User Id=clearuserid;Password=clearpassword
Now (from our SSH session to our VM) we’ll export that database using mysqldump. Pull the relevant bits from the connection string, and you’ll be prompted for a password.
mysqldump -h us-cdbr-azure-northcentral-a.cleardb.com -u clearuserid --password clearpassword > ~/dump.sql
And now we’ll create a new database to import it to
mysqladmin create newdatabasename -u root --password
And import it (again you’ll be prompted for a password)
mysql -u root --password newdatabasename < ~/dump.sql
Now make a new user and configure permissions as appropriate. Something like this:
mysql -u root --password > CREATE USER 'wordpress'@'%' IDENTIFIED BY 'somepassword'; > GRANT ALL PRIVILEGES ON newdatabasename.* TO 'wordpress'@'%'; > exit
Configure Azure VM Endpoint
You’ll need to go to the Endpoints configuration of your Azure VM in the portal and add an endpoint for MySQL – it will be port 3306. This exposes that port to other services.
Update Connection String
Now you just need to update your connection string, however it isn’t quite as easy as it may seem. The connection string that shows up in your website configuration in the Azure UI doesn’t actually appear to be used anywhere. You’ll instead need to edit the wp-config.php files in your wordpress install.
So FTP into your Azure Website (FTP information should be available in your dashboard). Find wp-config.php, and you should see definitions at the top of the file that look something like this:
// ** MySQL settings - You can get this info from your web host ** // /** The name of the database for WordPress */ define('DB_NAME', 'dbname');
/** MySQL database username */ define('DB_USER', 'username'); /** MySQL database password */ define('DB_PASSWORD', 'password'); /** MySQL hostname */ define('DB_HOST', 'host');
Simply make the appropriate updates, save the file, and you are done!