MySQL使用Binlog删库到恢复

必须数据库开启 Binlog 日志才行，不然这种方式没办法的，如果没有开启建议开启

开启 binlog 日志大概会有 1% 的性能损耗

Binlog 日志介绍

Binlog 记录模式

Binlog 是记录所有数据库表结构变更以及表数据修改的二进制 日志，不会记录 SELECT 和 SHOW 这类操作。Binlog 日志是以事件形式记录，还包含语句所执行的 消耗时间。开启 Binlog 日志有以下两个最重要的使用场景。

主从复制：在主库中开启 Binlog 功能，这样主库就可以把 Binlog 传递给从库，从库拿到 Binlog 后实现数据恢复达到主从数据一致性。

数据恢复：通过 mysqlbinlog 工具来恢复数据。

Binlog 文件名默认为“主机名_binlog-序列号”格式，例如 oak_binlog-000001，也可以在配置文件 中指定名称。文件记录模式有 STATEMENT、ROW 和 MIXED 三种，具体含义如下。

ROW（row-based replication, RBR）：日志中会记录每一行数据被修改的情况，然后在 slave 端对相同的数据进行修改。

优点：能清楚记录每一个行数据的修改细节，能完全实现主从数据同步和数据的恢复。

缺点：批量操作，会产生大量的日志，尤其是 alter table 会让日志暴涨。

STATMENT（statement-based replication, SBR）：每一条被修改数据的 SQL 都会记录到 master 的 Binlog 中，slave 在复制的时候 SQL 进程会解析成和原来 master 端执行过的相同的 SQL 再次执行。简称 SQL 语句复制。

优点：日志量小，减少磁盘 IO，提升存储和恢复速度

缺点：在某些情况下会导致主从数据不一致，比如 last_insert_id()、now()等函数。

MIXED（mixed-based replication, MBR）：以上两种模式的混合使用，一般会使用 STATEMENT 模式保存 binlog，对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存 binlog，MySQL 会根据执行的 SQL 语句选择写入模式。

推荐使用第三种

Binlog 文件操作

Binlog 状态查看

show variables like 'log_bin';

开启 Binlog 功能

mysql> set global log_bin=mysql-bin;
ERROR 1238 (HY000): Variable 'log_bin' is a read only variable

需要修改 my.cnf 或 my.ini 配置文件，在[mysqld]下面增加 log_bin=mysql-bin，重启 MySQL 服务。

  # Replication Master Server (default)  
  # binary logging is required for replication  
  log-bin=mysql-bin
  # binary logging format - mixed recommended  
  binlog_format=mixed
  server-id   = 1

binlog 常用命令

show binary logs; //等价于show master logs; 
show master status; 
show binlog events; 
show binlog events in 'mysql-bin.000001';

首先看下 bin log 信息

开始建库到删库操作

完整 sql 语句

create database lanebin;
use lanebin;
create table user (
id int primary key,
name varchar(200)
) engine = innodb ;
insert into user values(1,'tina');
insert into user values(2,'tom');
update user set name ='lucy' where id = 2;
drop database lanebin;

添加修改数据删除库

开始恢复操作

show binary logs;
show binlog events in 'mysql-bin.000001';

查看 binlog 日志事件

可以看到在 position 219～322 的位置创建 database lanebin；

可以看到在 position 1496～1597 的位置删除 database lanebin；

在 position 1597 位置开始删除 database lanebin

也可以新开一个控制台使用 mysqlbinlog 命令 可以导出 sql 语句

cd /usr/local/mysql/data
mysqlbinlog "mysql-bin.000001" > "lanebin.sql"; 
#出现错误
mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8'
#执行下面的语句
mysqlbinlog --no-defaults mysql-bin.000001  > "lanebin.sql";
#出现错误，无权限
#添加权限
sudo chmod 664 mysql-bin.000001
#再次执行
mysqlbinlog --no-defaults mysql-bin.000001  > "lanebin.sql";

使用 binlog 恢复数据

先找准恢复的位置创建数据库之前到删除数据库之前 219 --1496

同样注意尽量在 mysql 数据所在的文件下，不需要写全路径了

//按指定时间恢复 
mysqlbinlog --start-datetime="2021-07-09 12:10:49" --stopdatetime="2021-07-09 12:17:37" mysql-binlog.000001 | mysql -uroot -proot
//按事件位置号恢复 
mysqlbinlog --start-position=154 --stop-position=957 mysql-binlog.000001 | mysql -uroot -proot
#如果出现警告是密码直接输入不安全，可以先不输入密码，回车之后输入

–start-datetime：从二进制日志中读取指定等于时间戳或者晚于本地服务器的时间
–stop-datetime：从二进制日志中读取指定小于时间戳或者等于本地服务器的时间 取值和上述一样
–start-position：从二进制日志中读取指定 position 事件位置作为开始。
–stop-position：从二进制日志中读取指定 position 事件位置作为事件截至
–database=lanebin 指定只恢复 lanebin 数据库(一台主机上往往有多个数据库，只限本地 log 日志)

---

不常用选项：
-u --user=name 连接到远程主机的用户名
-p --password[=name] 连接到远程主机的密码
-h --host=name 从远程主机上获取 binlog 日志
–read-from-remote-server 从某个 MySQL 服务器上读取 binlog 日志

具体操作

cd /usr/local/mysql/data
#执行命令
mysqlbinlog --start-position=219 --stop-position=1496  mysql-bin.000001 | mysql -uroot -proot
#出现错误
mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8'
#执行
mysqlbinlog --no-defaults --start-position=219 --stop-position=1496  mysql-bin.000001 | mysql -uroot -proot

mysqlbinlog 这个工具无法识别 binlog 中的配置中的 default-character-set=utf8 这个指令。
可以在 MySQL 的配置/etc/my.cnf 中将 default-character-set=utf8 修改为 character-set-server = utf8

然后重启 MySQL 服务
如果不想重启 MySQL 可以加上 --no-defaults

mysqlbinlog --no-defaults --start-position=219 --stop-position=1496 mysql-bin.000001 | mysql -uroot -proot

查看执行后数据是否恢复

数据完成了恢复

关于备份恢复

一般是通过全量备份加增量备份的当时来实现完整版的恢复
具体实现看下我另外一篇文章

MySQL 备份删库到恢复