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
|