模拟环境,这里使用docker 进行测试 mysql 版本为5.7,
注意:这里的测试是使用了docker-compose 进行构建容器的 ,需要下载 docker 和 docker-compose
环境搭建
这里使用docker-compose 进行数据库的搭建
# 编写 docker-compose.yml 文件
version: '3'
services:
mysql:
image: mysql:5.7
container_name: mysql
restart: always
environment:
MYSQL_USER: admin
MYSQL_PASSWORD: admin
MYSQL_ROOT_PASSWORD: root123
MYSQL_DATABASE: test
volumes:
- ./my.cnf:/etc/mysql/conf.d/my.cnf
- /home/mysql5.7/data:/var/lib/mysql
ports:
- 3305:3306
编写mysql的 my.cnf 配置文件,在里面设置开启binlog日志
[mysqld]
log-bin=mysql-binlog
binlog-format=row
binlog-row-image=full
expire-logs-days=7
server-id=1
编写完成之后我们就有了两个文件
# 启动构建容器
docker-compose up -d# 下面是成功示例
开始模拟数据测试
# 进入数据库
[root@localhost mysql5.7]# docker exec -it f370c57ad343 mysql -uroot -proot123
# 选择数据库
mysql> use test;
# 创建一张表IAO
CREATE TABLE `demo` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(64) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
# 新增两条数据
insert into demo values(null,"zhangsan",11),(null,"lisi",23);
数据有了现在 开始模拟误删操作
# 删除整张表
mysql> drop table demo;
# 测试查询是否还能查到这个表 ,如果报错提示 ERROR 1146 (42S02): Table 'test.demo' doesn't exist 则代表删除成功
mysql> select * from demo;
# 查看mysql 当前binlog 日志 是写入 哪个binlog日志中 我这里是 mysql-binlog.000005
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-binlog.000005
Position: 1146
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
ERROR:
No query specified
# 然后因为用于操作 mysql-binlog.000005 方便并且不影响 数据库的操作,我们刷新一下binlog日志的日志写入 文件
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
# 在查看一下 写入的文件是哪个 这里是: mysql-binlog.000006 说明刷新成功
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-binlog.000006
Position: 154
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
ERROR:
No query specified
退出容器去解析binlog日志
# 退出
mysql> exit
Bye
您在 /var/spool/mail/root 中有新邮件
# 进入容器
[root@localhost mysql5.7]# docker exec -it f370c57ad343 bash
# 进入mysql binlog 日志文件目录下
root@f370c57ad343:/# cd /var/lib/mysql
# 查看目录下的文件
root@f370c57ad343:/var/lib/mysql# ls
# 使用命令查看 binlog文件
root@f370c57ad343:/var/lib/mysql# mysqlbinlog -v mysql-binlog.000005
#然后去找到我们 创建表时候的那个操作节点
=======================================================
# at 401
#230519 8:14:05 server id 1 end_log_pos 678 CRC32 0xe7022511 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1684484045/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
CREATE TABLE `demo` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(64) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
/*!*/;
=========================================================
# 找到这个创建表的sql, 然后我们可以发现这个操作的节点是 401 # at 401 记住这个起始的节点
# 接下来 我们要找到的就是 误删表的那个sql 那个节点
=====================================================
# at 964
#230519 8:16:51 server id 1 end_log_pos 1029 CRC32 0xe405cef8 Anonymous_GTID last_committed=3 sequence_number=4 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1029
#230519 8:16:51 server id 1 end_log_pos 1146 CRC32 0xe12f24c6 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1684484211/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
DROP TABLE `demo` /* generated by server */
===================================================
# 然后我们找到了这个误删的sql节点, 这个节点是 1029 ,但是我们要取删除节点往上一个 也就是 964 作为我们的截止节点 # at 964
开始截取binlog日志去进行数据恢复
# 截取数据 mysqlbinlog binlog文件 --start-position=开始节点 --stop-position=截止节点 -vv > 输出的文件名
root@f370c57ad343:/var/lib/mysql# mysqlbinlog mysql-binlog.000005 --start-position=401 --stop-position=964 -vv > ./rollback
# 登录数据库
root@f370c57ad343:/var/lib/mysql# mysql -uroot -proot123
# 执行刚刚截取的文件
mysql> source /var/lib/mysql/rollback
到这里数据已经恢复成功了。