ubuntu上mysql备份与恢复方法

Ubuntu上MySQL备份与恢复方法

一、备份方法

1. 使用mysqldump工具备份（逻辑备份，适用于大多数场景）

mysqldump是MySQL自带的逻辑备份工具，可导出数据库结构（表结构、视图、存储过程等）和数据，生成可执行的SQL文件。

备份单个数据库：

mysqldump -u [用户名] -p[密码] [数据库名] > [备份文件路径].sql

示例：备份mydatabase数据库到/home/user/backup目录，文件名包含时间戳：

mysqldump -u root -p mydatabase > /home/user/backup/mydatabase_$(date +%Y%m%d_%H%M%S).sql

备份所有数据库：
添加--all-databases选项，导出MySQL服务器上的所有数据库：
```
mysqldump -u root -p --all-databases > /home/user/backup/all_databases.sql
```
压缩备份文件：
通过管道将输出传递给gzip，减少存储空间占用：
```
mysqldump -u root -p mydatabase | gzip > /home/user/backup/mydatabase.sql.gz
```

2. 使用mysqlpump工具备份（并行备份，性能更优）

mysqlpump是MySQL 5.7及以上版本引入的并行备份工具，支持多线程导出，提升备份速度。

备份单个数据库：

mysqlpump -u [用户名] -p[密码] [数据库名] > [备份文件路径].sql

示例：备份mydatabase数据库：

mysqlpump -u root -p mydatabase > /home/user/backup/mydatabase_pump.sql

备份所有数据库：
添加--all-databases选项：

mysqlpump -u root -p --all-databases > /home/user/backup/all_databases_pump.sql

3. 物理备份（直接复制数据文件，适用于大型数据库）

物理备份通过复制MySQL数据目录（默认/var/lib/mysql）实现，恢复速度快，但需停止MySQL服务。

停止MySQL服务：
```
sudo systemctl stop mysql
```

打包数据目录：
使用tar命令压缩数据目录：

sudo tar -czvf /home/user/backup/mysql_physical_backup.tar.gz /var/lib/mysql

启动MySQL服务：
```
sudo systemctl start mysql
```

4. 自动化备份（通过Cron Job定期执行）

使用crontab设置定时任务，实现每日/每周自动备份。

创建备份脚本（如/home/user/backup/mysql_backup.sh）：

#!/bin/bash
BACKUP_DIR="/home/user/backup"
DB_USER="root"
DB_PASS="yourpassword"
DATE=$(date +%Y%m%d_%H%M%S)
mysqldump -u $DB_USER -p$DB_PASS mydatabase | gzip > "$BACKUP_DIR/mydatabase_$DATE.sql.gz"
# 删除7天前的备份
find "$BACKUP_DIR" -type f -name "*.sql.gz" -mtime +7 -exec rm {} \;

赋予脚本执行权限：

chmod +x /home/user/backup/mysql_backup.sh

添加Cron Job：
编辑crontab（crontab -e），添加以下内容（每日凌晨2点执行）：
```
0 2 * * * /home/user/backup/mysql_backup.sh
```

二、恢复方法

1. 使用SQL文件恢复（逻辑恢复，适用于逻辑备份）

通过mysql命令将备份的SQL文件导入数据库。

恢复单个数据库：
若目标数据库已存在，直接导入：

mysql -u [用户名] -p[密码] [数据库名] < [备份文件路径].sql

示例：恢复mydatabase数据库：

mysql -u root -p mydatabase < /home/user/backup/mydatabase.sql

恢复所有数据库：
使用--all-databases选项：

mysql -u root -p --all-databases < /home/user/backup/all_databases.sql

恢复到新数据库：
先创建新数据库，再导入备份文件：

mysql -u root -p -e "CREATE DATABASE new_database;"
mysql -u root -p new_database < /home/user/backup/mydatabase.sql

2. 解压后恢复（针对压缩的SQL备份）

若备份文件是.gz格式，需先解压再导入。

解压备份文件：

gunzip /home/user/backup/mydatabase.sql.gz

恢复数据库：

mysql -u root -p mydatabase < /home/user/backup/mydatabase.sql

3. 物理备份恢复（直接复制数据文件，适用于物理备份）

物理恢复需停止MySQL服务，替换数据目录后重启。

停止MySQL服务：
```
sudo systemctl stop mysql
```
备份现有数据目录（可选但推荐）：
```
sudo mv /var/lib/mysql /var/lib/mysql_old
```

创建新数据目录并复制备份文件：

sudo mkdir /var/lib/mysql
sudo cp -R /home/user/backup/mysql_physical_backup/* /var/lib/mysql/

修改权限：
确保MySQL用户（mysql）拥有数据目录的所有权：
```
sudo chown -R mysql:mysql /var/lib/mysql
```
启动MySQL服务：
```
sudo systemctl start mysql
```
验证数据完整性：
使用mysqlcheck工具检查并修复数据库：
```
sudo mysqlcheck -u root -p --all-databases --auto-repair
```

4. 使用二进制日志恢复（增量恢复，适用于误操作）

若MySQL开启了二进制日志（log_bin=ON），可通过mysqlbinlog工具恢复指定时间段的增量数据。

确认二进制日志开启状态：
登录MySQL，执行：
```
SHOW VARIABLES LIKE 'log_bin';
```
若结果为ON，则表示已开启。
查找二进制日志文件：
日志文件通常位于/var/log/mysql/目录，文件名类似mysql-bin.000001。

恢复指定时间段数据：
使用mysqlbinlog解析日志并过滤时间范围，再导入MySQL：

mysqlbinlog --start-datetime="2025-10-10 10:00:00" --stop-datetime="2025-10-10 11:00:00" /var/log/mysql/mysql-bin.000001 | mysql -u root -p

5. 使用Percona XtraBackup恢复（热备份，适用于InnoDB）

Percona XtraBackup是开源热备份工具，支持InnoDB和XtraDB存储引擎，可在不锁表的情况下备份大型数据库。

安装Percona XtraBackup：

sudo apt-get install percona-xtrabackup-24

准备备份（创建备份目录并执行备份）：

sudo xtrabackup --backup --target-dir=/home/user/backup/xtrabackup

准备恢复（整理备份文件，使其可用于恢复）：

sudo xtrabackup --prepare --target-dir=/home/user/backup/xtrabackup

恢复数据：
将备份文件复制到MySQL数据目录：

sudo xtrabackup --copy-back --target-dir=/home/user/backup/xtrabackup --datadir=/var/lib/mysql

修改权限并重启MySQL：

sudo chown -R mysql:mysql /var/lib/mysql
sudo systemctl restart mysql