MySQL Binlog详解

一个数据库必须拥有完善的日志系统，MySQL的日志系统组成如下：

• 错误日志（error log）：记录启动、运行或停止mysqld时出现的问题。
• 通用日志（general log）：记录建立的客户端连接和执行的语句。
• 二进制日志（binary log）：记录所有对数据库的更改，还用于复制。
• 慢查询日志（slow query log）：记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询。
• Innodb事务日志：事务日志或者叫redo log/undo log

缺省情况下，MySQL只打开错误日志，并且默认所有日志创建于mysqld数据目录中。
可以通过刷新日志，来强制mysqld来关闭和重新打开日志文件（或者在某些情况下切换到一个新的日志）。
当你执行一个FLUSH
LOGS语句或执行mysqladmin flush-logs或mysqladmin refresh时，则日志被刷新。

本文只是对binary log做一个详细解释。

二进制日志基础

1. binlog包含的内容和作用：

对数据库的所有DML以及一些DDL更改操作都会记录在二进制日志中
主要目的是尽可能的将数据库恢复到数据库故障点，因为二进制日志包含数据库的所有更新

binlog日志属于二进制文件，我们可以从binlog提取出来生成可阅读的SQL语句来重建当前数据库以及根据需要实现时点恢复(Point-In-Time)或不完全恢复。
用于在主从复制（mysql
replication）,
启用该选项数据库性能降低1%，但保障数据库完整性，对于重要数据库值得以性能换完整。有些类似于oracle开启归档模式。

2. binlog相关变量和参数

• 命令行参数

--log-bin [=file_name]

设置此参数表示启用binlog功能，并制定路径名称。

--log-bin-index[=file]

设置此参数是指定二进制索引文件的路径与名称。

--max_binlog_size

binlog最大值，最大和默认值是1GB，该设置并不能严格控制Binlog的大小，尤其是Binlog比较靠近最大值而又遇到一个比较大事务时，为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的所有SQL都记录进当前日志，直到事务结束。

  --binlog-do-db=db_name

此参数表示只记录指定数据库的二进制日志

--binlog-ignore-db=db_name

此参数表示不记录指定的数据库的二进制日志

• 系统变量

log_bin

binlog_cache_size

此参数表示binlog使用的内存大小，可以通过状态变量binlog_cache_use和binlog_cache_disk_use来帮助测试。

max_binlog_cache_size

此参数表示binlog使用的内存最大的尺寸

binlog_cache_use

使用二进制日志缓存的事务数量

binlog_cache_disk_use

使用二进制日志缓存但超过binlog_cache_size值并使用临时文件来保存事务中的语句的事务数量。

binlog_do_db

binlog_ignore_db

sync_binlog

这个参数直接影响mysql的性能和完整性。

sync_binlog=0：

mysql中默认的设置是sync_binlog=0，即不做任何强制性的磁盘刷新指令，这时性能是最好的，但风险也是最大的。当事务提交后，mysql仅仅是将binlog_cache中的数据写入binlog文件，但不执行fsync之类的磁盘同步，而让Filesystem自行决定什么时候来做同步，这个是性能最好的。但是一旦系统Crash，在文件系统缓存中的所有Binlog信息都会丢失。

3. 日志格式

• 基于SQL语句的复制(statement-based replication,SBR)，
• 基于行的复制(row-based replication,RBR)，
• 混合模式复制(mixed-based replication,MBR)。

之前的MySQL一直都只有基于statement的复制模式，直到5.1.5版本的MySQL才开始支持row level的复制。

从5.0开始，MySQL的复制已经解决了大量老版本中出现的无法正确复制的问题。

但是由于存储过程的出现，给MySQL Replication复制又带来了更大的新挑战。

另外，看到官方文档说，从5.1.8版本开始，MySQL提供了除Statement Level和Row
Level之外的第三种复制模式：Mixed，实际上就前两种模式的结合。

在Mixed模式下，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。

新版本中的Statement Level还是和以前一样，仅仅记录执行的语句。

而新版本的MySQL中对row level模式也被做了优化，并不是所有的修改都会以row
level来记录，像遇到表结构变更的时候就会以statement模式来记录，如果sql语句确实就是update或者delete等修改数据的语句，
那么还是会记录所有行的变更。

静态设置binlog格式：

vi my.cnf
  log-bin = mysql-bin
  #binlog_format = "STATEMENT"
  #binlog_format = "ROW"
  binlog_format = "MIXED"

动态修改binlog格式：

  mysql> SET SESSION binlog_format = 'STATEMENT';
  mysql> SET SESSION binlog_format = 'ROW';
  mysql> SET SESSION binlog_format = 'MIXED';
  mysql> SET GLOBAL binlog_format = 'STATEMENT';
  mysql> SET GLOBAL binlog_format = 'ROW';
  mysql> SET GLOBAL binlog_format = 'MIXED';

4. 日志切换策略

  使用索引来循环记录文件，在以下条件将循环至下一个索引

a.服务器重启

b.服务器被更新

c.日志达到了最大日志长度max_binlog_size

d.日志被手动刷新mysql> flush logs;

5. 清除binlog

PURGE {MASTER|BINARY} LOGS TO 'log_name' ; //log_name不会被清除

PURGE {MASTER|BINARY} LOGS BEFORE 'date' ; //date不会被清除

RESET MASTER; //删除之前所有的binlog，并重新生成新的binlog，后缀从000001开始。

二进制日志操作演示

a、启用二进制日志
--当前环境
root@localhost[(none)]> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.5.39                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.5.39                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+

root@localhost[(none)]> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   | --该参数用于设定是否启用二进制日志
| log_bin_trust_function_creators | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+

--以下为binary log相关参数
root@localhost[(none)]> show variables like '%binlog%';
+-----------------------------------------+----------------------+
| Variable_name                           | Value                |
+-----------------------------------------+----------------------+
| binlog_cache_size                       | 32768                |
| binlog_direct_non_transactional_updates | OFF                  |
| binlog_format                           | STATEMENT            |
| binlog_stmt_cache_size                  | 32768                |
| innodb_locks_unsafe_for_binlog          | OFF                  |
| max_binlog_cache_size                   | 18446744073709547520 |
| max_binlog_size                         | 1073741824           |
| max_binlog_stmt_cache_size              | 18446744073709547520 |
| sync_binlog                             | 0                    |
+-----------------------------------------+----------------------+

--当前mysql服务器数据文件的缺省位置
root@localhost[(none)]> show variables like '%datadir%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+

--停止mysql服务器
suse11b:~ # service mysql stop
Shutting down MySQL....                                               done

--编辑my.cnf来设定binary log日志位置(注，配置二进制日志路径及文件名后，系统变量log_bin被自动置为on)
suse11b:~ # vi /etc/my.cnf
suse11b:~ # grep -v ^# /etc/my.cnf
[mysqld]
log-error=/tmp/suse11b.err
log_bin=/var/lib/mysql/binarylog/binlog
suse11b:~ # mkdir -p /var/lib/mysql/binarylog
suse11b:~ # chown -R mysql:mysql /var/lib/mysql/binarylog

suse11b:~ # /etc/init.d/mysql start
Starting MySQL..                                                      done
suse11b:~ # ls -hltr /var/lib/mysql/binarylog/*
-rw-rw---- 1 mysql mysql  39 Oct  3 13:41 /var/lib/mysql/binarylog/binlog.index  #索引文件
-rw-rw---- 1 mysql mysql 107 Oct  3 13:41 /var/lib/mysql/binarylog/binlog.000001 #日志文件

b、切换日志
suse11b:~ # mysql -uroot -pxxx
root@localhost[(none)]> flush logs;
Query OK, 0 rows affected (0.04 sec)

root@localhost[(none)]> system ls -hltr /var/lib/mysql/binarylog/*
-rw-rw---- 1 mysql mysql  78 Oct  3 13:43 /var/lib/mysql/binarylog/binlog.index
-rw-rw---- 1 mysql mysql 107 Oct  3 13:43 /var/lib/mysql/binarylog/binlog.000002  #切换后产生了000002
-rw-rw---- 1 mysql mysql 147 Oct  3 13:43 /var/lib/mysql/binarylog/binlog.000001

root@localhost[(none)]> system mysqladmin flush-logs    #使用mysqladmin命令行工具flush-logs方式切换日志
root@localhost[(none)]> system ls -hltr /var/lib/mysql/binarylog/*
-rw-rw---- 1 mysql mysql 147 Oct  3 13:43 /var/lib/mysql/binarylog/binlog.000001
-rw-rw---- 1 mysql mysql 117 Oct  3 13:45 /var/lib/mysql/binarylog/binlog.index
-rw-rw---- 1 mysql mysql 107 Oct  3 13:45 /var/lib/mysql/binarylog/binlog.000003  #切换后产生了000003
-rw-rw---- 1 mysql mysql 147 Oct  3 13:45 /var/lib/mysql/binarylog/binlog.000002

root@localhost[(none)]> system mysqladmin refresh       #使用mysqladmin命令行工具refresh方式切换日志
root@localhost[(none)]> system ls -hltr /var/lib/mysql/binarylog/*
-rw-rw---- 1 mysql mysql 147 Oct  3 13:43 /var/lib/mysql/binarylog/binlog.000001
-rw-rw---- 1 mysql mysql 147 Oct  3 13:45 /var/lib/mysql/binarylog/binlog.000002
-rw-rw---- 1 mysql mysql 156 Oct  3 13:46 /var/lib/mysql/binarylog/binlog.index
-rw-rw---- 1 mysql mysql 107 Oct  3 13:46 /var/lib/mysql/binarylog/binlog.000004  #切换后产生了000004
-rw-rw---- 1 mysql mysql 147 Oct  3 13:46 /var/lib/mysql/binarylog/binlog.000003 

c、模拟产生二进制日志及查看内容
root@localhost[(none)]> create database tempdb;
Query OK, 1 row affected (0.00 sec)

root@localhost[(none)]> use tempdb
Database changed
root@localhost[tempdb]> create table tb1(id smallint,val varchar(10));
Query OK, 0 rows affected (0.00 sec)

root@localhost[tempdb]> insert into tb1 values(1,'jack');
Query OK, 1 row affected (0.01 sec)

root@localhost[tempdb]> system strings /var/lib/mysql/binarylog/binlog.000004
bin?8.T
5.5.39-log
z=.T
tempdb
create database tempdb
tempdb
create table tb1(id smallint,val varchar(10))
tempdb
BEGIN
tempdb
insert into tb1 values(1,'jack')

root@localhost[tempdb]> system more /var/lib/mysql/binarylog/binlog.index
/var/lib/mysql/binarylog/binlog.000001
/var/lib/mysql/binarylog/binlog.000002
/var/lib/mysql/binarylog/binlog.000003
/var/lib/mysql/binarylog/binlog.000004

--使用命令行工具mysqlbinlog直接提取二进制日志的内容
root@localhost[tempdb]> system mysqlbinlog /var/lib/mysql/binarylog/binlog.000004
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#141003 13:46:39 server id 1  end_log_pos 107   Start: binlog v 4, server v 5.5.39-log created 141003 13:46:39
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
PzguVA8BAAAAZwAAAGsAAAABAAQANS41LjM5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#141003 14:08:58 server id 1  end_log_pos 194   Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1412316538/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
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/*!*/;
create database tempdb
/*!*/;
# at 194
#141003 14:09:36 server id 1  end_log_pos 304   Query   thread_id=1     exec_time=0     error_code=0
use `tempdb`/*!*/;
SET TIMESTAMP=1412316576/*!*/;
create table tb1(id smallint,val varchar(10))
/*!*/;
# at 304
#141003 14:09:56 server id 1  end_log_pos 374   Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1412316596/*!*/;
BEGIN
/*!*/;
# at 374
#141003 14:09:56 server id 1  end_log_pos 471   Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1412316596/*!*/;
insert into tb1 values(1,'jack')
/*!*/;
# at 471
#141003 14:09:56 server id 1  end_log_pos 498   Xid = 25
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
--从以上的内容可以看出二进制日志记录了所有操作的DML语句及其开销，以及一些系统环境变量的信息。

d、管理二进制日志
--对于二进制日志，应尽可能保存在安全的位置，与数据分开存储
--使用show binary logs获取二进制日志相关信息
root@localhost[(none)]> help show binary logs;
Name: 'SHOW BINARY LOGS'
Description:
Syntax:
SHOW BINARY LOGS
SHOW MASTER LOGS

Lists the binary log files on the server. This statement is used as
part of the procedure described in [HELP PURGE BINARY LOGS], that shows
how to determine which logs can be purged.

root@localhost[tempdb]> show binary logs;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000001 |       147 |
| binlog.000002 |       147 |
| binlog.000003 |       147 |
| binlog.000004 |       498 |
+---------------+-----------+

show binlog events用于在二进制日志中显示事件。如果未指定'log_name'，则显示第一个二进制日志。
root@localhost[(none)]> help show binlog events;  --获取帮助信息
Name: 'SHOW BINLOG EVENTS'
Description:
Syntax:
SHOW BINLOG EVENTS
   [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

Shows the events in the binary log. If you do not specify 'log_name',
the first binary log is displayed.

root@localhost[(none)]> show binlog events;
+---------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name      | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+---------------+-----+-------------+-----------+-------------+---------------------------------------+
| binlog.000001 |   4 | Format_desc |         1 |         107 | Server ver: 5.5.39-log, Binlog ver: 4 |
| binlog.000001 | 107 | Rotate      |         1 |         147 | binlog.000002;pos=4                   |
+---------------+-----+-------------+-----------+-------------+---------------------------------------+

root@localhost[(none)]> show binlog events in 'binlog.000005';  --binlog.000005不存在，所以报错
ERROR 1220 (HY000): Error when executing command SHOW BINLOG EVENTS: Could not find target log

--下面的这个查询中，前面执行的DML在这里均可以看到
root@localhost[tempdb]> show binlog events in 'binlog.000004';
+---------------+-----+-------------+-----------+-------------+-------------------------------------------------------------+
| Log_name      | Pos | Event_type  | Server_id | End_log_pos | Info                                                        |
+---------------+-----+-------------+-----------+-------------+-------------------------------------------------------------+
| binlog.000004 |   4 | Format_desc |         1 |         107 | Server ver: 5.5.39-log, Binlog ver: 4                       |
| binlog.000004 | 107 | Query       |         1 |         194 | create database tempdb                                      |
| binlog.000004 | 194 | Query       |         1 |         304 | use `tempdb`; create table tb1(id smallint,val varchar(10)) |
| binlog.000004 | 304 | Query       |         1 |         374 | BEGIN                                                       |
| binlog.000004 | 374 | Query       |         1 |         471 | use `tempdb`; insert into tb1 values(1,'jack')              |
| binlog.000004 | 471 | Xid         |         1 |         498 | COMMIT /* xid=25 */                                         |
+---------------+-----+-------------+-----------+-------------+-------------------------------------------------------------+

root@localhost[tempdb]> show binlog events in 'binlog.000004' from 374;
+---------------+-----+------------+-----------+-------------+------------------------------------------------+
| Log_name      | Pos | Event_type | Server_id | End_log_pos | Info                                           |
+---------------+-----+------------+-----------+-------------+------------------------------------------------+
| binlog.000004 | 374 | Query      |         1 |         471 | use `tempdb`; insert into tb1 values(1,'jack') |
| binlog.000004 | 471 | Xid        |         1 |         498 | COMMIT /* xid=25 */                            |
+---------------+-----+------------+-----------+-------------+------------------------------------------------+

root@localhost[tempdb]> show binlog events in 'binlog.000004' from 374 limit 1;
+---------------+-----+------------+-----------+-------------+------------------------------------------------+
| Log_name      | Pos | Event_type | Server_id | End_log_pos | Info                                           |
+---------------+-----+------------+-----------+-------------+------------------------------------------------+
| binlog.000004 | 374 | Query      |         1 |         471 | use `tempdb`; insert into tb1 values(1,'jack') |
+---------------+-----+------------+-----------+-------------+------------------------------------------------+

d、删除历史日志
--使用purge手动删除指定日志
--使用expire-log-days删除失效日志，设置变量expire_logs_days，删除超出这个变量保留期之前的所有日志被删除
--自动日志删除通常发生在服务器启动以及日志flush
--reset master方式
root@localhost[(none)]> help purge;
Name: 'PURGE BINARY LOGS'
Description:
Syntax:
PURGE { BINARY | MASTER } LOGS
    { TO 'log_name' | BEFORE datetime_expr }

Examples:
PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';

root@localhost[tempdb]> purge binary logs to 'binlog.000003';
Query OK, 0 rows affected (0.12 sec)

root@localhost[tempdb]> show binary logs;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000003 |       147 |
| binlog.000004 |       498 |
+---------------+-----------+

root@localhost[tempdb]> system ls -hltr /var/lib/mysql/binarylog/*
-rw-rw---- 1 mysql mysql 147 Oct  3 13:46 /var/lib/mysql/binarylog/binlog.000003
-rw-rw---- 1 mysql mysql 498 Oct  3 14:09 /var/lib/mysql/binarylog/binlog.000004
-rw-rw---- 1 mysql mysql  78 Oct  3 14:23 /var/lib/mysql/binarylog/binlog.index

--使用before子句purge日志，binlog.000003被删除
root@localhost[tempdb]> purge binary logs before '2014-10-03 14:09:56';
Query OK, 0 rows affected (0.02 sec)

root@localhost[tempdb]> show binary logs;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000004 |       498 |
+---------------+-----------+

--重置所有日志
--reset master将删除在索引文件中列出所有的日志文件并重置索引文件，最后生成一个新的binlog文件。
--该操作之前先备份binlog至其它位置以备以后需要。
root@localhost[tempdb]> help reset master;
Name: 'RESET MASTER'
Description:
Syntax:
RESET MASTER

Deletes all binary log files listed in the index file, resets the
binary log index file to be empty, and creates a new binary log file.
This statement is intended to be used only when the master is started
for the first time.

root@localhost[tempdb]> reset master;
Query OK, 0 rows affected (0.13 sec)

root@localhost[tempdb]> show binary logs;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000001 |       107 |  --reset之后，从000001开始生成全新空日志
+---------------+-----------+

--expire_log系统变量控制二进制日志自动删除的天数。默认值为0,表示“没有自动删除”。启动时和二进制日志循环时可能删除。
root@localhost[tempdb]> show variables like 'expire_log%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 0     |
+------------------+-------+

root@localhost[tempdb]> set expire_logs_days=7;   --提示次系统变量为全局变量
ERROR 1229 (HY000): Variable 'expire_logs_days' is a GLOBAL variable and should be set with SET GLOBAL
root@localhost[tempdb]> set global expire_logs_days=7;  --设置
Query OK, 0 rows affected (0.01 sec)

root@localhost[tempdb]> select @@expire_logs_days;
+--------------------+
| @@expire_logs_days |
+--------------------+
|                  7 |
+--------------------+

mysqlbinlog解析提取binlog

1、提取mysqlbinlog的几种方式

a、使用show binlog events方式可以获取当前以及指定binlog的日志，不适宜提取大量日志。

b、使用mysqlbinlog命令行提取(适宜批量提取日志)。

2、演示show binlog events方式

mysql> show variables like 'version';
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| version       | 5.6.12-log |
+---------------+------------+

mysql> show binary logs;
+-----------------+-----------+
| Log_name        | File_size |
+-----------------+-----------+
| APP01bin.000001 |       120 |
+-----------------+-----------+

a、只查看第一个binlog文件的内容(show binlog events)
mysql> use replication;
Database changed
mysql> select * from tb;
+------+-------+
| id   | val   |
+------+-------+
|    1 | robin |
+------+-------+

mysql> insert into tb values(2,'jack');
Query OK, 1 row affected (0.02 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tb values(3,'fred');
Query OK, 1 row affected (0.00 sec)

mysql> show binary logs;
+-----------------+-----------+
| Log_name        | File_size |
+-----------------+-----------+
| APP01bin.000001 |       409 |
| APP01bin.000002 |       363 |
+-----------------+-----------+

mysql> show binlog events;
+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------+
| Log_name        | Pos | Event_type  | Server_id | End_log_pos | Info                                               |
+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------+
| APP01bin.000001 |   4 | Format_desc |        11 |         120 | Server ver: 5.6.12-log, Binlog ver: 4              |
| APP01bin.000001 | 120 | Query       |        11 |         213 | BEGIN                                              |
| APP01bin.000001 | 213 | Query       |        11 |         332 | use `replication`; insert into tb values(2,'jack') |
| APP01bin.000001 | 332 | Xid         |        11 |         363 | COMMIT /* xid=382 */                               |
| APP01bin.000001 | 363 | Rotate      |        11 |         409 | APP01bin.000002;pos=4                              |
+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------+
-- 在上面的结果中第3行可以看到我们执行的SQL语句，第4行为自动提交

b、查看指定binlog文件的内容(show binlog events in 'binname.xxxxx')
mysql> show binlog events in 'APP01bin.000002';
+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------+
| Log_name        | Pos | Event_type  | Server_id | End_log_pos | Info                                               |
+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------+
| APP01bin.000002 |   4 | Format_desc |        11 |         120 | Server ver: 5.6.12-log, Binlog ver: 4              |
| APP01bin.000002 | 120 | Query       |        11 |         213 | BEGIN                                              |
| APP01bin.000002 | 213 | Query       |        11 |         332 | use `replication`; insert into tb values(3,'fred') |
| APP01bin.000002 | 332 | Xid         |        11 |         363 | COMMIT /* xid=394 */                               |
+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------+

c、查看当前正在写入的binlog文件(show master status\G)
mysql> show master status\G
*************************** 1. row ***************************
             File: APP01bin.000002
         Position: 363
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

d、获取指定位置binlog的内容(show binlog events from)
mysql> show binlog events from 213;
+-----------------+-----+------------+-----------+-------------+----------------------------------------------------+
| Log_name        | Pos | Event_type | Server_id | End_log_pos | Info                                               |
+-----------------+-----+------------+-----------+-------------+----------------------------------------------------+
| APP01bin.000001 | 213 | Query      |        11 |         332 | use `replication`; insert into tb values(2,'jack') |
| APP01bin.000001 | 332 | Xid        |        11 |         363 | COMMIT /* xid=382 */                               |
| APP01bin.000001 | 363 | Rotate     |        11 |         409 | APP01bin.000002;pos=4                              |
+-----------------+-----+------------+-----------+-------------+----------------------------------------------------+

3、演示mysqlbinlog方式提取binlog

a、提取指定的binlog日志
# mysqlbinlog /opt/data/APP01bin.000001
# mysqlbinlog /opt/data/APP01bin.000001|grep insert
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
insert into tb values(2,'jack')

b、提取指定position位置的binlog日志
# mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001

c、提取指定position位置的binlog日志并输出到压缩文件
# mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001 |gzip >extra_01.sql.gz

d、提取指定position位置的binlog日志导入数据库
# mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001 | mysql -uroot -p

e、提取指定开始时间的binlog并输出到日志文件
# mysqlbinlog --start-datetime="2014-12-15 20:15:23" /opt/data/APP01bin.000002 --result-file=extra02.sql

f、提取指定位置的多个binlog日志文件
# mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001 /opt/data/APP01bin.000002|more

g、提取指定数据库binlog并转换字符集到UTF8
# mysqlbinlog --database=test --set-charset=utf8 /opt/data/APP01bin.000001 /opt/data/APP01bin.000002 >test.sql

h、远程提取日志，指定结束时间
# mysqlbinlog -urobin -p -h192.168.1.116 -P3306 --stop-datetime="2014-12-15 20:30:23" --read-from-remote-server mysql-bin.000033 |more

i、远程提取使用row格式的binlog日志并输出到本地文件
# mysqlbinlog -urobin -p -P3606 -h192.168.1.177 --read-from-remote-server -vv inst3606bin.000005 >row.sql

4、获取mysqlbinlog的帮助信息(仅列出常用选项)

-?, --help
   显示帮助消息并退出。

-d, --database=name
   只列出该数据库的条目(只适用本地日志)。

-f, --force-read
  
使用该选项，如果mysqlbinlog读它不能识别的二进制日志事件，它会打印警告，忽略该事件并继续。没有该选项，如果mysqlbinlog读到此类事件则停止。

-h, --host=name
   获取给定主机上的MySQL服务器的二进制日志。

-l, --local-load=name
   为指定目录中的LOAD DATA INFILE预处理本地临时文件。
-o,
--offset=#
   跳过前N个条目。

-p, --password[=name]
   当连接服务器时使用的密码。如果使用短选项形式(-p)，选项和密码之间不能有空格。
  
如果在命令行中–password或-p选项后面没有密码值，则提示输入一个密码。

-P, --port=#
   用于连接远程服务器的TCP/IP端口号。

--protocol=name
   使用的连接协议。

-R, --read-from-remote-server|--read-from-remote-master=name
  
从MySQL服务器读二进制日志。如果未给出该选项，任何连接参数选项将被忽略，即连接到本地。
  
这些选项是–host、–password、–port、–protocol、–socket和–user。

-r, --result-file=name
   将输出指向给定的文件。

-s, --short-form
   只显示日志中包含的语句，不显示其它信息，该方式可以缩小生成sql文件的尺寸。

-S, --socket=name
   用于连接的套接字文件。

--start-datetime=name
  
从二进制日志中读取等于或晚于datetime参量的事件，datetime值相对于运行mysqlbinlog的机器上的本地时区。
  
该值格式应符合DATETIME或TIMESTAMP数据类型。例如：2004-12-25 11:25:56 ，建议使用引号标识。

--stop-datetime=name
  
从二进制日志中读取小于或等于datetime的所有日志事件。关于datetime值的描述参见--start-datetime选项。

-j, --start-position=#
  
从二进制日志中第1个位置等于N参量时的事件开始读。
--stop-position=#
  
从二进制日志中第1个位置等于和大于N参量时的事件起停止读。

--server-id=#  
   仅仅提取指定server_id的binlog日志

--set-charset=name
   添加SET NAMES character_set到输出     
-t,
--to-last-log
   在MySQL服务器中请求的二进制日志的结尾处不停止，而是继续打印直到最后一个二进制日志的结尾。
  
如果将输出发送给同一台MySQL服务器，会导致无限循环。该选项要求–read-from-remote-server。

-D, --disable-log-bin
  
禁用二进制日志。如果使用–to-last-logs选项将输出发送给同一台MySQL服务器，可以避免无限循环。
  
该选项在崩溃恢复时也很有用，可以避免复制已经记录的语句。注释：该选项要求有SUPER权限。

-u, --user=name
   连接远程服务器时使用的MySQL用户名。

-v, --verbose
   用于输出基于row模式的binlog日志，-vv为列数据类型添加注释

-V, --version
   显示版本信息并退出。

5、小结

a、可以通过show binlog events以及mysqlbinlog方式来提取binlog日志。

b、show binlog events 参数有限不适宜批量提取，mysqlbinlog可用于批量提取来建立恢复数据库的SQL。

c、mysqlbinlog可以基于时间点，position等方式实现不完全恢复或时点恢复。

d、mysqlbinlog可以从支持本地或远程方式提取binlog日志。

e、mysqlbinlog可以基于server_id，以及基于数据库级别提取日志，不支持表级别。

转载于:https://www.cnblogs.com/chgxtony/p/4919563.html