How to Promote a Google Cloud Replica from an External MySQL Primary

This is the second blog in a series about how to migrate a MySQL instance from a VM to Google Cloud SQL. See the first blog here.

Scenario

Let’s review what we accomplished in the first blog. Our goal is to migrate from our MySQL 5.7 instance running on a GCE VM to Cloud SQL, also running MySQL 5.7. While this blog is focused on MySQL 5.7 migration, the steps will also work if you have servers running on MySQL 8.0. 

So far, we have created a Cloud SQL instance, and we are replicating it from an external primary on the VM. 

In this blog, we will cut over to our new Cloud SQL instance by failing over to it, so it will become the new primary server. In addition, we decided to add an extra layer of protection by then making the external MySQL server a replica of the Cloud SQL instance, just in case we find that we need to fail back.

Prerequisites

To start the process, we need to have a few things in place. We will first create a user account on the Cloud SQL instance to be used for replicating to the external replica. We’ll create the user using the MySQL client and only grant the needed privileges.

create user migration_user identified by ‘PASSWORD’;

grant replication slave on *.* to migration_user;

Cutover to Cloud SQL

Now we are ready to promote the Cloud SQL instance to be the primary. First, we will check to make sure replication has caught up on the Cloud SQL instance.

show slave status \G

Next, we can promote the replica via the Cloud SQL console.

In the GCP console:

• Choose the replica instance (mysql-cloudsql) from the Cloud SQL Instances page
• Click Promote replica and then OK

Create an external read replica

At this point, we have two standalone MySQL instances: one in Cloud SQL and one on the VM. In order to avoid writes on our new primary, we have not yet added the application users. This will allow us to quickly set up replication to go from our new primary in Cloud SQL to the external replica on the VM without having to do a new dump and load.

First, we need to prepare the MySQL instance on our VM to be a replica by reviewing the global variable settings below and changing those that are not already set as needed. This can be changed in advance.

Replication settings:

• gtid_mode=ON
• enforce_gtid_consistency=ON
• expire_logs_days=7
• read_only=ON
• log_slave_updates=ON
• binlog_format=ROW
• log_bin=ON

Here is an example of checking and changing one of the settings, gtid_mode.

mysql> show global variables like 'gtid_mode';
mysql> set global gtid_mode=ON;

Be sure to verify all of the options above, which can all be changed dynamically. It is also advised to make the same changes in the my.cnf file, in case MySQL is restarted at some point.

Next, establish replication on the external replica. Notice that we are not replicating the MySQL database.

mysql> stop slave;
mysql> CHANGE MASTER TO master_host='10.9.8.7', master_port=3306,
       MASTER_USER='migration_user', MASTER_PASSWORD='PASSWORD',
       MASTER_AUTO_POSITION=1;

mysql> CHANGE REPLICATION FILTER TO REPLICATE_IGNORE_DB=mysql;
mysql> show slave status \G
mysql> start slave;
mysql> show slave status \G

At this point, the application users can be added to Cloud SQL, and then the instance will be ready to accept traffic from the application. 

Conclusion

In this blog post, we cut over to a new Cloud SQL instance while ensuring data integrity and minimizing downtime. Our process started with a MySQL primary instance running on a VM supported by GCE and a replica hosted in Cloud SQL. Writes to the application were stopped before the maintenance began. The Cloud SQL instance was promoted to be the new primary, which then broke replication between the two instances, making them each standalone.

In order to allow for a rollback option, replication was then established from the Cloud SQL instance back to the MySQL instance on the VM, now known as the external replica. Following the new reverse replication setup, writes from the application were now permitted and routed to the new primary in Cloud SQL.

By following these clear and concise instructions, organizations can seamlessly migrate their databases to the cloud while maintaining operational efficiency and data reliability.

References

Cloud SQL Create an external replica: https://cloud.google.com/sql/docs/mysql/replication/configure-external-replica