MySQL 8新特性--原子DDL

标签：log -- mysql InnoDB MySQL 日志 id DDL

###1.Atomic DDL—原子DDL
从MySQL8.0开始支持原子DDL，原子DDL语句就是将和DDL操作关联的数据字典更新，存储引擎内部操作和二进制日志写入操作组合到单个，原子事务中。即使数据库在DDL执行期间挂了，也会提交事务，并将适用的更改保留到数据字典中，存储引擎和二进制日志，或者回滚事务。
MySQL 8.0中引入数据字典，可以实现原子DDL。在之前的MySQL版本中，元数据是存储在元数据文件，非事务性表和存储引擎特定的字典中，这需要中间提交。 MySQL数据字典提供的集中式事务元数据存储消除了这一障碍，使得将DDL语句可以集中提交或回滚(保证了一致性)。

1.1.支持的DDL语句

原子DDL支持表和非表相关的DDL语句。另外表相关的DDL操作需要存储引擎的支持，而非表相关的DDL则对存储引擎没有要求。目前，只有InnoDB存储引擎支持原子DDL。
支持的表DDL语句包括对数据库，表空间，表和索引的CREATE，ALTER，DROP操作，还有TRUNCATE TABLE语句。
支持的非表DDL语句包括：
CREATE和DROP语句，以及存储的程序，触发器，视图和用户定义函数（UDF）的ALTER语句（如果适用）。
帐户管理语句：CREATE，ALTER，DROP以及用户和角色的RENAME语句（如果适用），以及GRANT和REVOKE语句。

1.2.不支持的语句

非Innodb存储引擎的表相关的DDL语句
INSTALL PLUGIN和UNNSTALL PLUGIN语句
INSTALL COMPONENT 和UNINSTALL COMPONENT语句.
CREATE SERVER, ALTER SERVER和DROP SERVER语句.

1.3.原子DDL特点

原子DDL语句的特征包括以下几点：

元数据更新，二进制日志写操作和存储引擎操作和“适用的地方”都会被合并到一个事务中。
在DDL执行期间在SQL层面没有中间提交。
适用的地方：

数据字典，例程，事件和UDF高速缓存的状态要与DDL操作的状态一致，这意味着更新高速缓存以反映DDL操作是成功完成还是回滚。
DDL操作中涉及的存储引擎方法不执行中间提交，并且存储引擎将自身注册为DDL事务的一部分。
存储引擎支持DDL操作的重做和回滚，这在DDL操作的Post-DDL阶段执行。
DDL操作的可见行为是原子的，这会更改某些DDL语句的行为。

1.4.原子DDL给DDL执行行为带来的变化

由于引入了原子DDL，那么相应的DDL执行行为也会发生一些变化，下面将对各个DDL命令进行详细说明：

1.4.1.DROP TABLE

DROP TABLE操作完全支持原子DDL，只要其操作的表是原子DDL支持的存储引擎。要么全部删除，要么全部回滚。如下：

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

–表test2不存在，所以删除报错

mysql> drop table test,test2;
ERROR 1051 (42S02): Unknown table 'lei.test2'

–表test还存在

mysql> show tables;
+---------------+
| Tables_in_lei |
+---------------+
| test          |
+---------------+
1 row in set (0.00 sec)

注：如果是MySQL5.7和8.0组成主从，那么需要注意这一点。主库5.7表删掉了，但是从库8.0没有删掉。需要使用IF EXISTS语法来判断再删除。

1.4.2.DROP DATABASE

和DROP TABLE一样，只要这个数据库中所有表的存储引擎使用的是原子DDL支持的存储引擎，那么该操作就支持原子DDL，要么全部成功要么回滚。
注：因为删除数据库所在的目录是最后做的操作，不是原子事务的一部分。如果删除数据库目录失败了，那么不会回滚DROP DATABASE的操作。

1.4.3.支持原子DDL的表

对于不支持原子DDL存储引擎的表，那么删除表和原子DROP TABLE或DROP DATABASE不在同一个事务中。这些表删除操作将单独写入二进制日志，这会在中断DROP TABLE或DROP DATABASE操作的情况下将存储引擎，数据字典和二进制日志之间的差异限制为最多一个表。对于删除多个表的操作，不使用原子DDL支持的存储引擎的表将在执行之前删除。

1.4.4.CREATE TABLE, ALTER TABLE, RENAME TABLE, TRUNCATE TABLE, CREATE TABLESPACE和DROP TABLESPACE

这些操作和DROP TABLE一样，只要表是原子DDL支持的存储引擎即可。而在之前版本中，这些操作执行失败可能会造成存储引擎，数据字典和binlog的不一致。

1.4.5.DROP VIEW

和DROP TABLE一样，这里不再多说。

mysql> CREATE VIEW test.viewA AS SELECT * FROM t;
mysql> DROP VIEW test.viewA, test.viewB;
ERROR 1051 (42S02): Unknown table 'test.viewB'
mysql> SHOW FULL TABLES IN test WHERE TABLE_TYPE LIKE 'VIEW';
+----------------+------------+
| Tables_in_test | Table_type |
+----------------+------------+
| viewA          | VIEW       |
+----------------+------------+

1.4.6.账户管理操作

不再允许部分执行帐户管理的操作。帐户管理语句对所有命名用户要么全成功或要么全部回滚，如果发生错误则无效。在早期的MySQL版本中，为多个用户命名的帐户管理语句可能对某些用户成功，而对其他用户则失败。

mysql> CREATE USER user1;

–user1已存在

mysql> CREATE USER user1, user2;
ERROR 1396 (HY000): Operation CREATE USER failed for 'user1'@'%'

–user2没有创建成功

mysql> SELECT User FROM mysql.user WHERE User LIKE 'user%';
+-------+
| User  |
+-------+
| user1 |
+-------+

1.5.支持的存储引擎

目前，只有InnoDB存储引擎支持原子DDL。不支持原子DDL的存储引擎不会使用DDL原子性。那么这些存储引擎的DDL操作仍然可能会造成操作中断或仅部分完成时可能发生的不一致。
为了支持DDL操作的重做和回滚，InnoDB将DDL日志写入mysql.innodb_ddl_log表，该表是存储在mysql.ibd数据字典表空间中的隐藏数据字典表中。
要在DDL操作期间查看写入mysql.innodb_ddl_log表的DDL日志，请启用innodb_print_ddl_logs配置选项。下面会详细介绍。

注：不管参数innodb_flush_log_at_trx_commit的值，只要是对mysql.innodb_ddl_log表做的更改的重做日志都会立即刷新到磁盘。 立即刷新重做日志可以避免DDL操作修改数据文件的情况，但是由这些操作产生的对mysql.innodb_ddl_log表的更改的重做日志不会持久保存到磁盘。 这种情况可能会在回滚或恢复期间导致错误。“老大要重点保护”

InnoDB存储引擎分以下几个阶段执行DDL操作：
1)准备：创建所需对象并将DDL日志写入mysql.innodb_ddl_log表。 DDL日志定义了如何前滚和回滚DDL操作。
2)执行：执行DDL操作。例如，为CREATE TABLE操作执行create例程。
3)提交：更新数据字典并提交数据字典事务。
4)DDL后收尾工作：从mysql.innodb_ddl_log表中重播并删除DDL日志。为了确保可以安全地执行回滚而不引入不一致性，在最后阶段执行文件操作，例如重命名或删除数据文件。此阶段还从mysql.innodb_dynamic_metadata数据字典表中删除DROP TABLE，TRUNCATE TABLE和其他重建表的DDL操作的动态元数据。

无论事务是提交还是回滚，DDL日志都会在Post-DDL阶段重放并从mysql.innodb_ddl_log表中删除。如果服务器在DDL操作期间暂停，则DDL日志应仅保留在mysql.innodb_ddl_log表中。在这种情况下，DDL日志将在恢复后重放并删除。
在恢复情况下，可以在重新启动服务器时提交或回滚DDL事务。如果在重做日志和二进制日志中存在在DDL操作的提交阶段期间执行的数据字典事务，则该操作被视为成功并且前滚。否则，当InnoDB重放数据字典重做日志并回滚DDL事务时，将回滚未完成的数据字典事务。

1.6.查看DDL日志

想要查看DDL日志，需要启用参数innodb_print_ddl_logs。DDL日志是存储在mysql.innodb_ddl_log数据字典中，在数据字典那一章节提到过，默认值情况下用户无法直接访问其中的数据，所以只能通过这种方式访问。DDL日志是用于重做和回滚。mysql.innodb_ddl_log的结构如下：

CREATE TABLE mysql.innodb_ddl_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  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操作使用。

下面通过一个例子介绍如果查看DDL日志
–启用innodb_print_ddl_logs变量

mysql> SET GLOBAL innodb_print_ddl_logs=1;
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL log_error_verbosity=3; （MySQL 8.0 默认为2，error log 记录Errors and warnings，不记录notes）
Query OK, 0 rows affected (0.00 sec)

–执行一个DDL语句，注意存储引擎要是InnoDB。

mysql> CREATE TABLE t1 (c1 INT) ENGINE = InnoDB;
Query OK, 0 rows affected (0.15 sec)

–改回来

mysql> SET GLOBAL log_error_verbosity=2;
Query OK, 0 rows affected (0.00 sec)

查看数据库日志输出结果：

2018-11-15T07:48:05.400549Z 8 [Note] [MY-012473] [InnoDB] InnoDB: DDL log insert : [DDL record: DELETE SPACE, id=14, thread_id=8, space_id=6, old_file_path=./lei/t4.ibd]
2018-11-15T07:48:05.400719Z 8 [Note] [MY-012478] [InnoDB] InnoDB: DDL log delete : by id 14
2018-11-15T07:48:05.420265Z 8 [Note] [MY-012477] [InnoDB] InnoDB: DDL log insert : [DDL record: REMOVE CACHE, id=15, thread_id=8, table_id=1063, new_file_path=lei/t4]
2018-11-15T07:48:05.420380Z 8 [Note] [MY-012478] [InnoDB] InnoDB: DDL log delete : by id 15
2018-11-15T07:48:05.431264Z 8 [Note] [MY-012472] [InnoDB] InnoDB: DDL log insert : [DDL record: FREE, id=16, thread_id=8, space_id=6, index_id=144, page_no=4]
2018-11-15T07:48:05.431448Z 8 [Note] [MY-012478] [InnoDB] InnoDB: DDL log delete : by id 16
2018-11-15T07:48:05.457340Z 8 [Note] [MY-012485] [InnoDB] InnoDB: DDL log post ddl : begin for thread id : 8
2018-11-15T07:48:05.457535Z 8 [Note] [MY-012486] [InnoDB] InnoDB: DDL log post ddl : end for thread id : 8

标签：log,--,mysql,InnoDB,MySQL,日志,id,DDL
From： https://blog.51cto.com/u_12946336/5722419