欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页  >  数据库

MySQL复制（二） --- 二进制日志怎么干活的_MySQL

程序员文章站 2022-05-22 23:06:58
...
bitsCN.com

由之前的文章可以了解到，二进制日志在复制中起到举足轻重的作用，所以这一篇文章着重了解一下Mysql复制背后核心组件：二进制日志的庐山真面目。

二进制日志的结构

从概念上讲，二进制日志是一系列二进制日志事件。它包括一系列的binlog文件和一个binlog索引文件，当前服务器正在写入的binlog文件称之为active binlog。其文件名是通过配置文件中的log-bin和log-bin-index来定义的。

每个binlog文件是由若干binlog事件组成，以Format_description事件开始，以Rotate事件作为文件尾。

Format_description事件包含写binlog文件的服务器信息，以及关于文件状态的关键信息。如果服务器关闭或者重新启动，会创建一个新的binlog文件，同时写入新的Format_description事件，这个事件是必须的，因为服务器关闭和重启都会产生更新。服务器写完binlog文件后，在文件结尾添加一个Rotate事件，该事件包含下一个binlog文件的文件名及其开始读取的位置。除了Format_description和Rotate事件之外，binlog文件的其他事件都被分成 group进行管理。在事务存储引擎中，每个组大致对应一个事务，对于非事务存储引擎，每个语句本身就是一个组。通常情况下，每个组要么全部执行，要么去不执行。如果由于某种原因Slave在组执行的过程中停机，那么将从该组的起点而不是刚刚执行的语句开始复制。

binlog事件的结构

二进制日志版本4（binlog format 4）是在MySQL 5.0中引入，是专门为扩展而设计的。这里主要讨论二进制日志版本4。（MySQL 3.23 4.0 4.1版本都是使用二进制日志版本3）

每个binlog事件由三个部分组成：

  • 通用头（common header）：大小固定。事件的基本信息，其中重要的字段是事件类型和事件大小。
  • 提交头（post header）：大小固定。提交头与特定的事件类型相关
  • 事件体（Event body）：大小可变。事件体存储事件的主要数据，因事件类型不同而异。

具体看一下Format_description事件：

  • binlog文件格式版本
  • 服务器版本字符串：一般包括三部分，即版本号、连字符和其他构建项。例如：5.1.42-debug-log
  • 通用头的长度：存储了通用头的长度。这里是指Format_description事件，所以不同binlog文件该字段的值不同。除了Format_description和Rotate事件外，其他事件的通用头长度都是可变的。Format_description事件的通用头长度是不变的，是因为任何版本的服务器都需要读取这个事件。Rotate事件的通用头长度也是不变的，是因为Slave连接Master时首先要用到该事件。
  • 提交头的长度：binlog文件中所有事件的提交头长度是不变的，该字段存储了各个事件的提交头长度构成的数组。由于不同服务器间的事件数目不同，所以这个字段前面还存储了服务器的事件数目。

通过事件来记录数据库变更

首先，由于二进制日志是公共资源，所有线程都向它写入语句，为了避免两个线程同时更新二进制日志，在写之前需要获得一个互斥锁Lock_log，写完之后再释放。

所有涉及到数据库更新的语句都会以Query事件的形式写入二进制日志中，除了实际执行的语句外，Query事件还包含执行语句必需的上下文附加信息。下面给出了如何记录这些上下文信息

  • 当前数据库：在Query事件添加一个特殊字段记录当前数据库。
  • 用户自定义变量的值：User_var事件记录单个用户自定义的变量的变量名及其值。
  • RAND函数的种子：Rand事件记录Rand函数所用的随机数种子。
  • 当前时间：NOW，CURDATE，CURTIME，UNIX_TIMESTAMP和SYSDATE这五个函数会用到当前时间，针对这个事件会存储一个时间戳，表示事件何时开始执行。
  • AUTO_INCREMENT字段的插入值：Intvar事件记录在语句开始前，表内部的自动增量计数器的值。
  • 调用LAST_INSERTED_ID的返回值：Intvar事件记录这个函数在语句的返回值。
  • 线程ID：主要是涉及到临时表的处理。线程ID也是作为一个独立的字段存储在Query事件中。

* 对于SYSDATE函数，它返回的是函数执行时的时间，这一点不同于NOW函数，NOW返回的是语句执行的时间。所以SYSDATE对于复制来说是不安全的，尽量少用。

LOAD DATA INFILE语句

LOAD DATA INFILE比较特殊，它的上下文是文件系统的文件。要正确地传递和执行LOAD DATA INFILE语句，需要引入新的事件类型：

  • Begin_load_query：这个事件开始传输文件中的数据
  • Append_block：如果这个文件超过了连接的数据包大小所允许的最大值，那么跟随在Begin_load_query事件后面的一个或多个Append_block事件的系列包含着这个文件的剩余部分
  • Execute_load_query：Query事件的特殊变种，它包含了在Master上执行的LOAD DATA INFILE语句

对Master上执行的每个LOAD DATA INFILE语句而言，被读取的文件被映射到一个支持内部文件的缓冲区，并在接下来的处理流程中使用。此外，一个唯一的文件ID被分配给该执行语句，并用于指向该语句读取的文件。

当语句在执行的时，该文件的内容被写入二进制日志，作为以Begin_load_query事件开头的事件序列，Begin_load_query事件表示新文件的开始，且这个事件序列后面紧跟着零个或多个Append_block事件。每个写入二进制的事件都不会超过包大小所允许的最大值，这个最大值由max-allowed_packet选项指定。

当整个文件读取到表中后，通过写Execute_load_query事件到二进制日志来终止语句的执行。这个事件包含了执行语句和分配给该执行语句的文件ID。请注意，这并非是用户写的原始语句，而是重新创建的。

* Mysql 5.0.3之前的版本使用的事件名有点不一样，依次为Load_log_event，Execute_log_event，Create_file_log_event

二进制日志过滤器

my.cnf中有两个选项可用于过滤日志：binlog-do-db和binlog-ignore-db。这两个选项可以使用多次。

MySQL过滤事件的方式对于不熟悉的人来说可能有点奇怪。Mysql过滤是在语句级完成的，binlog-*-db使用当前数据库来决定是否应该过滤该语句，而不是由语句所影响的表所在的数据库决定的。对于下面的例子，使用binlog-ignore-db=bad筛选bad数据库，下例中一个都不会写入日志。

	USE bad; INSERT INTO t1 VALUES (1),(2);USE bad; INSERT INTO good.t2 VALUES (1),(2);USE bad; UPDATE good.t1, ugly.t2 SET a = b;

至于为什么不是以语句所影响的表所在的数据库来决定，可以尝试分析一下。如果以这种逻辑，使用binlog-ignore-db=ugly筛选时，第三条语句到底要不要写入日志呢？

为了避免在执行可能被过滤的语句时发生错误，请不要编写那种表名，函数名或存储过程名前面加数据库名的语句，而是通过使用use来改变当前数据库。

还有一个需要说明的是，只要设置了binlog-do-db，过滤器会无视binlog-ignore-db的设置。

当然对于MySQL复制来说，本身不建议使用过滤器，因为日志是不完整的。

二进制日志和安全

一般来说，一个有REPLICATION SLAVE权限的用户拥有读取Master上发生的所有事件的权限，因此为了安全应该保护该账户不被损害。具体预防的措施有：

  • 尽可能使从防火墙外无法登录该账户
  • 记录所有试图登录到该账户的日志，并将日志放置在一个单独的安全服务器上
  • 加密Master和Slave间所用的连接，例如MySQL的built-in SLL
  • 敏感信息不要放入日志文件中，比如说密码
	# 第二种做法不会把明文密码写入到日志中，更安全些UPDATE employee SET pass = PASSWORD('foobar')SET @pass = PASSWORD('foobar');UPDATE employee SET pass = @pass

触发器

为了在服务器上重放二进制日志，毫无问题的处理各种表的权限，有必要用SUPER权限的用户执行所有语句。但触发器没有被定义使用SUPER权限，所以重要的是以正确的用户作为触发器的定义者去重新创建触发器。CREATE TRIGGER提供了一个DEFINER子句，如果没有给语句指定DEFINER，该语句添加DEFINER子句后被写到二进制日志中，且使用当前用户作为其定义者。

	master>SHOW BINLOG EVENTS FROM 92236 LIMIT 1/G******************** 1. row ********************     Log_name: master-bin.000038          Pos: 92236   Event_type: Query    Server_id: 1  End_log_pos: 92491         Info: use `test`; CREATE DEFINER=`root`@`localhost` TRIGGER ...

调用触发器的语句被记录到二进制日志，但它没有连接到特定的触发器。相反，当Slave执行该语句时，它会自动执行受该语句影响的表相关联的所有触发器，这意味着可以在Master和Slave上有不同的触发器。

存储过程

存储过程的定义语句的处理和触发器是类似的，CREATE PROCETURE语句也有可选的子语句DEFINER，写入二进制日志的时候，会强制加上该子句的。调用过程和触发器不一样。

# 定义存储过程

delimiter $$

CREATE PROCEDURE employee_add(p_name CHAR(64), p_email CHAR(64), p_password CHAR(64))

MODIFIES SQL DATA

BEGIN

DECLARE pass CHAR(64);

set pass = PASSWORD(p_pass)

INSERT INTO employee(name, email, password) VALUES (p_name, p_email, pass);

END $$

delimiter ;

# 调用存储过程

master> CALL employee_add('chunk', 'chuck@example.com', 'abrakadabra');

master> SHOW BINLOG EVENTS FROM 104033/G

******************** 1. row ********************

Log_name: master-bin.000038

Pos: 104033

Event_type: Intvar

Server_id: 1

End_log_pos: 104061

Info: INSERT_ID=1

******************** 2. row ********************

Log_name: master-bin.000038

Pos: 104061

Event_type: Query

Server_id: 1

End_log_pos: 104416

Info: use `test`; INSERT INTO employee(name, email, password) VALUES(

NAME_CONST('p_name',_latin1'chuck' COLLATE 'latin1_swedish_ci'),

NAME_CONST('p_email',_latin1'chuck@example.com' COLLATE 'latin1_swedish_ci'),

NAME_CONST('pass',_latin1'*FEB778934FDSFQOPL7...' COLLATE 'latin1_swedish_ci'))

有四点需要注意：

CALL语句没有被写入二进制日志。取而代之的是，执行语句作为调用的结果被写入二进制日志。

该语句改写为不包含任何对存储过程的参数的引用。取而代之的是，使用NAME_CONST函数为每个参数创建一个单值的结果集

局部声明的变量pass也被换成了NAME_CONST表达式

调用语句写入二进制日志之前，上下文信息已经写入日志，这里指Intvar事件

存储函数

存储过程的定义语句的处理和触发器是类似的，CREATE FUNCTION语句也有可选的子语句DEFINER，写入二进制日志的时候，会强制加上该子句的。调用的时候，存储函数以与触发器相同的方式被复制。有一点需要注意的就是，SELECT语句不会被写入二进制日志，但是一个含有存储函数的SELECT语句是个例外。

对于存储函数还有一个需要提到的是权限问题。CREATE ROUTINE权限是定义一个存储过程或存储函数所必需的。严格说创建一个存储程序不需要其他权限，但它通常根据定义者的权限执行。在Slave上的复制线程在不进行权限检查的情况下执行，这留下了严重的安全漏洞。MySQL 5.0之前的版本没有存储程序，这样不会有问题，因为在Master上违规的语句不会写到二进制日志中。由于存储过程被展开了，只有在Master上成功执行的语句才会写进二进制日志，所以也不会有问题。而存储函数有点不同，它并没有被展开，也就是说有可能在Master和Slave上执行不同的程序分支，带来潜在安全漏洞。在存储函数定义时使用SQL SECURITY DEFINER而不是SQL SECURITY INVOKER可以防止这一点。因为这一点的考虑，MySQL默认要求SUPER权限来定义存储函数。

Events

定义跟其他存储程序一样，也会有DEFINER子句。由于事件由事件调度器调用，因此它们总是以定义者执行从而不会存在存储函数的安全漏洞。当事件被执行时，该语句被直接写入二进制日志。由于事件是在Master上执行的，他们在Slave上是自动禁止的。但有时候如果需要升级Slave，就需要允许在Slave上执行这些事件。

	UPDATE mysql.events SET status = ENABLED WHERE status = SLAVESIDE_DISABLED;

特殊结构

尽管基于语句的复制通常是简单的，但一些特殊结构必须小心处理，才能很好的来保证Slave执行语句时的上下文跟Master上执行时是一样的。

LOAD_FILE函数

LOAD_FILE函数让你可以获取一个文件，由于在复制过程中，它不会被传输，所以需要改写。

	INSERT INTO document(author, body) VALUES ('Fox', LOAD_FILR('index.html'));# 可以用LOAD DATA FILE改写LOAD DATA INFILE 'index.html' INTO TABLE document FIELDS TERMINATED BY '@*@' LINES TERMINATED BY '&%&' (author, body) SET author = 'FOX';# 还可以用用户定义变量改写SET @document = LOAD_FILE('index.html');INSERT INTO document(author, body) VALUES ('Fox', @document);

非事务性的变化和错误处理

如果有一个employee表是支持事务的InnoDB存储引擎(主键是mail)，而跟踪employee修改的log表是不支持事务的MyISAM存储引擎。在其上定义两个触发器，一个在INSERT之前触发tr_insert_before，插入一条记录到log表，插入纪录的状态为FAIL；一个在INSERT之后触发tr_insert_after，更改刚才插入纪录的状态为OK。连续插入两条完全相同记录时，tr_insert_before被触发，tr_insert_after则不会被触发。虽然employee失败回滚了，但是log里面插入的数据却没办法回滚，这是个问题。执行后二进制日志文件内容如下。

	master> SET @pass = PASSWORD('xyz');master> INSERT INTO employee (name, mail, password) VALUES ('hu', 'hu@fox.com', @pass);master> INSERT INTO employee (name, mail, password) VALUES ('hu', 'hu@fox.com', @pass);master> SHOW BINLOG EVENTS IN 'local-bin.000023'******************** 1. row ********************     Log_name: master-bin.000023          Pos: 1252   Event_type: Query    Server_id: 1  End_log_pos: 1320         Info: use 'test'; BEGIN******************** 2. row ********************     Log_name: master-bin.000023          Pos: 1320   Event_type: Intvar    Server_id: 1  End_log_pos: 1348         Info: LAST_INSERT_ID=1******************** 3. row ********************     Log_name: master-bin.000023          Pos: 1348   Event_type: User var    Server_id: 1  End_log_pos: 1426         Info: @'pass'=_utf 0x432423jklfslagklr... COLLATE utf8_general_ci******************** 4. row ********************     Log_name: master-bin.000023          Pos: 1426   Event_type: Query    Server_id: 1  End_log_pos: 1567         Info: use 'test'; INSERT INTO employee ...******************** 5. row ********************     Log_name: master-bin.000023          Pos: 1567   Event_type: Xid    Server_id: 1  End_log_pos: 1594         Info: COMMIT /* xid=60 */******************** 6. row ********************     Log_name: master-bin.000023          Pos: 1594   Event_type: Query    Server_id: 1  End_log_pos: 1662         Info: use 'test'; BEGIN******************** 7. row ********************     Log_name: master-bin.000023          Pos: 1662   Event_type: Intvar    Server_id: 1  End_log_pos: 1690         Info: LAST_INSERT_ID=1******************** 8. row ********************     Log_name: master-bin.000023          Pos: 1690   Event_type: User var    Server_id: 1  End_log_pos: 1768         Info: @'pass'=_utf 0x432423jklfslagklr... COLLATE utf8_general_ci******************** 9. row ********************     Log_name: master-bin.000023          Pos: 1768   Event_type: Query    Server_id: 1  End_log_pos: 1909         Info: use 'test'; INSERT INTO employee ...******************** 10. row ********************     Log_name: master-bin.000023          Pos: 1909   Event_type: Query    Server_id: 1  End_log_pos: 1980         Info: use 'test'; ROLLBACK

事务

由上面的二进制日志内容可以看到，执行事务的时候需要额外的处理。对于事务来说，为了使得每个事务的所有语句在一起，不是按照事务的开始顺序而是提交顺序记入二进制日志。为了确保每个事务都作为一个单元被写入二进制日志，服务器需要将在不同线程中执行的语句分开，保存在一个事务缓存中，在事务提交的时候缓存被清空，同时事务缓存的内容被复制到二进制日志中。

那如何记录非事务性的语句呢？有这么三条规则可以使用：

  1. 如果语句被标记成事务的，它将被写入事务缓存
  2. 如果语句没有被标记成事务性的，而且事务缓存中没有语句，该语句将被直接写入二进制日志
  3. 如果语句没有被标记成事务性的，但是事务缓存中已有语句，该语句被写入事务缓存
如果一个事务里面涉及到非事务性语句时候，确保影响非事务性表的语句在事务中首先被写入，此时根据规则2会直接写入二进制日志。如果在事务的后面语句中，需要从这些语句中取到任何值，可以分配他们到临时表或变量。

使用XA进行分布式事务处理

XA包含一个事务管理器，协调一系列的资源管理器，以便于它们将一个全局的事务当作一个原子单元进行提交。每个事务都被分配一个唯一的XID，这个XID被事务管理器和资源管理器使用。当在MySQL服务器内部使用时，事务管理器通常是二进制日志而资源管理器是存储引擎。 提交一个XA事务的过程：
  1. 第一阶段，每个存储引擎被要求为提交做准备。在准备时，存储引擎将它需要正确提交的一切信息写入到安全的存储器，然后返回一个OK消息。如果有一个存储引擎的回答是否定的，则意味着它不能提交这个事务，提交被终止，而且所有的引擎都被通知回滚事务。
  2. 在所有的存储引擎都返回OK的时候，在第二阶段开始之前，事务缓存被写入二进制日志。普通事务以带有COMMIT的普通查询事件结束，与此同时，XA事务则以一个包含XID的Xid事件结束。
在第二阶段，在第一阶段中准备好的所有存储引擎都被要求提交事务。当提交时，每个存储引擎都将报告它已经在持久存储器中提交了事务。理解提交不能失败是非常重要：一旦阶段一已经通过，存储引擎就要保证事务能被提交，从而不能在阶段2报告失败。即使硬件错误导致系统崩溃，但是由于存储引擎已经在持久存储引擎中存储了这些信息，在服务器重启后，它们将能够正确恢复。恢复流程大概是：启动时，服务器将打开最后一个二进制日志并检查Format description事件。如果binlog-in-use标志被设置，那么表示服务器已经崩溃而且需要执行XA恢复。服务器通过查看活动的二进制日志来读取Xid事件，并从二进制日志中查找所有事务的XID。每个存储引擎启动后马上被要求提交列表中的事务。对于列表中每个XID，存储引擎将确定哪个XID对应的事务是已经准备好但没有提交的，如果是就提交这个事务。如果存储引擎准备的事务XID不在此列表中，那么这个XID显然在服务器崩溃前没有被写入二进制日志，因此这个事务必须被回滚。

二进制日志管理

到目前为止，所提到的事件都是Master上的数据的改动。有一些事件虽然不是代表在Master上修改数据，但它们却会影响复制。比如在服务器停止的期间修改了数据文件之类，为了应对这些问题，也需要额外类型的事件。

二进制日志和系统崩溃安全

在数据库崩溃的时候，保持数据库和二进制日志相互一致性非常重要。换句话说，如果没有写入二进制日志，那么就应该没有更改被提交到存储引擎，反之亦然。

但对于非事务性引擎则有问题。例如，不可能保证二进制日志和MyISAM表之间的一致性，因为MyISAM是非事务性的，且MyISAM在试图记录语句之前就完成了修改。对于事务性存储引擎则不一样。正如前面所讲，事件被写入二进制日志是在释放所有表锁之前，所有改变传输到各个存储引擎之后的。如果在存储引擎释放锁之前系统宕机了，服务器在允许事务提交之前一定要确认写进二进制日志的改变已经写进实际表中，而这是需要和标准文件系统同步进行协调。

回忆一下XA，为了能安全应对宕机，当第一阶段完成的时候，所有的数据都应该已经写到了磁盘。这就意味着每次一个事务完成，系统页缓存(page cache)就必须写到磁盘，这种想法的代价很高，而且很多应用并不必须这样。可以通过sync-binlog选项来控制数据写磁盘的频率，默认为0，也就是不写磁盘的调度完全交给操作系统；设置n，表示每n次事务提交就写一次磁盘。

binlog文件轮换（binlog file rotate）

MySQL隔一段时间就会启用一个新文件来保存二进制日志事件。把文件切换称之为binlog file rotate。

主要有四种操作会导致文件轮换：

  1. 服务器停止：每次服务启动都会启用一个新的二进制日志文件。
  2. binlog文件大小达到最大值：这个值可以通过binlog-cache-size参数控制。
  3. 显式刷新：FLUSH LOGS
  4. 服务器发生事故：有些事故需要特殊的人工干预，这都会在复制流程上形成一个"缺口"
每个二进制日志都是以Format description事件开始的，该事件描述服务器的信息以及文件的状态和内容的信息。其中有几个需要关注：
  • binlog-in-use标志位：服务器在写二进制日志时有可能发生宕机，因此需要知道一个文件是否被正确的关闭。而且，如果一个文件本身损坏了，用它进行恢复会产生更多的问题。binlog-in-use就是用于标识一个文件的完整性，它在文件创建的时候被设置，在Rotate事件被写入文件后被清除。
  • 二进制日志文件格式版本号：
  • 服务器版本：
为了使得即使是在宕机的情况下，也能安全的切换日志文件，服务器采用了预写策略，在一个临时文件里写明其意图，该临时文件被称为purge index file（之所以这么叫是因为在清除二进制日志时它也被使用了），文件名是基于二进制日志索引文件名。当创建了新的二进制日志文件，并更新了索引文件之后，服务器会删除该临时文件。

Incidents

所谓incident事件是指那些在服务器上没有产生数据改变但却必须要写进二进制日志的事件，因为它们有可能影响到复制。大多数这种事件并不需要DBA干预，比如数据库的重启等。

  • stop：这是一种表示服务器正常关机的事件。如果服务器宕机，就不会有stop事件。这个事件会在旧的二进制日志文件里，因为重启会启用新文件。该事件仅仅包含一个通用头。当二进制日志在Slave上重放的时候，所有Stop事件都会被忽略。那这种事件有什么用呢，因为重启复制前可能手动恢复一个备份或者修改了文件，这时候DBA在重放该日志文件的时候，可以找到该事件从而知道在哪里开始或者停止重放。
  • Incident：该事件类型是在MySQL 5.1版本引入的。和Stop事件相比，该事件包含一个标志符来指定发生了哪种类型事故。它一般用来表示服务器被强制执行某个不被记入二进制日志的变更。比如，数据库重新加载，某个非事务性事件太大而无法写入二进制日志。MySQL Cluster在其中一个节点重新加载数据库而因此不同步时也会产生该事件。当二进制日志在Slave上重放的时候，碰到Incident事件的时候将会停止复制。

删除二进制文件

有几种方式可以删除二进制文件：

	1：设置my.cnf的expire-logs-days参数2：PURGE BINARY LOGS BEFORE datetime;3：PURGE BINARY LOGS TO 'filename';

删除二进制文件的机制：
开始删除文件之前，服务器会把要删除的文件列表写到一个临时文件（purge index file），然后才开始删除文件，最后删除该临时文件。这样即使在删除日志文件过程中系统宕机也能在服务器再启动时，继续删除未删除的文件。在前面讲到，purge index file也用于文件rotate的时候。

mysqlbinlog是一个可以查看binlog日志文件和relay日志文件内容的小程序。用mysqlbinlog工具来查看二进制日志内容的输出是可以直接在服务器上执行的。该命令是分析日志的一个利器，可以查看所有日志的语句内容和事件内容，因此用来查错。该命令的具体使用方法参照官方文档。注意可以用使用--hexdump选项来查看二进制日志，不过需要了解一下日志的数据格式。比如二进制日志的整数字段是以little-Endian顺序打印出来的，所以你必须从右往左读。32位的block 03 01 00 00表示16进制的103。

---待续

bitsCN.com
相关标签： 概念 二进制 真面目 文章

上一篇： PHP+jQuery 注册模块的改进（二）：邮箱激活_PHP

下一篇： php创建新用户注册界面布局实例

推荐阅读