How do I migrate a MySQL database in a non-UTC time zone using AWS DMS tasks?
I have a source and target MySQL instance that's in a non-UTC time zone. I want to migrate the database using an AWS Database Migration Service (AWS DMS) task. How can I do this?
"I have a source and target MySQL instance that's in a non-UTC time zone. I want to migrate the database using an AWS Database Migration Service (AWS DMS) task. How can I do this?Short descriptionWhen you use MySQL as a source, the timestamp data isn't migrated properly if your source MySQL instance uses a non-UTC time zone. Internally, a MySQL timestamp column is stored as UTC. But, when you select a date, MySQL automatically converts the timestamp column to the current session's time zone. MySQL converts TIMESTAMP values from the current time zone to UTC for storage. Then, MySQL converts those values back from UTC to the current time zone for retrieval.Similarly, when you store a date in a timestamp, MySQL converts TIMESTAMP values from your current time zone to UTC. Then, it converts those values back from UTC to the current time zone for retrieval.When you use AWS DMS, data can be inconsistent if your source and target MySQL instances have a time zone other than UTC. For example, if you have a source and target MySQL database that runs in the US/Pacific, this is not in UTC. So, the data in the source is captured and then applied to the target as UTC, which makes the data inconsistent.ResolutionTo solve this issue, use an extra connection attribute/endpoint setting in the source endpoint serverTimezone. Then, set the value to the time zone of the MySQL database.serverTimezone=US/PacificCompare these examples, one with an extra connection attribute (ECA) in the source endpoint serverTimezone, and one without an ECA.Without ECA serverTimezone in the source endpointNote: If you don't use the ECA, you might see data inconsistency between your source and target databases.Sourcemysql>  SELECT @@global.time_zone, @@session.time_zone;+--------------------+---------------------+| @@global.time_zone | @@session.time_zone |+--------------------+---------------------+| US/Pacific         | US/Pacific          |+--------------------+---------------------+mysql> create  table test_tz ( id int primary key,t_date timestamp);Query OK, 0 rows affected (0.28 sec)mysql> insert into test_tz values (10, now());Query OK, 1 row affected (0.31 sec)mysql> select * from test_tz;+----+---------------------+| id | t_date              |+----+---------------------+| 10 | 2022-06-27 20:50:29 |+----+---------------------+1 row in set (0.25 sec)Now that you have created an AWS DMS task, after the data is migrated, it looks like this after full load:Targetmysql>  SELECT @@global.time_zone, @@session.time_zone;+--------------------+---------------------+| @@global.time_zone | @@session.time_zone |+--------------------+---------------------+| US/Pacific         | US/Pacific          |+--------------------+---------------------+1 row in set (0.30 sec)mysql> select * from test_tz;+----+---------------------+| id | t_date              |+----+---------------------+| 10 | 2022-06-28 03:50:29 |    ===> This shows timestamp in UTC in target +----+---------------------+1 row in set (0.25 sec)Now, perform an insert in the source database.Sourcemysql>  insert into test_tz values (11, now());Query OK, 1 row affected (0.38 sec)mysql> select * from test_tz;+----+---------------------+| id | t_date              |+----+---------------------+| 10 | 2022-06-27 20:50:29 || 11 | 2022-06-27 21:10:13 |+----+---------------------+2 rows in set (0.24 sec)Targetmysql> select * from test_tz;+----+---------------------+| id | t_date              |+----+---------------------+    | 10 | 2022-06-28 03:50:29 || 11 | 2022-06-28 04:10:13 |    ===> This shows timestamp in UTC in target +----+---------------------+2 rows in set (0.25 sec)With ECA serverTimezone=US/Pacific in the source endpointData after full load:Sourcemysql> select * from test_tz;   +----+---------------------+   | id | t_date              |   +----+---------------------+   | 10 | 2022-06-27 20:50:29 |   | 11 | 2022-06-27 21:10:13 |   +----+---------------------+Targetmysql> select * from test_tz;   +----+---------------------+   | id | t_date              |   +----+---------------------+   | 10 | 2022-06-27 20:50:29 |   | 11 | 2022-06-27 21:10:13 |   +----+---------------------+Data during change data capture (CDC)Source3 rows in set (0.25 sec)+----+---------------------+| 12 | 2022-06-28 04:12:06 || 11 | 2022-06-28 04:10:13 || 10 | 2022-06-28 03:50:29 |+----+---------------------+| id | t_date              |+----+---------------------+mysql> select * from test_tz;mysql> mysql> Query OK, 1 row affected (0.38 sec)mysql> insert into test_tz values (12, current_time());Target3 rows in set (0.25 sec)+----+---------------------+| 12 | 2022-06-28 04:12:06 || 11 | 2022-06-28 04:10:13 || 10 | 2022-06-28 03:50:29 |+----+---------------------+| id | t_date              |+----+---------------------+mysql> select * from test_tz;So, the ECA serverTimezone helps you migrate and replicate the timestamp data when your source MySQL instance is using a non-UTC time zone.Follow"