Come posso migrare un database MySQL in un fuso orario non UTC utilizzando le attività AWS DMS?

Breve descrizione

Quando utilizzi MySQL come origine, i dati del timestamp non vengono migrati correttamente se l'istanza MySQL di origine utilizza un fuso orario non UTC. Internamente, una colonna di timestamp MySQL viene memorizzata come UTC. Tuttavia, quando selezioni una data, MySQL converte automaticamente la colonna del timestamp nel fuso orario della sessione corrente. MySQL converte i valori TIMESTAMP dal fuso orario corrente in UTC per l'archiviazione. Quindi, MySQL converte questi valori dall'UTC al fuso orario corrente per il recupero.

Allo stesso modo, quando memorizzi una data in un timestamp, MySQL converte i valori TIMESTAMP dal fuso orario corrente in UTC. Quindi, converte nuovamente tali valori da UTC al fuso orario corrente per il recupero.

Quando usi AWS DMS, i dati possono essere incoerenti se le istanze MySQL di origine e di destinazione hanno un fuso orario diverso da UTC. Ad esempio, se disponi di un database MySQL di origine e di destinazione che viene eseguito negli Stati Uniti/Pacifico, questo non è in UTC. Pertanto, i dati nell'origine vengono acquisiti e quindi applicati alla destinazione come UTC, il che rende i dati incoerenti.

Risoluzione

Per risolvere questo problema, utilizza un'impostazione aggiuntiva di attributo/endpoint di connessione nell'endpoint di origine ServerTimeZone. Quindi, imposta il valore sul fuso orario del database MySQL.

serverTimezone=US/Pacific

Confronta questi esempi, uno con un attributo di connessione aggiuntivo (ECA) nell'endpoint ServerTimeZone dell'endpoint di origine e uno senza ECA.

Senza ECA ServerTimeZone nell'endpoint di origine

Nota: se non utilizzi ECA, è possibile che si verifichi un'incoerenza dei dati tra i database di origine e di destinazione.

Origine

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)

Ora che hai creato un'attività AWS DMS, dopo la migrazione dei dati, appare così dopo il caricamento completo:

Destinazione

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)

Ora, esegui un inserimento nel database di origine.

Origine

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)

Destinazione

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)

Con ECA ServerTimeZone=US/Pacific nell'endpoint di origine

Dati dopo il pieno carico:

Origine

mysql> select * from test_tz;
   +----+---------------------+
   | id | t_date              |
   +----+---------------------+
   | 10 | 2022-06-27 20:50:29 |
   | 11 | 2022-06-27 21:10:13 |
   +----+---------------------+

Destinazione

mysql> select * from test_tz;
   +----+---------------------+
   | id | t_date              |
   +----+---------------------+
   | 10 | 2022-06-27 20:50:29 |
   | 11 | 2022-06-27 21:10:13 |
   +----+---------------------+

Dati durante l'acquisizione dei dati di modifica (CDC)

Origine

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());

Destinazione

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;

Pertanto, ECA ServerTimeZone consente di migrare e replicare i dati di timestamp quando l'istanza MySQL di origine utilizza un fuso orario non UTC.

---