Configure your source

Overview

Database Migration Service 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:

  1. 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.
  2. 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.
  3. You can activate or deactivate global transaction ID (GTID) logging by setting the GTID_MODE to ON, OFF, or OFF_PERMISSIVE. The GTID_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.

  4. 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.

  5. 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 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 AWS RDS should retain binary log files.

      To set the retention period for binary logs in AWS 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);

  6. All tables (except tables in system databases) use the InnoDB storage engine.
  7. 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.