MySQL 二进制日志格式深入理解

Seven 绯闻SEO 绯闻SEO,一个专注中小企业网站优化的SEO爱好者

mysql数据库的二进制日志文件对于我们来讲并不陌生了,我们数据库一般会开启二进制日志了,这样可以保证数据丢失之后利用它来恢复数据了,下面一起来了解一下二进制日志一些格式.

MySQL二制进日志用于记录数据库的变更记录,这里从结构上讨论一下日志的格式,每个日志都包含4个字节的magic number 和event的描述包.

日志有前四个字节是magic number:oxfe ox62 0×69 0x6e = 0xfe ‘b”i”n’ 转成整数:1852400382 用处就是读4个字节对比不是这个数,说明就不是二进制日志,就不用处理了.

log_event.sh中可以查到:

/* 4 bytes which all binlogs should begin with */

#define BINLOG_MAGIC "\xfe\x62\x69\x6e"

每个event的header大概如下:

-每个event中包含:event的类型,什么时间,由哪版本的MySQL产生的.

-从header头能找到该event的大小及一些变更信息.

第一个event称为:format descriptor event(Event描述结构:FDE) 用于说明日志的格式,其它event就是依赖于描述结构不同,用不同的结构记录数据,最后一个event是:日志切换事件(log-rotation event)用于指点定下个日志的文件名.

每个event的结构大概如下:

  1. +===================+
  2. |eventheader|
  3. +===================+
  4. |eventdata|
  5. +===================+

现在大多使用的V4结构,从MySQL 5.0起,具体如下:

  1. +=====================================+
  2. |event|timestamp0:4|
  3. |header+––––––––––––––+
  4. ||type_code4:1|
  5. |+––––––––––––––+
  6. ||server_id5:4|
  7. |+––––––––––––––+
  8. ||event_length9:4|--phpfensi.com
  9. |+––––––––––––––+
  10. ||next_position13:4|
  11. |+––––––––––––––+
  12. ||flags17:2|
  13. |+––––––––––––––+
  14. ||extra_headers19:x-19|
  15. +=====================================+
  16. |event|fixedpartx:y|
  17. |data+––––––––––––––+
  18. ||variablepart|
  19. +=====================================+

第一个event是FDE结构没有extra_headers部分,所以固定为19个字节.

FDE的event_data中定长部分为:

2字节的的日志格式版本,从MySQL 5.0后都是4

50字节 用于记录MySQL的版本号 如:5.6.16-64.2-rel64.2-log 不够50字节用0×00填充

4字节 日志产生的时间

1字节 header长度,一般是19,如果大于19,则下面的event都有extra_header字段

对于FDE变长部分一般为空

其它Event计算:

header length = x byte

data length = (event_lenth -x )byte

数据区里定长部分长度:

fixed_part=ybyte

variable_part=(event_length-(x+y))byte

补充:二进制操作

开启mysql二进制日志,编辑my.cnf,添加.

log-bin=/var/log/mysql/mysql-bin.log

开启日志后需要myssqladmin flush logs才能生效,需要注意的是log-bin指定扩展名是无效的,当mysql创建二进制日志文件时,首先创建一个以“mysql_log_bin”为名称,以“.index”为后缀的文件,再创建一个以“mysql_log_bin”为名称,以 “.000001”为后缀的文件,当mysql服务重新启动一次以“.000001”为后缀的文件会增加一个,并且后缀名加1递增,如果日志长度超过了 max_binlog_size的上限(默认是1G)也会创建一个新的日志文件;使用flush logs(mysql命令符)或者执行mysqladmin –u –p flush-logs(windows命令提示符)也会创建一个新的日志文件.

查看:由于日志是以二进制方式存储的,不能直接读取,需要使用mysql自带的mysqlbinlog工具来进行查看.

mysqlbinlog mysql-bin.000002 -d test

mysqlbinlog有一些选项可以使用,简单说明常用选项:

-d,--database=name :指定数据库名称,只列出指定数据库的操作.

-D, --disable-log-bin :执行恢复的时候,禁止二进制日志.可以防止同一台MySQL加上-t时进入死循环

-o,--offset=n:忽略掉日志前n行命令

-r,--result-file=name:将输出日志到指定文件

-R, --read-from-remote-server:从一个MySQL服务器上读取二进制

-s,--short-form:显示简单格式,省略一些信息

-S, --socket=name:socket文件连接path.

-t,--to-last-log:和-R一起使用,在二进制日志结束的时候并不会停止,而是在MySQL服务器最后生成的binlog结束,如果输出和输入都在一台MySQL上可能会导致死循环.

--set-charset=char-name:在输出文本格式的时候,在第一行加上set names char-name.

--start-datetime=# --stop-datetime=#:指定输出起始日期的日志.

--start-position=# --stop-position=#:指定起始日志的位置.

清理:

删除全部二进制日志:reset master

删除部分日志:

PURGE MASTER LOGS TO & PURGE MASTER LOGS BEFORE

PURGE MASTER LOGS TO 'mysql-bin.******'命令,是将'******'编号之前的所有日志进行删除

PURGE MASTER LOGS BEFORE 'yyyy-mm-dd hh:mm:ss'命令,是将在'yyyy-mm-dd hh:mm:ss'时间之前的所有日志进行删除.

设置日志过期时间:

修改my.cnf:expire_log_day=5

这里设置保存5天的日志,超过5天的日志会被自动删除

恢复:

完全恢复:mysqlbinlog mysql-bin.00001|mysql -uroot -p

基于时间点的恢复:

如果误删了一张表,使用完全恢复是没有用的,因为日志里同样也保留着删除的sql语句,所以我们需要恢复到误操作前的状态,然后跳过误操作的语句.

假如我在20:00误删了一张表,可以使用以下语句恢复:

mysqlbinlog --stop-date='2012-06-05 19:59:59' /var/log/mysql-bin.000001 | mysql -uroot -p

跳过误删除的时间点,再执行:

mysqlbinlog --start-date='2012-06-05 20:01:00' /var/log/mysql-bin.000001 | mysql -uroot -p

基于位置点的恢复:

基于位置点的恢复可以得到更为精确的数据。

binlog

如上图,drop table test这条语句的起始位置是889107,终止位置是889189,那么我们可以使用于以下语句进行恢复:

  1. mysqlbinlog--stop-position='889107'/var/lib/mysql/mysql-bin.000001|mysql-uroot-p
  2. mysqlbinlog--start-position='889189'/var/lib/mysql/mysql-bin.000001|mysql-uroot-p

有时有可能因为系统版本的问题,以上方法行不通,可以将二进制导出到一个sql文件中,再直接根据sql语句进行恢复.

mysqlbinlog mysqlbinlog.000001 >log.sql

相关广告
  • MySQL 二进制日志格式深入理解 MySQL 二进制日志格式深入理解 MySQL 二进制日志格式深入理解
相关阅读

MySQL 二进制日志格式深入理解

2019/10/10 17:32:47 | 谷歌SEO算法 | 云计算

MySQL二进制日志使用操作步骤

2019/10/10 17:32:36 | 谷歌SEO算法 | SEO知识