mysql备份与还原，误删恢复

一、备份

1.1、全量备份（定期备份）

1.1.1、使用宝塔备份

宝塔备份只需按下面界面操作即可，不做过多描述

1.1.2、使用shell脚本备份

新建备份脚本mysql_bk.sh，使用的mysql自带的mysqldump命令进行备份

#!/bin/bash

# 备份参数

# 备份数据保留天数
number=
# 备份文件存放绝对路径
backup_dir=
# mysql绝对路径，可通过which mysql或mysqld查看
mysql=
# 数据库账号名
username=
# 数据库密码，前后需添加"
password=
# 需备份数据库名，全量备份删除或注释该条，并删除执行备份中的--databases  "$database_name"
database_name=


# 时间参数不需要修改
dd=$(date +%Y-%m-%d-%H:%M:%S)
# 设置完整的PATH环境变量
export PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:"${mysql_dir}/bin"

# 创建备份目录
if [ ! -d "$backup_dir" ]; then
    mkdir -p "$backup_dir"
fi

# 进入备份目录
cd "$backup_dir" || exit 1

# 执行备份
"${mysql_dir}/bin/mysqldump" -u "$username" -p"$password" --databases  "$database_name" --single-transaction  | gzip > "$backup_dir/$database_name-$dd.sql.gz" 2>> "$backup_dir/error.log"

# 检查备份是否成功
if [ $? -eq 0 ]; then
    echo "$(date '+%Y-%m-%d %H:%M:%S') create $backup_dir/$database_name-$dd.sql.gz" >> "$backup_dir/log.txt"
else
    echo "$(date '+%Y-%m-%d %H:%M:%S') backup failed for $database_name" >> "$backup_dir/error.log"
    exit 1
fi

# 文件清理 - 修复文件计数和删除逻辑
count=$(find "$backup_dir" -name "*.sql.gz" -type f | wc -l)
if [ "$count" -gt "$number" ]; then
    # 找到最旧的文件
    delfile=$(find "$backup_dir" -name "*.sql.gz" -type f -printf '%T@ %p\n' | sort -n | head -1 | cut -d' ' -f2-)
    if [ -n "$delfile" ] && [ -f "$delfile" ]; then
        rm "$delfile"
        echo "$(date '+%Y-%m-%d %H:%M:%S') delete $delfile" >> "$backup_dir/log.txt"
    fi
fi

使用crontab 定时任务
输入 crontab -e 进行定制任务编写
Crontab 在线生成器 | 菜鸟工具
通过该工具生成定时时间后在后面增加 sh 脚本的绝对路径及 log 文件绝对路径

示例

0 1 * * * /data/mysql_bk/mysql_bk.sh >> /data/mysql_bk/mysql_bk.log 2>&1

 备注：
 0 1 * * *                      #通过crontab生成器生成，每天凌晨1点执行
 /data/mysql_bk/mysql_bk.sh      #mysql备份sh绝对路径
 >>                              #新增写入文本该条可照抄
 /data/mysql_bk/mysql_bk.log     #log存放位置可自行修改
 2>&1                            #日志保存形式可照抄

1.2、异地备份

待补充

二、恢复

2.1、宝塔恢复

只需点击数据库，点击备份，进行恢复即可

2.2、脚本恢复

脚本备份出来是.sql.gz文件，解压后使用navicate工具导入即可

2.3、binlog恢复备份后到新的数据

上面的备份只能恢复到备份的那一时刻，比如凌晨1点，白天处理的内容就需要通过二进制日志（Binlog）结合备份文件可实现数据恢复，核心步骤包括确认Binlog开启状态、定位误操作位置、提取并处理Binlog日志、恢复备份及增量数据

恢复前提条件 :

• 确认Binlog开启
  执行以下命令检查Binlog状态，返回log_bin=ON表示已启用：

SHOW VARIABLES LIKE 'log_bin';

• 若未开启，需在MySQL配置文件（如my.cnf）中添加以下配置并重启服务：

[mysqld]
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW  # 推荐使用ROW模式

• 检查备份可用性

若有全量备份（如mysqldump生成的备份文件），可优先恢复至最近备份点，再通过Binlog恢复增量数据；

若无备份，则依赖Binlog完整记录（从建库开始后的binlog都不能删才行，因为binlog记录了所有对数据进行修改的操作，如果没有之前的binlog，就没有建库那些操作，是无法全量恢复的） 误操作前后的数据变更。

恢复步骤

1. 定位误操作时间点与Binlog位置，需要找到备份后的start-position与误删前的stop-position，用于找出这段时间的操作日志。

• 按时间范围过滤Binlog（若已知误操作时间）：

# 直接查看，但内容是base64，较难读懂
mysqlbinlog --start-datetime="2023-11-02 15:00:00" --stop-datetime="2023-11-02 16:00:00" /var/lib/mysql/mysql-bin.000003
# 生成更易读的格式，生成到readable_data.sql，该方式无法导入，只用于查看position和语句
mysqlbinlog --start-datetime="2023-11-02 15:00:00" --stop-datetime="2023-11-02 16:00:00" -v --base64-output=DECODE-ROWS   /var/lib/mysql/mysql-bin.000001  /var/lib/mysql/mysql-bin.000002 > readable_data.sql

输出中会包含误操作（如DROP TABLE）的SQL语句及其位置信息。

• 遍历Binlog文件查找误操作（若时间未知）：

mysqlbinlog /var/lib/mysql/mysql-bin.000001 | grep "DROP TABLE"

记录误操作所在Binlog文件的start-position和stop-position。

输出内容示例：at 123456就是position的位置

# at 123456
#240101 10:00:00 server id 1  end_log_pos 123789 CRC32 0xabcdef12

2. 使用mysqlbinlog提取这段时间的Binlog

# 按position位置提取
mysqlbinlog --start-position=107 --stop-position=1000  --database=你的数据库名 /var/lib/mysql/mysql-bin.000003 > rollback.sql

# 按时间段提取，这段时间可能有产生了多个binlog文件
mysqlbinlog --start-datetime="2025-12-01 01:00:00"   --stop-datetime="2025-12-01 19:00:00" --database=你的数据库名 /var/lib/mysql/mysql-bin.000001 /var/lib/mysql/mysql-bin.000002  /var/lib/mysql/mysql-bin.000003  > rollback.sql

3. 导入生成的rollback.sql，里面是这段时间的操作日志

-- 1. 进入mysql
mysql -u username -p 
-- 2. 选择要恢复的数据库
USE new_database;

-- 3. 设置参数（避免导入错误）
SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
SET AUTOCOMMIT = 0;

-- 4. 执行SQL文件（需要完整路径）
SOURCE /完整路径/executable_data.sql;

-- 5. 提交事务并恢复设置
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
SET UNIQUE_CHECKS = 1;
SET AUTOCOMMIT = 1;