Ubuntu MySQL如何备份和恢复 - 问答

Ubuntu 上 MySQL 的备份与恢复指南

一常用方法概览

逻辑备份：使用 mysqldump 导出为 .sql 脚本，适合中小型数据库、跨版本迁移与时间点恢复（配合二进制日志）。
并行逻辑备份：使用 mysqlpump（MySQL 5.7.12+）提升导出速度，适合多库/大库。
物理热备：使用 Percona XtraBackup 在不停库情况下备份 InnoDB，适合大型数据库与快速恢复。
时间点恢复 PITR：开启 binlog，用 mysqlbinlog 回放指定时间段或位置的事务。

二使用 mysqldump 进行逻辑备份与恢复

备份
- 单库：mysqldump -u root -p --single-transaction --quick mydb > mydb.sql
- 多库：mysqldump -u root -p --databases db1 db2 > dbs.sql
- 全库：mysqldump -u root -p --all-databases > all.sql
- 压缩：mysqldump -u root -p mydb | gzip > mydb.sql.gz
- 远程：mysqldump -h 10.0.0.9 -P 3306 -u root -p mydb > mydb.sql
- 常用选项说明：
  - –single-transaction：对 InnoDB 一致性快照，避免锁表。
  - –quick：边读边写，减少内存占用。
  - 主从/复制场景可加 –master-data=2（记录 binlog 位点，便于 PITR）。
恢复
- 准备库：mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS mydb;"
- 导入：mysql -u root -p mydb < mydb.sql
- 压缩包：gunzip < mydb.sql.gz | mysql -u root -p mydb
- 全库导入：mysql -u root -p < all.sql
要点
- 逻辑备份文件为文本 SQL，便于审计、编辑与迁移。
- 建议在低峰期执行，并保留多份历史备份。

三使用 mysqlpump 并行备份与恢复

备份
- 单库：mysqlpump -u root -p mydb > mydb_pump.sql
- 多库：mysqlpump -u root -p --databases db1 db2 > dbs_pump.sql
- 全库：mysqlpump -u root -p --all-databases > all_pump.sql
恢复
- 与 mysqldump 相同：mysql -u root -p mydb < mydb_pump.sql
要点
- mysqlpump 支持并行导出，速度通常优于 mysqldump；但恢复仍用 mysql 客户端导入。

四使用 Percona XtraBackup 进行物理热备与恢复

安装
- Ubuntu：sudo apt-get update && sudo apt-get install percona-xtrabackup-24
备份
- 全量：sudo xtrabackup --backup --target-dir=/opt/backup/2025-12-10
准备（使备份一致性）
- sudo xtrabackup --prepare --target-dir=/opt/backup/2025-12-10
恢复
- 建议先停库并清空数据目录：sudo systemctl stop mysql && sudo rm -rf /var/lib/mysql/*
- 拷贝回数据：sudo xtrabackup --copy-back --target-dir=/opt/backup/2025-12-10
- 修正权限：sudo chown -R mysql:mysql /var/lib/mysql
- 启动：sudo systemctl start mysql
要点
- 适合大库与零停机需求；恢复速度快。
- 仅适用于 InnoDB/XtraDB；MyISAM 表在准备阶段会锁表。

五时间点恢复与自动化运维

时间点恢复 PITR
- 确认开启 binlog：mysql -u root -p -e "SHOW VARIABLES LIKE 'log_bin';"
- 按时间回放：mysqlbinlog --start-datetime="2025-12-10 10:00:00" --stop-datetime="2025-12-10 10:05:00" /var/log/mysql/mysql-bin.000001 | mysql -u root -p
- 按位置回放：mysqlbinlog --start-position=154 --stop-position=1056 /var/log/mysql/mysql-bin.000001 | mysql -u root -p

自动化备份脚本与定时任务

示例脚本 /opt/backup/mysql_backup.sh：

#!/bin/bash
DATE=$(date +%F_%H-%M-%S)
BACKUP_DIR="/opt/backup/mysql"
DB="mydb"
USER="root"
mkdir -p "$BACKUP_DIR"
mysqldump -u "$USER" -p --single-transaction --quick "$DB" \
  | gzip > "$BACKUP_DIR/${DB}_${DATE}.sql.gz"
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +7 -delete

赋权：chmod +x /opt/backup/mysql_backup.sh
每天 02:00 执行：0 2 * * * /opt/backup/mysql_backup.sh

远程备份与传输
- rsync -avz /opt/backup/mysql user@remote:/backup/mysql/
- 或 scp /opt/backup/mysql/mydb_*.sql.gz user@remote:/backup/mysql/
备份管理与验证
- 定期做恢复演练/自动化恢复测试，验证备份可用。
- 将备份存于远程/云存储，并做好访问控制与加密。

0 赞

0 踩