Mysql二进制日志介绍及用法

什么是二进制日志？

二进制日志主要记录mysql数据库的变化，二进制日志包含所有更新了数据或者潜在更新了数据（如没有匹配到任何行的delete语句），语句以时间的形式保存，描述了数据的更改。二进制日志还包含执行每个更新数据库语句的时间信息，使用二进制日志的主要目的是最大可能的恢复数据库。因为二进制日志包含备份后进行的所有更新，不记录没有修改任何数据的语句。 开启二进制日志对性能的开销很小，带来的好处远大于坏处。

开启二进制日志：
数据库完成安装之后，启动之前一定把二进制日志开启了！！！！
vi /etc/my.cnf
log-bin = mysql-bin # 路径及命名，默认在data下
expire_logs_days = 10 # 过期时间,二进制文件自动删除的天数,0代表不删除
max_binlog_size = 100M # 单个日志文件大小
binlog_format = row # 配置使用哪种模式的二进制日志
之后重启mysql服务即可。
Mysql语句的停止和开启：
set sql_log_bin = 0 # 停止二进制日志功能
set sql_log_bin = 1 # 开启二进制日志功能

查看mysql的二进制日志信息：

查看当前服务器所有二进制日志文件：

或者：

查看当前二进制日志状态：

重置二进制日志（删除所有）：
mysql> reset master;
部分删除：
purge binary logs to ‘DB-Server-bin.000002’; # 删除该日志文件之前的所有日志文件
purge binary logs before ‘2017-03-10 10:10:00’; # 清除该时间点以前的所有日志文件
purge master logs before date_sub( now( ), interval 7 day); # 清除7天前的日志文件
查看当前binlog文件的内容（也可以指定日志文件查看）：
mysql> show binlog events;
指定后：
mysql> show binlog events in ‘mysqlbin.000003’;
导出二进制日志：
按sql文件导出：
mysqlbinlog mysqlbin.000001 > 1.sql
按时间导出：
mysqlbinlog --start-datetime=“2019-06-24 20:19:02” --stop-datetime=“2019-06-24 20:19:35” mysqlbin.000001 >time.log
按照位置导出：

mysqlbinlog --start-position=106 --stop-position=186 mysqlbin.000001 > 101.log
进制日志不导出直接查看：
因为二进制日志是以二进制方式存储的，不能直接读取，需要使用mysql自带的mysqlbinlog工具来进行查看。
mysqlbinlog mysql-bin.000001 –d test2
对应的一些参数说明：

默认其形式为filename.number，number的形式为000001、000002等。每次重启mysql服务或运行mysql> flush logs;都会生成一个新的二进制日志文件，这些日志文件的number会不断地递增。除了生成上述的文件外还会生成一个名为filename.index的文件。这个文件中存储所有二进制日志文件的清单又称为二进制文件的索引。

使用二进制日志进行恢复：

格式：
mysqlbinlog [option] filename | mysql -uuser -ppasswd
option的参数主要有两个 --start-datetime --stop-datetime 和 start-position --stop-position ,前者指定恢复的时间点，后者指定恢复的位置（位置指的是二进制文件中 # at 580 580就是位置）。原理就是把记录的语句重新执行了一次，如果恢复了两次。会产生重复数据。
事例：
(1)完整恢复，先执行上次完整备份恢复，再执行自上次备份后产生的二进制日志文件恢复
mysql localhost mysql-bin.000001 | mysql -uroot -p
这样数据库就可以完全的恢复到崩溃前的完全状态
(2)基于时间点的恢复，如果确认误操作时间点为2018-03-20 10:00:00执行如下
mysqlbinlog --stop-date=‘2018-03-02 9:59:59’ mysql-bin.000001 | mysql -uroot -p
然后跳过误操作的时间点，继续执行后面的binlog
mysqlbinlog --start-date=‘2018-03-20 10:01:00’ mysql-bin.000001 | mysql -uroot -p
其中–stop-date=‘2018-03-20 9:59:59’ 和 --start-date=‘2018-03-20 10:01:00’
取两时间点
mysqlbinlog --start-datetime=“2018-03-20 11:25:56” --stop-datetime=“2018-03-20 14:20:10” mysql-bin.000001 | mysql -u root -p
#注：其中的时间是你误操作的时间，而且这个时间点还可能涉及到的不只是误操作，也有可能有正确的操作也被跳过去了。那么执行位置恢复
基于位置恢复，通过查看日志文件信息，确认6259-6362为误操作点
mysqlbinlog --stop-position=6259 mysql-bin.000001 | mysql -uroot -p #从1开始至6259的事件读，不包括6259事件
mysqlbinlog --start-position=6363 mysql-bin.000001 | mysql -uroot -p #从6259的事件开始读
取两事件点
mysqlbinlog --start-position=5786 --stop-position=6254 mysql-bin.000001 | mysql -uroot -p
到此为止基本就可以恢复被误删的数据了。
当然我们平时还是要管理好数据库权限，避免萌新们误删数据带来不必要的麻烦。当我们操作生产环境上的正式数据库时候一点要在操作之前先备份，提前做好准备。

二进制日志的三种模式：
二进制日志三种格式：STATEMENT，ROW，MIXED，由参数binlog_format控制

1、STATEMENT模式（SBR）
每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况（如非确定函数）下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)

2、ROW模式（RBR）
不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。优点就是能够完全还原或者复制日志被记录时的操作。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。IO压力大，性能消耗大。

3、MIXED模式（MBR）
Mixed格式=statement+row： mysql默认采用statement格式进行二进制日志文件的记录，但是在一些情况下会使用row格式，可能使用row格式的情况有：
1）表的存储引擎为NDB，这时对表的DML操作都会以row格式记录
2）使用了uuid(),user(),current_user(),found_rows(),row_count()等不确定函数
3）使用了insert delay语句
4）使用了用户自定义函数UDF
5）使用了临时表
看上去这种方式最好，但是在生产环境中为了保险起见，一般都会使用row模式。