Shulou(Shulou.com)05/31 Report--

How to solve the mysqldump time domain problem, many novices are not very clear, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

Today we will discuss the time domain problem of mysqldump again.

Problem introduction

A few days ago, a data cleaning script was deployed in a customer's system. Before cleaning the data, the script will first use mysqldump to export the data to be cleaned according to the conditions for cleaning the data, and then clean up. The script uses the crontab timed task to execute at 2 a.m. However, when I checked the execution of the script the next day, I found that the data cleaning work was completed smoothly, but only the table structure was exported in the SQL file exported by mysqldump, but no data was exported. It is very strange to see this phenomenon.

Problem analysis and troubleshooting

1. The where condition for viewing exported data is "gmt_modified"

< date_sub(curdate(),interval 359 day)"，基于之前的理解，我们想过有可能是时域的问题，所以确认了一下gmt_modified字段的数据类型，查看确认gmt_modified的数据类型为datetime，由于datetime数据类型是与时域无关的，所以针对这一问题，排除了时域对导出数据的影响。 2.难道是这个脚本在当前服务器的环境问题？将导出数据的条件改为"gmt_modified < date_sub(curdate(),interval 358 day)"，在这个条件下会查询出一天的数据，将脚本当中的数据删除部分注释掉，只执行数据导出的部分，发现该脚本完整的导出了数据。实在让人疑惑，为什么白天上班的时候数据能够备份出来，然而凌晨的时候数据就备不出来？ 3.这时候，怀疑是不是当时数据库处于某种状态，阻止了mysqldump的备份。于是写了一个脚本，每隔一秒去检测当前数据库的连接状态。加入crontab，与删除数据的脚本在凌晨同时调起。数据清理脚本大约1分钟执行完成，于是设定数据库连接监控脚本执行3分钟。第二天观察监控的日志，也并未发现有任何异常的连接。 4.手动执行脚本能够备份成功，crontab就无法备份，难道真的有什么鬼故事？于是在凌晨2点手动执行备份脚本，发现的确无法备份。将脚本当中的mysqldump语句摘录出来，单独执行，仍然没有备份成功。看来备份失败与脚本、与环境都没有关系，就是mysqldump的问题。又回到问题的起点，难道真的是时域惹的祸？于是在mysqldump时加上--skip-tz-utc的参数。执行备份，这次备份成功了。 --skip-tz-utc参数介绍 为什么--skip-tz-utc参数会影响mysqldump导出的时域呢，下面先简要介绍一下--skip-tz-utc这个参数。 在mysql服务器上执行mysqldump --help的命令，可以看到下面一段话。 --tz-utc SET TIME_ZONE='+00:00' at top of dump to allow dumping of TIMESTAMP data when a server has data in different time zones or data is being moved between servers with different time zones. (Defaults to on; use --skip-tz-utc to disable.) --tz-utc参数是mysqldump的默认参数，会使得mysqldump的导出文件的顶部加上一个设置时域的语句SET TIME_ZONE='+00:00'，这个时域是格林威治时间，这样当导出timestamp字段时，会把在服务器设置的当前时域下显示的timestamp时间值转化为在格林威治时间下显示的时间。如下图所示，mysqldump导出的文件当中显示的时间值相对于通过数据库查询显示的时间倒退了8个小时。 mysql>

Show variables like "time_zone"; +-+-+ | Variable_name | Value | +-+-+ | time_zone | + 08:00 | +-+-+ 1 row in set (0.01 sec) mysql > show create table t_timestamp +- - -Table | Create Table | +-+- - - -- + | t_timestamp | CREATE TABLE `t _ timestamp` (`id` int (11) NOT NULL AUTO_INCREMENT `name` varchar (255) COLLATE utf8_bin NOT NULL, `create_ time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP PRIMARY KEY (`id`) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8 COLLATE=utf8_bin | +-+- - -+ 1 row in set (0.00 sec) mysql > select * from t_timestamp +-+ | id | name | create_time | +-+ | 1 | messi | 2019-12-07 13:27:55 | | 3 | xavi | 2019-12-07 13:28:01 | | 5 | xsh | 20119-12-07 13:28:08 | | 7 | cr7 | 20119-12-08 14:24:18 | 9 | ozil | 20119-12-08 14:24:26 | | 11 | ramos | 20119-12-08 14:24:33 | 13 | pique | 20119-12-09 08:24:24 | 15 | henry | 20119-12-09 08:24:34 | 17 | lukaku | 20119-12-10 12:00:58 | 19 | rakitici | 20119-09 | 12-10 12:01:12 | | 21 | van dijk | 2019-12-11 22:00:46 | | 23 | mane | 2019-12-11 22:00:57 | 25 | suarez | 2019-12-11 22:01:34 | | 27 | Ronaldol | 2019-12-11 22:01:55 | 29 | Ronaldiho | 2019-12-12 18:00:20 | 31 | Deco | 2019-12-12 18:00:28 | +-+- -+-+ 16 rows in set (0.00 sec) [root@rhel74 timestamp] # mysqldump-- single-transaction-uroot-p123456-S / home/mysql/data/mysqldata1/sock/mysql.sock-- set-gtid-purged=OFF xshtest t_timestamp > full_timestamp.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@rhel74 timestamp] # vim fulltimestamp.sqlUniverse! 40101 SET @ OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT * / / *! 40101 SET @ OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS * /; / *! 40101 SET @ OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION * /; / *! 40101 SET NAMES utf8 * /; / *! 40103 SET @ OLD_TIME_ZONE=@@TIME_ZONE * /; / *! 40103 SET TIME_ZONE='+00:00' * /; / *! 40014 SET @ OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 * / / *! 40014 SET @ OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 * /; / *! 40101 SET @ OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' * /; / *! 40111 SET @ OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 * /; LOCK TABLES `t_ timestamp`WRITE lead / 40000 ALTER TABLE `ttimestamp` DISABLE KEYS * / INSERT INTO `t _ timestamp` VALUES (2019-12-07 05 purge 27purl 55'), (3mai Xaviqian memo 2019-12-07 05 purl 28 purl 01'), (5 meme XShengmei 2019-12-07 07 05 purge 28 purl 08'), (7 minus cr7 purl pr é cor 2019-12-07 06 purl 24 purl 18'), (9 prima prima ozilary parade 2019-12-08 06 mono 2414 26'), (11primer ramoscope 2019-12-08 0614 2414) (2019-12-09 0000 24'), (15-10-12-09 00 24'), (17-10-12-100 4)), (19-9-12-100 4) 2012-12-100 4), (21-12-12-11 14 00 46'), (25). 'suarez','2019-12-11 14 01purl 34'), (27 recorder Ronaldoleeju 2019-12-11 14 purl 01purl 55'), (29 pamphlet Ronaldihoe pamphlet 2019-12-12 10 00 rig 20'), (31 miner Decoy miner 2019-12-12 10 00 rime 28') / *! 40000 ALTER TABLE `t _ timestamp` ENABLE KEYS * /; UNLOCK TABLES _ timestamp` SET TIME_ZONE=@OLD_TIME_ZONE * /

I know-- tz-utc, then-- skip-tz-utc means that when mysqldump exports data, it does not use Greenwich mean time, but uses the time domain of the current mysql server to export. As shown in the following code, this backup uses-- skip-tz-utc, the time domain is not set in the statement of the exported file, and the time value displayed in the exported data is the same as the time value queried in the table.

[root@rhel74 timestamp] # mysqldump-- single-transaction-uroot-p123456-S / home/mysql/data/mysqldata1/sock/mysql.sock-- set-gtid-purged=OFF-- skip-tz-utc xshtest t_timestamp > full_timestamp_without_tz_utc.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@rhel74 timestamp] # vim fulltime timestamping with outdated timestamping utc.sqlGard accounts 40101 SET @ OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT * / / *! 40101 SET @ OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS * /; / *! 40101 SET @ OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION * /; / *! 40101 SET NAMES utf8 * /; / *! 40014 SET @ OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 * /; / *! 40014 SET @ OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 * / / *! 40101 SET @ OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' * /; / *! 40111 SET @ OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 * /; LOCK TABLES `t_ timestamp`WRITE lead / 40000 ALTER TABLE `ttimestamp` DISABLE KEYS * / INSERT INTO `ttimestamp` VALUES (1) (2019-12-07 13 purge 275 55'), (3) 3MIT Xavizhu memoir 2019-12-07 13 purl 28 purl 01'), (5) 5MIT XShLZ mater 2019-12-07 13 28 purl 28 RV 08'), (7 pr 7 mins cr7 cr 7 prizes 2019-12-08 14 140 24 rides 18'), (9 minus ozillic beacons 2019-12-08 14 140 24 24 26'), (11 priors ramoseries 2019-12-14 04 24 24') (2019-12-09-09 08), (15-14-12-09-09-08), (17-12-12-10-12, 2019-12-10, 12-12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 'suarez','2019-12-11 22-18-12-12-12-12-12-18-00-20'), (31-31-12-12-12-18-12-18-00) / *! 40000 ALTER TABLE `t_ timestamp` ENABLE KEYS * /; UNLOCK TABLES

So what is the meaning of this parameter? When some companies have multinational operations, they need to deploy two mysql servers in two time zones, both of which set the time domain of the servers according to their respective time zones. Suppose one server is in Beijing (East eighth District) and one server is in Tokyo (East Ninth District). Now you need to import the data from the Beijing server to the Tokyo server. As shown in the following code, when importing a dump file that does not add the-- skip-tz-utc parameter, the data of the queried t_timestamp table is one hour more than the time value of the server in the previous East eighth District, but because 13:00 in the server in the East eighth District and 14:00 in the East Nine District server represent the same time, so the extra hour displayed in the server in the East Ninth District is correct. If you do not add the-- skip-tz-utc parameter, after the dump file is imported into the East Ninth District server, although the time value displayed is the same as that previously displayed by the East eighth District server, the time represented by the two is already different. 13:00 in the East Ninth District is one hour slower than the 13:00 in the East eighth District.

[root@rhel74 timestamp] # mysql-utokyo-ptokyo-h292.168.239.31 tokyotest mysql > show variables like "time_zone" +-+-+ | Variable_name | Value | +-+-+ | time_zone | + 09:00 | +-+-+ 1 row in set (0.02 sec) # Import dump without-- skip-tz-utc parameter File [root@rhel74 timestamp] # mysql-utokyo-ptokyo-h292.168.239.31 tokyotest

< full_timestamp.sql mysql: [Warning] Using a password on the command line interface can be insecure.[root@rhel74 timestamp]# mysql -utokyo -ptokyo -h292.168.239.31 tokyotest mysql>

Select * from t_timestamp +-+ | id | name | create_time | +-+ | 1 | messi | 2019-12-07 14:27:55 | | 3 | xavi | 2019-12-07 14:28:01 | | 5 | xsh | 20119-12-07 14:28:08 | | 7 | cr7 | 20119-12-08 15:24:18 | 9 | ozil | 20119-12-08 15:24:26 | | 11 | ramos | 20119-12-08 15:24:33 | 13 | pique | 20119-12-09 09:24:24 | 15 | henry | 20119-12-09 09:24:34 | 17 | lukaku | 20119-12-10 13:00:58 | 19 | rakitici | 20119-09 | 12-10 13:01:12 | | 21 | van dijk | 2019-12-11 23:00:46 | | 23 | mane | 2019-12-11 23:00:57 | 25 | suarez | 2019-12-11 23:01:34 | | 27 | Ronaldol | 2019-12-11 23:01:55 | 29 | Ronaldiho | 2019-12-12 19:00:20 | 31 | Deco | 2019-12-12 19:00:28 | +-+- -- +-+ 16 rows in set (0.01sec) # Import the dump file with-- skip-tz-utc parameter [root@rhel74 timestamp] # mysql-utokyo-ptokyo-h292.168.239.31 tokyotest

< full_timestamp_without_tz_utc.sql mysql: [Warning] Using a password on the command line interface can be insecure.[root@rhel74 timestamp]# mysql -utokyo -ptokyo -h292.168.239.31 tokyotest mysql>

Select * from t_timestamp +-+ | id | name | create_time | +-+ | 1 | messi | 2019-12-07 13:27:55 | | 3 | xavi | 2019-12-07 13:28:01 | | 5 | xsh | 20119-12-07 13:28:08 | | 7 | cr7 | 20119-12-08 14:24:18 | 9 | ozil | 20119-12-08 14:24:26 | | 11 | ramos | 20119-12-08 14:24:33 | 13 | pique | 20119-12-09 08:24:24 | 15 | henry | 20119-12-09 08:24:34 | 17 | lukaku | 20119-12-10 12:00:58 | 19 | rakitici | 20119-09 | 12-10 12:01:12 | | 21 | van dijk | 2019-12-11 22:00:46 | | 23 | mane | 2019-12-11 22:00:57 | 25 | suarez | 2019-12-11 22:01:34 | | 27 | Ronaldol | 2019-12-11 22:01:55 | 29 | Ronaldiho | 2019-12-12 18:00:20 | 31 | Deco | 2019-12-12 18:00:28 | +-+- -+-+ 16 rows in set (0.01 sec)

After the above tests, we know that whether the addition of-- skip-tz-utc parameter will affect the import and export of the timastamp field, will it affect the datetime time field? We also did the following test, which shows that there is a SET TIME_ZONE='+00:00' setting time domain statement at the top of the skip-tz-utc,dump file, and if you add-- skip-tz-utc, there is no such statement, so the current server's time domain is used. But the data exported by both dump files is the same as the time value of the query in the database.

# query of data in the server of Dongqi District mysql > show variables like "time_zone" +-+-+ | Variable_name | Value | +-+-+ | time_zone | + 08:00 | +-+-+ 1 row in set (sec) mysql > show create table t_datetime +- - -+ | Table | Create Table | + - - -+ | t_datetime | CREATE TABLE `t _ datetime` (`id` int (11) NOT NULL AUTO_INCREMENT `name` varchar (255) COLLATE utf8_bin NOT NULL, `create_ time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP PRIMARY KEY (`id`) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8 COLLATE=utf8_bin | +-+- - -+ 1 row in set (0.06 sec) mysql > select * from t_datetime +-+ | id | name | create_time | +-+ | 1 | messi | 2019-12-07 13:27:55 | | 3 | xavi | 2019-12-07 13:28:01 | | 5 | xsh | 20119-12-07 13:28:08 | | 7 | cr7 | 20119-12-08 14:24:18 | 9 | ozil | 20119-12-08 14:24:26 | | 11 | ramos | 20119-12-08 14:24:33 | 13 | pique | 20119-12-09 08:24:24 | 15 | henry | 20119-12-09 08:24:34 | 17 | lukaku | 20119-12-10 12:00:58 | 19 | rakitici | 20119-09 | 12-10 12:01:12 | | 21 | van dijk | 2019-12-11 22:00:46 | | 23 | mane | 2019-12-11 22:00:57 | 25 | suarez | 2019-12-11 22:01:34 | | 27 | Ronaldol | 2019-12-11 22:01:55 | 29 | Ronaldiho | 2019-12-12 18:00:20 | 31 | Deco | 2019-12-12 18:00:28 | +-+- -- +-+ 16 rows in set (0.00 sec) # Export without-- skip-tz-utc parameter [root@rhel74 datetime] # mysqldump-- single-transaction-uroot-p123456-S / home/mysql/data/mysqldata1/sock/mysql.sock-- set-gtid-purged=OFF xshtest t_datetime > full_t_datetime.sql mysqldump: [Warning] Using a password on the command line interface can be insecure . [root@rhel74 datetime] # vim fullcharts datetime.sqlUniverse / 40101 SET @ OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT * / / *! 40101 SET @ OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS * /; / *! 40101 SET @ OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION * /; / *! 40101 SET NAMES utf8 * /; / *! 40103 SET @ OLD_TIME_ZONE=@@TIME_ZONE * /; / *! 40103 SET TIME_ZONE='+00:00' * /; / *! 40014 SET @ OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 * / / *! 40014 SET @ OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 * /; / *! 40101 SET @ OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' * /; / *! 40111 SET @ OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 * /; LOCK TABLES `t _ datetime`WRITEX DISABLE KEYS * / 40000 ALTER TABLE `tdatetime` INSERT INTO `tdatetime` VALUES (2019-12-07 13 rides 27purs 55'), (3 miners Xavizhong 2019-12-07 13 laps 28 laps 01'), (5 miners xshengliao 2019-12-07 13 collections 28 28 lbs 8'), (7 pics cr7 prizes 2019-12-08 14 rides 24 rides 18'), (9 miners ozillic beacons 2019-12-08 14 140 24ones 26'), (11 priors ramoseries 2019-12-08 14 140 24 rides 33'). (2019-12-09-09 08), (15-14-12-09-09-08), (17-12-12-10-12, 2019-12-10, 12-12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 'suarez','2019-12-11 22-18-12-12-12-12-12-18-00-20'), (31-31-12-12-12-18-12-18-00) / *! 40000 ALTER TABLE `t _ datetime` ENABLE KEYS * /; UNLOCK TABLESwitting accounts 40103 SET TIME_ZONE=@OLD_TIME_ZONE * / # add-- skip-tz-utc parameter [root@rhel74 datetime] # mysqldump-- single-transaction-uroot-p123456-S / home/mysql/data/mysqldata1/sock/mysql.sock-- set-gtid-purged=OFF-- skip-tz-utc xshtest t_datetime > full_t_datetime_without_tz_utc.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@rhel74 datetime] # vim full_t_datetime_without_tz_utc.sql/ *! 40101 SET @ OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT * / / *! 40101 SET @ OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS * /; / *! 40101 SET @ OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION * /; / *! 40101 SET NAMES utf8 * /; / *! 40014 SET @ OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 * /; / *! 40014 SET @ OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 * / / *! 40101 SET @ OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' * /; / *! 40111 SET @ OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 * /; LOCK TABLES `t _ datetime`WRITEX DISABLE KEYS * / 40000 ALTER TABLE `tdatetime` INSERT INTO `tdatetime` VALUES (2019-12-07 13 rides 27purs 55'), (3 miners Xavizhong 2019-12-07 13 laps 28 laps 01'), (5 miners xshengliao 2019-12-07 13 collections 28 28 lbs 8'), (7 pics cr7 prizes 2019-12-08 14 rides 24 rides 18'), (9 miners ozillic beacons 2019-12-08 14 140 24ones 26'), (11 priors ramoseries 2019-12-08 14 140 24 rides 33'). (2019-12-09-09 08), (15-14-12-09-09-08), (17-12-12-10-12, 2019-12-10, 12-12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 'suarez','2019-12-11 22-18-12-12-12-12-12-18-00-20'), (31-31-12-12-12-18-12-18-00) / *! 40000 ALTER TABLE `t_ datetime` ENABLE KEYS * /; UNLOCK TABLES

We import these two kinds of dump files to the Dongjiu server, and we can see from the following test that after import, the time value displayed by the two dump files in the Dongjiu server is the same, and this time value is also the same as the time value displayed in the Dongjiu server. But this is the same problem as the export of the timestamp field plus-- skip-tz-utc. The same time value is displayed in different time domain servers, but the same time value does not represent the same time in different time domain servers. So this is why it is better to use the timestamp field when you have business with multinational and cross-time zones.

# Dongjiu Server [root@rhel74 datetime] # mysql-utokyo-ptokyo-h292.168.239.31 tokyotest mysql > show variables like "time_zone" +-+-+ | Variable_name | Value | +-+-+ | time_zone | + 09:00 | +-+-+ 1 row in set (0.02 sec) # Import dump without-- skip-tz-utc parameter File [root@rhel74 datetime] # mysql-utokyo-ptokyo-h292.168.239.31 tokyotest select * from t_datetime +-+ | id | name | create_time | +-+ | 1 | messi | 2019-12-07 13:27:55 | | 3 | xavi | 2019-12-07 13:28:01 | | 5 | xsh | 20119-12-07 13:28:08 | | 7 | cr7 | 20119-12-08 14:24:18 | 9 | ozil | 20119-12-08 14:24:26 | | 11 | ramos | 20119-12-08 14:24:33 | 13 | pique | 20119-12-09 08:24:24 | 15 | henry | 20119-12-09 08:24:34 | 17 | lukaku | 20119-12-10 12:00:58 | 19 | rakitici | 20119-09 | 12-10 12:01:12 | | 21 | van dijk | 2019-12-11 22:00:46 | | 23 | mane | 2019-12-11 22:00:57 | 25 | suarez | 2019-12-11 22:01:34 | | 27 | Ronaldol | 2019-12-11 22:01:55 | 29 | Ronaldiho | 2019-12-12 18:00:20 | 31 | Deco | 2019-12-12 18:00:28 | +-+- -- +-+ 16 rows in set (0.00 sec) # Import dump file with-- skip-tz-utc parameter [root@rhel74 datetime] # mysql-utokyo-ptokyo-h292.168.239.31 tokyotest

< full_t_datetime_without_tz_utc.sql mysql: [Warning] Using a password on the command line interface can be insecure.[root@rhel74 datetime]# mysql -utokyo -ptokyo -h292.168.239.31 tokyotest mysql>

Select * from t_datetime +-+ | id | name | create_time | +-+ | 1 | messi | 2019-12-07 13:27:55 | | 3 | xavi | 2019-12-07 13:28:01 | | 5 | xsh | 20119-12-07 13:28:08 | | 7 | cr7 | 20119-12-08 14:24:18 | 9 | ozil | 20119-12-08 14:24:26 | | 11 | ramos | 20119-12-08 14:24:33 | 13 | pique | 20119-12-09 08:24:24 | 15 | henry | 20119-12-09 08:24:34 | 17 | lukaku | 20119-12-10 12:00:58 | 19 | rakitici | 20119-09 | 12-10 12:01:12 | | 21 | van dijk | 2019-12-11 22:00:46 | | 23 | mane | 2019-12-11 22:00:57 | 25 | suarez | 2019-12-11 22:01:34 | | 27 | Ronaldol | 2019-12-11 22:01:55 | 29 | Ronaldiho | 2019-12-12 18:00:20 | 31 | Deco | 2019-12-12 18:00:28 | +-+- -+-+ 16 rows in set (0.01 sec)

Through the above tests, we learned that the skip-tz-utc parameter affects the mysqldump exported timestamp field, but does not affect the datetime field. But this understanding of the problems we encountered in production at the beginning makes us even more confused. Doesn't the time domain problem affect the export of datetime fields? So why is it that when you export data in mysqldump using the datetime field as a where conditional field, it cannot be exported in the wee hours of the morning, but can be exported normally during the day? For these questions, we have carried out the following tests.

Experimental verification

1. Environment introduction

The database version used in this test is mysql5.7.22.

Mysql > select version (); +-+ | version () | 5.7.22-log | +-+

The current time domain set by the mysql server is the time domain of the East eighth District.

Mysql > show variables like "time_zone"; +-+-+ | Variable_name | Value | +-+-+ | time_zone | + 08:00 | +-+-+

The current system time is more than two o'clock in the morning at 2019-12-13 Beijing time, and if it is calculated as GMT, it is now more than 18:00 in the afternoon of 2019-12-12.

Mysql > select now (); +-+ | now () | +-+ | 2019-12-13 02:17:36 | +-+

The table structure of the t _ datetime table for testing is as follows.

CREATE TABLE `tdatetime` (`id` int (11) NOT NULL AUTO_INCREMENT, `name` varchar (255) COLLATE utf8_bin NOT NULL, `create_ time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

The table simulates six days of data from 2019-12-07 to 2019-12-12.

Mysql > select * from t_datetime +-+ | id | name | create_time | +-+ | 1 | messi | 2019-12-07 13:27:55 | | 3 | xavi | 2019-12-07 13:28:01 | | 5 | xsh | 20119-12-07 13:28:08 | | 7 | cr7 | 20119-12-08 14:24:18 | 9 | ozil | 20119-12-08 14:24:26 | | 11 | ramos | 20119-12-08 14:24:33 | 13 | pique | 20119-12-09 08:24:24 | 15 | henry | 20119-12-09 08:24:34 | 17 | lukaku | 20119-12-10 12:00:58 | 19 | rakitici | 20119-09 | 12-10 12:01:12 | | 21 | van dijk | 2019-12-11 22:00:46 | | 23 | mane | 2019-12-11 22:00:57 | 25 | suarez | 2019-12-11 22:01:34 | | 27 | Ronaldol | 2019-12-11 22:01:55 | 29 | Ronaldiho | 2019-12-12 18:00:20 | 31 | Deco | 2019-12-12 18:00:28 | +-+- -+-+

two。 Back up data from 5 days ago in t_datetime table without skip-tz-utc

According to the query conditions of 5 days ago, we can see that three pieces of data can be found from the table under this condition. Then according to our requirements, mysqldump should also back up the following 3 pieces of data.

Mysql > select * from t_datetime where create_time

< date_sub(curdate(), interval 5 day); +----+-------+---------------------+ | id | name | create_time | +----+-------+---------------------+ | 1 | messi | 2019-12-07 13:27:55 | | 3 | xavi | 2019-12-07 13:28:01 | | 5 | xsh | 2019-12-07 13:28:08 | +----+-------+---------------------+ 然而，事实上，按照create_time < date_sub(curdate(), interval 5 day)的条件，mysqldump没有备份出任何的数据。 [root@rhel74 datetime]# mysqldump --single-transaction -uroot -p123456 -S /home/mysql/data/mysqldata1/sock/mysql.sock --set-gtid-purged=OFF xshtest t_datetime --where="create_time < date_sub(curdate(), interval 5 day)" >

5_day_ago_without_skip_tz_utc.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@rhel74 datetime] # vim 5 days daytime agrology with outdated skippets tzily utc.sql-Dumping data for table `tdatetime`-WHERE: create_time

< date_sub(curdate(), interval 5 day) LOCK TABLES `t_datetime` WRITE;/*!40000 ALTER TABLE `t_datetime` DISABLE KEYS */;/*!40000 ALTER TABLE `t_datetime` ENABLE KEYS */; UNLOCK TABLES;/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; 3. 带skip-tz-utc备份t_datetime表5天以前的数据 在mysqldump的命令加上了--skip-tz-utc的参数，再次查看备份文件，可以看到这次备份出了我们想要的数据。 [root@rhel74 datetime]# mysqldump --single-transaction -uroot -p123456 -S /home/mysql/data/mysqldata1/sock/mysql.sock --set-gtid-purged=OFF --skip-tz-utc xshtest t_datetime --where="create_time < date_sub(curdate(), interval 5 day)" >

5_day_ago_with_skip_tz_utc.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@rhel74 datetime] # vim 5 daytime agocities with skippets tzmpies utc.sql-Dumping data for table `tdatetime`-WHERE: create_time

< date_sub(curdate(), interval 5 day) LOCK TABLES `t_datetime` WRITE;/*!40000 ALTER TABLE `t_datetime` DISABLE KEYS */; INSERT INTO `t_datetime` VALUES (1,'messi','2019-12-07 13:27:55'),(3,'xavi','2019-12-07 13:28:01'),(5,'xsh','2019-12-07 13:28:08');/*!40000 ALTER TABLE `t_datetime` ENABLE KEYS */; UNLOCK TABLES; 4. 查阅官方文档 虽然加上--skip-tz-utc，我们的备份需求是达到了。但是这种结果仍然得不到一种很好的解释。因为按照我们的理解，datetime数据类型是和时域无关的，然而在我们的实践中，时域却影响了数据备份。带着这个疑问，我们在mysql的官方文档找到了相关的答案。 第一段的前面两句找到了我们想要的答案：会话时域的设置会影响具有时域敏感性的时间值的显示。包括NOW()、CURDATE()函数，和用timestamp数据类型存储的字段。 看到这里，突然有点豁然开朗，我们之前的理解没有错，datetime数据类型的确是不受时域影响，然而使用create_time < date_sub(curdate(), interval 5 day)条件进行备份时，影响备份结果的，并非是datetime数据类型本身，而是条件表达式中curdate()函数。 由于使用mysqldump进行备份时，会设置当前会话的时域为+0:00，即使用格林威治时间。那么会话中的curdate()函数，会按照当前服务器时间减8个小时来进行计算。当前时间为2019-12-13的凌晨2点，那么减8个小时之后，通过格林威治时间计算的curdate()即为2019-12-12，然而datetime中的数值不变，那么根据2019-12-12计算出的5天以前便没有数据。 按照上面的结论，我们可以进行一个猜想，由于影响mysqldump备份结果集的是curdate()函数，那么我们将条件表达式中的curdate()函数替换成真实的时间字符串，这样就不会受时域的影响，而能够正常备份出数据来。按照这个猜想，我们又进行了如下的测试。 5. 不带skip-tz-utc，且用当前的真实时间代替备份条件中curdate()函数 [root@rhel74 datetime]# mysqldump --single-transaction -uroot -p123456 -S /home/mysql/data/mysqldata1/sock/mysql.sock --set-gtid-purged=OFF xshtest t_datetime --where="create_time < date_sub('2019-12-13', interval 5 day)" >

5_day_ago_without_curdate.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@rhel74 datetime] # vim 5 days daytime agnostic withoutdated curdate.sql LOCK TABLES-Dumping data for table `t _ datetime`-WHERE: create_time < date_sub ('2019-12-13, interval 5 day) LOCK TABLES `tdatetime`WRITE` DISABLE KEYS * / INSERT INTO `tdatetime` VALUES (1 ALTER TABLE `tdatetime` ENABLE KEYS * /; UNLOCK TABLES leading hand).

As expected, mysqldump backed up the data.

With regard to this problem, if we do not encounter it in practice, it is very difficult for us to pay attention to such subtle knowledge points simply by our own study. We can see that practice is the best teacher. What problems we usually encounter, we should always maintain a heart to break the casserole and ask to the end, so that we can grow up for ourselves. In addition, we should also maintain a divergent thinking, encounter problems, associate more, ask a few more why, and then find the answer yourself. Take the initiative to find a problem to solve the problem, instead of waiting for the problem to come to the door.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.