binlog无法恢复 mysql mysql binlog 恢复数据

1：mysql数据误删除还原

前提

mysql开启了binlog日志

binlog即binary log，我们可以通过binlog日志恢复数据

此文件是二进制日志文件。
它有两个作用，
一是增量备份，即只备份新增的内容；
二是用于主从复制等，即主节点维护了一个binlog日志文件，从节点从binlog中同步数据。

步骤

1：查询binlog要恢复的节点
2：从日志中复制临时恢复的数据节点文件
3：执行临时文件，数据恢复
4：验证恢复数据

具体如下
先验证是否开启binlog

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set (0.01 sec)

如果是OFF，则说明没有开启binlog。可以使用如下方法开启binlog
编辑/etc/my.cnf文件，在文件后面增加如下两行代码即可

server-id=1
log-bin=/var/lib/mysql/mysql-bin

server-id表示单个结点的id，这里由于只有一个结点，所以可以把id随机指定为一个数，这里将id设置成1。若集群中有多个结点，则id不能相同
第二句是指定binlog日志文件的名字为mysql-bin，以及其存储路径
重启让配置生效。

[root@localhost ~]# systemctl stop mysqld.service
[root@localhost ~]# systemctl start mysqld.service

再次验证

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.01 sec)

一般情况下，mysql默认开启此文件，就无需手动开启

方式一：传统二进制日志还原数据

准备工作

mysql> create database mydb charset utf8mb4;
mysql> use mydb;
mysql> create table test(id int)engine=innodb charset=utf8mb4;
mysql> insert into test values(1);
mysql> insert into test values(2);
mysql> insert into test values(3);
mysql> insert into test values(4);
mysql> commit;
mysql> update test set id=10 where id=4;
mysql> commit;
mysql> select * from test;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|   10 |
+------+
4 rows in set (0.00 sec)
mysql> drop database mydb;

1：由于在/etc/my.cnf配置了binlog，
这个日志会存储插入，删除，修改的日志信息 ：show master status\G;
查看删除节点开始于结束 ： show binlog events in ‘binlog.000001’;

mysql> show master status\G;
*************************** 1. row ***************************
             File: binlog.000001
         Position: 1960
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

#查找创库和删库的点,为219和1868
mysql> show binlog events in 'binlog.000001';
+---------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name      | Pos  | Event_type     | Server_id | End_log_pos | Info                                                               |
+---------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| binlog.000001 |  219 | Query          |         1 |         329 | create database mydb charset utf8mb4                               |
| binlog.000001 | 1868 | Query          |         1 |        1960 | drop database mydb                                                 |
+---------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+

2：另存为二进制日志信息

[root@localhost ~]# mysqlbinlog --start-position=219 --stop-position=1868 /var/lib/mysql/binlog.000001 > /tmp/binlog.sql

3：恢复数据

#临时关闭二进制日志记录以免重复记录
mysql> set sql_log_bin=0;
#恢复数据
mysql> source /tmp/binlog.sql
#重启二进制日志记录
mysql> set sql_log_bin=1;

4.查看数据恢复情况

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use mydb;
Database changed
mysql> select * from test;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|   10 |
+------+
4 rows in set (0.00 sec)、

方式二：基于GTID二进制日志还原数据

先修改配置文件
开启gtid模式 gtid_mode=ON

[root@localhost ~]# vi /etc/my.cnf
server-id=1
log-bin=binlog
gtid_mode=ON
enforce_gtid_consistency=true
log_slave_updates=1

#重启数据库服务
[root@localhost ~]# systemctl restart mysqld

准备数据

mysql> create database mydb1;
mysql> use mydb1;
Database changed
mysql> create table t1(id int)engine=innodb charset=utf8mb4;
mysql> insert into t1 values(1);
mysql> insert into t1 values(2);
mysql> insert into t1 values(3);
mysql> insert into t1 values(11);
mysql> insert into t1 values(12);
mysql> commit;
mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|   11 |
|   12 |
+------+
5 rows in set (0.00 sec)
mysql> drop database mydb1;

1：查看日志信息，找出要恢复的开始节点与结束节点
219-1848

mysql> show master status\G;
*************************** 1. row ***************************
             File: binlog.000003
         Position: 1944
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 51d3db57-bf69-11ea-976c-000c2911a022:1-8
1 row in set (0.00 sec)

mysql> show binlog events in 'binlog.000003';
+---------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name      | Pos  | Event_type     | Server_id | End_log_pos | Info                                                              |
+---------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
| binlog.000003 |  154 | Gtid           |         1 |         219 | SET @@SESSION.GTID_NEXT= '51d3db57-bf69-11ea-976c-000c2911a022:1' |
| binlog.000003 |  219 | Query          |         1 |         316 | create database mydb1                                             |
| binlog.000003 | 1784 | Gtid           |         1 |        1849 | SET @@SESSION.GTID_NEXT= '51d3db57-bf69-11ea-976c-000c2911a022:8' |
| binlog.000003 | 1849 | Query          |         1 |        1944 | drop database mydb1                                               |
+---------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+

2：另存为二进制日志信息，指定恢复的开始于结束

#8号事务记录为删除数据库，因此只需恢复1-7号事务记录即可
[root@localhost ~]# mysqlbinlog --skip-gtids --include-gtids='51d3db57-bf69-11ea-976c-000c2911a022:1-7' /var/lib/mysql/binlog.000003 >  /tmp/gtid.sql

参数

–include-gtids:包含事务
–exclude-gtids:排除事务
–skip-gtids:跳过事务

3：恢复文件

mysql> set sql_log_bin=0;
mysql> source /tmp/gtid.sql
mysql> set sql_log_bin=1;

4：验证恢复

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mydb1              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> use mydb1;
Database changed
mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|   11 |
|   12 |
+------+
5 rows in set (0.00 sec)

总结：

核心从日志文件找到要恢复的节点信息，生成要恢复的文件，执行-验证恢复

附
度度另外一种方式恢复数据如下

常用参数选项解释：
--start-position=875 起始pos点
--stop-position=954 结束pos点
--start-datetime="2016-9-25 22:01:08" 起始时间点
--stop-datetime="2019-9-25 22:09:46" 结束时间点
--database=zyyshop 指定只恢复zyyshop数据库(一台主机上往往有多个数据库，只限本地log日志)
-------------------------------------------------------- 
不常用选项： 
-u --user=name 连接到远程主机的用户名
-p --password[=name] 连接到远程主机的密码
-h --host=name 从远程主机上获取binlog日志
--read-from-remote-server 从某个MySQL服务器上读取binlog日志


a）查询第一个(最早)的binlog日志：
mysql> show binlog events\G;

b）指定查询 mysql-bin.000002这个文件：
mysql> show binlog events in 'mysql-bin.000002'\G;

c）指定查询 mysql-bin.000002这个文件，从pos点:624开始查起：
mysql> show binlog events in 'mysql-bin.000002' from 624\G;

d）指定查询 mysql-bin.000002这个文件，从pos点:624开始查起，查询10条（即10条语句）
mysql> show binlog events in 'mysql-bin.000002' from 624 limit 10\G;

e）指定查询 mysql-bin.000002这个文件，从pos点:624开始查起，偏移2行（即中间跳过2个），查询10条
mysql> show binlog events in 'mysql-bin.000002' from 624 limit 2,10\G;

若只回复某一条删除语句，就把删除前的节点重新放到一个文件，生成
如建表之后704
在3101的时刻进行了delete操作，从704建表之后的操作开始恢复

[root@localhost mysql]# /usr/bin/mysqlbinlog  /var/lib/mysql/mysql-bin.000001 --start-position=704 --stop-position=3101 |mysql -uroot -p hbk