[一、前言
mysql的原生复制是基于binlog实现的，binlog是mysql实例级别的服务日志，是一个二进制格式的日志文件，和存储引擎没有关系，即任何mysql存储引擎都会有binlog。binlog记录了所有的DML、DDL语句（不记录select和show语句），以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。binlog 的主要作用是复制和数据恢复。
本文主要介绍binlog的格式，以及mysql主从复制和恢复的实现原理。
二、Binlog介绍
2.1、Binlog的格式介绍
binlog有三种格式，分别是：ROW、STATEMENT、MIXED。
下文是mysql官方文档对于三种格式的介绍。
Binary Logging Formats
The server uses several logging formats to record information in the binary log:
Replication capabilities in MySQL originally were based on propagation of SQL statements from master to slave. This is called statement-based logging. You can cause this format to be used by starting the server with --binlog-format=STATEMENT.
In row-based logging (the default), the master writes events to the binary log that indicate how individual table rows are affected. You can cause the server to use row-based logging by starting it with --binlog-format=ROW.
A third option is also available: mixed logging. With mixed logging, statement-based logging is used by default, but the logging mode switches automatically to row-based in certain cases as described below. You can cause MySQL to use mixed logging explicitly by starting mysqld with the option --binlog-format=MIXED.
With statement-based replication, there may be issues with replicating nondeterministic statements. In deciding whether or not a given statement is safe for statement-based replication, MySQL determines whether it can guarantee that the statement can be replicated using statement-based logging. If MySQL cannot make this guarantee, it marks the statement as potentially unreliable and issues the warning, Statement may not be safe to log in statement format.
You can avoid these issues by using MySQL's row-based replication instead.
那么这三种格式到底有什么不同呢？
Statement
每一条会修改数据的sql都会记录在binlog中，是基于sql语句的。
优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。
缺点：一些特定函数功能，slave可与master上要保持一致会有很多相关问题(如sleep()函数， lastinsertid()，以及user-defined functions(udf)会出现问题).
Row
不记录sql语句上下文相关信息，仅保存哪条记录被修改。
优点：binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题
缺点：所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容,比如一条update语句，修改多条记录，则binlog中每一条修改都会有记录，这样造成binlog日志量会很大。
mysq官方说复制时，使用基于语句复制的格式会导致复制出现问题，推荐使用ROW格式可以解决该问题，所以生产环境一般都是ROW格式。
Mixed
是以上两种格式的混合使用。
2.2、Binlog的参数介绍
binlog的常见参数：
log_bin
设置此参数表示启用binlog功能，并指定路径名称
binlog_format
设置此参数是指定二进制文件的格式，只能是ROW、STATEMENT、MIXED
expire_logs_days
设置此参数是指定二进制文件的保留天数，一般建议保留30天
binlog_do_db
此参数表示只记录指定数据库的二进制日志
binlog_ignore_db
此参数表示不记录指定的数据库的二进制日志
max_binlog_cache_size
此参数表示binlog使用的内存最大的尺寸。
当我们执行多语句事务的时候，max_binlog_cache_size如果不够大的话，系统可能会报出“Multi-statementtransactionrequiredmorethan'max_binlog_cache_size'bytesofstorage”的错误。
binlog_cache_size
此参数表示binlog使用的内存大小，可以通过状态变量binlog_cache_use和binlog_cache_disk_use来帮助测试。
binlog_cache_use
使用二进制日志缓存的事务数量
binlog_cache_disk_use
使用二进制日志缓存但超过binlog_cache_size值并使用临时文件来保存事务中的语句的事务数量
max_binlog_size
Binlog最大值是1GB，该设置并不能严格控制Binlog的大小，尤其是Binlog比较靠近最大值而又遇到一个比较大事务时，为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的所有SQL都记录进当前日志，直到事务结束。
sync_binlog
该参数直接影响mysql的性能和完整性
sync_binlog=0：
当事务提交后，Mysql仅仅是将binlog_cache中的数据写入Binlog文件，但不执行fsync之类的磁盘 同步指令通知文件系统将缓存刷新到磁盘，而让Filesystem自行决定什么时候来做同步，这个是性能最好的。
sync_binlog=n：
在进行n次事务提交以后，Mysql将执行一次fsync之类的磁盘同步指令，同志文件系统将Binlog文件缓存刷新到磁盘。
Mysql中默认的设置是sync_binlog=0，即不作任何强制性的磁盘刷新指令，这时性能是最好的，但风险也是最大的。一旦系统Crash，在文件系统缓存中的所有Binlog信息都会丢失。
2.3、Binlog的内容介绍
分别执行了以下三句sql：
insert into t_test_20190930a values (3,'c','33',2,now() ,'hz','fb');
insert into t_test_20190930a values (4,'d','33',1,now() ,'bj','jd');
insert into t_test_20190930a select * from t_test_20190930a;
解析binlog步骤：
使用
show master status
找到当前的binlog文件,找到当前binlog是mysql-bin.000010
。再执行
show binlog eventsin'mysql-bin.000010';
可以查看该文件的事件。然后使用
mysqlbinlog--base64-output=decode-rows-v-v mysql-bin.000010>10.log
，即可将二进制的binlog转换为可读的文本文件。（mysqlbinlog的binlog需要使用绝对路径）。
以下是执行 show binlog eventsin'mysql-bin.000010';
的结果集：
| Log_name | Pos | Event_type | Server_id | Endlogpos | Info |
|---|---|---|---|---|---|
| mysql-bin.000010 | 4 | Format_desc | 58592126 | 123 | Server ver: 5.7.23-log, Binlog ver: 4 |
| mysql-bin.000010 | 123 | Previous_gtids | 58592126 | 194 | 690b347b-d5ae-11e9-9bb9-fa1704044400:1991-2017 |
| mysql-bin.000010 | 194 | Gtid | 58592126 | 259 | SET @@SESSION.GTID_NEXT= '690b347b-d5ae-11e9-9bb9-fa1704044400:2018' |
| mysql-bin.000010 | 259 | Query | 58592126 | 339 | BEGIN |
| mysql-bin.000010 | 339 | Table_map | 58592126 | 413 | tableid: 108 (test.ttest_20190930a) |
| mysql-bin.000010 | 413 | Write_rows | 58592126 | 471 | tableid: 108 flags: STMTEND_F |
| mysql-bin.000010 | 471 | Xid | 58592126 | 502 | COMMIT * xid=11 */ |
| mysql-bin.000010 | 502 | Gtid | 58592126 | 567 | SET @@SESSION.GTID_NEXT= '690b347b-d5ae-11e9-9bb9-fa1704044400:2019' |
| mysql-bin.000010 | 567 | Query | 58592126 | 647 | BEGIN |
| mysql-bin.000010 | 647 | Table_map | 58592126 | 721 | tableid: 108 (test.ttest_20190930a) |
| mysql-bin.000010 | 721 | Write_rows | 58592126 | 779 | tableid: 108 flags: STMTEND_F |
| mysql-bin.000010 | 779 | Xid | 58592126 | 810 | COMMIT * xid=18 */ |
| mysql-bin.000010 | 810 | Gtid | 58592126 | 875 | SET @@SESSION.GTID_NEXT= '690b347b-d5ae-11e9-9bb9-fa1704044400:2020' |
| mysql-bin.000010 | 875 | Query | 58592126 | 955 | BEGIN |
| mysql-bin.000010 | 955 | Table_map | 58592126 | 1029 | tableid: 108 (test.ttest_20190930a) |
| mysql-bin.000010 | 1029 | Write_rows | 58592126 | 1144 | tableid: 108 flags: STMTEND_F |
| mysql-bin.000010 | 1144 | Xid | 58592126 | 1175 | COMMIT * xid=25 */ |
| mysql-bin.000010 | 1175 | Rotate | 58592126 | 1222 | mysql-bin.000011;pos=4 |
以下是 mysqlbinlog--base64-output=decode-rows-v-v mysql-bin.000010>10.log&cat10.log
，截取部分。（mysqlBinlog 详细使用方法可以通过，mysqlbinlog --help查看）
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#191001 21:42:25 server id 58592126 end_log_pos 123 CRC32 0x97afbe01 Start: binlog v 4, server v 5.7.23-log created 191001 21:42:25 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 123
#191001 21:42:25 server id 58592126 end_log_pos 194 CRC32 0x7aebe78a Previous-GTIDs
# 690b347b-d5ae-11e9-9bb9-fa1704044400:1991-2017
# at 194
#191001 21:42:56 server id 58592126 end_log_pos 259 CRC32 0x2cab144d GTID last_committed=0 sequence_number=1 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '690b347b-d5ae-11e9-9bb9-fa1704044400:2018'/*!*/;
# at 259
#191001 21:42:56 server id 58592126 end_log_pos 339 CRC32 0x3c544a30 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1569937376/*!*/;
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 utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=45/*!*/;
SET @@session.time_zone='SYSTEM'/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 339
#191001 21:42:56 server id 58592126 end_log_pos 413 CRC32 0x0ff32eea Table_map: `test`.`t_test_20190930a` mapped to number 108
# at 413
#191001 21:42:56 server id 58592126 end_log_pos 471 CRC32 0xd6d597f2 Write_rows: table id 108 flags: STMT_END_F
### INSERT INTO `test`.`t_test_20190930a`
### SET
### @1=3 * INT meta=0 nullable=1 is_null=0 */
### @2='c' * VARSTRING(40) meta=40 nullable=1 is_null=0 */
### @3=33 * INT meta=0 nullable=1 is_null=0 */
### @4='2' * VARSTRING(40) meta=40 nullable=1 is_null=0 */
### @5=1569937376 * TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### @6='hz' * VARSTRING(80) meta=80 nullable=1 is_null=0 */
### @7='fb' * VARSTRING(80) meta=80 nullable=1 is_null=0 */
# at 471
#191001 21:42:56 server id 58592126 end_log_pos 502 CRC32 0x4c9c6c82 Xid = 11
COMMIT/*!*/;
。。。。。。
开始事物的时间:
SET TIMESTAMP=1569937376/!/;
BEGIN
sql的event起点
at 191001 :为事件的起点，是以191001字节开始。
sql的event发生的时间点
at 191001 21:42:56是事件发生的时间。
serverId
server id 58592126为master 的serverId
sqlevent终点及花费时间，错误码
endlogpos 502：为事件的终点，是以502 字节结束。
execTime=0: 花费的时间
error_code=0：错误码
Xid:事件指示提交的XA事务
2.4、Binlog的写入机制
到此我们知道在MySQL二进制日志是server层的，主要用来做主从复制和数据恢复时使用的。而事务日志（redo log）是InnoDB存储引擎层的，用来保证事务安全的。现在我们来讨论一下MySQL中binlog和redo log的机制问题。
问题1：为什么MySQL有binlog，还有redo log？
这个是因为MySQL体系结构的原因，MySQL是多存储引擎的，不管使用那种存储引擎，都会有binlog，而只有InnoDB引擎才有redo log。简单的说，binlog是MySQL Server层的，redo log是InnoDB层的。
问题2：事务是如何提交的？事务提交先写binlog还是redo log？如何保证这两部分的日志做到顺序一致性？
MySQL为了保证master和slave的数据一致性，就必须保证binlog和InnoDB redo日志的一致性（因为备库通过二进制日志重放主库提交的事务，而主库binlog写入在commit之前，如果写完binlog主库crash，再次启动时会回滚事务。但此时从库已经执行，则会造成主备数据不一致）。所以在开启Binlog后，如何保证binlog和InnoDB redo日志的一致性呢？为此，MySQL引入二阶段提交（two phase commit or 2pc），MySQL内部会自动将普通事务当做一个XA事务（内部分布式事物）来处理：
– 自动为每个事务分配一个唯一的ID（XID）。
– COMMIT会被自动的分成Prepare和Commit两个阶段。
– Binlog会被当做事务协调者(Transaction Coordinator)，Binlog Event会被当做协调者日志。
想了解2PC，可以参考文档：https://en.wikipedia.org/wiki/Two-phasecommitprotocol
Binlog在2PC中充当了事务的协调者（Transaction Coordinator）。由Binlog来通知InnoDB引擎来执行prepare，commit或者rollback的步骤。事务提交的整个过程如下。
以上的图片中可以看到，事务的提交主要分为两个主要步骤：
1.准备阶段（Storage Engine（InnoDB） Transaction Prepare Phase）
此时SQL已经成功执行，并生成xid信息及redo和undo的内存日志。然后调用prepare方法完成第一阶段，papare方法实际上什么也没做，将事务状态设为TRX_PREPARED，并将redo log刷磁盘。
2.提交阶段(Storage Engine（InnoDB）Commit Phase)
记录Binlog日志。
如果事务涉及的所有存储引擎的prepare都执行成功，则调用TCLOGBINLOG::logxid方法将SQL语句写到binlog（write()将binary log内存日志数据写入文件系统缓存，fsync()将binary log文件系统缓存日志数据永久写入磁盘）。此时，事务已经铁定要提交了。否则，调用harollback_trans方法回滚事务，而SQL语句实际上也不会写到binlog。
告诉引擎做commit。
最后，调用引擎的commit完成事务的提交。会清除undo信息，刷redo日志，将事务设为TRXNOTSTARTED状态。
注意：记录Binlog是在InnoDB引擎Prepare（即Redo Log写入磁盘）之后，这点至关重要。
由上面的二阶段提交流程可以看出，一旦步骤2中的操作完成，就确保了事务的提交，即使在执行步骤3时数据库发送了宕机。此外需要注意的是，每个步骤都需要进行一次fsync操作才能保证上下两层数据的一致性。步骤2的fsync参数由syncbinlog=1控制，步骤3的fsync由参数innodbflushlogattrxcommit=1控制，俗称“双1”，是保证CrashSafe的根本。
参数说明如下：
innodb_flush_log_at_trx_commit（redo）
0：log buffer每秒一次地写入log file中，且进行flush操作。InnoDB日志刷新频率由控制 innodb_flush_log_at_timeout，它允许你将日志刷新频率设置为N秒（其中N是1 … 2700，默认值为1）。
1：每次事务提交时都会把log buffer的数据写入log file，并进行flush操作。
2：每次事务提交时MySQL都会把log buffer的数据写入log file，不进行flush操作。
sync_binlog （binlog）
0：刷新binlog_cache中的信息到磁盘由os决定。
N：每N次事务提交刷新binlog_cache中的信息到磁盘。
事务的两阶段提交协议保证了无论在任何情况下，事务要么同时存在于存储引擎和binlog中，要么两个里面都不存在，这就保证了主库与从库之间数据的一致性。如果数据库系统发生崩溃，当数据库系统重新启动时会进行崩溃恢复操作，存储引擎中处于prepare状态的事务会去查询该事务是否也同时存在于binlog中，如果存在就在存储引擎内部提交该事务（因为此时从库可能已经获取了对应的binlog内容），如果binlog中没有该事务，就回滚该事务。例如：当崩溃发生在第一步和第二步之间时，明显处于prepare状态的事务还没来得及写入到binlog中，所以该事务会在存储引擎内部进行回滚，这样该事务在存储引擎和binlog中都不会存在；当崩溃发生在第二步和第三步之间时，处于prepare状态的事务存在于binlog中，那么该事务会在存储引擎内部进行提交，这样该事务就同时存在于存储引擎和binlog中。
为了保证数据的安全性，以上列出的3个步骤都需要调用fsync将数据持久化到磁盘。由于在引擎内部prepare好的事务可以通过binlog恢复，所以通常情况下第三个fsync是可以省略的。
另外，MySQL内部两阶段提交需要开启 innodb_support_xa=true
，默认开启。这个参数就是支持分布式事务两段式事务提交。redo和binlog数据一致性就是靠这个两段式提交来完成的，如果关闭会造成事务数据的丢失
问题3：为了保障主从复制安全，故障恢复是如何做的？
开启Binary log的MySQL在crash recovery时：MySQL在prepare阶段会生成xid，然后会在commit阶段写入到binlog中。在进行恢复时事务要提交还是回滚，是由Binlog来决定的。
事务的Xidlogevent存在，就要提交。事务的Xidlogevent不存在，就要回滚。
恢复的过程非常简单：从Binlog中读出所有的Xidlogevent 告诉InnoDB提交这些XID的事务 InnoDB回滚其它的事务
总结一下，基本顶多会出现下面是几种情况：
当事务在prepare阶段crash，数据库recovery的时候该事务未写入Binary log并且存储引擎未提交，将该事务rollback。
当事务在binlog阶段crash，此时日志还没有成功写入到磁盘中，启动时会rollback此事务。
当事务在binlog日志已经fsync()到磁盘后crash，但是InnoDB没有来得及commit，此时MySQL数据库recovery的时候将会读出二进制日志的Xidlogevent，然后告诉InnoDB提交这些XID的事务，InnoDB提交完这些事务后会回滚其它的事务，使存储引擎和二进制日志始终保持一致。
总结起来说就是如果一个事务在prepare阶段中落盘成功，并在MySQL Server层中的binlog也写入成功，那这个事务必定commit成功。
问题4：为什么需要保证二进制日志的写入顺序和InnoDB层事务提交顺序一致性呢？
这是因为备份及恢复需要，例如通过xtrabackup或ibbackup这种物理备份工具进行备份时，并使用备份来建立复制。
上面提到单个事务的二阶段提交过程，能够保证存储引擎和binary log日志保持一致，但是在并发的情况下怎么保证InnoDB层事务日志和MySQL数据库二进制日志的提交的顺序一致？当多个事务并发提交的情况，如果Binary Log和存储引擎顺序不一致会造成什么影响？
再5.6以前是通过 prepare_commit_mutex
锁以串行的方式来保证MySQL数据库上层二进制日志和Innodb存储引擎层的事务提交顺序一致，所以导致组提交（group commit）特性无法生效。
prepare_commit_mutex
锁，将redo log和binlog刷盘串行化，串行化的目的也仅仅是为了保证redo log和Binlog一致，继而无法实现group commit，牺牲了性能。
后来大神们通过binlog组提交来解决这个问题，binlog组提交，引入队列机制保证innodb commit顺序与binlog落盘顺序一致，并将事务分组，组内的binlog刷盘动作交给一个事务进行，实现组提交目的。
binlog提交将提交分为了3个阶段，FLUSH阶段，SYNC阶段和COMMIT阶段。每个阶段都有一个队列，每个队列有一个mutex保护，约定进入队列第一个线程为leader，其他线程为follower，所有事情交由leader去做，leader做完所有动作后，通知follower刷盘结束。
三、总结
binlog的作用：用于复制和数据恢复。
binlog的格式：三种格式，ROW、STATEMENT、MIXED
binlog的常见参数。
binlog的记录内容。
binlog的写入过程。
四、附录
4.1、Binlog的删除
binlog的删除可以手工删除或自动删除。
自动删除通过
expire_logs_days
参数控制。
如下表示binlog保留10天：
[root@localhots ~]# cat /etc/my.cnf|grep expire_logs_days
expire_logs_days = 10
[root@localhots ~]#
手动删除
purge master logs before '2019-09-28 17:00:00';
删除指定日期以前的日志索引中binlog日志文件
purge master logs to 'binlog.000002';
删除指定日志文件的日志索引中binlog日志文件
reset master;
删除master的binlog，即手动删除所有的binlog日志
reset slave;
删除slave的中继日志
4.2、Binlog的其他操作
set sql_log_bin=1/0;
如果用户有super权限，可以启用或禁用当前会话的binlog记录
show master logs;
查看master的binlog日志列表
show binary logs;
查看master的binlog日志文件大小
show master status;
用于提供master二进制日志文件的状态信息
show slave hosts;
显示当前注册的slave的列表。不以--report-host=slave_name选项为开头的slave不会显示在本列表中
flush logs;
手动切换binlog
