MySQL mysqldump备份与恢复

xtrabackup：
使用 --copy-back 整机恢复（不管备份是全实例还是单库）：停机、清空 /var/lib/mysql；
使用 --export 表空间导入，只恢复单库 / 单表：不用停机、不用清空全局数据目录。

mysqldump：
日常业务恢复（单表、单库、全库补数据）：不停机、不清空 /var/lib/mysql；
仅整机磁盘彻底损坏、需要全新初始化实例时，才停机清空目录再导入。

如果是重新开始搭建主从复制结构：从库需要停库、清空数据目录！

全量备份：
mysqldump -uroot -p'Whoami77169!!!' --single-transaction --all-databases --routines --events --triggers --default-character-set=utf8mb4 --master-data=2 --result-file=/home/huying2/full_backup.sql

关于--set-gtid-purged选项：
在开启了 GTID 的模式下：
导入全新空库 → 不用加，默认 AUTO/ON ,等备份恢复时把/var/lib/mysql/*清空
导入正在跑、有数据的库 → 强制加 --set-gtid-purged=OFF

mysqldump通用必带参数（InnoDB强制标配）
--single-transaction InnoDB热备，无锁表、不阻塞线上业务（核心）
--quick 流式读取大表，降低内存占用
--routines --triggers --events 同步备份存储过程、触发器、定时事件
--default-character-set=utf8mb4 设置字符
--all-databases 整库备份，所有数据库
--master-data=2 开启binlog节点

二、手动备份

2.1手动备份（主流6种场景）

场景1：单库完整备份（结构+数据，企业最常用）
mysqldump --single-transaction --routines --triggers --events --quick employees > /data/backup/employees_full_$(date +%Y%m%d).sql
employees：你需要备份的库名
输出路径：/data/backup/

场景2：单库仅备份表结构（无数据）
mysqldump --single-transaction --routines --triggers --events --quick --no-data employees > /data/backup/employees_struct_$(date +%Y%m%d).sql

场景3：单库仅备份数据（无建表语句）
mysqldump --single-transaction --quick --no-create-info employees > /data/backup/employees_data_$(date +%Y%m%d).sql

场景4：单表、多表备份
备份单表：employees 库下的 salaries 表
mysqldump --single-transaction --quick employees salaries > /data/backup/salaries_$(date +%Y%m%d).sql

备份多表：salaries + titles 两张表
mysqldump --single-transaction --quick employees salaries titles > /data/backup/two_table_$(date +%Y%m%d).sql

场景5：多库整库备份（一台实例多个业务库）
必须加上--databases，恢复时自动创建数据库

mysqldump --single-transaction --routines --triggers --events --quick --databases employees mysql > /data/backup/multi_db_$(date +%Y%m%d).sql

2.2备份校验（防止空文件、损坏文件）
查看文件大小（正常文件不为0）
ls -lh /data/backup
查看文件头部（确认有建库/建表语句）

head -20 /data/backup/employees_full_$(date +%Y%m%d).sql

举例：head -20 /data/backup/employees_full_20260614.sql

三、恢复数据

登录MySQL

临时关闭binlog日志，开机情况下必须执行

SET sql_log_bin = 0;
exit

恢复全量备份，需要带上密码
mysql -uroot -p'Whoami77169!!!' < /home/huying/full_bak.sql;

恢复单表 （需要提前创建好同名空库employees）
mysql -uroot -p'Whoami77169!!!' employees < /data/backup/salaries_20260614.sql

恢复单库 （需要提前创建好同名空库employees）
mysql -uroot -p'Whoami77169!!!' employees < /data/backup/salaries_20260614.sql

恢复完成自动恢复日志记录

SET sql_log_bin = 1;

两种导入方式比较：
生产环境 / 大文件：用 mysql < 备份文件（流式导入、内存低、报错立即终止，防脏数据）
测试环境 / 小文件：可用 source（交互式调试，生产禁止用于大文件）