binlog恢复数据库数据

1.MySQL开启binlog

MySQL安装完成后，MySQL5.7版本binlog默认不开启，MySQL8默认开启binlog；登录MySQL后，查看binlog状态sql如下：

show variables like '%log_bin%';

如未开启binlog日志，则可按以下步骤开启binlog日志

开启binlog日志

修改MySQL配置文件，linux中配置文件为my.conf，window下问my.ini，下面以centos为例演示

• 编辑配置文件

vim /etc/my.cnf

• 添加配置项

1. log-bin=mysql-bin

2. server-id=1

 

• 重启MySQL服务

 systemctl restart mysqld

• 进入MySQL查看binlog日志是否开启成功

 

log_bin为ON说明这个参数是开启的，就是说系统是记录了bin log的

log_bin_basename配置了bin log的文件路径及文件前缀名

log_bin_index配置了bin log索引文件的路径

• 查看日志列表

 show master logs;

• 根据log_bin_basename的路径查看binlog具体文件

 2.使用binlog日志恢复数据

当需要恢复数据时，为了防止恢复数据后影响最新业务，需要执行flush logs，产生一个新的binlog文件，此时旧的binlog文件不会再有写入； 

 

下面具体通过mysql-bin.000001来进行数据恢复  

恢复时需要在binlog中找到两个位置：

• 数据恢复的起始位置
• 数据恢复的结束位置

如在数据准备中的drop操作，需要在binlog中找到该位置，并将该位置作为数据恢复的结束位置

 2.1通过mysqlbinlog将binlog转为sql，以方便查询具体位置

mysqlbinlog --set-charset=utf-8 /var/lib/mysql/mysql-bin.000001>backuptmp.sql

 查看生成的backuptmp.sql，最终确定需要恢复的起始位置为219，结束位置为982

 

2.2通过mysqlbinlog执行恢复操作 

mysqlbinlog -v /var/lib/mysql/mysql-bin.000001 --start-position=219 --stop-position=982 | mysql -uroot -p123456 

/var/lib/mysql/mysql-bin.000001  要操作binlog文件

--start-position=219                      数据恢复的起始位置

--stop-position=982                      数据恢复的结束位置

mysql -uroot -p123456                 数据恢复需要登录数据库