Best practices for migrating to Cloud SQL for MySQL

There are a variety of ways to migrate to MySQL on Cloud SQL. The migration strategy for a given source depends on several factors including database size, downtime expectations, and experience of the engineers performing the migration. Let’s review some of the most common migration strategies.

If you are migrating over a small database that is only a few gigabytes in size or that contains static data, the easiest approach is to take a dump of the database via the mysqldump utility. Upload the dump file into Cloud Storage and import it into a Cloud SQL instance following the instructions in the article on export and import using SQL dump files.

Since the dump files contain logical SQL statements, one advantage of this approach is that it is possible to edit the dump files before loading them into Cloud Storage. However, given certain Cloud SQL restrictions avoid including anything in the dump file that could break an import as listed in the best practices for importing and exporting data.

When migrating many instances of MySQL or for larger migrations a better choice is to use the Database Migration Service (DMS). This is a service that provides a variety of migration paths including paths for both homogeneous and heterogeneous migrations to MySQL, and support for SQL Server and PostgreSQL as migration destinations too.

For most mid-sized to large-sized database migrations using DMS should suffice. Situations where DMS may not be appropriate include very large databases (such as several TB in size), or if you have MySQL engineers with replication know-how and who prefer more fine-grained control of the migration process using native MySQL replication.

If minimizing downtime is a priority during migration and you have experienced MySQL engineers on your team, then replicating from an external source (ES) using native binary-logged based replication is an option. This method leverages importing a baseline snapshot of your database using a method such as a Cloud Storage import.

You then configure MySQL replication from the source instance to the target Cloud SQL instance using a set of pre-created stored procedures already available on the Cloud SQL instance. The full instructions can be found in the article titled Use a custom import to set up replication from large external databases.

One key detail when using binary-log based replication for migration is that binary logs on the source instance remain available until no longer needed by the target Cloud SQL instance. When running MySQL on-premises or self-managed, parameters like expire_logs_days can be configured to control purging of binary logs. However, other cloud vendor managed services have their own restriction on binary log retention.

For example, Amazon Relational Database Service (RDS) allows for configuring binary log retention via a stored procedure name mysql.rds_set_configuration. This allows retention for up to seven days. In most cases seven days is enough for most small-to medium-sized migrations from RDS to Cloud SQL. In such situations users can leverage a well-documented process that involves creating a managed RDS replica. Then do something to "break" the replication such as making the replica writable and deleting a row or injecting some kind of "poison pill" transaction on the primary that makes its way into the binary log and breaks replication. The RDS automation will not purge binary logs as long as the replica still needs them (although there seems to be a 30-day overall limit before RDS “terminates” a broken replication stream).

Another workaround is using the mysqlbinlog client to download binary logs onto another intermediary MySQL instance to prevent premature purging of binary logs. For example, in RDS there is a stored procedure named mysql.rds_set_configuration that allows setting a retention period in hours to ensure binary logs remain on the host long enough for download. In this case you don’t need to stand up a MySQL instance as the intermediary since any server, like a ‘Binlog Server’, that looks like a MySQL instance, is there to store and forward binary logs.

MySQL is unique among most popular relational database systems in that it supports multiple pluggable storage engines. Originally MySQL supported a single storage engine known as MyISAM and, up until MySQL 8.0, most system tables used this storage engine. However, MyISAM lacked support for transactions and was not crash-safe in the event of a sudden system shutdown or database crash.

Since then InnoDB has become the de facto storage engine for most MySQL user tables given its crash-safe architecture and support for transactions. Still there are other storage engines commonly seen in the MySQL community, both on-premises and in other cloud providers, including the following:

• ARCHIVE - Stores data in a highly compressed format for archival purposes
• CSV - Stores data in a comma-separated format (CSV) and used for logging tables for the general and slow query log
• MEMORY - Stores tables in memory

In the case of Cloud SQL we require a storage engine that is transactional and crash-safe to support value added features like replication and point-in-time recovery. Therefore all user tables migrating over to Cloud SQL need to use the InnoDB storage engine or be converted to InnoDB during the migration process.

This is especially important if doing native MySQL replication from an external source to Cloud SQL. While Cloud SQL does not allow users to create a MyISAM table directly on a Cloud SQL instance via data definition language (DDL) commands like CREATE TABLE, it is currently possible for MyISAM tables to replicate from an external source to Cloud SQL.

However, these imported MyISAM tables on Cloud SQL can lead to stability and reliability issues later during operations like replication, point-in-time recovery, and failover. For this reason it is advisable to convert all MyISAM user tables to InnoDB before performing a migration. 

The following query will list all non-system MyISAM tables.

Being a managed service, MySQL for Cloud SQL does not allow SUPER privilege for user accounts. This is a departure from most on-premises installations of MySQL that allow for a root user with such a privilege. Similarly, most other cloud providers do not provide this SUPER privilege in a managed MySQL environment.

Whatever the case in Cloud SQL users receive a default MySQL user named ‘root’@’%’ that has grants to most privileges provided by MySQL except those that would affect the ability for Google to manage the service such as the aforementioned SUPER along with FILE and SHUTDOWN. For more details on the use privileges provided in Cloud SQL please review documentation about MySQL users.

When preparing to migrate, review all user accounts on the source instance. For example, run the SHOW GRANTS command for each user to review the current set of privileges and see if there are any that are restricted in Cloud SQL. Similarly the pt-show-grants tool from Percona can also list grants.

Restrictions on user privileges in Cloud SQL can affect migrations when migrating database objects that have a DEFINER attribute. This is often the case for stored procedures, triggers, user-defined functions, and views. If the definer for a database object on the source instance is a user with a privilege not supported in Cloud SQL then this can be an issue during or after migration.

For example, a stored procedure may have a super-privileged definer to run SQL commands like changing a global variable that is not allowed in Cloud SQL. For cases like this it may be necessary to rewrite the stored procedure code or to migrate non-table objects like stored procedures as a separate migration step.

Our documentation has a section entitled MySQL import and migration jobs containing metadata with DEFINER clause that talks a bit more about other issues related to the definer clause for database objects.

Due to the user privilege restrictions mentioned earlier, users cannot natively call SET GLOBAL command for changing database parameters. Instead Cloud SQL offers a pre-defined set of “flags” that can be modified via the UI Console, GCloud CLI, and REST API to modify parameters.

The full list of supported Cloud SQL flags for MySQL can be found in our public documentation in the section titled Supported flags. Before migrating to Cloud SQL, review the list of supported flags versus the flags currently used in your source environment. For example, one good practice is to create a test Cloud SQL instance with similar CPU and memory settings as your source instance and run SHOW GLOBAL VARIABLES on both the source and Cloud SQL instance and compare the differences.

Common flags that can have different settings on-premises or in a cloud provider versus Cloud SQL for MySQL include the following:

• Character set and collation settings such as character_set_server and collation_server. The defaults can differ between installations and lead to storage and performance differences. For example, latin1 versus utf8.
• Binary log settings such as binlog_format. Cloud SQL currently only supports row-based logging in the binary log.
• Advanced replication settings. For example, Cloud SQL does not support replication filter parameters like replicate-do-table and replicate-do-db.
• Advanced InnoDB settings. Cloud SQL allows modifying most InnoDB related parameters such as resizing the InnoDB buffer pool via changing innodb_buffer_pool_size. However, there may be some more fine-grained InnoDB settings not modifiable in Cloud SQL or with different defaults than on-premises or other cloud providers.