MySQL备份恢复全攻略：mysqldump与xtrabackup实战（DBA救命指南）

至今还记得2018年的那次事故开发误删用户全量数据当时只有每天凌晨1次的全量备份事故发生在下午5点我们硬生生靠binlog恢复了4个小时业务停摆半天损失惨重。从那以后我们彻底重构了备份策略——从“裸奔式”每日全备升级为「全量备份增量备份binlog归档」的黄金组合RTO恢复时间目标从4小时压缩到30分钟RPO恢复点目标从24小时缩减到5分钟再也不用为数据丢失夜不能寐。今天就把这份沉淀了多年的MySQL备份恢复实战攻略毫无保留分享给大家——涵盖mysqldump和xtrabackup两大核心工具从基础用法到生产实战从故障排查到最佳实践新手能上手老手能避坑。一、先搞懂两大备份工具该选谁MySQL备份工具千千万但生产环境最常用、最靠谱的就两个mysqldump和Percona XtraBackup。它们就像两把“双刃剑”各有专攻选对了能省90%的麻烦。核心结论小型数据库选mysqldump简单省心大型数据库选XtraBackup高效低影响跨版本迁移必用mysqldump。1.1 mysqldumpMySQL官方“轻量选手”mysqldump是MySQL自带的逻辑备份工具说白了就是“把数据库里的数据导出成SQL语句”就像把一本书的内容逐字抄下来可读性强操作简单。特性详细说明备份类型逻辑备份导出SQL语句肉眼可看锁机制默认锁表可用--single-transaction实现InnoDB一致性读不锁表速度较慢要逐行查询、生成SQL大数据量会“卡壳”适用场景小型数据库50GB、跨版本迁移、表级恢复优点简单、可读、跨平台、跨版本不用额外安装缺点大数据量时速度慢恢复时要执行大量SQL耗时久1.2 Percona XtraBackup大型库“重型利器”XtraBackup是Percona公司推出的物理热备份工具直接复制数据库的数据文件就像给硬盘做“镜像”速度快、对业务影响极小是大型数据库的首选。特性详细说明备份类型物理备份直接复制数据文件二进制格式锁机制几乎无锁InnoDB热备备份时不影响业务读写速度极快直接复制文件大数据量优势明显适用场景大型数据库50GB、生产环境低影响备份优点快速、低影响、支持增量备份恢复速度快缺点只支持同版本恢复配置比mysqldump复杂需要额外安装1.3 场景对应表直接抄作业不用纠结对照下面的场景选工具准没错应用场景推荐工具备份策略开发测试环境mysqldump每日全备简单省事小型生产50GBmysqldump每日全备 binlog归档中型生产50-500GBXtraBackup每周全备 每日增量大型生产500GBXtraBackup每周全备 每日增量 流式备份跨版本迁移mysqldump逻辑导出导入兼容性强表级恢复mysqldump单表导出精准恢复不影响其他表时间点恢复PITRXtraBackup binlog全备 binlog应用恢复到任意时间点1.4 环境要求必看避免踩坑环境不匹配备份必失败提前核对以下版本和配置组件版本要求关键说明MySQL Server8.0.35 或 8.4 LTS稳定版优先避免使用测试版Percona XtraBackup8.0.35版本必须与MySQL匹配比如MySQL 8.0.35XtraBackup也用8.0.35操作系统Rocky 9 / Ubuntu 24.04生产环境推荐兼容性最好磁盘空间数据量的2-3倍预留备份、压缩、恢复的空间避免空间不足导致备份失败qpress最新版XtraBackup压缩备份必需工具二、实战操作从准备到备份一步到位铺垫再多不如动手操作。这部分是核心每一步都有详细命令直接复制粘贴就能用记得替换自己的密码和目录。2.1 准备工作3步搞定避免后续踩坑2.1.1 安装备份工具分两种操作系统按需选择✅ Rocky Linux 9 / CentOS Stream 9# 安装Percona仓库XtraBackup需要 sudo dnf install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm sudo percona-release setup pxb-80 # 安装XtraBackup sudo dnf install -y percona-xtrabackup-80 # 安装压缩工具备份压缩必需 sudo dnf install -y qpress lz4 # 验证安装出现版本号即成功 xtrabackup --version # 正常输出xtrabackup version 8.0.35-30 based on MySQL server 8.0.35✅ Ubuntu 24.04sudo apt-get update sudo apt-get install -y wget gnupg2 lsb-release wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb sudo dpkg -i percona-release_latest.generic_all.deb sudo percona-release setup pxb-80 sudo apt-get install -y percona-xtrabackup-80 qpress2.1.2 创建备份专用用户不要用root用户备份创建专用备份用户分配最小权限更安全-- 登录MySQL执行以下SQL CREATE USER backuplocalhost IDENTIFIED BY BackupPass2024; -- 密码替换成自己的 -- mysqldump所需权限复制粘贴即可 GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT ON *.* TO backuplocalhost; -- XtraBackup所需权限复制粘贴即可 GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO backuplocalhost; GRANT BACKUP_ADMIN ON *.* TO backuplocalhost; -- MySQL 8.0 必需 GRANT SELECT ON performance_schema.log_status TO backuplocalhost; GRANT SELECT ON performance_schema.keyring_component_status TO backuplocalhost; FLUSH PRIVILEGES; -- 刷新权限立即生效2.1.3 规划备份目录目录混乱会导致后续恢复找不到文件建议按以下结构创建一目了然# 创建备份目录结构一次性执行 sudo mkdir -p /backup/mysql/{full,incremental,binlog,scripts,logs} sudo chown -R mysql:mysql /backup/mysql # 授权mysql用户避免权限不足 sudo chmod 750 /backup/mysql # 限制访问权限更安全 # 目录说明记好后续用得到 # /backup/mysql/full - 存放全量备份 # /backup/mysql/incremental - 存放增量备份 # /backup/mysql/binlog - 存放binlog归档 # /backup/mysql/scripts - 存放备份脚本 # /backup/mysql/logs - 存放备份日志 # 重要提醒备份目录建议用独立磁盘或NFS挂载避免和数据目录在同一磁盘防止磁盘损坏一起丢2.2 核心操作mysqldump实战用法适合小型数据库操作简单以下是最常用的几种场景覆盖90%的需求✅ 备份单个数据库mysqldump -u backup -pBackupPass2024 \ --single-transaction \ # InnoDB不锁表关键参数 --routines \ # 备份存储过程和函数 --triggers \ # 备份触发器 --events \ # 备份事件调度器 mydb /backup/mysql/full/mydb_$(date %Y%m%d).sql # 备份到指定目录带日期✅ 备份多个数据库mysqldump -u backup -pBackupPass2024 \ --single-transaction \ --routines \ --triggers \ --databases db1 db2 db3 /backup/mysql/full/multi_db_$(date %Y%m%d).sql✅ 备份所有数据库最常用mysqldump -u backup -pBackupPass2024 \ --single-transaction \ --routines \ --triggers \ --events \ --all-databases /backup/mysql/full/all_db_$(date %Y%m%d).sql✅ 备份单个表精准恢复不影响其他表mysqldump -u backup -pBackupPass2024 \ --single-transaction \ mydb users /backup/mysql/full/mydb_users_$(date %Y%m%d).sql # mydb是库名users是表名✅ 生产环境推荐参数组合带压缩更省空间这是我平时用的配置兼顾安全和效率直接复制用mysqldump -u backup -pBackupPass2024 \ --single-transaction \ # InnoDB一致性读不锁表必需 --source-data2 \ # 记录binlog位置注释形式方便恢复 --routines \ # 包含存储过程和函数 --triggers \ # 包含触发器 --events \ # 包含事件调度器 --set-gtid-purgedAUTO \ # GTID自动处理MySQL 8.0 推荐 --hex-blob \ # 二进制数据用十六进制避免乱码 --quick \ # 逐行读取减少内存占用 --max-allowed-packet512M \ # 支持大数据包避免导出失败 --default-character-setutf8mb4 \ # 字符集避免乱码 --all-databases \ | gzip /backup/mysql/full/all_db_$(date %Y%m%d).sql.gz # 压缩备份省空间注意MySQL 8.0.26 版本--master-data 参数已改为 --source-data别用错了2.3 核心操作XtraBackup实战用法适合大型数据库热备份、速度快重点掌握全量和增量备份生产环境最常用。✅ 全量备份基础增量备份的前提# 基础全量备份 xtrabackup --backup \ --userbackup \ --passwordBackupPass2024 \ --target-dir/backup/mysql/full/$(date %Y%m%d) # 备份目录带日期方便区分 # 备份并压缩推荐省空间 xtrabackup --backup \ --userbackup \ --passwordBackupPass2024 \ --target-dir/backup/mysql/full/$(date %Y%m%d) \ --compress \ # 开启压缩 --compress-threads4 # 4线程压缩提升速度 # 流式备份到远程大型库推荐避免本地磁盘不够 xtrabackup --backup \ --userbackup \ --passwordBackupPass2024 \ --streamxbstream \ # 流式输出 --compress \ | ssh backupremote-server cat /backup/mysql/full_$(date %Y%m%d).xbstream # 传输到远程服务器✅ 增量备份省空间适合大型库增量备份只备份上次备份后变化的数据比全量备份小很多步骤如下必须基于全量备份# 第一步创建全量备份基准备份只做一次 xtrabackup --backup \ --userbackup \ --passwordBackupPass2024 \ --target-dir/backup/mysql/full/base # 第二步创建第一次增量备份基于全量备份 xtrabackup --backup \ --userbackup \ --passwordBackupPass2024 \ --target-dir/backup/mysql/incremental/inc1 \ --incremental-basedir/backup/mysql/full/base # 指定基准备份目录 # 第三步创建第二次增量备份基于第一次增量 xtrabackup --backup \ --userbackup \ --passwordBackupPass2024 \ --target-dir/backup/mysql/incremental/inc2 \ --incremental-basedir/backup/mysql/incremental/inc1 # 指定上一次增量备份目录✅ 备份准备与恢复关键备份了不能恢复等于白做XtraBackup备份后不能直接恢复必须先“准备”应用redo log确保数据一致性# 1. 准备全量备份基准备份 xtrabackup --prepare \ --apply-log-only \ # 只应用redo log不做其他操作增量备份必需 --target-dir/backup/mysql/full/base # 2. 应用第一次增量备份到基准备份 xtrabackup --prepare \ --apply-log-only \ --target-dir/backup/mysql/full/base \ --incremental-dir/backup/mysql/incremental/inc1 # 3. 应用第二次增量备份最后一次增量不用加--apply-log-only xtrabackup --prepare \ --target-dir/backup/mysql/full/base \ --incremental-dir/backup/mysql/incremental/inc2 # 4. 最终准备确保数据完整 xtrabackup --prepare \ --target-dir/backup/mysql/full/base # 恢复备份停止MySQL后执行 xtrabackup --copy-back \ --target-dir/backup/mysql/full/base # 恢复后设置权限必须否则MySQL启动失败 chown -R mysql:mysql /data/mysql/data # /data/mysql/data是你的MySQL数据目录三、生产实战脚本案例直接落地手动执行备份太麻烦而且容易忘生产环境一定要用脚本定时任务实现自动化备份。另外附上3个高频故障恢复案例帮你应对突发情况。3.1 生产环境备份脚本直接复制可用✅ mysqldump备份脚本小型数据库文件路径/backup/mysql/scripts/mysqldump_backup.sh记得替换配置中的密码和目录#!/bin/bash # 文件/backup/mysql/scripts/mysqldump_backup.sh # 功能生产环境mysqldump自动化备份脚本 # 用法./mysqldump_backup.sh [full|single] [database_name] set -e # 执行出错立即退出 # 配置按需修改 MYSQL_USERbackup MYSQL_PASSBackupPass2024 # 替换成你的备份用户密码 MYSQL_HOSTlocalhost BACKUP_DIR/backup/mysql/full LOG_DIR/backup/mysql/logs RETENTION_DAYS7 # 备份保留7天自动清理 DATE$(date %Y%m%d_%H%M%S) LOG_FILE${LOG_DIR}/mysqldump_${DATE}.log # 备份元数据库记录备份信息可选 META_DBbackup_meta RECORD_BACKUPtrue # 初始化目录 mkdir -p ${BACKUP_DIR} ${LOG_DIR} # 日志函数 log() { echo [$(date %Y-%m-%d %H:%M:%S)] $1 | tee -a ${LOG_FILE} } # 记录备份结果到元数据库 record_backup() { local status$1 local error_msg$2 local end_time$(date %Y-%m-%d %H:%M:%S) local backup_size$(stat -c%s ${BACKUP_FILE} 2/dev/null || echo 0) local compressed_size$(stat -c%s ${BACKUP_FILE}.gz 2/dev/null || echo 0) if [ $RECORD_BACKUP true ]; then mysql -u${MYSQL_USER} -p${MYSQL_PASS} ${META_DB} EOF UPDATE backup_history SET end_time ${end_time}, duration_seconds TIMESTAMPDIFF(SECOND, start_time, ${end_time}), status ${status}, backup_size_bytes ${backup_size}, compressed_size_bytes ${compressed_size}, error_message ${error_msg} WHERE id ${BACKUP_ID}; EOF fi } # 全量备份 do_full_backup() { log Starting full backup... BACKUP_FILE${BACKUP_DIR}/all_databases_${DATE}.sql # 获取binlog位置用于时间点恢复 BINLOG_INFO$(mysql -u${MYSQL_USER} -p${MYSQL_PASS} -N -e SHOW MASTER STATUS) BINLOG_FILE$(echo $BINLOG_INFO | awk {print $1}) BINLOG_POS$(echo $BINLOG_INFO | awk {print $2}) GTID_EXECUTED$(mysql -u${MYSQL_USER} -p${MYSQL_PASS} -N -e SELECT global.gtid_executed | tr \n ) # 记录备份开始 if [ $RECORD_BACKUP true ]; then BACKUP_ID$(mysql -u${MYSQL_USER} -p${MYSQL_PASS} ${META_DB} -N -e INSERT INTO backup_history (backup_type, backup_tool, backup_path, start_time, binlog_file, binlog_position, gtid_executed) VALUES (full, mysqldump, ${BACKUP_FILE}, NOW(), ${BINLOG_FILE}, ${BINLOG_POS}, ${GTID_EXECUTED}); SELECT LAST_INSERT_ID(); ) fi # 执行备份 log Backup file: ${BACKUP_FILE} log Binlog: ${BINLOG_FILE}:${BINLOG_POS} mysqldump -u${MYSQL_USER} -p${MYSQL_PASS} -h${MYSQL_HOST} \ --single-transaction \ --source-data2 \ --routines \ --triggers \ --events \ --set-gtid-purgedAUTO \ --hex-blob \ --quick \ --max-allowed-packet512M \ --default-character-setutf8mb4 \ --all-databases ${BACKUP_FILE} 2${LOG_FILE} if [ $? -eq 0 ]; then log Backup completed successfully # 压缩备份 log Compressing backup... gzip ${BACKUP_FILE} log Compressed size: $(du -h ${BACKUP_FILE}.gz | cut -f1) record_backup success else log Backup FAILED! record_backup failed mysqldump failed exit 1 fi } # 单库备份 do_single_backup() { local DB_NAME$1 log Starting single database backup: ${DB_NAME} BACKUP_FILE${BACKUP_DIR}/${DB_NAME}_${DATE}.sql mysqldump -u${MYSQL_USER} -p${MYSQL_PASS} -h${MYSQL_HOST} \ --single-transaction \ --source-data2 \ --routines \ --triggers \ --set-gtid-purgedAUTO \ --hex-blob \ --quick \ ${DB_NAME} ${BACKUP_FILE} 2${LOG_FILE}