Migrate a MySQL Database to Google Cloud SQL
Challenge 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.
student_03_2a004b33a597@cloudshell:~ (qwiklabs-gcp-04-2d8ba71b6ea2)$ gcloud compute ssh --zone "us-central1-c" "blog" --project "qwiklabs-gcp-04-2d8ba71b6ea2"
Linux blog 5.10.0-29-cloud-amd64 #1 SMP Debian 5.10.216-1 (2024-05-03) x86_64
The programs included with the Debian GNU/Linux system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.
Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.
Last login: Tue May 28 05:37:42 2024 from 34.87.149.242
student-03-2a004b33a597@blog:~$ ls
wordpress.sql
student-03-2a004b33a597@blog:~$ pwd
/home/student-03-2a004b33a597
student-03-2a004b33a597@blog:~$ exit
logout
Connection to 34.66.17.32 closed.
student_03_2a004b33a597@cloudshell:~ (qwiklabs-gcp-04-2d8ba71b6ea2)$
student_03_2a004b33a597@cloudshell:~ (qwiklabs-gcp-04-2d8ba71b6ea2)$ gcloud compute scp --zone "us-central1-c" blog:/home/student-03-2a004b33a597/wordpress.sql .
wordpress.sql 100% 48KB 77.1KB/s 00:00
student_03_2a004b33a597@cloudshell:~ (qwiklabs-gcp-04-2d8ba71b6ea2)$ ls
README-cloudshell.txt wordpress.sql
student_03_2a004b33a597@cloudshell:~ (qwiklabs-gcp-04-2d8ba71b6ea2)$ gsutil cp ~/wordpress.sql gs://${PROJECT_ID}
Copying file:///home/student_03_2a004b33a597/wordpress.sql [Content-Type=application/sql]...
- [1 files][ 47.6 KiB/ 47.6 KiB]
Operation completed over 1 objects/47.6 KiB.
student_03_2a004b33a597@cloudshell:~ (qwiklabs-gcp-04-2d8ba71b6ea2)$ gcloud compute instances list
NAME: blog
ZONE: us-central1-c
MACHINE_TYPE: e2-medium
PREEMPTIBLE:
INTERNAL_IP: 10.128.0.3
EXTERNAL_IP: 34.66.17.32
STATUS: RUNNING
NAME: lab-monitor
ZONE: us-central1-c
MACHINE_TYPE: e2-medium
PREEMPTIBLE:
INTERNAL_IP: 10.128.0.2
EXTERNAL_IP: 34.133.202.209
STATUS: RUNNING
student_03_2a004b33a597@cloudshell:~ (qwiklabs-gcp-04-2d8ba71b6ea2)$ gcloud compute ssh --zone "us-central1-c" "blog" --project "qwiklabs-gcp-04-2d8ba71b6ea2"
Linux blog 5.10.0-29-cloud-amd64 #1 SMP Debian 5.10.216-1 (2024-05-03) x86_64
The programs included with the Debian GNU/Linux system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.
Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.
Last login: Tue May 28 05:48:08 2024 from 34.87.149.242
student-03-2a004b33a597@blog:~$ sudo service mysql status
● mariadb.service - MariaDB 10.5.23 database server
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
Active: active (running) since Tue 2024-05-28 04:57:10 UTC; 1h 0min ago
Docs: man:mariadbd(8)
https://mariadb.com/kb/en/library/systemd/
Main PID: 3063 (mariadbd)
Status: "Taking your SQL requests now..."
Tasks: 8 (limit: 4691)
Memory: 79.1M
CPU: 1.699s
CGroup: /system.slice/mariadb.service
└─3063 /usr/sbin/mariadbd
student-03-2a004b33a597@blog:~$ sudo service mysql stop
student-03-2a004b33a597@blog:~$ cd /var/www/html/wordpress
student-03-2a004b33a597@blog:/var/www/html/wordpress$ ls
index.php wp-activate.php wp-comments-post.php wp-content wp-links-opml.php wp-mail.php wp-trackback.php
license.txt wp-admin wp-config-sample.php wp-cron.php wp-load.php wp-settings.php xmlrpc.php
readme.html wp-blog-header.php wp-config.php wp-includes wp-login.php wp-signup.php
student-03-2a004b33a597@blog:/var/www/html/wordpress$ sudo nano wp-config.php
student-03-2a004b33a597@blog:/var/www/html/wordpress$ sudo service apache2 restart
student-03-2a004b33a597@blog:/var/www/html/wordpress$ sudo service apache2 status
● apache2.service - The Apache HTTP Server
Loaded: loaded (/lib/systemd/system/apache2.service; enabled; vendor preset: enabled)
Active: active (running) since Tue 2024-05-28 05:58:39 UTC; 8s ago
Docs: https://httpd.apache.org/docs/2.4/
Process: 13580 ExecStart=/usr/sbin/apachectl start (code=exited, status=0/SUCCESS)
Main PID: 13586 (apache2)
Tasks: 6 (limit: 4691)
Memory: 15.5M
CPU: 187ms
CGroup: /system.slice/apache2.service
├─13586 /usr/sbin/apache2 -k start
├─13587 /usr/sbin/apache2 -k start
├─13588 /usr/sbin/apache2 -k start
student-03-2a004b33a597@blog:/var/www/html/wordpress$ history
1 mysqldump --databases wordpress -h localhost -u blogadmin -p --hex-blob --skip-triggers --single-transaction --default-character-set=utf8mb4 > wordpress.sql
2 ls
3 export PROJECT_ID=$(gcloud info --format='value(config.project)')
4 gsutil mb gs://${PROJECT_ID}
5 gsutil cp ~/wordpress.sql gs://${PROJECT_ID}
6 gsutil mb gs://${PROJECT_ID}
7 exit
8 sutil cp ~/wordpress.sql gs://${PROJECT_ID}
9 gsutil cp ~/wordpress.sql gs://${PROJECT_ID}
10 ls
11 pwd
12 exit
13 ls
14 exit
15 sudo service mysql status
16 sudo service mysql stop
17 cd /var/www/html/wordpress
18 ls
19 sudo nano wp-config.php
20 sudo service apache2 restart
21 sudo service apache2 status
22 history
student-03-2a004b33a597@blog:/var/www/html/wordpress$ exit
logout
Connection to 34.66.17.32 closed.
student_03_2a004b33a597@cloudshell:~ (qwiklabs-gcp-04-2d8ba71b6ea2)$ history
1 gcloud compute ssh --zone "us-central1-c" "blog" --project "qwiklabs-gcp-04-2d8ba71b6ea2"
2 export PROJECT_ID=$(gcloud info --format='value(config.project)')
3 gsutil mb gs://${PROJECT_ID}
4 gsutil cp ~/wordpress.sql gs://${PROJECT_ID}
5 gcloud compute ssh --zone "us-central1-c" "blog" --project "qwiklabs-gcp-04-2d8ba71b6ea2"
6 scp 34.66.17.32:/home/student-03-2a004b33a597/wordpress.sql .
7 ls
{snip}
12 gcloud compute scp --zone "us-central1-c" blog:/home/student-03-2a004b33a597/wordpress.sql .
13 ls
14 gsutil cp ~/wordpress.sql gs://${PROJECT_ID}
15 gcloud compute instances list
16 gcloud compute ssh --zone "us-central1-c" "blog" --project "qwiklabs-gcp-04-2d8ba71b6ea2"
17 history
student_03_2a004b33a597@cloudshell:~ (qwiklabs-gcp-04-2d8ba71b6ea2)$