MySQL误删数据后找回

修改配置文件

编辑 MySQL 的主配置文件

vim /etc/my.cnf

在 【mysqld】标签下添加，如下内容，开启 binlog 日志

server-id=1
log-bin=mysql-bin

重启 mysql 服务

systemctl restart mysqld

场景构造

本教程讲创建 ops 数据库，一张表 customers 表，往表里添加三条记录
然后 做一下备份，备份完成后再往 customers 表里添加 三条记录。
接着删除 opt 数据库。
最后使用 备份 + binlog 日志还原数据

创建数据库

create database ops;
use ops;

创建表、插入数据

create table customers(
    id int not null auto_increment,
    name char(20) not null,
    age int not null,
    primary key(id)
)engine=InnoDB;
insert into customers values(1,"wangbo","24");
insert into customers values(2,"guohui","22");
insert into customers values(3,"zhangheng","27");

完整备份

创建 /opt/backup 文件夹，用于存放备份

mkdir -p /opt/backup

执行完整备份

mysqldump -uroot -pl@Qq_789 -B -F -R -x --master-data=2 ops|gzip >/opt/backup/ops_$(date +%F).sql.gz

插入数据

insert into customers values(4,"liupeng","21");
insert into customers values(5,"xiaoda","31");
insert into customers values(6,"fuaiai","26");

人为制造误操作

删除 ops 库

drop database ops;

完全备份+binlog日志恢复数据

停止 mysql 服务

systemctl stop mysqld

解压完全备份

cd /opt/backup/
gzip -d ops_2021-05-07.sql.gz

确定全备时刻binlog的位置

grep CHANGE ops_2021-05-07.sql

拷贝 binlog 日志

cd /var/lib/mysql
cp mysql-bin.000002 /opt/backup/

将binlog文件导出sql文件，并vim编辑它删除其中的drop语句

cd /opt/backup/
mysqlbinlog mysql-bin.000002 > 002bin.sql

删除 binlog 日志

rm -rf /var/lib/mysql/mysql-bin.*

恢复全备数据

先启动 mysql 服务

cd /opt/backup/
mysql -uroot -pl@Qq_789 < ops_2021-05-07.sql

登录数据库 查看恢复情况：

恢复 binlog 中的数据

编辑 002bin.sql 文件，将误操作部分注释掉或删除

 cd /opt/backup/
 vim 002bin.sql

执行 binlog 日志

mysql -uroot -pl@Qq_789 < 002bin.sql