binlog文件分析与mysqlbinlog工具的修改

问题

本文主要带着以下问题进行学习:

1、什么是binlog,有什么作用

2、binlog有哪些格式

3、分析一条典型binlog ,说明从binlog中可以得到哪些信息

4、如何修改mysqlbinlog,使得可以显示最后一条记录

这里主要考虑binlog的使用及相关格式,而不是关注binlog的写入时机。

 

一、简介

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

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

 

二、简单的使用binlog

show binary logs;    #显示binlog文件

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

bin/mysqlbinlog binlogfile    #解析binlog文件

 

利用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的格式有三种,这也反应了mysql的复制技术:基于SQL语句的复制(statement-based replication, SBR),基于行的复制(row-based replication, RBR),混合模式复制(mixed-based replication, MBR)。相应地,binlog的格式也有三种:STATEMENT,ROW,MIXED。

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

 

使用mysqlbinlog解析的binlog:

MIXED(STATEMENT):

# 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模式还可以减少从机的相关计算,如在主机中存在统计写入等操作时,从机就可以免掉该计算把值直接写入从机。

 

四、binlog记录

每个binlog的开始都是由4个字节:fe 62 69 6e,组成的魔数(后面三个字节就是bin)。

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

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

 

Timestamp(4) Type(1) Server_id(4) Total_size(4) End_log_pos(4) Flag(2)

Timestamp:从1970开始

Type:此log event type如FORMAT_DESCRIPTION_EVENT、QUERY、LOAD_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_header及body。

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

Flag:标志位。

 

QUERY类型的记录:

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

Query Post-Header:(单位字节)

 

Thread_id(4) Exec_time(4) Db_len(1) Error_code(2) Status_var_len(2)

Thread_id:is used to distinguish temporary tables that belong to differentclients.

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

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

Error_code:执行出错的错误号。

 

五、使用mysqlbinlog显示最后一条log

1. 使用脚本实现:(该脚本可以显示最后n条)

 

#!/bin/sh

#Access to the binlog’s last n records

#if don’t set -n, show the last record.

 

NUM=”1″

 

function last_logs()

{

#get the total records

local rec_acc=`./bin/mysqlbinlog $1 | grep -c ‘^# at [0-9][0-9]*$’`

rec_acc=`expr $rec_acc – $NUM`

#Skip the first N entries.

./bin/mysqlbinlog -o $rec_acc $1

}

 

if [ $# -lt 1 ] || [ $# -gt 3 ]

then

echo “Usage: mysqlbinlog [-n0-9] filename”

exit

elif [ $# -eq 2 ]

then

if echo $1|grep -q ‘^-n[0-9][0-9]*$’

then

NUM=`echo $1 | cut -d “n” -f 2`

last_logs $2

else

echo “Usage:mysqlbinlog [-n0-9] filename”

fi

else

last_logs $1

fi

此本质是首先利用mysqlbinlog binlogfile打印出所有的记录,然后通过正则表达式(^# at [0-9][0-9]*$)判断记录个数M。最后在使用mysqlbinlog –o M-N binlogfile,来显示最后N条。

脚本的使用方法:./last_logs –n3 binlogfile #显示最后三条,不包括第一条FORMAT_DESCRIPTION_EVENT。

 

2.直接修改mysqlbinlog.cc

上面的脚本必须两次扫描binlog文件,这对于大的文件来说消耗可能比较大。修改后的工具,主要利用的是:最后一条记录的end_log_pos刚好为文件的大小。利用这个条件来判断是否需要解析打印。下面为主要的代码:

 

/*begin:xiangzhong.wxd at:2011-7-9 16:30*/

int is_last_flag(int &argc, char **argv)

{

if (argc == 3 && (!strcmp(argv[1],”-L”) || !strcmp(argv[2],”-L”)))

{

last_flag = 1;

if (!strcmp(argv[1],”-L”))

{

char * temp = argv[1];

argv[1] = argv[2];

argv[2] = temp;

}

//free(argv[2]);

//argv[2] = ‘\0′;

argc–;

return 0;

}

return -1;

}

/*end:xiangzhong.wxd at:2011-7-9 16:30*/

 

/*begin:xiangzhong.wxd at:2011-7-9 */

struct stat file_buf;

stat(logname, &file_buf);

unsigned long file_size = (unsigned long)file_buf.st_size;

if((ev_type != FORMAT_DESCRIPTION_EVENT) && last_flag && (ev->log_pos != file_size))

goto end;

/*end:xiangzhong.wxd at:2011-7-9 */

其中int is_last_flag函数是判断是否要使用该新功能,如果使用就是在命令行里加个-L,并且只能再使用一个binlogfile作为参数,即与其它的参数如start-position一起使用无效。

使用方法:./bin/mysqlbinlog –L binlogfile

注意:不管是使用上面两种的任意一种,都会打印出#at 4,即第一条记录,所以实质上我们是总共打印了两条记录。(原因见上面的FORMAT_DESCRIPTION_EVENT解释)。

主要涉及的源文件:mysqlbinlog.cc、log_event.cc、log_event.h.

综上可以知道,其实binlog文件就像一个流文件,它每一条记录没有明显的开始及结束标志,它是通过长度来判断一条记录的结束位置,所以在分析binlog文件的时候总是必须从头开始,然后依次一条一条的读取。

服务器维护 服务器配置 服务器 维护 运维 网管 系统调优 网络调优 数据库优化