MySql的Bin-Log日志【原创】

• 简介

Bin-Log日志以一种更有效的格式，并且是事务安全的方式包含更新日志中可用的所有信息。

Bin-Log日志包含了所有更新了数据或者已经潜在更新了数据（例如，没有匹配任何行的一个DELETE）的所有语句。语句以“事件”的形式保存，它描述数据更改。

注释：Bin-Log日志已经代替了老的更新日志，更新日志在MySQL 5.1中不再使用。

Bin-Log日志还包含关于每个更新数据库的语句的执行时间信息。它不包含没有修改任何数据的语句。如果你想要记录所有语句（例如，为了识别有问题的查询），你应使用一般查询日志。

Bin-Log日志的主要目的是在恢复使能够最大可能地更新数据库，因为Bin-Log日志包含备份后进行的所有更新。

Bin-Log日志还用于在主复制服务器上记录所有将发送给从服务器的语句

• 开启mysqlBin-Log日志

在mysql的配置文件(my.cnf)中,添加下面一行。

log-bin = mysql-bin

我这里没有指定存放路径，默认会存放在mysql的数据存放目录，所有的二进制文件都以mysql-bin开头，当然你也可以自己指定mysql bin-log的存放路径和名称。

保存mysql的配置文件，重启mysql

查看mysql的Bin-Log日志是否开启：

show variables like 'log%';

如图，如果log_bin的值为ON则说明Bin-Log日志已经开启。这时查看mysql的数据存放目录会发现两个文件：mysql-bin.000001和mysql-bin.index这就是Bin-Log日志文件和二进制索引文件

• 测试msyqlBin-Log日志

首先我们在test数据库里创建一张表，名字为test1,然后插入几条数据。

create table test1 (id int not null);
insert into test1 values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

使用mysql提供的mysqlBin-Log工具查看Bin-Log日志

/usr/local/mysql/bin/mysqlBin-Log mysql-bin.000001

这时你会看到刚才我们上面写的两天数据库操作语句

有时上面的查看命令会报错，错误的字符集，这是需要加上--no-defaults来忽略这个错误

/usr/local/mysql/bin/mysqlBin-Log --no-defaults mysql-bin.000001

• 生成一个新的Bin-Log日志

mysql> flush logs;

这时mysql的数据存放目录里会多出一个二进制文件mysql-bin.000002

• 删除所有的二进制文件

mysql> reset master;

这时mysql的数据存放目录下只剩下mysql-bin.000001,其余的都被删除了。

• 查看最后一个Bin-Log日志

mysql> show master status;

Position记录了最后一次数据库操作的日志（非select）,下面我们在操作一次数据库，我们删除大于5的id

delete from test1 where id>5;

再次查看

利用Bin-Log日志恢复mysql数据

我们先生成一个新的二进制文件

mysql> flush logs;

这时多出一个文件mysql-bin.000002

我们在数据库里插入一些数据

insert into test1 values(6),(7),(8),(9),(10);

这是数据库里的id为1～10

我们再生成一个新的二进制文件

mysql> flush logs;

这时多出一个文件mysql-bin.000003

然后删除id>3的七条数据

delete from test1 where id>3;

然后我们恢复到id为1~10时的数据，可以用下面的操作

/usr/local/mysql/bin/mysqlBin-Log --no-defaults mysql-bin.000002 | /usr/local/mysql/bin/mysql -uroot test

我们也可以只恢复Bin-Log日志里的一部分数据

用mysqlBin-Log查看mysql-bin.000003内容如下：

/*!*/;

# at 1883

#131218 22:42:53 server id 1  end_log_pos 2010  Query   thread_id=1     exec_time=111   error_code=0

SET TIMESTAMP=1387377773/*!*/;

insert into test1 values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)

/*!*/;

# at 2010

#131218 22:42:53 server id 1  end_log_pos 2037  Xid = 214

COMMIT/*!*/;

# at 2037

#131218 23:19:17 server id 1  end_log_pos 2105  Query   thread_id=1     exec_time=0     error_code=0

SET TIMESTAMP=1387379957/*!*/;

BEGIN

/*!*/;

# at 2105

#131218 23:19:17 server id 1  end_log_pos 2196  Query   thread_id=1     exec_time=0     error_code=0

SET TIMESTAMP=1387379957/*!*/;

delete from test1 where id>3

/*!*/;

可以用下面的命令恢复到id为1～10的数据

/usr/local/mysql/bin/mysqlBin-Log --no-defaults --start-position=1883 --stop-position=2010 mysql-bin.000003 | /usr/local/mysql/bin/mysql -uroot test

当然这里也可以省略 --start-position

mysqlBin-Log有一些常用选项:

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

-D, --disable-log-bin :执行恢复的时候,禁止Bin-Log日志.可以防止同一台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一起使用,在Bin-Log日志结束的时候并不会停止,而是在MySQL服务器最后生成的Bin-Log结束,如果输出和输入都在一台MySQL上可能会导致死循环.

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

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

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