Menu
AWS Database Migration Service
User Guide (Version API Version 2016-01-01)

Using a MySQL-Compatible Database as a Source for AWS DMS

You can migrate data from one or many MySQL, MariaDB, or Amazon Aurora databases using AWS DMS. With a MySQL-compatible database as a source, you can migrate data to either another MySQL-compatible database or one of the other supported databases. MySQL versions 5.5, 5.6, and 5.7, and also MariaDB and Amazon Aurora, are supported for on-premises, Amazon RDS, and Amazon EC2 instance databases. To enable change data capture (CDC) with Amazon RDS MySQL, you must use Amazon RDS MySQL version 5.6 or higher.

Note

Regardless of the source storage engine (MyISAM, MEMORY, etc.), AWS DMS creates the MySQL-compatible target table as an InnoDB table by default. If you need to have a table that uses a storage engine other than InnoDB, you can manually create the table on the MySQL-compatible target and migrate the table using the "Do Nothing" mode. For more information about the "Do Nothing" mode, see Full Load Task Settings.

You can use SSL to encrypt connections between your MySQL-compatible endpoint and the replication instance. For more information on using SSL with a MySQL-compatible endpoint, see Using SSL With AWS Database Migration Service.

For additional details on working with MySQL-compatible databases and AWS DMS, see the following sections.

Prerequisites for Using a MySQL Database as a Source for AWS DMS

Before you begin to work with a MySQL database as a source for AWS DMS, make sure that you have the following prerequisites:

  • A MySQL account with the required security settings. For more information, see Security Requirements for Using a MySQL Database as a Source for AWS DMS.

  • A MySQL-compatible database with the tables that you want to replicate accessible in your network.

    • MySQL Community Edition

    • MySQL Standard Edition

    • MySQL Enterprise Edition

    • MySQL Cluster Carrier Grade Edition

    • MariaDB

    • Amazon Aurora

  • If your source is an Amazon RDS MySQL or MariaDB DB instance or an Amazon Aurora cluster, you must enable automatic backups. For more information on setting up automatic backups, see the Amazon RDS User Guide.

  • If you use change data capture (CDC), you must enable and configure binary logging. To enable binary logging, the following parameters must be configured in MySQL’s my.ini (Windows) or my.cnf (UNIX) file:

    Parameter

    Value

    server_id

    Set this parameter to a value of 1 or greater.

    log-bin

    Set the path to the binary log file, such as log-bin=E:\MySql_Logs\BinLog. Don't include the file extension.

    binlog_format

    Set this parameter to row.

    expire_logs_days

    Set this parameter to a value of 1 or greater. To prevent overuse of disk space, we recommend that you don't use the default value of 0.

    binlog_checksum

    Set this parameter to none.

    binlog_row_image

    Set this parameter to full.

  • To use change data capture (CDC) with an Amazon RDS MySQL DB instance as a source, AWS DMS needs access to the binary logs. Amazon RDS is fairly aggressive in clearing binary logs from the DB instance. To use CDC with a MySQL DB instance on Amazon RDS, you should increase the amount of time the binary logs remain on the MySQL DB instance. For example, to increase log retention to 24 hours, you would run the following command:

    Copy
    call mysql.rds_set_configuration('binlog retention hours', 24);
  • To replicate clustered (NDB) tables using AWS DMS, the following parameters must be configured in MySQL’s my.ini (Windows) or my.cnf (UNIX) file. Replicating clustered (NDB) tables is required only when you use CDC.

    Parameter

    Value

    ndb_log_bin

    Set this parameter to on. This value ensures that changes in clustered tables are logged to the binary log.

    ndb_log_update_as_write

    Set this parameter to OFF. This value prevents writing UPDATE statements as INSERT statements in the binary log.

    ndb_log_updated_only

    Set this parameter to OFF. This value ensures that the binary log contains the entire row and not just the changed columns.

Prerequisites for Using an Amazon RDS for MySQL Database as a Source for AWS DMS

When using an Amazon RDS for MySQL database as a source for AWS DMS, make sure that you have the following prerequisites:

  • You must enable automatic backups. For more information on setting up automatic backups, see the Amazon RDS User Guide.

  • To use change data capture (CDC) with an Amazon RDS MySQL DB instance as a source, AWS DMS needs access to the binary logs. Amazon RDS is fairly aggressive in clearing binary logs from the DB instance. To use CDC with a MySQL DB instance on Amazon RDS, you should increase the amount of time the binary logs remain on the MySQL DB instance to 24 hours or greater. For example, to increase log retention to 24 hours, you would run the following command:

    Copy
    call mysql.rds_set_configuration('binlog retention hours', 24);
  • The binlog_format parameter should be set to "ROW."

Limitations on Using a MySQL Database as a Source for AWS DMS

When using a MySQL database as a source, AWS DMS doesn't support the following:

  • The DDL statements Truncate Partition, Drop Table, and Rename Table.

  • Using an ALTER TABLE <table_name> ADD COLUMN <column_name> statement to add columns to the beginning or the middle of a table.

  • Capturing changes from tables whose names contain both uppercase and lowercase characters if the source MySQL instance is installed on an OS with a file system where file names are treated as case in-sensitive, for example Windows and OS X using HFS+.

  • The AR_H_USER header column.

  • The AUTO_INCREMENT attribute on a column is not migrated to a target database column.

  • Capturing changes when the binary logs are not stored on standard block storage. For example, CDC does not work when the binary logs are stored on Amazon S3.

Security Requirements for Using a MySQL Database as a Source for AWS DMS

As a security requirement, the AWS DMS user must have the ReplicationAdmin role with the following privileges:

  • REPLICATION CLIENT – This privilege is required for change data capture (CDC) tasks only. In other words, full-load-only tasks don't require this privilege.

  • REPLICATION SLAVE – This privilege is required for change data capture (CDC) tasks only. In other words, full-load-only tasks don't require this privilege.

  • SUPER – This privilege is required only in MySQL versions prior to 5.6.6.

The AWS DMS user must also have SELECT privileges for the source tables designated for replication.

Configuring a MySQL Database as a Source for AWS DMS

You can use extra connection attributes to configure the MySQL source. For more information about MySQL extra connection attributes, see Using Extra Connection Attributes with AWS Database Migration Service.