The Database Migration Service for MySQL supports one-time and continuous migrations from source databases to Cloud SQL destination databases.
Supported source databases for MySQL include versions 5.5, 5.6, 5.7, and 8.0.
To configure a source database, complete the following steps:
- Before you migrate data from the source database to the destination database, make sure that you stop all Data Definition Language (DDL) write operations. You can use a script to verify that DDL operations are stopped.
- 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.
You can activate or deactivate global transaction ID (GTID) logging by setting the
GTID_MODE
to ON, OFF, or OFF_PERMISSIVE. TheGTID_MODE
value of ON_PERMISSIVE isn't supported.If you're using a manual dump to migrate your data, then perform the migration with GTID enabled.
For more information about
GTID_MODE
, see Global Transaction ID System Variable.-
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:
To perform a continuous migration.EXECUTE:
For Database Migration Service for MySQL to access and check stored procedures on the source database.SELECT:
For Database Migration Service for MySQL to perform the dump.SHOW VIEW:
For Database Migration Service for MySQL to perform the dump.
If GTID is disabled on the source, then the user account needs the following additional privileges:
REPLICATION CLIENT
RELOAD
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
If GTID is disabled on the source, then the user account also needs the following privileges:
REPLICATION CLIENT
RELOAD
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 AWS RDS, setting the row-based configuration is done in the parameter group. To set retention for binary logs in RDS:
call mysql.rds_set_configuration('binlog retention hours', 168);
- 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.