一、前言

1.1.binlog

1.1.1 binlog简介

Mysql的binlog日志作用是用来记录mysql内部增删改查等对mysql数据库有更新的内容的记录（对数据库的改动），对数据库的查询select或show等不会被binlog日志记录;主要用于数据库的主从复制以及增量恢复。

1.1.2 Mysql Binlog格式介绍

Mysql binlog日志有三种格式，分别为Statement,MiXED,以及ROW！

（1）Row level

　　日志中会记录每一行数据被修改的情况，然后在slave端对相同的数据进行修改。

　　优点：能清楚的记录每一行数据修改的细节

　　缺点：数据量太大

（2）Statement level（默认）

每一条被修改数据的sql都会记录到master的bin-log中，slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql再次执行

优点：解决了 Row level下的缺点，不需要记录每一行的数据变化，减少bin-log日志量，节约磁盘IO，提高新能

　　缺点：容易出现主从复制不一致

（3）Mixed（混合模式）

　　结合了Row level和Statement level的优点

1.1.3 Binlog日志格式选择

Mysql默认是使用Statement日志格式，推荐使用MIXED.

由于一些特殊使用，可以考虑使用ROWED，如自己通过binlog日志来同步数据的修改，这样会节省很多相关操作。对于binlog数据处理会变得非常轻松,相对mixed，解析也会很轻松(当然前提是增加的日志量所带来的IO开销在容忍的范围内即可)。

[if !supportLists]l [endif]互联网公司使用MySQL的功能较少（不用存储过程、触发器、函数），选择默认的Statement level

[if !supportLists]l [endif]用到MySQL的特殊功能（存储过程、触发器、函数）则选择Mixed模式

[if !supportLists]l [endif]用到MySQL的特殊功能（存储过程、触发器、函数），又希望数据最大化一直则选择Row模式

1.2.设置binlog模式

1.2.1 查看MySQLbinlog模式

#以mysql5.6为例，系统默认为STATEMENT模式

mysql> show global variables

like "binlog_format%";

+---------------+-----------+

| Variable_name | Value |

+---------------+-----------+

| binlog_format |STATEMENT|

+---------------+-----------+

1 row in set (0.00 sec)

1.2.2 MySQL中设置binlog模式

set global binlog_format='ROW';

1.2.3 配置文件中设置binlog模式

mysql的binlog日志必须打开log-bin功能才能生存binlog日志，一般在/etc/my.cnf中的

[mysqld]

binlog_format='ROW' #放在mysqld模块下面，也可以不设置则默认为STATEMENT模式

#binlog日志路径

log-bin=/disk1/logs/mysql/mysql5.6/mysql-bin

设置之后重启mysql则会生效。

#用下面的命令可以查看是否启用了日志

mysql>show variables like 'log_bin';

二、mysqlbinlog

2.1. Mysqlbinlog简介

Mysqlbinlog功能是将Mysql的binlog日志转换成Mysql语句，默认情况下binlog日志是二进制文件，无法直接查看。查看需要使用mysqlbinlog命令。

2.2 mysqlbinlog使用说明

2.2.1. mysqlbinlog相关语法

#1帮助命令

mysqlbinlog --help

#2参数说明

--base64-output=name：可以控制输出语句何时是输出base64编码的BINLOG语句，默认是AUTO mysql5.6有4个选项'NEVER','AUTO','UNSPEC','DECODE-ROWS'

never：它将在输出中显示base64编码的BINLOG语句，NEVER参数会导致二进制语句不会显示

UNSPEC：unspecified未来指明

DECODE-ROWS解码处理二进制日志，特别是当指定-verbose选项时

AUTO：这是默认选项。当没有指定任何base64解码选项时，它将使用auto

mysqlbinlog将仅为某些事件类型打印BINLOG项

—database=db_name，-d db_name

只列出该数据库的条目(只用本地日志)。

–force-read，-f

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

–hexdump，-H

在注释中显示日志的十六进制转储。该输出可以帮助复制过程中的调试。在MySQL 5.1.2中添加了该选项。

–host=host_name，-h host_name

获取给定主机上的MySQL服务器的二进制日志。

–local-load=path，-l pat

为指定目录中的LOAD DATA INFILE预处理本地临时文件。

–offset=N，-o N

跳过前N个条目。

–password[=password]，-p[password]

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

–port=port_num，-P port_num

用于连接远程服务器的TCP/IP端口号。

–position=N，-j N

不赞成使用，应使用–start-position。

–protocol={TCP |SOCKET | PIPE | -position

使用的连接协议。

–read-from-remote-server，-R

从MySQL服务器读二进制日志。如果未给出该选项，任何连接参数选项将被忽略。这些选项是–host、–password、–port、–protocol、–socket和–user。

–result-file=name,

-r name将输出指向给定的文件。

–short-form，-s

只显示日志中包含的语句，不显示其它信息。

–socket=path，-S path

用于连接的套接字文件。

–start-datetime=datetime

从二进制日志中第1个日期时间等于或晚于datetime参量的事件开始读取。datetime值相对于运行mysqlbinlog的机器上的本地时区。该值格式应符合DATETIME或TIMESTAMP数据类型。例如：shell> mysqlbinlog –start-datetime=”2004-12-25 11:25:56″ binlog.000003该选项可以帮助点对点恢复。

–stop-datetime=datetime

从二进制日志中第1个日期时间等于或晚于datetime参量的事件起停止读。关于datetime值的描述参见–start-datetime选项。该选项可以帮助及时恢复。

–start-position=N

从二进制日志中第1个位置等于N参量时的事件开始读。

–stop-position=N

从二进制日志中第1个位置等于和大于N参量时的事件起停止读。

–to-last-logs，-t

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

–disable-logs-bin，-D

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

–user=user_name，-u user_name

连接远程服务器时使用的MySQL用户名。

–version，-V

显示版本信息并退出。

2.3 mysqlbinlog操作实例

我的mysql是5.6版本，binlog日志名字为mysql-bin.00001，下面是常用的操作

#查看日志内容

mysqlbinlog mysql-bin.000001 |more

#保存输出到文件（>）

也可以使用简单的Linux重定向命令，将输出存储到一个文件中，如下所示。

mysqlbinlogmysqld-bin.000001 > output.log

#获取特定数据库条目（-d）

默认情况下，mysqlbinlog会显示所有的内容，太过于杂乱。使用 -d 选项，可以指定一个数据库名称，将只显示在该数据库上所发生的事件。

mysqlbinlog-d crm mysql-bin.000001>crm.sql

#禁止恢复过程产生日志（-D）

在使用二进制日志文件进行数据库恢复时，该过程中也会产生日志文件，就会进入一个循环状态，继续恢复该过程中的数据。因此，当使用mysqlbinlog命令时，要禁用二进制日志，请使用下面所示的-D选项：

mysqlbinlog-D mysqld-bin.000001

#mysqlbinlog输出调试信息（--debug-check）

mysqlbinlog--debug-check mysqld-bin.000001

#跳过前N个条目（-o）

除了读取整个mysql二进制日志文件外，也可以通过指定偏移量来读取它的特定部分。可以使用 -o 选项。o代表偏移。

下面将跳过指定的mysql bin日志中的前10个条目。

mysqlbinlog -o 10 mysqld-bin.000001

# 在输出中只显示语句（-s）

默认情况下，正如在前面的示例输出中看到的一样，除了SQL语句之外，在mysqlbinlog输出中还会有一些附加信息。如果只想查看常规的SQL语句，而不需要其他内容，那么可以使用 -s 选项，如下所示。也可以使用 --short-form 选项，效果相同。

mysqlbinlog -s mysqld-bin.000001

#从远程服务器获取二进制日志（-R）

在本地机器上，还可以读取位于远程服务器上的mysql二进制日志文件。为此，需要指定远程服务器的ip地址、用户名和密码，如下所示。

此处使用-R选项。-R选项与-read-from-remote-server相同。

mysqlbinlog -R -h 192.168.3.75 -p mysqld-bin.000001

在上面命令中：

R 选项指示mysqlbinlog命令从远程服务器读取日志文件

h 指定远程服务器的ip地址

-p 将提示输入密码。默认情况下，它将使用“root”作为用户名。也可以使用 -u 选项指定用名。

mysqld-bin.000001 这是在这里读到的远程服务器的二进制日志文件的名称。

***从一个特定位置提取条目（-j,--start-position 包括起点）

通常在mysql二进制日志文件中，你将看到如下所示的位置号。下面是mysqlbinlog的部分输出，你可以看到“15028”是一个位置编号。

下面的命令将从位置编号为15028（包括15028）的二进制日志条目处开始读取。

mysqlbinlog -j 15028 mysqld-bin.000001 > from-15028.out

当在命令行中指定多个二进制日志文件时，开始位置选项将仅应用于给定列表中的第一个二进制日志文件。还可以使用 -H 选项来获得给定的二进制日志文件的十六进制转储，如下所示。

mysqlbinlog -H mysqld-bin.000001 > binlog-hex-dump.out

***将条目截止到一个特定的位置（--stop-position不包括终点）

就像前面的例子一样，你也可以从mysql二进制日志中截止到一个特定位置的条目，如下所示。

mysqlbinlog --stop-position=15600 mysqld-bin.000001 > upto-15600.out

**上面的15600号是不会被包括进去的

注：

一般--start-position和--stop-position组合用的也比较多，一般是恢复日志到一定位置范围内的，比如取（15028-16000）之间的

mysqlbinlog -j 15028 --stop-position=15600 mysqld-bin.000001>out.sql

***查看特定开始时间的条目（--start-datetime 包括起始时间）

下面将只提取从指定时间开始的条目。在此之前的任何条目都将被忽略。

mysqlbinlog --start-datetime="2018-04-18 10:00:00" mysqld-bin.000001

***查看特定结束时间的条目（--stop-datetime 不包括结束时间）