How to configure WordPress to use CloudSQL?

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!!!).
CloudSQL menu Create a new CloudSQL instanceSelect second generation instance type
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.
CloudSQL instance creation form

Prepare the database

OK, our new instance is up and running.
CloudSQL instances listClick 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.
Databases view tab


Create a new database window

So we have a new database. Let’s now add a new user. Go to the “Access Control” tab and then to “Users” submenu. Now “Create user account” with username and password.
Database users list


Create a new database user form

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.
Add an authorised network

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.
Deployment details


phpMyAdmin login page

Now on the left-hand side select wpdatabase and then on the top menu click Export and then Go.
phpMyAdmin pre-export


Export the data
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.
Create a new bucket

Upload the database dump

Now simply upload the downloaded DB dump. Click on “Upload Files“, select the file you want to upload – wpdatabase.sql in this case.
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.
Database import menu Database SQL dump browser


Database import options

To make sure everything went well, go to Operations tab and you should see something like this
Database operations list

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.
GCE SSH button
This will open a new window with a terminal view and active SSH session.
GCE SSH session

Update WordPress configuration

We need to update a wp-config.php file. Type:

This will open the file as a superuser, using Nano editor. It will look like this:
wp-config.php

Replace the values with your credentials and IP to be something like:

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.

Remove phpMyAdmin

Still being in the SSH window run:

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.
phpMyAdmin DB removal phpMyAdmin DB removal

Wait for a while and phpMyAmin will be completely removed from the system.

Remove MySQL server

To remove the MySQL server run:

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.

Conclusion

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.

Related Post


2 thoughts on “How to configure WordPress to use CloudSQL?

Leave a Reply

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