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

Using a MySQL-Compatible Database as a Target for AWS Database Migration Service

You can migrate data to MySQL databases using AWS DMS, either from another MySQL database or from one of the other supported databases.

MySQL versions 5.5, 5.6, and 5.7, as well as MariaDB and Aurora MySQL, are supported.

For additional details on working with MySQL databases as a target for AWS DMS, see the following sections.

Prerequisites for Using a MySQL-Compatible Database as a Target for AWS Database Migration Service

Before you begin to work with a MySQL database as a target 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 When Using MySQL as a Target for AWS Database Migration Service.

  • A MySQL database with the tables that you want to replicate accessible in your network. AWS DMS supports the following MySQL editions:

    • MySQL Community Edition

    • MySQL Standard Edition

    • MySQL Enterprise Edition

    • MySQL Cluster Carrier Grade Edition

    • MariaDB

    • Amazon Aurora with MySQL compatibility

  • During a load, you should consider disabling foreign keys. In order to disable foreign key checks on a MySQL-compatible database during a load, you can add the following command to the Extra Connection Attributes in the Advanced section of the target MySQL, Aurora MySQL, MariaDB endpoint connection information:

    initstmt=SET FOREIGN_KEY_CHECKS=0

Limitations on Using MySQL as a Target for AWS Database Migration Service

When using a MySQL database as a target, 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.

In addition, when you update a column's value to its existing value, MySQL returns a 0 rows affected warning. In contrast, Oracle performs an update of one row in this case. The MySQL result generates an entry in the awsdms_apply_exceptions control table and the following warning:

Some changes from the source database had no impact when applied to the target database. See awsdms_apply_exceptions table for details.

Security Requirements When Using MySQL as a Target for AWS Database Migration Service

When using MySQL as a target for data migration, you must provide MySQL account access to the AWS DMS user account. This user must have read/write privileges in the MySQL database.

To create the necessary privileges, run the following commands:

CREATE USER '<user acct>'@'%' IDENTIFIED BY <user password>'; GRANT ALTER, CREATE, DROP, INDEX, INSERT, UPDATE, DELETE, SELECT ON myschema.* TO '<user acct>'@'%'; GRANT ALL PRIVILEGES ON awsdms_control.* TO '<user acct>'@'%';

Extra Connection Attributes When Using MySQL as a Target for AWS DMS

You can use extra connection attributes to configure your MySQL target. You specify these settings when you create the target endpoint. Multiple extra connection attribute settings should be separated by a semicolon.

The following table shows extra configuration settings you can use when creating a MySQL target for AWS DMS:

Name Description

targetDbType

Specifies where to migrate source tables on the target, either to a single database or multiple databases.

Default value: MULTIPLE_DATABASES

Valid values: {SPECIFIC_DATABASE, MULTIPLE_DATABASES}

Example: targetDbType=MULTIPLE_DATABASES

parallelLoadThreads

Improves performance when loading data into the MySQL target database. Specifies how many threads to use to load the data into the MySQL target database. Note that setting a large number of threads may have an adverse effect on database performance since a separate connection is required for each thread.

Default value: 1

Valid values: 1-5

Example: parallelLoadThreads=1

initstmt=SET FOREIGN_KEY_CHECKS=0

Disables foreign key checks.

initstmt=SET time-zone

Specifies the time zone for the target MySQL database.

Default value: UTC

Valid values: A three or four character abbreviation for the time zone you want to use. Valid values are the standard time zone abbreviations for the operating system hosting the target MySQL database.

Example: initstmt=SET time_zone=UTC

afterConnectScript=SET character_set_connection='latin1'

Improves the performance of certain operations on the target MySQL database when converting from Latin1 to UTF8.

maxFileSize

Specifies the maximum size (in KB) of any CSV file used to transfer data to MySQL.

Default value: 32768 KB (32 MB)

Valid values: 1 - 1048576

Example: maxFileSize=512

Target Data Types for MySQL

The following table shows the MySQL database target data types that are supported when using AWS DMS and the default mapping from AWS DMS data types.

For additional information about AWS DMS data types, see Data Types for AWS Database Migration Service.

AWS DMS Data Types

MySQL Data Types

BOOLEAN

BOOLEAN

BYTES

If the length is from 1 through 65,535, then use VARBINARY (length).

If the length is from 65,536 through 2,147,483,647, then use LONGLOB.

DATE

DATE

TIME

TIME

TIMESTAMP

"If scale is => 0 and =< 6, then: DATETIME (Scale)

If scale is => 7 and =< 9, then: VARCHAR (37)"

INT1

TINYINT

INT2

SMALLINT

INT4

INTEGER

INT8

BIGINT

NUMERIC

DECIMAL (p,s)

REAL4

FLOAT

REAL8

DOUBLE PRECISION

STRING

If the length is from 1 through 21,845, then use VARCHAR (length).

If the length is from 21,846 through 2,147,483,647, then use LONGTEXT.

UINT1

UNSIGNED TINYINT

UINT2

UNSIGNED SMALLINT

UINT4

UNSIGNED INTEGER

UINT8

UNSIGNED BIGINT

WSTRING

If the length is from 1 through 32,767, then use VARCHAR (length).

If the length is from 32,768 through 2,147,483,647, then use LONGTEXT.

BLOB

If the length is from 1 through 65,535, then use BLOB.

If the length is from 65,536 through 2,147,483,647, then use LONGBLOB.

If the length is 0, then use LONGBLOB (full LOB support).

NCLOB

If the length is from 1 through 65,535, then use TEXT.

If the length is from 65,536 through 2,147,483,647, then use LONGTEXT with ucs2 for CHARACTER SET.

If the length is 0, then use LONGTEXT (full LOB support) with ucs2 for CHARACTER SET.

CLOB

If the length is from 1 through 65535, then use TEXT.

If the length is from 65536 through 2147483647, then use LONGTEXT.

If the length is 0, then use LONGTEXT (full LOB support).