mysql之 binlog维护详细解析（开启、binlog相关参数作用、mysqlbinlog解读、binlog删除）

binary log 作用：主要实现三个重要的功能：用于复制，用于恢复，用于审计。

binary log 相关参数：

log_bin

设置此参数表示启用binlog功能，并指定路径名称

log_bin_index

设置此参数是指定二进制索引文件的路径与名称

binlog_format

此参数控制二进制日志三种格式：STATEMENT，ROW，MIXED

① STATEMENT模式（SBR）

每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况（如非确定函数）下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)

② ROW模式（RBR）

不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。

③ MIXED模式（MBR）

以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

binlog_row_image

此参数控制二进制日志记录内容，有三种选择full、minimal、noblob，默认值是full。

full：在“before”和“after”影像中，记录所有的列值；

minimal：在“before”和“after”影像中，仅仅记录被更改的以及能够唯一识别数据行的列值；

noblob：在“before”和“after”影像中，记录所有的列值，但是BLOB 与 TEXT列除外（如未更改）。

binlog_do_db

此参数表示只记录指定数据库的二进制日志

binlog_ignore_db

此参数表示不记录指定的数据库的二进制日志

max_binlog_cache_size

此参数表示binlog使用的内存最大的尺寸

binlog_cache_size

此参数表示binlog使用的内存大小，可以通过状态变量binlog_cache_use和binlog_cache_disk_use来帮助测试。

binlog_cache_use：使用二进制日志缓存的事务数量

binlog_cache_disk_use:使用二进制日志缓存但超过binlog_cache_size值并使用临时文件来保存事务中的语句的事务数量

max_binlog_size

Binlog最大值，最大和默认值是1GB，该设置并不能严格控制Binlog的大小，尤其是Binlog比较靠近最大值而又遇到一个比较大事务时，为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的所有SQL都记录进当前日志，直到事务结束

sync_binlog

这个参数直接影响mysql的性能和完整性

sync_binlog=0：

当事务提交后，Mysql仅仅是将binlog_cache中的数据写入Binlog文件，但不执行fsync之类的磁盘 同步指令通知文件系统将缓存刷新到磁盘，而让Filesystem自行决定什么时候来做同步，这个是性能最好的。

sync_binlog=n，在进行n次事务提交以后，Mysql将执行一次fsync之类的磁盘同步指令，同志文件系统将Binlog文件缓存刷新到磁盘。

Mysql中默认的设置是sync_binlog=0，即不作任何强制性的磁盘刷新指令，这时性能是最好的，但风险也是最大的。一旦系统绷Crash，在文件系统缓存中的所有Binlog信息都会丢失

1.开启二进制日志 

mysql>show variables like '%log_bin%'; 

+---------------------------------+-------+ 

| Variable_name | Value | 

| log_bin | OFF | --该参数用于设定是否启用二进制日志 

| log_bin_trust_function_creators | OFF | 

| sql_log_bin | ON | 

[root@mysql ~]# service mysql stop

Shutting down MySQL.... [ OK ]

[root@mysql ~]# cp /etc/my.cnf /etc/my.cnf.bak

[root@mysql ~]# vi /etc/my.cnf

说明: 在/etc/my.cnf 文件中添加 log_bin=/var/lib/mysql/binarylog/binlog 

[root@mysql ~]# mkdir -p /var/lib/mysql/binarylog

[root@mysql ~]# chown -R mysql:mysql /var/lib/mysql/binarylog 

[root@mysql mysql]# service mysql start

Starting MySQL. [ OK ]

mysql> show variables like '%log_bin%';

+---------------------------------+---------------------------------------+

| Variable_name | Value |

| log_bin | ON |

| log_bin_basename | /var/lib/mysql/binarylog/binlog |

| log_bin_index | /var/lib/mysql/binarylog/binlog.index |

| log_bin_trust_function_creators | OFF |

| log_bin_use_v1_row_events | OFF |

| sql_log_bin | ON |

6 rows in set (0.00 sec)

[root@mysql mysql]# ll /var/lib/mysql/binarylog/

total 8

-rw-rw---- 1 mysql mysql 120 May 30 16:57 binlog.000001

-rw-rw---- 1 mysql mysql 39 May 30 16:57 binlog.index

2. 切换二进制日志

mysql> flush logs; 

Query OK, 0 rows affected (0.05 sec)

total 12

-rw-rw---- 1 mysql mysql 164 May 30 17:09 binlog.000001

-rw-rw---- 1 mysql mysql 120 May 30 17:09 binlog.000002

-rw-rw---- 1 mysql mysql 78 May 30 17:09 binlog.index

3.查看 binary log 个数

mysql> show binary logs;

+---------------+-----------+

| Log_name | File_size |

| binlog.000001 | 164 |

| binlog.000002 | 164 |

4. 查看正在使用的 binary log

mysql> show master status;

+---------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

| binlog.000010 | 120 | | | |

5.查看二进制日志事件

5.1 

mysql> show binlog events in 'binlog.000010';

+---------------+-----+-------------+-----------+-------------+---------------------------------------+

| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |

| binlog.000010 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.25-log, Binlog ver: 4 |

| binlog.000010 | 120 | Query | 1 | 219 | use `test`; create table andy(id int) |

2 rows in set (0.00 sec) 

5.2

mysql> show binlog events in 'binlog.000010' from 120 limit 2; 

+---------------+-----+------------+-----------+-------------+---------------------------------------+

| binlog.000010 | 219 | Query | 1 | 298 | BEGIN |

2 rows in set (0.00 sec)

6. 用 mysqlbinlog 工具查看 二进制日志

6.1

[root@mysql ~]# mysqlbinlog /var/lib/mysql/binarylog/binlog.000016

# at 199

#170530 19:42:52 server id 1 end_log_pos 306 CRC32 0x64d982e4 

Query thread_id= exec_time=0

error_code=0

use `test`/*!*/;

SET TIMESTAMP=1496144572/*!*/;

insert into name values('陶叶')

/*!*/;

# at 306

#170530 19:42:52 server id 1 end_log_pos 337 CRC32 0xf75d46d1 

Xid = 36

COMMIT/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

内容解析：

位置> 位于文件中的位置，“at 199”说明“事件”的起点，是以第199字节开始；“end_log_pos 306”说明以第306字节结束，下一个事件将以上一个事件结束位置为起点，周而复始。

时间戳> 事件发生的时间戳：“170530 19:42:52”

事件执行时间> 事件执行花费的时间:"exec_time=0"

错误码> 错误码为：“error_code=0”

服务器的标识> 服务器的标识id：“server id 1”

6.2 用 mysqlbinlog 工具查看 指定时间戳 binlog

[root@mysql ~]# mysqlbinlog --start-datetime="2017-05-30 19:42:52" /var/lib/mysql/binarylog/binlog.000016

6.3 用 mysqlbinlog 工具查看 指定position 的binlog

[root@mysql ~]# mysqlbinlog --start-position=199 --stop-position=306 /var/lib/mysql/binarylog/binlog.000016

7. 删除 binary log

7.1 自动删除 ， my.cnf中 添加 expire_logs_days

expire_logs_days = X # X为指定天数

7.2 手动删除（ 自动在操作系统层面把 os file 删除了）

mysql> reset master; //删除master的binlog

mysql> reset slave; //删除slave的中继日志

mysql> purge master logs before '2017-05-30 18:27:00'; //删除指定日期以前的日志索引中binlog日志文件

mysql> purge master logs to 'binlog.000011'; //删除binlog.000011之前的，不包含binlog.000011

文章可以转载，必须以链接形式标明出处。

本文转自 张冲andy 博客园博客，原文链接：  http://www.cnblogs.com/andy6/p/6921308.html ，如需转载请自行联系原作者