mysqldump parameters you may not know

mysqldump parameters you may not know

In the previous article, it was mentioned that the timestamp data recorded in the mysqldump backup file is based on the UTC time zone. When selecting and restoring a single database or table, pay attention to the time zone difference. Later, I checked the document again and found that the tz-utc and skip-tz-utc parameters are related to this. In this article, let's take a look at the role of these parameters.

1. Introduction to tz-utc and skip-tz-utc parameters

These two parameters can be used in the mysqldump backup process and are opposite to each other. As the names suggest, one parameter is to change the timestamp to UTC time zone, and the other is to skip the time zone change.

Execute the mysqldump --help command on the mysql server and you can see the following paragraph.

[root@host ~]# mysqldump --help
mysqldump Ver 10.13 Distrib 5.7.23, for Linux (x86_64)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
...omit a lot of content --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.)

The --tz-utc parameter is the default parameter of mysqldump, which will add a statement SET TIME_ZONE='+00:00' to set the time zone at the top of the mysqldump export file. This time zone is Greenwich Mean Time, which is the 0 time zone. In this way, when the timestamp field is exported, the timestamp time value displayed in the current time zone set by the server will be converted to the time displayed in Greenwich Mean Time. For example, our database uses Beijing time zone 8, and the timestamp value displayed in the file exported by mysqldump is 8 hours backwards compared to the time displayed by the database query.

Knowing --tz-utc, the meaning of --skip-tz-utc is that when mysqldump exports data, it does not use Greenwich Mean Time, but uses the time zone of the current MySQL server for export. In this way, the timestamp time value displayed in the exported data is the same as the time value queried in the table.

2. Specific effects of experimental parameters

To better understand the effect of this parameter, let's do a specific test. We know that mysqldump can be used with a where condition to back up some data. If we back up some data based on the timestamp field, does this have any effect on the parameters? Let's verify it together:

Let's first look at my environment settings and test data:

mysql> select version();
+------------+
| version() |
+------------+
| 5.7.23-log |
+------------+
1 row in set (0.00 sec)
# The time zone is Beijing time zone 8 mysql> show variables like 'time_zone'; 
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| time_zone | +08:00 |
+---------------+--------+
1 row in set (0.00 sec)

# The test table has 10 data in the datetime field and the timestamp field. The two times are displayed the same. 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 'Auto-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 | 2020-07-10 09:43:28 | 2020-07-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 |
+--------------+--------+----------+---------------------+---------------------+

By default, mysqldump turns on tz-utc. Let's first look at the backup results by default:

# To make the results more obvious, we use skip-extended-insert to display the data line by line # Full database 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 first and then change the time zone of this session to 0 time zone... omitted--
-- 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','2020-07-10 09:43:28','2020-07-10 01:43:28');
INSERT INTO `test_tb` VALUES (2,1002,'fgsw','2020-10-10 09:43:28','2020-10-10 01:43:28');
INSERT INTO `test_tb` VALUES (3,1003,'vffg','2020-10-10 02:00:00','2020-10-09 18:00:00');
INSERT INTO `test_tb` VALUES (4,1004,'wdsd','2020-10-31 23:43:28','2020-10-31 15:43:28');
INSERT INTO `test_tb` VALUES (5,1005,'grdb','2020-11-01 00:00:00','2020-10-31 16:00:00');
INSERT INTO `test_tb` VALUES (6,1006,'sdfv','2020-11-01 02:00:00','2020-10-31 18:00:00');
INSERT INTO `test_tb` VALUES (7,1007,'fgfg','2020-11-06 02:00:00','2020-11-05 18:00:00');
INSERT INTO `test_tb` VALUES (8,1008,'tyth','2020-11-10 09:43:28','2020-11-10 01:43:28');
INSERT INTO `test_tb` VALUES (9,1009,'ewer','2020-11-10 09:43:28','2020-11-10 01:43:28');
INSERT INTO `test_tb` VALUES (10,1010,'erre','2020-11-11 15:17:03','2020-11-11 07:17:03');
# It can be seen that the timestamp time value is subtracted by 8 hours while the datetime time value remains unchanged UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
# Change the time zone to the original time zone /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
-- Dump completed on 2020-11-11 15:34:21

# Use the where condition to back up partial data in a single table and back up data since November# Query in the databasemysql> 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.00 sec)
# mysqldump export [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' */;
...omitted--
-- 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','2020-11-06 02:00:00','2020-11-05 18:00:00');
INSERT INTO `test_tb` VALUES (8,1008,'tyth','2020-11-10 09:43:28','2020-11-10 01:43:28');
INSERT INTO `test_tb` VALUES (9,1009,'ewer','2020-11-10 09:43:28','2020-11-10 01:43:28');
INSERT INTO `test_tb` VALUES (10,1010,'erre','2020-11-11 15:17:03','2020-11-11 07:17:03');
# Only 4 UNLOCK TABLES were found to be exported;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

-- Dump completed on 2020-11-11 15:58:56

I suggest you take a close look at the exported results above. To be honest, I haven’t done any detailed testing before, and I’m a little surprised to see the results now. By default, the data is fine. Although the timestamp value is converted to the 0 time zone, it will still be displayed in your database time zone when you import the database. However, when using the where condition to export part of the data, the results obtained from the database query were different from the results exported by dump. At this time, mysqldump only exported the data whose time values ​​after conversion to the 0 time zone met the where condition, which was different from the results obtained by direct query. This was something I had not noticed before.

Let's take a look at the --skip-tz-utc parameter to see if it meets our expectations:

# Use skip-tz-utc full backup [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
..Omitted unseen time zone change statements--
-- 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','2020-07-10 09:43:28','2020-07-10 09:43:28');
INSERT INTO `test_tb` VALUES (2,1002,'fgsw','2020-10-10 09:43:28','2020-10-10 09:43:28');
INSERT INTO `test_tb` VALUES (3,1003,'vffg','2020-10-10 02:00:00','2020-10-10 02:00:00');
INSERT INTO `test_tb` VALUES (4,1004,'wdsd','2020-10-31 23:43:28','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','2020-11-01 02:00:00','2020-11-01 02:00:00');
INSERT INTO `test_tb` VALUES (7,1007,'fgfg','2020-11-06 02:00:00','2020-11-06 02:00:00');
INSERT INTO `test_tb` VALUES (8,1008,'tyth','2020-11-10 09:43:28','2020-11-10 09:43:28');
INSERT INTO `test_tb` VALUES (9,1009,'ewer','2020-11-10 09:43:28','2020-11-10 09:43:28');
INSERT INTO `test_tb` VALUES (10,1010,'erre','2020-11-11 15:17:03','2020-11-11 15:17:03');
# The timestamp value is displayed the same as the datetime value without conversion. UNLOCK TABLES;
-- Dump completed on 2020-11-11 16:23:32

# Use skip-tz-utc to back up some 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
.. omitted--
-- 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','2020-11-01 02:00:00','2020-11-01 02:00:00');
INSERT INTO `test_tb` VALUES (7,1007,'fgfg','2020-11-06 02:00:00','2020-11-06 02:00:00');
INSERT INTO `test_tb` VALUES (8,1008,'tyth','2020-11-10 09:43:28','2020-11-10 09:43:28');
INSERT INTO `test_tb` VALUES (9,1009,'ewer','2020-11-10 09:43:28','2020-11-10 09:43:28');
INSERT INTO `test_tb` VALUES (10,1010,'erre','2020-11-11 15:17:03','2020-11-11 15:17:03');
# The 6 data are consistent with the query in the database UNLOCK TABLES;
-- Dump completed on 2020-11-11 16:28:39

From the above results, we can see that after using the --skip-tz-utc parameter, the timestamp field value will not be converted, and the exported data is also in line with expectations.

3. Some small suggestions

So what is the significance of this parameter? When your database server is in a different time zone. Suppose one server is in Beijing (Eastern District 8) and the other server is in Tokyo (Eastern District 9). Now you need to import the data in the Beijing server to the Tokyo server. When importing the dump file without the default --skip-tz-utc parameter, the queried timestamp time data is one hour longer than the previous time value on the East Zone 8 server. However, since 13:00 on the East Zone 8 server and 14:00 on the East Zone 9 server represent the same time, the extra hour displayed on the East Zone 9 server is correct. If you add the --skip-tz-utc parameter, after the dump file is imported into the East 9 server, although the time value displayed is the same as the time value previously displayed by the East 8 server, the times they represent are different.

Regarding how to use this parameter, we should first understand that whether to add the --skip-tz-utc parameter will only affect the import and export of the timestamp field, and will not affect the datetime field.

Here the author recommends standardizing the use of the timestamp field first. For example, the timestamp field is only used for creation time and update time requirements, and only represents the creation and update time of the row of data, making it weakly related to the business. For other time fields, try to use datetime. In this way, even if mysqldump uses different parameters, the actual impact is not significant.

If your server is in a different time zone, it is recommended to follow the default so that the imported and exported data are correct. If your servers are all in the same time zone, then using the --skip-tz-utc parameter makes little difference. We just need to know that by default mysqldump will convert timestamp values ​​to the 0 time zone for storage. When backing up partial data and filtering by the timestamp field, it is recommended to add the --skip-tz-utc parameter. Here is another reminder: when selecting backups of a single database or a single table from the full backup, you should also pay attention to the timestamp field data.

The above are the details of mysqldump parameters that you may not know. For more information about mysqldump parameters, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:
  • Implementation of MySQL5.7 mysqldump backup and recovery
  • Detailed explanation of the idea of ​​using mysqldump+expect+crontab to implement mysql periodic cold backup in linux
  • Summary of MySql import and export methods using mysqldump
  • Detailed explanation of the use of MySQL mysqldump
  • How to use mysqldump for full and point-in-time backups
  • Docker uses the mysqldump command to back up and export mysql data in the project
  • MySQL data migration using MySQLdump command
  • PHP scheduled backup MySQL and mysqldump syntax parameters detailed
  • Detailed explanation of how to use the mysql backup script mysqldump
  • Detailed explanation of Linux mysqldump exporting database, data, and table structure
  • Detailed discussion on the issue of mysqldump data export
  • Use of MySQL official export tool mysqlpump

<<:  How to use Portainer to build a visual interface for Docker

>>:  SSM VUE Axios Detailed Explanation

Recommend

React Principles Explained

Table of contents 1. setState() Description 1.1 U...

CentOS 7 set grub password and single user login example code

There are significant differences between centos7...

How to use the Fuser command in Linux system

What is Fuser Command? The fuser command is a ver...

Mobile browser Viewport parameters (web front-end design)

Mobile browsers place web pages in a virtual "...

A brief discussion on the fun of :focus-within in CSS

I believe some people have seen this picture of c...

Web page color matching example analysis: Green color matching web page analysis

<br />Green is between yellow and blue (cold...

Dynamically edit data in Layui table row

Table of contents Preface Style Function Descript...

WeChat applet records user movement trajectory

Table of contents Add Configuration json configur...

Windows uses VMware to create a Linux virtual machine and install the CentOS7.2 operating system

Table of contents 1. Follow the wizard to create ...

Manjaro installation CUDA implementation tutorial analysis

At the end of last year, I replaced the opensuse ...

Detailed explanation of component communication in react

Table of contents Parent component communicates w...

Solution to the failure of entering the container due to full docker space

Since the problem occurred rather suddenly and th...

Elementui exports data to xlsx and excel tables

Recently, I learned about the Vue project and cam...

Summary of CSS sibling element floating analysis

float:left/right/none; 1. Same level floating (1)...

Tutorial on customizing rpm packages and building yum repositories for Centos

1 Keep the rpm package downloaded when yum instal...