當心！使用mysqldump備份可能會讓你欲哭無淚
1.問題描述
在一次使用mysqldump備份單表部分數據時，發現無備份數據。陣針對這一奇怪現象，進行分析。
2.問題復現與分析
#表結構信息
mysql> show create table test.t1;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_t` (`time`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
#表數據信息
mysql> select * from test.t1;
+----+---------------------+
| id | time |
+----+---------------------+
| 1 | 2018-12-10 22:15:39 |
| 2 | 2018-12-10 22:15:47 |
| 3 | 2018-12-10 22:15:50 |
| 4 | 2018-12-10 22:15:56 |
| 5 | 2018-12-10 22:15:57 |
| 6 | 2018-12-10 22:15:58 |
| 7 | 2018-12-10 22:15:58 |
| 8 | 2018-12-10 22:16:06 |
| 9 | 2018-12-10 22:16:06 |
| 10 | 2018-12-10 22:16:07 |
| 11 | 2018-12-10 22:16:08 |
| 12 | 2018-12-10 22:16:13 |
| 13 | 2018-12-10 22:16:13 |
| 14 | 2018-12-10 22:16:14 |
| 15 | 2018-12-10 22:16:15 |
+----+---------------------+
15 rows in set (0.00 sec)
使用mysqldump --where選項備份t1表部分數據。
#使用mysqldump根據time列條件備份
mysqldump -uroot -p123456 --default-character-set=utf8 -q --master-data=2 --single-transaction --databases test --tables t1 --where "time>2018-12-10 22:16:08">beifen.sql
#通過備份文件可以看出備份結果中並無數據。
...
LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;
...
在MySQL中使用相同條件查詢，並無異常，可以查到數據。
[root@master ~]# mysql -uroot -p123456 -e "select * from test.t1 where time>2018-12-10 22:16:08"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+---------------------+
| id | time |
+----+---------------------+
| 12 | 2018-12-10 22:16:13 |
| 13 | 2018-12-10 22:16:13 |
| 14 | 2018-12-10 22:16:14 |
| 15 | 2018-12-10 22:16:15 |
+----+---------------------+
嘗試備份t1全表數據
mysqldump -uroot -p123456 --default-character-set=utf8 -q --master-data=2 --single-transaction --databases test --tables t1 >beifen.sql
#有數據，但是仔細對照，我們可以發現時間回退了八個小時。
LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` VALUES (1,2018-12-10 14:15:39),(2,2018-12-10 14:15:47),(3,2018-12-10 14:15:50),(4,2018-12-10 14:15:56),(5,2018-12-10 14:15:57),(6,2018-12-10 14:15:58),(7,2018-12-10 14:15:58),(8,2018-12-10 14:16:06),(9,2018-12-10 14:16:06),(10,2018-12-10 14:16:07),(11,2018-12-10 14:16:08),(12,2018-12-10 14:16:13),(13,2018-12-10 14:16:13),(14,2018-12-10 14:16:14),(15,2018-12-10 14:16:15);
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;
#查看mysqldump備份文件頭部信息，mysqldump使用的是中時區。
...
/*!40103 SET TIME_ZONE=+00:00 */;
...
#查看MySQL和系統時區。
mysql> show variables like %time%;
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_password_lifetime | 0 |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| have_statement_timeout | YES |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| log_timestamps | UTC |
| long_query_time | 10.000000 |
| max_execution_time | 0 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 60 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | +08:00 |
| timestamp | 1544775697.554299 |
| wait_timeout | 28800 |
+---------------------------------+-------------------+
29 rows in set (0.01 sec)
[root@master ~]# date -R
Wed, 12 Dec 2018 16:00:34 +0800
#模擬數據恢復
mysql> drop table test.t1;
mysql -uroot -p123456<beifen
mysql> select * from t1;
+----+---------------------+
| id | time |
+----+---------------------+
| 1 | 2018-12-10 22:15:39 |
| 2 | 2018-12-10 22:15:47 |
| 3 | 2018-12-10 22:15:50 |
| 4 | 2018-12-10 22:15:56 |
| 5 | 2018-12-10 22:15:57 |
| 6 | 2018-12-10 22:15:58 |
| 7 | 2018-12-10 22:15:58 |
| 8 | 2018-12-10 22:16:06 |
| 9 | 2018-12-10 22:16:06 |
| 10 | 2018-12-10 22:16:07 |
| 11 | 2018-12-10 22:16:08 |
| 12 | 2018-12-10 22:16:13 |
| 13 | 2018-12-10 22:16:13 |
| 14 | 2018-12-10 22:16:14 |
| 15 | 2018-12-10 22:16:15 |
+----+---------------------+
15 rows in set (0.00 sec)
#數據恢復正常。但是存在一個問題，因為mysqldump備份時會把數據進行時區轉換，導致mysqldump過濾條件與篩選的數據差8個小時，所以可能會使where條件過濾不準確。比如文章開頭所碰到的問題。
解決辦法
#使用--skip-tz-utc，不使用mysqldump默認的中時區。
mysqldump --default-character-set=utf8 -q --master-data=2 --single-transaction --databases test --tables t1 --where "time=2018-12-10 22:16:08" -uroot -p123456 --skip-tz-utc>beifen.sql
#數據完全正確，文件頭部無時區轉換，所以數據恢復時正常。
LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` VALUES (11,2018-12-10 22:16:08);
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;
3.總結
在使用mysqldump備份含有timestamp類型列的表時，需要指定--skip-tz-utc選項，否則可能會導致備份數據異常。（推薦使用datetime，與時區無關且存儲範圍更大，可以避免一些問題。）
| 作者簡介
薛世傑·沃趣科技資料庫工程師
熟悉MySQL體系結構和innodb存儲引擎工作原理；擅長資料庫問題分析，備份恢復、SQL調優；喜好鑽研開源技術，熟悉多種開源工具。
推薦閱讀：
