MySQL binlog详解以及使用binlog恢复数据

binlog详解

二进制日志（binary log）记录了对MySQL数据库执行更改的所有操作，但是不包括SELECT和SHOW这类操作。

binlog配置

log-bin

默认情况下，binlog是不开启的，通过配置参数log-bin=XXX来开启，XXX为产生的二进制日志文件名，后缀默认是二进制日志的序列号，所在路径为数据库所在目录。
查看数据库所在目录：show variables like ‘datadir’;

max_binlog_sizse

指定了单个二进制日志文件的最大值，如果超过改值，则参数新的二进制日志文件，后缀名+1。MySQL5.0及其之后版本改值默认为1G。

binlog_cache_size

当使用支持事务的存储引擎如InnoDB时，所有未提交事务的二进制日志会被记录到一个缓冲区中（除非该缓冲区已经写满，则会在事务未提交时把日志写入一个临时文件中），等该事务提交时直接将缓冲区中的二进制日志写入二进制日志文件中，而该缓冲区的大小由binlog_cache_size决定，默认为32K。Binlog_cache_use记录了使用缓冲写二进制日志的次数，binlog_cache_disk_use记录了使用临时文件写二进制日志的次数。

max_binlog_cache_size

binlog缓冲区的最大大小

sync_binlog

从二进制日志缓冲区写入操作系统，默认还会经过操作系统的缓冲区，这样，当数据库宕机时，可能会丢失一部分为写入磁盘的二进制日志，可以将参数sync_binlog设置为1，表示采用同步写磁盘的方式来写二进制日志，即不使用操作系统的缓冲，但是这样降低了IO性能，sync_binlog的值默认为0,。

binlog-do-db和binlog-ignore-db

表示写入或忽略写入那些库的日志，默认为空，表示写入所有库的二进制日志。

log-slave-update

如果当前数据库是slave，默认不会将从master取得并执行的二进制日志写入自己的二进制日志文件中，如果将写入，这需要设置该参数。

expire_logs_days

expire_logs_days = n 表示日志保存事件为n天

binlog_format

binlog_format十分重要，它决定了记录二进制日志的格式。

binlog_format三种模式

Statement(默认)

记录的是逻辑SQL语句。

优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。
缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)

ROW

记录表的更改情况。不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。

优点是不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。
缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。

MIXED

以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

binlog日志的基本命令

简单配置

log-bin=mysql-bin
#单节点
server-id=1
binlog_format=row
expire_logs_days=2

重启数据库
此时会在数据文件下生产两个文件：
mysql-bin.00000*是二进制日志文件，mysql-bin.index是索引文件，记录二进制日志文件

查看binlog开启状态及其他参数

mysql> show variables like '%log_bin%';

查看所有binlog日志文件列表

查看最后一个binlog日志的编号名称及其最后一个操作事件pos结束点的值

刷新日志

flush logs

执行该命令会产生一个新编号的binlog文件。每当mysqld服务重启时，会自动执行刷新binlog日志命令。mysqldump备份数据时加-F选项也会刷新binlog日志。

清空所有binlog日志命令

reset master

查看binlog文件内容

方式一：

方式二：

指定从pos点n开始查询

利用binlog日志恢复mysql数据

测试表如下testindb.t：

现在将此数据库备份到本地(模拟每周的备份情况)，备份命令如下：

重启MySQL，使用新的二进制日志文件（会好找pos点）：

插入数据：

修改数据：

删除表和数据库：

drop table t;
drop database testindb;

至此，模拟了这样一个情况：数据库在某个时间有全库备份，然后又对数据库进行了修改操作，最后把表删除，把数据库删除。

新建数据库testindb，然后将备份的数据库导入：


现在使用binlog日志恢复缺少的数据，上面显示过当前二进制日志文件为mysql-bin.000002，先备份一下该文件：

然后刷新日志，产生新的日志文件，这样接下来的操作都会写入3号日志文件中，不会影响一会儿从2号文件中的恢复操作：

查看2号日志文件：
有两个插入数据的记录

执行mysqlbinlog命令恢复数据库：
其中整个命令的含义是通过mysqlbinlog读取日志内容并通过管道传给mysql命令

mysqlbinlog  --start-position=154 --stop-position=694 -d testindb mysql-bin.000002|mysql -uroot -p密码 testindb;

已经恢复至插入数据时的状态：

继续恢复修改的数据：

mysqlbinlog  --start-position=759 --stop-position=976 -d testindb mysql-bin.000002|mysql -uroot -p密码 testindb;

这里只是做演示，以上两个恢复步骤可以通过制定开始点pos和结束点pos一次恢复。
除了根据开始点和结束点以外，还可以根据开始时间和结束时间。前面说过查看binlog文件内容的方式一是使用mysqlbinlog命令，可以查看事务的开始时间和结束时间，通过该事件，也可以进行数据恢复：

mysqlbinlog  --start-datetime="2018-04-27 20:57:55" --stop-datetime="2018-04-27 20:58:18" testindb mysql-bin.000002|mysql -uroot -p密码 testindb;