MySQL备份恢复

Percona XtraBackup

下载安装

https://www.percona.com/downloads#percona-xtrabackup

全量备份

xtrabackup --backup --target-dir=/data/mysql_backup/base -uroot -p'HelloWorld@123' -H localhost -P 3306

增量备份

指定基础目录后进行增量备份，一般为嵌套的模式

# 1. 准备基础备份（如果未执行过）
xtrabackup --prepare --apply-log-only --target-dir=/data/mysql_backup/base

# 2. 准备第一个增量备份（increment1）
xtrabackup --prepare --apply-log-only \
  --target-dir=/data/mysql_backup/base \
  --incremental-dir=/data/mysql_backup/increment1

# 3. 创建第二个增量备份（increment2）
xtrabackup --backup \
  --target-dir=/data/mysql_backup/increment2 \
  --incremental-basedir=/data/mysql_backup/increment1 \
  --user=root \
  --password-file=/root/mysql_password.txt \
  --host=localhost \
  --port=3306

增量备份恢复

准备基础备份（仅应用日志）

xtrabackup --prepare --apply-log-only --target-dir=/data/mysql_backup/base

合并第一个增量备份（仅应用日志）

xtrabackup --prepare --apply-log-only \
  --target-dir=/data/mysql_backup/base \
  --incremental-dir=/data/mysql_backup/increment1

合并第二个增量备份（最后一次，完整prepare！）

xtrabackup --prepare \  # 注意：这里没有 --apply-log-only！
  --target-dir=/data/mysql_backup/base \
  --incremental-dir=/data/mysql_backup/increment2

恢复数据到MySQL

# 停止MySQL
systemctl stop mysqld

# 清空数据目录（谨慎操作）
mv /var/lib/mysql /var/lib/mysql.bak  # 建议先备份原数据
mkdir /var/lib/mysql

# 复制备份文件
xtrabackup --copy-back --target-dir=/data/mysql_backup/base

# 设置权限
chown -R mysql:mysql /var/lib/mysql

# 启动MySQL
systemctl start mysqld

为什么前几次增量恢复使用 --apply-log-only？

目的：仅合并数据，不提交事务

增量备份本身只包含 数据页的变化，而不是完整的事务日志。--apply-log-only 的作用是：

仅应用增量备份的数据变更，但不执行事务提交（避免产生不一致状态）。不执行崩溃恢复（crash recovery），因为后续可能还有更多的增量备份要应用。

差异备份就是基于全量备份和最后一次。

压缩备份需要安装qpress软件，使用–compress参数，准备数据之前使用–decompress参数解压。

mysqldump

基本用法

mysqldump -u [用户名] -p[密码] [数据库名] > [输出文件.sql]

常用选项

• -u 指定用户名
• -p 提示输入密码（不要在命令中直接写密码）
• --databases 备份多个数据库
• --all-databases 备份所有数据库
• --no-data 只导出结构，不导出数据
• --add-drop-table 在每张表前添加 DROP TABLE 语句
• --skip-lock-tables 不锁定表（适用于大型数据库）

示例

备份单个数据库：

mysqldump -u root -p mydatabase > backup.sql

备份多个数据库：

mysqldump -u root -p --databases db1 db2 db3 > backup.sql

只备份表结构：

mysqldump -u root -p --no-data mydatabase > structure.sql

从备份恢复：

mysql -u root -p mydatabase < backup.sql

在binlog中记录了在开始备份后出现的变动：

使用命令将其恢复：

mysqlbinlog /var/lib/mysql/binlog.000014 --start-position=158 | mysql -uroot -p'HelloWorld@123'

这样就可以保证数据的一致性。

记录导出

mysql -uroot -p'HelloWorld@123' masterke -e 'select * from masterke' --html > 1.txt

数据库备份案例

分解

环境配置

CONTAINER_NAME="yudao-mysql" 

# 数据库配置  
DB_HOST="localhost"  
DB_USER=""  
DB_PASSWORD=""  
DB_NAME=""  

# 本地备份文件存储路径  
LOCAL_BACKUP_PATH="/app/backup"  

# 远程服务器配置  
REMOTE_HOST=""  
REMOTE_USER="root"  
REMOTE_PATH="/app/backup"  

备份文件命名

TIMESTAMP=$(date +"%Y%m%d%H%M%S")  
BACKUP_FILE_NAME="${DB_NAME}_${TIMESTAMP}.sql"  
LOCAL_BACKUP_FILE_PATH="${LOCAL_BACKUP_PATH}/${BACKUP_FILE_NAME}"  

创建本地备份目录

mkdir -p "${LOCAL_BACKUP_PATH}"  

执行备份命令

DUMP_CMD="mysqldump -u ${DB_USER} -p${DB_PASSWORD} ${DB_NAME}"  
docker exec "${CONTAINER_NAME}" ${DUMP_CMD} > "${LOCAL_BACKUP_FILE_PATH}"  

备份结果检查与远程传输

if [ $? -eq 0 ]; then  
    echo "Backup successfully created: ${LOCAL_BACKUP_FILE_PATH}"  
    scp "${LOCAL_BACKUP_FILE_PATH}" "${REMOTE_USER}@${REMOTE_HOST}:${REMOTE_PATH}"  
    
    if [ $? -eq 0 ]; then  
        echo "Backup successfully transferred to ${REMOTE_HOST}:${REMOTE_PATH}"  
        rm "${LOCAL_BACKUP_FILE_PATH}"  
        echo "Local backup file deleted: ${LOCAL_BACKUP_FILE_PATH}"  
    else  
        echo "Failed to transfer backup to remote server."  
    fi  
else  
    echo "An error occurred during the backup process."  
fi  

使用crontab配置定时任务：

# 编辑crontab配置
crontab -e

# 添加定时任务，例如每天凌晨1点执行
0 1 * * * /path/to/your/script.sh

使用Jenkins配置定时任务：

1. 创建新任务：在Jenkins中创建一个新的定时构建任务。
2. 配置定时触发：设置任务的触发时间。
3. 执行脚本：在构建步骤中添加执行脚本的命令。

源代码

#!/bin/bash  

CONTAINER_NAME="yudao-mysql" 

# 数据库配置  
DB_HOST="localhost"  
DB_USER=""  
DB_PASSWORD=""  
DB_NAME=""  

# 本地备份文件存储路径  
LOCAL_BACKUP_PATH="/app/backup"  

# 远程服务器配置  
REMOTE_HOST=""  
REMOTE_USER="root"  
REMOTE_PATH="/app/backup"  

# 生成备份文件名  
TIMESTAMP=$(date +"%Y%m%d%H%M%S")  
BACKUP_FILE_NAME="${DB_NAME}_${TIMESTAMP}.sql"  
LOCAL_BACKUP_FILE_PATH="${LOCAL_BACKUP_PATH}/${BACKUP_FILE_NAME}"  

# 创建本地备份目录（如果不存在）  
mkdir -p "${LOCAL_BACKUP_PATH}"  

# 构建mysqldump命令  
DUMP_CMD="mysqldump -u ${DB_USER} -p${DB_PASSWORD} ${DB_NAME}"  

# 在Docker容器中执行mysqldump命令并导出备份文件  
docker exec "${CONTAINER_NAME}" ${DUMP_CMD} > "${LOCAL_BACKUP_FILE_PATH}"  

# 检查命令执行结果  
if [ $? -eq 0 ]; then  
    echo "Backup successfully created: ${LOCAL_BACKUP_FILE_PATH}"  
    
    # 通过SSH将备份文件传输到远程服务器  
    scp "${LOCAL_BACKUP_FILE_PATH}" "${REMOTE_USER}@${REMOTE_HOST}:${REMOTE_PATH}"  
    
    if [ $? -eq 0 ]; then  
        echo "Backup successfully transferred to ${REMOTE_HOST}:${REMOTE_PATH}"  
        
        # 可选：删除本地备份文件  
        rm "${LOCAL_BACKUP_FILE_PATH}"  
        echo "Local backup file deleted: ${LOCAL_BACKUP_FILE_PATH}"  
    else  
        echo "Failed to transfer backup to remote server."  
    fi  
else  
    echo "An error occurred during the backup process."  
fi  

还可以一次性备份所有数据库：

#!/bin/sh  

# 数据库配置  
DB_HOST=""  
DB_USER=""  
DB_PASSWORD=""

# 备份文件存储目录  
BACKUP_DIR="/mnt/data/backup/mysql/all_databases/"  
mkdir -p $BACKUP_DIR  

# 备份文件名  
BACKUP_FILE="$BACKUP_DIR/backup_$(date +%Y%m%d%H%M%S).sql"  

# 日志文件名  
LOG_FILE="$BACKUP_DIR/backup_$(date +%Y%m%d%H%M%S).log"  

# 使用mysqldump进行备份，并将日志重定向到日志文件  
docker run --rm -v "$BACKUP_DIR":/backup mysql:8.0.41 \
    mysqldump -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASSWORD" --all-databases > "$BACKUP_FILE" 2> "$LOG_FILE"  

# 检查备份是否成功  
if [ $? -eq 0 ]; then  
    echo "Backup successfully completed: $BACKUP_FILE" >> "$LOG_FILE"  
else  
    echo "Backup failed" >> "$LOG_FILE"  
    exit 1  
fi  

# 删除7天前的备份文件  
find "$BACKUP_DIR" -type f -name 'backup_*.sql' -mtime +7 -exec rm {} \;  

# 删除7天前的日志文件  
find "$BACKUP_DIR" -type f -name 'backup_*.log' -mtime +7 -exec rm {} \;