时间：2021-07-01 10:21:17 帮助过：8人阅读
由之前的文章可以了解到，二进制日志在复制中起到举足轻重的作用，所以这一篇文章着重了解一下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在组执行的过程中停机，那么将从该组的起点而不是刚刚执行的语句开始复制。
二进制日志版本4（binlog format 4）是在MySQL 5.0中引入，是专门为扩展而设计的。这里主要讨论二进制日志版本4。（MySQL 3.23 4.0 4.1版本都是使用二进制日志版本3）
每个binlog事件由三个部分组成：
具体看一下Format_description事件：
首先，由于二进制日志是公共资源，所有线程都向它写入语句，为了避免两个线程同时更新二进制日志，在写之前需要获得一个互斥锁Lock_log，写完之后再释放。
所有涉及到数据库更新的语句都会以Query事件的形式写入二进制日志中，除了实际执行的语句外，Query事件还包含执行语句必需的上下文附加信息。下面给出了如何记录这些上下文信息
* 对于SYSDATE函数，它返回的是函数执行时的时间，这一点不同于NOW函数，NOW返回的是语句执行的时间。所以SYSDATE对于复制来说是不安全的，尽量少用。
LOAD DATA INFILE比较特殊，它的上下文是文件系统的文件。要正确地传递和执行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上发生的所有事件的权限，因此为了安全应该保护该账户不被损害。具体预防的措施有：
# 第二种做法不会把明文密码写入到日志中，更安全些
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'))
有四点需要注意：
存储过程的定义语句的处理和触发器是类似的，CREATE FUNCTION语句也有可选的子语句DEFINER，写入二进制日志的时候，会强制加上该子句的。调用的时候，存储函数以与触发器相同的方式被复制。有一点需要注意的就是，SELECT语句不会被写入二进制日志，但是一个含有存储函数的SELECT语句是个例外。
对于存储函数还有一个需要提到的是权限问题。CREATE ROUTINE权限是定义一个存储过程或存储函数所必需的。严格说创建一个存储程序不需要其他权限，但它通常根据定义者的权限执行。在Slave上的复制线程在不进行权限检查的情况下执行，这留下了严重的安全漏洞。MySQL 5.0之前的版本没有存储程序，这样不会有问题，因为在Master上违规的语句不会写到二进制日志中。由于存储过程被展开了，只有在Master上成功执行的语句才会写进二进制日志，所以也不会有问题。而存储函数有点不同，它并没有被展开，也就是说有可能在Master和Slave上执行不同的程序分支，带来潜在安全漏洞。在存储函数定义时使用SQL SECURITY DEFINER而不是SQL SECURITY INVOKER可以防止这一点。因为这一点的考虑，MySQL默认要求SUPER权限来定义存储函数。
定义跟其他存储程序一样，也会有DEFINER子句。由于事件由事件调度器调用，因此它们总是以定义者执行从而不会存在存储函数的安全漏洞。当事件被执行时，该语句被直接写入二进制日志。由于事件是在Master上执行的，他们在Slave上是自动禁止的。但有时候如果需要升级Slave，就需要允许在Slave上执行这些事件。
UPDATE mysql.events SET status = ENABLED WHERE status = SLAVESIDE_DISABLED;
尽管基于语句的复制通常是简单的，但一些特殊结构必须小心处理，才能很好的来保证Slave执行语句时的上下文跟Master上执行时是一样的。
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
由上面的二进制日志内容可以看到，执行事务的时候需要额外的处理。对于事务来说，为了使得每个事务的所有语句在一起，不是按照事务的开始顺序而是提交顺序记入二进制日志。为了确保每个事务都作为一个单元被写入二进制日志，服务器需要将在不同线程中执行的语句分开，保存在一个事务缓存中，在事务提交的时候缓存被清空，同时事务缓存的内容被复制到二进制日志中。
那如何记录非事务性的语句呢？有这么三条规则可以使用：
到目前为止，所提到的事件都是Master上的数据的改动。有一些事件虽然不是代表在Master上修改数据，但它们却会影响复制。比如在服务器停止的期间修改了数据文件之类，为了应对这些问题，也需要额外类型的事件。
在数据库崩溃的时候，保持数据库和二进制日志相互一致性非常重要。换句话说，如果没有写入二进制日志，那么就应该没有更改被提交到存储引擎，反之亦然。
但对于非事务性引擎则有问题。例如，不可能保证二进制日志和MyISAM表之间的一致性，因为MyISAM是非事务性的，且MyISAM在试图记录语句之前就完成了修改。对于事务性存储引擎则不一样。正如前面所讲，事件被写入二进制日志是在释放所有表锁之前，所有改变传输到各个存储引擎之后的。如果在存储引擎释放锁之前系统宕机了，服务器在允许事务提交之前一定要确认写进二进制日志的改变已经写进实际表中，而这是需要和标准文件系统同步进行协调。
回忆一下XA，为了能安全应对宕机，当第一阶段完成的时候，所有的数据都应该已经写到了磁盘。这就意味着每次一个事务完成，系统页缓存(page cache)就必须写到磁盘，这种想法的代价很高，而且很多应用并不必须这样。可以通过sync-binlog选项来控制数据写磁盘的频率，默认为0，也就是不写磁盘的调度完全交给操作系统；设置n，表示每n次事务提交就写一次磁盘。
MySQL隔一段时间就会启用一个新文件来保存二进制日志事件。把文件切换称之为binlog file rotate。
主要有四种操作会导致文件轮换：
所谓incident事件是指那些在服务器上没有产生数据改变但却必须要写进二进制日志的事件，因为它们有可能影响到复制。大多数这种事件并不需要DBA干预，比如数据库的重启等。
有几种方式可以删除二进制文件：
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