mysqldump你可能不知道的参数
在前面文章中，有提到过mysqldump备份文件中记录的时间戳数据都是以UTC时区为基础的，在筛选恢复单库或单表时要注意时区差别。后来再次查看文档，发现tz-utc、skip-tz-utc参数与此有关，本篇文章我们一起来看下此参数的作用吧。
1.tz-utc与skip-tz-utc参数介绍
这两个参数可以作用于mysqldump备份过程中，互为相反参数。顾名思义可以看出，一个参数是将时间戳改为UTC时区，另一个是跳过时区变动。
在mysql服务器上执行mysqldump--help的命令，可以看到下面一段话。
[root@host~]#mysqldump--help mysqldumpVer10.13Distrib5.7.23,forLinux(x86_64) Copyright(c)2000,2018,Oracleand/oritsaffiliates.Allrightsreserved. ...省略很多内容 --tz-utcSETTIME_ZONE='+00:00'attopofdumptoallowdumpingof TIMESTAMPdatawhenaserverhasdataindifferenttime zonesordataisbeingmovedbetweenserverswith differenttimezones. (Defaultstoon;use--skip-tz-utctodisable.)
--tz-utc参数是mysqldump的默认参数，会使得mysqldump的导出文件的顶部加上一个设置时区的语句SETTIME_ZONE='+00:00'，这个时区是格林威治时间，也就是0时区。这样当导出timestamp时间戳字段时，会把在服务器设置的当前时区下显示的timestamp时间值转化为在格林威治时间下显示的时间。比如我们数据库采用北京时间东八区，mysqldump导出的文件当中显示的timestamp时间值相对于通过数据库查询显示的时间倒退了8个小时。
知道了--tz-utc，那么--skip-tz-utc的含义就是当mysqldump导出数据时，不使用格林威治时间，而使用当前mysql服务器的时区进行导出，这样导出的数据中显示的timestamp时间值也和表中查询出来的时间值相同。
2.实验参数具体作用
为了更清楚了解这对参数的作用，下面我们来具体测试下，我们知道mysqldump后可以跟where条件来备份部分数据，若根据timestamp字段来备份部分数据，这对参数是否有影响呢？我们一并来验证下：
先来看下我的环境设置及测试数据：
mysql>selectversion(); +------------+ |version()| +------------+ |5.7.23-log| +------------+ 1rowinset(0.00sec) #时区采用北京时间东八区 mysql>showvariableslike'time_zone'; +---------------+--------+ |Variable_name|Value| +---------------+--------+ |time_zone|+08:00| +---------------+--------+ 1rowinset(0.00sec) #测试表有datetime字段和timestamp字段共10条数据两个时间显示是相同的 mysql>showcreatetabletest_tb\G ***************************1.row*************************** Table:test_tb CreateTable:CREATETABLE`test_tb`( `increment_id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'自增主键', `stu_id`int(11)NOTNULLCOMMENT'学号', `stu_name`varchar(20)DEFAULTNULLCOMMENT'学生姓名', `dt_time`datetimeNOTNULL, `create_time`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'创建时间', PRIMARYKEY(`increment_id`) )ENGINE=InnoDBAUTO_INCREMENT=11DEFAULTCHARSET=utf8COMMENT='测试表' 1rowinset(0.00sec) mysql>select*fromtest_tb; +--------------+--------+----------+---------------------+---------------------+ |increment_id|stu_id|stu_name|dt_time|create_time| +--------------+--------+----------+---------------------+---------------------+ |1|1001|fgds|2020-07-1009:43:28|2020-07-1009:43:28| |2|1002|fgsw|2020-10-1009:43:28|2020-10-1009:43:28| |3|1003|vffg|2020-10-1002:00:00|2020-10-1002:00:00| |4|1004|wdsd|2020-10-3123:43:28|2020-10-3123:43:28| |5|1005|grdb|2020-11-0100:00:00|2020-11-0100:00:00| |6|1006|sdfv|2020-11-0102:00:00|2020-11-0102:00:00| |7|1007|fgfg|2020-11-0602:00:00|2020-11-0602:00:00| |8|1008|tyth|2020-11-1009:43:28|2020-11-1009:43:28| |9|1009|ewer|2020-11-1009:43:28|2020-11-1009:43:28| |10|1010|erre|2020-11-1115:17:03|2020-11-1115:17:03| +--------------+--------+----------+---------------------+---------------------+
mysqldump默认开启tz-utc，先来看下默认情况下的备份结果：
#为更明显看出结果我们使用skip-extended-insert来一行行展现数据 #全库备份 [root@host~]#mysqldump-uroot-pxxxx--skip-extended-insert--databasestestdb>utc_testdb.sql mysqldump:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure. [root@host~]#moreutc_testdb.sql --MySQLdump10.13Distrib5.7.23,forLinux(x86_64) -- --Host:localhostDatabase:testdb -------------------------------------------------------- --Serverversion5.7.23-log ...省略 /*!40103SET@OLD_TIME_ZONE=@@TIME_ZONE*/; /*!40103SETTIME_ZONE='+00:00'*/; #先保存老时区然后将此会话时区改为0时区 ...省略 -- --Dumpingdatafortable`test_tb` -- LOCKTABLES`test_tb`WRITE; /*!40000ALTERTABLE`test_tb`DISABLEKEYS*/; INSERTINTO`test_tb`VALUES(1,1001,'fgds','2020-07-1009:43:28','2020-07-1001:43:28'); INSERTINTO`test_tb`VALUES(2,1002,'fgsw','2020-10-1009:43:28','2020-10-1001:43:28'); INSERTINTO`test_tb`VALUES(3,1003,'vffg','2020-10-1002:00:00','2020-10-0918:00:00'); INSERTINTO`test_tb`VALUES(4,1004,'wdsd','2020-10-3123:43:28','2020-10-3115:43:28'); INSERTINTO`test_tb`VALUES(5,1005,'grdb','2020-11-0100:00:00','2020-10-3116:00:00'); INSERTINTO`test_tb`VALUES(6,1006,'sdfv','2020-11-0102:00:00','2020-10-3118:00:00'); INSERTINTO`test_tb`VALUES(7,1007,'fgfg','2020-11-0602:00:00','2020-11-0518:00:00'); INSERTINTO`test_tb`VALUES(8,1008,'tyth','2020-11-1009:43:28','2020-11-1001:43:28'); INSERTINTO`test_tb`VALUES(9,1009,'ewer','2020-11-1009:43:28','2020-11-1001:43:28'); INSERTINTO`test_tb`VALUES(10,1010,'erre','2020-11-1115:17:03','2020-11-1107:17:03'); #可以看出timestamp时间值减去了8小时而datetime时间值不变 UNLOCKTABLES; /*!40103SETTIME_ZONE=@OLD_TIME_ZONE*/; #再将时区改为原时区 /*!40101SETSQL_MODE=@OLD_SQL_MODE*/; --Dumpcompletedon2020-11-1115:34:21 #使用where条件备份单表部分数据备份11月份以来的数据 #数据库中查询 mysql>select*fromtest_tbwherecreate_time>='2020-11-0100:00:00'; +--------------+--------+----------+---------------------+---------------------+ |increment_id|stu_id|stu_name|dt_time|create_time| +--------------+--------+----------+---------------------+---------------------+ |5|1005|grdb|2020-11-0100:00:00|2020-11-0100:00:00| |6|1006|sdfv|2020-11-0102:00:00|2020-11-0102:00:00| |7|1007|fgfg|2020-11-0602:00:00|2020-11-0602:00:00| |8|1008|tyth|2020-11-1009:43:28|2020-11-1009:43:28| |9|1009|ewer|2020-11-1009:43:28|2020-11-1009:43:28| |10|1010|erre|2020-11-1115:17:03|2020-11-1115:17:03| +--------------+--------+----------+---------------------+---------------------+ 6rowsinset(0.00sec) #mysqldump导出 [root@host~]#mysqldump-uroot-pxxxx--skip-extended-inserttestdbtest_tb--where"create_time>='2020-11-0100:00:00'">utc_testdb2.sql mysqldump:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure. [root@host~]#moreutc_testdb2.sql --MySQLdump10.13Distrib5.7.23,forLinux(x86_64) -- --Host:localhostDatabase:testdb -------------------------------------------------------- --Serverversion5.7.23-log ... /*!40103SET@OLD_TIME_ZONE=@@TIME_ZONE*/; /*!40103SETTIME_ZONE='+00:00'*/; ...省略 -- --Dumpingdatafortable`test_tb` -- --WHERE:create_time>='2020-11-0100:00:00' LOCKTABLES`test_tb`WRITE; /*!40000ALTERTABLE`test_tb`DISABLEKEYS*/; INSERTINTO`test_tb`VALUES(7,1007,'fgfg','2020-11-0602:00:00','2020-11-0518:00:00'); INSERTINTO`test_tb`VALUES(8,1008,'tyth','2020-11-1009:43:28','2020-11-1001:43:28'); INSERTINTO`test_tb`VALUES(9,1009,'ewer','2020-11-1009:43:28','2020-11-1001:43:28'); INSERTINTO`test_tb`VALUES(10,1010,'erre','2020-11-1115:17:03','2020-11-1107:17:03'); #发现只导出4条 UNLOCKTABLES; /*!40103SETTIME_ZONE=@OLD_TIME_ZONE*/; --Dumpcompletedon2020-11-1115:58:56
建议各位仔细看下上面导出结果，说实话，笔者原来也没做过详细测试，现在看到结果也是稍微有点吃惊的。默认情况下，全备出来的数据是没问题的，虽然将timestamp时间值转为0时区显示，但当你导入数据库时还会以你的数据库时区来展示timestamp时间。但使用where条件导出部分数据时，却出现了数据库中查询得出的结果与dump导出的结果不同的情况，这个时候mysqldump只导出了转化成0时区后的时间值符合where条件的数据，与直接查询出的结果有出入，这是我原来没注意到的。
再来看下使用--skip-tz-utc参数，看下这个参数是否符合我们的预期：
#使用skip-tz-utc全备 [root@host~]#mysqldump-uroot-pxxxx--skip-extended-insert--skip-tz-utc--databasestestdb>skiputc_testdb.sql mysqldump:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure. [root@host~]#moreskiputc_testdb.sql --MySQLdump10.13Distrib5.7.23,forLinux(x86_64) -- --Host:localhostDatabase:testdb -------------------------------------------------------- --Serverversion5.7.23-log ..省略未见时区更改语句 -- --Dumpingdatafortable`test_tb` -- LOCKTABLES`test_tb`WRITE; /*!40000ALTERTABLE`test_tb`DISABLEKEYS*/; INSERTINTO`test_tb`VALUES(1,1001,'fgds','2020-07-1009:43:28','2020-07-1009:43:28'); INSERTINTO`test_tb`VALUES(2,1002,'fgsw','2020-10-1009:43:28','2020-10-1009:43:28'); INSERTINTO`test_tb`VALUES(3,1003,'vffg','2020-10-1002:00:00','2020-10-1002:00:00'); INSERTINTO`test_tb`VALUES(4,1004,'wdsd','2020-10-3123:43:28','2020-10-3123:43:28'); INSERTINTO`test_tb`VALUES(5,1005,'grdb','2020-11-0100:00:00','2020-11-0100:00:00'); INSERTINTO`test_tb`VALUES(6,1006,'sdfv','2020-11-0102:00:00','2020-11-0102:00:00'); INSERTINTO`test_tb`VALUES(7,1007,'fgfg','2020-11-0602:00:00','2020-11-0602:00:00'); INSERTINTO`test_tb`VALUES(8,1008,'tyth','2020-11-1009:43:28','2020-11-1009:43:28'); INSERTINTO`test_tb`VALUES(9,1009,'ewer','2020-11-1009:43:28','2020-11-1009:43:28'); INSERTINTO`test_tb`VALUES(10,1010,'erre','2020-11-1115:17:03','2020-11-1115:17:03'); #timestamp时间值显示与datetime显示一样未做转换 UNLOCKTABLES; --Dumpcompletedon2020-11-1116:23:32 #使用skip-tz-utc备份部分数据 [root@host~]#mysqldump-uroot-pxxxx--skip-extended-insert--skip-tz-utctestdbtest_tb--where"create_time>='2020-11-0100:00:00'">skiputc_testdb2.sql mysqldump:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure. [root@host~]#moreskiputc_testdb2.sql --MySQLdump10.13Distrib5.7.23,forLinux(x86_64) -- --Host:localhostDatabase:testdb -------------------------------------------------------- --Serverversion5.7.23-log ..省略 -- --Dumpingdatafortable`test_tb` -- --WHERE:create_time>='2020-11-0100:00:00' LOCKTABLES`test_tb`WRITE; /*!40000ALTERTABLE`test_tb`DISABLEKEYS*/; INSERTINTO`test_tb`VALUES(5,1005,'grdb','2020-11-0100:00:00','2020-11-0100:00:00'); INSERTINTO`test_tb`VALUES(6,1006,'sdfv','2020-11-0102:00:00','2020-11-0102:00:00'); INSERTINTO`test_tb`VALUES(7,1007,'fgfg','2020-11-0602:00:00','2020-11-0602:00:00'); INSERTINTO`test_tb`VALUES(8,1008,'tyth','2020-11-1009:43:28','2020-11-1009:43:28'); INSERTINTO`test_tb`VALUES(9,1009,'ewer','2020-11-1009:43:28','2020-11-1009:43:28'); INSERTINTO`test_tb`VALUES(10,1010,'erre','2020-11-1115:17:03','2020-11-1115:17:03'); #6条数据和数据库中查询一致 UNLOCKTABLES; --Dumpcompletedon2020-11-1116: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参数的资料请关注毛票票其它相关文章！