Tutorial: Migrating from MySQL to Cloud SQL using automated workflow

This tutorial describes how to migrate a MySQL 5.7 database to Cloud SQL by using the Cloud SQL automated migration workflow. It follows the external replica promotion strategy described in the companion document, Migration from MySQL to Cloud SQL. The tutorial assumes you're familiar with MySQL concepts, have an understanding of the features and capabilities of Cloud SQL, and have read the concept document.

Objectives

  • Deploy a MySQL database on Compute Engine to act as the source database.
  • Confirm that all Cloud SQL migration prerequisites are met.
  • Create a Cloud SQL replica by using the automated migration workflow.
  • Confirm that the replica database is running in Cloud SQL.
  • Promote the Cloud SQL database from replica to primary.
  • Update the application to use the Cloud SQL database.
  • Confirm that the primary database is running in Cloud SQL.

Costs

This tutorial uses the following billable components of Google Cloud:

To generate a cost estimate based on your projected usage, use the pricing calculator. New Google Cloud users might be eligible for a free trial.

Before you begin

  1. Sign in to your Google Account.

    If you don't already have one, sign up for a new account.

  2. In the Google Cloud Console, on the project selector page, select or create a Google Cloud project.

    Go to the project selector page

  3. Make sure that billing is enabled for your Cloud project. Learn how to confirm that billing is enabled for your project.

  4. Enable the Compute Engine and Cloud SQL APIs.

    Enable the APIs

Architecture

The initial architecture that you build has a MySQL-based WordPress application that's deployed using Cloud Deployment Manager and running on Compute Engine. Although this tutorial uses Compute Engine to run the source database, you can use the procedures in this tutorial to migrate a MySQL database from any location that has a public IPv4 access. This includes on-premises environments, colocation facilities, and cloud provider locations.

You start by creating an application running on Compute Engine that uses MySQL as a database, as shown in the following diagram:

Starting database architecture.

You then use the mysqldump command to replicate the MySQL database to Cloud SQL, as shown in the following diagram:

Database replication architecture.

After that, you promote the Cloud SQL replica to be primary and update the application to use the new primary database. After the migration, the architecture uses the database hosted on Cloud SQL. The following diagram shows apps using the new Cloud SQL database:

Migrated database architecture.

Deploying a source MySQL database on Compute Engine

To begin, you establish a source MySQL database that you can move to Cloud SQL. Any MySQL database running version 5.6 or 5.7 allows you to take advantage of the Cloud SQL automated migration workflow.

Deploy WordPress using Deployment Manager

To simplify the deployment of a source database, you use a WordPress installation from the Google Cloud Marketplace Click-to-Deploy solutions. WordPress is a common website creation and content management tool that uses MySQL. The Click-to-Deploy version offered on Google Cloud uses MySQL 5.7 and therefore meets the requirements for migrating to Cloud SQL.

  1. In the Cloud Console, go to the Google Click-to-Deploy page:

    Go to the Google Click-to-Deploy page

  2. Click the WordPress solution.

  3. Click Launch.

  4. Fill in the following fields:

    1. Deployment Name: my-wordpress-server
    2. Zone: us-east-1b
    3. Administrator e-mail address: Enter your email address.
    4. Firewall: Make sure that both HTTP and HTTPS options are selected.
  5. Click Deploy.

    The WordPress VM installation process begins. After a few minutes, the Deployment Manager displays the details of your deployed WordPress VM. Save all of this information for use later on in the tutorial, especially the information about the MySQL root user and the wordpress username and password.

  6. Click the Site address link to verify that your WordPress site is running.

    If everything is working, you see output like the following:

    An example of the click-to-deploy WordPress homepage.

Assign API scopes to the WordPress server

To finish setting up the WordPress installation on the Compute Engine VM, you need to add API scope permissions. Doing this lets the VM write its database exports to Cloud Storage. The exports in Cloud Storage are used later to create your Cloud SQL replica.

  1. Open Cloud Shell:

    Open Cloud Shell

  2. Shut off the WordPress VM:

    gcloud compute instances stop my-wordpress-server-vm \
        --zone us-east1-b
    

    The VM must be in a stopped state before you can apply new permissions through API scopes.

    Before you go to the next step, wait for the server to finish shutting down.

  3. Apply the new scope to allow the VM to write to Cloud Storage:

    gcloud beta compute instances set-scopes my-wordpress-server-vm \
        --scopes storage-rw \
        --zone us-east1-b
    
  4. Restart the VM:

    gcloud compute instances start my-wordpress-server-vm \
        --zone us-east1-b
    

Your VM can now read and write to Cloud Storage.

Connect as the WordPress administrator and create a post

The next step is to connect to the WordPress admin interface to create a new post and verify that the application and database are functioning correctly. Your IP address might have changed after you stopped and restarted the server, so you should verify the current public IP address of your WordPress VM.

  1. Verify the public IP address of the VM that's running WordPress:

    1. In the Cloud Console, go to the VM Instances page:

      Go to the VM Instances page

    2. Make a note of the External IP value for my-wordpress-server-vm.

  2. Verify that the WordPress application is running. Using the external IP address that you just got, use your browser to go to the public IP address of your WordPress VM:

    http://your-public-ip
    

    If the IP address is correct, you see the default WordPress page again.

  3. In the browser, go to the admin page for WordPress:

    http://your-public-ip/wp-admin
    
  4. Use the WordPress admin user and WordPress admin password to log into the admin interface.

  5. Click Posts, and then click Add New.

  6. Give your post the title My First Post and add some text.

  7. When you're done, click Publish, and then click Publish again to confirm:

    Publishing your first post.

  8. Click the Site address link to see your new post.

You now have a WordPress VM running a MySQL version 5.7 database.

Checking Cloud SQL migration prerequisites

Before you can migrate to Cloud SQL by using the automated migration workflow, you must make sure that you've met the prerequisites for configuring your source database server. The following sections walk you through this process, one prerequisite at a time.

Connect to the VM using SSH

You must confirm the prerequisites on the VM where WordPress is running, so you begin by connecting to the VM instance.

  1. In the Cloud Console, go the VM Instances page:

    Go to the VM Instances page

  2. In the list of virtual machine instances, click SSH in the row of the instance that you want to connect to:

    Connecting to the WordPress VM using SSH.

    Cloud Shell opens and gives you command-line access to your VM.

Confirm all prerequisites

Now that you have established a command-line connection to your WordPress server, you can make sure that MySQL is ready to be migrated.

Create a replication user

To support replication of the database, you create a replication user account in MySQL. You do this so that you don't have to use the root user for later steps.

  1. On the WordPress VM, connect to your MySQL database as the root user:

    mysql -u root -p -h localhost wordpress
    

    When you're prompted, enter the root password that you noted earlier in the Deployment Manager.

  2. While connected to the MySQL database, create the replication user:

    CREATE USER 'replication'@'%' IDENTIFIED BY 'mysupersecretpassword';
    GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
    

    You see output like the following:

    mysql> CREATE USER 'replication'@'%' IDENTIFIED BY 'mysupersecretpassword';
    Query OK, 0 rows affected (0.00 sec)
    mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
    Query OK, 0 rows affected (0.00 sec)
    
  3. Enter quit to exit from the mysql interface.

You now have a replication user that can be used by the Cloud SQL automated migration workflow.

Update the MySQL configuration file

There are four variables that you need to set in your MySQL configuration file. The configuration file is located at /etc/mysql/mysql.conf.d/mysqld.cnf. The values that you need to add are:

  • server-id. This value must be set in order to enable binary logging. The value must be between 1 and (2^32)-1 (4,294,967,295) and must be unique to each server. In this tutorial, you set the value to 2.
  • log-bin. This setting enables binary logging and signifies what filename the database will use to store the log. In this tutorial, you set the value to mysql-bin.
  • gtid_mode. This setting enables global transaction identifiers (GTIDs) to be used to identify transactions. This setting also requires the next command to be included in order to function. In this tutorial, you set the value to on.
  • enforce-gtid-consistency. This setting enforces GTID consistency by only allowing statements that can be logged using GTID. In this tutorial, you set the value to true.

The following steps describe how to set the values for all four MySQL variables.

  1. In the terminal window of your WordPress VM, set the mysqld.cnf configurations described earlier:

    sudo -s
    echo "server-id = 2" >> /etc/mysql/mysql.conf.d/mysqld.cnf
    echo "log-bin = mysql-bin" >> /etc/mysql/mysql.conf.d/mysqld.cnf
    echo "gtid_mode = ON" >> /etc/mysql/mysql.conf.d/mysqld.cnf
    echo "enforce-gtid-consistency = true" >> /etc/mysql/mysql.conf.d/mysqld.cnf
    exit
    
  2. Restart your MySQL server for the commands to take effect:

    sudo service mysql restart
    
  3. Log in to your mysql interface again:

    mysql -u root -p -h localhost wordpress
    
  4. Verify that all of the variables were correctly set after the reboot:

    SELECT @@gtid_mode, @@enforce_gtid_consistency, @@server_id;
    

    You see the following output:

    mysql> select @@gtid_mode, @@enforce_gtid_consistency, @@server_id;
    +-------------+----------------------------+-------------+
    | @@gtid_mode | @@enforce_gtid_consistency | @@server_id |
    +-------------+----------------------------+-------------+
    | ON          | ON                         |           2 |
    +-------------+----------------------------+-------------+
    1 row in set (0.00 sec)
    
  5. Enter quit to close the mysql interface.

Enable IPv4 access to the database

In order for you to use the Cloud SQL automated migration workflow, your source database must be accessible using a public IPv4 address. Because the WordPress server has an automatically assigned public IP address, you can allow this connectivity using a VPC firewall rule.

  1. In Cloud Shell, add a new network tag to your WordPress VM:

    gcloud compute instances add-tags my-wordpress-server-vm \
        --tags allowmysql \
        --zone us-east1-b
    
  2. Allow MySQL network traffic from the public internet to your VM:

    gcloud compute firewall-rules create "mysql-replication-access" \
        --allow tcp:3306 \
        --target-tags "allowmysql"
    

You now have a firewall rule that will allow MySQL traffic on port 3306 from the public internet to your WordPress VM.

Production databases can be administered and protected in a variety of ways. Make sure you contact your network and security administrators to coordinate the necessary access for any production database migrations with the automated migration workflow.

The Click-to-Deploy WordPress server is designed to allow the local web server to access the local database. Because you want to allow other database connections in support of your migration, you need to update the MySQL bind_address entry in the mysqld.cnf file to listen on ports other than localhost.

  1. In the terminal window of your WordPress VM, change the MySQL bind_address value to 0.0.0.0:

    sudo sed -i 's|bind-address.*|bind-address = 0.0.0.0|' /etc/mysql/mysql.conf.d/mysqld.cnf
    

    This allows MySQL connections on all available network interfaces.

  2. Restart your MySQL database for the changes to take effect:

    sudo service mysql restart
    

Create the database backup file

Next, you need to create a database backup file to create the replica from Cloud SQL. You do this by using the MySQL mysqldump command.

Basic data verification

Before you create your backup, you should validate the number of posts in your WordPress database. This is a way to verify that content has been successfully backed up.

  1. Connect to your WordPress server using SSH.
  2. Count the number of rows in the wp_posts table:

    mysql -u root -p -D wordpress -e "SELECT count(*) from wp_posts"
    

    You see output similar to the following:

    SELECT count(*) from wp_posts;
    +----------+
    | count(*) |
    +----------+
    |        6 |
    +----------+
    1 row in set (0.01 sec)
    

You now know the starting number of rows in your table.

Create a database backup and upload to Cloud Storage

The next task is to copy the results of the backup process to a Cloud Storage bucket.

  1. In Cloud Shell, create a new Cloud Storage bucket to hold the database backup:

    gsutil mb gs://${DEVSHELL_PROJECT_ID}-mysqldump/
    

    This creates a bucket that has a unique name (based on your Google Cloud project ID) for your project to store the mysqldump file.

  2. In the Cloud Console, go the VM Instances page:

    Go to the VM Instances page

  3. In the list of virtual machine instances, click SSH in the row of your WordPress server in order to connect to the VM:

    Connecting to the WordPress VM using SSH.

  4. In the terminal window of your WordPress VM, create a backup named mybackup1.sql:

    sudo mysqldump \
        -h localhost -P 3306 -u root -p \
        --databases wordpress \
        --hex-blob --skip-triggers --master-data=1 \
        --order-by-primary --compact --no-autocommit \
        --default-character-set=utf8 \
        --single-transaction --set-gtid-purged=on > mybackup1.sql
    
  5. Enter the MySQL root password.

  6. Copy the mybackup1.sql file to your Cloud Storage bucket:

    sudo gsutil cp ./mybackup1.sql gs://[your_project_name]-mysqldump
    

    You see output like the following:

    Copying file://./mybackup1.sql [Content-Type=application/x-sql]...
    / [1 files][510.4 KiB/510.4 KiB]
    Operation completed over 1 objects/510.4 KiB.
    

    You've now uploaded your backup file to Cloud Storage.

Creating a Cloud SQL external replica using the automated migration workflow

Now that you know the number of rows in your database and you have a complete backup of the database, you can create a replica database on Cloud SQL by using the automated migration workflow.

  1. In the Cloud Console, go the Cloud SQL page:

    Go to the Cloud SQL page

  2. Click Migrate data.

  3. Click Begin migration.

  4. In the Data source details section, provide the following values:

    • Name of data source: A name for the source database. Use mywordpress.
    • Public IP address of source: The external IP address of your WordPress VM. You got this value earlier when Deployment Manager created the server.
    • Port number of source: 3306
    • MySQL replication username: replication
    • MySQL replication user password: mysupersecretpassword
    • Database version: 5.7
  5. Click Next.

  6. In the Cloud SQL read replica creation section, provide the following values:

    • Read replica instance ID: The name of your Cloud SQL service instance. Use the name mywordpress-cloudsql.
    • Location: The region and zone that your WordPress VM was created in. For this tutorial, use us-east1-b.
    • Storage type: SSD
    • Storage capacity: 10 GB
    • Enable automatic storage increases: Make sure this option is enabled.
    • SQL Dump File: Click Browse and select the bucket and mysqldump file that you created earlier.
  7. Click Advanced settings.

  8. Click + Add network and then do the following:

    • Name the network external.
    • Under Network, enter 0.0.0.0/0
    • Click Done.

    Adding 0.0.0.0/0 allows public access to your external replica.

  9. Under Database flags click +Add item.

  10. In the Choose a flag list, do the following:

    1. Select sql_mode.
    2. In the drop-down list that appears on the right, choose ALLOW_INVALID_DATES.

    The sql_mode flag is required in order to migrate a WordPress database to Cloud SQL.

  11. Click Create.

    The instance is created in the background.

  12. In the Data Synchronization section, take note of the value of the Outgoing IP address field.

    It might take a few minutes for this field to be filled in. However, when you have an IP address, the Cloud SQL instance has been created.

  13. Click Next.

  14. Click Finish.

The automated migration workflow begins replicating your data from the source database on the WordPress VM to the Cloud SQL replica database. This process can take a while, depending on the size of your database, the distance between the source and destination, and the available IOPS of both the primary and replica database. In this case, your database is small, so the process is quick.

After a few minutes, you see a green check mark next to your database name, which indicates that replication is enabled. Click on your new database, and you also see a message Replication is enabled.

The console shows that replication is enabled.

This verifies that the Cloud SQL replication operation was successful.

Create MySQL user accounts on the replica database

When you migrate a single database as part of a MySQL server, the migration process does not migrate the user accounts of the database itself. In order to limit changes to users and application, you should re-create critical accounts on the Cloud SQL replica database.

  1. In Cloud Shell, reset the root user account on the Cloud SQL database to use the same root password as your primary WordPress database.

    gcloud sql users set-password root --host=% \
        --instance=mywordpress-cloudsql \
        --password=primary-root-password
    
  2. Create a wordpress user account with a username and password that matches the name and password in the primary WordPress database.

    gcloud sql users create wordpress --host=% \
        --instance=mywordpress-cloudsql \
         --password=your-wordpress-db-password
    

Confirming that the replica database is running in Cloud SQL

Now that Cloud SQL replication is enabled, you should verify data replication. To verify the replication of data, you need to connect to your new Cloud SQL database and then update the primary WordPress database with new content. If replication is working, you see the updated data in the Cloud SQL replica.

Basic data verification in Cloud SQL

The following steps demonstrate how to connect to the Cloud SQL replica database.

  1. In the Cloud Console, go the Cloud SQL page.

    Go to the Cloud SQL page

  2. Click the replica database to open the Instance Details page.

  3. Under Connect to this instance, click Connect using Cloud Shell.

    This opens Cloud Shell and automatically adds your connection IP address to the firewall.

  4. Press ENTER to execute the command.

  5. When you're prompted for the password, enter the root user password of the database.

    This establishes a MySQL connection to the Cloud SQL replica.

  6. Select the database:

    USE wordpress
    
  7. Run a query to count the rows in the database wp_posts table:

    SELECT count(*) from wp_posts;
    

    The output matches the previous output for number of rows:

    mysql> SELECT count(*) from wp_posts;
    +----------+
    | count(*) |
    +----------+
    |        6 |
    +----------+
    1 row in set (0.01 sec)
    

    This confirms that replication completed successfully during the database creation.

Keep this Cloud Shell session open for the next steps.

Create new database content

You can now create a new WordPress post to add context to the database. This lets you verify that new data is being replicated to Cloud SQL.

  1. In your browser, go to the admin page for your WordPress instance:

    http://your-public-ip/wp-admin
    
  2. Use the WordPress admin user and WordPress admin password to log in to the admin interface.

  3. Click Posts and then click Add New.

  4. Give your post the title My Second Post and then add some text.

  5. Click Publish, and then click Publish to confirm:

    Publishing your second post.

  6. Click the Site address link to see your new post.

Verify that the rows have increased in both the primary and replica

You can now verify that the data has been added in both the primary and replica databases.

  1. Connect to the WordPress VM using SSH.
  2. Run a query to count the number of rows in the wp_posts table:

    mysql -u root -p -D wordpress -e "SELECT count(*) from wp_posts"
    

    You see output like to the following:

    SELECT count(*) from wp_posts;
    +----------+
    | count(*) |
    +----------+
    |        8 |
    +----------+
    1 row in set (0.01 sec)
    

    Notice that the number of data rows has increased on the primary database.

  3. Open the Cloud Shell session that is connected to the replica Cloud SQL database.

  4. Run a query to count the rows in the replica wp_posts table:

    SELECT count(*) from wp_posts;
    

    You see output like the following:

    Mysql [wordpress]> SELECT count(*) from wp_posts;
    +----------+
    | count(*) |
    +----------+
    |        8 |
    +----------+
    1 row in set (0.01 sec)
    

    If this output shows the same number of rows in the original database table and the replica database table, you've confirmed that replication was successful when new content was added to the database.

Promoting the Cloud SQL database from replica to primary

Now that your replica is running in Cloud SQL and you have verified the replication, it's time to promote the replica to primary and complete the migration of the database to Cloud SQL.

Downtime considerations

Every MySQL migration incurs some amount of database downtime. The downtime associated with the automated migration workflow is minimal, but it still causes at least a few minutes of downtime. Make sure you understand your application requirements, service level objectives, and service level agreements before you begin migration.

Stop the primary database

The first step is to stop the MySQL service running on the WordPress VM. This stops all writes to the database and begins the window of downtime for the database and any applications connected to it.

  1. In the terminal window of your WordPress VM, stop the MySQL database:

    sudo service mysql stop
    

    Your primary MySQL database is now offline.

  2. In your browser, go to the WordPress site:

    Now you see an error message confirming that the application is offline:

    Error establishing a database connection.

Update application endpoints

With the primary database stopped, you need to update the application to use the Cloud SQL server. This confirms basic connectivity between the application and the Cloud SQL database before you promote the replica to primary.

WordPress has a simple configuration file that you need to update to point the application to the Cloud SQL replica.

  1. In the Cloud Console, go the Cloud SQL page.

    Go to the Cloud SQL page

  2. Click the replica database to open the Instance Details page.

  3. Copy the public IP address.

  4. Connect to your WordPress VM using SSH.

  5. In the terminal of the instance, update the addresses of the destination database in the configuration file. Replace your-cloud-sql-ip with the public IP address of your Cloud SQL replica.

    sudo sed -i -e 's/localhost/your-cloud-sql-ip/' /var/www/html/wp-config.php
    

Promote the replica

You have now updated the application code to support the new Cloud SQL database location. Now you need to promote the Cloud SQL replica to be the primary database. Promoting the Cloud SQL replica to be the primary database completes the migration of the MySQL database to Cloud SQL

  1. In the Cloud Console, go the Cloud SQL page.

    Go to the Cloud SQL page

  2. Click the replica database to open the Instance Details page.

  3. Click the Promote Replica button:

    Promoting the replica to be primary.

  4. Click OK to begin the promotion of the database.

After a few minutes, the database is shown as available in the Cloud SQL instances page. At this point, you can browse to your WordPress site and see that the site is back online.

Cleaning up

To avoid incurring charges to your Google Cloud account for the resources used in this tutorial.

Delete the project

  1. In the Cloud Console, go to the Manage resources page.

    Go to the Manage resources page

  2. In the project list, select the project that you want to delete and then click Delete .
  3. In the dialog, type the project ID and then click Shut down to delete the project.

What's next