使用binlog恢复数据
这里看下使用binlog恢复数据的思路和过程
一般重要的业务库都会定时做全备，并且打开binlog功能，mysql5.6以后就默认打开binlog了。
想要手动开启binlog，可以在配置文件中加上下面的字段：
[mysqld]
log-bin = /usr/local/var/mysql/logs/mysql-bin.log ##日志位置
expire-logs-days = 14 ##过期时间
max-binlog-size = 500M ##日志最大限制，超过会自动切割
server-id = 1
如果线上出现误删除数据的情况，我们可以采用下面的思路进行数据恢复：
1：创建临时库，并用最近的一个全备去恢复
2：查看binlog，找到这个全备时间点到误删除数据的点之间的操作
3：如果范围在一个binlog里，直接用指令恢复。如果范围跨越多个binlog，从最久远的那个开始恢复，直到恢复到删除数据的那个position，就能把数据控制到删除数据之前节点的状态！
mysql> show binary logs; ##查看具体binlog文件，也可以用‘show master logs；’来实现
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 2160 |
| mysql-bin.000002 | 43064 |
| mysql-bin.000003 | 3909 |
| mysql-bin.000004 | 143 |
| mysql-bin.000005 | 143 |
| mysql-bin.000006 | 2244 |
+------------------+-----------+
mysql> show binlog events; ##查新第一个binlog里的内容，很多，不截取了！
mysql> show binlog events in 'mysql-bin.000006'; ##查询指定binlog文件‘mysql-bin.000006’的内容，注意单引号！
mysql> show master status; ##查看当前正在写的binlog以及位置position
以上是查找binlog相关的指令。
倘若我们已经找到了对应binlog中的事务节点。如下：
mysql> show binlog events in 'mysql-bin.000007';
+------------------+-----+-------------+-----------+-------------+------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+------------------------------------------------------+
| mysql-bin.000007 | 4 | Format_desc | 2 | 120 | Server ver: 5.6.41-log, Binlog ver: 4 |
| mysql-bin.000007 | 120 | Query | 2 | 195 | BEGIN |
| mysql-bin.000007 | 195 | Query | 2 | 294 | use `RW`; insert into T values(2,3,4,5) |
| mysql-bin.000007 | 294 | Xid | 2 | 325 | COMMIT /* xid=25479 */ |
| mysql-bin.000007 | 325 | Query | 2 | 400 | BEGIN |
| mysql-bin.000007 | 400 | Query | 2 | 512 | use `RW`; insert into account00 values (23,45,67,89) |
| mysql-bin.000007 | 512 | Xid | 2 | 543 | COMMIT /* xid=25530 */ |
| mysql-bin.000007 | 543 | Query | 2 | 618 | BEGIN |
| mysql-bin.000007 | 618 | Query | 2 | 722 | use `RW`; insert into T values (23,45,6,789) |
| mysql-bin.000007 | 722 | Xid | 2 | 753 | COMMIT /* xid=25532 */ |
| mysql-bin.000007 | 753 | Query | 2 | 828 | BEGIN |
| mysql-bin.000007 | 828 | Query | 2 | 911 | use `RW`; delete from T |
| mysql-bin.000007 | 911 | Xid | 2 | 942 | COMMIT /* xid=25536 */ |
| mysql-bin.000007 | 942 | Rotate | 2 | 989 | mysql-bin.000008;pos=4 |
+------------------+-----+-------------+-----------+-------------+------------------------------------------------------+
这里看到，我们在828position开始的事务中删除了T表，那么恢复的节点就是从上次全备到753的position位置。
指令如下：
[root@VM-75-68 lib]# /usr/local/mysql/bin/mysqlbinlog --no-defaults --start-position=325 --stop-position=753 --database=RW /var/lib/mysql/mysql-bin.000007 | mysql -uroot -p51..dmz
这里的‘--no-defaults’参数是用来规避如下报错的：
/usr/local/mysql/bin/mysqlbinlog: unknown variable 'default-character-set=utf8'
mysqlbinlog二进制在mysql的编译目录的bin下，这里要使用绝对路径。
注意这里的--stop-position的位置不能是722,753才是这个事务提交的节点！
一般线上的二进制日志都比较大，这里具体二进制的内容可以使用如下命令读取到本地文件中：
[root@VM-75-68 lib]# mysql -uroot -p51..dmz -e "show binlog events in 'mysql-bin.000007';" > 007
这样就方便查找对应的时间或操作节点了！
由于线上的binlog很大，很多时候我们需要知道误操作的大致时间点，这样才能更快的帮助我们定位删除操作所在的position，也就能尽快的定位需要回复的position！
但是通过：
‘show binlog events in 'mysql-bin.000007';’的输出看不到关于时间的信息，因此往往还需要我们把二进制日志通过下面的方式读取下来：
[root@VM-75-68 mysql]# /usr/local/mysql/bin/mysqlbinlog --no-defaults /var/lib/mysql/mysql-bin.000007 -r test.sql
‘-r’表示输出的文件名，此时我们看下test.sql的内容：
[root@VM-75-68 mysql]# cat test.sql
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190912 1:07:05 server id 2 end_log_pos 120 CRC32 0xe569da34 Start: binlog v 4, server v 5.6.41-log created 190912 1:07:05
BINLOG '
uSl5XQ8CAAAAdAAAAHgAAAAAAAQANS42LjQxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAATTa
aeU=
'/*!*/;
# at 120
#190912 1:18:17 server id 2 end_log_pos 195 CRC32 0xcd2d1809 Query thread_id=16 exec_time=0 error_code=0
SET TIMESTAMP=1568222297/*!*/;
SET @@session.pseudo_thread_id=16/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 195
#190912 1:18:17 server id 2 end_log_pos 294 CRC32 0x60c0bec1 Query thread_id=16 exec_time=0 error_code=0
use `RW`/*!*/;
SET TIMESTAMP=1568222297/*!*/;
insert into T values(2,3,4,5)
/*!*/;
# at 294
#190912 1:18:17 server id 2 end_log_pos 325 CRC32 0x0f140a49 Xid = 25479
COMMIT/*!*/;
# at 325
#190912 1:20:59 server id 2 end_log_pos 400 CRC32 0x93250026 Query thread_id=16 exec_time=0 error_code=0
SET TIMESTAMP=1568222459/*!*/;
BEGIN
/*!*/;
# at 400
#190912 1:20:59 server id 2 end_log_pos 512 CRC32 0x17df84f4 Query thread_id=16 exec_time=0 error_code=0
SET TIMESTAMP=1568222459/*!*/;
insert into account00 values (23,45,67,89)
/*!*/;
# at 512
#190912 1:20:59 server id 2 end_log_pos 543 CRC32 0xa6ed016e Xid = 25530
COMMIT/*!*/;
# at 543
#190912 1:21:28 server id 2 end_log_pos 618 CRC32 0x618edf7e Query thread_id=16 exec_time=0 error_code=0
SET TIMESTAMP=1568222488/*!*/;
BEGIN
/*!*/;
# at 618
#190912 1:21:28 server id 2 end_log_pos 722 CRC32 0xc2bc15a5 Query thread_id=16 exec_time=0 error_code=0
SET TIMESTAMP=1568222488/*!*/;
insert into T values (23,45,6,789)
/*!*/;
# at 722
#190912 1:21:28 server id 2 end_log_pos 753 CRC32 0x70421b22 Xid = 25532
COMMIT/*!*/;
# at 753
#190912 1:21:53 server id 2 end_log_pos 828 CRC32 0x9667e2a6 Query thread_id=16 exec_time=0 error_code=0
SET TIMESTAMP=1568222513/*!*/;
BEGIN
/*!*/;
# at 828
#190912 1:21:53 server id 2 end_log_pos 911 CRC32 0x92b49dfe Query thread_id=16 exec_time=0 error_code=0
SET TIMESTAMP=1568222513/*!*/;
delete from T
/*!*/;
# at 911
#190912 1:21:53 server id 2 end_log_pos 942 CRC32 0x3753a272 Xid = 25536
COMMIT/*!*/;
# at 942
#190912 1:22:02 server id 2 end_log_pos 989 CRC32 0x4d526bbe Rotate to mysql-bin.000008 pos: 4
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
这里看起来似乎很杂乱无章，但是仔细看你会发现都是有规律的，我们单独拿一个事务出来分析：
BEGIN ##表示开始事务
/*!*/;
# at 400 ##开始的position
#190912 1:20:59 server id 2 end_log_pos 512 CRC32 0x17df84f4 Query thread_id=16 exec_time=0 error_code=0
SET TIMESTAMP=1568222459/*!*/;
insert into account00 values (23,45,67,89) ##执行的操作
/*!*/;
# at 512 ##操作结束的position
#190912 1:20:59 server id 2 end_log_pos 543 CRC32 0xa6ed016e Xid = 25530
COMMIT/*!*/;
# at 543 ##提交的position
因此，我们指定的stop-position一定要是commit之后的position，也就是这里的543！也就对应mysql界面的：
mysql-bin.000007 512 Xid 2 543 COMMIT /* xid=25530 */
这行的543位置的操作！
注意，在执行了误删除之后，在尽快的时间内执行‘flush logs；’这条指令会刷新binlog，也就是重新生成一个binlog文件，这样保证需要恢复的数据都存储在一个固定的binlog中，新的binlog会写入新的binlog中。
并且binlog的显示时间也很有意思，你在执行flush logs；的时候，生产新log的同时，截止到当前时间的上一个binlog的修改时间也会同步过来，因此，更方便我们查找某个时间段对应的binlog！
以上就是使用binlog恢复数据的一些操作，共勉！
