MySQL数据库备份与恢复详解

高危型

于 2024-10-09 14:53:07 发布

阅读量355

点赞数 16

文章标签： mysql

本文链接：https://blog.csdn.net/qq_64272546/article/details/142786956

版权

数据库备份是任何生产环境中必不可少的环节，它可以让你在遇到数据丢失、硬件故障或其他不可预见事件时，能够快速恢复数据，确保业务的连续性。MySQL作为流行的关系型数据库管理系统之一，其备份与恢复操作至关重要。本文将深入探讨MySQL数据库的备份与恢复方法，包括命令行工具、图形化工具以及自动化备份脚本的使用。

1. MySQL备份简介

MySQL备份分为逻辑备份和物理备份两种主要类型：

1.1 逻辑备份

逻辑备份是通过将数据库表的结构和数据以可读格式（如SQL脚本）导出，适用于小型数据库。常用工具包括mysqldump和mysqlpump。

1.2 物理备份

物理备份是直接复制数据库文件，包括数据库表、索引和日志文件。适用于大型数据库或需快速恢复的场景。常用工具包括XtraBackup和mysqlbackup。

2. MySQL逻辑备份

2.1 使用mysqldump进行备份

mysqldump是MySQL自带的命令行工具，用于导出数据库数据和结构。

2.1.1 备份整个数据库

bash

mysqldump -u[username] -p[password] [database_name] > [backup_file.sql]

示例：

bash

mysqldump -uroot -p mydatabase > mydatabase_backup.sql

2.1.2 备份多个数据库

bash

mysqldump -u[username] -p[password] --databases [database1] [database2] > [backup_file.sql]

示例：

bash

mysqldump -uroot -p --databases mydatabase1 mydatabase2 > databases_backup.sql

2.1.3 备份所有数据库

bash

mysqldump -u[username] -p[password] --all-databases > [backup_file.sql]

示例：

bash

mysqldump -uroot -p --all-databases > all_databases_backup.sql

2.1.4 备份特定表

bash

mysqldump -u[username] -p[password] [database_name] [table1] [table2] > [backup_file.sql]

示例：

bash

mysqldump -uroot -p mydatabase table1 table2 > tables_backup.sql

2.2 使用mysqlpump进行备份

mysqlpump是MySQL 5.7及以上版本提供的工具，速度较快，并支持并行处理。

2.2.1 备份整个数据库

bash

mysqlpump -u[username] -p[password] [database_name] > [backup_file.sql]

示例：

bash

mysqlpump -uroot -p mydatabase > mydatabase_backup.sql

2.2.2 备份多个数据库

bash

mysqlpump -u[username] -p[password] --databases [database1] [database2] > [backup_file.sql]

示例：

bash

mysqlpump -uroot -p --databases mydatabase1 mydatabase2 > databases_backup.sql

2.2.3 备份所有数据库

bash

mysqlpump -u[username] -p[password] --all-databases > [backup_file.sql]

示例：

bash

mysqlpump -uroot -p --all-databases > all_databases_backup.sql

3. MySQL物理备份

3.1 使用Percona XtraBackup进行备份

Percona XtraBackup是开源的物理备份工具，支持热备份，不影响正在运行的数据库服务。

3.1.1 安装Percona XtraBackup

Ubuntu系统：

bash

sudo apt-get update sudo apt-get install percona-xtrabackup-24

CentOS系统：

bash

sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm sudo yum install percona-xtrabackup-24

3.1.2 完全备份

首先创建备份目录：

bash

mkdir /path/to/backup

执行备份命令：

bash

xtrabackup --backup --target-dir=/path/to/backup

3.1.3 增量备份

首先创建一次全备份，记录基础备份目录：

bash

xtrabackup --backup --target-dir=/path/to/backup/full

然后进行增量备份，指定基础备份目录：

bash

xtrabackup --backup --target-dir=/path/to/backup/inc --incremental-basedir=/path/to/backup/full

3.1.4 应用日志和恢复

在恢复前需要应用日志：

bash

xtrabackup --prepare --target-dir=/path/to/backup/full

恢复数据到数据库目录（需停止MySQL服务）：

bash

xtrabackup --copy-back --target-dir=/path/to/backup/full chown -R mysql:mysql /var/lib/mysql

4. MySQL数据库恢复

4.1 使用mysqldump恢复

将导出的SQL文件重新导入数据库：

bash

mysql -u[username] -p[password] [database_name] < [backup_file.sql]

示例：

bash

mysql -uroot -p mydatabase < mydatabase_backup.sql

4.2 使用mysqlpump恢复

与mysqldump的恢复方法类似：

bash

mysql -u[username] -p[password] [database_name] < [backup_file.sql]

示例：

bash

mysql -uroot -p mydatabase < mydatabase_backup.sql

4.3 使用Percona XtraBackup恢复

4.3.1 恢复完全备份

首先应用日志：

bash

xtrabackup --prepare --target-dir=/path/to/backup/full

停止MySQL服务：

bash

sudo systemctl stop mysql

然后将数据恢复到数据目录：

bash

xtrabackup --copy-back --target-dir=/path/to/backup/full chown -R mysql:mysql /var/lib/mysql

启动MySQL服务：

bash

sudo systemctl start mysql

4.3.2 恢复增量备份

依次应用每个增量备份和完全备份的日志：

bash

xtrabackup --prepare --apply-log-only --target-dir=/path/to/backup/full xtrabackup --prepare --apply-log-only --target-dir=/path/to/backup/full --incremental-dir=/path/to/backup/inc xtrabackup --prepare --target-dir=/path/to/backup/full

然后与完全备份的恢复方法相同，停止服务并恢复文件。

5. 自动化备份脚本

编写自动化脚本，进行定期备份并压缩备份文件。

5.1 MySQL逻辑备份脚本

bash

#!/bin/bash # 配置变量 BACKUP_DIR="/path/to/backup" MYSQL_USER="root" MYSQL_PASSWORD="password" DATABASE_NAME="mydatabase" DATE=$(date +%F_%T) BACKUP_FILE="$BACKUP_DIR/$DATABASE_NAME-$DATE.sql" # 创建备份目录 mkdir -p $BACKUP_DIR # 执行备份 mysqldump -u $MYSQL_USER -p$MYSQL_PASSWORD $DATABASE_NAME > $BACKUP_FILE # 压缩备份文件 gzip $BACKUP_FILE # 保留最近7天的备份 find $BACKUP_DIR -type f -mtime +7 -name "*.sql.gz" -exec rm -f {} \;

5.2 MySQL物理备份脚本

需要确保已安装Percona XtraBackup：

bash

#!/bin/bash # 配置变量 BACKUP_DIR="/path/to/backup" DATE=$(date +%F_%T) FULL_BACKUP_DIR="$BACKUP_DIR/full-$DATE" INCREMENTAL_BACKUP_DIR="$BACKUP_DIR/inc-$DATE" # 创建备份目录 mkdir -p $BACKUP_DIR # 完全备份 xtrabackup --backup --target-dir=$FULL_BACKUP_DIR # 增量备份 xtrabackup --backup --target-dir=$INCREMENTAL_BACKUP_DIR --incremental-basedir=$FULL_BACKUP_DIR # 压缩备份文件 tar -czvf $FULL_BACKUP_DIR.tar.gz -C $FULL_BACKUP_DIR . tar -czvf $INCREMENTAL_BACKUP_DIR.tar.gz -C $INCREMENTAL_BACKUP_DIR . # 清理原始备份文件 rm -rf $FULL_BACKUP_DIR rm -rf $INCREMENTAL_BACKUP_DIR # 保留最近7天的备份 find $BACKUP_DIR -type f -mtime +7 -name "*.tar.gz" -exec rm -f {} \;