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.
|
1 |
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:
|
1 |
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.
|
1 |
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
|
1 |
mysqladmin create newdatabasename -u root --password |
And import it (again you’ll be prompted for a password)
|
1 |
mysql -u root --password newdatabasename < ~/dump.sql |
Now make a new user and configure permissions as appropriate. Something like this:
|
1 2 3 4 |
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:
|
1 2 3 |
// ** MySQL settings - You can get this info from your web host ** // /** The name of the database for WordPress */ define('DB_NAME', 'dbname'); |
|
1 2 3 4 5 6 7 8 |
/** 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!
Hiya,
a couple of things:
the addition “gt” requires the installation of the genometools package (not installed as standard with the Ubuntu image) and the lt addition requires looptools (same here) – either way, I am not sure what either of the tools are supposed to do here 🙂
for the mysqldump i kept getting the error, that dump.sql didn’t exist – seems you’re missing a “>” in the command; Also, the gt part didn’t work for me either, due to the lack of further commands.
What did appear to work is the following command, taken from the clearDB site:
mysqldump –single-transaction -u clearDBUser -p -h cleardbHost DBName > dump.sql
And for the import:
$ mysql -u username -p -h localhost database_name < dump.sql
However, the transition hasn't worked so far, my wordpress blog refuses to connect despite following the rest of your guide .(