mysqldump备份失败案例-白红宇

mysqldump备份失败案例

阅读量：394 次

发布时间：2019-03-05

本文共 7978 字，大约阅读时间需要 26 分钟。

mysqldump备份失败案例

一、问题描述

在一次备份过程中使用mysqldump以单表时间数据类型进行过滤备份，到恢复数据时发现mysqldump备份出来的sql文件中并没有数据。

二、问题复现与排查

2.1 问题复现

为了更好的问题排查，在表中建立了timestamp和datetime两列进行对比验证，表结构和数据如下：

mysql> show create table t1;+-------+-------------------------------------------------------------------------------------------+| Table | Create Table                                           +-------+-------------------------------------------------------------------------------------------+| t1    | CREATE TABLE `t1` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `time1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  `time2` datetime NOT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8 |+-------+-------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> select * from t1;+----+---------------------+---------------------+| id | time1               | time2               |+----+---------------------+---------------------+|  1 | 2019-07-08 13:10:13 | 2019-07-08 13:10:13 ||  2 | 2019-07-08 13:10:14 | 2019-07-08 13:10:14 ||  3 | 2019-07-08 13:10:15 | 2019-07-08 13:10:15 ||  4 | 2019-07-08 13:10:15 | 2019-07-08 13:10:15 ||  5 | 2019-07-08 13:10:15 | 2019-07-08 13:10:15 ||  6 | 2019-07-08 13:10:16 | 2019-07-08 13:10:16 ||  7 | 2019-07-08 13:10:16 | 2019-07-08 13:10:16 ||  8 | 2019-07-08 13:10:18 | 2019-07-08 13:10:18 ||  9 | 2019-07-08 13:12:48 | 2019-07-08 13:12:48 || 10 | 2019-07-08 13:12:49 | 2019-07-08 13:12:49 || 11 | 2019-07-08 13:12:49 | 2019-07-08 13:12:49 || 12 | 2019-07-08 13:12:54 | 2019-07-08 13:12:54 || 13 | 2019-07-08 13:13:13 | 2019-07-08 13:13:13 || 14 | 2019-07-08 13:13:16 | 2019-07-08 13:13:16 || 15 | 2019-07-08 13:13:17 | 2019-07-08 13:13:17 || 16 | 2019-07-08 13:13:19 | 2019-07-08 13:13:19 || 17 | 2019-07-08 13:13:21 | 2019-07-08 13:13:21 || 18 | 2019-07-08 13:13:22 | 2019-07-08 13:13:22 || 19 | 2019-07-08 13:13:23 | 2019-07-08 13:13:23 || 20 | 2019-07-08 13:13:25 | 2019-07-08 13:13:25 |+----+---------------------+---------------------+20 rows in set (0.00 sec)

使用mysqldump对timestamp和datetime两种数据类型进行过滤备份，备份语句和备份结果如下：

使用timestamp列作为过滤条件的备份语句：

mysqldump -uroot -p -S /usr/local/mysql/data/sock/mysql.sock --default-character-set=utf8 -q --master-data=2 --single-transaction --triggers --routines --events --databases boke --tables t1 --where "time1>'2019-07-08 13:12:48'">t1_time1.sql

使用datetime列作为过滤条件的备份语句：

mysqldump -uroot -p -S /usr/local/mysql/data/sock/mysql.sock --default-character-set=utf8 -q --master-data=2 --single-transaction --triggers --routines --events --databases boke --tables t1 --where "time2>'2019-07-08 13:12:48'">t1_time2.sql

使用timestamp列作为过滤条件的备份结果：

···/*!40103 SET TIME_ZONE='+00:00' */;···DROP TABLE IF EXISTS `t1`;/*!40101 SET @saved_cs_client     = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `t1` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `time1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  `time2` datetime NOT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client */;---- Dumping data for table `t1`---- WHERE:  time1>'2019-07-08 13:12:48'LOCK TABLES `t1` WRITE;/*!40000 ALTER TABLE `t1` DISABLE KEYS */;/*!40000 ALTER TABLE `t1` ENABLE KEYS */;UNLOCK TABLES;

使用datetime列作为过滤条件的备份结果：

···/*!40103 SET TIME_ZONE='+00:00' */;···DROP TABLE IF EXISTS `t1`;/*!40101 SET @saved_cs_client     = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `t1` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `time1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  `time2` datetime NOT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client */;---- Dumping data for table `t1`---- WHERE:  time2>'2019-07-08 13:12:48'LOCK TABLES `t1` WRITE;/*!40000 ALTER TABLE `t1` DISABLE KEYS */;INSERT INTO `t1` VALUES (10,'2019-07-08 05:12:49','2019-07-08 13:12:49'),(11,'2019-07-08 05:12:49','2019-07-08 13:12:49'),(12,'2019-07-08 05:12:54','2019-07-08 13:12:54'),(13,'2019-07-08 05:13:13','2019-07-08 13:13:13'),(14,'2019-07-08 05:13:16','2019-07-08 13:13:16'),(15,'2019-07-08 05:13:17','2019-07-08 13:13:17'),(16,'2019-07-08 05:13:19','2019-07-08 13:13:19'),(17,'2019-07-08 05:13:21','2019-07-08 13:13:21'),(18,'2019-07-08 05:13:22','2019-07-08 13:13:22'),(19,'2019-07-08 05:13:23','2019-07-08 13:13:23'),(20,'2019-07-08 05:13:25','2019-07-08 13:13:25');/*!40000 ALTER TABLE `t1` ENABLE KEYS */;UNLOCK TABLES;

进行数据恢复：

mysql -S /usr/local/mysql/data/sock/mysql.sock boke

2.2 问题分析

由上文的备份结果可知，在相同条件下，使用timestamp列作为过滤条件进行备份时，备份文件中并没有备份数据；在使用datetime列作为过滤条件进行备份的情况下，可以发现虽然是有数据的，但是’time1’和’time2’两列的时间不一致。

看到上面这个现象，这个时候我们应该就明白了，问题出在这两个时间数据类型上面。对于timestamp这种数据类型，会把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。而对于datetime这种数据类型，不会做任何改变，就是原样输入和输出。也就是说timestamp数据类型的记录导出会以utc时间格式导出，导入库中自动由UTC格式转为系统默认时区，所以看到导出文件timestamp内容和实际存储的不相符。同时也可以明白为什么使用time1列作为过滤条件为什么备份文件中没有数据。因为timestamp数据类型在数据库中是以utc时间格式存储，也就是将时间回退八个小时，但是过滤条件依旧是原来的时间。这就会导致备份时过滤出的结果并不是原本需要的，导致备份“失败”。

三、解决办法

在使用mysqldump备份时，加入参数–skip-tz-utc用于禁止timestamp时区转换。

mysqldump -S /usr/local/mysql/data/sock/mysql.sock --default-character-set=utf8 -q --master-data=2 --single-transaction --triggers --routines --events --skip-tz-utc --databases boke --tables t1 --where "time1>'2019-07-08 13:12:48'">t1_time1_2.sql

DROP TABLE IF EXISTS `t1`;/*!40101 SET @saved_cs_client     = @@character_set_client */; SET character_set_client = utf8mb4 ;CREATE TABLE `t1` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `time1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  `time2` datetime NOT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client */;---- Dumping data for table `t1`---- WHERE:  time1>'2019-07-08 13:12:48'LOCK TABLES `t1` WRITE;/*!40000 ALTER TABLE `t1` DISABLE KEYS */;INSERT INTO `t1` VALUES (10,'2019-07-08 13:12:49','2019-07-08 13:12:49'),(11,'2019-07-08 13:12:49','2019-07-08 13:12:49'),(12,'2019-07-08 13:12:54','2019-07-08 13:12:54'),(13,'2019-07-08 13:13:13','2019-07-08 13:13:13'),(14,'2019-07-08 13:13:16','2019-07-08 13:13:16'),(15,'2019-07-08 13:13:17','2019-07-08 13:13:17'),(16,'2019-07-08 13:13:19','2019-07-08 13:13:19'),(17,'2019-07-08 13:13:21','2019-07-08 13:13:21'),(18,'2019-07-08 13:13:22','2019-07-08 13:13:22'),(19,'2019-07-08 13:13:23','2019-07-08 13:13:23'),(20,'2019-07-08 13:13:25','2019-07-08 13:13:25');/*!40000 ALTER TABLE `t1` ENABLE KEYS */;UNLOCK TABLES;

mysql -S /usr/local/mysql/data/sock/mysql.sock -e "drop table boke.t1;" mysql -S /usr/local/mysql/data/sock/mysql.sock -e "select * from  boke.t1;" ERROR 1146 (42S02) at line 1: Table 'boke.t1' doesn't exist mysql -S /usr/local/mysql/data/sock/mysql.sock boke< t1_time1_2.sql  mysql -S /usr/local/mysql/data/sock/mysql.sock -e "select * from  boke.t1;"+----+---------------------+---------------------+| id | time1               | time2               |+----+---------------------+---------------------+| 10 | 2019-07-08 13:12:49 | 2019-07-08 13:12:49 || 11 | 2019-07-08 13:12:49 | 2019-07-08 13:12:49 || 12 | 2019-07-08 13:12:54 | 2019-07-08 13:12:54 || 13 | 2019-07-08 13:13:13 | 2019-07-08 13:13:13 || 14 | 2019-07-08 13:13:16 | 2019-07-08 13:13:16 || 15 | 2019-07-08 13:13:17 | 2019-07-08 13:13:17 || 16 | 2019-07-08 13:13:19 | 2019-07-08 13:13:19 || 17 | 2019-07-08 13:13:21 | 2019-07-08 13:13:21 || 18 | 2019-07-08 13:13:22 | 2019-07-08 13:13:22 || 19 | 2019-07-08 13:13:23 | 2019-07-08 13:13:23 || 20 | 2019-07-08 13:13:25 | 2019-07-08 13:13:25 |+----+---------------------+---------------------+

备份恢复成功！这个时候一定会有人问恢复时不会多加八个小时么？因为使用–skip-tz-utc选项后，导出文件中开头不会设置的/*!40103 SET TIME_ZONE=’+00:00’ */。

四、总结

在使用mysqldump备份且过滤条件为timestamp数据类型时，需要指定–skip-tz-utc选项，否则可能会导致备份数据异常。

备份后一定要验证备份文件的有效性！

转载地址：http://nkrwz.baihongyu.com/

你可能感兴趣的文章