Google Cloud: Migrate a MySQL Database to Google Cloud SQL By Creating a Database Dump

Lab Topic:

• Create a Google Cloud SQL instance and create a database
• Import a MySQL database into Cloud SQL
• Reconfigure an application to use Cloud SQL instead of a local MySQL database

Lab Scenario

Your WordPress blog is running on a server that is no longer suitable. As the first part of a complete migration exercise, you are migrating the locally hosted database used by the blog to Cloud SQL.

The existing WordPress installation is installed in the /var/www/html/wordpress directory in the instance called blog that is already running in the lab. You can access the blog by opening a web browser and pointing to the external IP address of the blog instance.

The existing database for the blog is provided by MySQL running on the same server. The existing MySQL database is called wordpress and the user called blogadmin with password Password1*, which provides full access to that database.

Your challenge

You need to create a new Cloud SQL instance to host the migrated database. Once you have created the new database and configured it, you can then create a database dump of the existing database and import it into Cloud SQL. When the data has been migrated, you will then reconfigure the blog software to use the migrated database.

For this lab, the WordPress site configuration file is located here: /var/www/html/wordpress/wp-config.php.

To sum it all up, your challenge is to migrate the database to Cloud SQL and then reconfigure the application so that it no longer relies on the local MySQL database.

Tips and Tricks

Google Cloud SQL – How-To Guides: The Cloud SQL documentation includes a set of How-to guides that provide guidance on how to create instances and databases, and how to connect applications to those databases.

WordPress Installation and Migration: The WordPress Codex provides information on how to install, configure, and migrate WordPress sites. You will find the instructions on how to create and prepare databases for use with WordPress here.

Check if VM instance containing Database is available

1. Go to Navigation menu > Compute Engine > Vm Instances
2. You will see the vm called “blog” present there. This is the vm on which our database exists.

3. Load the webpage by clicking the Public ip of the VM.

Create a Managed SQL database Instance in GCP

1. Go to Navigation menu > Databases > SQL
2. Click on Create Instance

3. Select MySQL from the options below.

4. Fill the details as below. Keep other options as is it is.

• Instance ID: demo
• Password: password

5. Click “Create Instance”. It will take couple of minutes for sql instance called “demo” to be created.

Create a Database Dump of existing Database

1. Go to navigation Menu > VM instances.
2. SSH to VM called “blog” which is hosting the existing database.

3. Now Create Database dump as below:

mysqldump --databases wordpress -h localhost -u blogadmin -p \
--hex-blob --skip-triggers --single-transaction \
--default-character-set=utf8mb4 > wordpress.sql

When prompted, put password as : Password1*

4. Export Project ID to a variable:

export PROJECT_ID=$(gcloud info --format='value(config.project)')

5. Create a Storage Bucket by using below command:

gsutil mb gs://${PROJECT_ID}

6. Copy the wordpress.sql file to storage bucket:

gsutil cp ~/wordpress.sql gs://${PROJECT_ID}

Verify that the wordpress.sql file has been copied to storage Bucket

1. Go to Navigation menu > Cloud Storage
2. Click on the Bucket with name of Project Id, you will see “wordpress.sql” file there in the bucket.

Create new database in Demo instance, setup User Authentication and Network

1. Go to the Navigation Menu > Databases > SQL
2. Click on “demo” Instance ID

3. Note Down the Public IP for the instance

4. Now Click on Databases Tab from the Left. Click on Create Database.

5. Fill details as below. Click on Create.

6. You will see our newly created database called “wordpress”.

7. Now Go to “Overview” from the left and click on IMPORT.

8. Fill in the details like below. for Bucket-name/File-name, click on browse and point to the wordpress.sql file in the storage bucket. Select destination database as wordpress. Click IMPORT.

9. Now go to “Users” from the left screen and click on “ADD USER ACCOUNT”.

10. Now put username and password as below. Keep other details as it is click ADD

11. You will see blogadmin user has been added.

12. Now go to “connections” tab from the left. then click on “Add Network”

13. Fill the details like below. Click done. Click Save.

[ The network depends on the PUBLIC IP of the VM “blog”]

14. Upon Successful completion you will see something like below:

Modifying “wp-config.php” to point to the Cloud SQL instance

1. Now go to compute engine > VM instances. SSH to blog vm.

2. Change to /var/www/html/wordpress:

3. Stop the mysql service.

4. Modify the wp-config.php file and update the new MySQL instance IP addess.

It should look like below:

From where did we get the MySQL ip ? well, we got it from here:

5. Restart the apache service.

6. Check the mysql service. Its still down.

7. Refresh the webpage, its still responding.

Congratulations !!! We have successfully migrated our mysql database to Google Cloud SQL

Happy Learning !!!