0、導讀

用mysqldump備份數據時，加上 -w 條件選項過濾部分數據，發現導出結果比實際少了40萬，什么情況？

本文約1500字，閱讀時間約5分鐘。

1、問題

我的朋友小文前幾天遇到一個怪事，他用mysqldump備份數據時，加上了 -w 選項過濾部分數據，發現導出的數據比實際上少了40萬。

要進行備份表DDL見下：

CREATE TABLE `oldbiao` (

  `aaaid` int(11) NOT NULL,

  `bbbid` int(11) NOT NULL,

  `cccid` int(11) NOT NULL,

  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

  `dddid` int(11) DEFAULT NULL,

  KEY `index01` (`ccccid`),

  KEY `index02` (`dddid`,`time`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

順便吐槽下，這個表DDL設計的真是low啊。沒主鍵，允許NULL。

mysqldump備份指令增加的 -w/--where 選項是：

-w "time>‘2016-08-01 00:00:00'"

加上這個參數的作用是：只備份 2016-8-1 之后的所有數據，相當于執行了下面這樣的SQL命令：

SELECT SQL_NO_CACHE * FROM t WHERE time>‘2016-08-01 00:00:00'

然后把導出的SQL文件恢復后，再隨機抽查下數據看看是否有遺漏的。不查不知道，一查嚇一跳，發現 2016-12-12 下午的數據是缺失的。經過仔細核查，發現比原數據大概少了40萬條記錄。

百思不得其解的小文請我幫忙排查問題。

2、排查

既然是少了一部分數據，那就要先定位到底是丟失了的是哪部分數據。

那么，如何定位呢？

搞數據庫的人，應該都知道折半查找法，這是計算機科學里比較基礎的概念之一。我們就利用這種方法來快速定位。

經過排查，發現是缺少的數據有個特點，根據時間排序，發現最早的數據是 2016-8-1 8點的，而我們上面設定的條件則是 2016-8-1 0點開始的所有數據，整整差了8個小時。

看到8小時這個特點，我想你應該大概想到什么原因了吧。對，沒錯，就是因為時區的因素導致的。

經過排查，發現是因為原先寫數據時，是以 0時區 時間寫入的，但執行mysqldump備份時則使用的是本地 東8區 的時間，所以就有了8小時的差距。

2、解決

知道了問題所在，方法就簡單了。有兩個方法：

1、修改mysqldump中的where條件時間值，減去8個小時。建議采用該方法。

mysqldump ... -w "time>‘2016-07-31 16:00:00'"

2、修改MySQL全局時區，從 0時區 改成 東8區，并且mysqldump加上 --skip-tz-utc 選項。這種方法需要修改MySQL的全局時區，可能會導致更多的業務問題，因此強烈不建議使用。

mysqldump ... --skip-tz-utc -w "time>‘2016-08-01 08:00:00'"

問題暫且按下，我們先來看下時區因素怎么影響查詢結果的。

先看下系統本地時間：

[yejr@imsyql]$ date -R

Wed, 21 Dec 2016 14:04:51 +0800

測試表DDL：

CREATE TABLE `t1` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

...

  `c1` timestamp NULL DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB;

查看MySQL的時區設置：
圖1

然后寫入一條數據：
圖2

第一次備份，用本地時間條件去過濾：

mysqldump -w "dt >= '2016-12-21 14:00:00'"

這種情況下，顯然是沒有結果的。
圖3

第二次備份，用本地時間減去8小時再去過濾：

mysqldump -w "dt >= '2016-12-21 06:00:00'"

這種就可以備份出數據了。
圖4

此外，我們注意到mysqldump的 --tz-utc 選項，它是和時區設定有關系的：

--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.)

這個選項的作用，就是以 0時區  備份數據，把所有時間都轉換成 0時區 的數據。比如本來是在 東8區(+08:00) 的時間 14:00:00，轉換成 0時區 后，會變成 06:00:00，原來是 西8區(-08:00) 的時間14:00:00，則轉換成 22:00:00。這個選項是默認啟用的。

在上面第一次備份時沒有數據，就是因為MySQL里本身存儲的就是 0時區 的數據，mysqldump也設定了轉換成 0時區，我們傳遞進去的參數卻是 東8區 的時間，因此沒有數據。

3、總結

本來只想簡單寫一下的，結果啰里啰嗦寫了好多。

其實我們只需要注意一點，服務器在哪里，就是用哪里的時區，也就是 SYSTEM 時區，在做SQL查詢以及mysqldump備份數據時，也使用服務器上的時間，而不使用我們本地時間。
圖5