系列文档参考 MYSQL系列-整体架构介绍

基本概念

ACID 原则

ACID，即 Atomicity（原子性）、Consistency（一致性）、Isolation（隔离性）、Durability（持久性） 四种特性的缩写。

ACID 也是一种比较出名的描述一致性的原则，通常出现在分布式数据库等基于事务过程的系统中。

具体来说，ACID 原则描述了分布式数据库需要满足的一致性需求，同时允许付出可用性的代价。

Atomicity：每次事务是原子的，事务包含的所有操作要么全部成功，要么全部不执行。一旦有操作失败，则需要回退状态到执行事务之前；
Consistency：数据库的状态在事务执行前后的状态是一致的和完整的，无中间状态。即只能处于成功事务提交后的状态；
Isolation：各种事务可以并发执行，但彼此之间互相不影响。按照标准 SQL 规范，从弱到强可以分为读未提交、读提交、可重复读和串行化四种隔离等级；
Durability：状态的改变是持久的，不会失效。一旦某个事务提交，则它造成的状态变更就是永久性的。

延伸-BASE与CAP

BASE原则

与 ACID 相对的一个原则是 eBay 技术专家 Dan Pritchett 提出的 BASE（Basic Availability，Soft-state，Eventual Consistency）原则。BASE 原则面向大型高可用分布式系统，主张牺牲掉对强一致性的追求，而实现最终一致性，来换取一定的可用性。

基本可用（Basically Available）：系统保证在出现故障或者异常情况时，仍然能够保证基本的可用性，即系统能够正常响应用户请求，但是可能会出现一些数据的不一致或者错误。
软状态（Soft State）：系统中的数据状态不需要是强一致性的，即允许数据在一段时间内处于中间状态，而不是必须保证数据在任何时间点都是完全一致的。这样可以提高系统的可扩展性和容错性。
最终一致性（Eventual Consistency）：系统保证最终会达到一致性状态，但是在数据更新后，不同节点之间可能存在一段时间的数据不一致。这种不一致是暂时的，随着时间的推移会逐渐被修复，直到所有节点都达到一致性状态。

BASE的设计理念是为了满足大规模分布式系统的需求，它与ACID的设计理念不同，不要求在所有情况下都严格保证数据的一致性，而是允许在某些情况下牺牲一部分的一致性，以换取更高的可用性和可扩展性。但是，在实际的应用中，需要根据具体的业务需求和系统特点，选择适合的数据管理系统设计理念，综合考虑数据的一致性、可用性和可扩展性等方面的因素。

CAP

CAP是指分布式系统中三个基本特性的缩写：

一致性（Consistency）：所有节点在同一时间看到的数据是一致的，即所有节点都具有相同的数据副本。在分布式系统中，这意味着在进行更新操作之后，所有节点必须最终达到一致的状态。
可用性（Availability）：系统能够在任何时间点正常响应用户请求，即系统的服务不会因为某些异常情况而停止响应。
分区容错性（Partition tolerance）：分布式系统能够在网络分区或者节点故障的情况下继续运行。这意味着系统在遇到网络分区或者节点故障时，仍然能够保证数据的一致性和可用性。

CAP理论指出，在一个分布式系统中，最多只能同时满足其中的两个特性，而不能同时满足所有三个特性。因此，当系统遇到网络分区或者节点故障时，分布式系统需要做出权衡，选择满足一致性和可用性中的哪一个特性，从而保证系统的正确性和可靠性。如果选择了分区容错性，那么系统可能会出现数据不一致的情况；如果选择了一致性和可用性，那么系统可能会出现停机和延迟响应等问题。

在实际的分布式系统中，需要根据具体的业务需求和系统特点，选择适合的CAP特性，综合考虑数据的一致性、可用性和容错性等方面的因素。

数据库并发事务存在问题

脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read） 都是数据库中的并发控制问题，主要影响事务的隔离性。

脏读（Dirty read）：指一个事务读取了另一个事务还未提交的未经提交的数据。如果另一个事务回滚，那么第一个事务读取的数据就是无效的，这可能会导致数据的不一致性。
不可重复读（Non-repeatable read）：指一个事务在读取同一行记录时，不同时间内读取到的数据不一致。这是因为在两次读取之间，另一个事务修改或删除了该行记录。
幻读（Phantom read）：指一个事务在读取一组记录时，第二次读取时发现多了或少了一些记录。这是因为在两次读取之间，另一个事务插入了一些记录或者删除了一些记录。

脏读、不可重复读、幻读都是由于并发事务之间的竞争而导致的问题，可以通过加锁或者使用更高级别的隔离级别来解决。

数据库隔离级别

SQL 标准的事务隔离级别包括：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）。

读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。
读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。
可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

隔离级别解决并发问题

数据库默认隔离级别

MYSQL

默认隔离级别是可重复读

mysql> show variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.01 sec)

mysql> show global variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)

延伸：SESSION隔离级别是指在一个 MySQL 会话中，可以通过设置隔离级别来控制该会话中的事务隔离级别。在同一个 MySQL 实例中，不同会话可以设置不同的隔离级别。可以使用以下命令来设置 SESSION 隔离级别：

SET SESSION TRANSACTION ISOLATION LEVEL <isolation_level>;

其中，isolation_level 表示要设置的隔离级别，可以是 READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 或 SERIALIZABLE。

ORACLE

Oracle默认的隔离级别是读已提交（Read Committed）

一条查询SQL执行过程

SQL语句执行整体流程如下，在前面的整体架构图中也介绍过

客户端

客户端主要是通过MYSQL驱动来和数据库进行通信，MySQL 驱动是一种软件组件，用于在应用程序和 MySQL 数据库之间建立连接，以便应用程序可以访问和操作 MySQL 数据库。MySQL 驱动通常有以下几种类型：
JDBC驱动（Java Database Connectivity）、ODBC驱动（Open Database Connectivity）、Connector/C 驱动等客户端需要频繁使用数据库连接，采用池化技术-数据库连接池来进行管理：维护一定的连接数，方便系统获取连接，使用就去池子中获取，用完放回去就可以了，我们不需要关心连接的创建与销毁，也不需要关心线程池是怎么去维护这些连接的。

常见的数据库连接池有 Druid、C3P0、DBCP，后面有一节专门讲述

连接器

连接器负责跟客户端建立连接、获取权限、维持和管理连接。
命令如下：

/home/mysql/mysql3306/bin/mysql -uroot -p -S /home/mysql/mysql3306/mysqld.sock

连接命令中的 mysql 是客户端工具，用来跟服务端建立连接。在完成经典的 TCP 握手后，连接器就要开始认证你的身份，这个时候用的就是你输入的用户名和密码。

如果用户名或密码不对，你就会收到一个"Access denied for user"的错误，然后客户端程序结束执行。
如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。

划重点：一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。

show processlist 可以查看当前连接

mysql> show processlist;
+----+------+---------------------------+------+---------+------+----------+------------------+
| Id | User | Host                      | db   | Command | Time | State    | Info             |
+----+------+---------------------------+------+---------+------+----------+------------------+
|  2 | root | localhost                 | NULL | Query   |    0 | starting | show processlist |
|  8 | root | TOBY-HYW.mshome.net:24708 | NULL | Sleep   |   27 |          | NULL             |
|  9 | root | TOBY-HYW.mshome.net:24709 | NULL | Sleep   |   27 |          | NULL             |
+----+------+---------------------------+------+---------+------+----------+------------------+
3 rows in set (0.00 sec)

客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值是 8 小时。

mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.01 sec)

建立连接的过程通常是比较复杂的，所以我建议你在使用中要尽量减少建立连接的动作，也就是尽量使用长连接。 MySQL 占用内存涨得特别快，这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。解决此问题：

定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
如果你用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

查询缓存

命中则直接结束，一般不使用。查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。query_cache_type(OFF|ON|DEMAND 按需，使用select SQL_CACHE * from T where ID=10显示指定)控制默认是关闭的

mysql> show variables like 'query_cache_type';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | OFF   |
+------------------+-------+

MySQL 8.0 版本直接将查询缓存的整块功能删掉了，也就是说 8.0 开始彻底没有这个功能了。

分析器

词法分析、语法分析如果你的语句不对，就会收到“You have an error in your SQL syntax”的错误提醒

优化器

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

执行器

MySQL 通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。

开始执行的时候，要先判断一下你对这个表有没有执行查询的权限，如果没有，就会返回没有权限的错误，如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

引擎层

提供接口给执行层使用，分为多个执行引擎，后面单独讲述

问题解答

问题1

如果表 T 中没有字段 k，而你执行了这个语句 select * from T where k=1, 那肯定是会报“不存在这个列”的错误： “Unknown column ‘k’ in ‘where clause’”。你觉得这个错误是在我们上面提到的哪个阶段报出来的呢？

答案：分析器

一条更新SQL执行过程

更新SQL和查询SQL在server层是一样的（在查询缓存时将缓存失效调），在引擎层不一样，下面我们基于INNODB引擎说明更新流程，主要涉及两个重要的日志模块和一些Buffer

基于INNODB概念说明

更新涉及到INNODB中一些概念，redo log、undo log、Buffer Pool、Redo log Buffer、bin log（Server层）

redo log（重做日志）

Redo log（重做日志）是InnoDB存储引擎中实现事务持久性（Durability）的机制之一。它记录了InnoDB引擎执行的所有修改数据操作，例如插入、更新和删除等操作。

当InnoDB执行一个事务提交操作时，它会把该事务所做的修改记录到Redo log中，然后再将这些修改写入磁盘（这就是MYSQL的WAL技术，Write-Ahead Logging）。这样，在发生意外故障导致MySQL崩溃时，可以通过Redo log恢复数据的一致性。

具体来说，当MySQL重启时，InnoDB存储引擎会首先检查Redo log，找到最后一个已经提交的事务，并且把该事务对应的修改操作应用到磁盘上，使得数据恢复到最新的一致状态。这个过程被称为Redo恢复。同时这种能力也称为crash-safe。

需要注意的是，Redo log只是保证了事务的持久性，并不保证事务的原子性和一致性。如果MySQL发生意外故障，恢复过程可能会导致数据的丢失或者不一致。因此，在应用程序设计和系统运维中，需要综合考虑事务隔离级别、数据库备份和灾备等方面的因素，以确保数据的安全性和可靠性。

redo log是一组文件循环写入的，如下图，write pos 是当前记录的位置，一边写一边后移。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。write pos 和 checkpoint 之间的是空着的部分，可以用来记录新的操作。

redo log相关的参数配置如下：


mysql> show variables like 'innodb_log_file%';
+---------------------------+----------+
| Variable_name             | Value    |
+---------------------------+----------+
| innodb_log_file_size      | 50331648 |
| innodb_log_files_in_group | 2        |
+---------------------------+----------+
2 rows in set (0.00 sec)

mysql> show variables like 'innodb_flush_log_at_trx_commit%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.00 sec)

//my.conf中配置redo log文件路径地址
[mysqld]
innodb_log_group_home_dir = /data/mysql/logs/

innodb_log_file_size:定义Redo log文件的大小，默认值为48M（50331648=48*1024*1024）。注意，修改这个参数需要同时修改innodb_log_files_in_group参数
innodb_log_files_in_group：定义Redo log文件组中的文件数，默认值为2。通常建议将文件数设置为2或者更多，这样可以避免当一个文件正在被刷写到磁盘时，另一个文件已经被填满导致Redo log缓冲区满而发生异常。
innodb_flush_log_at_trx_commit：定义Redo log的刷写策略，默认值为1。如果设置为1，表示每次事务提交时都会将Redo log刷写到磁盘，这样可以保证事务的持久性。如果设置为0，表示每次事务提交时只将Redo log缓存到系统缓存中，而不会立即刷写到磁盘，这样可以提高性能，但是可能会导致数据的丢失。如果设置为2，表示每次事务提交时将Redo log缓存到系统缓存中，并且每秒钟将缓存中的Redo log刷写到磁盘。
innodb_log_group_home_dir:Redo log的存储路径可以通过配置文件（my.cnf或者my.ini）中的innodb_log_group_home_dir参数来进行配置。

Redo log Buffer（重做日志缓冲区）

Redo log buffer（重做日志缓冲区）是InnoDB存储引擎中的一个内存区域，用于暂存待写入Redo log文件的修改操作。当用户执行插入，更新或删除操作时，InnoDB会先将修改操作写入Redo log buffer中，然后再异步将这些修改操作刷写到硬盘上的Redo log文件中。其何时刷入磁盘受innodb_flush_log_at_trx_commit参数影响

mysql> show variables like 'innodb_log_buffer_size%';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| innodb_log_buffer_size | 16777216 |
+------------------------+----------+
1 row in set (0.00 sec)

innodb_log_buffer_size:定义Redo log缓冲区的大小，默认值为16M。这个缓冲区用于暂存待刷写到Redo log文件的修改操作，缓冲区越大，可以减少刷写操作的次数，提高性能。

延伸：Redo log Buffer何时写入redo log受什么影响

事务提交：当InnoDB存储引擎接收到一个事务提交的命令时，会将该事务所做的修改操作从Redo log buffer中刷写到Redo log文件中，以保证事务的持久性。如果innodb_flush_log_at_trx_commit参数的值设置为1（默认值），则每个事务提交时都会将Redo log buffer中的内容刷写到Redo log文件中。
Checkpoint：InnoDB存储引擎会定期执行Checkpoint操作，将内存中的数据刷写到磁盘中。在Checkpoint操作过程中，如果Redo log buffer中存在未写入Redo log文件的数据，则会将这些数据一并刷写到Redo log文件中。
Redo log buffer满：当Redo log buffer满时，InnoDB存储引擎会将其中的一部分数据刷写到Redo log文件中，以便继续写入新的修改操作。这个过程被称为Redo log buffer的轮换。Redo log buffer的大小可以通过innodb_log_buffer_size参数进行配置。

延伸：何为InnoDB存储引擎Checkpoint操作

InnoDB存储引擎会定期执行Checkpoint操作，以将内存中的数据刷写到磁盘中。Checkpoint操作是一种后台进程，它的主要作用是将修改过的数据从内存中刷写到磁盘上的数据文件中，以保证数据的持久性。

Checkpoint操作的执行频率由InnoDB存储引擎自动调整，可以通过配置参数innodb_max_dirty_pages_pct来控制。这个参数定义了InnoDB存储引擎内存中脏页（即已经被修改但还未写入磁盘的数据页）的最大比例。当脏页占用的比例达到innodb_max_dirty_pages_pct指定的阈值时，InnoDB存储引擎会触发Checkpoint操作，将脏页中的数据刷写到磁盘。


mysql> show variables like 'innodb_max_dirty_pages_pct%';
+--------------------------------+-----------+
| Variable_name                  | Value     |
+--------------------------------+-----------+
| innodb_max_dirty_pages_pct     | 75.000000 |
| innodb_max_dirty_pages_pct_lwm | 0.000000  |
+--------------------------------+-----------+
2 rows in set (0.00 sec)

undo log（撤销日志）

Undo log（撤销日志）是InnoDB存储引擎中的一种日志，用于实现事务的回滚和MVCC（多版本并发控制）机制。当一个事务对数据库进行修改时，InnoDB会记录下原始数据的备份，以便在事务回滚时可以将数据恢复到修改前的状态。这些备份数据就保存在Undo log中。

Undo log实际上是一个逻辑日志，它记录了对数据库进行修改的操作，包括插入、更新和删除等。

在MVCC机制中，Undo log还用于实现多版本数据的控制。当一个事务对某个数据进行修改时，InnoDB会先将原始数据的备份保存到Undo log中，并在数据行上创建一个新的版本号。这样，在其他事务读取该数据行时，就可以根据版本号判断该事务是否可以看到该数据行。如果该事务的版本号早于当前数据行的版本号，那么它就看不到该数据行，从而实现了对数据的并发控制。

mysql> show variables like '%undo%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| innodb_max_undo_log_size | 1073741824 |
| innodb_undo_directory    | ./         |
| innodb_undo_log_truncate | OFF        |
| innodb_undo_logs         | 128        |
| innodb_undo_tablespaces  | 0          |
+--------------------------+------------+
5 rows in set (0.00 sec)

innodb_max_undo_log_size:指定单个Undo log文件的最大大小。
innodb_undo_directory:指定Undo表空间所在的目录。默认情况下，Undo表空间会存储在数据目录下的ibdata文件中，但是也可以将它存储在独立的文件中以便更好的管理和维护。
innodb_undo_log_truncate:是一个用于控制Undo log文件截断的参数。当一个事务提交时，InnoDB存储引擎会将该事务所做的修改操作写入Undo log文件中，以便在事务回滚时可以恢复数据。如果Undo log文件已经满了，InnoDB会自动切换到下一个Undo log文件。此时，如果当前Undo log文件中的Undo log已经不再需要，InnoDB可以通过截断该文件来释放磁盘空间。 0：表示不启用Undo log文件截断。当Undo log文件已满时，InnoDB存储引擎会停止写入新的Undo log，直到当前Undo log文件被清空或者被删除。1：表示启用Undo log文件截断。- 2：表示启用Undo log文件截断，并将截断时删除的Undo log标记为已经使用过。这样，在下一次Checkpoint操作时，InnoDB存储引擎会将标记为已经使用过的Undo log从Undo表空间中删除，从而进一步释放磁盘空间。
innodb_undo_logs：自定义多少个rollback segment，默认值为128。
innodb_undo_tablespaces：指定undo log平均分配到多少个表空间文件中，默认值为0，即全部写入一个文件中。不建议修改为非0值，我们直接使用默认值即可。

回滚日志记录文件信息

取自 MySQL回滚日志（undo log）总结

Buffer Pool

Buffer Pool是InnoDB存储引擎中的一个内存缓存区，用于存储数据库表和索引数据的副本。当InnoDB需要读取表或者索引数据时，会首先在Buffer Pool中查找，如果找到了相应的数据，就可以直接将数据返回给查询请求；如果没有找到相应的数据，则需要从磁盘中读取数据，并将其加载到Buffer Pool中，以便下次快速访问。

mysql> show variables like '%buffer_pool%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 134217728      |
+-------------------------------------+----------------+
10 rows in set (0.00 sec)

innodb_buffer_pool_chunk_size：用于配置Buffer Pool的chunk（内存块）大小，以支持更好的内存管理和分配。默认值为128MB，可以根据实际情况进行调整。
innodb_buffer_pool_dump_at_shutdown：用于配置是否在MySQL实例关闭时将Buffer Pool中的数据转储到磁盘。默认值为ON，表示开启该功能；可以设置为OFF，表示关闭该功能。
innodb_buffer_pool_dump_now：用于触发将Buffer Pool中的数据转储到磁盘，以支持数据备份和恢复。默认值为OFF，可以设置为ON，表示立即转储数据到磁盘。
innodb_buffer_pool_dump_pct：用于配置Buffer Pool中数据转储到磁盘的比例阈值。默认值为25，表示当Buffer Pool中脏数据占用的比例超过25%时，将数据转储到磁盘。
innodb_buffer_pool_filename：用于配置Buffer Pool数据文件的路径和文件名。默认值为ib_buffer_pool，保存在MySQL数据目录下。
innodb_buffer_pool_instances：用于配置Buffer Pool的分片数量，以支持更好的多线程并发访问。默认值为8，可以根据实际情况进行调整。
innodb_buffer_pool_load_abort：用于控制在加载Buffer Pool数据时是否允许中断操作。默认值为OFF，表示不允许中断操作；可以设置为ON，表示允许中断操作。
innodb_buffer_pool_load_at_startup：用于配置在MySQL实例启动时是否自动加载Buffer Pool数据。默认值为ON，表示开启该功能；可以设置为OFF，表示关闭该功能。
innodb_buffer_pool_load_now：用于触发立即加载Buffer Pool数据到内存。默认值为OFF，可以设置为ON，表示立即加载数据到内存。
innodb_buffer_pool_size：用于配置Buffer Pool的大小，单位为字节。默认值为128MB，可以根据实际情况进行调整。

buffer pool内面内容如下

取自 InnoDB的Buffer Pool

延伸：Buffer Pool的Change buffer

对MySQL的增删改操作的时候,Buffer Pool会帮我们做缓存操作,减少磁盘的读写操作,提高数据库性能和并发能力,而这个能力是Buffer Pool的写缓存(change buffer)提供的,也就是说Buffer Pool 里面还包含了一个写缓存(Change buffer).

写缓存只对非唯一普通索引页有效,即在对非唯一普通索引页更新操作的时候,如果缓冲池中存在数据(数据页),则直接修改缓冲池中的数据页,反之从磁盘把该数据页写入到写缓存中;当查询该数据页的时候,如果缓冲池中存在数据(数据页),则直接返回给执行器,反之,需要进行一次磁盘读操作,把在磁盘读取到数据页载入到缓冲池中;查询数据的时候,需要查看该数据页是否在写缓存有存在记录,如果有,则需要跟写缓存进行marge操作后返回给执行器.

bin log（归档日志）

Bin log（二进制日志）是MySQL数据库中的一种日志文件，用于记录数据库的所有修改操作，包括插入、更新和删除等操作。Bin log记录的是对数据进行修改的SQL语句，而不是修改后的数据本身。

Bin log主要用于实现MySQL的数据备份、恢复和数据同步等功能。通过将Bin log文件传输到其他MySQL实例，可以将数据同步到其他服务器上，实现数据的高可用和负载均衡。同时，Bin log也可以用于数据恢复，通过将Bin log文件中的SQL语句逆向执行，可以将数据库恢复到指定的时间点。


mysql> show variables like '%binlog%';
+--------------------------------------------+----------------------+
| Variable_name                              | Value                |
+--------------------------------------------+----------------------+
| binlog_cache_size                          | 32768                |
| binlog_checksum                            | CRC32                |
| binlog_direct_non_transactional_updates    | OFF                  |
| binlog_error_action                        | ABORT_SERVER         |
| binlog_format                              | ROW                  |
| binlog_group_commit_sync_delay             | 0                    |
| binlog_group_commit_sync_no_delay_count    | 0                    |
| binlog_gtid_simple_recovery                | ON                   |
| binlog_max_flush_queue_time                | 0                    |
| binlog_order_commits                       | ON                   |
| binlog_row_image                           | FULL                 |
| binlog_rows_query_log_events               | OFF                  |
| binlog_stmt_cache_size                     | 32768                |
| binlog_transaction_dependency_history_size | 25000                |
| binlog_transaction_dependency_tracking     | COMMIT_ORDER         |
| innodb_api_enable_binlog                   | OFF                  |
| innodb_locks_unsafe_for_binlog             | OFF                  |
| log_statements_unsafe_for_binlog           | ON                   |
| max_binlog_cache_size                      | 18446744073709547520 |
| max_binlog_size                            | 1073741824           |
| max_binlog_stmt_cache_size                 | 18446744073709547520 |
| sync_binlog                                | 1                    |
+--------------------------------------------+----------------------+
22 rows in set (0.00 sec)

mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+
6 rows in set (0.00 sec)

log_bin：用于控制是否启用Bin log。默认值为OFF，可以设置为ON，表示启用Bin log功能。
binlog_format：用于配置Bin log中记录的SQL语句的格式。支持三种格式：
- 默认值为STATEMENT，表示记录SQL语句的文本格式；
- ROW，表示记录SQL语句影响的行的详细信息；
- MIXED，表示根据实际情况动态选择STATEMENT或ROW格式。
binlog_row_image：用于控制Bin log中记录的ROW格式的SQL语句的内容。支持三种格式：FULL、MINIMAL和NOBLOB。
- 默认值为FULL，表示记录完整的行数据
- MINIMAL，表示只记录修改的列和主键
- NOBLOB，表示不记录BLOB和TEXT类型的列
log_bin_basename：用于配置Bin log文件的基础名称。默认值为hostname-bin，其中hostname为MySQL服务器的主机名。Bin log文件的完整名称由基础名称和序号组成，例如hostname-bin.000001。
max_binlog_size：用于控制单个Bin log文件的最大大小，单位为字节。默认值为1073741824（1GB），可以根据实际情况进行调整。
binlog_cache_size：用于控制Bin log写入缓存的大小，单位为字节。默认值为32768，可以根据实际情况进行调整。
max_binlog_files：用于控制Bin log文件的最大数量。默认值为1000，可以根据实际情况进行调整。
sync_binlog：用于控制Bin log的同步写入行为。取值如下：
- 0：mySQL不会等待操作系统将Bin log缓存数据刷新到磁盘。
- 1：MySQL会等待操作系统将Bin log缓存数据刷新到磁盘后才返回。这种情况下，数据的持久性和一致性得到了保障，但是会降低系统的性能。
- N（N为正整数）时，MySQL会在每写入N次Bin log数据后等待操作系统将Bin log缓存数据刷新到磁盘。这种情况下，可以在一定程度上平衡数据的持久性和一致性与系统的性能。

延伸：MYSQL双1配置

为保证数据库可靠性，进行双1配置将innodb_flush_log_at_trx_commit和sync_binlog都设置为1

SQL更新过程

具体步骤如下：

首先执行器根据 MySQL 的执行计划来查询数据，先是从缓存池中查询数据，如果没有就会去数据库中查询，如果查询到了就将其放到缓存池中
在数据被缓存到缓存池的同时，会写入 undo log 日志文件
更新的动作是在 BufferPool 中完成的，同时会将更新后的数据添加到 redo log buffer 中
完成以后就可以提交事务，在提交的同时会做以下三件事
- 将redo log buffer中的数据刷入到 redo log 文件中
- 将本次操作记录写入到 bin log文件中
- 将 bin log 文件名字和更新内容在 bin log 中的位置记录到redo log中，同时在 redo log 最后添加 commit 标记

延伸：MYSQL两阶段提交协议

2PC，它是一种保证分布式事务数据一致性的协议，它中文名叫两阶段提交，它将分布式事务的提交拆分成了2个阶段，分别是Prepare和Commit/Rollback。

Prepare阶段，将Redo Log写入文件，并刷入磁盘，记录上内部XA事务的ID，同时将Redo Log状态设置为Prepare。Redo Log写入成功后，再将Binlog同样刷入磁盘，记录XA事务ID。

Commit阶段，向磁盘中的Redo Log写入Commit标识，表示事务提交。然后执行器调用存储引擎的接口提交事务。这就是整个过程。

2PC这套机制真的能保证数据一致性吗？

假设Redo Log刷入成功了，但是还没来得及刷入Binlog MySQL就挂了。此时重启之后会发现Redo Log并没有Commit标识，此时根据记录的XA事务找到这个事务，进行回滚。

如果Redo Log刷入成功，而且Binlog也刷入成功了，但是还没有来得及将Redo Log从Prepare改成Commit MySQL就挂了，此时重启会发现虽然Redo Log没有Commit标识，但是通过XID查询到的Binlog却已经成功刷入磁盘了。

参考
1.一条SQL语句是如何执行的
2.MySQL - 一条 SQL 的执行过程详解
3.基于Redo Log和Undo Log的MySQL崩溃恢复流程

MYSQL系列-基本概念和SQL执行过程