MySQl Study学习之--MySQl二进制日志管理

傷城~ 2022-08-02 04:48 253阅读 0赞

**MySQl Study学习之--MySQl二进制日志管理**

**MySQL二进制日志（Binary Log）**

**  a、它包含的内容及作用如下：**  
    包含了所有更新了数据或者已经潜在更新了数据(比如没有匹配任何行的一个DELETE)  
    包含关于每个更新数据库(DML)的语句的执行时间信息  
    不包含没有修改任何数据的语句，如果需要启用该选项，需要开启通用日志功能  
    主要目的是尽可能的将数据库恢复到数据库故障点，因为二进制日志包含备份后进行的所有更新  
    用于在主复制服务器上记录所有将发送给从服务器的语句  
    启用该选项数据库性能降低1%，但保障数据库完整性，对于重要数据库值得以性能换完整。有些类似于[Oracle][]开启归档模式。  
    
**  b、开启二进制日志的方法及属性**  
    使用\--log-bin\[=file\_name\]选项或在配置文件中指定log-bin启动时，mysqld写入包含所有更新数据的SQL命令的日志文件。  
    对于未给出file\_name值，默认名为\-bin后面所跟的主机名。  
    在未指定绝对路径的情形下，缺省位置保存在数据目录下。  
    每个二进制日志名会添加一个数字扩展名用于日志老化，因此不支持自定义的扩展名，会被mysql数字扩展名动态替换。  
    若当前的日志大小达到max\_binlog\_size，则自动创建新的二进制日志。  
    对于大的事务，二进制日志会超过max\_binlog\_size设定的值。也即是事务仅仅写入一个二进制日志。  
    由是可知，二进制日志文件大小接近，其size不是完全相等，这点不同于oracle。  
    二进制日志文件会有一个对应二进制日志索引文件，该文件包含所有的二进制日志，其文件名与二进制日志相同，扩展名为.index  
    二进制索引文件通过\--log-bin-index\[=file\_name\]选项来指定  
    RESETMASTER语句将删除所有二进制日志文件，这将影响到从库。也可以用PURGE MASTER LOGS只删除部分二进制文件。

**二进制日志记录了所有对数据库执行更改的操作，二进制主要有以下两种作用**：

1、恢复（recovery）2、复制（replication）

二进制日志的启动：配置参数log-bin\[=name\]，如果不指定name，则默认二进制日志文件名为主机名，后缀名为二级制日志的序列号，所在路径为数据库所在目录。

以index为后缀的文件为二进制日志的索引文件，用来存储过程生产的二进制日志。

--------------------

和二进制日志相关的参数：

max\_binlog\_size、binlog\_cache\_size、sync\_binlog、binlog-do-db、binlog-ignore-db、log-slave-update、binlog\_format

**1、max\_binlog\_size，**该参数指定了单个二进制日志文件的最大值，如果超过该值，则产生新的二进制日志文件，后缀名+1，并记录到.index文件，从Mysql5.0开始的默认值为1073741824，代表1G。

Mysql5.5的默认值

当使用事务的存储引擎InnoDB时，所有未提交的事务会记录到一个缓存中，等待事务提交时，直接将缓冲中的二进制日志写入二进制日志文件，而该缓冲的大小由binlog\_cache\_size决定，默认大小为32KB，此外，binlog\_cache\_size是基于session的，也就是，当一个线程开始一个事务时，mysql会自动分配一个大小为binlog\_cache\_size的缓存，因此该值得设置需要相当小心，可以通过show global status 查看binlog\_cache\_use、binlog\_cache\_disk\_use的状态，可以判断当前binlog\_cache\_size的设置是否合适。

**2、Binlog\_cache\_size**的默认大小为32KB

**3、参数sync\_binlog=\[N\]**表示每写缓存多少次就同步到磁盘，如果将N设置为1，则表示采用同步写磁盘的方式来写二进制日志，该参数很重要，这个以后还会提到。值得注意的是，在将该参数设置为1时，也应该将innodb\_support\_xa设为1来解决，这可以确保二进制日志和InnoDB存储引擎数据文件的同步。

**4、参数binlog-do-db和binlog-ignore-db**表示需要写入或者忽略写入哪些库的日志，默认值为空，则表示将所有库的日志同步到二进制日志。

**5、Log-slave-updat**e该参数在搭建master=>slave=>slave的架构时，需要配置。

**6、Binlog\_format**参数也特别重要。从mysql5.1版本开始引入这个参数，该参数可以设置的值有STATEMENT\\、ROW、MIXED；

（1）STATEMENT格式和之前的mysql版本一样，二进制日志文件记录的是日志的逻辑SQL语句。

（2）在ROW格式下，二进制日志记录的不再是简单的SQL语句了，而是记录表的行更改情况，此时可以将InnoDB的事务隔离基本设为READ COMMITTED，以获得更好的并发性。

（3）MIXED格式下，mysql默认采用的STATEMENT格式进行二进制日志文件的记录，但是在一些情况下会使用ROW格式，可能的情况包括：

1）表的存储引擎为NDB，这时对于表的DML操作都会以ROW格式记录

2）使用了UUID()、USER()、CURRENT\_USER()、FOUND\_ROWS()、ROW\_COUNT()等不确定函数

3）使用了INSERT DELAY语句

4）使用了用户定义函数

5）使用了临时表

**案例分析：  
  
1、二进制日志存放位置**

**\[root@mysrv ~\]\# more /etc/my.cnf**  
\[mysqld\]  
datadir=/usr/local/mysql/data/mysql  
  
**mysql> show variables like '%datadir%';**  
\+---------------+------------------------------+  
| Variable\_name | Value                        |  
\+---------------+------------------------------+  
| datadir       | /usr/local/mysql/data/mysql/ |  
\+---------------+------------------------------+  
1 row in set (0.00 sec)

**查看日志信息：**

[root@mysrv ~]# cd /usr/local/mysql/data/mysql/
    [root@mysrv mysql]# ls -l
    total 29928
    -rw-rw---- 1 mysql mysql       56 Jun 30 17:12 auto.cnf
    -rw-rw---- 1 mysql mysql 18874368 Jul  8 15:17 ibdata1
    -rw-rw---- 1 mysql mysql  5242880 Jul  8 15:17 ib_logfile0
    -rw-rw---- 1 mysql mysql  5242880 Jun 30 17:12 ib_logfile1
    -rw-rw---- 1 mysql mysql      124 Jul  8 15:17 master.info
    drwx--S--- 2 mysql mysql     4096 Jun 30 17:15 mysql
    -rw-rw---- 1 mysql mysql      114 Jun 30 17:15 mysql-bin.000001
    -rw-rw---- 1 mysql mysql    63438 Jun 30 17:15 mysql-bin.000002
    -rw-rw---- 1 mysql mysql  1096670 Jun 30 17:15 mysql-bin.000003
    -rw-rw---- 1 mysql mysql      252 Jun 30 17:26 mysql-bin.000004
    -rw-rw---- 1 mysql mysql      114 Jun 30 17:40 mysql-bin.000005
    -rw-rw---- 1 mysql mysql      133 Jun 30 17:51 mysql-bin.000006
    -rw-rw---- 1 mysql mysql      114 Jun 30 17:56 mysql-bin.000007
    -rw-rw---- 1 mysql mysql      114 Jul  8 15:17 mysql-bin.000008
    -rw-rw---- 1 mysql mysql      157 Jul  8 15:18 mysql-bin.000009
    -rw-rw---- 1 mysql mysql      157 Jul  8 15:20 mysql-bin.000010
    -rw-rw---- 1 mysql mysql      114 Jul  8 15:20 mysql-bin.000011
    -rw-rw---- 1 mysql mysql      209 Jul  8 15:20 mysql-bin.index
    -rw-rw---- 1 mysql mysql    24920 Jul  8 15:21 mysrv.err
    -rw-rw---- 1 mysql mysql        5 Jul  8 15:17 mysrv.pid
    -rw-rw---- 1 mysql mysql      163 Jul  8 15:18 mysrv-relay-bin.000003
    -rw-rw---- 1 mysql mysql      163 Jul  8 15:20 mysrv-relay-bin.000004
    -rw-rw---- 1 mysql mysql      114 Jul  8 15:20 mysrv-relay-bin.000005
    -rw-rw---- 1 mysql mysql       75 Jul  8 15:20 mysrv-relay-bin.index
    drwx--S--- 2 mysql mysql     4096 Jun 30 17:15 performance_schema
    drwx--S--- 2 mysql mysql     4096 Jun 30 18:15 prod
    -rw-rw---- 1 mysql mysql       58 Jul  8 15:20 relay-log.info
    drwx--S--- 2 mysql mysql     4096 Jun 30 17:15 test

<strong>mysrv-relay-bin.index，为记录日志文件的index文件</strong>

[root@mysrv mysql]# cat mysql-bin.index
    ./mysql-bin.000001
    ./mysql-bin.000002
    ./mysql-bin.000003
    ./mysql-bin.000004
    ./mysql-bin.000005
    ./mysql-bin.000006
    ./mysql-bin.000007
    ./mysql-bin.000008
    ./mysql-bin.000009
    ./mysql-bin.000010
    ./mysql-bin.000011
    ./mysql-bin.000012
    ./mysql-bin.000013
    ./mysql-bin.000014

**2、二进制日志文件的切换**

**执行flush logs产生新的二进制日志：**  
mysql> show master status;  
\+------------------+----------+--------------+------------------+  
| File             | Position | Binlog\_Do\_DB | Binlog\_Ignore\_DB |  
\+------------------+----------+--------------+------------------+  
| mysql-bin.000011 |      114 |              |                  |  
\+------------------+----------+--------------+------------------+  
1 row in set (0.00 sec)  
  
mysql> flush logs;  
Query OK, 0 rows affected (0.08 sec)

[root@mysrv mysql]# ls -l
    total 29936
    -rw-rw---- 1 mysql mysql       56 Jun 30 17:12 auto.cnf
    -rw-rw---- 1 mysql mysql 18874368 Jul  8 15:17 ibdata1
    -rw-rw---- 1 mysql mysql  5242880 Jul  8 15:17 ib_logfile0
    -rw-rw---- 1 mysql mysql  5242880 Jun 30 17:12 ib_logfile1
    -rw-rw---- 1 mysql mysql      124 Jul  8 15:17 master.info
    drwx--S--- 2 mysql mysql     4096 Jun 30 17:15 mysql
    -rw-rw---- 1 mysql mysql      114 Jun 30 17:15 mysql-bin.000001
    -rw-rw---- 1 mysql mysql    63438 Jun 30 17:15 mysql-bin.000002
    -rw-rw---- 1 mysql mysql  1096670 Jun 30 17:15 mysql-bin.000003
    -rw-rw---- 1 mysql mysql      252 Jun 30 17:26 mysql-bin.000004
    -rw-rw---- 1 mysql mysql      114 Jun 30 17:40 mysql-bin.000005
    -rw-rw---- 1 mysql mysql      133 Jun 30 17:51 mysql-bin.000006
    -rw-rw---- 1 mysql mysql      114 Jun 30 17:56 mysql-bin.000007
    -rw-rw---- 1 mysql mysql      114 Jul  8 15:17 mysql-bin.000008
    -rw-rw---- 1 mysql mysql      157 Jul  8 15:18 mysql-bin.000009
    -rw-rw---- 1 mysql mysql      157 Jul  8 15:20 mysql-bin.000010
    -rw-rw---- 1 mysql mysql      157 Jul  8 15:24 mysql-bin.000011
    -rw-rw---- 1 mysql mysql      114 Jul  8 15:24 mysql-bin.000012
    -rw-rw---- 1 mysql mysql      228 Jul  8 15:24 mysql-bin.index
    -rw-rw---- 1 mysql mysql    25322 Jul  8 15:24 mysrv.err
    -rw-rw---- 1 mysql mysql        5 Jul  8 15:17 mysrv.pid
    -rw-rw---- 1 mysql mysql      163 Jul  8 15:18 mysrv-relay-bin.000003
    -rw-rw---- 1 mysql mysql      163 Jul  8 15:20 mysrv-relay-bin.000004
    -rw-rw---- 1 mysql mysql      163 Jul  8 15:24 mysrv-relay-bin.000005
    -rw-rw---- 1 mysql mysql      114 Jul  8 15:24 mysrv-relay-bin.000006
    -rw-rw---- 1 mysql mysql      100 Jul  8 15:24 mysrv-relay-bin.index
    drwx--S--- 2 mysql mysql     4096 Jun 30 17:15 performance_schema
    drwx--S--- 2 mysql mysql     4096 Jun 30 18:15 prod
    -rw-rw---- 1 mysql mysql       58 Jul  8 15:24 relay-log.info
    drwx--S--- 2 mysql mysql     4096 Jun 30 17:15 test

**采用mysqladmin切换：**

\[root@mysrv ~\]\# mysqladmin flush-logs -u root -p  
Enter password:

[root@mysrv mysql]# ls -l
    total 29944
    -rw-rw---- 1 mysql mysql       56 Jun 30 17:12 auto.cnf
    -rw-rw---- 1 mysql mysql 18874368 Jul  8 15:17 ibdata1
    -rw-rw---- 1 mysql mysql  5242880 Jul  8 15:17 ib_logfile0
    -rw-rw---- 1 mysql mysql  5242880 Jun 30 17:12 ib_logfile1
    -rw-rw---- 1 mysql mysql      124 Jul  8 15:17 master.info
    drwx--S--- 2 mysql mysql     4096 Jun 30 17:15 mysql
    -rw-rw---- 1 mysql mysql      114 Jun 30 17:15 mysql-bin.000001
    -rw-rw---- 1 mysql mysql    63438 Jun 30 17:15 mysql-bin.000002
    -rw-rw---- 1 mysql mysql  1096670 Jun 30 17:15 mysql-bin.000003
    -rw-rw---- 1 mysql mysql      252 Jun 30 17:26 mysql-bin.000004
    -rw-rw---- 1 mysql mysql      114 Jun 30 17:40 mysql-bin.000005
    -rw-rw---- 1 mysql mysql      133 Jun 30 17:51 mysql-bin.000006
    -rw-rw---- 1 mysql mysql      114 Jun 30 17:56 mysql-bin.000007
    -rw-rw---- 1 mysql mysql      114 Jul  8 15:17 mysql-bin.000008
    -rw-rw---- 1 mysql mysql      157 Jul  8 15:18 mysql-bin.000009
    -rw-rw---- 1 mysql mysql      157 Jul  8 15:20 mysql-bin.000010
    -rw-rw---- 1 mysql mysql      157 Jul  8 15:24 mysql-bin.000011
    -rw-rw---- 1 mysql mysql      157 Jul  8 15:34 mysql-bin.000012
    -rw-rw---- 1 mysql mysql      114 Jul  8 15:34 mysql-bin.000013
    -rw-rw---- 1 mysql mysql      247 Jul  8 15:34 mysql-bin.index
    -rw-rw---- 1 mysql mysql    26536 Jul  8 15:34 mysrv.err
    -rw-rw---- 1 mysql mysql        5 Jul  8 15:17 mysrv.pid
    -rw-rw---- 1 mysql mysql      163 Jul  8 15:18 mysrv-relay-bin.000003
    -rw-rw---- 1 mysql mysql      163 Jul  8 15:20 mysrv-relay-bin.000004
    -rw-rw---- 1 mysql mysql      163 Jul  8 15:24 mysrv-relay-bin.000005
    -rw-rw---- 1 mysql mysql      163 Jul  8 15:34 mysrv-relay-bin.000006
    -rw-rw---- 1 mysql mysql      114 Jul  8 15:34 mysrv-relay-bin.000007
    -rw-rw---- 1 mysql mysql      125 Jul  8 15:34 mysrv-relay-bin.index
    drwx--S--- 2 mysql mysql     4096 Jun 30 17:15 performance_schema
    drwx--S--- 2 mysql mysql     4096 Jun 30 18:15 prod
    -rw-rw---- 1 mysql mysql       58 Jul  8 15:34 relay-log.info
    drwx--S--- 2 mysql mysql     4096 Jun 30 17:15 test

**\[root@mysrv ~\]\# mysqladmin refresh -u root -p  
Enter password: **

[root@mysrv mysql]# ls -l
    total 29952
    -rw-rw---- 1 mysql mysql       56 Jun 30 17:12 auto.cnf
    -rw-rw---- 1 mysql mysql 18874368 Jul  8 15:17 ibdata1
    -rw-rw---- 1 mysql mysql  5242880 Jul  8 15:17 ib_logfile0
    -rw-rw---- 1 mysql mysql  5242880 Jun 30 17:12 ib_logfile1
    -rw-rw---- 1 mysql mysql      124 Jul  8 15:17 master.info
    drwx--S--- 2 mysql mysql     4096 Jun 30 17:15 mysql
    -rw-rw---- 1 mysql mysql      114 Jun 30 17:15 mysql-bin.000001
    -rw-rw---- 1 mysql mysql    63438 Jun 30 17:15 mysql-bin.000002
    -rw-rw---- 1 mysql mysql  1096670 Jun 30 17:15 mysql-bin.000003
    -rw-rw---- 1 mysql mysql      252 Jun 30 17:26 mysql-bin.000004
    -rw-rw---- 1 mysql mysql      114 Jun 30 17:40 mysql-bin.000005
    -rw-rw---- 1 mysql mysql      133 Jun 30 17:51 mysql-bin.000006
    -rw-rw---- 1 mysql mysql      114 Jun 30 17:56 mysql-bin.000007
    -rw-rw---- 1 mysql mysql      114 Jul  8 15:17 mysql-bin.000008
    -rw-rw---- 1 mysql mysql      157 Jul  8 15:18 mysql-bin.000009
    -rw-rw---- 1 mysql mysql      157 Jul  8 15:20 mysql-bin.000010
    -rw-rw---- 1 mysql mysql      157 Jul  8 15:24 mysql-bin.000011
    -rw-rw---- 1 mysql mysql      157 Jul  8 15:34 mysql-bin.000012
    -rw-rw---- 1 mysql mysql      157 Jul  8 15:36 mysql-bin.000013
    -rw-rw---- 1 mysql mysql      114 Jul  8 15:36 mysql-bin.000014
    -rw-rw---- 1 mysql mysql      266 Jul  8 15:36 mysql-bin.index
    -rw-rw---- 1 mysql mysql    26671 Jul  8 15:35 mysrv.err
    -rw-rw---- 1 mysql mysql        5 Jul  8 15:17 mysrv.pid
    -rw-rw---- 1 mysql mysql      163 Jul  8 15:18 mysrv-relay-bin.000003
    -rw-rw---- 1 mysql mysql      163 Jul  8 15:20 mysrv-relay-bin.000004
    -rw-rw---- 1 mysql mysql      163 Jul  8 15:24 mysrv-relay-bin.000005
    -rw-rw---- 1 mysql mysql      163 Jul  8 15:34 mysrv-relay-bin.000006
    -rw-rw---- 1 mysql mysql      163 Jul  8 15:36 mysrv-relay-bin.000007
    -rw-rw---- 1 mysql mysql      114 Jul  8 15:36 mysrv-relay-bin.000008
    -rw-rw---- 1 mysql mysql      150 Jul  8 15:36 mysrv-relay-bin.index
    drwx--S--- 2 mysql mysql     4096 Jun 30 17:15 performance_schema
    drwx--S--- 2 mysql mysql     4096 Jun 30 18:15 prod
    -rw-rw---- 1 mysql mysql       58 Jul  8 15:36 relay-log.info
    drwx--S--- 2 mysql mysql     4096 Jun 30 17:15 test

**3、查看和日志相关的参数：**

**mysql 二进制日志应用案例：  
  
事务操作生成二进制日志：**

mysql> use prod;  
Database changed  
mysql> show tables;  
\+----------------+  
| Tables\_in\_prod |  
\+----------------+  
| t1             |  
\+----------------+  
1 row in set (0.02 sec)  
  
mysql> select \* from t1;  
\+------+-------+  
| id   | name  |  
\+------+-------+  
|    1 | tom   |  
|    2 | scott |  
|    3 | jerry |  
\+------+-------+  
3 rows in set (0.00 sec)  
  
mysql> insert into t1 values (4,'rose');  
Query OK, 1 row affected (0.03 sec)

mysql> commit;  
Query OK, 0 rows affected (0.00 sec)  
  
**查看生成的二进制日志内容：**

**\[root@mysrv mysql\]\# mysqlbinlog mysql-bin.000014**  
/\*!40019 SET @@session.max\_insert\_delayed\_threads=0\*/;  
/\*!50003 SET @OLD\_COMPLETION\_TYPE=@@COMPLETION\_TYPE,COMPLETION\_TYPE=0\*/;  
DELIMITER /\*!\*/;  
\# at 4  
\#150708 15:36:17 server id 2  end\_log\_pos 114   Start: binlog v 4, server v 5.6.4-m7-log created 150708 15:36:17  
\# Warning: this binlog is either in use or was not closed properly.  
BINLOG '  
8dKcVQ8CAAAAbgAAAHIAAAABAAQANS42LjQtbTctbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA  
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVgAEGggAAAAICAgCAAAAANxYJ/w=  
'/\*!\*/;  
\# at 114  
\#150708 15:39:44 server id 2  end\_log\_pos 189   Query   thread\_id=5     exec\_time=0     error\_code=0  
SET TIMESTAMP=1436341184/\*!\*/;  
SET @@session.pseudo\_thread\_id=5/\*!\*/;  
SET @@session.foreign\_key\_checks=1, @@session.sql\_auto\_is\_null=0, @@session.unique\_checks=1, @@session.autocommit=1/\*!\*/;  
SET @@session.sql\_mode=0/\*!\*/;  
SET @@session.auto\_increment\_increment=1, @@session.auto\_increment\_offset=1/\*!\*/;  
/\*!\\C utf8 \*//\*!\*/;  
SET @@session.character\_set\_client=33,@@session.collation\_connection=33,@@session.collation\_server=8/\*!\*/;  
SET @@session.lc\_time\_names=0/\*!\*/;  
SET @@session.collation\_database=DEFAULT/\*!\*/;  
BEGIN  
/\*!\*/;  
\# at 189  
\#150708 15:39:44 server id 2  end\_log\_pos 291   Query   thread\_id=5     exec\_time=0     error\_code=0  
use prod/\*!\*/;  
SET TIMESTAMP=1436341184/\*!\*/;  
insert into t1 values (4,'rose')  
/\*!\*/;  
\# at 291  
\#150708 15:39:44 server id 2  end\_log\_pos 318   Xid = 23  
COMMIT/\*!\*/;  
DELIMITER ;  
\# End of log file  
ROLLBACK /\* added by mysqlbinlog \*/;  
/\*!50003 SET COMPLETION\_TYPE=@OLD\_COMPLETION\_TYPE\*/;  
  
mysql> insert into t1 values (5,'john');  
Query OK, 1 row affected (0.02 sec)  
  
mysql> commit;  
Query OK, 0 rows affected (0.00 sec)  
  
**\[root@mysrv mysql\]\# strings mysql-bin.000014**  
5.6.4-m7-log  
prod  
prod  
BEGIN  
prod  
prod  
**insert into t1 values (4,'rose')**  
prod  
prod  
BEGINw  
prod  
prod  
**insert into t1 values (5,'john')w**

**\[root@mysrv mysql\]\# mysqlbinlog mysql-bin.000014**  
/\*!40019 SET @@session.max\_insert\_delayed\_threads=0\*/;  
/\*!50003 SET @OLD\_COMPLETION\_TYPE=@@COMPLETION\_TYPE,COMPLETION\_TYPE=0\*/;  
DELIMITER /\*!\*/;  
\# at 4  
\#150708 15:36:17 server id 2  end\_log\_pos 114   Start: binlog v 4, server v 5.6.4-m7-log created 150708 15:36:17  
\# Warning: this binlog is either in use or was not closed properly.  
BINLOG '  
8dKcVQ8CAAAAbgAAAHIAAAABAAQANS42LjQtbTctbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA  
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVgAEGggAAAAICAgCAAAAANxYJ/w=  
'/\*!\*/;  
\# at 114  
\#150708 15:39:44 server id 2  end\_log\_pos 189   Query   thread\_id=5     exec\_time=0     error\_code=0  
SET TIMESTAMP=1436341184/\*!\*/;  
SET @@session.pseudo\_thread\_id=5/\*!\*/;  
SET @@session.foreign\_key\_checks=1, @@session.sql\_auto\_is\_null=0, @@session.unique\_checks=1, @@session.autocommit=1/\*!\*/;  
SET @@session.sql\_mode=0/\*!\*/;  
SET @@session.auto\_increment\_increment=1, @@session.auto\_increment\_offset=1/\*!\*/;  
/\*!\\C utf8 \*//\*!\*/;  
SET @@session.character\_set\_client=33,@@session.collation\_connection=33,@@session.collation\_server=8/\*!\*/;  
SET @@session.lc\_time\_names=0/\*!\*/;  
SET @@session.collation\_database=DEFAULT/\*!\*/;  
BEGIN  
/\*!\*/;  
\# at 189  
\#150708 15:39:44 server id 2  end\_log\_pos 291   Query   thread\_id=5     exec\_time=0     error\_code=0  
use prod/\*!\*/;  
SET TIMESTAMP=1436341184/\*!\*/;  
**insert into t1 values (4,'rose')**  
/\*!\*/;  
\# at 291  
\#150708 15:39:44 server id 2  end\_log\_pos 318   Xid = 23  
COMMIT/\*!\*/;  
\# at 318  
\#150708 15:42:47 server id 2  end\_log\_pos 393   Query   thread\_id=5     exec\_time=0     error\_code=0  
SET TIMESTAMP=1436341367/\*!\*/;  
BEGIN  
/\*!\*/;  
\# at 393  
\#150708 15:42:47 server id 2  end\_log\_pos 495   Query   thread\_id=5     exec\_time=0     error\_code=0  
SET TIMESTAMP=1436341367/\*!\*/;  
**insert into t1 values (5,'john')**  
/\*!\*/;  
\# at 495  
\#150708 15:42:47 server id 2  end\_log\_pos 522   Xid = 25  
COMMIT/\*!\*/;  
DELIMITER ;  
\# End of log file  
ROLLBACK /\* added by mysqlbinlog \*/;  
/\*!50003 SET COMPLETION\_TYPE=@OLD\_COMPLETION\_TYPE\*/;  
\[root@mysrv mysql\]\#   
  
**查看生成的二进制日志：  
mysql> show binary logs;**

+------------------+-----------+
    | Log_name         | File_size |
    +------------------+-----------+
    | mysql-bin.000001 |       114 |
    | mysql-bin.000002 |     63438 |
    | mysql-bin.000003 |   1096670 |
    | mysql-bin.000004 |       252 |
    | mysql-bin.000005 |       114 |
    | mysql-bin.000006 |       133 |
    | mysql-bin.000007 |       114 |
    | mysql-bin.000008 |       114 |
    | mysql-bin.000009 |       157 |
    | mysql-bin.000010 |       157 |
    | mysql-bin.000011 |       157 |
    | mysql-bin.000012 |       157 |
    | mysql-bin.000013 |       157 |
    | mysql-bin.000014 |       522 |
    +------------------+-----------+
    14 rows in set (0.00 sec)

**查看日志记录的事件：**

mysql> show binlog events;
    +------------------+-----+-------------+-----------+-------------+-----------------------------------------+
    | Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                    |
    +------------------+-----+-------------+-----------+-------------+-----------------------------------------+
    | mysql-bin.000001 |   4 | Format_desc |         1 |         114 | Server ver: 5.6.4-m7-log, Binlog ver: 4 |
    +------------------+-----+-------------+-----------+-------------+-----------------------------------------+
    1 row in set (0.06 sec)
    
    
    mysql> show binlog events in 'mysql-bin.000014';
    +------------------+-----+-------------+-----------+-------------+----------------------------------------------+
    | Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                         |
    +------------------+-----+-------------+-----------+-------------+----------------------------------------------+
    | mysql-bin.000014 |   4 | Format_desc |         2 |         114 | Server ver: 5.6.4-m7-log, Binlog ver: 4      |
    | mysql-bin.000014 | 114 | Query       |         2 |         189 | BEGIN                                        |
    | mysql-bin.000014 | 189 | Query       |         2 |         291 | use `prod`; insert into t1 values (4,'rose') |
    | mysql-bin.000014 | 291 | Xid         |         2 |         318 | COMMIT /* xid=23 */                          |
    | mysql-bin.000014 | 318 | Query       |         2 |         393 | BEGIN                                        |
    | mysql-bin.000014 | 393 | Query       |         2 |         495 | use `prod`; insert into t1 values (5,'john') |
    | mysql-bin.000014 | 495 | Xid         |         2 |         522 | COMMIT /* xid=25 */                          |
    +------------------+-----+-------------+-----------+-------------+----------------------------------------------+
    7 rows in set (0.00 sec)

**数据恢复案例：**

**1、模拟数据环境**  
  
mysql> select \* from t1;  
\+------+-------+  
| id   | name  |  
\+------+-------+  
|    1 | tom   |  
|    2 | scott |  
|    3 | jerry |  
|    4 | rose  |  
|    5 | john  |  
\+------+-------+  
5 rows in set (0.00 sec)  
  
**删除数据：**  
mysql> delete from t1;  
Query OK, 5 rows affected (0.02 sec)

mysql> commit;  
Query OK, 0 rows affected (0.00 sec)  
  
mysql> select \* from t1;  
Empty set (0.00 sec)  
  
  
**利用日志恢复，将日志生成文本文件进行分析：  
\[root@mysrv mysql\]\# mysqlbinlog mysql-bin.000014 >/home/mysql/log14.txt**  
  
\[root@mysrv mysql\]\# cat log14.txt   
/\*!40019 SET @@session.max\_insert\_delayed\_threads=0\*/;  
/\*!50003 SET @OLD\_COMPLETION\_TYPE=@@COMPLETION\_TYPE,COMPLETION\_TYPE=0\*/;  
DELIMITER /\*!\*/;  
\# at 4  
\#150708 15:36:17 server id 2  end\_log\_pos 114   Start: binlog v 4, server v 5.6.4-m7-log created 150708 15:36:17  
\# Warning: this binlog is either in use or was not closed properly.  
BINLOG '  
8dKcVQ8CAAAAbgAAAHIAAAABAAQANS42LjQtbTctbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA  
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVgAEGggAAAAICAgCAAAAANxYJ/w=  
'/\*!\*/;  
\# at 114  
\#150708 15:39:44 server id 2  end\_log\_pos 189   Query   thread\_id=5     exec\_time=0     error\_code=0  
SET TIMESTAMP=1436341184/\*!\*/;  
SET @@session.pseudo\_thread\_id=5/\*!\*/;  
SET @@session.foreign\_key\_checks=1, @@session.sql\_auto\_is\_null=0, @@session.unique\_checks=1, @@session.autocommit=1/\*!\*/;  
SET @@session.sql\_mode=0/\*!\*/;  
SET @@session.auto\_increment\_increment=1, @@session.auto\_increment\_offset=1/\*!\*/;  
/\*!\\C utf8 \*//\*!\*/;  
SET @@session.character\_set\_client=33,@@session.collation\_connection=33,@@session.collation\_server=8/\*!\*/;  
SET @@session.lc\_time\_names=0/\*!\*/;  
SET @@session.collation\_database=DEFAULT/\*!\*/;  
BEGIN  
/\*!\*/;  
\# at 189  
\#150708 15:39:44 server id 2  end\_log\_pos 291   Query   thread\_id=5     exec\_time=0     error\_code=0  
use prod/\*!\*/;  
SET TIMESTAMP=1436341184/\*!\*/;  
insert into t1 values (4,'rose')  
/\*!\*/;  
**\# at 291**  
\#150708 15:39:44 server id 2  end\_log\_pos 318   Xid = 23  
COMMIT/\*!\*/;  
**\# at 318**  
\#150708 15:42:47 server id 2  end\_log\_pos 393   Query   thread\_id=5     exec\_time=0     error\_code=0  
SET TIMESTAMP=1436341367/\*!\*/;  
BEGIN  
/\*!\*/;  
**\# at 393**  
\#150708 15:42:47 server id 2  end\_log\_pos 495   Query   thread\_id=5     exec\_time=0     error\_code=0  
SET TIMESTAMP=1436341367/\*!\*/;  
insert into t1 values (5,'john')  
/\*!\*/;  
**\# at 495**  
\#150708 15:42:47 server id 2  end\_log\_pos 522   Xid = 25  
COMMIT/\*!\*/;  
\# at 522  
\#150708 15:56:57 server id 2  end\_log\_pos 597   Query   thread\_id=5     exec\_time=0     error\_code=0  
SET TIMESTAMP=1436342217/\*!\*/;  
BEGIN  
/\*!\*/;  
**\# at 597        ；；日志记录的操作事件的编号，如果要恢复前面的数据，需要在delete前停止             **  
\#150708 15:56:57 server id 2  end\_log\_pos 681   Query   thread\_id=5     exec\_time=0     error\_code=0  
SET TIMESTAMP=1436342217/\*!\*/;  
**delete from t1**  
/\*!\*/;  
**\# at 681**  
\#150708 15:56:57 server id 2  end\_log\_pos 708   Xid = 31  
COMMIT/\*!\*/;  
DELIMITER ;  
\# End of log file  
ROLLBACK /\* added by mysqlbinlog \*/;  
/\*!50003 SET COMPLETION\_TYPE=@OLD\_COMPLETION\_TYPE\*/;  
\[root@mysrv mysql\]\#   
  
**利用二进制日志恢复：  
\[root@mysrv mysql\]\# mysqlbinlog mysql-bin.000014 --stop-pos=597 |mysql -u root -p**  
Enter password:   
  
\---在delete操作前停止日志的应用

**查看已恢复的数据：**  
  
mysql> select \* from t1;  
\+------+------+  
| id   | name |  
\+------+------+  
|    4 | rose |  
|    5 | john |  
\+------+------+  
2 rows in set (0.00 sec)  
  
**---前面三行数据由于从master同步而来，在slave日志中没有记载，此次不再做恢复操作**  
  
**---数据恢复成功！**

[Oracle]: http://www.linuxidc.com/topicnews.aspx?tid=12