###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