MySQL 数据库备份与恢复详解（分步指南）

一、备份方法分类

1. 逻辑备份
   • 通过 SQL 语句导出数据结构+数据（常用工具：mysqldump）。
   • 优点：跨平台、跨版本兼容性强。
   • 缺点：大数据库备份/恢复速度较慢。
2. 物理备份
   • 直接复制数据库文件（如 InnoDB 的 .ibd 文件或 MyISAM 的 .MYD/.MYI 文件）。
   • 优点：速度快，适合大型数据库。
   • 缺点：需停止服务或锁表，版本兼容性差。
3. 工具备份
   • 使用专业工具（如 Percona XtraBackup、MySQL Enterprise Backup）。
   • 特点：支持热备份、增量备份，适合生产环境。

二、逻辑备份与恢复（mysqldump）

1. 全库备份

bash复制代码

	# 导出所有数据库（需权限）
	mysqldump -u root -p --all-databases > all_databases.sql
	# 导出指定数据库
	mysqldump -u root -p --databases db1 db2 > dbs.sql

2. 单库/单表备份

bash复制代码

	# 导出单个数据库
	mysqldump -u root -p dbname > dbname.sql
	# 导出指定表（仅结构或仅数据）
	mysqldump -u root -p dbname table1 table2 --no-data # 仅结构
	mysqldump -u root -p dbname table1 --where="id<100" # 条件导出

3. 恢复数据

bash复制代码

	# 全库恢复（需先创建数据库或指定 --all-databases）
	mysql -u root -p < all_databases.sql
	# 指定数据库恢复
	mysql -u root -p dbname < dbname.sql
	# 从指定位置恢复（如恢复单表）
	sed -n '/-- Table structure for table `table1`/,/UNLOCK TABLES/p' dbname.sql | mysql -u root -p dbname

三、物理备份与恢复

1. 停止 MySQL 服务

bash复制代码

sudo systemctl stop mysql

2. 复制数据文件

bash复制代码

	# 默认数据目录（路径可能不同）
	cp -r /var/lib/mysql /backup/mysql_backup

3. 恢复数据

bash复制代码

	# 停止 MySQL 服务
	sudo systemctl stop mysql
	# 替换数据目录
	cp -r /backup/mysql_backup/* /var/lib/mysql/
	# 修改文件权限（重要！）
	chown -R mysql:mysql /var/lib/mysql
	# 启动 MySQL
	sudo systemctl start mysql

四、高级备份工具（Percona XtraBackup）

1. 安装工具

bash复制代码

	# Ubuntu/Debian
	apt-get install percona-xtrabackup-24

2. 全量热备份

bash复制代码

xtrabackup --user=root --password=密码 --backup --target-dir=/backup/xtra_backup

3. 增量备份

bash复制代码

	xtrabackup --user=root --password=密码 --backup --target-dir=/backup/incremental \
	--incremental-basedir=/backup/xtra_backup

4. 恢复数据

bash复制代码

	# 准备全量备份
	xtrabackup --prepare --apply-log-only --target-dir=/backup/xtra_backup
	# 合并增量备份
	xtrabackup --prepare --apply-log-only --target-dir=/backup/xtra_backup \
	--incremental-dir=/backup/incremental
	# 复制到数据目录
	xtrabackup --copy-back --target-dir=/backup/xtra_backup
	# 修改权限并启动 MySQL
	chown -R mysql:mysql /var/lib/mysql
	systemctl start mysql

五、自动化备份策略

1. Cron 定时任务

   bash复制代码

   	# 每天凌晨备份，保留7天
   	0 2 * * * /usr/bin/mysqldump -u root -p密码 --all-databases | gzip > /backup/mysql_$(date +\%Y\%m\%d).sql.gz
   	find /backup/ -name "*.sql.gz" -mtime +7 -delete

2. 云存储同步
   使用 rclone 或 aws s3 sync 将备份文件上传至云端。

六、注意事项

1. 一致性备份
   • 使用 FLUSH TABLES WITH READ LOCK（MyISAM）或 innodb_support_xa=1（InnoDB）确保数据一致性。
2. 大文件处理
   • 分卷压缩：mysqldump ... | gzip | split -b 100M -
   • 并行恢复：pv large_dump.sql | mysql -u root -p
3. 权限验证
   • 确保备份用户有 SELECT, SHOW VIEW, TRIGGER, LOCK TABLES 权限。
4. 测试恢复流程
   • 定期在测试环境验证备份可用性。

七、常见错误处理

1. 恢复时出现表已存在错误

   sql复制代码

   	-- 导入时添加参数
   	mysql -u root -p --force dbname < dbname.sql

2. 字符集不一致
   在 my.cnf 中明确设置 character_set_server=utf8mb4。

3. InnoDB 表空间问题
   使用 innodb_force_recovery 参数尝试修复（谨慎操作！）。

通过合理选择备份策略并严格测试恢复流程，可最大限度保障数据库安全与业务连续性。