Configure your source

MySQL   |  PostgreSQL   |  PostgreSQL to AlloyDB

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:

1. 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.
2. 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 initial load phase. You can use a script to verify that DDL operations are stopped. After the migration is in the incremental load phase, you can resume DDL operations.
3. 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.
4. If your source database contains objects that reference tables in the mysql, performance_schema, information_schema, ndbinfo, or sys 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.

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

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

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

   For more information on privileges, see Privileges Provided by MySQL. For more information about user accounts, see MySQL user account format.

   Type 2

   The user account that you configure must have the following privileges:

   • REPLICATION SLAVE
   • EXECUTE

   For more information on privileges, see Privileges Provided by MySQL. For more information about user accounts, see MySQL user account format.

   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.

   For more information on privileges, see Privileges Provided by MySQL. For more information about user accounts, see MySQL user account format.

   Type 4

   No privileges are required.

   For more information on privileges, see Privileges Provided by MySQL. For more information about user accounts, see MySQL user account format.

8. 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 more information about binary logging, see The Binary Log.

     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);

     The maximum value that you can set for this parameter is 168 hours (or 7 days).

     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);

     You're entering this command because you want to ensure proper replication by configuring your system to retain binary logs for a minimum of 7 days (or 168 hours).

     After entering this command, restart your server so that the changes you made can take effect.

9. All tables (except tables in system databases) use the InnoDB storage engine.

   For more information about InnoDB, see The InnoDB Storage Engine. For more information about converting to InnoDB, see Converting Tables from MyISAM to InnoDB.

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

    For more information about the MySQL user password length limitation, see MySQL Bug #43439.

Previous

Get set up

Next

Create a source connection profile

Send feedback