如何使用mysqldump备份

问题描述

当我使用mysqldump备份单表的一些数据时，发现没有备份数据。我们来分析一下这个奇怪的现象。

问题复现及分析

#表结构信息mysql> show create table test.t1;+-------+-- - ------------------------------------------------- - ------------------------------------------------- - ------------------------------------------------- - ------------------------------------------------- - ----------------------------------------------------+|表|创建表 |+ -------+---------------------------------------- ------- ------------------------------------------- ------- ------------------------------------------- ---------------------------------------------------------------------- ----------------------------------- --------------- --+| t1 |创建表 `t1` (`id` int(11) NOT NULL AUTO_INCRMENT,`time` 时间戳不为空默认 CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,主键 (`id`),KEY `idx_t` (`time`)) ENGINE=InnoDB AUTO_INCRMENT=16 默认字符集=utf8 |+--------+-------- --------------------------- ----------------------- --------------------------- ----------------------- --------------------------- ----------------------- --------------------------- ----------------------- --------------------------- ----------+1 row in set (0.00 sec)#表数据信息 mysql> select * from test.t1;+----+------------ ---------+|编号 |时间 |+----+-- ------------------+| 1 | 2023-12-10 22:15:39 || 2 | 2023-12-10 22:15:47 || 3 | 2023-12-10 22:15:50 || 4 | 2023-12-10 22:15:56 || 5 | 2023-12-10 22:15:57 || 6|| 2023-12-10 22:15:58 || 7 | 2023-12-10 22:15:58 || 8 | 2023-12-10 22:16:06 || 9 | 2023-12-10 22 :16:06 || 10 | 10 2023-12-10 22:16:07 || 11 | 11 2023年-12-10 22:16:08 || 12 | 12 2023-12-10 22:16:13 || 13 | 2023-12-10 22:16:13 || 14 | 14 2023-12-10 22:16:14 || 15 | 15 2023-12-10 22:16:15 |+----+----- ----------------+15 行集合（0.00 秒）

使用 mysqldump --where 选项备份 t1 表中的一些数据。

#使用mysqldump根据时间列条件备份mysqldump -uroot -p123456 --default-character-set=utf8 -q --master-data=2 --single-transaction --databases test --tables t1 --where "time>'2023-12-10 22:16:08'">beifen.sql#从备份文件中可以看出，备份结果中没有数据。 ...锁定表 `t1` 写入;/*!40000 更改表 `t1` 禁用键 */;/*!40000 更改表 `t1` 启用键 */;解锁表;...

在MySQL中使用同样的条件查询，没有异常，可以找到数据。

[root@master ~]# mysql -uroot -p123456 -e "select * from test.t1 where time>'2023-12-10 22:16:08'"mysql: [警告] 在命令行界面上使用密码可能不安全。+----+---------------------+|编号 |时间 |+- ---+--------------------+| 12 | 12 2023-12-10 22:16:13 || 13 | 2023-12-10 22:16:13 || 14 | 14 2023-12-10 22:16:14 || 15 | 15 2023-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#有数据，但是仔细对比可以发现时间倒退了八个小时。锁定表 `t1` 写入;/*!40000 更改表 `t1` 禁用键 */;插入 `t1` 值 (1,'2023-12-10 14:15:39'),(2,'2023- 12-10 14:15:47'),(3,'2023-12-10 14:15:50'),(4,'2023-12-10 14:15:56'),(5,'2023 -12-10 14:15:57'),(6,'2023-12-10 14:15:58'),(7,'2023-12-10 14:15:58'),(8,' 2023-12-10 14:16:06'),(9,'2023-12-10 14:16:06'),(10,'2023-12-10 14:16:07'),(11, '2023-12-10 14:16:08'),(12,'2023-12-10 14:16:13'),(13,'2023-12-10 14:16:13'),(14 ,'2023-12-10 14:16:14'),(15,'2023-12-10 14:16:15');/*!40000 ALTER TABLE `t1` ENABLE KEYS*/;UNLOCK TABLES;#查看mysqldump备份文件的头信息。 mysqldump 使用中间时区。 .../*!40103 SET TIME_ZONE='+00:00' */;...#查看MySQL和系统时区。 mysql> 显示像 '%time%' 这样的变量;+--------------------------------+---- ----------------+|变量名 |值 |+---------------------------- -----+------------- ------+| binlog_max_flush_queue_time | binlog_max_flush_queue_time | 0 ||连接超时 | 10||日期时间格式 | %Y-%m-%d %H: %i:%s ||默认密码生命周期 | 0 ||延迟插入超时| 300||时间戳的显式默认值 |关闭 ||冲洗时间| 0 ||有_语句_超时|是 || innodb_flush_log_at_timeout | innodb_flush_log_at_timeout | innodb_flush_log_at_timeout 1 || innodb_lock_wait_timeout | innodb_lock_wait_timeout | innodb_lock_wait_timeout | innodb_lock_wait_timeout 50|| innodb_old_blocks_time | innodb_old_blocks_time | 1000 || innodb_rollback_on_timeout | innodb_rollback_on_timeout | innodb_rollback_on_timeout关闭||交互超时 | 28800 || lc_时间_名称 | zh_CN ||锁等待超时| 31536000 ||日志时间戳 |世界标准时间 ||长查询时间 | 10.000000 ||最大执行时间 | 0 ||网络读取超时 | 30||网络写入超时 |60|| rpl_semi_sync_master_timeout | rpl_semi_sync_master_timeout 10000 || rpl_stop_slave_timeout | rpl_stop_slave_timeout | rpl_stop_slave_timeout 31536000 ||从属网络超时 | 60||慢启动时间 | 2 ||系统时区 |国家标准时间||时间格式 | %H:%i:%s ||时区 | +08:00 ||时间戳| 1544775697.554299 ||等待超时 | 28800 |+-- ----------------------------------+---------- -------- -+29 rows in set (0.01 sec)[root@master ~]# date -RWed, 12 Dec 2023 16:00:34 +0800# 模拟数据恢复 mysql> drop table test.t1 ;mysql -uroot -p123456 从 t1 选择 *;+----+----------------------+|编号 |时间 |+----+--------------------+| 1 | 2023-12-10 22:15:39 || 2 | 2023-12-10 22:15:47 || 3 | 2023-12-10 22:15:50 || 4 | 2023-12-10 22:15:56 | | 5 | 2023-12-10 22:15:57 || 6 | 2023-12-10 22:15:58 || 7 | 2023-12-10 22:15:58 || 8 | 2023-12-10 22:16:06 || 9 | 2023-12-10 22:16:06 || 10 | 10 2023-12-10 22:16:07 || 11 | 11 2023-12-10 22:16:08 || 12 | 12 2023-12-10 22:16:13 || 13 | 2023-12-10 22:16:13 || 14 | 14 2023-12-10 22:16:14 || 15 | 15 2023-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='2023-12-10 22:16:08' " -uroot -p123456 --skip-tz-utc>beifen.sql#数据完全正确。文件头中没有时区转换，因此数据恢复正常。锁定表 `t1` 写入;/*!40000 ALTER TABLE `t1` 禁用键 */;INSERT INTO `t1` VALUES (11,'2023-12-10 22:16:08');/*!40000 ALTER TABLE `t1` 启用键 */;解锁表;

这篇关于《如何使用mysqldump备份》的文章就分享到这里了，希望以上内容能够对大家有一定的帮助，让大家能够学到更多的知识，如果您觉得文章不错，欢迎分享哦让更多人看到。