In the previous post of this series we’ve successfully set up a WordPress blog on Google Compute Engine. It’s a very basic solution which would work, but it can be improved. As you know it stores all the files (wp-content/uploads/) and a DB on the same instance. This means that you can easily loose your data and files. You can of course do backups on the machine but you’ll run out of space soon. Remember that having a persistent disk costs you money and doesn’t scale – it’s basically not the best idea to keep all the system parts on 1 virtual machine. In this post I’ll show you how to move the local database to the cloud (using CloudSQL). This will allow you to keep your database in a safer place, do backups and scale if you need to.
WARNING: This post will be a bit long and will contain a lot of images (screenshots). This doesn’t mean it’s hard to achieve the goal. It’s just a lot of clicking (to create DB instance etc.) and other manual things to do. In the end you’ll find that it was actually pretty easy to do this, so please be patient.
What is CloudSQL?
Straight from the docs:
Fully-Managed PostgreSQL BETA & MySQL
Cloud SQL is a fully-managed database service that makes it easy to set up, maintain, manage, and administer your relational PostgreSQL BETA and MySQL databases in the cloud. Cloud SQL offers high performance, scalability, and convenience. Hosted on Google Cloud Platform, Cloud SQL provides a database infrastructure for applications running anywhere.
Create a CloudSQL instance
First thing we need to do is to create a new instance. This instance will contain databases, so you can consider this as a virtual machine which runs dedicated databases. In your GCP console go to SQL page and then “Create instance“. Select MySQL (PostgreSQL is finally in Beta!!!).
Now, give it a name, select the MySQL version (5.7 in this case), set the region (I use us-central), change the machine type to db-f1-micro (the cheapest one). I choose SSD disk as its much more performant than a standard HDD. 10GB should be enough and you can always increase it. Once the form looks fine to you press the Create button and wait for a few minutes.
Prepare the database
OK, our new instance is up and running.
Click on it and you’ll be taken to the instance details. Here you can see some useful stats about it like storage usage, IP, CPU usage and many many more. Now it’s time to prepare our database.
Our current instance has 3 databases which we won’t use as they’re for internal MySQL stuff. We want to create a new database to store our blog data there. In the instance details page go to “Database” tab and create a new one. Give it a name and leave a character set as utf8.
So far we have created a new instance and then added a new database and user. There’s 1 more thing to do. Our new database is now not accessible from the outside. This means that our blog (GCE virtual machine) won’t be able to connect to it. Let’s add our VM IP address to the authorised networks.
In the same place, go to “Access Control” -> “Authorisation” and click on the “Add network” button. Then just name the entry and put your GCE machine IP (How to get your VM IP). Hit save and you’re done.
Configure the blog to use CloudSQL
At this point our database is prepared to accept connections only from our blog VM. We can leave it as it is for a while and focus on configuring the blog. What we’re going to do now is export your blog database, save it on your machine, upload it to Cloud Storage and then use it to import into the new CloudSQL DB.
Export blog’s database
First thing we need to do is to export the current database (the one on our VM). We’ll use a preinstalled phpMyAdmin. In your browser go to http://<blog_IP>/phpmyadmin/
phpMyAdmin is a interface where you can fully manage your database data. We’ll use it to export blog’s data and save it on a local machine.
If you followed my previous post you have the credentials (Menu -> Deployment Manager). Username is: root and the password you should use is listed in the deployment details.
This will export the whole database into a file and download it. You should see a wpdatabase.sql file being downloaded. You can close it now as we won’t need it anymore. Actually, we’re going to uninstall it in the end (why to keep it?).
Upload the database dump to Cloud Storage
Now when we have backed the database up it needs to be uploaded to Cloud Storage. It’s the easiest way to import into CloudSql.
First, let’s create a new Bucket. A bucket is a placeholder for all the directories and files under 1 address. Everything you store must be placed in buckets. They’re the on the top level of the storage structure.
Create a new bucket
Go to Menu -> Storage -> Create bucket. Give it a name (the name must be globally unique so if someone has already used that – you’ll have to choose a different name), select a default storage class (it’s nicely described) and finally Create.
Upload the database dump
Import blog’s database into CloudSQL
So we have our data dump uploaded, it’s time to import it. Go to CloudSQL and then to your Instance details. Next in the top menu click on Import. Browse and locate your .sql file, make sure you selected SQL format, show advanced options and put a DB name (wordpress) and Import. Wait for a few moments.
Go back to the bucket and remove the file – it’s not needed anymore.
Update WordPress configuration with new database
Our new database is finally filled with data and waiting for a connection. Now we need to update our blog configuration. This part will be the most “advanced” because it requires updating a PHP file directly on the VM.
SSH into the virtual machine
Go to the Compute Engine dashboard and locate your VM and press the “SSH” button next to it. I mentioned in the previous post – it’s super easy to connect with GCE virtual machines.
This will open a new window with a terminal view and active SSH session.
Update WordPress configuration
We need to update a wp-config.php file. Type:
sudo nano /var/www/html/wp-config.php
This will open the file as a superuser, using Nano editor. It will look like this:
Replace the values with your credentials and IP to be something like:
// ** MySQL settings - You can get this info from your web host ** //
/** The name of the database for WordPress */
/** MySQL database username */
/** MySQL database password */
define('DB_PASSWORD', 'your DB user password');
/** MySQL hostname */
and save the changes (Ctrl + X then Y then Enter). You’re done! Go to your blog and everything should be working as expected. You have successfully migrated you blog database to CloudSQL.
You could stop at this point and start blogging, but there’s something else we can do.
Clean up the virtual machine
As I mentioned before, we can remove things we don’t use and need anymore. On this blog I’ve removed phpMyAdmin and MySQL server. We now use CloudSQL so there’s no point of keeping it on the VM – this uses resources.
Still being in the SSH window run:
sudo apt-get purge phpmyadmin
and confirm. You’ll be asked if you want to remove a database as well. Highlight Yes and press Enter. Do the same for the next question (removes phpMyAdmin database). Then provide your database password and continue.
Wait for a while and phpMyAmin will be completely removed from the system.
Remove MySQL server
To remove the MySQL server run:
sudo apt-get remove --purge 'mysql-server.*'
and confirm. This will completely remove MySQL server but will keep the client and PHP libraries required to connect to MySQL databases.
Now you’re DONE.
As you can see it wasn’t hard to configure CloudSQL, export and import the databases and finally update WordPress configuration. By doing this you have your database in much safer place, ready to scale if you need. You’ll also have backups so if something goes wrong you don’t loose data.
It’s also worth mentioning that your database is not bounded to the server anymore. You can host your blog wherever you want, move it as you wish but the database will remain as is.
In the next post of this series I’ll describe how to store uploaded files using Cloud Storage. It’s much shorter and simpler to configure.