MySQL的所有的数据库改变操作（除了select等）都会记录到一种二进制文件中，这种文件就是bin log。
bin log 用来实现主从复制，也常用来误删数据库找回丢失的记录。
一般恢复从两种纬度来进行恢复：
为了测试方便可以先删除之前的bin log文件。
注意：最好是自己本地开发环境来执行以下命令，千万不要在测试环境或者其它共用环境删除bin log。
# 用来查看当前最新的bin log文件及对应的结束位置 show master status; # 查看所有bin log文件 show binary logs; # 生成一个新的bin log文件（推荐：自己玩的时候可以先生成一个新的bin log文件，所有新操作都记录在新生成的文件中） flush binary logs; # 删除所有binlog日志，新日志编号从头000001开始（建议自己玩的时候可以使用） reset master; # 删除mysql-bin.000001之前的所有bin log purge master logs to 'mysql-bin.000001'; # 删除指定时间之前的数据 purge master logs before '2022-09-01 23:59:59';四：准备测试数据
执行SQL时不要一下子全部执行，为了后续方便演示基于时间范围的操作，需要一条一条的执行，最好每条命令间隔1秒以上执行。
CREATE DATABASE `db1`;
use db1;
CREATE TABLE `tbl_user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL COMMENT '用户名',
`status` tinyint(4) DEFAULT NULL COMMENT '状态(0:关闭，1：打开)',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into tbl_user(username, status) values('monday', 0);
insert into tbl_user(username, status) values('vbirdbest', 1);
update tbl_user set status = 1 where username = 'monday';
delete from tbl_user where username = 'vbirdbest';
drop database db1;
五：查看bin log
bin log 需要关注的字段：
# 查看当前最新的bin log文件 mysql> show master status; # 查看bin文件基本信息, Pos表示位置position，如904位置是insert一条数据 mysql> show binlog events in 'mysql-bin.000001';方式二：mysqlbinlog命令
mysqlbinlog方式和show binlog events方式差不多，其中 at 表示开始位置Pos(position)，end_log_pos表示结束位置点。
# 如果报权限问题切换root用户 mysqlbinlog: File '/usr/local/mysql/data/mysql-bin.000001' not found (Errcode: 13 - Permission denied) ~ sudo su # 解码：将insert/update/delete等解码成SQL语句 mysqlbinlog --no-defaults --skip-gtids --base64-output=DECODE-ROWS -vv /usr/local/mysql/data/mysql-bin.000001六：恢复数据mysqlbinlog 6.1 mysqlbinlog命令常用参数
# 删除所有bin log文件，从mysql-bin.000001开始 mysql> reset master; mysql> show master status;6.3 模拟数据库误删操作
mysql> CREATE DATABASE `db1`;
mysql> use db1;
mysql> CREATE TABLE `tbl_user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL COMMENT '用户名',
`status` tinyint(4) DEFAULT NULL COMMENT '状态(0:关闭，1：打开)',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
mysql> insert into tbl_user(username, status) values('monday', 0);
mysql> insert into tbl_user(username, status) values('vbirdbest', 1);
# mysql备份 https://blog.csdn.net/zhou920786312/article/details/123354503
# 单事务导出包括存储过程、触发器、事件
~ mysqldump -uroot -p --single-transaction --triggers --routines --events --databases db1 > db1-20220904.dump
mysql> insert into tbl_user(username, status) values('modely', 1);
# 模拟误操作，清空了表
mysql> delete from tbl_user;
6.3 从备份中恢复
~ mysql -uroot -p < alldb1.dump七：从bin log中恢复
总共有3条数据，已经有2条从备份中恢复了，剩下1条我们从bin log中找出来恢复。
7.1 方式一：基于时间范围 7.1.1 先查看一下所有的内容，确认时间范围~ sudo su ~ /usr/local/mysql/bin/mysqlbinlog --no-defaults --base64-output=DECODE-ROWS -vv --skip-gtids=true --database=db1 /usr/local/mysql/data/mysql-bin.0000017.1.2 根据时间范围确认过滤出来的日志
/usr/local/mysql/bin/mysqlbinlog --no-defaults --start-datetime="2022-09-04 18:12:17" --stop-datetime="2022-09-04 18:12:52" --base64-output=DECODE-ROWS -vv --skip-gtids=true --database=db1 /usr/local/mysql/data/mysql-bin.0000017.1.3 确认好后导出.sql文件
# 导出sql文件 /usr/local/mysql/bin/mysqlbinlog --no-defaults --start-datetime="2022-09-04 18:12:17" --stop-datetime="2022-09-04 18:12:52" --base64-output=DECODE-ROWS -vv --skip-gtids=true --database=db1 /usr/local/mysql/data/mysql-bin.000001 > /Users/mengday/binlog.sql7.1.4 从.sql文件中恢复数据
# 2. 方式一：然后登录mysql source这个文件 ~ mysql -uroot -p mysql> source /Users/mengday/binlog.sql; # 2. 方式二 mysql -uroot -p < /Users/mengday/binlog.sql;
通过 source binlog.sql 或者 mysql -uroot -p < binlog.sql 都没有将insert语句插入进去，不知道什么原因。通过另一种形式也没有执行成功！！！
/usr/local/mysql/bin/mysqlbinlog --no-defaults --start-datetime="2022-09-04 18:12:17" --stop-datetime="2022-09-04 18:12:52" --base64-output=DECODE-ROWS -vv --skip-gtids=true --database=db1 /usr/local/mysql/data/mysql-bin.000001 | mysql -uroot -p7.2 方式二：基于位置(推荐)
因为在同一秒中可能操作多条SQL，如果在这一秒中有一部分需要恢复有一部分不需要恢复那么使用居于时间范围的就不准确，使用基于位置的就更加准确。
7.2.1 基于位置过滤日志/usr/local/mysql/bin/mysqlbinlog --no-defaults --start-position=841 --stop-position=948 --base64-output=DECODE-ROWS -vv --skip-gtids=true --database=db1 /usr/local/mysql/data/mysql-bin.000001 > /Users/mengday/binlog2.sql7.2.2 恢复数据
source binlog2.sql;
同样不出意外的insert语句并没有执行。
7.3 多文件恢复# 跨bin log文件 mysqlbinlog --skip-gtids=true --start-position=802 mysql-bin.000001 --stop-position=726 mysql-bin.000002 |mysql -uroot -proot # 从mysql-bin.000001 802位置开始到mysql-bin.000002结束 mysqlbinlog --skip-gtids=true --start-position=802 mysql-bin.000001 mysql-bin.000002 |mysql -uroot -proot
注意：如果有多个mysql-bin文件，文件中的位置position不是唯一的，是重复利用的。所以当操作多个文件时要小心。例如 at 4 可能在多个bin log中出现。需要操作多个文件时最好分开一个一个单独操作。
八：binlog2sql 工具既然通过source不能直接执行bin log sql语句，那么我们就把bin log sql语句转化成正常的sql语句自己手动执行。
Python工具 binlog2sql：https://github.com/danfengcao/binlog2sql
git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql # 注意：如果本地python环境冲突的话，最好为当前项目创建一个虚拟环境，否则很容易报ModuleNotFoundError: No module named 'pymysqlreplication' python3 -m venv env source env/bin/activate pip3 install -r requirements.txt # 查询所有sql语句，从而找到想要恢复是sql语句 python3 binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'root123' -ddb1 -ttbl_user --start-file='mysql-bin.000001' # 通过起始位置进一步确认是否是想要的sql python3 binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'root123' -ddb1 -ttbl_user --start-file='mysql-bin.000001' --start-position=705 --stop-position=948 # -B参数生成对应的回滚sql，如insert回滚sql为delete python3 binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'root123' -ddb1 -ttbl_user --start-file='mysql-bin.000001' --start-position=705 --stop-position=948 > insert.sql | cat # 拿到这个SQL我直接执行，我就不信恢复不过来 INSERT INTO `db1`.`tbl_user`(`id`, `username`, `status`) VALUES (6, 'modely', 1); #start 705 end 948 time 2022-09-04 18:12:17至此葵花宝典已经连成。
