Migrate your WordPress local MySQL to Cloud SQL

Regardless of where you hosting your WordPress site, you can use Cloud SQL to be your backend database. In this How-to, I will explain the steps I took to migrate from the WordPress local MySQL to Cloud SQL.

WordPress is using MySQL as the default database to store pages, post’s and other data, when you set up a WordPress site, MySQL is installed on the same server you are going to run your WordPress site on. The connection in wp-config.php to MySQL is set up as a localhost connection.

Migrating data

The first task you need to do is to migrate data from your MySQL localhost instance, anytime you work with a database you need to enable maintenance mode for your website. In WordPress, the easiest way is to get a maintenance mode plugin. After you have your website in maintenance mode, you can start the migration. There are four steps you need to take, they are:

• Make a copy of your current WordPress database.
• Create a Cloud SQL instance
• Upload the data to the new Cloud SQL
• Change WordPress database config

Make a copy of your current WordPress database.

There are a number of ways you can take a copy of your WordPress database.

Command Line

If you have access to the server were your WordPress site and database exist, then you can use the command line to make a dump of your database.

# sudo mysqldump wordpress -u wordpress -p > torbjornzetterlund.sql

After your run that command, you get a file named torbjornzetterlund.sql, which is a copy of your WordPress MySQL database, the next step would be to download the torbjornzetterlund.sql from your server to your laptop. As I run on GCP it’s a simple task to download a file. If you are hosted somewhere else, then you may have to use a different tool to download the file e.g. SFTP

There are many ways you can specify options in mysqldump, if you want to be playing it safer than what I did you can use this.

mysqldump -u username -p username --single-transaction --quick --lock-tables=false > db1-backup-$(date +%F).sql

WordPress backup Plugin

Another option you have to get your database is the use of a WordPress Plugin for backing up your database, there are many options available. You may already have one, you can read this article that compares 9 different plugins

MySql Workbench

My favorite for this job is MySQL Workbench – I can use it to export data from my “old” WordPress database and to import it to my “new” Cloud SQL database. I prefer this approach as I get a copy of the database and can continue taking backup of my Cloud SQL after migration.

Google Cloud SQL migration

Google offers an option in Cloud SQL to migrate a MySQL database to Cloud SQL. The Google Cloud SQL Migration Assistant can help you migrate a MySQL database into Cloud SQL. Different types of migration offered by Google.

• On-premises to Google Cloud SQL
• External cloud provider to Google Cloud SQL Learn more
• Google Cloud Project to Google Cloud Project

Setting up Cloud SQL

I have chosen to use Google Cloud SQL, you can also opt for another cloud provider Cloud SQL service, my instructions are based on setting up Google Cloud SQL. From the Google Cloud Platform (GCP) Navigation Menu I select SQL.

If this is your first time, you will a message box that asks you to either create a new instance or to migrate from a current instance. Click to create a new instance, select MySQL

Fill in the details. Pay attention to Region and Zone so you get the correct region for your data.

Another important parameter is connectivity, you need to choose Public IP and then you have to add the IP address of your server, this is done so you are allowing specific networks to access your Cloud SQL server. This is not the most secure connection, you may want to set up a Cloud SQL Proxy to make your cloud SQL secure.

When you have completed the setup you would get a dashboard of your Cloud SQL instance, there are options to make changes, add connections, add database users, make backups, etc.

Upload the backup data to the new Cloud SQL

Now you can connect MySQL Workbench to your Google Cloud SQL, you may first have to find your computer’s IP address and add it to the trusted connection in your Cloud SQL. When you have established the connection, the next step is to import your database backup to your new Cloud SQL instance.

Change WordPress database config

Now you have your data in the Cloud SQL, there is only one step left and it’s to change your wp-config.php database configuration to point to your new Cloud SQL instance. In the wp-config.php change the hostname, if you have created a different user and password change that, and also if you changed database name you have to change that.

// ** MySQL settings - You can get this info from your web host ** //
/** The name of the database for WordPress */
define('DB_NAME', 'wordpress-single');

/** MySQL database username */
define('DB_USER', 'root');

/** MySQL database password */
define('DB_PASSWORD', 'root');

/** MySQL hostname */
define('DB_HOST', 'localhost');

Save your wp-config.php file and the last step is to disable maintenance mode that we set at the beginning of the migration tasks. Your site should come back without any issues, now you are running your database in Cloud SQL.

Pricing

The pricing for cloud SQL is pay as you go, for my site I pay about 7 Euro a month using Cloud SQL as my database, this is on top of the cost of hosting the WordPress website.

Conclusion

What you pay for with a Cloud SQL database are features you don’t get with your localhost setup, you get high availability, replication, backups, etc. You save time that you would have to spend on doing the maintenance yourself. It’s worth it.

The migration was straight forward, no need to set up any servers as Cloud SQL runs in a serverless environment, it took me an hour to migrate.

With Cloud SQL you can from connecting BigQuery with your Cloud SQL. BigQuery creates an external connection and with federated queries, you can query your Cloud SQL to create views that you can use for Data Studio Reports or for analyzes.