How do I migrate a MySQL database in a non-UTC time zone using AWS DMS tasks?

4 minute read
0

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.


AWS OFFICIAL
AWS OFFICIALUpdated 2 years ago