Mysql binlog相关概念详解

本文介绍了MySQL的binlog日志,包括Statement、Row和Mixed三种格式的特点,以及binlog的配置、日志格式说明和数据恢复操作。对于理解binlog在数据库复制和数据恢复中的作用具有基础指导意义。
摘要由CSDN通过智能技术生成

近期项目中涉及到 mysql-binlog 解析,所以先研究下 binlog 的一些基础知识,后续再对相关的开源产品详细研究

1、介绍

Mysql binlog日志有三种格式,分别为Statement,MiXED,以及ROW

1.Statement:每一条会修改数据的sql都会记录在binlog中。

优点:不需要记录每一行的变化,减少了binlog日志量,节约了IO,提高性能。(相比row能节约多少性能与日志量,这个取决于应用的SQL情况,正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量,但是考虑到如果带条件的update操作,以及整表删除,alter表等操作,ROW格式会产生大量日志,因此在考虑是否使用ROW格式日志时应该跟据应用的实际情况,其所产生的日志量会增加多少,以及带来的IO性能问题)

缺点:由于记录的只是执行语句,为了这些语句能在slave上正确运行,因此还必须记录每条语句在执行的时候的一些相关信息,以保证所有语句能在slave得到和在master端执行时候相同 的结果。另外mysql 的复制,像一些特定函数功能,slave可与master上要保持一致会有很多相关问题(如sleep()函数, last_insert_id(),以及user-defined functions(udf)会出现问题).
使用以下函数的语句也无法被复制:
* LOAD_FILE()
* UUID()
* USER()
* FOUND_ROWS()
* SYSDATE() (除非启动时启用了 --sysdate-is-now 选项)
同时在INSERT ...SELECT 会产生比 RBR 更多的行级锁

2.Row:不记录sql语句上下文相关信息,仅保存哪条记录被修改。

优点: binlog中可以不记录执行的sql语句的上下文相关的信息,仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程,或function,以及trigger的调用和触发无法被正确复制的问题

缺点: 所有的执行的语句当记录到日志中的时候,都将以每行记录的修改来记录,这样可能会产生大量的日志内容,比如一条update语句,修改多条记录,则binlog中每一条修改都会有记录,这样造成binlog日志量会很大,特别是当执行alter table之类的语句的时候,由于表结构修改,每条记录都发生改变,那么该表每一条记录都会记录到日志中。

3.Mixedlevel: 是以上两种level的混合使用

    一般的语句修改使用statment格式保存binlog,如一些函数,statement无法完成主从复制的操作,则采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式,也就是在Statement和Row之间选择一种.新版本的MySQL中队row level模式也被做了优化,并不是所有的修改都会以row level来记录,像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句,还是会记录所有行的变更。

2、配置

#Mysql Binlog日志格式可以通过mysql的my.cnf文件的属性binlog_format指定。如以下:
#binlog日志格式
binlog_format = MIXED
#binlog日志名
log_bin =目录/mysql-bin.log
#binlog过期清理时间
expire_logs_days = 7
#binlog每个日志文件大小
max_binlog_size = 100m
#需要备份的数据库名,如果备份多个数据库,重复设置这个选项即可
binlog-do-db=db1,db2,db3
#不需要备份的数据库苦命,如果备份多个数据库,重复设置这个选项即可
binlog-ignore-db=db1,db2,db3

3、日志格式说明


通过MysqlBinlog指令查看具体的mysql日志,如下:

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

SET TIMESTAMP=1350355892/*!*/;

BEGIN

/*!*/;

# at 1643330

#121016 10:51:32 server id 1  end_log_pos 1643885        Query     thread_id=272571   exec_time=0   error_code=0

SET TIMESTAMP=1350355892/*!*/;

Insert into T_test….)

/*!*/;

# at 1643885

#121016 10:51:32 server id 1  end_log_pos 1643912        Xid = 0

COMMIT/*!*/;

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

1.开始事物的时间:

SET TIMESTAMP=1350355892/*!*/;

BEGIN

2.sqlevent起点

#at 1643330 :为事件的起点,是以1643330字节开始。

3.sqlevent 发生的时间点

#121016 10:51:32:是事件发生的时间,

4.serverId

server id 1 :为master 的serverId

5.sqlevent终点及花费时间,错误码

end_log_pos 1643885:为事件的终点,是以1643885 字节结束。

execTime 0: 花费的时间

error_code=0:错误码

Xid:事件指示提交的XA事务

Mixed日志说明:

在slave日志同步过程中,对于使用now这样的时间函数,MIXED日志格式,会在日志中产生对应的unix_timestamp()*1000的时间字符串,slave在完成同步时,取用的是sqlEvent发生的时间来保证数据的准确性。另外对于一些功能性函数slave能完成相应的数据同步,而对于上面指定的一些类似于UDF函数,导致Slave无法知晓的情况,则会采用ROW格式存储这些Binlog,以保证产生的Binlog可以供Slave完成数据同步。

4、操作

show variables like 'binlog%'; #查看binlog配置信息
show binary logs; #查看所有的日志文件
flush logs; #刷新一个新的 binlog 文件出来

show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
#IN 'log_name' :指定要查询的binlog文件名(不指定就是第一个binlog文件)
#FROM pos :指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
#LIMIT [offset,] :偏移量(不指定就是0,跳过几行)
#row_count :查询总条数(不指定就是所有行)

reset master;#重置 binlog,之前的文件会被清空,位点重置
show master status; #查看当前 master 的position
mysqlbinlog [options] log-files #binlog 是二进制文件,无法直接使用编辑器查看内容,必须使用 mysql 提供的查看工具查看

options:
--base64-output #binlong 中的 sql 语句是加密过的,不加这个参数看的的是乱码
--start-datetime #从二进制日志中读取指定等于时间戳或者晚于本地计算机的时间
--stop-datetime #从二进制日志中读取指定小于时间戳或者等于本地计算机的时间 取值和上述一样
--start-position #从二进制日志中读取指定position 事件位置作为开始。
--stop-position #从二进制日志中读取指定position 事件位置作为事件截至

5、使用 binlog 恢复数据

恢复命令的语法格式:
mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名

--------------------------------------------------------
常用参数选项解释:
--start-position=875 起始pos点
--stop-position=954 结束pos点
--start-datetime="2016-9-25 22:01:08" 起始时间点
--stop-datetime="2019-9-25 22:09:46" 结束时间点
--database=zyyshop 指定只恢复zyyshop数据库(一台主机上往往有多个数据库,只限本地log日志)
-------------------------------------------------------- 
不常用选项: 
-u --user=name 连接到远程主机的用户名
-p --password[=name] 连接到远程主机的密码
-h --host=name 从远程主机上获取binlog日志
--read-from-remote-server 从某个MySQL服务器上读取binlog日志
--------------------------------------------------------
小结:实际是将读出的binlog日志内容,通过管道符传递给mysql命令。这些命令、文件尽量写成绝对路径;

完全恢复(需要手动vim编辑mysql-bin.000003,将那条drop语句剔除掉)
[root@vm-002 backup]# /usr/bin/mysqlbinlog /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops
  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值