Preface:
As mentioned in the previous article, the timestamp data recorded in the mysqldump backup file is based on the UTC time zone, so be aware of time zone differences when filtering for a single database or table. The tz-UTC and Skip-TZ-UTC parameters are related to this parameter. In this article, we will take a look at the function of these parameters.
1. This section describes tz-UTC and Skip-TZ-UTC parameters
These two parameters can be used in the mysqldump backup process, as opposed to each other. As the name implies, one parameter is to change the timestamp to UTC time zone, and the other parameter is to skip time zone changes.
Dump –help = mysqldump –help = mysqldump –help
[root@host ~]# mysqldump --help
mysqldump Ver 10.13 Distrib 5.723..for Linux (x86_64)
Copyright (c) 2000.2018, Oracle and/or its affiliates. Allrights reserved. ... Leave out a lot of stuff--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.)
Copy the codeSET TIME_ZONE=’+00:00′ — the tz-UTC parameter is the default mysqldump parameter, and causes the top of the exported mysqldump file to be SET with a TIME_ZONE statement. In this way, when exporting the TIMESTAMP timestamp field, the timestamp value displayed in the current time zone set by the server is converted to the time displayed in Greenwich Mean Time. For example, our database uses Beijing time east 8 zone, the timestamp value displayed in the exported file mysqldump is 8 hours backward compared to the time displayed through the database query.
— skip-tz-UTC — skip-tz-UTC — skip-tz-UTC — skip-tz-UTC — skip-tz-UTC — skip-tz-UTC — skip-tz-UTC In this way, the timestamp value displayed in the exported data is the same as the time value queried in the table.
2. Specific effects of experimental parameters
Mysql > alter table select * from ‘where’ where (timestamp); mysql > alter table select * from ‘where’; mysql > alter table select * from ‘where’; Let’s verify:
Let’s take a look at my environment Settings and test data:
mysql> select version();
+------------+
| version() |
+------------+
| 5.723.-log |
+------------+
1 row in set (0.00SEC) # Time zone use Beijing time east zone 8 mysql> show variables like 'time_zone';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| time_zone | +08:00 |
+---------------+--------+
1 row in set (0.00SEC) # Test table has datetime field andtimestampField,10Two pieces of data are displayed at the same time as mysql> show create table test_tb\G
*************************** 1. row ***************************
Table: test_tb
Create Table: CREATE TABLE `test_tb` (
`increment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Increment primary key',
`stu_id` int(11) NOT NULL COMMENT 'student id',
`stu_name` varchar(20) DEFAULT NULL COMMENT 'Student name',
`dt_time` datetime NOT NULL,
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time'.PRIMARY KEY (`increment_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COMMENT='Test table'
1 row in set (0.00 sec)
mysql> select * from test_tb;
+--------------+--------+----------+---------------------+---------------------+
| increment_id | stu_id | stu_name | dt_time | create_time |
+--------------+--------+----------+---------------------+---------------------+
| 1 | 1001 | fgds | 202007 -- 10 09:43:28 | 202007 -- 10 09:43:28 |
| 2 | 1002 | fgsw | 2020- 10- 10 09:43:28 | 2020- 10- 10 09:43:28 |
| 3 | 1003 | vffg | 2020- 10- 10 02:00:00 | 2020- 10- 10 02:00:00 |
| 4 | 1004 | wdsd | 2020- 10- 31 23:43:28 | 2020- 10- 31 23:43:28 |
| 5 | 1005 | grdb | 2020- 11- 01 00:00:00 | 2020- 11- 01 00:00:00 |
| 6 | 1006 | sdfv | 2020- 11- 01 02:00:00 | 2020- 11- 01 02:00:00 |
| 7 | 1007 | fgfg | 2020- 11- 06 02:00:00 | 2020- 11- 06 02:00:00 |
| 8 | 1008 | tyth | 2020- 11- 10 09:43:28 | 2020- 11- 10 09:43:28 |
| 9 | 1009 | ewer | 2020- 11- 10 09:43:28 | 2020- 11- 10 09:43:28 |
| 10 | 1010 | erre | 2020- 11- 11 15:17:03 | 2020- 11- 11 15:17:03 |
+--------------+--------+----------+---------------------+---------------------+
Copy the codeMysqldump enables tz-UTC by default.
# for more obvious results we useskip-extended-insertTo display data line by line # full backup [root]@host ~]# mysqldump -uroot -pxxxx --skip-extended-insert --databases testdb > utc_testdb.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@host ~]# more utc_testdb.sql
-- MySQL dump 10.13 Distrib 5.7.23, for Linux (x86_64)
--
-- Host: localhost Database: testdb
-- ------------------------------------------------------
- Server version 5.7.23 - log. omit/ *! 40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/ *! 40103 SET TIME_ZONE='+00:00' */; Save the old time zone and change the time zone for this session0Time zone... omit--
-- Dumping data for table `test_tb`
--
LOCK TABLES `test_tb` WRITE;
/ *! 40000 ALTER TABLE `test_tb` DISABLE KEYS */;
INSERT INTO `test_tb` VALUES (1.1001.'fgds'.'the 2020-07-10 09:43:28'.'the 2020-07-10 01:43:28');
INSERT INTO `test_tb` VALUES (2.1002.'fgsw'.'the 2020-10-10 09:43:28'.'the 2020-10-10 01:43:28');
INSERT INTO `test_tb` VALUES (3.1003.'vffg'.'the 2020-10-10 02:00:00'.'the 2020-10-09 18:00:00');
INSERT INTO `test_tb` VALUES (4.1004.'wdsd'.'the 2020-10-31 23:43:28'.'the 2020-10-31 15:43:28');
INSERT INTO `test_tb` VALUES (5.1005.'grdb'.'2020-11-01 00:00:00'.'the 2020-10-31 16:00:00');
INSERT INTO `test_tb` VALUES (6.1006.'sdfv'.'the 2020-11-01 02:00:00'.'the 2020-10-31 18:00:00');
INSERT INTO `test_tb` VALUES (7.1007.'fgfg'.'the 2020-11-06 02:00:00'.'the 2020-11-05 18:00:00');
INSERT INTO `test_tb` VALUES (8.1008.'tyth'.'the 2020-11-10 09:43:28'.'the 2020-11-10 01:43:28');
INSERT INTO `test_tb` VALUES (9.1009.'ewer'.'the 2020-11-10 09:43:28'.'the 2020-11-10 01:43:28');
INSERT INTO `test_tb` VALUES (10.1010.'erre'.'the 2020-11-11 15:17:03'.'the 2020-11-11 07:17:03'); # You can see thattimestampThe time value is subtracted8UNLOCK TABLES; datetime;/ *! 40103 SET TIME_ZONE=@OLD_TIME_ZONE */; Change time zone to original time zone/ *! 40101 SET SQL_MODE=@OLD_SQL_MODE */;
-- Dump completed on 2020-11-11 15:34:21# usewhereConditional backup Partial data backup of a single table11Mysql > select * from 'mysql'> select * from test_tb where create_time > = '2020-11-01 00:00:00';
+--------------+--------+----------+---------------------+---------------------+
| increment_id | stu_id | stu_name | dt_time | create_time |
+--------------+--------+----------+---------------------+---------------------+
| 5 | 1005 | grdb | 2020- 11- 01 00:00:00 | 2020- 11- 01 00:00:00 |
| 6 | 1006 | sdfv | 2020- 11- 01 02:00:00 | 2020- 11- 01 02:00:00 |
| 7 | 1007 | fgfg | 2020- 11- 06 02:00:00 | 2020- 11- 06 02:00:00 |
| 8 | 1008 | tyth | 2020- 11- 10 09:43:28 | 2020- 11- 10 09:43:28 |
| 9 | 1009 | ewer | 2020- 11- 10 09:43:28 | 2020- 11- 10 09:43:28 |
| 10 | 1010 | erre | 2020- 11- 11 15:17:03 | 2020- 11- 11 15:17:03 |
+--------------+--------+----------+---------------------+---------------------+
6 rows in set (0.00SEC) # mysqldump dump [root@host ~]# mysqldump -uroot -pxxxx --skip-extended-insert testdb test_tb --where "create_time >= '2020-11-01 00:00:00' " > utc_testdb2.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@host ~]# more utc_testdb2.sql
-- MySQL dump 10.13 Distrib 5.7.23, for Linux (x86_64)
--
-- Host: localhost Database: testdb
-- ------------------------------------------------------
- Server version 5.7.23 - log./ *! 40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/ *! 40103 SET TIME_ZONE='+00:00' */; . omit--
-- Dumping data for table `test_tb`
--
-- WHERE: create_time >= '2020-11-01 00:00:00'
LOCK TABLES `test_tb` WRITE;
/ *! 40000 ALTER TABLE `test_tb` DISABLE KEYS */;
INSERT INTO `test_tb` VALUES (7.1007.'fgfg'.'the 2020-11-06 02:00:00'.'the 2020-11-05 18:00:00');
INSERT INTO `test_tb` VALUES (8.1008.'tyth'.'the 2020-11-10 09:43:28'.'the 2020-11-10 01:43:28');
INSERT INTO `test_tb` VALUES (9.1009.'ewer'.'the 2020-11-10 09:43:28'.'the 2020-11-10 01:43:28');
INSERT INTO `test_tb` VALUES (10.1010.'erre'.'the 2020-11-11 15:17:03'.'the 2020-11-11 07:17:03'); # find only export4The UNLOCK TABLES;/ *! 40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
-- Dump completed on 2020-11-11 15:58:56
Copy the codeI suggest you take a closer look at the above results. To be honest, I didn’t do a detailed test before, and now I’m a little surprised to see the results. By default, all data is fine. Although the timestamp value is changed to 0, the timestamp time will be displayed in your database’s time zone when you import the database. Mysqldump (); mysqldump (); mysqldump (); mysqldump ();
Let’s use the — skip-tz-UTC parameter to see if it meets our expectations:
# useskip-tz-Utc perfect [root@host ~]# mysqldump -uroot -pxxxx --skip-extended-insert --skip-tz-utc --databases testdb > skiputc_testdb.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@host ~]# more skiputc_testdb.sql
-- MySQL dump 10.13 Distrib 5.7.23, for Linux (x86_64)
--
-- Host: localhost Database: testdb
-- ------------------------------------------------------
- Server version 5.7.23 - log. Omit statements that do not see time zone changes--
-- Dumping data for table `test_tb`
--
LOCK TABLES `test_tb` WRITE;
/ *! 40000 ALTER TABLE `test_tb` DISABLE KEYS */;
INSERT INTO `test_tb` VALUES (1.1001.'fgds'.'the 2020-07-10 09:43:28'.'the 2020-07-10 09:43:28');
INSERT INTO `test_tb` VALUES (2.1002.'fgsw'.'the 2020-10-10 09:43:28'.'the 2020-10-10 09:43:28');
INSERT INTO `test_tb` VALUES (3.1003.'vffg'.'the 2020-10-10 02:00:00'.'the 2020-10-10 02:00:00');
INSERT INTO `test_tb` VALUES (4.1004.'wdsd'.'the 2020-10-31 23:43:28'.'the 2020-10-31 23:43:28');
INSERT INTO `test_tb` VALUES (5.1005.'grdb'.'2020-11-01 00:00:00'.'2020-11-01 00:00:00');
INSERT INTO `test_tb` VALUES (6.1006.'sdfv'.'the 2020-11-01 02:00:00'.'the 2020-11-01 02:00:00');
INSERT INTO `test_tb` VALUES (7.1007.'fgfg'.'the 2020-11-06 02:00:00'.'the 2020-11-06 02:00:00');
INSERT INTO `test_tb` VALUES (8.1008.'tyth'.'the 2020-11-10 09:43:28'.'the 2020-11-10 09:43:28');
INSERT INTO `test_tb` VALUES (9.1009.'ewer'.'the 2020-11-10 09:43:28'.'the 2020-11-10 09:43:28');
INSERT INTO `test_tb` VALUES (10.1010.'erre'.'the 2020-11-11 15:17:03'.'the 2020-11-11 15:17:03');
# timestampTime value displayed as datetime not converted UNLOCK TABLES;-- Dump completed on 2020-11-11 16:23:32# useskip-tz-Utc Backup partial data [root@host ~]# mysqldump -uroot -pxxxx --skip-extended-insert --skip-tz-utc testdb test_tb --where "create_time >= '2020-11-01 00:00:00' " > skiputc_testdb2.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@host ~]# more skiputc_testdb2.sql
-- MySQL dump 10.13 Distrib 5.7.23, for Linux (x86_64)
--
-- Host: localhost Database: testdb
-- ------------------------------------------------------
- Server version 5.7.23 - log. omit--
-- Dumping data for table `test_tb`
--
-- WHERE: create_time >= '2020-11-01 00:00:00'
LOCK TABLES `test_tb` WRITE;
/ *! 40000 ALTER TABLE `test_tb` DISABLE KEYS */;
INSERT INTO `test_tb` VALUES (5.1005.'grdb'.'2020-11-01 00:00:00'.'2020-11-01 00:00:00');
INSERT INTO `test_tb` VALUES (6.1006.'sdfv'.'the 2020-11-01 02:00:00'.'the 2020-11-01 02:00:00');
INSERT INTO `test_tb` VALUES (7.1007.'fgfg'.'the 2020-11-06 02:00:00'.'the 2020-11-06 02:00:00');
INSERT INTO `test_tb` VALUES (8.1008.'tyth'.'the 2020-11-10 09:43:28'.'the 2020-11-10 09:43:28');
INSERT INTO `test_tb` VALUES (9.1009.'ewer'.'the 2020-11-10 09:43:28'.'the 2020-11-10 09:43:28');
INSERT INTO `test_tb` VALUES (10.1010.'erre'.'the 2020-11-11 15:17:03'.'the 2020-11-11 15:17:03');
# 6UNLOCK TABLES;-- Dump completed on 2020-11-11 16:28:39
Copy the codeAs can be seen from the above results, the value of timestamp timestamp field is not converted after using — skip-tz-UTC parameter, and part of the exported data also conforms to the expectation.
3. Some tips
So what does this parameter mean? When your database server is in a different zone. Suppose one server is in Beijing (East 8 district) and one server is in Tokyo (East 9 District). Now you need to import data from the Beijing server to the Tokyo server. When the dump file is imported according to the default without — skip-tz-UTC parameter, the queried timestamp data is one hour longer than the time value in the previous EAST 8 server. However, since 13 o ‘clock in the east 8 server and 14 o ‘clock in the East 9 server represent the same time, so, The extra hour on the east 9th ward server, which is correct. If the — skip-tz-UTC parameter is added, the dump file imported to the EAST 9 server will display the same time value as the previous East 8 server, but the time they represent is different.
As to how to use this parameter, we should first understand that whether to add — skip-tz-UTC parameter only affects the import and export of timestamp field, not datetime field.
Here, the author suggests standardizing the use of timestamp field. For example, the timestamp field is only used for the creation time and update time requirements, which only represents the creation and update time of the data in the row and is weakly correlated with business. Other time fields should try to use Datetime. So even if mysqldump takes a different parameter, it doesn’t really matter.
If your server is in a different time zone, it is recommended to stick to the default, so that the imported and exported data is correct. If your servers are all in the same time zone, there is little difference between using the — skip-tz-UTC parameter, just know that mysqldump converts timestamp values to 0 by default. When part of the data is backed up and filtered by timestamp fields, it is recommended to add — skip-tz-UTC. Once again, when filtering single database or single table backups from full backup, also pay attention to the timestamp field data.
Reference:
- zhuanlan.zhihu.com/p/99395517
