一、mysqlbinlog简介

binlog又叫二进制日志文件,它会将mysql中所有修改数据库数据的Query以二进制的形式记录到日志文件中,如:create,insert,drop,update;(对于select操作则不会被记录到binlog里,因为它并没有修改数据库的数据)。binlog一般存储在数据目录下,并且命名为:mysql-bin.***(这个可以在配置文件中修改my.cnflog-bin=mysql-bin,就是文件名的前缀;mysqld在每个 binlog 名后面添加一个数字扩展名。每次启动服务器或刷新日志时增加文件的大小大于max_binlog_size,一个事务不会被拆分开)。

binlog主要是用于保证数据完整的,如主从备份,通过从binlog文件中读取操作来在salve机上进行同样的操作,保证主从备份,当然不可能每次都从开始的地方redo,所以每条记录都有一个时间截TIMESTAMP

二、binglog的类型

binlog的格式有三种,这也反应了mysql的复制技术:基于SQL语句的复制(statement-based replication, SBR),基于行的复制(row-based replication, RBR),混合模式复制(mixed-based replication, MBR)。相应地,binlog的格式也有三种:STATEMENTROWMIXED

mysql>showvariables like 'binlog_format'    #查看binlog的格式

使用mysqlbinlog解析的binlog

MIXEDSTATEMENT):

# at 193(开始位置)

#110708 10:03:06(时间截) server id(产生该事件的服务id 1  end_log_pos(日志的结束位置) 280  Query(事件类型)  thread_id=10    exec_time=0     error_code=0

SETTIMESTAMP=1310090586/*!*/;

insert into tvalues(17)

/*!*/;

 

ROW模式:

BEGIN

/*!*/;

# at 174

# at 214

#110708 10:49:22server id 1  end_log_pos 214   Table_map: `test`.`t` mapped to number 14

#110708 10:49:22server id 1  end_log_pos 248   Write_rows: table id 14 flags: STMT_END_F

BINLOG '

MnAWThMBAAAAKAAAANYAAAAAAA4AAAAAAAEABHRlc3QAAXQAAQMAAQ==

MnAWThcBAAAAIgAAAPgAAAAAAA4AAAAAAAEAAf/+MgAAAA==

'/*!*/;

# at 248

#110708 10:49:22server id 1  end_log_pos 317   Query  thread_id=1     exec_time=0     error_code=0

SETTIMESTAMP=1310093362/*!*/;

COMMIT

STATEMENT是基于sql语句级别的binlog,每一条修改数据的sql都会被保存到binlog里;ROW是基于行级别的,他会记录每一行记录的变化,就是将每一行的修改都记录到binlog里面,记录的非常详细,但sql语句并没有在binlog,replication里面也不会因为存储过程触发器等造成Master-Slave数据不一致的问题,但是有个致命的缺点日志量比较大.由于要记录每一行的数据变化,当执行update语句后面不加where条件的时候或alter table的时候,产生的日志量是相当的大。MIXED:在默认情况下是statement,但是在某些情况下会切换到row状态,如当一个DML更新一个ndb引擎表,或者是与时间用户相关的函数等。在主从的情况下,在主机上如果是STATEMENT模式,那么binlog就是直接写now(),然而如果这样的话,那么从机进行操作的时间,也执行now(),但明显这两个时间不会是一样的,所以对于这种情况就必须把STATEMENT模式更改为ROW模式,因为ROW模式会直接写值而不是写语句(该案例是错误的,即使是STATEMENT模式也可以使用now()函数,具体原因以后再分析)。同样ROW模式还可以减少从机的相关计算,如在主机中存在统计写入等操作时,从机就可以免掉该计算把值直接写入从机。

以上类型的优缺点:

SBR 的优点:
历史悠久,技能成熟
binlog
文件较小
binlog
中包含了所有数据库修改信息,可以据此来审核数据库的安全等情况
binlog
可以用于实时的还原,而不仅仅用于复制
主从版本可以不一样,从服务器版本可以比主服务器版本高
SBR 
的缺点:
不是所有的UPDATE语句都能被复制,尤其是包含不确定操作的时候。
调用具有不确定因素的 UDF 时复制也可能出疑问
运用以下函数的语句也不能被复制:
* LOAD_FILE()
* UUID()
* USER()
* FOUND_ROWS()
* SYSDATE() (
除非启动时启用了 –sysdate-is-now 选项)
INSERT … SELECT 
会产生比 RBR 更多的行级锁
复制须要执行 全表扫描(WHERE 语句中没有运用到索引) UPDATE 时,须要比 RBR 请求更多的行级锁
对于有 AUTO_INCREMENT 字段的 InnoDB表而言,INSERT 语句会阻塞其他 INSERT 语句
对于一些复杂的语句,在从服务器上的耗资源情况会更严重,而 RBR 模式下,只会对那个发生变化的记录产生影响
存储函数(不是存储流程 )在被调用的同时也会执行一次 NOW() 函数,这个可以说是坏事也可能是好事
确定了的 UDF 也须要在从服务器上执行
数据表必须几乎和主服务器保持一致才行,否则可能会导致复制出错
执行复杂语句如果出错的话,会消耗更多资源

RBR 的优点:
任何情况都可以被复制,这对复制来说是最安全可靠的
和其他大多数数据库系统的复制技能一样
多数情况下,从服务器上的表如果有主键的话,复制就会快了很多
复制以下几种语句时的行锁更少:
* INSERT … SELECT
包含 AUTO_INCREMENT 字段的 INSERT
没有附带条件或者并没有修改很多记录的 UPDATE  DELETE 语句
执行 INSERTUPDATEDELETE 语句时锁更少
从服务器上采用多线程来执行复制成为可能
RBR 
的缺点:
binlog 
大了很多
复杂的回滚时 binlog 中会包含大量的数据
主服务器上执行 UPDATE 语句时,所有发生变化的记录都会写到 binlog 中,而 SBR 只会写一次,这会导致频繁发生 binlog 的并发写疑问
UDF 
产生的大 BLOB 值会导致复制变慢
不能从 binlog 中看到都复制了写什么语句(加密过的)
当在非事务表上执行一段堆积的SQL语句时,最好采用 SBR 模式,否则很容易导致主从服务器的数据不一致情况发生
另外,针对系统库 mysql 里面的表发生变化时的处理准则如下:
如果是采用 INSERTUPDATEDELETE 直接操作表的情况,则日志格式根据 binlog_format 的设定而记录
如果是采用 GRANTREVOKESET PASSWORD 等管理语句来做的话,那么无论如何 都采用 SBR 模式记录。

MBR融合了两种记录模式的优点。故个人比较推荐使用MBR模式。

、简单的使用binlog及查看修改其类型

show binary logs;    #显示binlog文件

 purge binary logsto 'mysql-bin.**'  #删除到**文件

 bin/mysqlbinlog binlogfile    #解析binlog文件

具体使用可以查看帮助文件,也可以参考官方用法:

http://dev.mysql.com/doc/refman/5.1/zh/client-side-scripts.html#mysqlbinlog

利用binlog恢复数据:

bin/mysqlbinlog  --start-datetime='2011-7-7 18:0:0'--stop-datetime='2011-7-7 20:07:13' data/mysql-bin.000008 |mysql -u root

查看及修改binlog的类型可以通过查看my.cnf文件,如混合型的设置如下:

log-bin=mysql-bin

#binlog_format=STATEMENT
      #binlog_format=ROW

binlog_format=mixed

另外也可以通过查看binlog日志文件的内容,具体上面已进行讲解。 也可以通过查看sql状态来查看:

mysql> show variables like '%log%';
+-----------------------------------------+--------------------------------+
| Variable_name                           | Value                          |
+-----------------------------------------+--------------------------------+
| back_log                                | 50                             |
| binlog_cache_size                       | 32768                          |
| binlog_direct_non_transactional_updates | OFF                            |
| binlog_format                           | MIXED

 

不复启mysql重新加载my.cnf文件的情况下,修改mysqlbinlog类型的方法:

mysql> SET SESSION binlog_format = 'STATEMENT';
mysql> SET SESSION binlog_format = 'ROW';
mysql> SET SESSION binlog_format = 'MIXED';
mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED'; 

每个binlog的开始都是由4个字节:fe 62 69 6e,后面三个字节就是bin

然后接下来的就是一条记录的内容它包括:Common-Header,这部分不同版本的大小不一样,4.0以上的都是19个字节。在这个之后就是BODY

Common-Header格式:(单位:字节)

mysqlbinlog的日志类型_数据库数据 binlog 日志类型

Timestamp:1970开始

Type:log event typeFORMAT_DESCRIPTION_EVENTQUERYLOAD_EVENT等,其中每个binlog的第一条记录的类型都是FORMAT_DESCRIPTION_EVENT,它记录了该binlog的相关信息,如版本,这些信息对于后序分析binlog记录是有用的,所以对于任务要读取binlog的内容的工具都必须先读取第一条记录。QUERY包括我们经常操作的如:create,drop,update,insert等。

Server_id:创建这个事件的server id。防止循环主从导致的主机被从写。The master's server id (is preserved in therelay log; used to prevent from infinite loops in circular replication).

Total_size:该记录的大小,包括common_headerbody

End_log_pos:此下一条记录的开始位置。也是此条记录结束位置的上一个字节。

Flag:标志位。

 

QUERY类型的记录:

QUERY类型的记录除了开始的common-header之外,在body的开头是一个Post-header,然后之后才是真正的body内容。

Query Post-Header:(单位字节)

mysqlbinlog的日志类型_数据库数据 binlog 日志类型_02

Thread_idis used to distinguish temporary tables that belong to differentclients.

Exec_timeThe time from whenthe query started to when it was logged in the binlog, in seconds.QUERY到达到这个binlog事件生成的时间间隔。

Db_len:当前数据库的名称长度。

Error_code:执行出错的错误号。

来源:http://www.361way.com/mysqlbinlog-type/934.html