Mysql的binlog知识整理详解

痛定思痛。 2022-03-21 08:39 237阅读 0赞

## 引言 ##

作为一个java开发，虽然不必像DBA那样整天和binlog打交道，但是不可避免在工作中经常会听到binlog。以前我只知道binlog是保存了mysql的数据操作命令，用户数据恢复。现在很多架构类似与点评的puma，会使用binlog配合kafka进行数据同步。那么这个binlog到底是什么样的文件，如何起到数据恢复和同步的作用呢？本次进行总结。

## binlog介绍 ##

### 定义 ###

*  定义一：binlog日志用于记录所有更新了数据或者已经潜在更新了数据（例如，没有匹配任何行的一个DELETE）的所有语句。语句以“事件”的形式保存，它描述数据更改。
 *  定义二：binlog是记录所有数据库表结构变更（例如CREATE、ALTER TABLE…）以及表数据修改（INSERT、UPDATE、DELETE…）的二进制日志。  
    binlog不会记录SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改，但你可以通过查询通用日志来查看MySQL执行过的所有语句。多说一句，如果update操作没有造成数据变化，也是会记入binlog  
    以上是篇文章的定义，我都摘录下来，其实意思是差不多的。

### 使用binlog ###

1，如何查看是否启用binlog呢？

show variables like 'log_%';

结果如图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2l2ZXJzb24yMDEwMTEyMjI4_size_16_color_FFFFFF_t_70]  
可以看到log\_bin是开启的。  
关键信息解释：

*  log\_bin\_index ：设置此参数是指定二进制索引文件的路径与名称
 *  log\_bin： 设置此参数表示启用binlog功能

2，查看所有binlog日志列表

show master logs;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2l2ZXJzb24yMDEwMTEyMjI4_size_16_color_FFFFFF_t_70 1]

### 日志文件 ###

上文说到，binlog是二进制文件。那么，这个二进制文件具体是怎样的呢？  
这个二进制文件包括两类文件：索引文件和日志文件。

ps -ef|grep mysql

结果：  
/usr/local/mysql/bin/mysqld --user=\_mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/usr/local/mysql/data/mysqld.local.err --pid-file=/usr/local/mysql/data/mysqld.local.pid --keyring-file-data=/usr/local/mysql/keyring/keyring --early-plugin-load=keyring\_file=keyring\_file.so

可知binlog文件目录为：/usr/local/mysql/data  
可以看到文件如下图：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2l2ZXJzb24yMDEwMTEyMjI4_size_16_color_FFFFFF_t_70 2]

*  索引文件（文件名后缀为.index）用于记录哪些日志文件正在被使用
 *  日志文件（文件名后缀为.00000\*）记录数据库所有的DDL和DML(除了数据查询语句)语句事件。

### 查看binlog ###

mysqlbinlog binlog.000002

结果如下：

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
    /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
    DELIMITER /*!*/;
    # at 4
    #190131 15:32:56 server id 1  end_log_pos 124 CRC32 0x28d8b133 	Start: binlog v 4, server v 8.0.14 created 190131 15:32:56 at startup
    # Warning: this binlog is either in use or was not closed properly.
    ROLLBACK/*!*/;
    BINLOG '
    qKRSXA8BAAAAeAAAAHwAAAABAAQAOC4wLjE0AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
    AAAAAAAAAAAAAAAAAACopFJcEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA
    CgEzsdgo
    '/*!*/;
    # at 124
    #190131 15:32:56 server id 1  end_log_pos 155 CRC32 0x6b4b9944 	Previous-GTIDs
    # [empty]
    # at 155
    #190131 15:42:16 server id 1  end_log_pos 232 CRC32 0x16aaff2a 	Anonymous_GTID	last_committed=0	sequence_number=1	rbr_only=no	original_committed_timestamp=1548920536803824	immediate_commit_timestamp=1548920536803824	transaction_length=198
    # original_commit_timestamp=1548920536803824 (2019-01-31 15:42:16.803824 CST)
    # immediate_commit_timestamp=1548920536803824 (2019-01-31 15:42:16.803824 CST)
    /*!80001 SET @@session.original_commit_timestamp=1548920536803824*//*!*/;
    /*!80014 SET @@session.original_server_version=80014*//*!*/;
    /*!80014 SET @@session.immediate_server_version=80014*//*!*/;
    SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
    # at 232
    #190131 15:42:16 server id 1  end_log_pos 353 CRC32 0x66bcc6f3 	Query	thread_id=8	exec_time=0	error_code=0	Xid = 6
    SET TIMESTAMP=1548920536/*!*/;
    SET @@session.pseudo_thread_id=8/*!*/;
    SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
    SET @@session.sql_mode=1168113696/*!*/;
    SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
    /*!\C utf8mb4 *//*!*/;
    SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
    SET @@session.lc_time_names=0/*!*/;
    SET @@session.collation_database=DEFAULT/*!*/;
    /*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
    create database petertest
    /*!*/;
    # at 353
    #190131 15:43:26 server id 1  end_log_pos 432 CRC32 0x36c6f69e 	Anonymous_GTID	last_committed=1	sequence_number=2	rbr_only=no	original_committed_timestamp=1548920606264810	immediate_commit_timestamp=1548920606264810	transaction_length=294
    # original_commit_timestamp=1548920606264810 (2019-01-31 15:43:26.264810 CST)
    # immediate_commit_timestamp=1548920606264810 (2019-01-31 15:43:26.264810 CST)
    /*!80001 SET @@session.original_commit_timestamp=1548920606264810*//*!*/;
    /*!80014 SET @@session.original_server_version=80014*//*!*/;
    /*!80014 SET @@session.immediate_server_version=80014*//*!*/;
    SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
    # at 432
    #190131 15:43:26 server id 1  end_log_pos 647 CRC32 0xf7c4f80e 	Query	thread_id=8	exec_time=0	error_code=0	Xid = 12
    use `petertest`/*!*/;
    SET TIMESTAMP=1548920606/*!*/;
    /*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
    CREATE TABLE `test` (
    `ID` varchar(32) NOT NULL,
    `data` text,
    PRIMARY KEY (`ID`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    /*!*/;
    SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
    DELIMITER ;
    # End of log file
    /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
    /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

分析上面内容，可以看到我创建表的语句都记录在日志中。但是这种方式不太好看，所有内容都在一起，我们有另一种查看方式：

show binlog events in 'binlog.000002'\G;

结果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2l2ZXJzb24yMDEwMTEyMjI4_size_16_color_FFFFFF_t_70 3]

### 如何用binlog恢复数据 ###

以下对ops库的member表进行操作  
mysql> use ops；  
mysql> CREATE TABLE IF NOT EXISTS `member` (  
\-> `id` int(10) unsigned NOT NULL AUTO\_INCREMENT,  
\-> `name` varchar(16) NOT NULL,  
\-> `sex` enum(‘m’,‘w’) NOT NULL DEFAULT ‘m’,  
\-> `age` tinyint(3) unsigned NOT NULL,  
\-> `classid` char(6) DEFAULT NULL,  
\-> PRIMARY KEY (`id`)  
\-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;  
Query OK, 0 rows affected (0.10 sec)

mysql> show tables;  
±--------------+  
| Tables\_in\_ops |  
±--------------+  
| member |  
±--------------+  
1 row in set (0.00 sec)

事先插入两条数据  
mysql> insert into member(`name`,`sex`,`age`,`classid`) values(‘wangshibo’,‘m’,27,‘cls1’),(‘guohuihui’,‘w’,27,‘cls2’);  
Query OK, 2 rows affected (0.08 sec)  
Records: 2 Duplicates: 0 Warnings: 0  
mysql> select \* from member;  
±—±----------±----±----±--------+  
| id | name | sex | age | classid |  
±—±----------±----±----±--------+  
| 1 | wangshibo | m | 27 | cls1 |  
| 2 | guohuihui | w | 27 | cls2 |  
±—±----------±----±----±--------+  
2 rows in set (0.00 sec)

下面开始进行场景模拟：  
1）  
ops库会在每天凌晨4点进行一次完全备份的定时计划任务，如下：  
\[root@vm-002 ~\]\# crontab -l  
0 4 \* \* \* /usr/bin/mysqldump -uroot -p -B -F -R -x --master-data=2 ops|gzip >/opt/backup/ops\_$(date +%F).sql.gz

这里手动执行下，将ops数据库备份到/opt/backup/ops\_KaTeX parse error: Expected 'EOF', got '\#' at position 38: …\[root@vm-002 ~\]\#̲ mysqldump -uro…(date +%F).sql.gz  
Enter password:  
\[root@vm-002 ~\]\# ls /opt/backup/  
ops\_2016-09-25.sql.gz

参数说明：  
\-B：指定数据库  
\-F：刷新日志  
\-R：备份存储过程等  
\-x：锁表  
–master-data：在备份语句里添加CHANGE MASTER语句以及binlog文件及位置点信息

待到数据库备份完成，就不用担心数据丢失了，因为有完全备份数据在！！

由于上面在全备份的时候使用了-F选项，那么当数据备份操作刚开始的时候系统就会自动刷新log，这样就会自动产生  
一个新的binlog日志，这个新的binlog日志就会用来记录备份之后的数据库“增删改”操作  
查看一下：  
mysql> show master status;  
±-----------------±---------±-------------±-----------------+  
| File | Position | Binlog\_Do\_DB | Binlog\_Ignore\_DB |  
±-----------------±---------±-------------±-----------------+  
| mysql-bin.000003 | 106 | | |  
±-----------------±---------±-------------±-----------------+  
1 row in set (0.00 sec)

也就是说， mysql-bin.000003 是用来记录4:00之后对数据库的所有“增删改”操作。

2）  
早上9点上班了，由于业务的需求会对数据库进行各种“增删改”操作。  
比如：在ops库下member表内插入、修改了数据等等：

先是早上进行插入数据：  
mysql> insert into ops.member(`name`,`sex`,`age`,`classid`) values(‘yiyi’,‘w’,20,‘cls1’),(‘xiaoer’,‘m’,22,‘cls3’),(‘zhangsan’,‘w’,21,‘cls5’),(‘lisi’,‘m’,20,‘cls4’),(‘wangwu’,‘w’,26,‘cls6’);  
Query OK, 5 rows affected (0.08 sec)  
Records: 5 Duplicates: 0 Warnings: 0

mysql> select \* from member;  
±—±----------±----±----±--------+  
| id | name | sex | age | classid |  
±—±----------±----±----±--------+  
| 1 | wangshibo | m | 27 | cls1 |  
| 2 | guohuihui | w | 27 | cls2 |  
| 3 | yiyi | w | 20 | cls1 |  
| 4 | xiaoer | m | 22 | cls3 |  
| 5 | zhangsan | w | 21 | cls5 |  
| 6 | lisi | m | 20 | cls4 |  
| 7 | wangwu | w | 26 | cls6 |  
±—±----------±----±----±--------+  
7 rows in set (0.00 sec)

3）  
中午又执行了修改数据操作：  
mysql> update ops.member set name=‘李四’ where id=4;  
Query OK, 1 row affected (0.07 sec)  
Rows matched: 1 Changed: 1 Warnings: 0

mysql> update ops.member set name=‘小二’ where id=2;  
Query OK, 1 row affected (0.06 sec)  
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select \* from member;  
±—±----------±----±----±--------+  
| id | name | sex | age | classid |  
±—±----------±----±----±--------+  
| 1 | wangshibo | m | 27 | cls1 |  
| 2 | 小二 | w | 27 | cls2 |  
| 3 | yiyi | w | 20 | cls1 |  
| 4 | 李四 | m | 22 | cls3 |  
| 5 | zhangsan | w | 21 | cls5 |  
| 6 | lisi | m | 20 | cls4 |  
| 7 | wangwu | w | 26 | cls6 |  
±—±----------±----±----±--------+  
7 rows in set (0.00 sec)

4）  
在下午18:00的时候，悲剧莫名其妙的出现了！  
手贱执行了drop语句，直接删除了ops库！吓尿！  
mysql> drop database ops;  
Query OK, 1 row affected (0.02 sec)

5）  
这种时候，一定不要慌张！！！  
先仔细查看最后一个binlog日志，并记录下关键的pos点，到底是哪个pos点的操作导致了数据库的破坏(通常在最后几步)；

先备份一下最后一个binlog日志文件：  
\[root@vm-002 ~\]\# cd /var/lib/mysql/  
\[root@vm-002 mysql\]\# cp -v mysql-bin.000003 /opt/backup/  
`mysql-bin.000003' ->`/opt/backup/mysql-bin.000003’  
\[root@vm-002 mysql\]\# ls /opt/backup/  
mysql-bin.000003 ops\_2016-09-25.sql.gz

接着执行一次刷新日志索引操作，重新开始新的binlog日志记录文件。按理说mysql-bin.000003  
这个文件不会再有后续写入了，因为便于我们分析原因及查找ops节点，以后所有数据库操作都会写入到下一个日志文件。  
mysql> flush logs;  
Query OK, 0 rows affected (0.13 sec)

6）  
读取binlog日志，分析问题。  
读取binlog日志的方法上面已经说到。  
方法一：使用mysqlbinlog读取binlog日志：  
\[root@vm-002 ~\]\# cd /var/lib/mysql/  
\[root@vm-002 mysql\]\# mysqlbinlog mysql-bin.000003

方法二：登录服务器，并查看(推荐此种方法)  
mysql> show binlog events in ‘mysql-bin.000003’;

±-----------------±----±------------±----------±------------±---------------------------------------------------------------------------------------------------------------------------+  
| Log\_name | Pos | Event\_type | Server\_id | End\_log\_pos | Info |  
±-----------------±----±------------±----------±------------±---------------------------------------------------------------------------------------------------------------------------+  
| mysql-bin.000003 | 4 | Format\_desc | 1 | 106 | Server ver: 5.1.73-log, Binlog ver: 4 |  
| mysql-bin.000003 | 106 | Query | 1 | 173 | BEGIN |  
| mysql-bin.000003 | 173 | Intvar | 1 | 201 | INSERT\_ID=3 |  
| mysql-bin.000003 | 201 | Query | 1 | 444 | use `ops`; insert into ops.member(`name`,`sex`,`age`,`gsan','w',21,'cls5'),('lisi','m',20,'cls4'),('wangwu','w',26,'cls6') | | mysql-bin.000003 | 444 | Xid | 1 | 471 | COMMIT /* xid=66 */ | | mysql-bin.000003 | 471 | Query | 1 | 538 | BEGIN | | mysql-bin.000003 | 538 | Query | 1 | 646 | use`ops`; update ops.member set name='李四' where id= | | mysql-bin.000003 | 646 | Xid | 1 | 673 | COMMIT /* xid=68 */ | | mysql-bin.000003 | 673 | Query | 1 | 740 | BEGIN | | mysql-bin.000003 | 740 | Query | 1 | 848 | use`ops\`; update ops.member set name=‘小二’ where id= |  
| mysql-bin.000003 | 848 | Xid | 1 | 875 | COMMIT /\* xid=69 \*/ |  
| mysql-bin.000003 | 875 | Query | 1 | 954 | drop database ops |  
| mysql-bin.000003 | 954 | Rotate | 1 | 997 | mysql-bin.000004;pos=4 |  
±-----------------±----±------------±----------±------------±---------------------------------------------------------------------------------------------------------------------------+  
13 rows in set (0.00 sec)

或者：

mysql> show binlog events in ‘mysql-bin.000003’\\G;  
…  
…  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 12. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
Log\_name: mysql-bin.000003  
Pos: 875  
Event\_type: Query  
Server\_id: 1  
End\_log\_pos: 954  
Info: drop database ops  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 13. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
Log\_name: mysql-bin.000003  
Pos: 954  
Event\_type: Rotate  
Server\_id: 1  
End\_log\_pos: 997  
Info: mysql-bin.000004;pos=4  
13 rows in set (0.00 sec)

通过分析，造成数据库破坏的pos点区间是介于 875–954 之间（这是按照日志区间的pos节点算的），只要恢复到875前就可。

7）  
先把凌晨4点全备份的数据恢复：  
\[root@vm-002 ~\]\# cd /opt/backup/  
\[root@vm-002 backup\]\# ls  
mysql-bin.000003 ops\_2016-09-25.sql.gz  
\[root@vm-002 backup\]\# gzip -d ops\_2016-09-25.sql.gz  
\[root@vm-002 backup\]\# mysql -uroot -p -v < ops\_2016-09-25.sql  
Enter password:

/\*!40101 SET @OLD\_CHARACTER\_SET\_CLIENT=@@CHARACTER\_SET\_CLIENT \*/

/\*!40101 SET @OLD\_CHARACTER\_SET\_RESULTS=@@CHARACTER\_SET\_RESULTS \*/

…  
…

/\*!40111 SET SQL\_NOTES=@OLD\_SQL\_NOTES \*/

这样就恢复了截至当日凌晨(4:00)前的备份数据都恢复了。

mysql> show databases; \#发现ops库已经恢复回来了  
mysql> use ops;  
Reading table information for completion of table and column names  
You can turn off this feature to get a quicker startup with -A

Database changed  
mysql> show tables;  
±--------------+  
| Tables\_in\_ops |  
±--------------+  
| member |  
±--------------+  
1 row in set (0.00 sec)

mysql>

但是这仅仅只是恢复了当天凌晨4点之前的数据，在4:00–18:00之间的数据还没有恢复回来！！  
怎么办呢？  
莫慌！这可以根据前面提到的mysql-bin.000003的新binlog日志进行恢复。

8）  
从binlog日志恢复数据  
恢复命令的语法格式：  
mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名

常用参数选项解释：  
–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日志

小结：实际是将读出的binlog日志内容，通过管道符传递给mysql命令。这些命令、文件尽量写成绝对路径；

a）完全恢复(需要手动vim编辑mysql-bin.000003，将那条drop语句剔除掉)  
\[root@vm-002 backup\]\# cp /var/lib/mysql/mysql-bin.000003 /opt/backup  
\[root@vm-002 backup\]\# mysqlbinlog /opt/backup/mysql-bin.000003 > /opt/backup/000003.sql  
\[root@vm-002 backup\]\# vim /opt/backup/000003.sql \#删除里面的drop语句  
\[root@vm-002 backup\]\# mysql -uroot -p -v < /opt/backup/000003.sql

温馨提示：  
在恢复全备数据之前必须将该binlog文件移出，否则恢复过程中，会继续写入语句到binlog，最终导致增量恢复数据部分变得比较混乱！  
可参考：[https://www.cnblogs.com/kevingrace/p/5904800.html][https_www.cnblogs.com_kevingrace_p_5904800.html]

b）指定pos结束点恢复(部分恢复)：  
–stop-position=471 pos结束节点（按照事务区间算，是471）  
注意：  
此pos结束节点介于“member表原始数据”与更新“name=‘李四’”之前的数据，这样就可以恢复到更改“name=‘李四’”之前的数据了。  
操作如下：  
\[root@vm-002 ~\]\# /usr/bin/mysqlbinlog --stop-position=471 --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops

恢复截止到更改“name=‘李四’”之间的数据（按照事务区间算，是673）  
\[root@vm-002 ~\]\# /usr/bin/mysqlbinlog --stop-position=673 --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops

mysql> select \* from member;  
±—±----------±----±----±--------+  
| id | name | sex | age | classid |  
±—±----------±----±----±--------+  
| 1 | wangshibo | m | 27 | cls1 |  
| 2 | guohuihui | w | 27 | cls2 |  
| 3 | yiyi | w | 20 | cls1 |  
| 4 | 李四 | m | 22 | cls3 |  
| 5 | zhangsan | w | 21 | cls5 |  
| 6 | lisi | m | 20 | cls4 |  
| 7 | wangwu | w | 26 | cls6 |  
±—±----------±----±----±--------+  
7 rows in set (0.00 sec)

c）指定pso点区间恢复(部分恢复)：  
更新 name=‘李四’ 这条数据，日志区间是Pos\[538\] --> End\_log\_pos\[646\]，按事务区间是：Pos\[471\] --> End\_log\_pos\[673\]

更新 name=‘小二’ 这条数据，日志区间是Pos\[740\] --> End\_log\_pos\[848\]，按事务区间是：Pos\[673\] --> End\_log\_pos\[875\]

c1）  
单独恢复 name=‘李四’ 这步操作，可这样：  
按照binlog日志区间单独恢复：  
\[root@vm-002 ~\]\# /usr/bin/mysqlbinlog --start-position=538 --stop-position=646 --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops

按照事务区间单独恢复  
\[root@vm-002 ~\]\# /usr/bin/mysqlbinlog --start-position=471 --stop-position=673 --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops

c2）  
单独恢复 name=‘小二’ 这步操作，可这样：  
按照binlog日志区间单独恢复：  
\[root@vm-002 ~\]\# /usr/bin/mysqlbinlog --start-position=740 --stop-position=848 --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops

按照事务区间单独恢复  
\[root@vm-002 ~\]\# /usr/bin/mysqlbinlog --start-position=673 --stop-position=875 --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops

c3）  
将 name=‘李四’、name=‘小二’ 多步操作一起恢复，需要按事务区间，可这样：  
\[root@vm-002 ~\]\# /usr/bin/mysqlbinlog --start-position=471 --stop-position=875 --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops

查看数据库：  
mysql> select \* from member;  
±—±----------±----±----±--------+  
| id | name | sex | age | classid |  
±—±----------±----±----±--------+  
| 1 | wangshibo | m | 27 | cls1 |  
| 2 | 小二 | w | 27 | cls2 |  
| 3 | yiyi | w | 20 | cls1 |  
| 4 | 李四 | m | 22 | cls3 |  
| 5 | zhangsan | w | 21 | cls5 |  
| 6 | lisi | m | 20 | cls4 |  
| 7 | wangwu | w | 26 | cls6 |  
±—±----------±----±----±--------+  
7 rows in set (0.00 sec)

这样，就恢复了删除前的数据状态了！！

=====================================================

另外：也可以指定时间节点区间恢复(部分恢复)，就是说除了用pos节点的办法进行恢复，也可以通过指定时间节点区间进行恢复，  
按时间恢复需要用mysqlbinlog命令读取binlog日志内容，找时间节点。

如上，误删除ops库后：  
先进行全备份恢复  
\[root@vm-002 backup\]\# mysql -uroot -p -v < ops\_2016-09-25.sql

查看ops数据库  
mysql> select \* from member;  
±—±----------±----±----±--------+  
| id | name | sex | age | classid |  
±—±----------±----±----±--------+  
| 1 | wangshibo | m | 27 | cls1 |  
| 2 | guohuihui | w | 27 | cls2 |  
±—±----------±----±----±--------+  
2 rows in set (0.00 sec)

mysql>

查看mysq-bin00003日志，找出时间节点  
\[root@vm-002 ~\]\# cd /var/lib/mysql  
\[root@vm-002 mysql\]\# mysqlbinlog mysql-bin.000003

BEGIN
    /*!*/;
    # at 173
    #160925 21:57:19 server id 1 end_log_pos 201 Intvar
    SET INSERT_ID=3/*!*/;
    # at 201
    #160925 21:57:19 server id 1 end_log_pos 444 Query thread_id=3 exec_time=0 error_code=0
    use `ops`/*!*/;
    SET TIMESTAMP=1474811839/*!*/;
    insert into ops.member(`name`,`sex`,`age`,`classid`) values('yiyi','w',20,'cls1'),('xiaoer','m',22,'cls3'),('zhangsan','w',21,'cls5'),('lisi','m',20,'cls4'),('wangwu','w',26,'cls6')                               #执行的sql语句
    /*!*/;
    # at 444
    #160925 21:57:19 server id 1 end_log_pos 471 Xid = 66    #开始执行的时间
    COMMIT/*!*/;
    # at 471
    #160925 21:58:41 server id 1 end_log_pos 538 Query thread_id=3 exec_time=0 error_code=0    #结束时间
    SET TIMESTAMP=1474811921/*!*/;
    BEGIN
    /*!*/;
    # at 538
    #160925 21:58:41 server id 1 end_log_pos 646 Query thread_id=3 exec_time=0 error_code=0
    SET TIMESTAMP=1474811921/*!*/;
    update ops.member set name='李四' where id=4     #执行的sql语句
    /*!*/;
    # at 646
    #160925 21:58:41 server id 1 end_log_pos 673 Xid = 68    #开始执行的时间
    COMMIT/*!*/;
    # at 673
    #160925 21:58:56 server id 1 end_log_pos 740 Query thread_id=3 exec_time=0 error_code=0   #结束时间
    SET TIMESTAMP=1474811936/*!*/;
    BEGIN
    /*!*/;
    # at 740
    #160925 21:58:56 server id 1 end_log_pos 848 Query thread_id=3 exec_time=0 error_code=0
    SET TIMESTAMP=1474811936/*!*/;
    update ops.member set name='小二' where id=2      #执行的sql语句
    /*!*/;
    # at 848
    #160925 21:58:56 server id 1 end_log_pos 875 Xid = 69   #开始执行的时间
    COMMIT/*!*/;
    # at 875
    #160925 22:01:08 server id 1 end_log_pos 954 Query thread_id=3 exec_time=0 error_code=0    #结束时间
    SET TIMESTAMP=1474812068/*!*/;
    drop database ops
    /*!*/;
    # at 954
    #160925 22:09:46 server id 1 end_log_pos 997 Rotate to mysql-bin.000004 pos: 4
    DELIMITER ;
    # End of log file
    ROLLBACK /* added by mysqlbinlog */;
    /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

恢复到更改“name=‘李四’”之前的数据  
\[root@vm-002 ~\]\# /usr/bin/mysqlbinlog --start-datetime=“2016-09-25 21:57:19” --stop-datetime=“2016-09-25 21:58:41” --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops

\[root@vm-002 ~\]\# /usr/bin/mysqlbinlog --start-datetime=“2016-09-25 21:58:41” --stop-datetime=“2016-09-25 21:58:56” --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops  
mysql> select \* from member;  
±—±----------±----±----±--------+  
| id | name | sex | age | classid |  
±—±----------±----±----±--------+  
| 1 | wangshibo | m | 27 | cls1 |  
| 2 | guohuihui | w | 27 | cls2 |  
| 3 | yiyi | w | 20 | cls1 |  
| 4 | 李四 | m | 22 | cls3 |  
| 5 | zhangsan | w | 21 | cls5 |  
| 6 | lisi | m | 20 | cls4 |  
| 7 | wangwu | w | 26 | cls6 |  
±—±----------±----±----±--------+  
7 rows in set (0.00 sec)

\[root@vm-002 ~\]\# /usr/bin/mysqlbinlog --start-datetime=“2016-09-25 21:58:56” --stop-datetime=“2016-09-25 22:01:08” --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops  
mysql> select \* from member;  
±—±----------±----±----±--------+  
| id | name | sex | age | classid |  
±—±----------±----±----±--------+  
| 1 | wangshibo | m | 27 | cls1 |  
| 2 | 小二 | w | 27 | cls2 |  
| 3 | yiyi | w | 20 | cls1 |  
| 4 | 李四 | m | 22 | cls3 |  
| 5 | zhangsan | w | 21 | cls5 |  
| 6 | lisi | m | 20 | cls4 |  
| 7 | wangwu | w | 26 | cls6 |  
±—±----------±----±----±--------+  
7 rows in set (0.00 sec)

这样，就恢复了删除前的状态了！

**总结：所谓恢复，就是让mysql将保存在binlog日志中指定段落区间的sql语句逐个重新执行一次而已。**

## 参考文档 ##

[https://www.cnblogs.com/kevingrace/p/6065088.html][https_www.cnblogs.com_kevingrace_p_6065088.html]  
[https://www.cnblogs.com/rjzheng/p/9721765.html][https_www.cnblogs.com_rjzheng_p_9721765.html]  
[https://www.cnblogs.com/kevingrace/p/5907254.html][https_www.cnblogs.com_kevingrace_p_5907254.html]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2l2ZXJzb24yMDEwMTEyMjI4_size_16_color_FFFFFF_t_70]: /images/20220321/2305d2e56ff843a4b6f10df83aadab78.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2l2ZXJzb24yMDEwMTEyMjI4_size_16_color_FFFFFF_t_70 1]: /images/20220321/b7f1212915734316aeea336a4dc30fff.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2l2ZXJzb24yMDEwMTEyMjI4_size_16_color_FFFFFF_t_70 2]: /images/20220321/15681aea156946139b104adc696f6c02.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2l2ZXJzb24yMDEwMTEyMjI4_size_16_color_FFFFFF_t_70 3]: /images/20220321/b5dc7ac570bf49f0aa8b5be7bcb9d8a7.png
[https_www.cnblogs.com_kevingrace_p_5904800.html]: https://www.cnblogs.com/kevingrace/p/5904800.html
[https_www.cnblogs.com_kevingrace_p_6065088.html]: https://www.cnblogs.com/kevingrace/p/6065088.html
[https_www.cnblogs.com_rjzheng_p_9721765.html]: https://www.cnblogs.com/rjzheng/p/9721765.html
[https_www.cnblogs.com_kevingrace_p_5907254.html]: https://www.cnblogs.com/kevingrace/p/5907254.html