当心！使用mysqldump备份可能会让你欲哭无泪

淩亂°似流年 2022-03-17 15:46 223阅读 0赞

### **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调优；喜好钻研开源技术，熟悉多种开源工具。