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

最新推荐文章于 2023-04-06 11:44:58 发布
最新推荐文章于 2023-04-06 11:44:58 发布
阅读量1.6k 收藏
点赞数
文章标签： 数据库
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调优；喜好钻研开源技术，熟悉多种开源工具。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28218939/viewspace-2636876/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28218939/viewspace-2636876/

congcheng1062
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
数据库备份与恢复（实战mysqldump+bin-log）
菜鸟的博客
10-18 1614
指定dump所有数据库。等价于使用--databases选定所有库。
MySQL启动不成功，一直运行
weixin_44471490的博客
09-11 773
Mysql 启动不成功，一直在执行中，最终报错： The server quit without updating PID file 执行 mysql.server restart 进行重启，报错： ERROR! MySQL server PID file could not be found! 原因分析 通常出现这种情况是由于以下几个原因： 可能是 /usr/local/mysql/data/mysql.pid 文件没有写的权限 解决方法 ：给予权限，执行 chown -R mysql:mysq
记一次磁盘写满导致mysql服务挂掉问题的排查过程
firesea的专栏
08-04 1100
问题：测试环境13.113数据库服务器mysql服务不可用 1、df -h 检查服务器磁盘占用情况，发现磁盘占满，预计是导致mysql服务不可用的原因，通过清理没用的日志文件，重新启动mysql，发现服务可以正常启动。 此时发现，空间占用还在以每小时2G的速度增加，显然是因为有某个进程在写入磁盘。下面进行空间优化及问题排查。 2、du -h --max-depth=10 /* | awk '{ if($1 ~ /M/){split($1, arr, "M")}; if(($1 ~ /G/) || (
MySQL数据的导出和导入工具:mysqldump
热门推荐
羔才生编程村
06-24 4万+
YIZ: http://www.yiz.nameyizblog: http://blog.yiz.name导出要用到MySQL的mysqldump工具，基本用法是：   shell> mysqldump [OPTIONS] database [tables]   如果你不给定任何表，整个数据库将被导出。   通过执行mysqldump --help，
大量连接导致mysql宕机解决办法
qq_39993224的博客
12-31 1392
一.记录自己解决的一次大量连接导致mysql数据库宕机情况。 解决办法： 第一步： [mysqld] 修改mysql配置文件,总共为1到6，最好不要超过3，否则会有数据永久丢失情况出现。 innodb_force_recovery = 1 第二步： 正常命令，重启服务 第三步： mysqldump -u root -p --all-databases > all-databases.sql（...
mysql——mysql数据备份与数据恢复
m0_47161295的博客
08-19 7248
备份与恢复一、数据库备份的分类1、按不同维度分类2、常见的备份方法二、MySQL完全备份1、概述2、优缺点3、数据库完全备份分类1、MySQL物理冷备份及恢复2、mysqldump备份与恢复1、mysqldump命令对单个库进行完全备份2、mysqldump命令对多个库进行完全备份3、对所有库进行完全备份4、使用mysqldump备份表的操作三、恢复数据库四、MySQL增量备份五、MySQL数据库增量恢复1、分类2、增量恢复的方法（1）一般恢复（2）基于位置的恢复（3）基于时间点的恢复 一、数据库备份的分类
使用mysqldump导入数据和mysqldump增量备份(mysqldump使用方法)
09-10
【使用mysqldump导入数据和mysqldump增量备份】是MySQL数据库管理中的关键操作，主要用于数据安全和系统恢复。以下是对这些知识点的详细解释： 1. **mysqldump基本用法**： - 最基础的命令格式是：`mysqldump -u ...
mysql mysqldump数据备份和增量备份
09-10
本文将详细介绍如何使用 `mysqldump` 工具进行全量备份和增量备份，以及如何结合shell脚本来实现定时备份。 首先，全量备份是备份数据库的所有数据，不依赖于之前的备份。在MySQL中，我们可以使用 `mysqldump` 命令...
使用mysqldump实现mysql备份
09-09
mysqldump客户端可用来转储数据库或搜集数据库进行备份或将数据转移到另一个SQL服务器(不一定是一个MySQL服务器)。今天我们就来详细探讨下mysqldump的使用方法
mysql使用mysqldump备份和还原
10-27
本篇文章将详细讲解如何使用`mysqldump`进行数据库的备份与还原，以及与之相关的`qt`编程中如何集成`mysqldump`功能。 首先，`mysqldump`备份数据库的基本命令格式如下： ```shell mysqldump [选项] [数据库名] > ...
mysql备份脚本 mysqldump使用方法详解
09-09
一个典型的mysqldump备份脚本会包含以下部分： 1. **设置环境变量**：在脚本开头，通过`source`命令加载系统和用户环境变量，确保脚本运行时能正确识别MySQL的安装路径和其他配置。 2. **定义备份参数**：包括...
dump会卡住mysql吗_mysqldump不得不说的秘密
weixin_31755479的博客
01-19 744
Many people use mysqldump –single-transaction to get consistent backup for their Innodb tables without making database read only. In most cases it works, but did you know there are some cases when yo...
从库mysqldump会导致复制中断
qq_40907977的博客
08-24 508
背景 环境 数据库版本：5.6.24-log 主从架构：一主两从搭配mha 操作步骤 开发要求truncate一张表，在删除之前需要备份下数据； 在从库上使用mysqldump命令做逻辑备份，备份sql： mysqldump -uXXX -p osdc osdc_XXX > /tmp/osdc_info.sql； 这台从库与主库的同步出现中断，报错为： Slave_IO_Running: Yes Slave_SQL_Running: No Last_Errno: 1756 Last_Er
mysql查询表挂死_通过mysql的表分区解决数据库挂死
weixin_34007013的博客
01-19 295
来说下我们这个应用场景吧：我们这个监控系统目前每天存储的历史监控数据大概有几百万条(具体跟监控频率有关系)，用户可以自定义历史数据保存时间，假如用户设置的时间为12天，那么12天后也就是第13天，我们就要删掉最早一天的数据，以此类推。最近项目部署后，刚好到第12天，项目就挂掉了，排查原因发现是mysql数据库被一句sql语句给搞死了，看了下程序里面对历史数据删除的代码如下：public void ...
使用Mysql自带的Mysqldump进行备份
hanzi552200的博客
04-06 396
我是Docker安装了MySqL5.7，然后宿主机是没安装的，本来是想在容器里面备份好以后放在映射的路径下，但是想想太麻烦还是宿主机安装一个相同版本的Mysql，然后不要启动就好了。Mysqldump是安装mysql的时候自带的，但是只能逻辑备份，还是存在着备份的时候会被插入新的数据，貌似会自动锁表？以下是我备份的脚本，其中账号密码我放在了/etc/my.cnf里面了。注意，如果要备份所有数据库，则该账号需要具备所有数据库的权限。注意，这里是判断要清除的文件是根据修改时间，而不是文件名。
故障分析 | MySQL5.7 使用 mysqldump 重要避坑事项
ActionTech的博客
11-12 744
作者：王向 爱可生 DBA 团队成员，负责公司 DMP 产品的运维和客户 MySQL 问题的处理。擅长数据库故障处理。对数据库技术和 python 有着浓厚的兴趣。 本文来源：原创投稿 *爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。 背景 笔者在一次处理客户 MySQL 问题时遇到客户的 MySQL 的 sys 库不能用了并抛出一下错误： mysql> SELECT * FROM sys.processlist; ERROR 1356 (HY000): View.
MySQL数据备份
zt_96123的博客
07-22 384
1 数据库备份 1.1为什么需要备份数据？ 在生产环境中我们数据库可能会遭遇各种各样的不测从而导致数据丢失, 大概分为以下几种： 硬件故障 软件故障 自然灾害 黑客攻击 误操作 (占比最大) 所以, 为了在数据丢失之后能够恢复数据, 我们就需要定期的备份数据, 备份数据的策略要根据不同的应用场景进行定制, 大致有几个参考数值, 我们可以根据这些数值从而定制符合特定环境中的数据备份策略： 能够...
mysql使用mysqldump备份提示密码不安全
weixin_45387943的博客
03-30 768
这是高版本不允许直接在对话框输入密码。只要在提示框里面输入就可以了。
MySQL--2--mysqldump参数详解
weixin_34242819的博客
12-18 1044
Mysqldump参数大全（参数来源于mysql5.5.19源码） 参数 参数说明 --all-databases , -A 导出全部数据库。 mysqldump -uroot -p --all-databases --all-tablespaces , -Y 导出全部表空间。 mysqldump -uroot -p --all-databases...
java使用mysqldump备份
最新发布
09-28
java使用mysqldump命令可以备份MySQL数据库。mysqldump是一个命令行工具，可以用于导出MySQL数据库的结构和数据。以下是java使用mysqldump备份的步骤： 1. 首先，确保你已经安装了MySQL数据库和mysqldump工具。 2. 在Java代码中，使用ProcessBuilder类创建一个进程来执行mysqldump命令。设置命令行参数，包括数据库的用户名、密码、主机名、数据库名称以及备份文件的路径。 3. 通过调用start()方法来启动进程，并等待进程完成。 4. 检查备份文件是否成功生成。 以下是一个示例代码： ``` import java.io.File; import java.io.IOException; public class DatabaseBackup { public static void main(String[] args) { String username = "your_username"; String password = "your_password"; String hostname = "localhost"; String databaseName = "your_database"; String backupPath = "path_to_backup_file.sql"; String command = String.format("mysqldump -u %s -p%s -h %s %s -r %s", username, password, hostname, databaseName, backupPath); try { ProcessBuilder processBuilder = new ProcessBuilder(command.split(" ")); Process process = processBuilder.start(); int exitCode = process.waitFor(); if (exitCode == 0) { System.out.println("Database backup successful!"); } else { System.out.println("Database backup failed!"); } } catch (IOException | InterruptedException e) { e.printStackTrace(); } } } ```

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值