登录  /  注册
首页 > 数据库 > mysql教程 > 正文

MySQL中binlog日志文件的详细介绍

不言
发布： 2018-10-17 17:06:49
转载
7212人浏览过

本篇文章给大家带来的内容是关于mysql中binlog日志文件的详细介绍，有一定的参考价值，有需要的朋友可以参考一下，希望对你有所帮助。

MySQL 的 binlog日志文件，记录了数据库表的全部修改操作。本文简单整理 MySQL binlog 相关知识，以及如何使用 binlog 恢复或闪回数据库数据。

STATEMENT 格式的 binlog

要想开启 binlog，需要在启动 MySQL 时传入 --log-bin 参数。或者也可以在 MySQL 配置文件 /etc/my.cnf，设置 log_bin 开启 binlog。MySQL 5.7 开始，开启 binlog 后，--server-id 参数也必须指定，否则 MySQL 服务器会启动失败。

binlog_format 支持 STATEMENT, ROW, MIXED 三种格式，MySQL 5.5 和 5.6 默认为 STATEMENT，MySQL 5.7.7 开始默认为 ROW。若 SQL 使用 UUID(), RAND(), VERSION() 等函数，或者使用存储过程、自定义函数，基于 STATEMENT 的主从复时，是不安全的（很多人可能会认为 NOW(), CURRENT_TIMESTAMP 这些函数也是不安全的，事实上是安全的） [doc1, doc2 ]。基于 ROW 的主从复制，是最安全的复制方式。

现在先来看下 STATEMENT 格式的 binlog，/etc/my.cnf 文件修改的内容如下：

server_id = 1
log_bin = mysql-bin
binlog_format = STATEMENT
binlog_row_image=FULL
登录后复制

重启 MySQL 后，在数据目录 datadir 下，比如 /var/lib/mysql/，将会生成相应的 binlog 文件，mysql-bin.index 和 mysql-bin.000001。.index 后缀的文件保存全部 binlog 文件名。mysql-bin.000001 文件记录 binlog 内容。每次 MySQL 启动或者 flush 日志，都将按序号创建一个新的日志文件。另外，当日志文件大小超过 max_binlog_size 时，也会创建一个新的日志文件。

现在来试一试 binlog 功能。假设在 testdb 库在有 hello 表，并对其中某行做修改操作：

mysql> select * from hello;
+----+-------+
| id | name  |
+----+-------+
|  1 | Andy  |
|  2 | Bill  |
|  3 | Candy |
+----+-------+
4 rows in set (0.00 sec)

mysql> update hello set name = 'Will' where id = 3;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0
登录后复制

binlog 为二进制文件，需要使用 mysqlbinlog（doc, man）命令查看：

$ sudo mysqlbinlog /var/lib/mysql/mysql-bin.000001  # 直接在 mysql 服务器上读取 binlog 文件
$ mysqlbinlog -R -h192.168.2.107 -uroot -p123456 mysql-bin.000001  # 或者，远程读取 binlog 文件
登录后复制

执行 update 后相应新增的 binlog 文件内容：

# at 154
#180617 22:47:49 server id 1  end_log_pos 219 CRC32 0x4bd9d69b     Anonymous_GTID    last_committed=0    sequence_number=1    rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#180617 22:47:49 server id 1  end_log_pos 302 CRC32 0x476fafc9     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1529246869/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
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/*!*/;
BEGIN
/*!*/;
# at 302
#180617 22:47:49 server id 1  end_log_pos 423 CRC32 0x7f2c2c7a     Query    thread_id=2    exec_time=0    error_code=0
use `testdb`/*!*/;
SET TIMESTAMP=1529246869/*!*/;
update hello set name = 'Will' where id = 3
/*!*/;
# at 423
#180617 22:47:49 server id 1  end_log_pos 454 CRC32 0x68da744a     Xid = 12
COMMIT/*!*/;
登录后复制

ROW 格式的 binlog

修改 /etc/my.cnf 的 binlog_format 为 ROW，再重启 MySQL。格式修改后，会生成一个新的 binlog 文件 mysql-bin.000002。

mysql> show create table hello;
+-------+-------------------------------------------------------------------------+
| Table | Create Table
+-------+-------------------------------------------------------------------------+
| hello | CREATE TABLE `hello` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 |
+-------+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from hello where id;
+----+------+
| id | name |
+----+------+
|  1 | Andy |
|  2 | Lily |
|  3 | Will |
+----+------+
1 row in set (0.00 sec)

mysql> update hello set name = 'David' where id = 3;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0
登录后复制

查看 ROW 格式的 binlog，需要使用 sudo mysqlbinlog -v --base64-output=DECODE-ROWS /var/lib/mysql/mysql-bin.000002 命令。执行 update 后相应新增的 binlog 内容：

# at 154
#180617 22:54:13 server id 1  end_log_pos 219 CRC32 0x2ce70d4d     Anonymous_GTID    last_committed=0    sequence_number=1    rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#180617 22:54:13 server id 1  end_log_pos 293 CRC32 0x8183fddf     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1529247253/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
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/*!*/;
BEGIN
/*!*/;
# at 293
#180617 22:54:13 server id 1  end_log_pos 346 CRC32 0x0fc7e1a4     Table_map: `testdb`.`hello` mapped to number 110
# at 346
#180617 22:54:13 server id 1  end_log_pos 411 CRC32 0xb58e729d     Update_rows: table id 110 flags: STMT_END_F
### UPDATE `testdb`.`hello`
### WHERE
###   @1=3
###   @2='Will'
### SET
###   @1=3
###   @2='David'
# at 411
#180617 22:54:13 server id 1  end_log_pos 442 CRC32 0xef964db8     Xid = 13
COMMIT/*!*/;
登录后复制

若执行如下 SQL：

mysql> insert hello (name) values ('Frank');
Query OK, 1 row affected (0.02 sec)
登录后复制

相应生成的 binlog 内容：

# at 442
#180617 22:55:47 server id 1  end_log_pos 507 CRC32 0x79de08a7     Anonymous_GTID    last_committed=1    sequence_number=2    rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 507
#180617 22:55:47 server id 1  end_log_pos 581 CRC32 0x56f9eb6a     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1529247347/*!*/;
BEGIN
/*!*/;
# at 581
#180617 22:55:47 server id 1  end_log_pos 634 CRC32 0xedb73620     Table_map: `testdb`.`hello` mapped to number 110
# at 634
#180617 22:55:47 server id 1  end_log_pos 684 CRC32 0x525a6a70     Write_rows: table id 110 flags: STMT_END_F
### INSERT INTO `testdb`.`hello`
### SET
###   @1=4
###   @2='Frank'
# at 684
#180617 22:55:47 server id 1  end_log_pos 715 CRC32 0x09a0d4de     Xid = 14
COMMIT/*!*/;
登录后复制

若执行如下 SQL：

mysql> delete from hello where id = 2;
Query OK, 1 row affected (0.02 sec)
登录后复制

相应生成的 binlog 内容：

# at 715
#180617 22:56:44 server id 1  end_log_pos 780 CRC32 0x9f52450e     Anonymous_GTID    last_committed=2    sequence_number=3    rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 780
#180617 22:56:44 server id 1  end_log_pos 854 CRC32 0x0959bc8d     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1529247404/*!*/;
BEGIN
/*!*/;
# at 854
#180617 22:56:44 server id 1  end_log_pos 907 CRC32 0x2945260f     Table_map: `testdb`.`hello` mapped to number 110
# at 907
#180617 22:56:44 server id 1  end_log_pos 956 CRC32 0xc70df255     Delete_rows: table id 110 flags: STMT_END_F
### DELETE FROM `testdb`.`hello`
### WHERE
###   @1=2
###   @2='Bill'
# at 956
#180617 22:56:44 server id 1  end_log_pos 987 CRC32 0x0c98f18e     Xid = 15
COMMIT/*!*/;
登录后复制

使用 binlog 增量恢复

MySQL 逻辑备份通常会结合全量备份和增量备份，使用 mysqldump 定期全量备份数据库，然后利用 binlog 保存增量数据。恢复数据时，就是用 mysqldump 备份的数据恢复到备份的时间点。数据库在备份时间点到当前时间的增量修改，则通过 mysqlbinlog 将 binlog 中的增量数据恢复到数据库。现在假设已经使用 mysqldump 将数据库还原到：

mysql> select * from hello;
+----+------+
| id | name |
+----+------+
|  1 | Andy |
|  2 | Lily |
|  3 | Will |
+----+------+
3 rows in set (0.00 sec)
登录后复制

之后执行的 SQL：

update hello set name = 'David' where id = 3;
insert hello (name) values ('Frank');
delete from hello where id = 2;
登录后复制

不管是使用 STATEMENT 还是 ROW，mysqlbinlog 命令都可以将 binlog 增量恢复到数据库  [doc ]。

观察 binlog 可以看到，从最开始的 update hello set name = 'David' where id = 3; 到最终的 delete from hello where id = 2;，时间上从 "2018-06-17 22:54:13" 到 "2018-06-17 22:56:44"，所以基于时间点恢复，命令如下：

$ sudo mysqlbinlog --start-datetime="2018-06-17 22:54:13" --stop-datetime="2018-06-17 22:56:44" mysql-bin.000002 | mysql -uroot -p123456
登录后复制

binlog 的事件位置号是从 "154" 到 "956"，但需要注意的是 用 --start-position 和 --stop-position 指定位置点范围，逻辑上对应的是 start ，所以基于时间点恢复，命令如下：

$ sudo mysqlbinlog --start-position=154 --stop-position=957 mysql-bin.000002 | mysql -uroot -p123456
登录后复制

两种方式任意执行，都能将数据恢复到：

mysql> select * from hello;
+----+-------+
| id | name  |
+----+-------+
|  1 | Andy  |
|  3 | David |
|  4 | Frank |
+----+-------+
3 rows in set (0.00 sec)
登录后复制

使用 binlog2sql 闪回

binlog2sql，作者为曹单锋，大众点评 DBA。binlog2sql，从 MySQL binlog 解析出你要的 SQL。根据不同选项，你可以得到原始 SQL、回滚 SQL、去除主键的 INSERT SQL 等。binlog2sql，底层实现依赖 python-mysql-replication，由该库完成 MySQL 复制协议和 binlog 格式的解析。

$ python binlog2sql/binlog2sql.py -h192.168.2.107 -uroot -p123456 --start-position=154 --stop-position=957 --start-file='mysql-bin.000002'
UPDATE `testdb`.`hello` SET `id`=3, `name`='David' WHERE `id`=3 AND `name`='Will' LIMIT 1; #start 4 end 411 time 2018-06-17 22:54:13
INSERT INTO `testdb`.`hello`(`id`, `name`) VALUES (4, 'Frank'); #start 442 end 684 time 2018-06-17 22:55:47
DELETE FROM `testdb`.`hello` WHERE `id`=2 AND `name`='Bill' LIMIT 1; #start 715 end 956 time 2018-06-17 22:56:44
登录后复制

生成回滚 sql：

$ python binlog2sql/binlog2sql.py --flashback -h192.168.2.107 -uroot -p123456 --start-position=154 --stop-position=956 --start-file='mysql-bin.000002'
INSERT INTO `testdb`.`hello`(`id`, `name`) VALUES (2, 'Bill'); #start 715 end 956 time 2018-06-17 22:56:44
DELETE FROM `testdb`.`hello` WHERE `id`=4 AND `name`='Frank' LIMIT 1; #start 442 end 684 time 2018-06-17 22:55:47
UPDATE `testdb`.`hello` SET `id`=3, `name`='Will' WHERE `id`=3 AND `name`='David' LIMIT 1; #start 154 end 411 time 2018-06-17 22:54:13
登录后复制

闪回的现实原理很简单，先通过 MySQL 复制协议的 com-binlog-dump 命令 dump 出 binlog，然后按照 binlog 的格式规范解析 binlog，将 binlog 转换成 SQL，再将这些 SQL 转换反向逻辑的 SQL，最后再倒序执行。

Java 解析 binlog

上文中的 binlog2sql 其实底层依赖 python-mysql-replication 库，这是 Python 库。如果想使用 Java 解析 binlog 可以使用 mysql-binlog-connector-java（github）库。目前开源的 CDC 工具，如 Zendesk maxwell、Redhat debezium、LinkedIn Databus 等都底层依赖 mysql-binlog-connector-java 或者其前身 open-replicator。使用 mysql-binlog-connector-java 的示例代码如下：

BinaryLogClient client = new BinaryLogClient("192.168.2.107", 3306, "root", "123456");
client.setBinlogFilename("mysql-bin.000001");
client.setBinlogPosition(4);
client.setBlocking(false);
client.registerEventListener(event -> {
    System.out.println(event);
});
client.connect();
登录后复制

输出（省略部分内容）：

...
Event{header=EventHeaderV4{timestamp=1529247253000, eventType=TABLE_MAP, serverId=1, headerLength=19, dataLength=34, nextPosition=346, flags=0}, data=TableMapEventData{tableId=110, database='testdb', table='hello', columnTypes=8, 15, columnMetadata=0, 40, columnNullability={1}}}
Event{header=EventHeaderV4{timestamp=1529247253000, eventType=EXT_UPDATE_ROWS, serverId=1, headerLength=19, dataLength=46, nextPosition=411, flags=0}, data=UpdateRowsEventData{tableId=110, includedColumnsBeforeUpdate={0, 1}, includedColumns={0, 1}, rows=[
    {before=[3, Will], after=[3, David]}
]}}
...
Event{header=EventHeaderV4{timestamp=1529247347000, eventType=TABLE_MAP, serverId=1, headerLength=19, dataLength=34, nextPosition=634, flags=0}, data=TableMapEventData{tableId=110, database='testdb', table='hello', columnTypes=8, 15, columnMetadata=0, 40, columnNullability={1}}}
Event{header=EventHeaderV4{timestamp=1529247347000, eventType=EXT_WRITE_ROWS, serverId=1, headerLength=19, dataLength=31, nextPosition=684, flags=0}, data=WriteRowsEventData{tableId=110, includedColumns={0, 1}, rows=[
    [4, Frank]
]}}
...
Event{header=EventHeaderV4{timestamp=1529247404000, eventType=TABLE_MAP, serverId=1, headerLength=19, dataLength=34, nextPosition=907, flags=0}, data=TableMapEventData{tableId=110, database='testdb', table='hello', columnTypes=8, 15, columnMetadata=0, 40, columnNullability={1}}}
Event{header=EventHeaderV4{timestamp=1529247404000, eventType=EXT_DELETE_ROWS, serverId=1, headerLength=19, dataLength=30, nextPosition=956, flags=0}, data=DeleteRowsEventData{tableId=110, includedColumns={0, 1}, rows=[
    [2, Bill]
]}}
登录后复制

以上就是MySQL中binlog日志文件的详细介绍的详细内容，更多请关注php中文网其它相关文章！

智能AI问答
PHP中文网智能助手能迅速回答你的编程问题，提供实时的代码和解决方案，帮助你解决各种难题。不仅如此，它还能提供编程资源和学习指导，帮助你快速提升编程技能。无论你是初学者还是专业人士，AI智能助手都能成为你的可靠助手，助力你在编程领域取得更大的成就。
我要提问
相关标签：
mysql
来源：segmentfault思否网
收藏 点赞
上一篇：MySQL中行锁、页锁和表锁的简单介绍 下一篇：MySQL字符集是什么?字符集的相关介绍
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn
作者最新文章
最新问题
子窗口操作父窗口，输出没反应 前两句可执行，最后一句没法应
P粉722478067来自于2024-04-19 15:37:47
0 0 19
父窗口没有输出 document.onclick = function(){ window.opener.document.write('我是子窗口的输出');  &nb...
P粉722478067来自于2024-04-18 23:52:34
0 0 40
关于CSS思维导图的课件在哪？ 课件
凡人来自于2024-04-16 10:10:18
0 0 130
PX自动转换为REM错误  <style>html {   font-size: calc(100vw / 3.75);      }...
凡人来自于2024-04-16 09:34:16
0 0 775
PHP数组从URL参数中获取的行为不如预期 我有一个包含类别ID的URL参数，我想将其视为一个数组，如下所示：http://example.com?cat[]=3,9,13在PHP中，我使用它从URL参数获取数组：$catI...
P粉785905797来自于2024-04-06 22:09:02
0 1 438
通过添加 Width 属性将内容向左移动 我已经为主体提供了边距。主要{左边缘：200px;右边距：200px；文本对齐：居中}由于我想以两行而不是一行显示文本，因此我在样式中添加了width属性。.p{字体大小：12px...
P粉738046172来自于2024-04-06 22:01:35
0 2 317
我应该在 apache 中哪里放置 CustomLog 指令 我正在使用php:7.2-apachedocker。我需要禁用运行状况检查url登录访问日志。基于此链接，他们提到了有关修改Customlog指令的信息。我不是关于需要更改Cust...
P粉573809727来自于2024-04-06 22:03:59
0 1 442
返回值中变量的格式是什么？ 我是php的新学习者。我发现有一段代码：if($x<time()){return[false,'error'];}逻辑或变量并不重要，但我不明白[false,'error']...
P粉757556355来自于2024-04-06 21:55:20
0 1 266
页面突然无法拉动 css 或 bootstrap 所以我正在开发一个页面，我昨天做了一部分，效果很好，今天我继续做剩下的部分，一切都很好。当我尝试将其作为普通html页面打开时，CSS或BOOTSTRAP不起作用，仅显示页面文本，...
P粉771233336来自于2024-04-06 21:58:04
0 1 357
如何在 React 中的排序方法上触发渲染（带有过滤器和分页）？ 这是我的Sort.js样式组件：<SortWrapper><SortText>SortBy</SortText><SortSelecton...
P粉970736384来自于2024-04-06 21:28:37
0 1 1863
相关专题
更多>
热门推荐
热门教程
更多>
相关推荐
热门推荐
最新课程
最新下载
更多>
网站特效
网站源码
网站素材
前端模板
关于我们 免责申明 意见反馈 讲师合作 广告合作 最新更新
php中文网：公益在线php培训，帮助PHP学习者快速成长！
关注服务号 技术交流群
app下载
PHP中文网订阅号
每天精选资源文章推送
PHP中文网APP
随时随地碎片化学习
PHP中文网抖音号
发现有趣的

Copyright 2014-2024 https://www.php.cn/ All Rights Reserved | php.cn | 湘ICP备2023035733号

  • 精品班

  • 技术支持

  • 技术咨询

  • 学习群

  • 会员优惠

  • 返回顶部