MySQL 小技巧：恢复误删除的表

案例说明

每天 2:30 做完全备份，早上 10:00 误删除 了表 students，10:10 才发现故障

现需要将数据库还原到 10:10 的状态，且恢复被删除的 students 表。

思路分析

1. 停止数据库服务
2. 将数据 还原到 2:30 的完全备份状态
3. 再通过二进制日志还原
   1. 默认：二进制日志记录到了 10:10 你停止数据库的时间
   2. 因此：此二进制日志文件中记录着 10:00 删表的动作
   3. 所以：我们可以 将该二进制日志文件中的删表动作去除
4. 然后基于 该去除了删除操作的二进制日志内容 将数据还原（ 此期间需要关闭二进制日志记录功能 ）

实际操作

前置操作

1. 验证数据库的二进制日志功能是否开启（ 且二进制日志文件存放在不同目录 ）
2. 二进制日志类型（ ROW 行类型 ）

0) "前置条件" ( 开启二进制日志功能 )
## 并将二进制日志文件与数据库文件分开存放
1. 创建二进制日志文件的存放目录
mkdir /mysql/data/logbin -pv
chown -R mysql:mysql /mysql/data/logbin/
 
2. 定义二进制日志文件路径 ( 服务器选项: log-bin )
vim /etc/my.cnf
vim /etc/my.cnf.d/mysql-server.cnf      # MySQL 8.0
[mysqld]
log_bin                                 # 开启二进制日志功能 ( 必须 )
log-bin=/mysql/data/logbin/mysql-bin    # 定义二进制日志文件路径
 
3. 重启 mariadb 服务
setenforce 0         # 关闭 SELinux ( 重要 )
systemctl restart mariadb.service
# 如重启报错 ( 可以尝试查看日志 tail -f /var/log/mysql/mysqld.log )
# 将报错信息发送至 ChatGPT 为你提供解决思路
 
4. 验证
ll /mysql/data/logbin
cat /mysql/data/logbin/mysql-bin.index ( 该文件记录当前已有的二进制日志文件列表 )
 
5. 验证
select @@sql_log_bin;
select @@log_bin;
select @@binlog_format;    # 查看二进制日志类型

模拟 误删数据

1. 模拟 2:30 的完全备份
2. 模拟 2:30 -10:00 之间的数据更新
3. 模拟 10:00 误删除了一个重要的表
4. 模拟 10:00 后其他表数据还在更新
5. 模拟 10:10 发现异常

模拟 还原过程

1. 停止数据库服务
2. 基于 mysqlbinlog 导出二进制日志文件内容
3. 将二进制日志文件中的删表操作去除！
4. 准备还原数据库
   1. 启用数据库服务
   2. 登录数据库，临时关闭二进制日志功能
   3. 先还原完全备份数据
   4. 再还原二进制日志文件数据
   5. 开启二进制日志功能
5. 验证数据库内容

// 模拟 2:30 的完全备份
// -F 选项会刷新使用一个新的二进制日志文件
[root@centos8 ~] mysqldump -uroot -p -A -F --single-transaction --master-data=2 > /backup/allbackup_`date +%F_%T`.sql
 
[root@centos8 ~] ll /backup/
 
-rw-r--r--. 1 root root 1235591 Feb  3 13:31 allbackup_2024-02-03_13:31:12.sql
 
// 完全备份后的数据更新 ( 模拟 2:30 -10:00 之间的数据更新 )
use hellodb;
insert students (stuid,name,age,gender) values('50','rose',20,'f');
insert students (stuid,name,age,gender) values('60','bob',20,'m');
 
// 模拟 10:00 误删除了一个重要的表
drop table students;
 
// 后续其它表继续更新  ( 模拟 10:00 后其他表数据还在更新 )
use hellodb1;
insert teachers (tid,name,age,gender) values('50','wangj',20,'f');
insert teachers (tid,name,age,gender) values('60','wangjun',20,'m');
 
MariaDB [hellodb1]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|  50 | wangj         |  20 | f      |
|  60 | wangjun       |  20 | m      |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)
 
// 10:10 发现表删除 ( 模拟 10:10 发现异常 )
// 先停止数据库
systemctl stop mysqld
 
// 从完全备份中, 找到二进制位置 ( 找到 2:30 - 10:10 使用的二进制日志文件名称 )
[root@centos8 ~] grep '\-\- CHANGE MASTER TO' /backup/allbackup_2024-02-03_13\:31\:12.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=156;    # 得到的信息
 
// "基于 mysqlbinlog 导出二进制日志文件内容"
[root@centos8 ~] mysqlbinlog --start-position=156 /mysql/data/logbin/mysql-bin.000003 > /backup/inc.sql
 
// 找到误删除的语句, 从备份文件中删除此语句
// 也就是将二进制日志文件中的删表操作去除!
[root@centos8 ~] vim /backup/inc.sql
# DROP TABLE `student_info` /* generated by server */
 
// 注: 如果文件过大, 可以使用 sed 实现    ( "重要" )
[root@centos8 ~] sed -i.bak '/^DROP TABLE/d' /backup/inc.sql
 
// 利用完全备份和修改过的二进制日志进行还原操作
[root@centos8 ~] systemctl start mysqld      # 启用数据库服务
[root@centos8 ~] mysql -uroot -p
MariaDB [hellodb]> set sql_log_bin=0;        # 临时关闭二进制日志功能
MariaDB [hellodb]> source /backup/allbackup_2024-02-03_13:31:12.sql;
MariaDB [hellodb]> source /backup/inc.sql;
MariaDB [hellodb]> set sql_log_bin=1;        # 开启二进制日志功能
 
// "验证数据库"
mysql -e 'show databases';
mysql -e 'select * from hellodb.students;'
mysql -e 'select * from hellodb1.teachers;'

基于如上案例，我们可以得知开启二进制日志功能的重要性，以及每天做全量备份的重要性。

注意：二进制日志文件不要与数据库文件存放同一个目录。全量备份的文件也应该不存放于本机，而存放在其他主机。异机器备份。