Mysql binlog那些事儿

最新推荐文章于 2024-08-15 09:21:27 发布

wangwei830

最新推荐文章于 2024-08-15 09:21:27 发布

阅读量352

点赞数

分类专栏： mysql 文章标签： mysql 数据库服务器

原文链接：https://mp.weixin.qq.com/s?__biz=MzI2NTA3OTY2Nw==&mid=2647636989&idx=1&sn=403ca30053e24e23323bcf2898e0dae5&chksm=f299c5e1c5ee4cf70b7ae83604680c17e0a85f74abff2024e6bc2906f94d92b46e97a86a6f87&mpshare=1&scene=24&srcid=1231HcfJ7idejJalu0Tgibjp&sharer_sharetim

版权

mysql 专栏收录该内容

20 篇文章 0 订阅

订阅专栏

binlog的作用

binlog是binary log的缩写，即二进制日志。binlog中记载了数据库发生的变化，比方说新建了一个数据库或者表、表结构发生改变、表中的数据发生了变化时都会记录相应的binlog日志。

binlog主要用在下边两个方面：

•用途一：用于复制。

现在人们张口闭口就是亿级并发，虽然是夸张，但单台物理机器所能同时处理的请求是有限的却是一个事实。为了提高并发处理请求的能力，一般将MySQL服务部署在多台物理机器中，这些服务器中维护相同的数据副本。

其中一个典型的部署方案就是一主多从，即一台主服务器（Master）和多台从服务器（Slave）。对于改变数据库状态的请求（DDL、DML等），就将它们发送给主服务器，对于单纯的查询（如SELECT语句）请求，就将它们发送给从服务器。为了让各个从服务器中存储的数据和主服务器中存储的数据一致，每当我们改变了主服务器中的数据后，就需要将改变的信息同步给各个从服务器。binlog日志中正好记录了数据库发生的各种改变的信息，从服务器读取主服务器产生的binlog日志，然后执行这些binlog日志中所记录的数据库变化语句，从而达到主从服务器数据一致的效果。

•用途二：用于恢复。

工作中我们可能有意无意的就将数据库里的数据给“毁”了，比方说写DELETE语句不加WHERE子句，那一整个表的数据都就没了！为了数据的安全性，我们需要定时备份数据库（mysqldump命令），不过这种全量备份我们不可能每秒都做一遍，而是每天或者每个月做一次全量备份。那如果在两次全量备份中间手贱写了不加WHERE条件的DELETE语句该怎么办呢？只能将数据库恢复到前一次全量备份时的样子吗？还好我们有binlog日志，我们可以从上一次全量备份开始，执行自改次备份后产生的binlog日志，直到我们写DELETE语句之前的binlog日志为止。这样就可以完成数据库恢复的功能。

怎么配置binlog

MySQL服务器并不一定会生成binlog日志，我们可以通过查看log_bin系统变量来判断当前MySQL服务器是否生成binlog日志：

上例中bin_log系统变量的值为ON，表明当前服务器生成binlog，若为OFF表明当前服务器不生成binlog。

如果当前服务器不生成binlog，我们想开启binlog，那么就需要重启服务器，设置log-bin启动选项：

–log-bin[=base_name]

binlog日志并不是仅写到一个文件中，而是写入一组文件中，这组文件的命名是这样的：

basename.000001
basename.000002
basename.000003
basename.000004
…

也就是这组日志文件名称都包含一个basename，然后以一个数字结尾。

启动选项log-bin[=base_name]中的base_name就是这组binlog日志文件名称都包含的部分。如果我们不指定base_name（即单纯的使用–log-bin），那MySQL服务器会默认将主机名-bin作为binlog日志文件的basename。

我们看一下例子。

如果启动服务器的命令是：

mysqld --log-bin

表示开启binlog，并将binlog写入MySQL服务器的数据目录下。我的主机名是xiaohaizi，那MySQL服务器程序生成的binlog日志文件名就像是这样：

xiaohaizi-bin.000001
xiaohaizi-bin.000002
xiaohaizi-bin.000003
xiaohaizi-bin.000004
…

如果启动命令是：

mysqld --log-bin=xx

表示开启binlog，并将binlog写入MySQL服务器的数据目录下，binlog日志文件名就像是这样：

xx.000001
xx.000002
xx.000003
xx.000004
…

我们可以在将启动选项log-bin[=base_name]的base_name指定为一个绝对路径，那么binlog日志就不会被放到默认的数据目录中，而是写到我们指定的绝对路径下了。比方说启动命令是：

mysqld --log-bin=/Users/xiaohaizi/xx

这样binlog日志就会被写入/Users/xiaohaizi/路径下，binlog日志文件名就像是这样：

xx.000001
xx.000002
xx.000003
xx.000004
…

小贴士：

log-bin启动选项也可以放在配置文件中，我们这里就不赘述了。

binlog在文件系统中的内容

我们刚强调，binlog日志不是单个文件，而是一组包含共同basename的文件。比方说现在我的机器上有以下4个binlog文件：

xiaohaizi-bin.000001
xiaohaizi-bin.000002
xiaohaizi-bin.000003
xiaohaizi-bin.000004

这些binlog文件并不能直接被当作文本打开，毕竟人家的名字是binlog，存储的是二进制数据。

除了真正存储binlog日志的文件外，MySQL服务器还会在相同的路径下生成一个关于binlog的索引文件，在我的系统上它的名称就是：

xiaohaizi-bin.index

这个索引文件是一个文本文件，我们可以直接打开：

shell> cat xiaohaizi-bin.index
./xiaohaizi-bin.000001
./xiaohaizi-bin.000002
./xiaohaizi-bin.000003
./xiaohaizi-bin.000004

可以看到，这个索引文件只是简单的将各个binlog文件的路径存储了起来而已。
查看binlog的语句

binlog中记录数据库发生更改的各种事件（events），这些事件的种类非常多，完整的事件类型如下所示：

enum Log_event_type {
UNKNOWN_EVENT= 0,
START_EVENT_V3= 1,
QUERY_EVENT= 2,
STOP_EVENT= 3,
ROTATE_EVENT= 4,
INTVAR_EVENT= 5,
LOAD_EVENT= 6,
SLAVE_EVENT= 7,
CREATE_FILE_EVENT= 8,
APPEND_BLOCK_EVENT= 9,
EXEC_LOAD_EVENT= 10,
DELETE_FILE_EVENT= 11,
NEW_LOAD_EVENT= 12,
RAND_EVENT= 13,
USER_VAR_EVENT= 14,
FORMAT_DESCRIPTION_EVENT= 15,
XID_EVENT= 16,
BEGIN_LOAD_QUERY_EVENT= 17,
EXECUTE_LOAD_QUERY_EVENT= 18,
TABLE_MAP_EVENT = 19,
PRE_GA_WRITE_ROWS_EVENT = 20,
PRE_GA_UPDATE_ROWS_EVENT = 21,
PRE_GA_DELETE_ROWS_EVENT = 22,
WRITE_ROWS_EVENT = 23,
UPDATE_ROWS_EVENT = 24,
DELETE_ROWS_EVENT = 25,
INCIDENT_EVENT= 26,
HEARTBEAT_LOG_EVENT= 27,
IGNORABLE_LOG_EVENT= 28,
ROWS_QUERY_LOG_EVENT= 29,
WRITE_ROWS_EVENT = 30,
UPDATE_ROWS_EVENT = 31,
DELETE_ROWS_EVENT = 32,
GTID_LOG_EVENT= 33,
ANONYMOUS_GTID_LOG_EVENT= 34,
PREVIOUS_GTIDS_LOG_EVENT= 35,
ENUM_END_EVENT
/* end marker */
};

其中的一些我们熟悉的事件：

•WRITE_ROWS_EVENT：插入记录。

•UPDATE_ROWS_EVENT：更新记录。

•DELETE_ROWS_EVENT：删除记录。

像创建、修改数据库或者表结构这些语句也都可以找到对应类型的事件，我们这里就不一一展开了。

为了查看我们的binlog中包含了哪些事件，可以使用下边这个语句（带中括号[]的表示可以省略的语句）：

SHOW BINLOG EVENTS
[IN ‘log_name’]
[FROM pos]
[LIMIT [offset,] row_count]

其中：

•[IN ‘log_name’]：log_name表示我们要查看哪个binlog日志文件的内容。

•[FROM pos]：pos表示我们要查看binlog文件的起始偏移量（通过指定这个值可以直接去查看某个偏移量处的事件）。

•LIMIT [offset,] row_count：这个LIMIT子句的含义和我们写SQL语句中LIMIT子句的含义是一样的，offset表示我们要从哪个事件开始查看，row_count表示我们要查看多少个事件。

下边该做一下测试了。

如果我们直接执行SHOW BINLOG EVENTS，表示查看第1个binlog日志文件的内容，在我的机器上就是xiaohaizi-bin.000001这个文件的内容：

mysql> SHOW BINLOG EVENTS;
±---------------------±----±---------------±----------±------------±--------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
±---------------------±----±---------------±----------±------------±--------------------------------------+
| xiaohaizi-bin.000001 | 4 | Format_desc | 3 | 123 | Server ver: 5.7.21-log, Binlog ver: 4 |
| xiaohaizi-bin.000001 | 123 | Previous_gtids | 3 | 154 | |
| xiaohaizi-bin.000001 | 154 | Anonymous_Gtid | 3 | 219 | SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’ |
| xiaohaizi-bin.000001 | 219 | Query | 3 | 296 | BEGIN |
| xiaohaizi-bin.000001 | 296 | Table_map | 3 | 367 | table_id: 138 (xiaohaizi.s1) |
| xiaohaizi-bin.000001 | 367 | Update_rows | 3 | 634 | table_id: 138 flags: STMT_END_F |
| xiaohaizi-bin.000001 | 634 | Xid | 3 | 665 | COMMIT /* xid=65 */ |
| xiaohaizi-bin.000001 | 665 | Stop | 3 | 688 | |
±---------------------±----±---------------±----------±------------±--------------------------------------+
8 rows in set (0.01 sec)

可以看到共输出了8个列，其中：

•Log_name：表示binlog日志的文件名。•Pos：表示该事件在binlog日志文件中的起始偏移量。•Event_type：表示这个事件的类型。•Server_id：表示产生该事件的server_id（server_id是一个系统变量，我们可以通过配置让不通的MySQL服务器拥有不通的server_id）。•End_log_pos：表示下一个事件的在binlog日志文件中的起始偏移量。•Info：关于本事件的一些说明。

如果您对MySQL了解不多的话，那上边执行SHOW BINLOG EVENTS语句的输出的大部分事件可能都比较懵，可能能看懂两个事件：

•起始偏移量为219（Pos=219）的事件是一个表明开始事务（BEGIN）的事件。•起始偏移量为367（Pos=367）的事件是一个更新记录的事件（UPDATE）。

好了现在先不细究各种类型的事件都代表什么，我们目前只需要知道binlog日志是由若干个事件组成的就好了。

如果我们想看其他binlog日志的详细情况，那就需要用到IN子句了：

mysql> SHOW BINLOG EVENTS IN ‘xiaohaizi-bin.000004’;
±---------------------±----±---------------±----------±------------±------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
±---------------------±----±---------------±----------±------------±------------------------------------------+
| xiaohaizi-bin.000004 | 4 | Format_desc | 3 | 123 | Server ver: 5.7.21-log, Binlog ver: 4 |
| xiaohaizi-bin.000004 | 123 | Previous_gtids | 3 | 154 | |
| xiaohaizi-bin.000004 | 154 | Anonymous_Gtid | 3 | 219 | SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’ |
| xiaohaizi-bin.000004 | 219 | Query | 3 | 327 | use xiaohaizi; create table tt1 (c int) |
| xiaohaizi-bin.000004 | 327 | Anonymous_Gtid | 3 | 392 | SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’ |
| xiaohaizi-bin.000004 | 392 | Query | 3 | 469 | BEGIN |
| xiaohaizi-bin.000004 | 469 | Table_map | 3 | 520 | table_id: 167 (xiaohaizi.tt1) |
| xiaohaizi-bin.000004 | 520 | Write_rows | 3 | 560 | table_id: 167 flags: STMT_END_F |
| xiaohaizi-bin.000004 | 560 | Xid | 3 | 591 | COMMIT /* xid=71 / |
| xiaohaizi-bin.000004 | 591 | Anonymous_Gtid | 3 | 656 | SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’ |
| xiaohaizi-bin.000004 | 656 | Query | 3 | 733 | BEGIN |
| xiaohaizi-bin.000004 | 733 | Table_map | 3 | 784 | table_id: 167 (xiaohaizi.tt1) |
| xiaohaizi-bin.000004 | 784 | Update_rows | 3 | 830 | table_id: 167 flags: STMT_END_F |
| xiaohaizi-bin.000004 | 830 | Xid | 3 | 861 | COMMIT / xid=73 */ |
±---------------------±----±---------------±----------±------------±------------------------------------------+
14 rows in set (0.00 sec)

这样我们就看到了xiaohaizi-bin.000004这个binlog日志文件中存储了哪些事件了。

大家可以自行测试一下FROM子句和LIMIT子句的用法，这里就不展示了。
mysqlbinlog工具的使用

由于binlog是二进制格式的，我们不能直接以文本的形式查看。使用SHOW BINLOG EVENTS又只能看到粗略的信息，如果我们想查看binlog日志文件的详细信息的话，就需要使用MySQL给我们提供的实用工具——mysqlbinlog。

像mysqld、mysql这些可执行文件一样，mysqlbinlog也被放在了MySQL安装目录下的bin目录下。

我们可以将想查看的binlog日志文件路径作为mysqlbinlog的参数，就能查看文本形式的事件详细信息了。比方说我们看一下xiaohaizi-bin.000001：

shell> mysqlbinlog ./xiaohaizi-bin.000001
/!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1/;
/!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0/;
DELIMITER /!/;

at 4

#211202 20:01:14 server id 3 end_log_pos 123 CRC32 0xa308715b Start: binlog v 4, server v 5.7.21-log created 211202 20:01:14 at startup
ROLLBACK/!/;
BINLOG ’
irWoYQ8DAAAAdwAAAHsAAAAAAAQANS43LjIxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACKtahhEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AVtxCKM=
'/!/;

at 123

#211202 20:01:14 server id 3 end_log_pos 154 CRC32 0x0d6a1ce6 Previous-GTIDs

[empty]

at 154

#211202 20:07:07 server id 3 end_log_pos 219 CRC32 0xab157b64 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

#211202 20:07:07 server id 3 end_log_pos 296 CRC32 0xedb6b609 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1638446827/!/;
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=1436549152/!/;
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 296

#211202 20:07:07 server id 3 end_log_pos 367 CRC32 0x43cd57ee Table_map: xiaohaizi.s1 mapped to number 138

at 367

#211202 20:07:07 server id 3 end_log_pos 634 CRC32 0xe2981d9e Update_rows: table id 138 flags: STMT_END_F

BINLOG ’
67aoYRMDAAAARwAAAG8BAAAAAIoAAAAAAAEACXhpYW9oYWl6aQACczEACAMPDw8PDw8PDiwBLAEs
ASwBLAEsASwB/u5XzUM=
67aoYR8DAAAACwEAAHoCAAAAAIoAAAAAAAEAAgAI//8ApAMAABgAZmt3YW91b2syY2sxY2RlMzA2
bzZ2NHcxCQAxMzA4NzI2NzgTAHBqdHFxc2dsMngxMjd4MWZjdngBAG0MAHBycmp3bmtxbjV1aRoA
NHN3cWJsNXEzd3V2bzUyZGdscmI1eWlmencJAGxzMjFoNHZhNwCkAwAAGABma3dhb3VvazJjazFj
ZGUzMDZvNnY0dzEJADEzMDg3MjY3OBMAcGp0cXFzZ2wyeDEyN3gxZmN2eAEAbQwAcHJyandua3Fu
NXVpGgA0c3dxYmw1cTN3dXZvNTJkZ2xyYjV5aWZ6dwIAeHieHZji
'/!/;

at 634

#211202 20:07:07 server id 3 end_log_pos 665 CRC32 0xe586ffeb Xid = 65
COMMIT/!/;

at 665

#211202 20:07:19 server id 3 end_log_pos 688 CRC32 0x8c69bad2 Stop
SET @@SESSION.GTID_NEXT= ‘AUTOMATIC’ /* added by mysqlbinlog / /!*/;
DELIMITER ;

End of log file

/!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE/;
/!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0/;

哇唔，好大一片输出！

其中以# at xx开头的表示这是一个事件的开始，诸如：

•# at 4：表示在偏移量为4的地方有一个事件，下边是该事件的详细信息。

•# at 123表示在偏移量为123的地方有一个事件，下边是该事件的详细信息。

…

具体的格式请看下篇。

wangwei830

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql binlog那些事儿

binlog的作用binlog是binary log的缩写，即二进制日志。binlog中记载了数据库发生的变化，比方说新建了一个数据库或者表、表结构发生改变、表中的数据发生了变化时都会记录相应的binlog日志。binlog主要用在下边两个方面：•用途一：用于复制。现在人们张口闭口就是亿级并发，虽然是夸张，但单台物理机器所能同时处理的请求是有限的却是一个事实。为了提高并发处理请求的能力，一般将MySQL服务部署在多台物理机器中，这些服务器中维护相同的数据副本。其中一个典型的部署方案就是一主多从，即
复制链接

扫一扫

专栏目录