mysqldump你可能不知道的參數(shù)
在前面文章中，有提到過 mysqldump 備份文件中記錄的時(shí)間戳數(shù)據(jù)都是以 UTC 時(shí)區(qū)為基礎(chǔ)的，在篩選恢復(fù)單庫或單表時(shí)要注意時(shí)區(qū)差別。后來再次查看文檔，發(fā)現(xiàn) tz-utc、skip-tz-utc 參數(shù)與此有關(guān)，本篇文章我們一起來看下此參數(shù)的作用吧。
1.tz-utc與skip-tz-utc參數(shù)介紹
這兩個(gè)參數(shù)可以作用于 mysqldump 備份過程中，互為相反參數(shù)。顧名思義可以看出，一個(gè)參數(shù)是將時(shí)間戳改為 UTC 時(shí)區(qū)，另一個(gè)是跳過時(shí)區(qū)變動。
在 mysql 服務(wù)器上執(zhí)行 mysqldump --help 的命令，可以看到下面一段話。
[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.
...省略很多內(nèi)容
--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 參數(shù)是 mysqldump 的默認(rèn)參數(shù)，會使得 mysqldump 的導(dǎo)出文件的頂部加上一個(gè)設(shè)置時(shí)區(qū)的語句 SET TIME_ZONE='+00:00' ，這個(gè)時(shí)區(qū)是格林威治時(shí)間，也就是0時(shí)區(qū)。這樣當(dāng)導(dǎo)出 timestamp 時(shí)間戳字段時(shí)，會把在服務(wù)器設(shè)置的當(dāng)前時(shí)區(qū)下顯示的 timestamp 時(shí)間值轉(zhuǎn)化為在格林威治時(shí)間下顯示的時(shí)間。比如我們數(shù)據(jù)庫采用北京時(shí)間東八區(qū)，mysqldump 導(dǎo)出的文件當(dāng)中顯示的 timestamp 時(shí)間值相對于通過數(shù)據(jù)庫查詢顯示的時(shí)間倒退了8個(gè)小時(shí)。
知道了 --tz-utc ，那么 --skip-tz-utc 的含義就是當(dāng) mysqldump 導(dǎo)出數(shù)據(jù)時(shí)，不使用格林威治時(shí)間，而使用當(dāng)前 mysql 服務(wù)器的時(shí)區(qū)進(jìn)行導(dǎo)出，這樣導(dǎo)出的數(shù)據(jù)中顯示的 timestamp 時(shí)間值也和表中查詢出來的時(shí)間值相同。
2.實(shí)驗(yàn)參數(shù)具體作用
為了更清楚了解這對參數(shù)的作用，下面我們來具體測試下，我們知道 mysqldump 后可以跟 where 條件來備份部分?jǐn)?shù)據(jù)，若根據(jù) timestamp 字段來備份部分?jǐn)?shù)據(jù)，這對參數(shù)是否有影響呢？我們一并來驗(yàn)證下：
先來看下我的環(huán)境設(shè)置及測試數(shù)據(jù)：
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.23-log |
+------------+
1 row in set (0.00 sec)
# 時(shí)區(qū)采用北京時(shí)間東八區(qū)
mysql> show variables like 'time_zone';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| time_zone | +08:00 |
+---------------+--------+
1 row in set (0.00 sec)
# 測試表 有datetime字段和timestamp字段 共10條數(shù)據(jù) 兩個(gè)時(shí)間顯示是相同的
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 '學(xué)號',
`stu_name` varchar(20) DEFAULT NULL COMMENT '學(xué)生姓名',
`dt_time` datetime NOT NULL,
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間',
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 默認(rèn)開啟 tz-utc ，先來看下默認(rèn)情況下的備份結(jié)果：
# 為更明顯看出結(jié)果 我們使用skip-extended-insert來一行行展現(xiàn)數(shù)據(jù) # 全庫備份 [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' */; # 先保存老時(shí)區(qū) 然后將此會話時(shí)區(qū)改為0時(shí)區(qū) ...省略 -- -- 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時(shí)間值減去了8小時(shí) 而datetime時(shí)間值不變 UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; # 再將時(shí)區(qū)改為原時(shí)區(qū) /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; -- Dump completed on 2020-11-11 15:34:21 # 使用where條件備份單表部分?jǐn)?shù)據(jù) 備份11月份以來的數(shù)據(jù) # 數(shù)據(jù)庫中查詢 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導(dǎo)出 [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'); # 發(fā)現(xiàn)只導(dǎo)出4條 UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; -- Dump completed on 2020-11-11 15:58:56
建議各位仔細(xì)看下上面導(dǎo)出結(jié)果，說實(shí)話，筆者原來也沒做過詳細(xì)測試，現(xiàn)在看到結(jié)果也是稍微有點(diǎn)吃驚的。默認(rèn)情況下，全備出來的數(shù)據(jù)是沒問題的，雖然將 timestamp 時(shí)間值轉(zhuǎn)為0時(shí)區(qū)顯示，但當(dāng)你導(dǎo)入數(shù)據(jù)庫時(shí)還會以你的數(shù)據(jù)庫時(shí)區(qū)來展示 timestamp 時(shí)間。但使用 where 條件導(dǎo)出部分?jǐn)?shù)據(jù)時(shí)，卻出現(xiàn)了數(shù)據(jù)庫中查詢得出的結(jié)果與dump導(dǎo)出的結(jié)果不同的情況，這個(gè)時(shí)候 mysqldump 只導(dǎo)出了轉(zhuǎn)化成0時(shí)區(qū)后的時(shí)間值符合 where 條件的數(shù)據(jù)，與直接查詢出的結(jié)果有出入，這是我原來沒注意到的。
再來看下使用 --skip-tz-utc 參數(shù)，看下這個(gè)參數(shù)是否符合我們的預(yù)期：
# 使用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 ..省略 未見時(shí)區(qū)更改語句 -- -- 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時(shí)間值顯示與datetime顯示一樣 未做轉(zhuǎn)換 UNLOCK TABLES; -- Dump completed on 2020-11-11 16:23:32 # 使用skip-tz-utc備份部分?jǐn)?shù)據(jù) [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條數(shù)據(jù) 和數(shù)據(jù)庫中查詢一致 UNLOCK TABLES; -- Dump completed on 2020-11-11 16:28:39
從上面結(jié)果可以看出，使用 --skip-tz-utc 參數(shù)后，timestamp 時(shí)間戳字段值不會轉(zhuǎn)換，導(dǎo)出部分?jǐn)?shù)據(jù)也符合預(yù)期。
3.一些小建議
那么這個(gè)參數(shù)的意義何在呢？當(dāng)你的數(shù)據(jù)庫服務(wù)器處于不同時(shí)區(qū)時(shí)。假設(shè)一個(gè)服務(wù)器在北京（東八區(qū)），一個(gè)服務(wù)器在東京（東九區(qū)），現(xiàn)在需要將北京服務(wù)器里的數(shù)據(jù)導(dǎo)入至東京服務(wù)器。當(dāng)導(dǎo)入按照默認(rèn)不加 --skip-tz-utc 參數(shù)的dump文件，查詢的 timestamp 時(shí)間數(shù)據(jù)相對于在之前的東八區(qū)服務(wù)器的時(shí)間值多了一個(gè)小時(shí)，但由于東八區(qū)服務(wù)器里的13點(diǎn)和東九區(qū)服務(wù)器里的14點(diǎn)代表的是同一時(shí)刻，所以，在東九區(qū)的服務(wù)器里顯示的多出的一個(gè)小時(shí)，這樣顯示是正確的。而如果增加 --skip-tz-utc 參數(shù)，dump文件導(dǎo)入東九區(qū)服務(wù)器后，盡管顯示的時(shí)間值和之前東八區(qū)服務(wù)器顯示的時(shí)間值相同，但兩者代表的時(shí)刻卻已經(jīng)不同。
關(guān)于這個(gè)參數(shù)應(yīng)該如何使用，我們首先應(yīng)該明白，是否加上 --skip-tz-utc 參數(shù)，只會影響 timestamp 字段的導(dǎo)入導(dǎo)出，對 datetime 時(shí)間字段不會影響。
這里筆者建議首先對 timestamp 字段使用作出規(guī)范。比如 timestamp 字段只用于創(chuàng)建時(shí)間和更新時(shí)間需求，只代表該行數(shù)據(jù)的創(chuàng)建及更新時(shí)間，做到與業(yè)務(wù)弱相關(guān)，其他時(shí)間字段盡量使用 datetime 。這樣即使 mysqldump 采用不同參數(shù)，實(shí)際產(chǎn)生影響也不大。
如果你的服務(wù)器處于不同時(shí)區(qū)，那建議還是按照默認(rèn)來，這樣導(dǎo)入導(dǎo)出的數(shù)據(jù)都是正確的。如果你的服務(wù)器都是處于同一時(shí)區(qū)，那么是否使用 --skip-tz-utc 參數(shù)區(qū)別不大，我們只需知道默認(rèn)情況 mysqldump 會將 timestamp 時(shí)間值轉(zhuǎn)為0時(shí)區(qū)存儲即可。當(dāng)備份部分?jǐn)?shù)據(jù)且以 timestamp 字段來篩選時(shí)，這時(shí)候建議增加 --skip-tz-utc 參數(shù)。這里再次提醒下，從全備中篩選單庫或單表的備份時(shí)，也要注意下 timestamp 字段數(shù)據(jù)。
以上就是mysqldump你可能不知道的參數(shù)的詳細(xì)內(nèi)容，更多關(guān)于mysqldump 參數(shù)的資料請關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
MySQL設(shè)置global變量和session變量的兩種方法詳解
這篇文章主要介紹了MySQL設(shè)置global變量和session變量的兩種方法，每種方法給大家介紹的非常詳細(xì) ,需要的朋友可以參考下2018-10-10
Mysql聯(lián)合查詢UNION和UNION ALL的使用介紹
本文詳細(xì)介紹了Mysql的聯(lián)合查詢命令UNION和UNION ALL,總結(jié)了使用語法和注意事項(xiàng)，以及學(xué)習(xí)例子和項(xiàng)目例子,需要的朋友可以參考下2014-04-04
這篇文章主要介紹了mysql查詢表里的重復(fù)數(shù)據(jù)方法,需要的朋友可以參考下2017-05-05
mysql 字符串長度計(jì)算實(shí)現(xiàn)代碼(gb2312+utf8)
PHP對中文字符串的處理一直困擾于剛剛接觸PHP開發(fā)的新手程序員。下面簡要的剖析一下PHP對中文字符串長度的處2011-12-12
這篇文章主要介紹了MySQL自定義函數(shù)簡單用法,結(jié)合實(shí)例形式分析了mysql自定義函數(shù)的基本定義、使用方法及操作注意事項(xiàng),需要的朋友可以參考下2018-12-12
給大家用過實(shí)例分析了Mysql查詢中指定順序排序的相關(guān)技術(shù)問題，需要的朋友參考一下吧。2017-12-12
MySQL有時(shí)候忘記了root密碼是一件傷感的事，下面通過本文給大家介紹Mysql忘記root密碼怎么辦的相關(guān)知識，需要的朋友參考下2016-01-01
這篇文章主要介紹了mysqld_safe啟動腳本源碼閱讀、分析,mysqld_safe是一個(gè)帶有安全特性的啟動腳本,使用Shell語言編寫,需要的朋友可以參考下2014-07-07
這篇文章主要介紹了MYSQL大量寫入問題優(yōu)化詳解，文中優(yōu)化點(diǎn)解釋的很清楚，讓人看完就明了，感興趣的同學(xué)可以閱讀理解下2021-03-03
這篇文章主要介紹了帶你了解MySQL中的事件調(diào)度器EVENT，幫助大家更好的理解和學(xué)習(xí)MySQL，感興趣的朋友可以了解下2020-08-08