关于MySQL二进制日志Binlog的认识

MySQL的二进制日志可以说或是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二进制日志是失误安全型的.

在5.1的版本你二进制日志还替代了原本的更新日志.一般来说开启二进制日志大概会有1%的性能损耗(参见MySQL官方中文手册5.1.24版).二进制有两个最重要的使用场景:

其一是:MySQL Replication在Master端开启binlog,Mster把它的二进制日志传递给slaves来达到master-slave数据一直的目的.

其二:自然就是数据恢复了,通过使用mysqlbinlog工具来恢复数据.

binlog不支持select,show一类语句,如果要记录这些语句可以通过打开MySQL的general log来大到目的.

可以利用mysqlbinlog工具来查看二进制日志,打开一个正在使用的binlog

$mysqlbinlog var/mysql-bin.000022
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#091222 18:09:33 server id 2  end_log_pos 106   Start: binlog v 4, server v 5.1.41-debug-log created 091222 18:09:33 at startup
# Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.
ROLLBACK/*!*/;
# at 106
#091222 23:20:35 server id 2  end_log_pos 174   Query   thread_id=4     exec_time=1     error_code=0
SET TIMESTAMP=1261495235/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/;
SET @@session.sql_mode=0/*!*/;
/*!/C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
BEGIN
/*!*/;
# at 174
#091222 23:20:35 server id 2  end_log_pos 269   Query   thread_id=4     exec_time=0     error_code=0
use test/*!*/;
SET TIMESTAMP=1261495235/*!*/;
insert bench values(20,'xiaojj')
/*!*/;
# at 269
#091222 23:20:35 server id 2  end_log_pos 296   Xid = 22
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

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=# :指定起始日志的位置.

一个典型的恢复例子如下

$mysqlbinlog -d=binlog var/mysql-bin.000022|mysql –uroot –p

#在本地恢复binlog数据库数据操作.

MySQL的二进制日志量的繁忙的服务器上是巨大的,尤其是使用了row级别记录binlog日志量更是惊人,长时间不管理的话会非常浪费磁盘空间,通常删除日志有以下几种方法:
reset master在执行reset master之后会清空所有二进制日志,新日志会从*****-000001开始

purge master logs这个命令有2中用法:其一,purge master logs to 'mysql-bin.000022'形式的意思是将编号22之前的日志全部删除,其二是purge master logs before'yyyy-mm-dd HH(24):mm:ss'是将指定时间之前的binlog全部删除

第三种方法就是在mysqld加入--expire_logs_days=n设置日志的有效期为n天.

binlog是以二进制形式保存的,所以并不能像慢查询日志那样可以直接查看.binlog有3种记录形式分别是:

STATEMENT:基于sql语句级别的binlog,每一条修改数据的sql都会被保存到binlog里.

ROW:基于行级别的,他会记录每一行记录的变化,就是将每一行的修改都记录到binlog里面,记录的非常详细,在replication里面也不会因为存储过程触发器等造成Master-Slave数据不一致的问题,但是有个致命的缺点日志量比较大.由于要记录每一行的数据变化,当执行update语句后面不加where条件的时候或alter table的时候,产生的日志量时惊人的.

MIXED:在mixed下默认是statement,但是在下列情况下会切换到row状态:

.当一个DML更新一个ndb引擎表

.使用uuid()函数的时候,这是由于uuid()Universal Unique Identifier每次在运行的时候产生的值都是唯一的

.当2个或更多的表同时被更新且有auto_increment列的时候

.任意的insert delayed被执行的时候

.当一个视图需要row-based replication时候,比如一个sql在创建视图的时候使用了uuid()函数

.调用一个UDF

.如果一个语句使用的时row,同时这个语句还在使用了一些临时表,所有后来的语句都会以row方式记录(除了使用那些临时表的).直到该客户端断开所有的临时表.临时表是不能使用row方式的,因此,一旦row被使用,那么后来的使用那个表的语句就是不安全的,所以近似于这个客户端在断开所有临时表的时候是不安全的.

.还有一些函数如found_rows(),row_count(),user(),current_user()以及系统变量(部分session级别的除外)

.在最近的MySQL版本里面当使用mysql数据库里面的log表,或者使用load_fileio()函数

几乎所有的engine都支持row级别的binlog,但是statement级别的在innodb上只有隔离级别是repeatable read和serializable时支持.

当操作影响mysql数据库grant表的时候,有以下情况:

.当grant表被直接修改的时候例如使用insert,update,delete,replace,do,load data file,truncate,select等语句的时候使用的时候相应的设定的系统设定的类型来记录binlog.

.当是有grant,create user,revoke,set password,rename user,drop,alter,create(除了create table...select...)都将使用statement级别来记录binlog而忽视系统设置.

create table...select是一个集数据定义和数据处理为一体的语句.他的create table部分使用statement方式,而select部分使用系统定义的方式.

可以通过启动的时候加上--binlog-format=row|statement|mixed来指定binlog级别.还可以写道my.cnf里面的[mysqld]里面,当然在MySQL运行中通过set命令也可以修改binlog的级别.例如

mysql>set global binlog_format=’row’;

mysql>set global binlog_format=1;#和row一样,同样2和statement,3和mixed也是一样的级别

binlog_format的默认值不同版本也不相同,应使用"show variables like '%binlog%';"查看

有以下几种原因,当一个replication在运行的时候,转换binlog_format可能会导致一个错误:

.有一个存储过程函数或触发器

.ndb存储引擎是可用的

.replication正在进行的使用row级别且打开临时表的session

系统参数,关于二进制日志的一些选项:

--binlog-row-event-max-size选项,使用row级别时指定,当一个时间的所有行小于这个值才会被记录到binlog,单位是byte,该数值必须是256的整数倍,默认是1024.

使用show variables like "%bin%";的部分变量

--log-bin=0|1 二进制日志是否开启

--binlog-format=statement|row|mixed 二进制日志的级别statement,row或mixed

--max-binlog-size=# 二进制日志文件的最大尺寸,但这并不是严格的.

--binlog-cache-size=# 事务进行过程中存储二进制日志的缓存大小,系统会为每个session分配指定大小的空间

--max-binlog-cache-size=# 表示的时二进制日志最大可使用的内存,如果数值国小会导致系统报错.

--innodb-locks-unsafe-for_binlog=0|1 控制在innodb表中使用insert into ...select语句时是否锁定源表,on不锁定,off锁定

--log-bin-trust-function-creators=0|1 二进制日志打开时有效,该参数决定存储函数创建者是否可以创建会导致不安全的时间记录到二进制日志的存储函数。当为0时,此时这也是默认值,除了拥有super权限除了create routine或alter routine权限.同时也会强制函数必须和deterministic或reads sql data又或者no sql的字符集一致.当设为1时MySQL不会去限制这些,这个变量同样作用于触发器

--sql-log-bin=0|1 当前session的sql是否记录到binlog,拥有super权限用户才可修改

--sync-binlog=n在进行n次事务提交后,MySQL将执行一次磁盘同步指令讲binlog文件的缓存写到磁盘,当为0时MySQL总是讲binlog_cache写到binlog文件,有fs来决定什么时候来同步到磁盘.该参数对MySQL性能有较大影响,简朝阳<MySQL性能调优与架构设计>中谈到1和0两个值之间性能能有5以上倍的差距

使用show status like "%bin%";的部分变量

Binlog_cache_disk_use 使用二进制日志缓存超过binlog_cache_size的值,被迫用临时文件来保存事务中的语句的事务数量

Binlog_cache_use 二进制日志中经历过事务的binlog缓存的数量

Replication中还以通过一下选项来减少binlog数据量,来达到提到效率的目的,前两个用在Master端,后六个是用在Slave端的

--binlog-do-db二进制日志记录的数据库(多个数据库用,分隔)

--binlog-ignore-db二进制日忽略的数据库(多个数据库用,分隔)

在replication的slave端还有一下6个参数

--replication-do-db设定需要复制的数据库(多个数据库用,分隔)

--replication-ignore-db设定忽略复制的数据库(多个数据库用,分隔)

--replication-do-table设定需要复制的表(多个表用,分隔)

--replication-ignore-table设定忽略复制的表(多个表用,分隔)

--replication-wild-do-table同replication-do-table功能一样,但是可以加通配符

--replication-wild-ignore-table同replication-ignore-table功能一样,但是可以加通配符

更多关于二进制说明查看最新版本官方英文手册