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 description
When 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.
Resolution
To 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/Pacific
Compare 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 endpoint
Note: If you don't use the ECA, you might see data inconsistency between your source and target databases.
Source
mysql> 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:
Target
mysql> 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.
Source
mysql> 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)
Target
mysql> 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 endpoint
Data after full load:
Source
mysql> select * from test_tz;
+----+---------------------+
| id | t_date |
+----+---------------------+
| 10 | 2022-06-27 20:50:29 |
| 11 | 2022-06-27 21:10:13 |
+----+---------------------+
Target
mysql> 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)
Source
3 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());
Target
3 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.