二进制日志(binary log)介绍与调整
1、二进制日志(binary log)介绍
二进制日志(binary log)：记录数据库里的数据被修改。
（insert,update,delete,create,drop,alter）的相关语句；
作用：增量数据恢复和主从复制；
2、二进制日志(binary log)调整
[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（增量恢复）做主从同步的时候关闭会出现错误
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.设置参数自动删除
[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.从头删除到指定的文件位置
[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.删除指定的时间
mysql> PURGE MASTER LOGS BEFORE '2017-03-20 00:00:00';
4.删除所有，重置
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模式
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中的数据
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
[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
