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 for MySQL 5.6, 5.7, 8.0, 8.4
• Amazon Aurora 5.6, 5.7, 8.0
• Microsoft Azure Database for MySQL 5.7, 8.0

For MySQL 8.0 sources, Database Migration Service also supports the following minor versions: 8.0.18, 8.0.26, 8.0.27, 8.0.28, 8.0.30, 8.0.31, 8.0.32, 8.0.33, 8.0.34, 8.0.35, 8.0.36, 8.0.37, 8.0.39, 8.0.40.

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 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.
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. Configure global transaction ID (GTID) logging by setting the GTID_MODE to ON or OFF. The GTID_MODE value of ON_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 to ON.
   • If you're using a manual dump to migrate your data, set GTID_MODE to ON.
   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 or later: 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 or later: 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. Before you configure binary logs, ensure that you:
   • Enable binary logs on your source database.
   • Use row-based binary logging.
   • Retain binary logs for a long enough period to support the database migration. Generally, a week is sufficient.

   To configure binary logs, expand the section for your source:

   Self-hosted MySQL

   Depending on your MySQL version, specify a period with enough time for replication to occur:

   • MySQL 5.5 - 5.7: expire_logs_days
   • MySQL 8.0: expire_logs_days, binlog_expire_logs_seconds

   Microsoft Azure Database for MySQL

   Binary logging is enabled by default on Microsoft Azure Database for MySQL. You don't need to enable it. For more information, see the Microsoft documentation.

   Configure the following required parameters:

   1. Set binlog_expire_logs_seconds to a period that is long enough to support the database migration.

      For more information, see Configure server parameters in Azure Database for PostgreSQL and the binlog_expire_logs_seconds parameter in the Microsoft documentation.

   2. Restart your server so that the changes you made can take effect.

   Amazon RDS

   For Amazon RDS, you set row-based configuration 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).

   Amazon Aurora

   For Amazon Aurora, follow these steps:

   1. Enable binary logging for your MySQL database.
   2. Set the binary log retention period:

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

11. For Microsoft Azure Database for MySQL sources only: Check the value of your require_secure_transport setting.

    By default, Microsoft Azure databases require SSL/TLS encryption for all incoming connections. Depending on the require_secure_transport value, use one of the following encryption settings when you create the source connection profile:

    • If require_secure_transport is set to on, select Basic, TLS, or mTLS.
    • If require_secure_transport is set to off, select None.

Previous

Get set up

Next

Create a source connection profile

Send feedback