Mysql之binlog数据恢复

binlog——数据恢复

一、什么是binlog

binlog（binary log）日志，是二进制日志文件。有增量备份和主从复制作用。

增量备份：保存除查询语句外的执行操作，在mysql误操作后，可以通过binlog日志来恢复数据，本次要说的就是这个东西。

主从复制：主节点维护binlog日志文件，从节点从binlog日志文件中同步数据

二、binlog配置

mysql默认不开启binlog配置，需要通过修改mysql的配置文件来启用binlog日志

windows：修改my.ini配置文件

linux：修改my.cnf配置文件

# windows示例

# 1.查看binlog是否开启，如未开启 log_bin 一项为OFF
show variables like '%log_bin%';

# 2.my.ini 配置文件中添加如下配置
[mysqld]
log-bin = mysql-bin # 自定义binlog日志文件存放位置【必须】，以mysql-bin.00000*的形式存放到数据文件夹下
server_id = 666 # 自定义节点id
binlog_format = ROW # binlog日志格式，有 STATEMENT(默认)，ROW，MIXED 三种方式。ROW:记录哪条数据被修改了，修改成了什么样子了
# expire_logs_days = 7 # binlog日志过期清理时间
# max_binlog_size = 256m # binlog单个日志文件大小
# binlog_cache_size = 16m # binlog缓存大小
# max_binlog_cache_size = 1024m # 最大binlog缓存大小

# 3.重启mysql服务后再次通过第一步查看binlog开启状态
Variable_name     	Value
log_bin				ON 					# binlog已开启
log_bin_basename	D:\mysql-bin 		# binlog日志文件存放，后缀名为.00000*
log_bin_index		D:\mysql-bin.index  # 二进制索引文件存放

三、binlog相关命令

# 1.查询binlog日志
show master logs;
# 数据如下所示，当前有一个名为mysql-bin.000001的binlog日志文件，以及该日志文件的大小
--
Log_name         	File_size 
mysql-bin.000001	154
--

# 2.查看指定日志文件的数据,日志文件中记录了一些执行事件相关的东西
show binlog events in 'mysql-bin.000001';
# 数据如下所示
--
Log_name			Pos	Event_type		Server_id	End_log_pos		Info
mysql-bin.000001	4	Format_desc		666			123				Server ver: 5.7.25-log, Binlog ver: 4
mysql-bin.000001	123	Previous_gtids	666			154		
--

# 3.刷新binlog日志，会新增一个日志文件在下次刷新binlog前的执行事件都会记录到这个新日志文件里面
flush logs;
# 再次查询binlog日志，日志文件多出一个，并且第一个日志文件的大小有变化
show master logs;
--
Log_name         	File_size 
mysql-bin.000001	201
mysql-bin.000002	154
--
# 查看第一个日志文件的数据，发现多了一个名为Rotate的事件，该事件是记录了我们执行的flush logs命令
show binlog events in 'mysql-bin.000001';
--
Log_name			Pos	Event_type		Server_id	End_log_pos		Info
mysql-bin.000001	4	Format_desc		666			123				Server ver: 5.7.25-log, Binlog ver: 4
mysql-bin.000001	123	Previous_gtids	666			154	
mysql-bin.000001	154	Rotate			666			201				mysql-bin.000002;pos=4
--
# 4.重置binlog日志
reset master;
# 查询binlog日志，发现日志文件被清除只剩下一个，并且日志文件的数据也是初始数据
show master logs;
--
Log_name         	File_size 
mysql-bin.000001	154
--

四、binlog数据恢复

1.准备工作

# 1.确保mysql已经开启binlog,并且执行一次重置binlog日志方便测试
reset master;
# 2.创建数据库 test_bl
create database test_bl;
use test_bl;
# 3.创建表 test_user
create table test_user(
	id int(32) primary key auto_increment,
    name varchar(6)
);
# 4.添加数据
insert into test_user(name) values("张三");
insert into test_user(name) values("李四");
# 5.查看数据
--
id	name
1	张三
2	李四
--

2.查看binlog日志

# 1.查看当前的binlog日志
show master logs;
# 数据如下所示，可以发现有一个日志文件，并且该日志文件大小相比于初始大小增加了不少
--
Log_name         	File_size 
mysql-bin.000001	939
--
# 2.查看该日志文件的数据
show binlog events in 'mysql-bin.000001';
# 数据如图所示：日志文件中已将我们执行创表和添加数据的操作记录在内

# 可以看到记录的每个事件都有Pos（开始位置）和End_log_pos（结束位置）
# a.在End_log_pos=219的Event_type中有Anonymous_Gtid关键字代表在下一个Anonymous_Gtid之前的记录都为一组信息，而End_log_pos=452的Event_type中有Anonymous_Gtid关键字代表下一个组。从End_log_pos=219 到 End_log_pos=387 为一组操作。在End_log_pos=387的Info中记录着我们创表的语句

# 在End_log_pos=527的Info中有BEGIN关键字代表事务的开始，而End_log_pos=663的Info中有COMMIT关键字代表事务的提交。从End_log_pos=527 到 End_log_pos=663 为一个事务操作。
# b.在End_log_pos=452 到 End_log_pos=663 这一组之间，有一个End_log_pos=623的Event_type事件类型为Write_rows，代表该事件是新增数据事件。
# c.在End_log_pos=728 到 End_log_pos=939 这一组之间，有一个End_log_pos=908的Event_type事件类型为Write_rows，代表该事件是新增数据事件。

# 综上所述a记录创表,b和c记录新增的数据

3.删除表再恢复

# 1.删除test_user表
drop table test_user;

# 2.查看binlog日志
show master logs;
# 数据如下所示：发现文件大小又变大了，说明删除表的操作被记录了
--
Log_name         	File_size 
mysql-bin.000001	1132
--
# 查看mysql-bin.000001日志数据验证下
show binlog events in 'mysql-bin.000001';
# 数据如图所示：发现多了一组操作，在End_log_pos=1132的Info中记录我们执行删除表的操作

# 3.如何恢复数据
# 首先明确我们要恢复到日志中的哪一个位置，图中高亮的两条事件是一组删除操作，所以我们应该要恢复到删除组之前的位置，也就是End_log_pos=939

# 4.恢复数据需要使用mysqlbinlog,所以我们先退出mysql,来到dos命令窗口
# 切换到mysql目录下，所以下面命令的位置都是相对于当前位置
cd develop\mysql-5.7.25
# 使用mysqlbinlog.exe 进行数据恢复,此处我的mysql用户名为root,密码为123456
bin\mysqlbinlog.exe --stop-position=939 --database=test_bl sqlData\mysql-bin.000001 | mysql -uroot -p123456
# 命令拆分说明
# bin\mysqlbinlog.exe		mysqlbinlog.exe位置
# --stop-position=939		恢复到binlog日志文件中对应的End_log_pos位置
# --database=test_bl		指定数据库，等同于 -d=test_bl
# sqlData\mysql-bin.000001	mysql-bin.000001日志文件的位置
# | 						|
# mysql -uroot -p123456		mysql登录命令
# mysqlbinlog还有其它的选项，具体可以自己进行查询，如指定开始恢复位置 --start-position=位置(等同于-j=位置)，所以有需要指定开始位置和结束位置的，可以将 --start-position --stop-position两个选项一起使用。

# 5.进入mysql，查看test_user表和两条数据是不是又回来了