前言

1、一条查询语句的执行过程：

select * from table_name where ID = 1

2、一条更新语句的执行过程：

update table_name set name = '小王子' where ID = 1 

 
   
   
 

  
    
    
  
执行器会先到引存储引擎中取ID=2这行数据，因ID是主键，可以直接根据索引找到这一行。如果ID=2这一行所在的数据也本来就在内存中，就直接返回给执行器；否则需要先从磁盘读入内存，然后再返回。
 
   
   
 

• 执行器拿到引擎返回的数据，把name设置为小王子，得到新的一行数据，再调用引擎接口写入这行新数据。

• 引擎将这行新数据更新到内存中，同时将这个更新操作记录到redo log里面，此时redo log处于prepare状态。然后可以告知执行器已经执行完毕了，随时可以提交事务。

• 执行器生成这个操作的binlog，并把binlog写入磁盘。

• 执行器调用引擎提供的事务接口，引擎把刚刚写入的redo log改成提交状态，更新完成。

最后三步就是两阶段提交，prepare -> commit，为什么会有两阶段提交呢？为了实现两份日志之间的逻辑一致。如果DBA承诺能实现数据库恢复到半个月以内任意一秒的状态，那么日志系统一定保存了最近半个月的binlog，同时系统也会定期整库备份。

如果不采用两阶段提交会有什么问题吗？

• 先写redo log后写binlog。假设redo log写完，binlog还没有写完时，MySQL进程异常重启了。由于redo log写完了，系统即使crash，仍然可以把数据恢复过来。但是由于binlog还没写完就crash了，这时候binlog里面没有这行记录这个语句。因此，如果后续需要使用这个binlog进行数据恢复或者主从同步时，就会导致数据不一致。

• 先写binlog后写redo log。如果binlog写完之后crash，由于redo log还没写，崩溃恢复以后这个事务无效，数据更新失败。但是在binlog中已经记录了把name更新成功。之后如果使用这个binlog，就会多一个事务出来，导致数据不一致。

一、binlog定义

1、定义

binlog记录了数据库/表的变更事件（DML、DDL）e.g: 操作数据库表的语句、更新表数据等；它还记录了可能进行的更改事件，e.g: 一条没有匹配到行的delete语句，基于row的日志记录除外；statement格式的binlog以事件的形式记录更新操作。另外，binlog还记录了每条更新数据的花费时间。

2、两个重要用途：

主从复制

我们典型的部署方案就是一主多从，即一台主服务器（Mater）和多台从服务器（Slave）。对于更改数据库状态的请求（DDL、DML等）由Master处理，而单纯的查询（如Select语句）请求由Slave来处理。binlog日志中记录了数据库中发生的各种改变，master节点会把binlog日志发送到slave节点，slave节点执行这些binlog日志中记录的数据库变化语句，主、从服务器实现数据的最终一致性。

diff: dump thread & io thread & sql thread

恢复数据

如果工作中我们无意把数据库的数据给删了，比如写delete语句忘记加where条件，那么整个表的数据就被删了。为了保证数据的安全性，我们需要定时执行mysqldump备份命令，一般是按照天维度的全量备份。那么如果在两次备份之间执行了delete操作，mysqldump只能恢复到上次备份的数据；这样就可以使用binlog恢复备份数据中不存在的增量数据。

3、binlog、redo log、undo log的区别

• redo log和undo log是innodb特有的，binlog是mysql server层的；

• redo log是循环写（默认大小48M），binlog是追加写（默认大小1G）；

• redo log主要是为了实现MySQL crash safe；undo log是主要是在事务中使用，回滚和MVCC；binlog主要是实现主从复制和恢复数据。

二、binlog日志格式

1、binlog日志有三种格式：

• 基于statement的日志记录：事件包含了数据更改（insert、update、delete）的SQL语句。

• 基于row的日志记录：事件描述了对单个行的更改。

• 混合日志(mixed)默认使用基于statement日志记录，但会根据需要自动切换到基于row的日志。

binlog包括两类文件：

• 二进制日志索引文件（.index）:记录所有的二进制文件。

• 二进制日志文件（.00000*）:记录所有的DDL和DML语句事件。

2、binlog配置参数：

MySQL配置文件my.cnf中提供了一些参数来进行binlog的设置：

设置此参数表示启用binlog功能，并制定二进制日志的存储目录log-bin=/home/mysql/binlog/
#mysql-bin.*日志文件最大字节（单位：字节）#设置最大100MBmax_binlog_size=104857600
#设置了只保留7天BINLOG（单位：天）expire_logs_days = 7
#binlog日志只记录指定库的更新#binlog-do-db=db_name
#binlog日志不记录指定库的更新#binlog-ignore-db=db_name
#写缓冲多少次，刷一次磁盘，默认0sync_binlog=0

max_binlog_size表示binlog文件的最大容量，其最大值和默认值都是1G，但是该设置并不能严格控制binlog的大小，例如binlog靠近最大值时而又遇到了一个较大的事务；那么为了保证事务的完整性不能做日志切换的动作，只能将该事务的所有SQL都记录到当前日志直到事务结束。

sync_binlog：这个参数决定了binlog日志的更新频率。默认为0，表示该操作由操作系统根据自身负载决定多久刷一次磁盘。值为1表示每一条事务提交都会立刻写盘。值为n表示n个事务提交才会写盘。写binlog的时机是事务SQL执行后，释放锁或者事务未commit之前；这样保证了binlog记录的操作时序和数据库实际的变更顺序一致。

三、binlog事件类型&写入时机

1、事件类型：

不同的操作会对应着不同的事件类型，且不同的binlog日志模式，同一种操作的事件类型也不相同。下面我们一起看下常见的事件类型。

• FORMAT_DESCRIPTION_EVENT：指定了MySQL的版本，binlog的版本，该binlog文件创建的时间。

• QUERY_EVENT时间通常在以下几种情况下使用：

• 事务开始时，执行得BEGIN操作；

• STATEMENT格式中的DML操作

• ROW格式中的DDL操作

• XID_EVENT在事务提交时，不管是STATEMENT还是ROW格式的binlog，都会在末尾添加一个XID_EVENT代表事务的结束。该事件记录了事务的ID，在MySQL进行崩溃恢复时，根据事务在binlog中提交情况决定是否提交存储引擎的事务。

• ROWS_EVENT：对于 ROW 格式的 binlog，所有的 DML 语句都是记录在 ROWS_EVENT。ROWS_EVENT分为三种：

• WRITE_ROWS_EVENT：对于 insert 操作，WRITE_ROWS_EVENT 包含了要插入的数据。

• UPDATE_ROWS_EVENT：对于 update 操作，UPDATE_ROWS_EVENT 不仅包含了修改后的数据，还包含了修改前的值。

• DELETE_ROWS_EVENT：对于 delete 操作，仅仅需要指定删除的主键。

2、binlog写入时机：

MySQL的日志都遵循WAL机制，也就是write ahead logging，先写日志再写数据。事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写入到binlog文件中。MySQL进程给binlog cache分配了一块内存，每个线程一个，binlog_cache_size用于控制单个线程内binlog cache所占内存的大小。如果超过了这个参数值，就需要暂存到磁盘。事务提交时，执行器就把binlog cache里的所有事务写入到binlog中，并清空binlog cache。sync_binlog的值决定刷磁盘的频率，如果将sync_binlog设置为N，对应的风险就是：如果主机发生异常重启，会丢失最近N个事务的binlog日志。

3、redo log写入时机(补充)：

redo log会存在三种状态：

• 存在redo log buffer中，物理上是在MySQL进程内存中；

• 写入磁盘（write），但是没有持久化（fsync），物理上存在文件系统FS page cache；

• 持久化磁盘，对应着hard disk。

日志写入到redo log buffer是很快的，write到page cache也差不多，但是持久化到磁盘就会慢很多。

为了控制redo log的写入策略，InnoDB提供了innodb_flush_log_at_trx_commit参数，它有三种取值：

• 设置为0表示每次事务提交都只是把redo log留在redo logbuffer中；

• 设置为1表示每次事务提交都将redo log直接持久化到磁盘；

• 设置为2表示每次事务提交时都只是把redo log写入到page cache。

InnoDB有一个后台线程（bg_thread），每个1s就会把redo log buffer中的日志，调用write写入到文件系统的page cache，然后调用fsync持久化到磁盘。

注意，事务执行过程中redo log也是写在redo log buffer中，这些redo log也会被后台线程一起持久化到磁盘。也就是说，一个没有提交的事务redo log也有可能已经被持久化磁盘。

实际上，除了后台线程每秒一次的轮询操作外，还有两种场景会让一个没有提交事务的redo log写入磁盘。

1. 一种是，redo log buffer占用的空间即将达到innodb_log_buffer_size一半的时候，后台线程就会主动写盘。由于这个事务并没有提交，所以这个写盘动作只是write，而没有调用fsync，也就是留在了文件系统的page cache。

2. 另一种是，并行事务提交的时候，顺带将这个事务redo log buffer持久到了磁盘。假设一个事务A执行到了一半，已经写了一些redo log到buffer中，这时候有另一个线程的事务B提交，如果事务B把redo log buffer持久化到磁盘。这时候就会把事务A在redo log buffer里的日志一起持久化到磁盘。

四、binlog应用场景

1、数据恢复

误操作删库只能跑路？？不至于，不至于......

首先找到最近一次的备份数据，比如全量备份的频率是1次/天，那我们就找到昨天的备份数据恢复历史数据，然后用binlog恢复到最新的数据。

用 binlog 来恢复数据的标准做法是，用 mysqlbinlog 工具解析出来，然后把解析结果整个发给 MySQL 执行。类似下面的命令：

mysqlbinlog master.000001 --start-position=2738 --stop-position=2973 | mysql -h127.0.0.1 -P13000 -u$user -p$pwd;

2、读写分离

• MySQL(A)是主库master，所有的更新操作都在master上进行；同时会有多个slave，每个slave都连接到master上，获取binlog在本地回放，实现数据复制。

• 所以，在业务层面需要对执行的sql进行判断。所有的更新操作都通过master(insert、update、delete等)，而查询操作(Select等)都在slave上进行。

3、数据最终一致性

MySQL主备一致：

流程：主库接收到客户端的更新请求后，执行内部事务的更新逻辑，同时写binlog。备库B跟主库A之间维持一个长链接，主备切换同步的过程是这样的：

• 在备库 B 上通过 change master 命令，设置主库 A 的 IP、端口、用户名、密码，以及要从哪个位置开始请求 binlog，这个位置包含文件名和日志偏移量；

• 在备库 B 上执行 start slave 命令，这时候备库会启动两个线程，就是图中的 io_thread 和 sql_thread。其中 io_thread 负责与主库建立连接；

• 主库 A 校验完用户名、密码后，开始按照备库 B 传过来的位置，从本地读取 binlog，发给 B。

• 备库 B 拿到 binlog 后，写到本地文件，称为中转日志（relay log）；

• sql_thread 读取中转日志，解析出日志里的命令，并执行。

消费binlog：

在日常开发中，有时会遇到这样的需求，当数据库操作成功后还有一些其他的操作：更新缓存、发送MQ消息等。

五、总结

• 一条查询语句和更新语句的执行流程，讨论了redo log两阶段提交的必要性；

• binlog的定义：记录了数据库/表的变更事件（DML、DDL）；

• binlog的日志格式、配置参数、事件类型和写入时机；

• binlog常见的应用场景：数据恢复、主备一致等。