mysql二进制日志文件_mysql 二进制日志文件

一、二进制日志文件介绍

二进制日志以一种更有效的格式，并且是事务安全的方式包含更新日志中可用的所有信息。

二进制日志包含了所有更新了数据或者已经潜在更新了数据(例如，没有匹配任何行的一个DELETE)的所有语句。语句以“事件”的形式保存，它描述数据更改。

注释：二进制日志已经代替了老的更新日志，更新日志在MySQL 5.1中不再使用。

二进制日志还包含关于每个更新数据库的语句的执行时间信息。它不包含没有修改任何数据的语句。如果你想要记录所有语句(例如，为了识别有问题的查询)，你应使用一般查询日志。参见5.11.2节，“通用查询日志”。

二进制日志的主要目的是在恢复使能够最大可能地更新数据库，因为二进制日志包含备份后进行的所有更新。

二进制日志还用于在主复制服务器上记录所有将发送给从服务器的语句。参见第6章：MySQL中的复制。

运行服务器时若启用二进制日志则性能大约慢1%。但是，二进制日志的好处，即用于恢复并允许设置复制超过了这个小小的性能损失。

二、二进制日志文件格式

输入以下命令,查看二进制日志文件

mysql>show binlog events in 'log.000005'  \G;

显示如下内容：

*************************** 1. row ***************************

Log_name: log.000007

Pos: 4

Event_type: Format_desc

Server_id: 1

End_log_pos: 106

Info: Server ver: 5.1.61-community-log, Binlog ver: 4

*************************** 2. row ***************************

Log_name: log.000007

Pos: 106

Event_type: Query

Server_id: 1

End_log_pos: 177

Info: BEGIN

*************************** 3. row ***************************

Log_name: log.000007

Pos: 177

Event_type: Query

Server_id: 1

End_log_pos: 285

Info: use `cms`; INSERT INTO `xxxx` (`xxx`) VALUES ('2222')

*************************** 4. row ***************************

Log_name: log.000007

Pos: 285

Event_type: Xid

Server_id: 1

End_log_pos: 312

Info: COMMIT /* xid=37 */

*************************** 5. row ***************************

Log_name: log.000007

Pos: 312

Event_type: Query

Server_id: 1

End_log_pos: 383

Info: BEGIN

*************************** 6. row ***************************

Log_name: log.000007

Pos: 383

Event_type: Query

Server_id: 1

End_log_pos: 490

Info: use `cms`; INSERT INTO `xxxx` (`xxx`) VALUES ('666')

*************************** 7. row ***************************

Log_name: log.000007

Pos: 490

Event_type: Xid

Server_id: 1

End_log_pos: 517

Info: COMMIT /* xid=42 */

*************************** 8. row ***************************

Log_name: log.000007

Pos: 517

Event_type: Query

Server_id: 1

End_log_pos: 588

Info: BEGIN

*************************** 9. row ***************************

Log_name: log.000007

Pos: 588

Event_type: Query

Server_id: 1

End_log_pos: 695

Info: use `cms`; INSERT INTO `xxxx` (`xxx`) VALUES ('777')

*************************** 10. row ***************************

Log_name: log.000007

Pos: 695

Event_type: Xid

Server_id: 1

End_log_pos: 722

Info: COMMIT /* xid=44 */

*************************** 11. row ***************************

Log_name: log.000007

Pos: 859

Event_type: Rotate

Server_id: 1

End_log_pos: 896

Info: log.000008;pos=4

10 rows in set (0.00 sec)

ERROR:

No query specified

解读：

一个二进制日志文件是由一序列的事件组成，下面是一个事件的格式内容

*************************** 1. row ***************************

Log_name: log.000007

Pos: 588

Event_type: Query

Server_id: 1

End_log_pos: 695

Info: use `cms`; INSERT INTO `xxxx` (`xxx`) VALUES ('777')

log_name： 事件所在的二进制日志文件

Server_id：这是创建事件的服务器id

Pos：这是事件在文件的开始位置，也是事件开始的第一个字节

Event_type：事件类型，比如每个日志文件的头部都必须有 Format_desc 事件，Query 事件，一般的DML,DDL 都是Query 事件类型，比如insert,update,delete,create,alter等等，文件尾部也基本上会有日志轮换事件,Rotate,其Info: log.000008;pos=4，下一个日志文件log.000008: 位置4

End_log_pos:这是事件在文件中的结束位置，也是下一个事件的开始位置，所以此事件的字节范围在pos 至End_log_pos - 1

Info:这是事件的信息详细，不同事件显示的内容不一样，比如query(insert), 事件的信息记录use `cms`; INSERT INTO `xxxx` (`xxx`) VALUES ('777')，记录的是SQL语句

二进制日志文件都以Format_desc 事件开始，以日志轮换事件Rotate 结束

三、日志文件事件

mysql 为了记录数据库全部的更新，变化，所以记录当前发生的所有情况信息，即记录发生变化的整个上下文信息，

举个例简单例子，比如插入insert ,一般info 会有use ‘cms’ 使用数据库，再比如 set @values = 45; insert into cs values(@values);

sql 语句中使用了变量，日志文件中必有定义@values 变量的User_var 事件

下面列举以上下文件事件

1、user_var : 记录单个用户定义的变量名及变量值

2、rand：记录调用rand 函数的随机数种子

3、intvar：如果插入是auto_increment 字段，或调用last_insert_id 函数时，该事件记录下一个自增的数值，比如当前自增 1，将会记录 2;

上下文事件类型待续。。。。

四、二进制日志文件相关配置

#二进制日志文件的存放路径

log-bin=D:\data\mysql\Master\log.log

#二进制日志文件的索引文件路径，如果log-bin 选择的值为log 或，log-bin-index 空，则索引文件名为log.index,

log-bin-index=D:\data\mysql\Master\log.index

#需要记录更新的数据库名

binlog-do-db

#忽略某个特定的数据库的变化,如果binlog-do-db，binlog-ignore-db  都设置了值，最后启作用的只有binlog-do-dbbinlog-ignore-db

#binlog 需要保存的天数，即指定的数值早的文件将从文件系统中删除，这个选项默认是0，即永远不删除

expire-log-days=days

#二进制日志的缓冲区字节大写，即内存的字节大小，二进制日志开始保存在内存中，当满足条件(sync-binlog)后再写入log 的磁盘文件

#如果使用许多大型事务，增大此选项能提高性能，但意味这其他部分功能得到的内存就相对减少，这可能导致性能下降，需要综合考虑

binlog-cache-size=bytes

#最大二进制日志缓冲区，一般大型事务需要的内存比较大，有可能使其他线程性能下降，如果大于此选项设置的值，则语句会出错并被终止

max-binlog-cache-size=bytes

#log-bin二进制日志文件的大小，当文件的大小超过此选项值时，二进制日志文件被轮换，产生新的日志文件保存新事务，事务不会被截断在不同文件中，同一个事务 #产生内容都将会被写入同一个二进制文件中max-binlog-size=bytes #指定什么情况下调用fdatasync(2) 函数，将二进制日志文件写入磁盘，比如 数值 为3，每提交3个事务，将写入一次磁盘，如果数值为0，表示永远不会调用fdatasync(2) 函数，数值为1，是最安全的设置，但也是性能最差的，(程序里面有批量操作，一定不能打开sync-binlog，否则会引起性能极大下降。) sync-binlog=2