mysql相关（二）、binlog
一、MySQL的二进制日志binlog可以说是MySQL最重要的日志，它记录了所有的DDL和DML语句（除了数据查询语句select）,以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。
一般来说开启binlog日志大概会有1%的性能损耗。但开启后可以用来恢复数据。
二、开启binlog
show variables like 'log_%'; #查看binlog是否开启，log_bin值为ON 则已开启
如未开启，则可以按下面方法开启;
编辑打开mysql配置文件/etc/my.cnf在[mysqld]区块添加
log-bin=mysql-bin(也可指定二进制日志生成的路径，如：log-bin=/opt/Data/mysql-bin) server-id=1 # id值唯一
binlog-format = ROW #选择行模式
binlog_format=MIXED(加入此参数才能记录到insert语句)
配置好后，重启mysql即可。
三、常用binlog命令
1、 查看所有binlog日志列表
show master logs;
2、 查看master状态，即最后（最新）一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值。
show master status;
3、flush 刷新log日志，自此刻开始产生一个新编号的binlog日志文件;
flush logs;
注意：每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqlddump备份数据时加-F选项也会刷新binlog日志；
4、重置（清空）所有binlog日志
reset master;
四、查看binlog内容
binlog为二进制文件，查看内容必须使用自带的mysqlbinlog命令查看
使用mysqlbinlog命令查看binlog日志内容，下面截取其中的一个片段分析分析：
解释：
server id 1:数据库主机的服务号
end_log_pos 796 :sql结束时的pos节点
thread_id=11:线程号
也可以根据时间查看
mysqlbinlog --no-defaults mysql-bin.000720 --start-datetime="2018-09-12 18:45:00" --stop-datetime="2018-09-12:18:47:00"
上面这种办法读取出binlog日志的全文内容比较多，不容易分辨查看到pos点信息，下面介绍一种更为方便的查询命令：
mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
参数解释：
a、IN 'log_name':指定要查询的binlog文件名（不指定就是第一个binlog文件）
b、FROM pos:指定从哪个pos起始点开始查起（不指定就是从整个文件首个pos点开始算）
c、LIMIT【offset】：偏移量(不指定就是0)
d、row_count :查询总条数（不指定就是所有行）
2、上面这条语句可以将指定的binlog日志文件，分成有效事件行的方式返回，并可使用limit指定pos点的起始偏移，查询条数！
a、查询第一个最早的binlog日志：
show binlog events\G;
b、指定查询mysql-bin.000002这个文件
show binlog events in 'mysql-bin.000002'\G;
c、指定查询mysql-bin.000002这个文件，从pos点:624开始查起：
show binlog events in 'mysql-bin.000002' from 624\G;
d、指定查询mysql-bin.000002这个文件，从pos点:624开始查起，查询10条（即10条语句）
show binlog events in 'mysql-bin.000002' from 624 limit 10\G;
e、指定查询 mysql-bin.000002这个文件，从pos点:624开始查起，偏移2行（即中间跳过2个）查询10条（即10条语句）。
show binlog events in 'mysql-bin.000002' from 624 limit 2,10\G;
五、使用binlog恢复数据
一般mysql数据都会定时备份，比如每天凌晨5点备份一次：
mysqldump -utan -p***** -d --databases xxx > /mysql_bak/dump.sql #-d只备份表结构
mysqldump -utan -p***** -F -R --databases xxx > /mysql_bak/$DATE.sql #备份表数据
其中在全备份的时候使用了-F选项，那么当数据备份操作刚开始的时候系统就会自动刷新log,这样就会自动产生一个新的binlog日志，这个新的binlog日志就会用来记录备份之后的数据库'增删改操作'。
-R:备份存储过程等
这样如果在早上9点上班后悲剧发生了，不小心删了库。。。
这个时候千万不要慌张，既然悲剧已经发生，尽快弥补
首先，通知相关人员知悉此事
然后，停止业务，避免新的数据产生
接着，登录服务器查看最后一个binlog文件，比如mysql-bin.000003 然后备份一下，这个是最重要的文件，不能再有闪失了，然后数据库内执行flush logs;生成一个新的日志文件，
然后，读取binlog文件，查看删库的时间点
mysql> show binlog events in '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 | 123 | Server ver: 5.7.27-log, Binlog ver: 4 | | mysql-bin.000003 | 123 | Previous_gtids | 1 | 154 | | | mysql-bin.000003 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000003 | 219 | Query | 1 | 298 | BEGIN | | mysql-bin.000003 | 298 | Intvar | 1 | 330 | INSERT_ID=22 | | mysql-bin.000003 | 330 | Query | 1 | 474 | use `data`; insert into `user_info` (sex,age) values (0,77777),(0,88888),(0,99999) | | mysql-bin.000003 | 474 | Xid | 1 | 505 | COMMIT /* xid=8138 */ | | mysql-bin.000003 | 505 | Anonymous_Gtid | 1 | 570 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000003 | 570 | Query | 1 | 670 | create database `xxxxx` | | mysql-bin.000003 | 670 | Anonymous_Gtid | 1 | 735 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000003 | 735 | Query | 1 | 827 | drop database data | | mysql-bin.000003 | 827 | Anonymous_Gtid | 1 | 892 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000003 | 892 | Query | 1 | 989 | create database `qqqq` | +------------------+-----+----------------+-----------+-------------+------------------------------------------------------------------------------------+ 13 rows in set (0.00 sec)
通过分析发现，删库pos点是827，只要回复到相应pos点之前即可。
先用备份文件恢复5点之前的数据
mysql> source /root/dump.sql #恢复表结构 source /root/mysql.2019-08-01.mysql; #恢复上次dump备份的数据
此时恢复了截止凌晨5:00前的备份数据了，接着从binlog中恢复5:00到删库之间的数据
恢复格式如下：
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=ops指定只恢复ops数据库(一台主机上往往有多个数据库，只限本地log日志)
因为我们删库pos点是827，上一条sql语句pos点是735，所以可以执行
mysqlbinlog mysql-bin.000003 --stop-position=735 --database=mysqlbinlog mysql-bin.000003 --stop-position=735 --database=data | mysql -uroot -p -vdata | mysql -uroot -p -v
切记不要把结束点写成827，否则会执行827的语句再次删库
因为加了--database=data因此不会恢复二进制日志中关于其他库的相应操作，如果需要恢复其他库的相应操作，则再加上--database=xxx即可
还可以按时间区间，按pos点区间恢复，步骤原理大同小异。
六、总结：
所谓恢复，就是让mysql将保存在binlog日志中指定段落区间的sql语句逐个重新执行一次而已。
