MySQL和Mariadb二进制日志binlog实例讲解

Mariadb/mysql提供了4中不同的日志，分别是错误日志(error.log)、普通日志(general log)、慢日志(slow log)以及二进制日志(binlog)。错误日志记录了系统启动、运行以及停止过程中遇到的一些问题;普通日志记录了Mariadb执行的所有语句以及语句开始执行的时间等信息，用户可以选择性的打开它;慢日志记录了Mariadb所有慢查询的相关信息;而二进制日志则以事件的形式记录了mariadb的库表结构以及表数据的所有变更信息。

一：binlog的作用

l 复制：在Mariadb/mysql的主从结构中，主库的binlog记录了主库的所有更改操作，从库通过读取主库的binlog，在本地重放获取的binlog，这样从库就拥有和主库相同的数据，达到复制目的。

l 备份恢复：binlog记录了数据库的所有更改信息，所以当Mariadb/mysql发生崩溃的时候，能够以最近备份点作为起点，然后执行在备份点之后产生的binlog中所有事件，实现数据库最大可能的恢复。

index文件按照书序记录了Mariadb/Mysql使用的所有binlog文件，管理binlog文件。

二：binlog·的3种格式

STATEMENT：顾名思义，STATEMENT格式的binlog记录的是数据库上执行的原生SQL语句

l ROW：这种格式的binlog记录的是数据表的行是怎样被修改的。

l MIXED：如果设置了这种格式，Mariadb/Mysql会在一些特定的情况下自动从STATEMENT格式切换到ROW格式。例如，包含UUID等不确定性函数的语句，引用了系统变量的语句，等等。

三：开启binlog

[mysqld]

log-bin = base-name

BINLOG_FORMAT = ROW

四：binlog的相关参数

max_binlog_size参数 此参数来限定单个binlog文件的大小。binlog文件可能会大于max_binlog_size参数设定的阀值，因为一个事务所产生的所有事件必须记录在同一个binlog文件中。

binlog过滤器 拥有root权限的用户可以通过执行SET sql_log_bin = 0命令来禁用当前会话，该回话执行的所有操作都不会记录在binlog中。

sync_binlog参数;默认情况下syc_binlog=0,binlog文件在每次写入内容后是不会立刻持久化到磁盘上的具体的持久化操作会交给操作系统来做。真阳当操作系统崩溃的时候，对binlog文件进行的修改可能会丢失，从而造成binlog文件的数据丢失和不一致性。为了避免发生这种情况，可以将sync_binlog配置成1，这样在将事务写入到binlog文件之后会立即执行fsync操作将binlog文件的修改同步到磁盘上。但这样会降低Mariadb的性能，因为sync是个昂贵的系统调用，也可以将sync_binlog配置成一个整数N，指定在写入N个是事务之后才执行一次fsync操作。

bin-log事件

通过执行show binlog events in "binlog-file"命令来查看指定binlog文件中的事件。如果binlog格式为STATEMENT ,还能看出具体执行的SQL语句。

五：清理binlog

手动清理binlog的方式有两种，一种是使用Mariadb提供的purge命令，一种是通过系统自带的rm命令

purge {binary | master } logs to "binlog-file-name"

purge {binary | master } logs before "datetime-expi"

其中第一种形式的purge命令的作用是将binlog—file-name之前的所有binlog文件清理掉，而第二种形式的purge命令是将最后修改时间早于datetime-expi的binlog文件清理掉

使用rm命令手动清理binlog的流程如下。

(1)确保你的mysql处于停止状态

(2)使用rm命令按顺序删除binlog文件

(3)修改index文件，把已经删除的binlog文件从index文件中删除

自动清理binlog

在启动mysql server的时候，携带expire_log_days=N 参数(0≤N≤99)，或则在配置文件中添加expire_logs_days=N选项，这样mysql只会保存N天的binlog，过期的binlog会被自动清理掉。

六：MySQL和Mariadbmysqlbinlog工具

mysqlbilog工具可以将binlog中事件包含的信息以文本的形式打印出来。

mysqlbinlog [options] binlog-file ...

通过mysqlbinlog进行恢复主要有一下两步，

1：使你的数据库恢复到最近备份点的状态

2：执行mysqlbinlog your-bin-log | mysql -u root -p ,将binlog中记录的修改，反映到数据库中。(如果有多个binlog文件，本步需要执行多次)