MySQL’s binary log结构简介

binary log,是mysql中一个非常重要的部分,其主要作用有两个:

• Replication:在master端开启binary log后,binary log记录所有数据库的改动,然后slave端获得这个binary log文件内容,就可以在slave端进行同样的操作,使master和slave保持一致.这是binary log的一个非常重要的用途.
• 备份:在某个时间点a作了一次备份,然后利用binary log记录从这个时间点a后的所有对数据库的改动,然后下一次还原的时候,利用时间点a的备份文件和这个binary log文件,就可以将数据还原至最新时点.

本文简单介绍binary log的结构.
注:本文中,”binary log”是指整体,包括所有binlog文件和binlog index文件,而”binlog”,是指单独的某个二进制日志文件,”binlog index”,就是指二进制日志索引文件.

一.开启binary log功能
在my.cnf文件中添加:

log-bin=master-bin log-bin-index=master-bin.index

log-bin是指定以后生成的各个binlog文件的前缀,在这里我们设为master-bin,因此以后生成的binlog文件将会是:

master-bin.000001 master-bin.000002 ......

而log-bin-index则是指定binlog index文件的名称,这里我们设为master-bin.index.

二.Binary log的结构和组织
1.每个binlog记录的是一个个事件,是对数据库产生变动了的事件,例如一条update语句,而select语句一般不会写入binlog,因为它对数据库不产生变动.,在这里我们简称这些事件为”event”.注意不要和mysql的stored routines中的event混淆.
2.binlog index文件只有一个,它记录的是所有binlog的文件名称,引用”MySQL High Availability”中的图来表述各个binlog文件和binlog index文件之间的关系:

再实例查看一下,在某个存放了binlog文件的目录下:

[zhouminjun.pt@xxxx data]$ ls -l -rw-rw---- 1 zhouminjun.pt users 973 Aug 23 17:06 mysql-bin.000001 -rw-rw---- 1 zhouminjun.pt users 106 Aug 23 17:07 mysql-bin.000002 -rw-rw---- 1 zhouminjun.pt users 324 Aug 23 17:08 mysql-bin.000003 -rw-rw---- 1 zhouminjun.pt users 598 Aug 23 21:01 mysql-bin.000004 -rw-rw---- 1 zhouminjun.pt users 149 Aug 24 08:36 mysql-bin.000005 -rw-rw---- 1 zhouminjun.pt users 3249 Aug 24 13:25 mysql-bin.000006 -rw-rw---- 1 zhouminjun.pt users 114 Aug 24 08:36 mysql-bin.index [zhouminjun.pt@xxxx data]$ cat mysql-bin.index ./mysql-bin.000001 ./mysql-bin.000002 ./mysql-bin.000003 ./mysql-bin.000004 ./mysql-bin.000005 ./mysql-bin.000006

可以看到,binlog index文件其实就是很简单的记录当前所有binlog的文件名,每行一个.当你使用”RESET MASTER”、”FLUSH LOGS”这类命令时,binlog index文件也会相应变动.

3.再看看binlog文件,binlog文件是二进制的,直接打开是不可阅读的.有两种方法阅读其中的内容:
a.在客户端中使用”show binlog events”查看:

mysql> show binlog events\G; *************************** 1. row *************************** Log_name: mysql-bin.000005 Pos: 4 Event_type: Format_desc Server_id: 1 End_log_pos: 106 Info: Server ver: 5.1.49-debug-log, Binlog ver: 4 *************************** 2. row *************************** Log_name: mysql-bin.000005 Pos: 106 Event_type: Rotate Server_id: 1 End_log_pos: 149 Info: mysql-bin.000006;pos=4 2 rows in set (0.00 sec)

可以看到，当前有两条event,下面解释下各个字段的意义:
Log_name:这个event所在的binlog名称.这里是在mysql-bin.000005
Pos:这个event在当前binlog中的位置
Event_type:这个event的类型,类型是有很多,当前这个是Format_desc类型
Server_id:这个event是在哪个server上发生的.注意在replication中,这个server id记录的是master端的server id.
End_log_pos:下一个event的位置.因此当前这个event的长度是End_log_pos-Pos.
Info:直观的可读的关于本条event的信息.

在刚才查看binlog的时候,里面有两条event,第一条类型是Format_desc,第二条是Rotate:

Format_desc:这是每一个binlog文件的头,是每一个binlog文件必有的第一个event,在这个event中,记录了一些诸如binary log格式版本,产生这个event的mysql server版本等等.

Rotate:一般来说,这是每个binlog文件的结束event.有如下情况(我在5.1.49-debug-log上已测):
1.当你使用”flush logs”命令时,mysql会自动在当前正在使用的binlog文件末尾加上这个rotate事件,然后就不再使用这个binlog了,同时会创建一个新的binlog文件,然后自动在新创建的这个binlog中加上一个Format_desc event,并且更新binlog index(将这个新创建的binlog文件名添加进入).
2.每次重启mysqld(通过mysqladmin关闭,再手动重启)的时候,mysql也会关闭当前binlog,并且写入一个Stop event(注意不是rotate event),然后再启动的时候会按照上面一样,创建新的binlog文件并作相关工作.
3.通过kill命令直接杀死掉mysql,这个时候发生没有任何event写入.

其他还有很多event类型，最常见的可能就是Query了，update、delete等操作都是Query类型.通常一个update操作在binlog文件中会产生好几个event,因为为了保证主从一致,需要确保很多外部因素的一致.
在这里我们引入一个分组的概念:除了format_desc和rotate,其他的event我们往往可以对其进行分组,对于事务型数据库来说，往往认为一个事务就是一个分组，对于非事务型数据库和类似create、alter这类语句来说,一条语句本身就是一个分组.
那么通常,一个分区内的语句要么全都执行,要么全都不执行,在replication中,如果slave在执行一个分组内的某条语句时,mysql突然中断了,那么mysql会重新从头开始这个分组,而非从中断点继续.这就能很好的保证了事务的原则.

这里有一点要说明的是,用show binlog events命令查看的永远是binlog index文件中的第一个binlog文件内容,一般来说是master-bin.000001文件,要查看其他binlog文件需要使用:

show binlog events in 'mysql-bin.000003';

查看mysql目前正在写哪个binlog文件可以先执行”show master status\G;”查看.

b.使用mysql自带的工具mysqlbinlog,比如对于一个经历了正常启动,再正常关闭(mysqladmin shutdown)的binlog来说,用mysqlbinlog查看:

[zhouminjun.pt@xxxxx data]$ mysqlbinlog mysql-bin.000007 /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #100825 19:19:30 server id 1 end_log_pos 106 Start: binlog v 4, server v 5.1.49-debug-log created 100825 19:19:30 at startup ROLLBACK/*!*/; BINLOG ' Qvx0TA8BAAAAZgAAAGoAAAAAAAQANS4xLjQ5LWRlYnVnLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAABC/HRMEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC '/*!*/; # at 106 #100825 19:20:12 server id 1 end_log_pos 125 Stop DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

用mysqlbinlog查看binlog可能比乱一点.但是能查看到更多的内容.
每个event都是以类似下面两行开始:

# at 267 #100825 11:57:57 server id 1 end_log_pos 335 Query thread_id=99 exec_time=0 error_code=0 第一行的267代表这个event的起始位的位置.

第二行的100825 11:57:57代表这个event开始执行的时间(对于replication来说,无论在master的binlog中还是slave的relay-log中,值是相同的,都是指在master端开始执行的时间),100825是指10年8月25.
接着是master的server id.
end_log_pos指的是下一个event的起始位置,注意这个值如果是在replication中的relay-log中,那这个值是直接从master处搬过来的,因此对于relay-log来说有可能是不准的.
thread_id就是执行这个event的线程id.
exec_time对于master端的binlog来说是执行这个event所花费的时间,对slave端的relay-log来说,这个值是slave端执行这个event结束的时间减去master端开始执行这个event的时间,因此在一些情况下，我们还能根据这个值大概分析出slave落后于master的时间.