二进制日志(binary log)

1、二进制日志(binary log)介绍

　　二进制日志(binary log)：记录数据库里的数据被修改。

　　（insert,update,delete,create,drop,alter）的相关语句；

　　作用：增量数据恢复和主从复制；

2、二进制日志(binary log)调整

1 2 3 4 5 6 7 8 9 10 11

[root@db01-51 ~]# mysql -S /data/3306/mysql.sock -e "show variables like '%log_bin%';" +---------------------------------+-----------------------------+ | Variable_name                   | Value                       | +---------------------------------+-----------------------------+ | log_bin                         | ON                          |记录binlog开关 | log_bin_basename                | /data/3306/oldboy-bin       | | log_bin_index                   | /data/3306/oldboy-bin.index |binlog文件 | log_bin_trust_function_creators | OFF                         | | log_bin_use_v1_row_events       | OFF                         | | sql_log_bin                     | ON                          |临时不记录binlog开关（增量恢复）某个时间点某些语句不记录binlog +---------------------------------+-----------------------------+

临时不记录binlog（增量恢复）做主从同步的时候关闭会出现错误

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

mysql> set session sql_log_bin = OFF; Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%log_bin%'; +---------------------------------+-----------------------------+ | Variable_name                   | Value                       | +---------------------------------+-----------------------------+ | log_bin                         | ON                          | | log_bin_basename                | /data/3306/oldboy-bin       | | log_bin_index                   | /data/3306/oldboy-bin.index | | log_bin_trust_function_creators | OFF                         | | log_bin_use_v1_row_events       | OFF                         | | sql_log_bin                     | OFF                         | +---------------------------------+-----------------------------+ 6 rows in set (0.00 sec)

　binlog文件切割的条件：

　　a.数据库重启自动切割binlog为新文件。

　　b.执行mysqldump -F或mysqladmin flush-logs切割binlog为新文件。

　　c.binlog文件达到1.1G，自动切割binlog为新文件。

　　d.人为配置切割及调整

删除binlog日志文件方法：

　　1.设置参数自动删除

1 2 3 4 5 6 7 8

[root@db01-51 ~]# grep expire_logs_days /data/3306/my.cnf expire_logs_days = 7 [root@db01-51 ~]# mysql -S /data/3307/mysql.sock -e "show variables like 'expire_logs_days%';" +------------------+-------+ | Variable_name | Value | +------------------+-------+ | expire_logs_days | 7 | +------------------+-------+

　　2.从头删除到指定的文件位置

1 2 3 4 5

[root@db01-51 ~]# mysql -S /data/3307/mysql.sock -e "purge binary logs to 'xusx-bin.000002';" [root@db01-51 ~]# ll /data/3307/data/ drwx------ 2 mysql mysql      4096 Mar 19 08:11 xusx -rw-rw---- 1 mysql mysql    654021 Mar 20 18:17 xusx-bin.000002　　##--->01删到02 -rw-rw---- 1 mysql mysql        32 Mar 20 23:44 xusx-bin.index

　　3.删除指定的时间

1	mysql> PURGE MASTER LOGS BEFORE '2017-03-20 00:00:00';

　　4.删除所有，重置

1 2	mysql> reset master; Query OK, 0 rows affected (0.03 sec)

MySQL binlog三种模式

二进制日志log-bin作用：

　　1、以二进制形式记录更改数据库的SQL语句（insert，update，delete，create，drop，alter等）。

　　2、用于MySQL主从复制。

　　3、增量数据备份及恢复

Row Level：

　　日志中会记录成每一行数据被修改的情况，然后在slave端再对相同的数据进行修改。

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

Statement Level(默认语句模式)

　　每一条被修改数据的sql都会记录到master的bin-log中。slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql来再次执行。

　　优点：statement level下的优点首先就是解决了row level下的缺点，不需要记录每一行数据的变化，减少bin-log日志量，节约磁盘IO，提高性能。因为他只需要记录在Master上所有执行的语句的细节，以及执行语句时候的上下文的信息。

Mixed：

　　实际上就是前两种模式的结合。在Mixed模式下，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。新版本中的Statment level还是和以前一样，仅仅记录执行的语句。新版本的MySQL中对row level模式也被做了优化，并不是所有的修改都会以row level来记录像遇到表结构变更的时候就会以statement模式来记录，如果sql语句确实就是update或者delete等修改数据的语句，那么还是会记录所有行的变更。

总结：

Statement level (默认语句模式)按照执行的SQL语句记录

　　不需要记录每行数据的变化，减少bin-log日志量，节约磁盘IO，提高性能

　　缺点：主从复制特殊功能会导致无法正常复制。存储过程、触发器、函数。

Row Level

　　按行记录日志，细致，主从复制容易保持一致

　　缺点：数据量大

mixed：智能模式

　　数据量大选择Statement Level，特殊可能引起数据不一致就选行模式

　　企业场景如何选择binlog的模式

1、互联网公司、使用MySQL的功能相对少（存储过程、触发器、函数）

　　选择默认的语句模式，Statement Level（默认）

2、公司如果用到使用MySQL的特殊功能（存储过程、触发器、函数）

　　选择Mixed模式

3、公司如果用到使用MySQL的特殊功能（存储过程、触发器、函数），又希望数据最大化，此时最好Row level模式 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

mysql> show variables like '%binlog_format%'; +---------------+-----------+ | Variable_name | Value     | +---------------+-----------+ | binlog_format | STATEMENT | +---------------+-----------+ 1 row in set (0.00 sec) mysql> set global binlog_format = 'ROW'; Query OK, 0 rows affected (0.00 sec) [root@db01-51 ~]# grep binlog_format /data/3306/my.cnf binlog_format = 'ROW' mysql> show variables like '%binlog_format%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | ROW   | +---------------+-------+ 1 row in set (0.00 sec)

修改3306中的数据

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

mysql> select * from test; +----+------+ | id | name | +----+------+ |  1 | a    | |  2 | b    | +----+------+ 2 rows in set (0.00 sec) mysql> update test set name='kk'; Query OK, 2 rows affected (0.00 sec) Rows matched: 2  Changed: 2  Warnings: 0 mysql> select * from test; +----+------+ | id | name | +----+------+ |  1 | kk   | |  2 | kk   | +----+------+ 2 rows in set (0.00 sec)

查看binlog日志文件

[root@mysql-db03 ~]# mysqlbinlog --help|grep rows
'decode-rows' decodes row events into commented

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

[root@db01-51 3306]# mysqlbinlog --base64-output='decode-rows' -v oldboy-bin.000002 # at 904 #170321  1:17:07 server id 6  end_log_pos 970 CRC32 0xb53c660d  Update_rows: table id 70 flags: STMT_END_F ### UPDATE `xusx`.`test` ### WHERE ###   @1=1 ###   @2='a' ### SET ###   @1=1 ###   @2='kk' ### UPDATE `xusx`.`test` ### WHERE ###   @1=2 ###   @2='b' ### SET ###   @1=2 ###   @2='kk' # at 970