Issue with Time Zone while migrating MySQL on-premises DB to AWS RDS

Question

I am having MySQL 5.6 database with PST time format in on-premises server. Now we are willing to migrate the database to RDS.

For this, we have taken backup from physical server and restored in RDS and setup replication to sync the data.

But found the time is getting changed to UTC. We found the time format in RDS is UTC, so we changed the time format of time_zone to PST in parameter group. But we are not able to change the default time_zone parameter.

Due to this, the time which is coming from master server is getting updated to UTC and storing in database which is 8hrs more than actual time.

Due to constraints, we are not able to use AWS DMS for replicating data. How can we change the default time_zone or fix the time zone issue in RDS.

Answer 1

I was using MySQL replication with Mixed mode replication which captures the data with timestamp and timezone.

Later, I changed the MySQL replication to Row based replication which captures the data with time. So what ever the time format it is, it just send the insert/update statements with time, which solved my problem.

Answer 2

You can set connection attributes in DMS including serverTimezone

serverTimezone
Specifies the time zone for the source MySQL database.
Example: serverTimezone=US/Pacific;
Don't enclose time zone data in single quotation marks.

Documentation from AWS support

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.