MySQL二进制日志

MySQL的二进制日志（binary log）是一个二进制文件，主要用于记录修改数据库或有可能引起数据变更的SQL。二进制日志（binary log）中记录了对MySQL数据库执行更改的所有操作，并且记录了语句发生时间、执行时长、操作数据等其它额外信息，但是它不记录select、show等那些不修改数据的SQL语句。二进制日志主要用于数据库恢复和主从复制，以及审计操作。

1.查看二进制日志是否已开启
进入mysql：show variables like 'log_bin';

2.查看当前服务器所有的二进制文件：
show binary logs;等同于show master logs;

3.查看当前二进制日志文件状态：
show master status;

二进制日志开启的方法：
查看系统变量log_bin，值为OFF，表示没有开启二进制日志。
如果需要开启，则必须在my.cnf中[mysqld] 下添加log_bin=[DIR\[filename]]，DIR参数指定二进制文件的存储路径；filename参数指定二进制文件的文件名。其中filename可以任意指定，系统变量log_bin是静态参数，不能动态修改。

在my.cnf文件中添加log_bin=mysql_bin_log，重启MySQL，log_bin即变为了ON
指定二进制日志的路径位置：log_bin=/myslq/bin_log/mysql_binlog

二进制日志切换方法：
使用flush logs切换二进制日志，也可用mysqladmin flush-logs -u root -p进行切换
show master status； flush logs；
每次重启MySQL服务也会生成一个新的二进制日志文件，相当于二进制日志切换。切换二进制日志时，可以看到number不断递增。另外，除了二进制日志文件，还能看到生成了一个DB-Server-bin.index的文件，这个文件中存储所有二进制日志文件的清单又称为二进制文件的索引。

二进制日志删除方法：
二进制日志的删除可以通过命令手工删除，也可以设置自动清理。
1.purge binary logs to xxx; 表示删除某个日志之前的所有二进制日志文件。这个命令会修改index中相关数据
purge binary logs to 'mysql_bin_log_000003';
2.清除某个时间点以前的二进制日志文件。
purge binary logs before '2017-7-18 10:10:00';
3.清除7天前的二进制日志文件
purge msater logs before date_sub(now(),interval 7 day);
4.清除所有二进制日志文件（当前不存在主从复制关系）
reset master;
5.我们可以设置expire_logs_days参数，设置自动清理，其默认值为0，表示不启用过期自动删除功能，如果启用了自动清理功能，表示超出此天数的二进制日志文件将被自动删除，自动删除工作通常发生在MySQL启动时或flush日志时。
show variables like 'expire_logs_days';
设置：set global expire_logs_days = 6;

二进制日志相关参数：
1.系统变量log_bin_basename是MySQL5.6.2开始引入的。它表示二进制日志文件名。默认值为datadir+‘/’+hostname+'-bin'。该参数不需要设置，也不能在my.cnf中设置，否则会报错。
show variables like 'log_bin_basename';
2.系统变量log_bin_index是MySQL5.6.4开始引入的，它表示二进制日志的索引文件，该参数可以在my.cnf中设置。
show variables like 'log_bin_index';
3.系统变量log_bin_trust_function_creators,默认为OFF，这个参数开启会限制存储过程、Function、触发器的创建。
show variables like 'log_bin_trust_function_creators';
4.系统变量sql_log_bin用不控制会话级别二进制日志功能的开启或关闭，默认为ON，表示启用二进制日志功能。
show variables like 'sql_log_bin';
5.系统变量expire_logs_days,之前已有介绍
6.系统变量binlog_cache_size表示为每个客户端分配binlog_cache_size大小的缓存，默认值32768.二进制日志缓存使用的前提条件是服务器端使用了支持事务的引擎以及开启了bin log功能，它是MySQL用来提高binlog的效率而设计的一个用于短时间内临时缓存binlog数据的内存区域。如果我们的数据库事务较多或多事务语句，写入量比较大，可适当调高binlog_cache_size。同时，我们可以通过binlog_cache_use以及binlog_cache_disk_use来分析设置的binlog_cache_size是否足够，是否有大量的binlog_cache由于内存大小不够而使用零时文件来缓存了。
show cariables like 'binlog_cache_size';
show status like 'binlog%'; 通过查看binlog_cache_use和binlog_cache_disk_use来判断binlog_cache_size是否需要调整。
7.系统变量ma_binlog_cache_size二进制日志能够使用的最大cache内存大小。当执行多语句事务时，max_binlog_cache_size如果不够大，系统可能会报出“Multi-statement transaction required more than ‘max_binlog_cache_size’ bytes of storage”的错误。
show variables like 'max_binlog_cache_size';
8.系统变量max_binlog_stmt_cache_size，max_binlog_cache_size针对事务语句，max_binlog_stmt_cache_size针对非事务语句，当我们发现Binlog_cache_disk_use或者Binlog_stmt_cache_disk_use比较大时就需要考虑增大cache的大小
show variables like 'max_binlog_stmt_cache_size';
9.系统变量max_binlog_size，表示二进制日志的最大值，一般设置为512MB或1GB，但不能超过1GB。该设置并不能严格控制二进制日志的大小，遇到比较大事务时，为了保证事务的完整性，不可能做切换日志的动作，只能将事务的所有SQL都记录进当前日志，直到事务结束。
show variables like 'max_binlog_size';
10.系统变量binlog_checksum用作复制的主从校验。NONE表示不生成checksum，CRC-32表示使用这个算法做校验。
show variables like 'binlog_checksum';
11.系统变量sync_binlog，这个参数对于MySQL系统来说很重要，它不仅影响二进制日志文件对MySQL所带来的性能损耗，而且还影响到 MySQL中数据的完整性。
sync_binlog=0，当事务提交后，Mysql仅仅是将binlog_cache中的数据写入binlog文件，但不执行fsync之类的磁盘同步指令通知文件系统将缓存刷新到磁盘，而是让Filesystem自行决定什么时候来做同步。MySQL中默认的设置是 sync_binlog=0，即不作任何强制性的磁盘刷新指令，这个设置性能是最好的，但风险也是最大的。一旦系统崩溃（Crash），在文件系统缓存中的所有二进制日志信息都会丢失。从而带来数据不完整问题。
  sync_binlog=n，在进行n次事务提交以后，Mysql将执行一次fsync之类的磁盘同步指令，同时文件系统将Binlog文件缓存刷新到磁盘。
可以适当的调整sync_binlog， 在牺牲一定的一致性下，获取更高的并发和性能。
show variables like 'sync_binlog';
12.系统变量binlog_format指定二进制日志的类型。分别有STATEMENT、ROW、MIXED三种值。MySQL 5.7.6之前默认为STATEMENT模式。MySQL 5.7.7之后默认为ROW模式。这个参数主要影响主从复制。
show variables like 'binlog_format';

查看二进制日志内容：
1.使用show binlog events方式可以获取当前以及指定binlog的日志，不适宜提取大量日志。
show binlog events[IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
show binlog events in 'mysql_bin_log.000002';
show binlog events in 'DB-Server-bin.000012' from 336;  表示从Pos为336那一条数据开始显示
2.使用mysqlbinlog命令查看日志内容（适宜批量提取日志）
system mysqlbinlog 路径/mysql_bin_log.000002；查看日志内容
mysqlbinlog 路径/mysql_bin_log.000002> test.sql；  将内容输出到test.sql