备案控制台
探索云世界
开发者社区 数据库 文章 正文

mysqldump备份失败案例

2019-07-13 1005
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL Serverless，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
简介： 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
数据库
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境，其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
梓杰
目录
相关文章
红目香薰
|
2月前
|
SQL 关系型数据库 MySQL
MySQL技能完整学习列表11、日志和备份——1、查看日志——2、数据备份和恢复（mysqldump, mysqlbinlog）
MySQL技能完整学习列表11、日志和备份——1、查看日志——2、数据备份和恢复（mysqldump, mysqlbinlog）
红目香薰
45 0
游戏服务器开发社区
|
5月前
|
SQL 缓存 关系型数据库
MYSQL增量备份和全量备份脚本
MYSQL增量备份和全量备份脚本
游戏服务器开发社区
456 1
姚远OracleACE
|
11月前
|
SQL 关系型数据库 MySQL
MySQL的备份和恢复案例
当发生掉电或硬件故障，MySQL重新启动时会自动进行Crash Recovery，从日志文件中读取pending的信息，rollback uncommit同时把已经提交的交易flush到datafile，这些信息可以从MySQL的错误日志中看到，这里我用kill -9模拟宕机
姚远OracleACE
114 0
baphsqca3imha
|
SQL 弹性计算 数据可视化
MySQL 下全量备份与还原｜学习笔记
快速学习 MySQL 下全量备份与还原
baphsqca3imha
156 0
MySQL 下全量备份与还原｜学习笔记
baphsqca3imha
|
SQL 存储 关系型数据库
MySQL 下全量和增量备份与还原原理｜学习笔记
快速学习 MySQL 下全量和增量备份与还原原理
baphsqca3imha
163 0
互联网-小阿宇
|
数据安全/隐私保护
Mysqldump备份报错1449
Mysqldump备份报错1449
互联网-小阿宇
159 0
游客r35aptsdr4whc
|
关系型数据库 MySQL Shell
mysqldump工具备份数据
mysqldump工具备份数据
游客r35aptsdr4whc
90 0
mysqldump工具备份数据
时光不写博客
|
SQL 关系型数据库 MySQL
mysql备份单个表恢复失败
问题 备份数据库 mysqldump -uroot -p database_name tab1 > test.sql; # 远程加 -h 恢复数据库 使用的是source，进入数据库以后 use database_name; source test.
时光不写博客
6013 0
技术小胖子
|
Ubuntu 关系型数据库 MySQL
生产环境究竟是使用mysqldump还是xtrabackup来备份与恢复数据库？
技术小胖子
1665 0
科技小能手
|
关系型数据库 MySQL 存储
mysqldump备份失败问题小结
科技小能手
1502 0

热门文章

最新文章

  • 1
    随机生成UserAgent的python库（fake-useragent库）
  • 2
    使用zxing识别一幅包含多个二维码的图片
  • 3
    阿里云的SLB，植入cookie和重写cookie有什么区别？
  • 4
    基于Flink的实时日志分析系统实践
  • 5
    如何在服务器上跑python程序
  • 6
    电池技术多年没有较大发展，成为移动设备最大制约
  • 7
    Spread for WinRT 7新功能使用指南
  • 8
    codeforces Soldier and Number Game（dp+素数筛选）
  • 9
    九度oj-1001-Java
  • 10
    Android:随笔——对页面的View进行截图
  • 1
    分解商业周期时间序列：线性滤波器、HP滤波器、Baxter滤波器、Beveridge Nelson分解等去趋势2
    9
  • 2
    分解商业周期时间序列：线性滤波器、HP滤波器、Baxter滤波器、Beveridge Nelson分解等去趋势法1
    9
  • 3
    灰色关联分析(Grey Relation Analysis,GRA)中国经济社会发展指标
    9
  • 4
    极值分析：分块极大值BLOCK-MAXIMA、阈值超额法、广义帕累托分布GPD拟合降雨数据时间序列
    9
  • 5
    GARCH-DCC模型和DCC（MVT）建模估计
    8
  • 6
    ARIMA、ARIMAX、 动态回归和OLS 回归预测多元时间序列
    10
  • 7
    R语言预测期货波动率的实现：ARCH与HAR-RV与GARCH，ARFIMA模型比较
    10
  • 8
    spss modeler用决策树神经网络预测ST的股票
    9
  • 9
    K-means和层次聚类分析癌细胞系微阵列数据和树状图可视化比较
    8
  • 10
    matlab中使用VMD(变分模态分解)对信号去噪
    8

    • 相关课程

    更多
  • 数据库的前世今生
  • 数据库常见问题排查
  • 数据库及SQL/MySQL基础

    • 相关电子书

    更多
  • 数据库异地备份及不还原快速查询备份集最佳实践
  • 阿里云数据库案例集下载
  • 数据库2025 V3

    • 相关实验场景

    更多
  • Excel文件转存到RDS数据库
  • PolarDB的Online DDL功能验证实验
  • 快照备份与恢复
  • PolarDB MySQL无损快速的热备切换
  • Tair for Redis数据闪回：任意时间点数据恢复
  • PolarDB MySQL通过DMS恢复误操作数据
    • 下一篇
    阿里云oss简介和使用流程
    为什么选择阿里云什么是云计算全球基础设施技术领先稳定可靠安全合规分析师报告
    产品和定价全部产品免费试用产品动态产品定价价格计算器云上成本管理
    解决方案技术解决方案
    文档与社区文档开发者社区天池大赛培训与认证
    权益中心免费试用高校计划企业扶持计划推荐返现计划
    支持与服务基础服务企业增值服务迁云服务官网公告健康看板信任中心
    关注阿里云
    关注阿里云公众号或下载阿里云APP，关注云资讯，随时随地运维管控云服务
    阿里云APP阿里云微信
    联系我们：4008013260
    法律声明Cookies政策廉正举报安全举报联系我们加入我们
    阿里巴巴集团淘宝网天猫全球速卖通阿里巴巴国际交易市场1688阿里妈妈飞猪阿里云计算AliOS万网高德UC友盟优酷钉钉支付宝达摩院淘宝海外阿里云盘饿了么
    © 2009-2024 Aliyun.com 版权所有 增值电信业务经营许可证： 浙B2-20080101 域名注册服务机构许可： 浙D3-20210002 京D3-20220015
    浙公网安备 33010602009975号浙B2-20080101-4