Mysql binlog详解(详细介绍)

binlog是什么？

binlog是二进制文件，是一组日志文件，包含有关对服务器实例进行数据修改的信息(比如表的创建操作或者表数据更改)，语句以描述修改的事件的形式存储，二进制日志还包含有关每条语句使用更新数据的时间的信息。
简单的说，就是记录mysql表中数据的增删改操作，不记录查询(select)和show语句。如果要记录所有语句（比如排查问题时），需要开启常规查询日志(general_log)，默认是关闭的。
启用binlog会使服务器性能下降，但是相对于它能还原，复制操作的用处，这点性能影响不足挂齿。
binlog中还包含一些其他元数据，例如：

• 有关正确再现语句所需的服务器状态的信息
• 错误代码
• 维护binlog本身所需的元数据（例如，rotate events）

binlog有什么用？

1.复制(主副同步)，binlog日志在主服务器上发送语句的记录到从属服务器上，这些从属服务器执行这些语句，以保证数据库能和主服务器数据库进行相同的更改，

2.数据恢复，还原备份文件后，将重新执行备份后记录的二进制日志中的事件，这些事件使数据库从备份点更新。

binlog如何记录数据的更改的？

binlog按事件的线性序列记录，修改数据库的sql将生成一个或多个事件并将其追加到binlog日志中，还有一些辅助事件类型描述了binlog的结构。
可以通过两种方式来记录查询(增删改等语句)：

• statement format格式(语句格式)：查询以文本的形式写入binlog
• row format(行格式)：更改的行以二进制的形式写入binlog，每行可包括Before Image(前镜像) (BI) 且/或 After Image(后镜像) (AI)，BI标识要修改的行，AI描述更改后的行。
  日志的事件有三种类型：
  1.Write_rows_log_event：向表中添加新行，只有AI
  2.Update_rows_log_event：修改表中的现有行，BI和AI都有
  3.Delete_rows_log_event：从表中删除现有行，只有BI

binlog记录格式有哪些，如何设置？

1.STATEMENT：基于语句的日志记录(包含产生数据更改的SQL语句插入，更新，删除)又称为SBL优点：不需要记录每一行的变化，减少日志量，提高了性能。比如对于按照条件删除，修改表数据时，如果是ROW格式就会将每行的变化都记录下来，而STATEMENT不会产生这么多日志。缺点：不保证正确记录，记录每一行的时候还需要记录一些上下文信息，对一些函数可能不支持，造成主副同步时，数据不一致。2.ROW：基于行的日志记录(描述对各行的更改)又称为RBL优点：保证正确记录，只记录每一行改成什么，不需要记录上下文信息，不会因为其它原因造成数据的正确复制问题。缺点：每一行的变化都需要记录，会产生大量的日志。3.MIXED：混合日志记录(默认使用基于语句的日志记录，但会根据需要自动切换到基于行的日志记录)
保证正确记录，会根据具体sql来区分使用哪种格式。

建议：如果对于数据正确性要求高，无法确定格式会造成数据的异常问题时，建议使用ROW。

查询binlog记录格式语法，默认记录格式为STATEMENT

show variables like '%binlog_format%'

设置全局binlog记录格式

SET GLOBAL binlog_format = 'STATEMENT'; 
SET GLOBAL binlog_format = 'ROW'; 
SET GLOBAL binlog_format = 'MIXED';

设置当前会话的binlog记录格式

SET SESSION binlog_format = 'STATEMENT';
SET SESSION binlog_format = 'ROW';
SET SESSION binlog_format = 'MIXED';

binlog开启方法

先查下数据库有没有开启binlog

show variables like 'log_%';

在mysql配置文件my.ini中[mysqld]下增加以下配置，然后重启mysql即可

binlog_format = MIXED
log-bin = E:mysql路径\\mysql-5.6.35-winx64\\logs\\mysql-bin.log
expire-logs-days = 7
max_binlog_size = 200m
binlog_cache_size = 10m
max_binlog_cache_size = 500m

设置binlog记录格式
binlog_format = MIXED
设置日志路径，这里最好和mysql的数据目录，也就是data不在一个磁盘分区，这样就算出问题，也不好造成数据无法恢复，性能也会提高
log-bin = E:mysql路径\mysql-5.6.35-winx64\logs\mysql-bin.log
设置binlog清理时间
expire-logs-days = 7
设置每个文件的大小
max_binlog_size = 200m
设置缓存大小
binlog_cache_size = 10m
设置最大缓存大小
max_binlog_cache_size = 500m

查看binlog日志

windows 下进入mysql bin文件夹，需要使用mysqlbinlog程序来读取显示内容，不能直接打开查看。

要想看当前binlog的状态，使用命令

show master status;

然后到binlog的路径中，找到该文件，通过mysqlbinlog来打开查看日志。

mysqlbinlog --no-defaults E:\......\mysql-5.6.35-winx64\logs\mysql-bin.000001

为什么要使用 --no-defaults 而不是直接
mysqlbinlog E:…\mysql-5.6.35-winx64\logs\mysql-bin.000001

可以看到不用 --no-defaults 时会报错 unknown variable ‘default-character-set=utf8’

解决方法有两种：
一种就是使用携带–no-defaults的命令去打开binlog日志，无非就是看中文的时候是乱码
另一种就是修改配置文件，网上说将[client]下的 default-character-set=utf8 配置修改为 character-set-server = utf8 ，经测试还是不行，需要使用loose-default-character-set=utf8配置，或者都注释掉。

虽然，这样能解决问题，但是需要重启mysql，而且会影响数据库编码，建议不要这样做。

测试binlog记录

新建一个测试表

CREATE TABLE `demo_innodb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sex` int(1) DEFAULT '0' COMMENT '性别',
  `realname` varchar(10) DEFAULT '' COMMENT '姓名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='InnoDB表';

1.新增一条数据

insert into demo_innodb values(2,1,'李四')

查看下binlog，因为中间重启了很多次mysql，所以当前binlog为 mysql-bin.000007。

可以看到新增的数据在日志中，下面试试查询语句 select，

2.查询表数据

select * from demo_innodb
select * from demo_innodb where id=2

可以看到查询语句没有记录在binlog中。

3.删除表数据

delete from demo_innodb where id=1
truncate table demo_innodb

删除语句也记录下来了。

删除或清空binlog日志

正常情况下我们是不需要删除的，因为在配置文件中配置了，expire-logs-days = 7，7天清理一次。
如果想要手动清除，可以使用下面命令删除

#删除mysql-bin.000003之前的所有binlog日志,不包括mysql-bin.000003
PURGE BINARY LOGS TO 'mysql-bin.000003';
#删除2019-01-30 10:53:26 这个日期之前的所有binlog日志
PURGE BINARY LOGS BEFORE '2019-01-30 10:53:26';

#删除全部binlog日志，谨慎使用此语句
RESET MASTER

如果是主从模式，想要安全的删除binlog，需要按照步骤删除：
1.在每个从属服务器上，用SHOW SLAVE STATUS语句检查它正在读取哪个日志文件
2.使用SHOW BINARY LOGS语句来获取主服务器上二进制日志文件的列表
3.确定所有从站中最早的日志文件，这是目标文件。如果所有从属服务器都是最新的，则这是列表中的最后一个日志文件
4.对要删除的所有日志文件进行备份
5.清除所有日志文件，但不包括目标文件