MySQL binlog日志

binlog日志记录对数据发生或潜在发生更改的SQL语句，并以二进制的形式保存在磁盘中；
作用：可以用来查看数据库的变更历史（具体的时间点所有的SQL操作）、数据库增量备份和恢复（增量备份和基于时间点的恢复）、MySQL的复制（主主数据库的复制、主从数据库的复制）恢复数据

文件位置：默认存放位置为数据库文件所在目录mysql/data下
文件的命名方式： 名称为hostname-bin.xxxxx （重启mysql一次将会自动生成一个新的binlog）

 

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)会出现问题).

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

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

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

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

 

1、登录MySQL，查看binlog日志的状态

登录MySQL后，输入show variables like '%log_bin%'；查看到binlog日志为OFF关闭状态;

2、开启MySQL binlog日志

在my.cnf配置文件中添加如下图的两句

首先我们来认识几个与bin-log相关的MySQL命令：
flush logs; 会多一个最新的bin-log日志
show master status; 查看最后一个bin-log日志的相关信息
reset master; 清空所有的bin-log日志

这个时候我们进入到MySQL里，查看一下最后一个bin-log日志信息

然后刷新一下bin-log日志，再查看下最后一个bin-log日志，此时已经变成了000002这个版本了，

以后的增删改操作就记录在这个最新的bin-log日志里

 接着我们创建一个数据库，然后创建一个表，再插入一些记录，然后再查看下日志信息有没有变化

接着我们再看下bin-log信息，此时位置已经发生了变化，也就是说已经对刚才的操作进行了记录

接着我们打开二进制日志，看看里面就是记录了什么，我们需要知道的是，如果用vi打开二进制日志的乱码的，

所以我们需要使用下面的命令进行打开：

/usr/local/mysql/bin/mysqlbinlog --no-defaults mysql-bin.000002

也可以另存为其他格式的文件打开

/usr/local/mysql/bin/mysqlbinlog --no-defaults mysql-bin.000002 > /tmp/add.sql

 

我把打开的日志截图看看.我用红色圈起来的都是我们需要注意的，end_log_pos大概的意思是这这个操作的最后位置在哪，

例如：end_log_pos 400，在299-400之间是存了插入操作的记录的，insert into test1 values(1);

下面我们来做一个利用二进制恢复数据库数据的操作。
流程如下：
（1）先生成一个新的日志文件（000003）；
（2）然后删除test1表的数据；
（3）然后利用前一个日志恢复数据（000002）；
（4）最后查看是否恢复成功。

 用mysql-bin.000002来恢复数据

/usr/local/mysql/bin/mysqlbinlog --no-defaults mysql-bin.000002 |mysql -uroot -p

我们到数据库里查看下是否恢复成功了：

 

增量还原，讲几个常用的，比较有用的
a）根据时间来还原 –start-date,–stop-date

/usr/local/mysql/bin/mysqlbinlog –start-date=”2010-09-29 18:00:00″ –stop-date=”2010-09-29 23:00:00″ /var/lib/mysql/mysql-bin.000002 |mysql -u root -p

b）根据起始位置来还原，–start-position,–stop-position

现在恢复mysql-bin.000002恢复，从134点开始到386结束

/usr/local/mysql/bin/mysqlbinlog --no-defaults mysql-bin.000002 --start-position 134 --stop-position=386|mysql -uroot -p

c）根据数据库名来进行还原 -d在这里是小写的d，请不要把它和mysqldump中的－D搞混了。哈哈。

/usr/local/mysql/bin/mysqlbinlog -d test  /var/lib/mysql/mysql-bin.000002

d）根据数据库所在IP来分－h

/usr/local/mysql/bin/mysqlbinlog -h 192.1681.102  /var/lib/mysql/mysql-bin.000002

 e）根据数据库所占用的端口来分－P

/usr/local/mysql/bin/mysqlbinlog -p 13306  /var/lib/mysql/mysql-bin.000002

 f）根据数据库serverid来还原–server-id

在数据库的配置文件中，都有一个serverid并且同一集群中serverid是不能相同的。

/usr/local/mysql/bin/mysqlbinlog –server-id=1  /var/lib/mysql/mysql-bin.000002

注意：上面的几个例子，我都是一个一个说的，其实可以排列组合的。

下面还有一个重要索引文件就是mysql-bin.index

.\mysql-bin.000001
.\mysql-bin.000002
.\mysql-bin.000003

mysql-bin.index的起索引作用，因为增量的数据不一定在一个mysql-bin000这样的文件里面，这个时候，我们就要根据mysql-bin.index来找mysql-bin这样的增量文件了

如果mysql里面做了这样的配置binlog-do-db=test1，增量备份里面只会有test1这个数据库的数据

 

3、设置自动清理binlog日志和手动删除的方法

MYSQL主从复制(replication)采用 RBR 模式后，binlog的格式为"ROW"，能解决很多原先出现的主键重复问题。在一个繁忙的master db server上，binlog日志文件增长速度很快，如果不定时清除，硬盘空间很快就会被充满。
设置自动清理mysql binlog日志:
配置my.cnf：expire_logs_days = 10

 

在运行时修改：

show binary logs;
show variables like '%log%';
set global expire_logs_days = 10;

 清除之前可以采用相应的备份策略。 手动删除10天前的mysql binlog日志：

PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);
show master logs;

 MASTER和BINARY是同义词。