浅谈 MySQL Binlog 日志

2022-11-27
北京
本文字数：3180 字
阅读完需：约 10 分钟

1 什么是 binlog？

binlog 是二进制日志文件，用于记录 mysql 的数据更新或者潜在更新状况，在 mysql 主从复制中就是依靠的 binlog。可以通过语句“show binlog events in 'binlogfile'”来查看 binlog 的具体事件类型。binlog 记录的所有操作实际上都有对应的事件类型的，MySQL binlog 的三种工作模式：

Row level（行记录）简介：
日志中会记录每一行数据被修改的情况，然后在 slave 端对相同的数据进行修改。
优点：
所有更改都可以复制。这是最安全的复制形式。
对于以下类型的语句，源上需要更少的行锁，从而实现更高的并发性：
INSERT ... SELECT
INSERT与 AUTO_INCREMENT
UPDATE``DELETE带有WHERE不使用键或不更改大多数检查行的子句的语句。
需要的行锁更少。
缺点：
RBR 可以生成更多必须记录的数据。写入数据可能会导致并发问题。用
无法在副本上看到从源接收并执行了哪些语句。
对于使用MyISAM 存储引擎的表，INSERT当将它们作为基于行的事件应用于二进制日志时，语句的副本需要比将它们作为语句应用时更强的锁。这意味着MyISAM在使用基于行的复制时不支持对表的并发插入。
Statement level（语句记录，默认）简介：
每一条被修改数据的 sql 都会记录到 master 的 bin-log 中，slave 在复制的时候 sql 进程会解析成和原来 master 端执行过的相同的 sql 再次执行。在主从同步中一般是不建议用 statement 模式的，因为会有些语句不支持，比如语句中包含 UUID 函数，以及 LOAD DATA IN FILE 语句等
优点：
写入日志文件的数据更少。当更新或删除影响许多行时，这会导致日志文件所需的存储空间少得多。这也意味着可以更快地完成备份和恢复。
日志文件包含进行了任何更改的所有语句，因此它们可用于审计数据库。
缺点：
INSERT ... SELECT与基于行的复制相比，需要更多的行级锁。
UPDATE需要表扫描的语句,必须比基于行的复制锁定更多的行。
对于InnoDB， INSERT使用 AUTO_INCREMENT阻塞其他非冲突INSERT语句的语句。
对于复杂语句，必须在更新或插入行之前在副本上评估和执行语句。使用基于行的复制，副本只需要修改受影响的行，而不是执行完整的语句。
如果对副本进行评估时出现错误，尤其是在执行复杂语句时，则基于语句的复制可能会随着时间的推移慢慢增加受影响行的误差幅度。
存储函数以与NOW()调用语句相同的值执行。但是，这不适用于存储过程。
必须在副本上应用确定性可加载函数。
表定义在源和副本上必须（几乎）相同。
Mixed（混合模式）简介：
结合了 Row level 和 Statement level 的优点，同时 binlog 结构也更复杂。

binlog 的格式：

2 binlog 有什么作用？

MySQL 主从复制：MySQL Replication 在 Master 端开启 binlog，Master 把它的二进制日志传递给 slaves 来达到 master-slave 数据一致的目的
数据恢复：通过使用 mysqlbinlog 工具来使恢复数据

3 binlog 的常用操作

3.1 启动和关闭 binlog

启动 binlog，修改配置文件并重启：

[root@iZ2ze4m2ri7irkf6h6n8zoZ mysql]# vim /etc/my.cnf############追加以下内容#############[mysqld]#开启binlog，提前建好/home/mysql/binlog文件名称log-bin=/home/mysql/binlog############追加内容完成#############[root@iZ2ze4m2ri7irkf6h6n8zoZ mysql]# service mysql restart

复制代码

关闭 binlog：

删除配置文件中相应的内容

查看 binlog 是否开启：

mysql> show variables like 'log_bin';+---------------+-------+| Variable_name | Value |+---------------+-------+| log_bin       | ON    |+---------------+-------+1 row in set (0.00 sec)

复制代码

3.2 查看 binlog

先在 mysql 中看 binlog 文件的名称

mysql> show binary logs;+---------------+-----------+| Log_name      | File_size |+---------------+-----------+| binlog.000001 |       412 |+---------------+-----------+1 row in set (0.00 sec)

复制代码

根据 binlog 名称查看 binlog 内容

[root@iZ2ze4m2ri7irkf6h6n8zoZ mysql]# mysqlbinlog binlog.000001/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!40019 SET @@session.max_insert_delayed_threads=0*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /*!*/;# at 4#211122 15:36:30 server id 1  end_log_pos 107   Start: binlog v 4, server v 5.5.62-log created 211122 15:36:30 at startup# Warning: this binlog is either in use or was not closed properly.ROLLBACK/*!*/;BINLOG 'fkibYQ8BAAAAZwAAAGsAAAABAAQANS41LjYyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAB+SJthEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA=='/*!*/;# at 107#211122 15:38:14 server id 1  end_log_pos 220   Query  thread_id=1  exec_time=0  error_code=0use `test`/*!*/;SET TIMESTAMP=1637566694/*!*/;SET @@session.pseudo_thread_id=1/*!*/;SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;SET @@session.sql_mode=0/*!*/;SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;/*!\C utf8 *//*!*/;SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;SET @@session.lc_time_names=0/*!*/;SET @@session.collation_database=DEFAULT/*!*/;create table student(id int(10),name varchar(20) )/*!*/;# at 220#211122 15:38:40 server id 1  end_log_pos 288   Query  thread_id=1  exec_time=0  error_code=0SET TIMESTAMP=1637566720/*!*/;BEGIN/*!*/;# at 288#211122 15:38:40 server id 1  end_log_pos 385   Query  thread_id=1  exec_time=0  error_code=0SET TIMESTAMP=1637566720/*!*/;insert into student values(1,"zs")/*!*/;# at 385#211122 15:38:40 server id 1  end_log_pos 412   Xid = 12COMMIT/*!*/;DELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

复制代码

上面输出包括信息：

position: 位于文件中的位置，即第一行的,说明该事件记录从文件第 21019 个字节开始
timestamp: 事件发生的时间戳。
server id: 服务器标识。
end_log_pos 表示下一个事件开始的位置（即当前事件的结束位置+1）
thread_id: 执行该事件的线程 id 。
exec_time: 事件执行的花费时间
error_code: 错误码，0 意味着没有发生错误
type:事件类型 Query

3.3 binlog 常用命令

mysql> show binlog events;   #只查看第一个binlog文件的内容mysql> show binlog events in 'mysql-bin.000002';#查看指定binlog文件的内容mysql> show binary logs;  #获取binlog文件列表mysql> show master status； #查看当前正在写入的binlog文件

复制代码

3.4 切换 binlog 的模式

mysql> show global variables like '%binlog_format%';+---------------+-----------+| Variable_name | Value     |+---------------+-----------+| binlog_format | STATEMENT |+---------------+-----------+1 row in set (0.00 sec) mysql> SET global binlog_format = 'ROW';Query OK, 0 rows affected (0.02 sec) mysql> show global variables like '%binlog_format%';+---------------+-------+| Variable_name | Value |+---------------+-------+| binlog_format | ROW   |+---------------+-------+1 row in set (0.00 sec)