How to set time_zone for Source DB in AWS DMS?

Question

I am trying to migrate a MySQL database to Aurora, but can't get the timezone set.

According to the documentation: "Valid values are the standard time zone abbreviations for the operating system hosting the source MySQL database."

Executing date on my Linux (Ubuntu) machine shows: Thu Dec 7 10:27:48 AEDT 2017.

I have configured the Source Endpoint to use:

Extra connection attributes: initstmt=SET time_zone=AEDT

Which results in my connection test to fail with:

Error Details: [errType=ERROR_RESPONSE, status=1022502, errMessage=Cannot connect to ODBC provider ODBC general error., errDetails= RetCode: SQL_ERROR SqlState: HY000 NativeError: 1298 Message: [unixODBC][MySQL][ODBC 5.3(w) Driver]Unknown or incorrect time zone: 'AEDT' ODBC general error.]

I've tried "Australia/Sydney" as well (same value as in RDS Parameter Groups) but getting the same error.

Any ideas?

I am totally aware of that this should be UTC. Not my choice - legacy.

Update: It seems initstmt=SET time_zone="+11:00" works, but leading to this issue.

Don't quote the time zone identifier: `initstmt=SET time_zone=AEDT` — bishop, Dec 07 '17 at 00:09
Doesn't work either. Fails with recognizing parameter "time". — kev, Dec 07 '17 at 00:11
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/160663/discussion-between-bishop-and-kev). — bishop, Dec 07 '17 at 00:22

score 0 · Answer 1 · answered Dec 14 '17 at 07:36

Do you want to Migrate MySQL from a Linux server to Aurora, the user replication instead of DMS. DMS will not support many DDL during the data load. So create a replication between MySQL to Aurora.

Master - Your current MySQL. slave - Aurora.

Prepare:

Both instances are able to communicate with each other.
The master must be 5.5 or greater version.
binlog_format to ROW on the Master MySQL.

Create user for replication:

CREATE USER 'rep_user'@'%' IDENTIFIED BY 'rep_user';  
GRANT REPLICATION slave ON *.* TO 'rep_user'@'%' IDENTIFIED BY 'rep_user';  
FLUSH PRIVILEGES;

Take dump with Binlog details:

mysqldump -u user -p dbname --master-data=2 > backup.sql

less backup.sql this will give you the binlog file name and its position.

Restore the Backup:

mysql -h auroraendpoint -u user -p dbname < backup.sql

Enable replication:

CALL mysql.rds_set_external_master ('Master_RDS_EndPoint', 3306,    'Replication_user_name', 'Replication_User_password', 'BinLog_FileName', BingLog_Position, 0);

Strat replication:

call mysql.rds_start_replication;

Cutover:

During the maintenance window do a cutover and make the Aurora as a master.

call mysql.rds_stop_replication;  
CALL mysql.rds_reset_external_master;