Overview
Database Migration Service supports one-time and continuous migrations from source databases to Cloud SQL destination databases.
Supported source databases for MySQL include:
- Amazon RDS 5.6, 5.7, 8.0
- Self-managed MySQL (on premises or on any cloud VM that you fully control) 5.5, 5.6, 5.7, 8.0
- Cloud SQL 5.6, 5.7, 8.0
- Amazon Aurora 5.6, 5.7, 8.0
To configure a source database, complete the following steps:
- For Cloud SQL sources: If you are migrating from a Cloud SQL instance that uses a Private IP connection to a Cloud SQL instance that uses a non-RFC 1918 address IP range, add the non-RFC 1918 range to the network configuration of your source Cloud SQL instance. See Configure authorized networks in Cloud SQL documentation.
- Before you migrate data from the source database to the destination database, make sure that you stop all Data Definition Language (DDL) write operations during the full dump phase. You can use a script to verify that DDL operations are stopped. After the migration is in the CDC phase, you can resume DDL operations.
- Ensure that your source database doesn't contain metadata defined by users with the DEFINER clause. See Create and run a MySQL migration job containing metadata with a DEFINER clause.
- If your source database contains objects that reference
tables in the
mysql
,performance_schema
,information_schema
,ndbinfo
, orsys
system schemas, ensure that the replica databases also contain these system schema tables.If the replica databases don't have these tables, your migration job might fail with the
Unknown table in system schema
error. - You must set the server-id option to a value of 1 or larger. For more information, see Replication and Binary Logging Options and Variables.
- Configure global transaction ID (GTID) logging by setting the
GTID_MODE
toON
orOFF
. TheGTID_MODE
value ofON_PERMISSIVE
isn't supported.The value you should use depends on the migration requirements:
- If you
migrate to an existing destination instance that has
read replicas
enabled, set
GTID_MODE
toON
. - If you're using a manual dump to migrate your data, set
GTID_MODE
toON
.
GTID_MODE
, see Global Transaction ID System Variable. - If you
migrate to an existing destination instance that has
read replicas
enabled, set
-
You must configure the user account used to connect to the source database to accept connections from anywhere (host =
%
). Access can be restricted to this user in a later step.To limit the possibility of compromising other aspects of the database, we recommend that you create a separate account for this purpose.
There are four types of combinations of migrations and dumps:
- Type 1: Continuous migration and managed dump
- Type 2: Continuous migration and manual dump
- Type 3: One-time migration and managed dump
- Type 4: One-time migration and manual dump
Privileges for each type of migration and dump combination are listed in the tabs below.
Type 1
The user account that you configure must have the following privileges:
REPLICATION SLAVE
EXECUTE
SELECT
SHOW VIEW
REPLICATION CLIENT
RELOAD
TRIGGER
- (For migrating from Amazon RDS and Amazon Aurora only)
LOCK TABLES
MySQL version 8.0 and above: For optimal performance, make sure you don't grant the
BACKUP_ADMIN
privilege to this account.Type 2
The user account that you configure must have the following privileges:
REPLICATION SLAVE
EXECUTE
Type 3
The user account that you configure must have the following privileges:
SELECT
SHOW VIEW
TRIGGER
- (For migrating from Amazon RDS and Amazon Aurora only)
LOCK TABLES
- (For migrating from sources with the
GTID_MODE = ON
setting only)RELOAD
MySQL version 8.0 and above: For optimal performance, make sure you don't grant the
BACKUP_ADMIN
privilege to this account.Type 4
No privileges are required.
- To perform a one-time or continuous migration, ensure that binary logs are enabled on your source database, and that:
- Row-based binary logging is used.
- Binary logs are retained for a long enough period to support the database migration. Generally, a week is sufficient.
For Amazon RDS, setting row-based configuration is done in the parameter group by configuring the
binlog retention hours
parameter. This parameter is used to specify how many hours Amazon RDS should retain binary log files.To set the retention period for binary logs in Amazon RDS, use the
mysql.rds_set_configuration
stored procedure and specify a period with enough time for replication to occur. For example:call mysql.rds_set_configuration('binlog retention hours',168);
For Amazon Aurora, first, enable binary logging for your MySQL database.
Then, set the binary log retention period by entering the following command:
mysql> call mysql.rds_set_configuration('binlog retention hours', 168);
After entering this command, restart your server so that the changes you made can take effect.
- All tables (except tables in system databases) use the InnoDB storage engine.
- The password of the user account used to connect to the source database must not exceed 32 characters in length. This is an issue specific to MySQL Replication.
Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.
Last updated 2024-08-09 UTC.