一篇文章搞懂bin-log

---

---

一、什么是bin-log？

bin-log是server层实现的二进制日志，他会记录我们的CRUD操作的执行逻辑进行记录。

1.1、bin-log特性

1、Binlog在MySQL的Server层实现（引擎共用）
2、Binlog为逻辑日志,记录的是一条语句的原始逻辑
3、Binlog不限大小,追加写入,不会覆盖以前的日志	

如果误删了数据库，可以试用binlog进行归档，要使用binlog首先得开启Mysql的binlog功能。

1.2、bin-log配置（my.cnf文件配置）

配置开启binlog
log-bin=/usr/local/mysql/data/binlog/mysql-bin
注意5.7以及更高版本需要配置本项：
server-id=123454
#（自定义,保证唯一性）;
#binlog格式，有3种statement,row,mixed
binlog-format=ROW
#表示每1次执行写入就与硬盘同步，会影响性能，为0时表示，事务提交时mysql不做刷盘操作，由系统决定
sync-binlog=1

1.3、binlog格式

statement:记录是这个操作语句的逻辑（比如update set x=x where 1=1 记录这个），效率高，没那么安全；
row：记录影响的记录之前的结果（比如name=ljc,他记录的不是这条语句，而是记录影响的结果）,优先考虑它，传输效率会低一点，但是安全性高；  
mixed：动态判断，以上2种根据情况来使用

1.4、binlog命令

mysql> show variables like '%log_bin%'; 查看bin-log是否开启
mysql> flush logs; 会多一个最新的bin-log日志
mysql> show master status; 查看最后一个bin-log日志的相关信息
mysql> reset master; 清空所有的bin-log日志

1.5、查看binlog内容

mysql> /usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001 查看binlog内容

binlog里的内容不具备可读性，所以需要我们自己去判断恢复的逻辑点位，怎么观察呢？
看重点信息，比如begin,commit这种关键词信息，只要在binlog当中看到了，你就可以理解为begin-commit之间的信息是一个完整的事务逻辑,然后再根据位置position判断恢复即可。

binlog内容如下：

1.6、数据归档操作

从bin-log恢复数据
恢复全部数据
/usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001 |mysql -uroot -p test(数据库名)

恢复指定位置数据(设置position的起始位置)
/usr/local/mysql/bin/mysqlbinlog --no-defaults --start-position="408" --stop-position="731"  /usr/local/mysql/data/binlog/mysql-bin.000001 |mysql -uroot -p test(数据库)

恢复指定时间段数据
/usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001 --stop-date= "2018-03-02 12:00:00"  --start-date= "2019-03-02 11:55:00"|mysql -uroot -p test(数据库)

---

二、bin-log常见操作

2.1、mysql binlog 开启与关闭

查看mysql binlog配置

show global variables like '%log_bin%';

binlog 当前是关闭的。

开启binlog

打开my.cnf或my.ini加入以下语句，重启mysql
log_bin=ON
log_bin_basename=/usr/local/var/mysql/mysql-bin
log_bin_index=/usr/local/var/mysql/mysql-bin.index

ON表示打开binlog日志，关闭改为OFF。

log_bin_basename
表示binlog日志的基本文件名，后面会追加标识来区分每一个文件。

log_bin_index
指定binlog文件的索引文件，这个文件管理了所有binlog文件的目录。

---

如果是mysql5.7以下，这样设置就可以了，如果是5.7以上，需要按以下设置

log_bin=mysql-bin
server_id=123456
log_bin 表示自定义binlog文件名称。
server_id 表示随机指定一个与其他集群机器不重名的字符串，配置mysql replication需要定义，不能和canal的slaveId重复。

重启后再次查看mysql binlog配置

[root@localhost ~]# systemctl stop mysqld.service #关闭服务
[root@localhost ~]# systemctl start mysqld.service #开启服务

show global variables like ‘%log_bin%’;

可以看到binlog已开启。

2.2、查看binlog日志文件列表

show master logs;

2.3、查看当前正在写入的binlog日志

show master status;

2.4、刷新binlog日志文件

flush logs;

2.5、清空日志文件

reset master;

---

2.6、mysqlbinlog工具（查看binlog文件）

上面说到的查看日志，可以知道是他是一个二进制不具备可读性的日志文件，通过mysqlbinlog工具查看，可以具有一定的可读性。

mysqlbinlog -v mysql-bin.000001 > mysql-bin-1.log

可以看到有一定的格式

使用mysqlbinlog时需要注意一下几点

1.不要查看当前正在写入的binlog文件，可以先把文件复制到其他目录，再执行查看。
2.不要加force参数强制访问。
3.如果binlog格式是row模式的，请加-vv参数。

---

三、binlog归档使用案例

定义一个存储过程，写入数据

drop procedure if exists tproc;
delimiter $$
create procedure tproc(i int)
begin
    declare s int default 1;
    declare c char(50) default repeat('a',50);
    while s<=i do
        start transaction;
        insert into test values(null,c);
        commit;
        set s=s+1;
    end while;
end$$
delimiter ;

删除数据

truncate test;

binlog归档

/usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001 |mysql -uroot -p test(数据库名)

归档完毕，数据恢复

---

总结

在实际开发中，一定要开启binlog非常重要！binlog文件对于一个数据库的处理和恢复有很重要的作用