Configure your source MySQL database for continuous migrations

This page describes how to configure the following source database instances for continuous homogeneous MySQL migrations:

Configure a Cloud SQL for MySQL source

To configure a Cloud SQL for MySQL source instance for your migration process, do the following:

  1. Check if your source database contains metadata defined by users with the DEFINER clause. See Verify metadata with a DEFINER clause.

    Database Migration Service for homogeneous MySQL migrations doesn't migrate user accounts and logins. Sources that contain metadata defined by user accounts with the DEFINER clause might block your migration.

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

  3. Enable and configure binary logs on your source database.

    In Cloud SQL for MySQL, binary logging is enabled automatically when you use point-in-time recovery (PITR). You can configure the log format and retention period with the system flags available in Cloud SQL. Do the following:

    1. Enable PITR on your instance. This feature automatically enables binary logging. It isn't possible to enable binary logging without PITR in Cloud SQL. See Point-in-time recovery (PITR) in the Cloud SQL documentation.
    2. Use Cloud SQL flags to adjust logging configuration. For more details, see Configure database flags in the Cloud SQL documentation.
      1. Database Migration Service requires that binlog_format is set to ROW. You don't need to adjust this setting, Cloud SQL for MySQL automatically sets the binlog_format flag to ROW.
      2. Set the log retention period to a long enough period to support the database migration. Generally, a week is sufficient. Use one of the following flags:
        • MySQL 5.6 - 5.7: expire_logs_days
        • MySQL 8.0 and later: expire_logs_days, binlog_expire_logs_seconds

  4. Create a dedicated migration user account in your instance. See Create a user in the Cloud SQL documentation. Make sure that:

    • You allow the dedicated user account to connect from any host (host=%).
    • The password for the account doesn't exceed 32 characters in length. For more information about the MySQL user password length limitations, see MySQL Bug #43439.

  5. Assign the necessary privileges to the dedicated migration user account. For continuous migrations, the full list of required privileges depends on whether you provide your own dump files or use Database Migration Service managed dump feature:

    Managed dump

    If you want Database Migration Service to automatically take the full dump of your source database, assign the following privileges to the user account:

    Manual dump

    If you provide your own dump files created with mysqldump, you need to provide the privileges required for the CDC phase:

Configure a self-hosted MySQL source

To configure a self-hosted MySQL source instance for your migration process, do the following:

  1. Ensure that all your tables (except tables in system databases) use the InnoDB storage engine. If you use a different storage engine, you might need to convert your tables. For more information, see Converting Tables from MyISAM to InnoDB.
  2. Check if your source database contains metadata defined by users with the DEFINER clause. See Verify metadata with a DEFINER clause.

    Database Migration Service for homogeneous MySQL migrations doesn't migrate user accounts and logins. Sources that contain metadata defined by user accounts with the DEFINER clause might block your migration.

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

  4. Configure the server-id setting to a value of 1 or larger. For more information, see Replication and Binary Logging Options and Variables.

  5. Make sure that GTID_MODE is set to ON or OFF, no other value is supported.

    Database Migration Service requires GTID_MODE set to ON in the following scenarios:

    For more information about GTID_MODE, see Global Transaction ID System Variable.

  6. Enable and configure binary logs on your source database. Do the following:

      • Enable binary logs on your source database.
      • Set binlog_format to 'ROW'. See Row-based binary logging.
      • Set the log retention period to a long enough period to support the database migration. Generally, a week is sufficient. Use one of the following flags:
        • MySQL 5.5 - 5.7: expire_logs_days
        • MySQL 8.0: expire_logs_days, binlog_expire_logs_seconds

  7. Create a dedicated migration user account in your instance. See Account management statements. Make sure that:

    • You allow the dedicated user account to connect from any host (host=%).
    • The password for the account doesn't exceed 32 characters in length. For more information about the MySQL user password length limitations, see MySQL Bug #43439.

  8. Assign the necessary privileges to the dedicated migration user account. For continuous migrations, the full list of required privileges depends on whether you provide your own dump files or use Database Migration Service managed dump feature:

    Managed dump

    If you want Database Migration Service to automatically take the full dump of your source database, assign the following privileges to the user account:

    Manual dump

    If you provide your own dump files created with mysqldump, you need to provide the privileges required for the CDC phase:

Configure an Amazon RDS for MySQL source

To configure an Amazon RDS for MySQL source instance for your migration process, do the following:

  1. Amazon RDS for MySQL uses InnoDB storage engine by default, but allows using other storage engines. Ensure that all your tables (except tables in system databases) use the InnoDB storage engine. If you use a different storage engine, you might need to convert your tables. For more information, see Converting Tables from MyISAM to InnoDB.
  2. Check if your source database contains metadata defined by users with the DEFINER clause. See Verify metadata with a DEFINER clause.

    Database Migration Service for homogeneous MySQL migrations doesn't migrate user accounts and logins. Sources that contain metadata defined by user accounts with the DEFINER clause might block your migration.

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

  4. Make sure that GTID_MODE is set to ON or OFF, no other value is supported. Amazon RDS for MySQL uses GTID_MODE = OFF by default.

    Database Migration Service requires GTID_MODE set to ON in the following scenarios:

    For more information about GTID_MODE, see Global Transaction ID System Variable.

  5. Verify and configure binary logs on your source database. Do the following:

    1. Ensure your instance uses automated backups. Amazon RDS generates binary logs only when automated backups are enabled. For more information, see Enabling automated backups in the Amazon RDS documentation.

    2. Set the log retention period to a long enough period to support the database migration. Generally, a week is sufficient. See Setting and showing binary log configuration in the Amazon RDS documentation.

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

  6. Create a dedicated migration user account in your instance. See Account management statements. Make sure that:

    • You allow the dedicated user account to connect from any host (host=%).
    • The password for the account doesn't exceed 32 characters in length. For more information about the MySQL user password length limitations, see MySQL Bug #43439.

  7. Assign the necessary privileges to the dedicated migration user account. For continuous migrations, the full list of required privileges depends on whether you provide your own dump files or use Database Migration Service managed dump feature:

    Managed dump

    If you want Database Migration Service to automatically take the full dump of your source database, assign the following privileges to the user account:

    Manual dump

    If you provide your own dump files created with mysqldump, you need to provide the privileges required for the CDC phase:

Configure an Amazon Aurora for MySQL source

To configure an Amazon Aurora for MySQL source instance for your migration process, do the following:

  1. Check if your source database contains metadata defined by users with the DEFINER clause. See Verify metadata with a DEFINER clause.

    Database Migration Service for homogeneous MySQL migrations doesn't migrate user accounts and logins. Sources that contain metadata defined by user accounts with the DEFINER clause might block your migration.

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

  3. Make sure that GTID_MODE is set to ON or OFF, no other value is supported. Amazon Aurora for MySQL uses GTID_MODE = OFF by default.

    Database Migration Service requires GTID_MODE set to ON in the following scenarios:

    For more information about GTID_MODE, see Global Transaction ID System Variable.

  4. Enable and configure binary logs on your source database. Do the following:

    1. Enable binary logging for your MySQL database.

    2. Set the log retention period to a long enough period to support the database migration. Generally, a week is sufficient. See Setting and showing binary log configuration.

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

  5. Create a dedicated migration user account in your instance. See Account management statements. Make sure that:

    • You allow the dedicated user account to connect from any host (host=%).
    • The password for the account doesn't exceed 32 characters in length. For more information about the MySQL user password length limitations, see MySQL Bug #43439.

  6. Assign the necessary privileges to the dedicated migration user account. For continuous migrations, the full list of required privileges depends on whether you provide your own dump files or use Database Migration Service managed dump feature:

    Managed dump

    If you want Database Migration Service to automatically take the full dump of your source database, assign the following privileges to the user account:

    Manual dump

    If you provide your own dump files created with mysqldump, you need to provide the privileges required for the CDC phase:

Configure a Microsoft Azure Database for MySQL source

To configure a Microsoft Azure Database for MySQL source instance for your migration process, do the following:

  1. Check if your source database contains metadata defined by users with the DEFINER clause. See Verify metadata with a DEFINER clause.

    Database Migration Service for homogeneous MySQL migrations doesn't migrate user accounts and logins. Sources that contain metadata defined by user accounts with the DEFINER clause might block your migration.

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

  3. Make sure that GTID_MODE is set to ON or OFF, no other value is supported. Microsoft Azure Database for MySQL uses GTID_MODE = OFF by default.

    Database Migration Service requires GTID_MODE set to ON in the following scenarios:

    For more information about GTID_MODE, see Global Transaction ID System Variable.

  4. Verify and configure binary logging settings on your source database. Do the following:

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

    2. Set binlog_expire_logs_seconds to a period that is long enough to support the database migration. Generally, a week is sufficient.

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

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

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

  6. Create a dedicated migration user account in your instance. See Account management statements. Make sure that:

    • You allow the dedicated user account to connect from any host (host=%).
    • The password for the account doesn't exceed 32 characters in length. For more information about the MySQL user password length limitations, see MySQL Bug #43439.

  7. Assign the necessary privileges to the dedicated migration user account. For continuous migrations, the full list of required privileges depends on whether you provide your own dump files or use Database Migration Service managed dump feature:

    Managed dump

    If you want Database Migration Service to automatically take the full dump of your source database, assign the following privileges to the user account:

    Manual dump

    If you provide your own dump files created with mysqldump, you need to provide the privileges required for the CDC phase:

What's next

To get a complete, step-by-step migration walkthrough for homogeneous Cloud SQL for MySQL migrations, see Get started with Cloud SQL for MySQL migrations.