- 微信
- 微博
  
  分享文章到微博
- 复制链接
  
  复制链接到剪贴板

浅谈MySQL Binlog

海风极客发表于 2022/11/27 21:33:32 2022/11/27

【摘要】 1 什么是binlog？binlog是二进制日志文件，用于记录mysql的数据更新或者潜在更新状况，在mysql主从复制中就是依靠的binlog。可以通过语句“show binlog events in ‘binlogfile’”来查看binlog的具体事件类型。binlog记录的所有操作实际上都有对应的事件类型的，MySQL binlog的三种工作模式：Row level（行记录）简介：...

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 '
fkibYQ8BAAAAZwAAAGsAAAABAAQANS41LjYyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAB+SJthEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#211122 15:38:14 server id 1  end_log_pos 220 	Query	thread_id=1	exec_time=0	error_code=0
use `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=0
SET 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=0
SET TIMESTAMP=1637566720/*!*/;
insert into student values(1,"zs")
/*!*/;
# at 385
#211122 15:38:40 server id 1  end_log_pos 412 	Xid = 12
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* 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)