MySQL 逻辑 & 物理备份全攻略：指令大全 + 实战指南

原创已于 2025-03-13 17:37:46 修改 · 1.2k 阅读

22 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

收录于

mysql

于 2025-03-13 17:12:27 首次发布

备份导入(恢复)注意事项

(1)参数检查

autocommit:必须开启,不然会导致数据库hang住
wait_timeout \ interactive_timeout:建议调大,设置过小，且导入时间长，会导致还没导入完，会话超时断开连接，导致任务失败。
set session wait_timeout=28800; \ set session interactive_timeout=28800; max_allowed_packet = 128M #防止包过大而失败

(2)检查 SQL文件中所要 DROP 的表是否是自己预期内的

less xxxxx.sql | grep -E "^DROP TABLE IF EXISTS

(3)使用PV工具监控文件导入过程，精准估算剩余时间及完成时间

#参数说明：

#-W：在需要密码输入时有用，可等待密码输出完成，再开启监控进度条
#-L:限流，将传输限制在每秒最大字节的范围内（大小可自定义，单位可变）

一.mysqldump

非一致性备份（生产不可用）

mysqldump -uroot -p -P3306 test > /data/backup/user_`date +%Y%d%m`_0.s

原理解析

连接数据库建立 connect
获取当前 GTID 信息
查看需要备份的 DB 和 table
对表加 read lock
show create table 备份表结构和数据（循环至整个 DB 表备份结束）
释放 read lock

缺陷:

备份数据不一致
备份锁表时间时长和备份内容成正比
适合非事务引擎的一致性备份

⼀致性备份

mysqldump -uroot -p -P3306 --master-data=2 --single-transaction -B test> /data/backup/test_`date +%Y%d%m`.sql

原理解析

连接数据库建立 connect
Flush table（无 --master-data 无此步骤）：
检查是否能进行加锁一致性备份，减少 FTWRL 锁表时间。有则等之，无则进行加 FTWRL（确保如果 update 事务执行后立马加锁）。关闭所有打开的表，强制关闭所有正在使用的表，并且将所有更新的数据刷新到磁盘，这个时间不会锁表。
FLUSH TABLES WITH READ LOCK --> 如果不加参数 --master-data 就无此步骤
执行 flush tables 操作，并且加一个全局读锁。
设置 RR 事务隔离级别，准备快照一致性读。
START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */：
开启一个一致性快照的事务，--single-transaction 决定。

备份命令大全

备份

1. 生产单库备份命令

mysqldump -uroot -P3306 --default-character-set=utf8mb4 --single-transaction --master-data=2 --triggers --events --routines -B test --set-gtid-purged=off >/data/backup/mysql3306_`date +%Y%d%m `-test.sql

2.整实例备份（不推荐使用）

mysqldump -uroot -P3306 --default-character-set=utf8mb4 --single-transaction --set-gtid-purged=OFF  --all-databases   --master-data=2 --triggers --events --routines >/data/backup/mysql3306_`date +%Y%d%m`-backup_db01.sql

3.备份单表

mysqldump -uroot  -P3306 --default-character-set=utf8mb4 --single-transaction --master-data=2 --triggers --events --routines -B test --tables test1   --set-gtid-purged=off >/data/backup/test1.sql

4.按条件备份数据库

mysqldump -uroot -P3306 --default-character-set=utf8mb4 --single-transaction --master-data=2 --triggers --events --routines -B test --tables test1 --set-gtid-purged=off --where='id=1' >/data/backup/test1.sql

5.只导出表结构

mysqldump -uroot  -P3306  -d --single-transaction  --set-gtid-purged=OFF  --master-data=2  --triggers --events --routines  test test1 > /data/backup/test.sql

6.只备份数据

mysqldump -uroot -P3306 --default-character-set=utf8mb4 --single-transaction --master-data=2 --flush-logs --set-gtid-purged=off --hex-blob --no-create-info test test1 > /data/backup/test1_only_data.sql

恢复

1.命令行文件导入

mysql -uroot -p -P3306  test test1 < /data/backup/test1_only_data.sql

2. source导入

source /chj/class/backup/t1.sql;

监控恢复进度

pv -W -L 10K /data/backup/test1.sql| mysql -uroot -P3306 test

#-W：在需要密码输入时有用，可等待密码输出完成，再开启监控进度条
#-L:限流，将传输限制在每秒最大字节的范围内（大小可自定义，单位可变）

二.mydumper&myloader

mysqldump无法并行，这点与Oracle的expdp相比，存在一定的劣势，但是开源的mydumper是一个很好的补充。

原理解析

主线程 FLUSH TABLES WITH READ LOCK，阻止 DML 语句写入，保证数据的一致性
读取当前时间点的二进制日志文件名和日志写入的gtid并记录在 metadata 文件中。
n个（线程数能够指定，默认是 4）dump线程开启并启用一致的事务 START TRANSACTION WITH CONSISTENT SNAPSHOT；
dump non-InnoDB tables，首先导出非事务引擎的表（如果指定--trx-consistency-only则忽略）
主线程 UNLOCK TABLES 非事务引擎备份完成后，释放全局只读锁
dump InnoDB tables，事务备份 InnoDB 表
事务结束

1.单库单表备份

mydumper   -P 3306  -u root -t 8 -F 64 --trx-consistency-only -v 3  -G -E -R -B test -T test1  -L /data/backup/test1.log  -o /data/backup/test1

2.数据导入

myloader -P 3306  -u root   -t 8 -L /data/backup/loader.log  -d /data/backup/test1

三.mysqlpump

MySQL5.7之后多的一个备份工具：mysqlpump。

例子

mysqlpump --exclude-databases=mysql,sys #备份过滤mysql和sys数据库

mysqlpump --exclude-tables=test1,test2 #备份过滤所有数据库中test1,test2表

mysqlpump -B test --exclude-tables=test1,test2 #备份过滤test库中的test1、test2表

四.select..into outfile,load data & mysqlimport

1. 高效读取文本数据到制定库表中

2. 适合异构迁移(可以灵活制定数据导入) 场景:- 大数据结果集导入.- 测试造数

3.工具分类- select .. into outfile导出工具- load data & mysqlimport导入工具

1.SELECT ... INTO OUTFILE

2. 使用 `LOAD DATA INFILE` 导入数据

3. 使用 `mysqlimport` 导入数据

五.物理备份Xtrabackup

主要特性：热备，增量备份MySQL，流压缩传输其他服务器，在线移动表，轻易创建主从，备份MySQL服务不会增大服务器负载

备份方式

全量备份:快速恢复数据,占据空间过多,备份时间长
增量备份:基于上次全量或者增量,对数据库修改进行备份,省空间,省备份速度,但是恢复慢,且复杂
日志备份:针对Mysql二进制日志,结合使用,使数据库恢复到任意位置

全量备份原理

首先会启动一个 xtrabackup_log 后台检测的进程，实时检测 MySQL redo 的变化，一旦发现 redo 有新的日志写入，立刻将日志写入到日志文件 xtrabackup_log 中。
复制 InnoDB 的数据文件和系统表空间文件 ibdata1 到对应的以默认时间戳为备份目录的地方。
复制结束后，执行 flush table with read lock 操作。
复制 .frm .myd .myi 文件。
并且在这一时刻获取 binary log 的位置。
将表进行解锁 unlock tables。
停止 xtrabackup_log 进程。

增量备份原理

增量备份主要是通过拷贝 InnoDB 中有变更的页（指的是 LSN 大于 xtrabackup_checkpoints 中的 LSN 号）。增量备份是基于全备的，第一次增量备份的数据是基于上一次全备，之后的每一次增量都是基于上一次的增量，最终达到一致性的增备。增备的过程中，和全备很类似，区别在于第二步。

安装Xtrabackup

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm

yum localinstall percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm

示例

普通磁盘全备：

xtrabackup --defaults-file=/data/mysql3306/etc/my.cnf  --port=3306 --user root  --backup-lock-timeout=1  --parallel=8 --use-memory=2G --backup --target-dir=/data/backup/xtrabackup/