-
五万字多图解 MySQL 事务核心知识点
前言
本文将会对 MySQL 大部分核心的知识点进行详解，主要涉及到多种表空间、锁、隔离级别、redo log、binlog、undo log、双写（doublewrite）、两阶段提交、MVCC、B+Tree 等模块。
本文将在前面的章节讲解一些基础的知识点，在本文的后面将是文章的核心章节 事务 相关知识点。
一、表空间
1.1 共享表空间
共享表空间也叫系统表空间，用于存储数据字典、双写缓冲区（doublewrite buffer）、更改缓冲区（hange buffer）和重做日志（undo log）等。
可通过 SHOW VARIABLES LIKE ‘innodb_data_file_path’ 查看，若不设定则 Innodb 在初始化时候会创建一个稍大于12MB的单个自动扩展数据文件，并命名为 ibdata1。
mysql> SHOW VARIABLES LIKE 'innodb_data_file_path'; +-----------------------+------------------------+ | Variable_name | Value | +-----------------------+------------------------+ | innodb_data_file_path | ibdata1:12M:autoextend | +-----------------------+------------------------+- 1
- 2
- 3
- 4
- 5
- 6
共享表空间默认存放在 datadir 指定的目录下，可通过 innodb_data_home_dir 指定。如有下设置：
innodb_data_home_dir = /myibdata/ innodb_data_file_path=ibdata1:50M:autoextend- 1
- 2
共享表空间完整的命名规范必须包括文件名、文件大小、autoextend 属性和 max 属性，且最后只能设置为： autoextend 或者 max。
其中文件大小以千字节（K）、兆字节(M)或千兆字节(G)为单位，且若以K为单位必须设定为1024的整数倍，否则将自动四舍五入到最接近的M值。
file_name:file_size[:autoextend[:max:max_file_size]] e.g 1: # 指定autoextend属性后，数据文件的大小会随着空间的需要自动增加 64MB. # 大小可由参数 innodb_autoextend_increment 控制。 innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend e.g 2: # 若要指定自动扩展数据文件的最大大小，在 autoextend 属性后面增加 max 属性。 # 如下代表允许ibdata1增长到 500MB 大小。 innodb_data_file_path=ibdata1:12M:autoextend:max:500M- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
若不开启独立表空间，那所有表的数据、索引等数据都存放在共享表空间中。这样做有好处也有缺点。
优点：
- 单表空间不受操作系统单文件大小限制，因为可以将一个表数据分布到不同的 ibddata 文件中。
- 方便管理，文件相对不多不会存在过多的句柄。
缺点:
- 磁盘空间使用效率低，如删数据后可能出现空间不能复用情况导致碎片化严重。
- 多表公用一个文件时并发时可能出现IO 瓶颈，所以需要频繁写入的场景并不适合共享表空间。
1.2 独立表空间
独立表空间是将InnoDB表及其索引都存储在一个单独的 you_table_name.ibd 数据文件中，并使用 you_table_name.frm 存放描述文件。
每个.ibd 文件代表一个单独的表空间。这个特性是由参数：innodb_file_per_table 配置选项控制的，在MySQL 5.6.6及更高版本中默认启用。
innodb_file_per_table=ON- 1
独立表空间默认存放在 innodb_data_home_dir 下以具体数据库名为目录名的目录下，如在 Test 库下创建了 test 表：
drwx------ 5 root root 160 Jun 12 03:51 ./ drwxr-xr-x 16 mysql root 512 Jun 12 03:50 ../ -rw-r----- 1 root root 67 Jun 12 03:50 db.opt -rw-r----- 1 root root 1966 Jun 12 03:50 test.frm -rw-r----- 1 mysql mysql 98304 Jun 12 03:50 test.ibd- 1
- 2
- 3
- 4
- 5
凡事有好坏，独立表空间也有着有缺点：
优点：
- 使用 truncate table 或 drop table 时候，可以释放、回收磁盘空间。而使用共享表空间的则仅在系统表空间数据文件(ibdata文件)内部创建空闲空间。
- 可以将表指定到单独的磁盘中，如将查询热点表单独存到一个磁盘中以优化查询；在如将一个大表单独享用一个独立磁盘等等。create table… data directory=absolute_path_to_directory（单独指定的磁盘路径）
- 当使用 optimize table 优化重建表时候，会新建一个只存放需要保留的数据的文件.ibd，并将新 .ibd 文件替换旧的 .ibd。不需要在共享表空间操作，不影响其他表的使用。
- 支持可迁移表空间特性，可以对单独表的迁移。如将一个 .ibd文件和.frm 复制到其他的数据库实例中就能使用。
- 可以在操作系统层面就能知道单表的使用大小，而不用通过mysql。
- 当 innodb_flush_method=O_DIRECT，Linux文件系统不允许并发写入单个文件。所以使用独立表空间就比使用共享表空间更具优势。
缺点：
- 当进行 fsync 时候是在独立表空间文件上进行，所以不能像使用共享表空间那样可以将多个 I/O 合并成一个进行操作。一定程度上会增加较多的 fsync 调用。
- mysqld 会为每一个表保留一个文件句柄，若表过多的情况下可能会影响性能。
- 单表大小会受操作系统单文件大小限制。
可以结合共享表空间和独立表空间优缺点和具体的业务特点选择是否开启独立表空间，当然也可以将共享表空间中的表抽离成独立表空间。
一般有两种方式可以采用，在此之前都需要开启独立表空间。
innodb_file_per_table=ON- 1
两种方式分别是：
- 逻辑备份后，重启数据库后导入。
- 重启数据库并将需要抽离的表执行 alter table you_table_name engine=innodb，该方法只会对新表使用独立表空间，旧表还是会使用共享表空间。
1.3 临时表空间
默认情况下 innodb 会在 innodb_data_home_dir目录创建一个 12MB 的 ibtmp1 文件，用于存放查询时候产生临时表。
可以使用 innodb_temp_data_file_path选项指定临时表空间数据文件的路径、文件名和文件大小。
其设置方式和共享表空间一致，如：ibtmp1:12M:autoextend。
在使用大型临时表或广泛使用临时表的环境中，自动扩展临时表空间数据文件可能会变得很大。
大型数据文件也可能来自使用临时表的长时间运行的查询。为防止临时数据文件变得过大，可配置 innodb_temp_data_file_path选项以指定最大数据文件大小。二、锁
锁的作用是在并发情况下通过加锁的机制实现各种隔离级别，MySQL 有着多种锁的存在，可以按照锁粒度、锁机制、算法分类、模式、状态、特殊的自增锁等几种类型进行分类。
2.1 按照粒度分类
按锁粒度可分为：全局锁、表级锁、页级锁、行级锁。
全局锁：对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的 MDL、DDL、更新操作语句等事务提交将被阻塞。通常用于全库的逻辑备份。
# 可使用以下语句进行加锁 FLUSH TABLES WITH READ LOCK; # 可使用以下语句进行释放锁 UNLOCK TABLES;- 1
- 2
- 3
- 4
- 5
表级锁：对当前操作的整张表加锁，分为表锁、元数据锁（meta data lock，MDL)两种。
元数据锁是MySQL5.5版本引入，用于解决或者保证DDL操作与DML操作之间的一致性。当对一个表做增删改查操作的时候，加 MDL读锁；当要对表做结构变更操作的时候，加 MDL写锁。
# 表锁加锁 lock tables table_name read/write； # 释放表锁 unlock tables; # 可使用语句查看表是否加锁， In_use = 1时候代表该表上有锁存在。 SHOW OPEN TABLES; e.g. mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | test | +----------------+ 1 row in set (0.01 sec) mysql> lock tables test read; Query OK, 0 rows affected (0.01 sec) mysql> show open tables; +----------+---------------------------+--------+-------------+ | Database | Table | In_use | Name_locked | +----------+---------------------------+--------+-------------+ | test | test | 1 | 0 | +----------+---------------------------+--------+-------------+ 22 rows in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
页级锁：锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多；行级冲突少，但速度慢。因此采取了折中的页级锁，一次锁定相邻的一组记录。BDB 引擎支持页级锁。
行级锁：粒度最低的锁，发生锁冲突的概率也最低、并发度最高。但是加锁慢、开销大，容易发生死锁现象。只有 InnoDB 支持行级锁，分为共享锁和排他锁。
其中行级锁并不是直接锁记录，而是锁索引。索引分为主键索引（聚集（clustered）索引）和非主键索引（非聚集（unclustered）索引）两种，所以加锁分为两种情况：
- 使用主键索引，行级锁就会锁定这条主键索引；
- 使用非主键索引，行级锁先锁定该非主键索引，再锁定对应的主键索引。 在UPDATE、DELETE操作时，行级锁不仅锁定 WHERE 条件扫描过的所有索引记录，而且会锁定相邻的键值，即临键锁（next-key locking）。
Innodb 是否使用行锁，需要注意以下几种情况：
- 不通过索引条件查询的时候，InnoDB使用的是表锁。
- 行锁是针对索引加的锁，所以尽管访问不同行的记录，如果使用了相同的索引键，也是会出现锁冲突的。
- 即便在条件中使用了索引字段，但是执行计划选择了全表扫描情况下，innodb 使用的是表锁而非行锁。
2.2 按机制分类
按属性分类，可分为共享锁（S 锁）和排他锁（X 锁）。
共享锁(Shared)
又称为S 锁、读锁，即多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。只有当数据上的读锁被释放后，其他事务才能对其添加写锁。共享锁主要是为了支持并发的读取数据而出现的，读取数据时，不允许其他事务对当前数据进行修改操作，从而避免 不可重读 的问题的出现。
# 开启共享锁 select * from table lock in share mode;- 1
- 2
- 3
排它锁（Exclusive）
又称为X 锁、写锁、独占锁。当一个事务对数据加上写锁后，其他事务既不能对该数据添加读锁，也不能对该数据添加写锁，写锁与其他锁都是互斥的。写锁主要是为了解决在修改数据时，不允许其他事务对当前数据进行修改和读取操作，从而可以有效避免 脏读 问题的产生。
innodb 在发生 DML(update,delete,insert)) 语句时默认会数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用 select …f or update语句。
2.3 按加锁方式分类
可分为隐式（自动）锁、显示锁。
隐式加锁：
- InnoDB 自动加意向锁。
- DML 语句自动添加排他锁。
显示加锁：
3. 共享锁（S）：SELECT * FROM table WHERE … LOCK IN SHARE MODE。
4. 排他锁（X) ：SELECT * FROM table WHERE … FOR UPDATE。2.4 按算法分类
可分为记录锁、间隙锁、临键锁。
记录锁（Record Lock）：
A record lock is a lock on an index record.
For example, SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;
prevents any other transaction from inserting, updating, or deleting rows where the value of t.c1 is 10.记录锁也称之为行锁，锁住的是索引记录，如果使用索引作为条件命中了记录，那么就是记录锁。被锁住的记录不能被别的事务插入相同的索引键值，修改和删除。
间隙锁（Gap Lock）：
A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record.
锁定一段范围内（左开右开的区间）的索引记录。使用间隙锁锁住的是一个区间，不仅仅是这个区间中的每一条数据。本质上是用于阻止其他事务在该间隙内插入新记录，而自身事务是允许在该间隙内插入数据的。间隙锁只存在可重复读隔离。
间隙锁是不互斥的，故不区分共享间隙锁或互斥间隙锁。即两个事务可以同时持有包含共同间隙的间隙锁，不管两个间隙锁的间隙区间完全一样，还是一个间隙锁包含的间隙区间是另一个间隙锁包含间隙区间的子集。
如上图中：1、5、6、9代表是已经有的记录主键，中间的部分是空着的。- 如 select * from table where id = 3 或 select * from where id between 1 and 5，这时候其他事务不能在区间（1,5）之间插入新的记录。
- 如 select * from table where id = 20，这时区间（9，+∞）之间是被锁住的。如 insert id = 13 的语句将会被阻塞。
临键锁（Next-key Lock）：
A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.
临键锁相当于记录锁+间隙锁的组合。是为了解决 幻读 问题而出现的。其锁住的范围是索引记录，和索引区间。
临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。
如上图中：1、5、6、9代表是已经有的记录主键，中间的部分是空着的。- 如 select * from table where id > 2 and id < 9, 该范围内包含了存在和不存在的记录，故会锁定（1, 6]、（6,9] 区间，其他事务不能在该区间插入数据。
2.5 按模式分类
可分为悲观锁和乐观锁。其中乐观锁比较特殊，是借助业务逻辑来实现的锁机制。
乐观锁
乐观锁一般会在数据表中增加一个字段来表示是否被其他事务更新，比如更新状态、版本号等等。乐观假想数据不会被更新，当此次事务需要提交时候判断该条数据是否在本次事务开始后提交前被其他事务更改了。如被更改了，则返回给业务逻辑判断如何处理。这样的优点就是能减少锁使用，提供性能。
悲观锁
悲观锁可以看作乐观锁的相对面，基于一种悲观的态度类来防止一切数据冲突，它是以一种预防的姿态在修改数据之前把数据锁住，然后再对数据进行读写。在它释放锁之前任何请求都不能对其数据进行操作，直到前面锁持有者把锁释放后。其他需要对数据加锁的才可以对数据进行加锁进行操作，通常情况数据库本身锁的机制都是基于悲观锁的机制实现的。
其优缺点都很明显：
优点：可以完全保证数据的独占性和正确性。
缺点：加锁释放锁的过程会造成消耗，所以性能相对不高，且会增加产生死锁的风险。2.6 按状态分类
可分为意向共享锁(IS)、意向排它锁(IX)。
Intention locks are table-level locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table。
- Before a transaction can acquire a shared lock on a row in a table, it must first acquire an IS lock or stronger on the table.
- Before a transaction can acquire an exclusive lock on a row in a table, it must first acquire an IX lock on the table.
从上文不难得出：共享锁/意向排他锁属于表锁，且取得意向共享锁/意向排他锁是取得共享锁/排他锁的前置条件。
- 获得行的共享锁必须先获得表的意向共享锁。
- 获得行的排他
共享锁/排他锁与意向共享锁/意向排他锁的兼容性关系：
排他锁（X） 意向排他锁（IX） 共享锁（S） 意向共享锁（IS） 排他锁（X ） 互斥 互斥 互斥 互斥 意向排他锁（IX） 互斥 可并行 互斥 可并行 共享锁（S） 互斥 互斥 可并行 可并行 意向共享锁（IS） 互斥 可并行 可并行 可并行 从上表加粗部分可看出意向锁之间不是互斥的，可以并行进行。
意向锁的存在价值在于在定位到特定的行所持有的锁之前，提供一种更粗粒度的锁，可以大大节约引擎对于锁的定位和处理的性能，因为在存储引擎内部，锁是由一块独立的数据结构维护的，锁的数量直接决定了内存的消耗和并发性能。
如：事务 A 对表 T 的进行 DML 操作（加X锁）这时也会对表 T 加意向排它锁，在事务 A 提交之前，加入有事务 B 也进行 DML 操作，此时表级别的意向排它锁就能告诉事务 B 需要等待（表 T 上有意向排他锁），而不需要再去行级别判断。
2.7 自增锁
An AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns.The innodb_autoinc_lock_mode configuration option controls the algorithm used for auto-increment locking. It allows you to choose how to trade off between predictable sequences of auto-increment values and maximum concurrency for insert operations.
从文中不难得出：自增锁是一种特殊的表级锁，由插入到带有AUTO_INCREMENT列的表中的事务使用。
当发生类 insert 语句时候，大致上可分为几种情况分析：
Simple inserts
可以预先确定要插入的行数（当语句被初始处理时）的语句。 这包括没有嵌套子查询的单行和多行 INSERT 和 REPLACE 语句，但不包括INSERT … ON DUPLICATE KEY UPDATE。Bulk inserts
事先不知道要插入的行数（和所需自动递增值的数量）的语句。 这包括INSERT … SELECT、REPLACE … SELECT和LOAD DATA语句，但不包括纯INSERT。InnoDB 在处理每行时一次为 AUTO_INCREMENT 列分配一个新值。
Mixed-mode inserts
- Simple inserts 中部分指定了自增值。
- INSERT … ON DUPLICATE KEY UPDATE。其中 AUTO_INCREMENT 列的分配值不一定会在 UPDATE 阶段使用，所以此类语言比较浪费自增键。
innodb 提供了自增锁三种锁定模式，由参数 innodb_autoinc_lock_mode 控制。
- innodb_autoinc_lock_mode=0 该模式下上面三种类型 insert 语句在开始时候都会得到一个表级别的 aotu_inc 锁，在语句结束时候才进行释放。注意是每条语句，一个事务中可能包含多条语句。基于语句复制(statement-based replication) 安全，自增值连续，并发不高。
- innodb_autoinc_lock_mode=1 Innod 默认模式，该模式在兼用 0 模式下对 Simple inserts 类进行优化。Simple inserts 在插入前可以知道具体的条数，故直接生成对应记录数的连续值，语句得到了相应的值后就可以提前释放锁。基于语句复制(statement-based replication) 安全，自增值连续，并发中等。
- innodb_autoinc_lock_mode=2 该模式下没有 auto_inc 锁，性能最好。但是基于二机制ROW语句复制或恢复是不安全的，mysql 推荐 binlog 为 row模式，故比较少用该模式。
三、隔离级别
隔离级别的出现是为了解决多事务并发情况下可能会产生的几种异常情况，分别是 更新丢失(Lost Update)、脏读(Drity Read)、不可重复度(Non-repeatable Read)、幻读(Phantom Read)。
3.1 解决问题
更新丢失(Lost Update)
通常会出现于两种情况：回滚丢失和覆盖丢失。脏读(Drity Read)：
又称无效数据读，即读取未提交数据，一个事务读取另外一个事务还没有提交的数据叫脏读。
总结为：读取未提交数据。不可重复读(Non-repeatable Read)：
即在同一个事务内，相同的查询多次返回结果是不一致的。
总结为：事务中多次读取，数据内容不一致。幻读(Phantom Read)：
总结为：事务中多次读取，数据总量不一致其中不可重复度和幻读可能有些人认为是同样的问题，其实是不一样的。不可重复读侧重于记录的修改，幻读侧重于记录的新增或删除。
标准SQL定义了四种隔离级别，用于解决上面几种问题、支持事务的实现。分别是：读取未提交(Read Uncommitted)、读提交(Read Committed)、可重复读(Repeatable Read)、串行化(Serializable)。
其中各种隔离级别所能解决上述问题的支持情况如下表所示(Yes代表能解决；No代表不能解决)：
隔离级别 脏读 不可重复读 幻读 读取未提交(Read Uncommitted) No No No 读提交(Read Committed) Yes No No 可重复读(Repeatable Read) Yes Yes No 串行化(Serializable) Yes Yes Yes 其中可重复读(Repeatable Read) 可用了临键锁 + MVCC解决了幻读现象，下文在讲解。
3.2 浅谈隔离级别
Read Uncommitted（读未提交）
即事务间可相互读取其他事务没有提交的数据，往往会引发脏读问题。该隔离级别整体优势不是很明显，故并不常用于实际生产中。了解即可。Read Committed（读提交）
即可读取其它事务提交的数据，部分关系型数据库默认的隔离界别（如：Oracle）。但是往往会出现 不可重复读 问题，即事务不同阶段对同一记录读取到的值是不相同的。Repeatable Read（可重复读）
即使用视图保证事务过程中同一记录的一致，但是不能避免 幻读 问题的产生。该隔离级别是 MySQL （innodb、Falcon）默认隔离级别，通常使用MVCC + 临键锁来避免幻读产生。Serializable（串行化）
即使用对数据加锁（共享、排他）实现是数据记录操作的串行执行，可解决脏读、不可重复的、幻读的问题。一致性最高但性能相对不高。3.3 查看隔离级别
MySQL 默认的隔离级别是 可重复读，可以使用以下语句进行查看。
select @@session.tx_isolation; select @@global.tx_isolation; # MySQL8 之后也可使用下面语句查看 select @@session.transaction_isolation; select @@global.transaction_isolation;- 1
- 2
- 3
- 4
- 5
- 6
- 7
3.4 设置隔离级别
# 可选级别：READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE # 会话级别 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ # 全局级别 SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ- 1
- 2
- 3
- 4
- 5
- 6
- 7
四、bin log
bin log 是 MySQL 的一种二进制日志，记录引起或可能（更新删除没有匹配的记录）引起数据库变动的事件信息。
bin log 以事件形式记录，不是事务日志。对于非事务表的操作，每当语句执行完成则直接写入；对于事务表的操作则会在事务提交时（先记录到缓存中）一次性写入。
4.1 用途
主要用于数据备份和恢复、主从同步、审计、数据异构、基于数据的任务分发等等。
主从同步
slave 从服务器 I/O 线程将主服务器的二进制日志读取过来记录到从服务器本地文件，然后SQL线程会读取 relay log 日志的内容并应用到从服务器，从而使从服务器和主服务器的数据保持一致。
数据异构
把数据按需（数据结构、存取方式、存取形式）异地构建存储。
基于数据的任务分发
多服务依赖相同数据而不侵占其他服务的实现。
4.1开启 bin log
MariaDB/MySQL默认没有启动二进制日志，要启用二进制日志使用 --log-bin=[on|off|file_name] 或配置文件中 log-bin=[on|filename]选项指定。
如果没有给定file_name，则默认为datadir下的主机名加"-bin"，并在后面跟上一串数字表示日志序列号，如果给定的日志文件中包含了后缀(logname.suffix)将忽略后缀部分。
mysqld 会创建一个二进制日志索引文件，当二进制日志文件滚动的时候会向该文件中写入对应的信息。所以该文件包含所有使用的二进制日志文件的文件名。
默认情况下该文件与二进制日志文件的文件名相同，扩展名为’.index’。要指定该文件的文件名使用 --log-bin-index[=file_name] 选项。
当 mysqld 在运行时不应手动编辑该文件，免得 mysqld 变得混乱或重启失败。
-rw-r----- 1 mysql mysql 340 Jun 26 09:25 bin-log.000003 -rw-r----- 1 mysql mysql 17 Jun 26 09:25 bin-log.index- 1
- 2
- 最大值通过变量 max_binlog_size 设置(默认值为1G)。但由于二进制日志可能是基于事务来记录的(如innodb表类型)，而事务是绝对不可能也不应该跨文件记录的，如果正好二进制日志文件达到了最大值但事务还没有提交则不会滚动日志，而是继续增大日志，所以 max_binlog_size 指定的值和实际的二进制日志大小不一定相等。
- 出于安全和性能考虑，极不建议将二进制日志和datadir放在同一磁盘上。
4.2 查看 bin log
MySQL 中查看二进制日志的方法主要有几种。
1. mysqlbinlog 命令
格式：mysqlbinlog [option] log-file1 log-file2…# 常用选项 -d,--database=name：只查看指定数据库的日志操作 -o,--offset=#：忽略掉日志中的前n个操作命令 -r,--result-file=name：将输出的日志信息输出到指定的文件中，使用重定向也一样可以。 -s,--short-form：显示简单格式的日志，只记录一些普通的语句，会省略掉一些额外的信息如位置信息和时间信息以及基于行的日志。可以用来调试，生产环境千万不可使用 --set-charset=char_name：在输出日志信息到文件中时，在文件第一行加上set names char_name --start-datetime,--stop-datetime：指定输出开始时间和结束时间内的所有日志信息 --start-position=#,--stop-position=#：指定输出开始位置和结束位置内的所有日志信息 -v,-vv：显示更详细信息，基于row的日志默认不会显示出来，此时使用-v或-vv可以查看- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
在一个新开的实例中创建一个 test 数据库、test 数据表并插入一条数据为例子查看 bin log 记录情况。
root@565bf1f8399a:/var/lib/mysql# mysqlbinlog bin-log.000002 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; 1. 初始信息，可以看到记录了时间和位置信息(at 4)。 # at 4 #220626 9:43:32 server id 1 end_log_pos 256 CRC32 0x1485ea02 Start: binlog v 4, server v 10.5.12-MariaDB-1:10.5.12+maria~focal-log created 220626 9:43:32 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG ' RCq4Yg8BAAAA/AAAAAABAAABAAQAMTAuNS4xMi1NYXJpYURCLTE6MTAuNS4xMittYXJpYX5mb2Nh bC1sb2cAAAAAAAAAAABEKrhiEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAEEwQADQgICAoKCgEC6oUU '/*!*/; # at 256 #220626 9:43:32 server id 1 end_log_pos 299 CRC32 0x6468dccc Gtid list [0-1-6] # at 299 #220626 9:43:32 server id 1 end_log_pos 340 CRC32 0x418a9234 Binlog checkpoint bin-log.000002 # at 340 #220626 9:44:47 server id 1 end_log_pos 382 CRC32 0x5ad3b3b6 GTID 0-1-7 ddl /*!100101 SET @@session.skip_parallel_replication=0*//*!*/; /*!100001 SET @@session.gtid_domain_id=0*//*!*/; /*!100001 SET @@session.server_id=1*//*!*/; /*!100001 SET @@session.gtid_seq_no=7*//*!*/; # at 382 #220626 9:44:47 server id 1 end_log_pos 471 CRC32 0xd1fa3319 Query thread_id=4 exec_time=0 error_code=0 SET TIMESTAMP=1656236687/*!*/; SET @@session.pseudo_thread_id=4/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1, @@session.sql_if_exists=0/*!*/; SET @@session.sql_mode=1411383296/*!*/; 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.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; 2. 创建 test 数据库。 CREATE DATABASE `test` /*!*/; # at 471 #220626 9:45:20 server id 1 end_log_pos 513 CRC32 0x54820548 GTID 0-1-8 ddl /*!100001 SET @@session.gtid_seq_no=8*//*!*/; # at 513 #220626 9:45:20 server id 1 end_log_pos 711 CRC32 0x408eb615 Query thread_id=4 exec_time=0 error_code=0 3. 创建 test 数据表。 use `test`/*!*/; SET TIMESTAMP=1656236720/*!*/; CREATE TABLE `test`.`test` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL DEFAULT '', PRIMARY KEY (`id`) ) /*!*/; # at 711 #220626 9:45:46 server id 1 end_log_pos 753 CRC32 0x29248341 GTID 0-1-9 trans /*!100001 SET @@session.gtid_seq_no=9*//*!*/; 开启事务 START TRANSACTION /*!*/; # at 753 #220626 9:45:46 server id 1 end_log_pos 785 CRC32 0x1538c98f Intvar 自增锁 SET INSERT_ID=1/*!*/; # at 785 #220626 9:45:46 server id 1 end_log_pos 903 CRC32 0xb1a7f07c Query thread_id=4 exec_time=0 error_code=0 SET TIMESTAMP=1656236746/*!*/; 4. 插入一条数据 INSERT INTO `test`.`test`(`name`) VALUES ('测试') /*!*/; # at 903 #220626 9:45:46 server id 1 end_log_pos 934 CRC32 0x503162f1 Xid = 51 COMMIT/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
2. show binary logs
用于查看当前使用了哪些二进制日志文件。- cat mysql-bin.index
- show {binary | master} logs
3. show binlog events
用于查看日志中进行了哪些操作，show binlog events in ‘bin-log’;mysql> show binlog events in 'bin-log.000002'; +----------------+-----+-------------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +----------------+-----+-------------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------+ | bin-log.000002 | 4 | Format_desc | 1 | 256 | Server ver: 10.5.12-MariaDB-1:10.5.12+maria~focal-log, Binlog ver: 4 | | bin-log.000002 | 256 | Gtid_list | 1 | 299 | [0-1-6] | | bin-log.000002 | 299 | Binlog_checkpoint | 1 | 340 | bin-log.000002 | | bin-log.000002 | 340 | Gtid | 1 | 382 | GTID 0-1-7 | | bin-log.000002 | 382 | Query | 1 | 471 | CREATE DATABASE `test` | | bin-log.000002 | 471 | Gtid | 1 | 513 | GTID 0-1-8 | | bin-log.000002 | 513 | Query | 1 | 711 | use `test`; CREATE TABLE `test`.`test` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL DEFAULT '', PRIMARY KEY (`id`) ) | | bin-log.000002 | 711 | Gtid | 1 | 753 | BEGIN GTID 0-1-9 | | bin-log.000002 | 753 | Intvar | 1 | 785 | INSERT_ID=1 | | bin-log.000002 | 785 | Query | 1 | 903 | use `test`; INSERT INTO `test`.`test`(`name`) VALUES ('测试') | | bin-log.000002 | 903 | Xid | 1 | 934 | COMMIT /* xid=51 */ | +----------------+-----+-------------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------+ # 可以指定起始位置。同样，起始位置必须指定正确，不能指定不存在的位置。 mysql> show binlog events in 'bin-log.000002' from 711; +----------------+-----+------------+-----------+-------------+-----------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +----------------+-----+------------+-----------+-------------+-----------------------------------------------------------------+ | bin-log.000002 | 711 | Gtid | 1 | 753 | BEGIN GTID 0-1-9 | | bin-log.000002 | 753 | Intvar | 1 | 785 | INSERT_ID=1 | | bin-log.000002 | 785 | Query | 1 | 903 | use `test`; INSERT INTO `test`.`test`(`name`) VALUES ('测试') | | bin-log.000002 | 903 | Xid | 1 | 934 | COMMIT /* xid=51 */ | +----------------+-----+------------+-----------+-------------+-----------------------------------------------------------------+- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
4. show master status
用于显示主服务器中的二进制日志信息。如果是主从结构，它只会显示主从结构中主服务器的二进制日志信息。可以查看到当前正在使用的日志及下一事件记录的开始位置，还能查看到哪些数据库需要记录二进制日志，哪些数据库不记录二进制日志。 mysql> show master status; +----------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +----------------+----------+--------------+------------------+ | bin-log.000002 | 934 | | | +----------------+----------+--------------+------------------+ 1 row in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
4.3 刷新 bin log
可使用命令：mysqladmin -uroot -p refresh
4.4 删除 bin log
1. reset master
删除所有日志，并让日志文件重新从000001开始。2. PURGE { BINARY | MASTER } LOGS { TO ‘log_name’ | BEFORE datetime_expr }
purge master logs to “binlog_name.00000X” 将会清空00000X之前的所有日志文件，正在使用中的日志文件中，将无法进行 purge。1.例如删除000004之前的日志文件：
mysql> purge master logs to "mysql-bin.000004";- 1
2.删除指定日期之前的所有日志：
purge master logs before '2022-06-25 12:36:40';- 1
3. --expire_logs_days=N
使用–expire_logs_days=N选项指定过了多少天日志自动过期清空。4.5 记录格式
MySQL 5.1之前，只有一种基于语句 statement 形式的日志记录格式。即将所有的相关操作记录为SQL语句形式。
但是这样的记录方式对某些特殊信息无法同步记录，例如uuid，now()等这样动态变化的值。
MySQL 5.1开始，MySQL支持 statement、row、mixed 三种形式的记录方式，由变量 binlog_format 来指定。
- row 形式是基于行来记录，也就是将相关行的每一列的值都在日志中保存下来，这样的结果会导致日志文件变得非常大，但是保证了动态值的确定性。 innodb引擎的创始人之一推荐使用row格式。
- mixed 形式，如何记录日志由MySQL自己来决定。
以本节第二小节内容为例，各种格式如下所示：
statement
row格式
记录内容不可直接阅读，需要加 -vv 选项。记录的非常详细，所以日志文件相对大。
mixed格式
默认会采用 statement 的方式记录，部分特殊情况会采用row的形式来记录日志。- 存储引擎为NDB，对表的DML操作都会以row的格式记录。
- 使用了uuid()、user()、current_user()、found_rows()、row_count()等不确定函数。
- 使用了insert delay语句。
- 用了临时表。
mysqlbinlog mysql-bin.000001 -vv- 1
4.6 刷新时机
事务执行过程中，先把日志写到 bin log cache，事务提交的时候，再把 bin log cache 写到 bin log 文件中。
一个事务的 bin log 不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为 bin log cache。
刷盘流程如下：
需要注意上图中标红的两个写操作，设置不当可能导致日志丢失。两个写入时机由参数：sync_binlog 控制。
- sync_binlog=0 ：每次提交事务都只写到 page cache，由系统自行判断什么时候执行fsync 到 binlog 文件。若机器宕机，page cache 里面的 binglog 会丢失。
- sync_binlog=1：每次提交事务都会执行 fsync，要保证事务的一致性和持久性的时候，必须将sync_binlog的值设置为1。
- sync_binlog=N(N >= 1)：每次提交事务都只写到 page cache，但累积N个事务后才fsync。若机器宕机，page cache 里面的 N个事务的 binglog 会丢失。
4.7 相关设置
#指定是否启用记录二进制日志或者指定一个日志路径(路径不能加.否则.后的被忽略). log_bin = {on | off | base_name} #指定是否启用记录二进制日志，只有在log_bin开启的时候才有效 sql_log_bin ={ on | off } #指定自动删除二进制日志的时间，即日志过期时间 expire_logs_days = #明确指定要记录日志的数据库 binlog_do_db = #指定不记录二进制日志的数据库 binlog_ignore_db = #指定mysql-bin.index文件的路径 log_bin_index = #指定二进制日志基于什么模式记录 binlog_format = { mixed | row | statement } # MySQL5.6.2添加了该变量，当binlog format为row时，默认不会记录row对应的SQL语句，设置为1或其他true布尔值时会记录，但需要使用mysqlbinlog -v查看，这些语句是被注释的，恢复时不会被执行。 binlog_rows_query_log_events = { 1|0 } #指定二进制日志文件最大值，超出指定值将自动滚动。但由于事务不会跨文件，所以并不一定总是精确。 max_binlog_size = #基于事务类型的日志会先记录在缓冲区，当达到该缓冲大小时这些日志会写入磁盘。 binlog_cache_size = 32768 #指定二进制日志缓存最大大小，硬限制。默认4G，建议不要改。 max_binlog_cache_size = #一般等同于且决定binlog_cache_size大小，所以修改缓存大小时只需修改这个而不用修改binlog_cache_size binlog_stmt_cache_size = 32768 #binlog_cache_size的值 binlog_stmt_cache_disk_use: #使用缓存写二进制日志的次数(这是一个实时变化的统计值) binlog_cache_use： #使用临时文件写二进制日志的次数，当日志超过了binlog_cache_size的时候会使用临时文件写日志，如果该变量值不为0，则考虑增大binlog_cache_size的值。 binlog_cache_disk_use: #使用缓存写二进制日志的次数 binlog_stmt_cache_use： #使用临时文件写二进制日志的次数，当日志超过了binlog_cache_size的时候会使用临时文件写日志，如果该变量值不为0，则考虑增大 #这个参数直接影响mysql的性能和完整性 sync_binlog = { 0 | n } sync_binlog=0: 不同步，日志何时刷到磁盘由FileSystem决定，这个性能最好。 sync_binlog=n: 每写n次事务(注意，对于非事务表来说，是n次事件，对于事务表来说，是n次事务，而一个事务里可能包含多个二进制事件)，MySQL将执行一次磁盘同步指令fdatasync()将缓存日志刷新到磁盘日志文件中。Mysql中默认的设置是sync_binlog=0，即不同步，这时性能最好，但风险最大。一旦系统奔溃，缓存中的日志都会丢失。 要保证事务的一致性和持久性的时候，必须将sync_binlog的值设置为1。 因为每次事务提交都会写入二进制日志，设置为1就保证了每次事务提交时二进制日志都会写入到磁盘中，从而立即被从服务器复制过去。- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
要保证事务的一致性和持久性的时候，必须将sync_binlog的值设置为1。
因为每次事务提交都会写入二进制日志，设置为1就保证了每次事务提交时二进制日志都会写入到磁盘中，从而立即被从服务器复制过去。4.8 恢复重做
指定二进制日志的起始位置（可指定终止位置）并将其保存到 sql 文件中，由 mysql 命令来载入恢复即可。当然也可以直接通过管道送给mysql。
基于位置来恢复还是基于时间点来恢复，这两种行为都可以。选择时间点来恢复比较直观些，并且跨日志文件恢复时更方便。
mysqlbinlog --stop-datetime="2022-06-25 12:17:44" /tmp/mysql-bin.000005 | mysql -u user -p password # 恢复多个二进制日志文件时： mysqlbinlog mysql-bin.[*] | mysql -uroot -p password # 入到一个文件中后恢复。 mysqlbinlog mysql-bin.000001 > /tmp/1.sql mysqlbinlog mysql-bin.000002 >>/tmp/2.sql mysql -u root -p password -e "source /tmp/1.sql"- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
五、redo log
和 bin log 不同，redo log 不是二进制日志。它是物理日志，记录数据页的物理修改。用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。
redo log 是 innodb 为了支持崩溃恢复而出现的，只记录 innodb 存储引擎中表的修改。
bin log 和 inndb 总的来说有如下不同：
- 层面不同 bin log MySQL 本身实现的日志模块，而 redo log 是 innodb 引擎层实现的。
- 记录内容不同 bin log 记录的是逻辑性语句，即便是行格式形式。而 redo log 记录在物理上更改的日志，它记录的是数据库中每个页的修改。
- 写入形式不同 bin log 每次(看具体写入时机)事务提交的时候一次性写入缓存中，而 redo log 是分为两阶段写入：1. 准备阶段写如 redo log 缓存中。2. 然后当 innodb buffer 数据更新完成且事务提交后，再向 redo log 缓存中写入提交动作。也就是两阶段提交，后续章节会详解。
- 写入时机不同 bin log 在事务提交的时候一次性写入，故日志中的记录方式和提交顺序有关，且一次提交对应一次记录。而 redo log 记录的是物理页的修改，同一个事务中可能有多次记录。最后提交的事务会覆盖所有未提交的事务记录。如有事务A 有多个版本的操作记录 v-1、v-2、v-3，v-3 代表的记录是最后的操作结果，所以数据页最终的结果就是 v-3 操作的。redo log 是并发写入的，不同事务之间的不同版本的记录会穿插写入到 redo log文件中。
- redo log 具有幂等性，而 bin log 是记录所有影响数据的操作，记录的内容较多。
5.1 用途
如名所示，redo log 是重做日志，提供前滚操作。让 inndb 具有崩溃恢复能力从而支持事务，保证数据的持久性与完整性。
5.2 redo log 构成
redo log 由两部分组成：1. 处于内存中的日志缓存（redo log buffer）; 2. 位于磁盘中的重做日志文件（redo log file）。
如下图所示：
redo log 以块为单位进行存储的，每个块占512字节，称之为 redo log block。不管是存于缓存中 redo log 还是位于磁盘文件中的 redo log 都是以512字节为一块存储的。
日志块（log block）
每个redo log block由3部分组成：日志块头、日志块尾和日志主体。其中日志块头占用12字节，日志块尾占用8字节，所以每个 redo log block 的日志主体部分只有512-12-8=492字节。
日志块头包含4部分：
- log_block_hdr_no：(4字节) 记录该日志块在redo log buffer中的位置ID。
- log_block_hdr_data_len：(2字节) 记录该log block中已使用的log大小。写满该log block时为0x200，表示512字节。
- log_block_first_rec_group：(2字节) 记录该log block中第一个log的开始偏移位置。
- lock_block_checkpoint_no：(4字节) 记录写入检查点信息的位置。
日志组（log group）
log group 表示的是 redo log group，一个组内由多个大小完全相同的redo log file组成。
组是一个逻辑的概念，并没有真正的文件来表示这是一个组，但是可以通过变量 innodb_log_group_home_dir 来定义组的目录，redo log file 都放在这个目录下，默认是在datadir下。
组内 redo log file 的数量由变量 innodb_log_files_group 决定，默认值为2，即两个 redo log file。
-rw-r----- 1 mysql mysql 100663296 Jun 26 09:45 ib_logfile0 -rw-r----- 1 mysql mysql 100663296 Jun 26 09:45 ib_logfile1- 1
- 2
在 innodb_log_group_home_dir 配置的路径下可以看到两个 ib_logfile 开头的文件，它们就是 log group 中的 redo log file。大小完全一致且等于变量 innodb_log_file_size 定义的值。
innodb 将 log buffer 中的r edo log block 刷到 log file 中时，会以追加写入的方式循环轮询写入。即先在第一个log file（即ib_logfile0）的尾部追加写，直到满了之后向第二个log file（即ib_logfile1）写。当第二个log file满了会清空一部分第一个log file继续写入。
5.3 redo log 格
未完待续…
六、两阶段提交
七、undo log
八、 MVCC
-
相关阅读:
Selenium打开浏览器闪退问题（浏览器驱动是对应的前提）-解决办法
4.设置样式
安卓案例：选项菜单
微信小程序实现lot开发01 学习微信小程序 helloworld
函数的凹凸性与拐点
js函数作用域
关键词搜索1688商品数据接口(标题|主图|SKU|价格|优惠价|掌柜昵称|店铺链接|店铺所在地）
【OpenVINO™】在 C# 中使用OpenVINO™ 部署PP-YOLOE实现物体检测
Go语言进阶，交叉编译，数字与字符的转换，多变参数
python开发工程师面试准备
- 原文地址：https://blog.csdn.net/m0_51504545/article/details/125267886