Configure your source MySQL database for one-time migrations

This page describes how to configure the following source database instances for one-time 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. 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.

  4. Assign the necessary privileges to the dedicated migration user account. For one-time 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:

    • SELECT
    • SHOW VIEW
    • TRIGGER
    • RELOAD
    • For source MySQL versions 8.0 or later: Make sure the migration account doesn't have the BACKUP_ADMIN privilege.

    Manual dump

    If you provide your own dump files created with mysqldump, you don't need to assign any extra privileges to the user account.

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

  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 one-time 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 don't need to assign any extra privileges to the user account.

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. 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 one-time 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 don't need to assign any extra privileges to the user account.

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

  5. Assign the necessary privileges to the dedicated migration user account. For one-time 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 don't need to assign any extra privileges to the user account.

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

  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 one-time 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 don't need to assign any extra privileges to the user account.

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.