binlog的简单数据恢复

1. 环境准备

1）在/etc/my.cnf文件中的服务器端开启二进制功能

log_bin=/data/binlog/mysql-bin

2）确认二进制记录方式

binlog_format=row

此处选用DML语句的RBR模式，特点是记录数据行的变化，日志量大，可读性低，需要借助工具分析，但足够严谨。
SMR模式则原封不动的记录语句，日志量小，可读性强，但不够严谨。

3）取消自动提交功能

autocommit=0

4）查看日志开启情况

3306 [(none)]>show variables like '%log_bin%';
+---------------------------------+------------------------------+
| Variable_name                   | Value                        |
+---------------------------------+------------------------------+
| log_bin                         | ON                           |
| log_bin_basename                | /data/binlog/mysql-bin       |
| log_bin_index                   | /data/binlog/mysql-bin.index |
| log_bin_trust_function_creators | OFF                          |
| log_bin_use_v1_row_events       | OFF                          |
| sql_log_bin                     | ON                           |
+---------------------------------+------------------------------+
6 rows in set (0.01 sec)

2. 模拟数据丢失

3306 [(none)]>create database binlog charset utf8;
Query OK, 1 row affected (0.01 sec)

3306 [(none)]>use binlog;
Database changed
3306 [binlog]>create table t1(id int);
Query OK, 0 rows affected (0.04 sec)

3306 [binlog]>insert into t1 values(1);
Query OK, 1 row affected (0.57 sec)

3306 [binlog]>commit;
Query OK, 0 rows affected (0.01 sec)

3306 [binlog]>insert into t1 values(2);
Query OK, 1 row affected (0.02 sec)

3306 [binlog]>commit;
Query OK, 0 rows affected (0.01 sec)

3306 [binlog]>insert into t1 values(3);
Query OK, 1 row affected (0.00 sec)

3306 [binlog]>commit;
Query OK, 0 rows affected (0.03 sec)

3306 [binlog]>drop database binlog;
Query OK, 1 row affected (0.79 sec)

3. 进行数据恢复

1）查看是哪个二进制日志文件

3306 [(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |     1432 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

2）查看日志内容的事件

3306 [(none)]>show binlog events in 'mysql-bin.000002';
+------------------+------+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+------+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000002 |    4 | Format_desc    |         6 |         123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-bin.000002 |  123 | Previous_gtids |         6 |         154 |                                       |
| mysql-bin.000002 |  154 | Anonymous_Gtid |         6 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000002 |  219 | Query          |         6 |         332 | create database binlog charset utf8   |
| mysql-bin.000002 |  332 | Anonymous_Gtid |         6 |         397 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000002 |  397 | Query          |         6 |         498 | use `binlog`; create table t1(id int) |
| mysql-bin.000002 |  498 | Anonymous_Gtid |         6 |         563 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000002 |  563 | Query          |         6 |         637 | BEGIN                                 |
| mysql-bin.000002 |  637 | Table_map      |         6 |         684 | table_id: 108 (binlog.t1)             |
| mysql-bin.000002 |  684 | Write_rows     |         6 |         724 | table_id: 108 flags: STMT_END_F       |
| mysql-bin.000002 |  724 | Xid            |         6 |         755 | COMMIT /* xid=11 */                   |
| mysql-bin.000002 |  755 | Anonymous_Gtid |         6 |         820 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000002 |  820 | Query          |         6 |         894 | BEGIN                                 |
| mysql-bin.000002 |  894 | Table_map      |         6 |         941 | table_id: 108 (binlog.t1)             |
| mysql-bin.000002 |  941 | Write_rows     |         6 |         981 | table_id: 108 flags: STMT_END_F       |
| mysql-bin.000002 |  981 | Xid            |         6 |        1012 | COMMIT /* xid=13 */                   |
| mysql-bin.000002 | 1012 | Anonymous_Gtid |         6 |        1077 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000002 | 1077 | Query          |         6 |        1151 | BEGIN                                 |
| mysql-bin.000002 | 1151 | Table_map      |         6 |        1198 | table_id: 108 (binlog.t1)             |
| mysql-bin.000002 | 1198 | Write_rows     |         6 |        1238 | table_id: 108 flags: STMT_END_F       |
| mysql-bin.000002 | 1238 | Xid            |         6 |        1269 | COMMIT /* xid=15 */                   |
| mysql-bin.000002 | 1269 | Anonymous_Gtid |         6 |        1334 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000002 | 1334 | Query          |         6 |        1432 | drop database binlog                  |
+------------------+------+----------------+-----------+-------------+---------------------------------------+
23 rows in set (0.00 sec)

3）截取出从建库到删库的position区间

根据日志文件内容，可以看出建库的起始点pos是219；删库的pos起始位置是1334和1432，我们只需要删库前的pos即可，即1334。

4）在命令行恢复导出sql文件

[root@bff8617b4afc tools]# mysqlbinlog --start-position=219 --stop-position=1334 /data/binlog/mysql-bin.000002 >/tmp/bin.sql

5）在数据库恢复数据

3306 [(none)]>source /tmp/bin.sql

6）检查是否恢复成功

3306 [binlog]>select * from binlog.t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.03 sec)

4. 总结：

1.首先确保参数开启。
二进制开启；
取消自动提交；
启用RBR行记录模式。

2.确认是哪个二进制文件。

3.确认需要的position号，即恢复数据的起始位置。