您的位置：首页 > 数据库 > > 正文

mysqldump 命令详解（mysqldump你可能不知道的参数）

更多 时间：2022-01-26 01:09:20 类别：数据库 浏览量：845

mysqldump 命令详解

mysqldump你可能不知道的参数

在前面文章中，有提到过 mysqldump 备份文件中记录的时间戳数据都是以 UTC 时区为基础的，在筛选恢复单库或单表时要注意时区差别。后来再次查看文档，发现 tz-utc、skip-tz-utc 参数与此有关，本篇文章我们一起来看下此参数的作用吧。

1.tz-utc与skip-tz-utc参数介绍

这两个参数可以作用于 mysqldump 备份过程中，互为相反参数。顾名思义可以看出，一个参数是将时间戳改为 UTC 时区，另一个是跳过时区变动。

在 mysql 服务器上执行 mysqldump --help 的命令，可以看到下面一段话。

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
    		•
  • [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.
  • ...省略很多内容
  •  --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' ，这个时区是格林威治时间，也就是0时区。这样当导出 timestamp 时间戳字段时，会把在服务器设置的当前时区下显示的 timestamp 时间值转化为在格林威治时间下显示的时间。比如我们数据库采用北京时间东八区，mysqldump 导出的文件当中显示的 timestamp 时间值相对于通过数据库查询显示的时间倒退了8个小时。

    知道了 --tz-utc ，那么 --skip-tz-utc 的含义就是当 mysqldump 导出数据时，不使用格林威治时间，而使用当前 mysql 服务器的时区进行导出，这样导出的数据中显示的 timestamp 时间值也和表中查询出来的时间值相同。

    2.实验参数具体作用

    为了更清楚了解这对参数的作用，下面我们来具体测试下，我们知道 mysqldump 后可以跟 where 条件来备份部分数据，若根据 timestamp 字段来备份部分数据，这对参数是否有影响呢？我们一并来验证下：

    先来看下我的环境设置及测试数据：

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
    		•
  • mysql> select version();
  • +------------+
  • | version() |
  • +------------+
  • | 5.7.23-log |
  • +------------+
  • 1 row in set (0.00 sec)
  • # 时区采用北京时间东八区
  • mysql> show variables like 'time_zone';
  • +---------------+--------+
  • | Variable_name | Value |
  • +---------------+--------+
  • | time_zone   | +08:00 |
  • +---------------+--------+
  • 1 row in set (0.00 sec)
  •  
  • # 测试表 有datetime字段和timestamp字段 共10条数据 两个时间显示是相同的
  • 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 '自增主键',
  •  `stu_id` int(11) NOT NULL COMMENT '学号',
  •  `stu_name` varchar(20) DEFAULT NULL COMMENT '学生姓名',
  •  `dt_time` datetime NOT NULL,
  •  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  •  PRIMARY KEY (`increment_id`)
  • ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COMMENT='测试表'
  • 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 |
  • +--------------+--------+----------+---------------------+---------------------+
    		•

    • mysqldump 默认开启 tz-utc ，先来看下默认情况下的备份结果：

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
    		•
  • # 为更明显看出结果 我们使用skip-extended-insert来一行行展现数据
  • # 全库备份
  • [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
  •  
  • ...省略
  • /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
  • /*!40103 SET TIME_ZONE='+00:00' */;
  • # 先保存老时区 然后将此会话时区改为0时区
  • ...省略
  • --
  • -- 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');
  • # 可以看出timestamp时间值减去了8小时 而datetime时间值不变
  • UNLOCK TABLES;
  • /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
  • # 再将时区改为原时区
  • /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
  • -- Dump completed on 2020-11-11 15:34:21
  •  
  • # 使用where条件备份单表部分数据 备份11月份以来的数据
  • # 数据库中查询
  • 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.00 sec)
  • # mysqldump导出
  • [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' */;
  • ...省略
  • --
  • -- 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');
  • # 发现只导出4条
  • UNLOCK TABLES;
  • /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
  •  
  • -- Dump completed on 2020-11-11 15:58:56
    		•

    • 建议各位仔细看下上面导出结果，说实话，笔者原来也没做过详细测试，现在看到结果也是稍微有点吃惊的。默认情况下，全备出来的数据是没问题的，虽然将 timestamp 时间值转为0时区显示，但当你导入数据库时还会以你的数据库时区来展示 timestamp 时间。但使用 where 条件导出部分数据时，却出现了数据库中查询得出的结果与dump导出的结果不同的情况，这个时候 mysqldump 只导出了转化成0时区后的时间值符合 where 条件的数据，与直接查询出的结果有出入，这是我原来没注意到的。

    再来看下使用 --skip-tz-utc 参数，看下这个参数是否符合我们的预期：

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
    		•
  • # 使用skip-tz-utc全备
  • [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
  • ..省略 未见时区更改语句
  • --
  • -- 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');
  • # timestamp时间值显示与datetime显示一样 未做转换
  • UNLOCK TABLES;
  • -- Dump completed on 2020-11-11 16:23:32
  •  
  • # 使用skip-tz-utc备份部分数据
  • [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
  • .. 省略
  • --
  • -- 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');
  • # 6条数据 和数据库中查询一致
  • UNLOCK TABLES;
  • -- Dump completed on 2020-11-11 16:28:39
    		•

    • 从上面结果可以看出，使用 --skip-tz-utc 参数后，timestamp 时间戳字段值不会转换，导出部分数据也符合预期。

    3.一些小建议

    那么这个参数的意义何在呢？当你的数据库服务器处于不同时区时。假设一个服务器在北京（东八区），一个服务器在东京（东九区），现在需要将北京服务器里的数据导入至东京服务器。当导入按照默认不加 --skip-tz-utc 参数的dump文件，查询的 timestamp 时间数据相对于在之前的东八区服务器的时间值多了一个小时，但由于东八区服务器里的13点和东九区服务器里的14点代表的是同一时刻，所以，在东九区的服务器里显示的多出的一个小时，这样显示是正确的。而如果增加 --skip-tz-utc 参数，dump文件导入东九区服务器后，尽管显示的时间值和之前东八区服务器显示的时间值相同，但两者代表的时刻却已经不同。

    关于这个参数应该如何使用，我们首先应该明白，是否加上 --skip-tz-utc 参数，只会影响 timestamp 字段的导入导出，对 datetime 时间字段不会影响。

    这里笔者建议首先对 timestamp 字段使用作出规范。比如 timestamp 字段只用于创建时间和更新时间需求，只代表该行数据的创建及更新时间，做到与业务弱相关，其他时间字段尽量使用 datetime 。这样即使 mysqldump 采用不同参数，实际产生影响也不大。

    如果你的服务器处于不同时区，那建议还是按照默认来，这样导入导出的数据都是正确的。如果你的服务器都是处于同一时区，那么是否使用 --skip-tz-utc 参数区别不大，我们只需知道默认情况 mysqldump 会将 timestamp 时间值转为0时区存储即可。当备份部分数据且以 timestamp 字段来筛选时，这时候建议增加 --skip-tz-utc 参数。这里再次提醒下，从全备中筛选单库或单表的备份时，也要注意下 timestamp 字段数据。

    以上就是mysqldump你可能不知道的参数的详细内容，更多关于mysqldump 参数的资料请关注开心学习网其它相关文章！

    原文链接：https://cloud.tencent.com/developer/article/1750352

    标签：MySQLdump 参数
  • 上一篇：java集成钉钉发送消息（Python实现钉钉发送报警消息的方法）
  • 下一篇：常用的几种jsp开发环境搭建（怎么一键安装Jsp网站程序？Jsp环境一键配置软件JspStudy推荐）
    • 您可能感兴趣
    首页 编程学习 Web前端 数据库 软件设计
    开心学习 ©2013-2021 保留所有权利