MySQL8支持DDL原子性

简介

InnoDB表的DDL支持事务完整性，要么成功要么回滚，将DDL操作回滚日志写入到data dictionary 数据字典表mysql.innodb_ddl_log 中用于回滚操作，该表是隐藏的表，通过show tables无法看到。通过设置参数，可将ddl操作日志打印输出到mysql错误日志中。

为什么MySQL8支持原子性

老版本mysql数据库元数据信息在存储于.FRM.TRG.OPT文件系统、MyISAM 引擎的系统表里、以及 Innodb 存储引擎的系统表。分裂成多处这样的架构导致在系统异常的情况下很容易造成数据的不一致。

mysql8中，Data Dictionary 全部存在于由 InnoDB 表构成的系统表中。当遇到 crash recovery 的时候，系统可以安全的进行事务回滚，保障 DDL 语句的原子性。

设置日志开关并创建表验证

设置日志开关

mysql> show variables like 'log_error%';
+----------------------------+----------------------------------------+
| Variable_name              | Value                                  |
+----------------------------+----------------------------------------+
| log_error                  | /var/log/mysqld.log                    |
| log_error_services         | log_filter_internal; log_sink_internal |
| log_error_suppression_list |                                        |
| log_error_verbosity        | 2                                      |
+----------------------------+----------------------------------------+
4 rows in set (0.01 sec)

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

mysql> set global log_error_verbosity=3;
Query OK, 0 rows affected (0.00 sec)

mysql> set global innodb_print_ddl_logs=1;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t3 (id int);
Query OK, 0 rows affected (0.73 sec)

mysql> show variables like 'log_error';
+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| log_error     | /var/log/mysqld.log |
+---------------+---------------------+
1 row in set (0.00 sec)

查err日志中创建t3表的日志内容：
2023-09-20T07:40:35.222561Z 11 [Note] [MY-012473] [InnoDB] DDL log insert : [DDL record: DELETE SPACE, id=18, thread_id=11, space_id=6, old_file_path=./testdb/t3.ibd]
2023-09-20T07:40:35.222925Z 11 [Note] [MY-012478] [InnoDB] DDL log delete : 18
2023-09-20T07:40:35.328508Z 11 [Note] [MY-012477] [InnoDB] DDL log insert : [DDL record: REMOVE CACHE, id=19, thread_id=11, table_id=1068, new_file_path=testdb/t3]
2023-09-20T07:40:35.328658Z 11 [Note] [MY-012478] [InnoDB] DDL log delete : 19
2023-09-20T07:40:35.379493Z 11 [Note] [MY-012472] [InnoDB] DDL log insert : [DDL record: FREE, id=20, thread_id=11, space_id=6, index_id=158, page_no=4]
2023-09-20T07:40:35.379641Z 11 [Note] [MY-012478] [InnoDB] DDL log delete : 20
2023-09-20T07:40:35.566811Z 11 [Note] [MY-012485] [InnoDB] DDL log post ddl : begin for thread id : 11
2023-09-20T07:40:35.566979Z 11 [Note] [MY-012486] [InnoDB] DDL log post ddl : end for thread id : 11

补充说明

原子 DDL 操作步骤

InnoDB 存储引擎执行 DDL 时是分阶段进行的。比如 ALTER TABLE，可能会在执行提交阶段之前多次执行准备阶段和执行阶段的操作。

• 准备：创建所需的对象，并且将 DDL 日志写入 mysql.innodb_ddl_log 表中。DDL 日志定义了如何前滚和回滚相应的 DDL 操作。
• 执行：执行 DDL 操作。例如，为 CREATE TABLE 执行创建操作。
• 提交：更数据字典并提交数据字典事务。
• Post-DDL：重放并删除 mysql.innodb_ddl_log 表中的 DDL 日志。为了能够安全地执行回滚操作而不会导致不一致性，对于文件的操作，例如重命名数据文件或移动数据文件，放在这个最后的阶段执行。这个阶段还会为 DROP TABLE、TRUNCATE TABLE 以及其他重建表的 DDL 操作删除数据字典表 mysql.innodb_dynamic_metadata 中的动态元数据。

无论事务被提交还是回滚，在 Post-DDL 阶段都会重放并删除 mysql.innodb_ddl_log 表中的 DDL 日志。只有当服务器在执行 DDL 操作的过程中出现故障时，才会在 mysql.innodb_ddl_log 表中保留 DDL 日志。这种情况下，在服务器恢复之后执行 DDL 日志的重放和删除。

对于需要进行恢复的情况，服务器重启之后，可能执行 DDL 事务的提交，也可能执行事务的回滚。如果在提交阶段执行的数据字典事务已经记录在重做日志和二进制日志中，就会认为 DDL 操作已经成功，并且执行前滚操作。否则，当 InnoDB 重放数据字典重做日志的时候，将会回滚不完整的数据字典事务，并且回滚 DDL 事务。

数据字典表mysql.innodb_ddl_log介绍

数据字典表mysql.innodb_ddl_log中，该表存储在 mysql.ibd 数据字典表空间。所谓隐藏数据字典表，就是在不能以正常的方式直接访问，得在调试 debug 模式下访问隐藏表。

CREATE TABLE mysql.innodb_ddl_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,#每条 DDL 日志记录的唯一标识符。
  thread_id BIGINT UNSIGNED NOT NULL,
  type INT UNSIGNED NOT NULL,
  space_id INT UNSIGNED,
  page_no INT UNSIGNED,
  index_id BIGINT UNSIGNED,
  table_id BIGINT UNSIGNED,
  old_file_path VARCHAR(512) COLLATE UTF8_BIN,
  new_file_path VARCHAR(512) COLLATE UTF8_BIN,
  KEY(thread_id));

各个字段的注释，

id：每条 DDL 日志记录的唯一标识符。
thread_id：每个 DDL 日志记录都与一个 thread_id 相关联，用于重放和删除某个特定 DDL 事务的 DDL 日志。涉及多个数据文件操作的 DDL 事务将会生成多条 DDL 日志记录。
type：DDL 操作的类型。包括 FREE （删除一棵索引树）、DELETE（删除一个文件）、RENAME （重命名文件）或者 DROP（从数据字典表 mysql.innodb_dynamic_metadata 中删除元数据）。
space_id：表空间 ID.
page_no：包含分配信息的页面；例如，索引树的根页面。
index_id：索引 ID。
table_id：表 ID。
old_file_path：旧的表空间文件路径。用于创建或删除表空间文件的 DDL 操作；以及重命名表空间的 DDL 操作。
new_file_path：新的表空间文件路径。用于重命名表空间文件的 DDL 操作。

MySQL 8.0 原子 DDL 使用范围

支持的 ddl 范围

• 与表相关的原子 DDL 包括：
  数据库、表空间、表、索引的 CREATE、ALTER 以及 DROP 语句，以及 TRUNCATE TABLE 语句。
• 与表无关的原子 DDL 包括：
  存储过程、触发器、视图以及用户定义函数 UDF 的 CREATE 和 DROP 语句，以及适用的 ALTER 语句。
  帐户管理语句：用户和角色的 CREATE、ALTER、DROP 语句，以及适用的 RENAME 语句，以及 GRANT 和 REVOKE 语句。

不支持的 ddl 范围

• 非 InnoDB 存储引擎上的表相关 DDL 语句。
• INSTALL PLUGIN 和 UNINSTALL PLUGIN 语句。
• INSTALL COMPONENT 和 UNINSTALL COMPONENT 语句。
• CREATE SERVER、ALTER SERVER 以及 DROP SERVER 语句。

参考：

新特性解读 | MySQL 8.0 之原子 DDL - 知乎

MySQL8.0新特性(干货) - 知乎