Shulou(Shulou.com)06/01 Report--

This article mainly introduces how to deal with the data inconsistency caused by mysql timezone. The content of the article is carefully selected and edited by the author. It has a certain pertinence and is of great significance to everyone's reference. The following is to understand with the author how to deal with the data inconsistency caused by mysql timezone.

Mysql > select * From_unixtime (create_time) from table name where source_id=xxx\ row * * id: xxx uid: xxx source_id: xxx Create_time: 1495324892 type: 0 source_status: 0 nick_name: environmental Monitoring Group-xxx start_time: 1495324883 remark: ignore Video pre_check_status: 0 source: 3from_unixtime (create_time): 2017-05-21 00 01purl 321 row in set (0.00 sec)

Because the operation library is two masters and two slaves, I compared the master and slave data and found that it is indeed different. One is 8: 00 in the morning, the other is in the early morning, using the from_unixtime function, the result is 8 hours apart, which is more like a time zone problem.

Mysql > select from_unixtime (1495324892) +-- + | from_unixtime (1495324892) | +-- + | 2017-05-21 08:01:32 | +-- + 1 row in set (1495324892 sec) mysql > select from_unixtime (1495324892) +-- + | from_unixtime (1495324892) | +-- + | 2017-05-21 00:01:32 | +-- + 1 sec (2017 sec)

Suspected to be the time zone problem of the linux machine, but the query found that it was all the same.

Thu May 25 15:11:29 CST 2017

Since it has nothing to do with the linux time zone, it should be related to the time of the MySQL. Query the master and slave time again.

Mysql > select now (); +-+ | now () | +-+ | 0-05-25 15:12:28 | +-+ 1 row in set (0.00 sec) mysql > select now () +-+ | now () | +-+ | 0-05-25 07:09:31 | +-+ 1 row in set (0.00 sec)

It's really a matter of time for MySQL.

Mysql > show variables like "time_zone%" +-+-+ | Variable_name | Value | +-+-+ | system_time_zone | HKT | | time_zone | SYSTEM | +-+-+ 2 rows in set (0.00 sec) mysql > show variables like "% time_zone%" +-+-+ | Variable_name | Value | +-+-+ | system_time_zone | UTC | | time_zone | SYSTEM | +-+-+ 2 rows in set (0.00 sec)

It is found that one MySQL is in UTC time, World Standard time, and the other is Hong Kong time. China is the East eighth District, which is 8 hours more than the world standard time, which explains why it is shown to clock in at 8 o'clock but go to work in the early hours of the morning. So change the MySQL in question and modify the time zone.

Set global time_zone ='+ 8rig 00mm / set time_zone ='+ 8lv / 00mm / s flush privileges

After reading the above about how to deal with the data inconsistency caused by mysql timezone, many readers must have some understanding. If you need to get more industry knowledge and information, you can continue to pay attention to our industry information column.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.