AI 助理
备案控制台
开发者社区 数据库 文章 正文

mysqldump备份失败案例

2019-07-13 1058
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
RDS MySQL Serverless 高可用系列，价值2615元额度，1个月
简介： mysql 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<t1_time2.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 |
+----+---------------------+---------------------+

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选项，否则可能会导致备份数据异常。
  备份后一定要验证备份文件的有效性！

文章标签：
云数据库 RDS MySQL 版
关系型数据库
MySQL
存储
SQL
数据库
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL，然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
梓杰
目录
相关文章
南城余
|
7月前
|
SQL 关系型数据库 MySQL
MySQL进阶 - 备份与恢复
MySQL进阶 - 备份与恢复
南城余
39 0
红目香薰
|
8月前
|
SQL 关系型数据库 MySQL
MySQL技能完整学习列表11、日志和备份——1、查看日志——2、数据备份和恢复（mysqldump, mysqlbinlog）
MySQL技能完整学习列表11、日志和备份——1、查看日志——2、数据备份和恢复（mysqldump, mysqlbinlog）
红目香薰
105 0
姚远OracleACE
|
SQL 关系型数据库 MySQL
MySQL的备份和恢复案例
当发生掉电或硬件故障，MySQL重新启动时会自动进行Crash Recovery，从日志文件中读取pending的信息，rollback uncommit同时把已经提交的交易flush到datafile，这些信息可以从MySQL的错误日志中看到，这里我用kill -9模拟宕机
姚远OracleACE
172 0
游客r35aptsdr4whc
|
关系型数据库 MySQL Shell
mysqldump工具备份数据
mysqldump工具备份数据
游客r35aptsdr4whc
129 0
mysqldump工具备份数据
金雪人
|
SQL Oracle 关系型数据库
MySQL误删恢复方法1
MySQL不同于oracle，没有闪回查询这类概念，但网上流传几个闪回的开源工具如 binglog2sql、MyFlash，可以使用binglog日志进行误操作数据的恢复。
金雪人
166 0
金雪人
|
SQL 运维 关系型数据库
MySQL误删恢复方法2
实际工作中总会发生数据误删除的场景，在没有备份情况下，如何快速恢复误删数据就显得非常重要。 本文基于MySQL的binlog日志机制，当日志格式设置为“binlog_format=ROW”时，记录一步一步手动解析binlog、恢复误删数据的全过程，供大家参考使用。
金雪人
184 0
互联网-小阿宇
|
数据安全/隐私保护
Mysqldump备份报错1449
Mysqldump备份报错1449
互联网-小阿宇
285 0
Java大家族
|
SQL 关系型数据库 MySQL
MySQL之数据的备份与还原
MySQL之数据的备份与还原
Java大家族
220 0
余二五
|
MySQL 关系型数据库 数据库
mysql数据备份与还原命令
余二五
990 0
技术小胖子
|
Ubuntu 关系型数据库 MySQL
生产环境究竟是使用mysqldump还是xtrabackup来备份与恢复数据库？
技术小胖子
1713 0

热门文章

最新文章

  • 1
    DDD基础教程：一文带你读懂DDD分层架构
  • 2
    charles抓包显示乱码解决方法
  • 3
    持续定义SaaS模式云数据仓库+数据银行
  • 4
    ip查询显示泛播是什么意思？
  • 5
    patch 命令用法详解（转）
  • 6
    小程序审核常见驳回类型
  • 7
    PHP Memcache扩展安装配置步骤
  • 8
    PreferenceActivity 自动保存属性
  • 9
    netstat命令详解
  • 10
    “点击欺诈”恶意软件藏有更大的威胁？
  • 1
    插件实现12306网站“按预填信息”自动抢票
    49
  • 2
    SPAR3D：一张图片就能生成3D模型，每个物体的重建时间仅需0.7秒！
    48
  • 3
    Search-o1：人大清华联合推出动态检索推理框架，使模型能够在推理过程中动态检索外部知识
    38
  • 4
    在使用Promise.allSettled()时，如何处理不同状态的Promise？
    34
  • 5
    除了使用Polyfill，还有其他解决Promise.allSettled()兼容性问题的方法吗？
    35
  • 6
    Promise.allSettled()方法的兼容性如何？
    109
  • 7
    Promise.allSettled()方法的语法是什么？
    40
  • 8
    Promise.allSettled()和Promise.all()在处理错误时的差异是什么？
    29
  • 9
    Promise.allSettled() 和 Promise.all() 有什么区别？
    27
  • 10
    灵码回复消息的字体太小,并在pycharm中没法设置.Baidu Comate就可以直接插件中设置了
    109

    • 相关电子书

    更多
  • 数据库异地备份及不还原快速查询备份集最佳实践
  • 阿里云数据库案例集下载
  • 高可用数据库的搭建与备份恢复策略验证实战

    • 相关实验场景

    更多
  • 快照备份与恢复
  • Tair for Redis数据闪回：任意时间点数据恢复
  • PolarDB MySQL从表回收站中恢复误删的表
  • PolarDB MySQL通过DMS恢复误操作数据
    • 下一篇
    DataWorks智能交互式数据开发与分析之旅