MySQL系列之二进制日志Binlog学习笔记

举报
yd_273762914 发表于 2020/12/02 23:48:22 2020/12/02
【摘要】 学习准备： Mysql服务于端（5.7+版本）Mysql客户端软件（workbench、sqlyog etc.） 1、什么是Binlog？ 在上一篇博客的学习，我们知道了InnoDB存储引擎的两种事务日志，redo log是InnoDB特有的功能，而MySQL也是有自己的日志机制的，也即本文学习的binlog binlog（二进制日志）：binary log，简...

学习准备：

  • Mysql服务于端（5.7+版本）
  • Mysql客户端软件（workbench、sqlyog etc.）

1、什么是Binlog？

在上一篇博客的学习，我们知道了InnoDB存储引擎的两种事务日志，redo log是InnoDB特有的功能，而MySQL也是有自己的日志机制的，也即本文学习的binlog

binlog（二进制日志）：binary log，简称是binlog，binlog记录所有数据库表结构变更以及表数据修改，而不会记录SELECT和SHOW这类操作，数据保存的是二进制数据

binlog以事件的形式保存，还包括sql执行所需的时间等等信息，开启Binlog日志有以下两个最重要的使用场景：

  • 主从复制：binlog的特性可以被应用于主从复制，主库（master）开启binlog功能，从库（salve）通过binlog的事件记录，将数据同步到数据库
  • 数据恢复：binlog可以用于在数据恢复，因为binlog记录了sql的变更修改操作日志，如果数一不小心删库了，就可以通过binlog进行数据恢复

2、Binlog记录模式

Binlog日志的文件名默认是“主机名_binlog-序列号”的形式，eg：adminstrator_binlog-000001，当然也可以在配置文件修改命名为mysqlbinlog：

[mysql]
log-bin=mysqlbinlog

  
 
    
  
  • 1
    • 
  
  • 2
    •

binlog对log event进行记录，文件记录模式有STATEMENT、ROW和MIXED三种，具体含义：

  • Row模式：row-based replication，简称RBR，日志会记录每一行数据被修改的情况，如果用于主从复制，在slave数据库会对相同的数据进行修改
  • STATEMENT模式：statement-based replication, 简称SBR，这种模式，没错修改变更的sql都会被记录在日志里
  • MIXED模式：mixed-based replication, 简称MB，这种模式，一般会使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog

在 MySQL 5.7.7 之前，默认的格式是 STATEMENT，在 MySQL 5.7.7 及更高版本中，默认值是 ROW。日志格式通过 binlog-format 指定，如 binlog-format=STATEMENT、binlog-format=ROW、binlog-format=MIXED。

3、Binlog文件结构

binlog机制中用来表示保存修改操作的数据结构是Log event。不同的修改操作对应的不同的log event。比较常用的log event有：Query event、Row event、Xid event等，可以获取一份log来看看：

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#201012 11:26:13 server id 1  end_log_pos 124 CRC32 0x253ff34e 	Start: binlog v 4, server v 8.0.13 created 201012 11:26:13 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
1cyDXw8BAAAAeAAAAHwAAAABAAQAOC4wLjEzAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADVzINfEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA
CgFO8z8l
'/*!*/;
# at 124
#201012 11:26:14 server id 1  end_log_pos 155 CRC32 0xf80d0acf 	Previous-GTIDs
# [empty]
# at 155
#201012 14:04:52 server id 1  end_log_pos 228 CRC32 0xe767e3f3 	Anonymous_GTID	last_committed=0	sequence_number=1	rbr_only=no	original_committed_timestamp=1602482692499600	immediate_commit_timestamp=1602482692499600	transaction_length=183
# original_commit_timestamp=1602482692499600 (2020-10-12 14:04:52.499600 ?¨ª¨¤¡ä?¡Â??¡ã?¦Ìo¡À¨º¡Á?¨º¡À??)
# immediate_commit_timestamp=1602482692499600 (2020-10-12 14:04:52.499600 ?¨ª¨¤¡ä?¡Â??¡ã?¦Ìo¡À¨º¡Á?¨º¡À??)
/*!80001 SET @@session.original_commit_timestamp=1602482692499600*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 228
#201012 14:04:52 server id 1  end_log_pos 338 CRC32 0x8f981f33 	Query	thread_id=9	exec_time=0	error_code=0	Xid = 16
SET TIMESTAMP=1602482692/*!*/;
SET @@session.pseudo_thread_id=9/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
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/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
create database `test`
/*!*/;
# at 338
#201012 14:31:46 server id 1  end_log_pos 411 CRC32 0xc6dbb42e 	Anonymous_GTID	last_committed=1	sequence_number=2	rbr_only=no	original_committed_timestamp=1602484306791600	immediate_commit_timestamp=1602484306791600	transaction_length=181
# original_commit_timestamp=1602484306791600 (2020-10-12 14:31:46.791600 ?¨ª¨¤¡ä?¡Â??¡ã?¦Ìo¡À¨º¡Á?¨º¡À??)
# immediate_commit_timestamp=1602484306791600 (2020-10-12 14:31:46.791600 ?¨ª¨¤¡ä?¡Â??¡ã?¦Ìo¡À¨º¡Á?¨º¡À??)
/*!80001 SET @@session.original_commit_timestamp=1602484306791600*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

  
 
    
  
  • 1
    • 
  
  • 2
    • 
  
  • 3
    • 
  
  • 4
    • 
  
  • 5
    • 
  
  • 6
    • 
  
  • 7
    • 
  
  • 8
    • 
  
  • 9
    • 
  
  • 10
    • 
  
  • 11
    • 
  
  • 12
    • 
  
  • 13
    • 
  
  • 14
    • 
  
  • 15
    • 
  
  • 16
    • 
  
  • 17
    • 
  
  • 18
    • 
  
  • 19
    • 
  
  • 20
    • 
  
  • 21
    • 
  
  • 22
    • 
  
  • 23
    • 
  
  • 24
    • 
  
  • 25
    • 
  
  • 26
    • 
  
  • 27
    • 
  
  • 28
    • 
  
  • 29
    • 
  
  • 30
    • 
  
  • 31
    • 
  
  • 32
    • 
  
  • 33
    • 
  
  • 34
    • 
  
  • 35
    • 
  
  • 36
    • 
  
  • 37
    • 
  
  • 38
    • 
  
  • 39
    • 
  
  • 40
    • 
  
  • 41
    • 
  
  • 42
    •

画表格表示其主要信息：

信息 描述
position 位于文件中的位置，表示事件记录从文件第?个字节开始
timestamp 事件开始的时间戳
server_id 服务器的server id
end_log_pos 表示下一个事件开始的位置
thread_id 执行该事件的线程id
exec_time 事件执行的花费时间
error_code 错误码，0意味着没有发生错误
type 事件类型Query

4、Binlog写入机制

binlog 什么时候刷新到磁盘？binlog刷数据到磁盘跟参数 sync_binlog 相关

  • 如果设置为0：则表示MySQL不控制binlog的刷新，由文件系统去控制日志的刷新；
  • 如果设置为不为0的值：则表示每 sync_binlog 次事务，MySQL调用文件系统刷新binlog到磁盘中

然后binlog具体是怎么刷日志的？画图表示：
在这里插入图片描述

  • 根据记录模式和操作触发log事件从而生成log event
  • 将事务执行过程的log event写入缓存区，缓存区是一种binlog_cache_mngr数据结构，该结构中有两个缓冲区，一个是stmt_cache，用于存放不支持事务的信息；另一个是trx_cache，用于存放支持事务的信息。
  • 事务在提交阶段会将产生的log event写入到磁盘binlog文件中

5、Binlog命令操作

  • 启用Binlog
    通过命令set global log_bin=1;启动，也可以在my.ini配置文件加上配置：
[mysql]
#log-bin=ON
binlog-format=ROW
log-bin=mysqlbinlog

  
 
    
  
  • 1
    • 
  
  • 2
    • 
  
  • 3
    • 
  
  • 4
    •

不能通过命令配置，只能修改my.ini配置文件
在这里插入图片描述

  • 确定binlog是否启动
    在这里插入图片描述

  • 查看binlog相关参数
    在这里插入图片描述

  • 设置binlog_format模式

set global binlog_format='ROW';　

  
 
    
  
  • 1
    •
  • binlog events命令
//等价于show master logs;
show binary logs; 
//查看最新一个binlog日志文件名称和Position
show master status;
//查看 binlog 内容
show binlog events;
//查看 指定binlog 内容
show binlog events in 'mysqlbinlog.000001';

  
 
    
  
  • 1
    • 
  
  • 2
    • 
  
  • 3
    • 
  
  • 4
    • 
  
  • 5
    • 
  
  • 6
    • 
  
  • 7
    • 
  
  • 8
    •
  • mysqlbinlog 命令
// 查看指定的binlog日志文件
mysqlbinlog "文件名"
// 查看指定binlog文件并保存到test.sql文件
mysqlbinlog "文件名" > "test.sql"

  
 
    
  
  • 1
    • 
  
  • 2
    • 
  
  • 3
    • 
  
  • 4
    •

要到对应目录，不然就需要指定路径：
在这里插入图片描述
查看test.sql：
在这里插入图片描述

  • 使用 binlog 恢复数据
// 按指定时间恢复
mysqlbinlog --start-datetime="2020-10-25 18:00:00" --stop-
datetime="2020-10-26 00:00:00" mysqlbinlog.000001 | mysql -uroot -p123

  
 
    
  
  • 1
    • 
  
  • 2
    • 
  
  • 3
    • 
 
// 按事件位置号恢复
mysqlbinlog --start-position=592 --stop-position=708 mysqlbinlog.000001 | mysql -u root -p

  
 
    
  
  • 1
    • 
  
  • 2
    •
  • 清除binlog文件

指定日志文件

purge binary logs to 'mysqlbinlog.000001';

  
 
    
  
  • 1
    •

按时间清日志

purge binary logs before '2020-10-10 00:00:00';

  
 
    
  
  • 1
    •

清除所有日志

reset master; //清除所有文件

  
 
    
  
  • 1
    •

ps：可以通过设置expire_logs_days参数来启动自动清理功能。默认值为0表示没启用。设置为1表示超出1天binlog文件会自动删除掉。


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

mysql> show master status\G;
*************************** 1. row *************************** File: mysqlbinlog.000001 Position: 519 Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

mysql> show binlog events \G;
... 
mysql> show binlog events in 'mysqlbinlog.000001'\G;
*************************** 1. row *************************** Log_name: mysqlbinlog.000001 Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 124 Info: Server ver: 8.0.13, Binlog ver: 4
*************************** 2. row *************************** Log_name: mysqlbinlog.000001 Pos: 124
 Event_type: Previous_gtids
  Server_id: 1
End_log_pos: 155 Info:
*************************** 3. row *************************** Log_name: mysqlbinlog.000001 Pos: 155
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 228 Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 4. row *************************** Log_name: mysqlbinlog.000001 Pos: 228
 Event_type: Query
  Server_id: 1
End_log_pos: 338 Info: create database `test` /* xid=16 */
*************************** 5. row *************************** Log_name: mysqlbinlog.000001 Pos: 338
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 411 Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 6. row *************************** Log_name: mysqlbinlog.000001 Pos: 411
 Event_type: Query
  Server_id: 1
End_log_pos: 519 Info: drop database `test` /* xid=40 */
6 rows in set (0.00 sec)


  
 
    
  
  • 1
    • 
  
  • 2
    • 
  
  • 3
    • 
  
  • 4
    • 
  
  • 5
    • 
  
  • 6
    • 
  
  • 7
    • 
  
  • 8
    • 
  
  • 9
    • 
  
  • 10
    • 
  
  • 11
    • 
  
  • 12
    • 
  
  • 13
    • 
  
  • 14
    • 
  
  • 15
    • 
  
  • 16
    • 
  
  • 17
    • 
  
  • 18
    • 
  
  • 19
    • 
  
  • 20
    • 
  
  • 21
    • 
  
  • 22
    • 
  
  • 23
    • 
  
  • 24
    • 
  
  • 25
    • 
  
  • 26
    • 
  
  • 27
    • 
  
  • 28
    • 
  
  • 29
    • 
  
  • 30
    • 
  
  • 31
    • 
  
  • 32
    • 
  
  • 33
    • 
  
  • 34
    • 
  
  • 35
    • 
  
  • 36
    • 
  
  • 37
    • 
  
  • 38
    • 
  
  • 39
    • 
  
  • 40
    • 
  
  • 41
    • 
  
  • 42
    • 
  
  • 43
    • 
  
  • 44
    • 
  
  • 45
    • 
  
  • 46
    • 
  
  • 47
    • 
  
  • 48
    • 
  
  • 49
    • 
  
  • 50
    • 
  
  • 51
    • 
  
  • 52
    • 
  
  • 53
    • 
  
  • 54
    • 
  
  • 55
    • 
  
  • 56
    • 
  
  • 57
    • 
  
  • 58
    • 
  
  • 59
    • 
  
  • 60
    • 
  
  • 61
    • 
  
  • 62
    • 
  
  • 63
    • 
  
  • 64
    • 
  
  • 65
    •

6、Redo Log和Binlog区别

  • Redo log是InnoDB特有的功能，属于事务日志；Binlog是MySQL的功能
  • Redo log不是二进制文件，而Binlog是一种二进制的日志文件
  • Redo log是物理日志，记录的是数据页更新的状态内容，Binlog是逻辑日志，记录的是更新过程
  • Redo Log作为服务器异常宕机后事务数据自动恢复使用，Binlog可以作为主从复制和数据恢复使用
  • Redo Log日志是循环写，日志空间大小是固定，Binlog是追加写入，不会覆盖写

对于redo log可以参考我之前博客：MySQL系列之事务日志redo log学习笔记

附录：参考资料

文章来源: smilenicky.blog.csdn.net，作者：smileNicky，版权归原作者所有，如需转载，请联系作者。

原文链接：smilenicky.blog.csdn.net/article/details/109077630

【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
MySQL SQL
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入