Migrate a MySQL Database to Google Cloud SQL – Qwiklab

0 51

When you open the page of this lab in Qwiklabs, you can find the task requirements by click the green activity tracker (on the top right of the page) to expand the score box.

The screenshot above shows that there are 5 steps required for completing this lab. Combining with the instruction details, they are translated to the following mission statements.

  1. Create a Cloud SQL instance
  2. Export and import the wordpress database to the Cloud SQL instance
  3. Authorize the VM instance blog to access the Cloud SQL
  4. Change wp-config.php points to the Cloud SQL instance
  5. Check that the blog still responds to requests

  1. Check Existing WordPress Resources
    1. In the lab page, there is the IP of the Demo Blog Site shown in the left panel after provisioning lab resources.
    2. In the web console, navigate to Compute Engine > VM instances. You should find a VM instance called blog has been provisioned. A WordPress server is running on this instance.
    3. The instance should have the same external IP as shown in the panel. Copy the external IP and open it in a new tab. A website called Blog-Demo will be displayed.
  2. Create Cloud SQL Instance
    1. In the web console, navigate to SQL and click Create instance. Choose MySQL, give a name to the SQL instance, e.g. demo. Generate a root password and then click Create.
    2. Wait for the instance to be initialized, the process usually takes 5 to 10 minutes.
  3. Export SQL Database
    1. In the web console, navigate to Compute Engine > VM instances and click the SSH button of the instance called blog.
    2. Both the WordPress and its MySQL database are running in this VM instance. The existing MySQL database is called wordpress and the user called blogadmin with password Password1*.
    3. In the SSH session, use mysqldump to export the MySQL database to a dump file with the following flags:
      mysqldump --databases wordpress -h localhost -u blogadmin -p --hex-blob --skip-triggers --single-transaction --default-character-set=utf8mb4 > wordpress.sql
    4. In this lab, you can remove the --ignore-table [VIEW_NAME1] flag because the database does not include any views. Replace the argument values, the command line should look like:
      export PROJECT_ID=$(gcloud info --format='value(config.project)')
      gsutil mb gs://${PROJECT_ID}
      gsutil cp ~/wordpress.sql gs://${PROJECT_ID}
    5. Now you need to copy the dump file to Cloud Storage. Use the following commands to create a bucket and upload the file to it.
  4. Import to Cloud SQL
    1. Go back to the web console, navigate to SQL to check whether the cloud SQL instance is ready. Once the instance is online, click the instance name to view the details.
    2. Choose DATABASES tab, and click Create database.
    3. In the dialogue, enter wordpress as the name and select utf8mb4 as the character set.
    4. Now you prepared an empty database called wordpress in the Cloud SQL. Click IMPORT button at the top of the page.
    5. In the dialogue, click Browse to select the dump file in the Cloud Storage, and select wordpress in the Database field.
    6. Click Import to start the process.
  5. Authorized Blog Instance to Access Cloud SQL
    1. While the database is importing to the cloud SQL, choose USERS tab and click Create user account.
    2. In the dialogue, enter blogadmin as the user name and Password1* as the password.
    3. Choose CONNECTIONS tab, under the Public IP click Add network.
    4. Copy the IP of the Demo Blog site to the Network field, and format the number in form of a CIDR notation X.X.X.0/24.
    5. Click Done and Save to authorize the VM instance blog to access the Cloud SQL.
  6. Reconfigure WordPress to connect the Cloud SQL instance
    1. For this lab, the WordPress site configuration file is located in the /var/www/html/wordpress/ directory. Use the following commands to change the current directory and list the files,
      cd /var/www/html/wordpress/
      ls
    2. You should find a file called wp-config.php.
    3. Open the wp-config.php, such as using nano editor”
      sudo nano wp-config.php
    4. Find the following lines in the file,
      /** MySQL hostname */
      define('DB_HOST', 'localhost');
    5. Replace localhost with the Public IP of the Cloud SQL instance, e.g,
      /** MySQL hostname */
      define('DB_HOST', '35.226.248.101');
    6. Press Ctrl + x and type Y to save and exit the file.
    7. If you refresh the website in your web browser, it should be restored. That means the WordPress site is connected to the Cloud SQL. Otherwise, use the following command to restart the webserver,
      sudo service apache2 restart
      sudo service apache2 status

Leave A Reply

Your email address will not be published.