Mysql(一) Mysql二进制日志

 

Mysql Binary Log

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

一 二进制日志文件开启方法

　　修改my.cnf文件，文件路径在/etc/my.cnf，在[mysqld]下面增加

# mysql-bin 是日志的基本名或前缀名,默认在数据目录（data）下。
log-bin=mysql-bin

二 二进制日志文件查询

#系统变量log_bin的值为OFF表示没有开启二进制日志。ON表示开启了二进制日志
mysql> show variables like 'log_bin';
#查看当前服务器所有的二进制文件
mysql> show binary logs;
mysql> show master logs;
#查看二进制文件状态
mysql> show master status;

三 二进制日志文件切换

mysql> flush logs;

　　每次重启MySQL服务也会生成一个新的二进制日志文件，相当于二进制日志切换。切换二进制日志时，在my.cnf文件中配置的二进制文件的前缀名的后缀的number会不断递增。除了这些二进制日志文件外，还生成了mysql-bin.index的文件，这个文件中存储所有二进制日志文件的清单,又称为二进制文件的索引。

四 二进制日志文件的删除 

#删除某个二进制日志之前（number）的所有二进制文件,这个命令会修改二进制索引文件（mysql-bin.index）中的索引
mysql> purge binary logs to '二进制日志名';
#删除某个时间点以前的二进制日志文件。
mysql> purge binary logs before '2018-07-05 10:10:00';
#清除7天前的二进制日志文件
mysql> purge master logs before date_sub( now( ), interval 7 day);
#清除所有的二进制日志文件（当前不存在主从复制关系）
mysq> reset master;

 　　也可以在my.cnf文件中设置expire_logs_days参数，设置自动清理，值为天数，其默认值为0,表示不启用过期自动删除功能，如果启用了自动清理功能，表示超出此天数的二进制日志文件将被自动删除，自动删除工作通常发生在MySQL启动时或FLUSH日志时。

五 二进制文件主要参数

1 log_bin_trust_function_creators

当二进制日志启用后，这个变量就会启用。它控制是否可以信任存储函数创建者，不会创建写入二进制日志引起不安全事件的存储函数。如果设置为0（默认值），用户不得创建或修改存储函数，除非它们具有除CREATE ROUTINE或ALTER ROUTINE特权之外的SUPER权限。 设置为0还强制使用DETERMINISTIC特性或READS SQL DATA或NO SQL特性声明函数的限制。 如果变量设置为1，MySQL不会对创建存储函数实施这些限制。 此变量也适用于触发器的创建。

在MySQL主从复制机器的master的数据库中创建function，如果此参数的值是默认值，则会抛出一下异常

Error Code: 1418. This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
　　原来是因为在主从复制的两台MySQL服务器中开启了二进制日志选项log-bin,slave会从master复制数据，而一些操作，比如function所得的结果在master和slave上可能不同，所以存在潜在的安全隐患。因此，在默认情况下会阻止function的创建。

有两种方法解决此问题

①.将log_bin_trust_function_creators参数设置为ON，这样一来开启了log-bin的MySQL Server便可以随意创建function。这里存在潜在的数据安全问题，除非明确的知道创建的function在master和slave上的行为完全一致。

#1 动态方式启动数据库服务器，服务器重启之后失效
mysql> set global log_bin_trust_function_creators=1;
#2 修改my.cnf文件添加log_bin_trust_function_creators=1配置。

　另外如果是在master上创建函数，想通过主从复制的方式将函数复制到slave上则也需在开启了log-bin的slave中设置上述变量的值为ON（变量的设置不会从master复制到slave上，这点需要注意），否则主从复制会报错。

②.明确指明函数的类型
1 DETERMINISTIC 不确定的
2 NO SQL 没有SQl语句，当然也不会修改数据
3 READS SQL DATA 只是读取数据，当然也不会修改数据
例如：CREATE DEFINER=`username`@`%` READS SQL DATA FUNCTION `fn_getitemclock`(i_itemid bigint,i_clock int,i_pos int) RETURNS int(11)...
这样一来相当于明确的告知MySQL服务器这个函数不会修改数据，因此可以在开启了log-bin的服务器上安全的创建并被复制到开启了log-bin的slave上。

2 sql_log_bin

此变量控制是否对二进制日志进行日志记录。默认值为1（进行日志记录）。若要更改当前会话的日志记录，请更改此变量的会话值。会话用户必须具有设置此变量的超级特权。将此变量设置为会话的0，以暂时禁止记录二进制日志，常用于想在主库上执行一些操作，但不复制到slave库上。

set sql_log_bin=0;

要慎重使用global修饰符（set global sql_log_bin=0)，这样会导致所有在Master数据库上执行的语句都不记录到binlog。

INSERT、UPDATE、DELETE的SQL语句会导致Master和Slave数据库数据不一致，要谨慎操作。

3 expire_logs_days

这个参数是用来控制binlog日志文件保留时间，超过保留时间的binlog日志会被自动删除。

4 binlog_cache_size

　这个参数是用来控制二进制日志缓冲大小，我们知道InnoDB存储引擎是支持事务的，实现事务需要依赖于日志技术，为了性能，日志编码采用二进制格式。那么，我们如何记日志呢？有日志的时候，就直接写磁盘？可是磁盘的效率是很低的，如果你用过Nginx，一般Nginx输出access log都是要缓冲输出的。因此，记录二进制日志的时候，我们是否也需要考虑Cache呢？答案是肯定的，但是Cache不是直接持久化，于是面临安全性的问题——因为系统宕机时，Cache中可能有残余的数据没来得及写入磁盘。因此，Cache要权衡，要恰到好处：既减少磁盘I/O，满足性能要求；又保证Cache无残留，及时持久化，满足安全要求。

5 max_binlog_cache_size

　　这个参数是用来控制为每个session 最大可分配的内存，在事务过程中用来存储二进制日志的缓存。

6 max_binlog_size

　　这个参数是用来控制binlog日志大小，如果二进制日志写入的内容超出给定值，日志就会发生滚动。不能将该变量设置为大于1GB或小于4096字节。 默认值是1GB。

7 binlog_checksum

　　这个参数用来处理主从复制事件校验。

　　http://blog.sina.com.cn/s/blog_53fab15a0102vodv.html

8 sync_binlog

　　此参数来控制数据库的binlog刷到磁盘上去。默认sync_binlog=0，表示MySQL不控制binlog的刷新，由文件系统自己控制它的缓存的刷新。这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。

　　如果sync_binlog>0，表示每sync_binlog次事务提交，MySQL调用文件系统的刷新操作将缓存刷下去。最安全的就是sync_binlog=1了，表示每次事务提交，MySQL都会把binlog刷下去，是最安全但是性能损耗最大的设置。这样的话，在数据库所在的主机操作系统损坏或者突然掉电的情况下，系统才有可能丢失1个事务的数据。但是binlog虽然是顺序IO，但是设置sync_binlog=1，多个事务同时提交，同样很大的影响MySQL和IO性能。虽然可以通过group commit的补丁缓解，但是刷新的频率过高对IO的影响也非常大。对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。

　　所以很多MySQL DBA设置的sync_binlog并不是最安全的1，而是100或者是0。这样牺牲一定的一致性，可以获得更高的并发和性能。

9 binlog_format

　　此参数来设置二进制日志的格式

　　从 MySQL 5.1.12 开始，可以用以下三种模式来实现：基于SQL语句的复制(statement-based replication, SBR)，基于行的复制(row-based replication, RBR)，混合模式复制(mixed-based replication, MBR)。相应地，binlog的格式也有三种：STATEMENT，ROW，MIXED。MBR 模式中，SBR 模式是默认的。

　　① Statement：每一条会修改数据的sql都会记录在binlog中。

优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。(相比row能节约多少性能与日志量，这个取决于应用的SQL情况，正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量，但是考虑到如果带条件的update操作，以及整表删除，alter表等操作，ROW格式会产生大量日志，因此在考虑是否使用ROW格式日志时应该跟据应用的实际情况，其所产生的日志量会增加多少，以及带来的IO性能问题。)

缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同 的结果。另外mysql 的复制,像一些特定函数功能，slave可与master上要保持一致会有很多相关问题(如sleep()函数， last_insert_id()，以及user-defined functions(udf)会出现问题).

　　② Row:不记录sql语句上下文相关信息，仅保存哪条记录被修改。

优点： binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题

缺点:所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容,比如一条update语句，修改多条记录，则binlog中每一条修改都会有记录，这样造成binlog日志量会很大，特别是当执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。

　　③MIXED: 是以上两种level的混合使用

　　一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种.新版本的MySQL中队row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句，还是会记录所有行的变更。

 

六 查看二进制日志文件内容

1 查看第一个binlog文件的内容

mysql>  show binlog events;

 

2查看某个特定binglog文件的内容

mysql> show binlog events in 'mysql-bin.000001';

 

3 使用mysqlbinlog命令查看二进制日志文件中的的内容

# ./mysqlbinlog /usr/local/mysql/mysql/data/mysql-bin.000001;

 

　　Running a server with binary logging enabled makes performance slightly slower. However, the benefits of the binary log in enabling you to set up replication and for restore operations generally outweigh this minor performance decrement.