MySQL 数据库备份和恢复全指南

MySQL 是一款常用的开源数据库系统，在日常运维中，数据备份和恢复是系统管理的重要一环。本文将细致介绍 MySQL 两大备份方案—— mysqldump 和 XtraBackup，包括备份方式、恢复步骤、定时脚本、远程备份和常见问题处理方案。

---

一、mysqldump 备份和恢复

1.1 基础备份

mysqldump -h 服务器IP -P 3306 -u 用户名 -p 数据库名 > backup.sql

1.2 无锁 InnoDB 备份

mysqldump --single-transaction --quick --lock-tables=false -u root -p your_db > backup.sql

• --single-transaction：适用 InnoDB，无需锁表
• --quick：较低内存占用

1.3 跟踪输出进度

• 增加 --verbose 显示备份进度
• tail -f backup.sql 实时观察
• 配合 pv 显示进度条

mysqldump -u root -p your_db | pv > backup.sql

1.4 分表备份

for table in $(mysql -u root -p -D your_db -e "SHOW TABLES;" -s --skip-column-names); domysqldump -u root -p your_db $table > ${table}.sql
done

1.5 恢复 SQL 备份

恢复單个数据库

mysql -u root -p your_db < backup.sql

如果没有数据库，需先创建：

CREATE DATABASE your_db;

恢复全部数据库

mysql -u root -p < all_backup.sql

提示

• 如果 SQL 包含 CREATE DATABASE 和 USE，无需预创建数据库
• 恢复时可关闭 foreign key 检查

SET foreign_key_checks = 0;

1.6 远程 mysqldump

mysqldump -h192.168.1.100 -P3306 -u root -p your_db > remote_backup.sql

前提

• 配置 bind-address=0.0.0.0
• 应用 root@% 访问权限
• 防火墙打开 3306 端口

---

二、XtraBackup 无锁热备份

2.1 安装

Ubuntu:

sudo apt install percona-xtrabackup

macOS:

brew install percona-xtrabackup

2.2 全量备份

xtrabackup --backup --target-dir=/data/backups/full_$(date +%F_%H-%M-%S) \--user=root --password=123456

2.3 增量备份

xtrabackup --backup --target-dir=/data/backups/inc1 \--incremental-basedir=/data/backups/full --user=root --password=123456

2.4 准备恢复 (prepare)

xtrabackup --prepare --target-dir=/data/backups/full

增量需先 apply-log-only 合并，最后一次不加

2.5 恢复

systemctl stop mysqld
xtrabackup --copy-back --target-dir=/data/backups/full
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld

---

三、定时自动备份脚本

mysqldump

#!/bin/bash
DATE=$(date +%F_%H-%M-%S)
mysqldump -u root -p123456 your_db > /data/backups/db_$DATE.sql

XtraBackup

#!/bin/bash
DATE=$(date +%F_%H-%M-%S)
BACKUP_DIR="/data/backups/full_$DATE"
xtrabackup --backup --target-dir=$BACKUP_DIR --user=root --password=123456
xtrabackup --prepare --target-dir=$BACKUP_DIR

cron 定时任务：

crontab -e
0 2 * * * /path/to/backup.sh

---

四、方案适用场景

方案	是否锁表	适用场景
mysqldump (InnoDB + --single-transaction)	否	小型数据，快速备份、数据迁移
XtraBackup	否	大型数据，需求高可用性、增量备份环境

---

如果你有更复杂的数据库备份需求，包括 Docker/集群/跨地域备份、定制化脚本或固定日志/更新检测，欢迎留言，我可以按照你的环境维护需求符合性地符合定制。