Shulou(Shulou.com)05/31 Report--

The main content of this article is to explain "what is the use of MySQL8.0 's Crash Safe DDL". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "what's the use of MySQL8.0 's Crash Safe DDL"?

Preface

In previous versions of MySQL8.0, for architectural reasons, mysql used a unified frm file at the server layer to store table metadata information that could be recognized by different storage engines. In fact, innodb itself stores metadata information. This brings some challenges to ddl, because this architecture can not atomize ddl, and we can often see temporary files left under the data directory online, or errors such as the inconsistent number of columns in server and innodb layers. Even some ddl may leave the metadata in the innodb and lose the frm, making it impossible to rebuild the table. In order to solve this problem, we implemented a function called drop table force to force cleaning. .)

(all of the following discussions assume the use of the InnoDB storage engine)

By version 8.0, we know that all metadata has been uniformly managed by InnoDB, which makes it possible to implement atomic ddl. Almost all operations on innodb tables, stored procedures, triggers, views, or UDF can be atomized:

-metadata modification, binlog and innodb operations are all placed in a transaction-an internal hidden system table `mysql.innodb_ddl_ log` has been added, and the ddl operation is recorded in this table. Note that the redo generated by the operation on this table will be fsync to disk, regardless of innodb_flush_log_at_trx_commit configuration. When a crash restarts, depending on whether the transaction commits or not, it is decided to roll back or perform ddl operations through the records of this table-adding a phase of post-ddl, which is also the last phase of ddl, to: 1. True physical deletion or renaming of files; 2. Delete the entry in innodb_ddl_log; 3. For some ddl operations, it will also update its dynamic metadata information (stored in `mysql.innodb_dynamic_ metadata`, such as corrupt flag, auto_ Inc values, etc.)-after the end of a normal ddl, its ddl log should also be cleaned. If it crashes, it will try to replay: 1. If you have reached the last stage of ddl (after commit), replay ddl log and finish the ddl; 2. If you are in an intermediate state, roll back ddl

Due to the introduction of atomic ddl, the behavior of some ddl operations has also changed:

-DROP TABLE: in previous versions, T1 would be deleted if multiple tables were deleted in a drop table statement, for example, T1, T2, and T2 did not exist. But in 8.0, neither T1 nor T2 is deleted, but an error is thrown. Therefore, pay attention to the replication problem of 5.7-> 8.0 (DROP VIEW, CREATE USER also have similar problems)-DROP DATABASE: modify metadata and ddl_log commit the transaction first, while the real physically deleted data file is placed at the end, so if the file crashes when deleting the file, the drop database test will continue to be performed according to ddl_log when restarting:

MySQL has added a sweet option, innodb_print_ddl_logs. After opening it, we can see the corresponding ddl log from the error log. Let's take a look at some typical ddl processes through this.

Root@ (none) 11:12:19 > SET GLOBAL innodb_print_ddl_logs = 1; Query OK, 0 rows affected (0.00 sec) root@ (none) 11:12:22 > SET GLOBAL log_error_verbosity = 3 Query OK, 0 rows affected (0.00 sec) CREATE DATABASEmysql > CREATE DATABASE test;Query OK, 1 row affected (0.02 sec)

The database creation statement does not write log_ddl, which may not be a high-frequency operation. If the process of creating database fails, you may need to delete the directory manually after restart.

CREATE TABLEmysql > USE test;Database changedmysql > CREATE TABLE T1 (an INT PRIMARY KEY, b INT) Query OK, 0 rows affected [InnoDB] DDL log insert: [DDL record: DELETE SPACE, id=428, thread_id=7, space_id=76, old_file_path=./test/t1.ibd] [InnoDB] DDL log delete: by id 428 [InnoDB] DDL log insert: [DDL record: REMOVE CACHE, id=429, thread_id=7, table_id=1102, new_file_path=test/t1] [InnoDB] DDL log delete: by id 429 [InnoDB] DDL log insert: [DDL record: FREE, id=430, thread_id=7, space_id=76, index_id=190 Page_no=4] [InnoDB] DDL log delete: by id 430 [InnoDB] DDL log post ddl: begin for thread id: 7InnoDB] DDL log post ddl: end for thread id: 7

From the log point of view, there are three types of operations, which actually describe three reverse operations that need to be performed if the operation fails: delete the data file, release the data dictionary information in memory, and delete the index btree. Before creating the table, the data is written to the ddl_log, and after the table is created and commit, the records are deleted from the ddl log.

In addition, there is a DDL log delete log in the above log, which is actually committed by a separate transaction each time the ddl log is written, but after the commit, a delete operation is performed using the current transaction and will not be committed until the operation is over.

Instant mysql > ALTER TABLE T1 ADD COLUMN c INT;Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0 [InnoDB] DDL log post ddl: begin for thread id: 7 [InnoDB] DDL log post ddl: end for thread id: 7

Note that Instant ddl is executed here, which is a new feature supported in 8.0.13. The add operation can only modify the metadata, so there is no need to record the data from the ddl log.

Delete mysql > ALTER TABLE T1 DROP COLUMN c Query OK, 0 rows affected (2.77 sec) Records: 0 Duplicates: 0 Warnings: 0 [InnoDB] DDL log insert: [DDL record: DELETE SPACE, id=487, thread_id=7, space_id=83, old_file_path=./test/#sql-ib1108-1917598001.ibd] [InnoDB] DDL log delete: by id 487[ InnoDB] DDL log insert: [DDL record: REMOVE CACHE, id=488, thread_id=7, table_id=1109 New_file_path=test/#sql-ib1108-1917598001] [InnoDB] DDL log delete: by id 488 [InnoDB] DDL log insert: [DDL record: FREE, id=489, thread_id=7, space_id=83, index_id=200, page_no=4] [InnoDB] DDL log delete: by id 489 [InnoDB] DDL log insert: [DDL record: DROP, id=490, thread_id=7, table_id=1108] [InnoDB] DDL log insert: [DDL record: RENAME SPACE, id=491, thread_id=7, space_id=82, old_file_path=./test/#sql-ib1109-1917598002.ibd New_file_path=./test/t1.ibd] [InnoDB] DDL log delete: by id 491[ InnoDB] DDL log insert: [DDL record: RENAME TABLE, id=492, thread_id=7, table_id=1108, old_file_path=test/#sql-ib1109-1917598002, new_file_path=test/t1] [InnoDB] DDL log delete: by id 492[ InnoDB] DDL log insert: [DDL record: RENAME SPACE, id=493, thread_id=7, space_id=83, old_file_path=./test/t1.ibd New_file_path=./test/#sql-ib1108-1917598001.ibd] [InnoDB] DDL log delete: by id 493[ InnoDB] DDL log insert: [DDL record: RENAME TABLE, id=494, thread_id=7, table_id=1109, old_file_path=test/t1, new_file_path=test/#sql-ib1108-1917598001] [InnoDB] DDL log delete: by id 494[ InnoDB] DDL log insert: [DDL record: DROP, id=495, thread_id=7, table_id=1108] [InnoDB] DDL log insert: [DDL record: DELETE SPACE, id=496, thread_id=7 Space_id=82, old_file_path=./test/#sql-ib1109-1917598002.ibd] [InnoDB] DDL log post ddl: begin for thread id: 7 [InnoDB] DDL log replay: [DDL record: DELETE SPACE, id=496, thread_id=7, space_id=82, old_file_path=./test/#sql-ib1109-1917598002.ibd] [InnoDB] DDL log replay: [DDL record: DROP, id=495, thread_id=7, table_id=1108] [InnoDB] DDL log replay: [DDL record: DROP, id=490, thread_id=7 Table_id=1108] [InnoDB] DDL log post ddl: end for thread id: 7

This is a typical three-stage ddl process: it is divided into three stages: prepare, perform and commit:

Prepare: this phase modifies the metadata and creates a temporary ibd file # sql-ib1108-1917598001.ibd. If an abnormal crash occurs, we need to be able to delete this temporary file, so similar to create table, we have also written three logs for this idb: delete space, remove cache, and free btree.

Perform: perform the operation and copy the data to the above ibd file (while processing online dmllog). This part does not involve log ddl operation.

Commit: update the data dictionary information and commit the transaction. Several logs will be written here:

DROP: table_id=1108

RENAME SPACE: # sql-ib1109-1917598002.ibd file is rename into t1.ibd

RENAME TABLE: # sql-ib1109-1917598002 is transformed into T1 by rename

RENAME SPACE: t1.ibd is rename into # sql-ib1108-1917598001.ibd

RENAME TABLE: T1 table is rename into # sql-ib1108-1917598001

DROP TABLE: table_id=1108

DELETE SPACE: delete # sql-ib1109-1917598002.ibd

In fact, the ddl log written in this step describes the reverse process of commit phase operation: t1.ibd rename into # sql-ib1109-1917598002, sql-ib1108-1917598001 rename into T1 table, and finally delete the old table. The operation of deleting the old table is not performed here, but is performed in the post-ddl phase.

Post-ddl: after the transaction is committed, perform the final action: replay ddl log, delete the old files, and clean up the relevant information in the mysql.innodb_dynamic_metadata

DELETE SPACE: # sql-ib1109-1917598002.ibd

DROP: table_id=1108

DROP: table_id=1108

Indexed mysql > ALTER TABLE T1 ADD KEY (b); Query OK, 0 rows affected (0.14 sec) Records: 0 Duplicates: 0 Warnings: 0 [InnoDB] DDL log insert: [DDL record: FREE, id=431, thread_id=7, space_id=76, index_id=191, page_no=5] [InnoDB] DDL log delete: by id 431 [InnoDB] DDL log post ddl: begin for thread id: 7 [InnoDB] DDL log post ddl: end for thread id: 7

The index is created by inplace, and there are no temporary files, but if an exception occurs, it still needs to clean up the temporary index when an exception occurs, so an FREE log is added to delete the temporary index when an exception occurs.

TRUNCATE TABLEmysql > TRUNCATE TABLE T1 Query OK, 0 rows affected [InnoDB] DDL log insert: [DDL record: RENAME SPACE, id=439, thread_id=7, space_id=77, old_file_path=./test/#sql-ib1103-1917597994.ibd, new_file_path=./test/t1.ibd] [InnoDB] DDL log delete: by id 439 [InnoDB] DDL log insert: [DDL record: DROP, id=440, thread_id=7, table_id=1103] [InnoDB] DDL log insert: [DDL record: DELETE SPACE, id=441, thread_id=7, space_id=77 Old_file_path=./test/#sql-ib1103-1917597994.ibd] [InnoDB] DDL log insert: [DDL record: DELETE SPACE, id=442, thread_id=7, space_id=78, old_file_path=./test/t1.ibd] [InnoDB] DDL log delete: by id 442 [InnoDB] DDL log insert: [DDL record: REMOVE CACHE, id=443, thread_id=7, table_id=1104, new_file_path=test/t1] [InnoDB] DDL log delete: by id 443 [InnoDB] DDL log insert: [DDL record: FREE, id=444, thread_id=7 Space_id=78, index_id=194, page_no=4] [InnoDB] DDL log delete: by id 444 [InnoDB] DDL log insert: [DDL record: FREE, id=445, thread_id=7, space_id=78, index_id=195, page_no=5] [InnoDB] DDL log delete: by id 445 [InnoDB] DDL log post ddl: begin for thread id: 7 [InnoDB] DDL log replay: [DDL record: DELETE SPACE, id=441, thread_id=7, space_id=77 Old_file_path=./test/#sql-ib1103-1917597994.ibd] [InnoDB] DDL log replay: [DDL record: DROP, id=440, thread_id=7, table_id=1103] [InnoDB] DDL log post ddl: end for thread id: 7

Truncate table is a more interesting topic, in the early version 5.6 and before, it was carried out by deleting the old table to create a new table. After 5.7, in order to ensure atomicity, it was changed to an in-situ truncate file, while a truncate log file was added. If it crashes in the truncate process, you can restore truncate through this file when it crashes. By version 8.0, it has reverted to the way of deleting old tables and creating new ones. Unlike before, version 8.0 can roll back to old data in the event of a crash instead of executing it again. Taking the above as an example, it mainly includes several steps:

T1.ibd rename the table into # sql-ib1103-1917597994.ibd

Create a new file t1.ibd

Post-ddl: delete the old file # sql-ib1103-1917597994.ibd

RENAME TABLEmysql > RENAME TABLE T1 TO T2 * * query OK, 0 rows affected (0.06 sec)

DDL LOG:

[InnoDB] DDL log insert: [DDL record: RENAME SPACE, id=450, thread_id=7, space_id=78, old_file_path=./test/t2.ibd, new_file_path=./test/t1.ibd] [InnoDB] DDL log delete: by id 450 [InnoDB] DDL log insert: [DDL record: RENAME TABLE, id=451, thread_id=7, table_id=1104, old_file_path=test/t2 New_file_path=test/t1] [InnoDB] DDL log delete: by id 451 [InnoDB] DDL log post ddl: begin for thread id: 7 [InnoDB] DDL log post ddl: end for thread id: 7

This is relatively simple, only need to record the reverse operation of rename space and rename table. Post-ddl does not need to do actual operation.

DROP TABLEDROP TABLE t2 [InnoDB] DDL log insert: [DDL record: DROP, id=595, thread_id=7, table_id=1119] [InnoDB] DDL log insert: [DDL record: DELETE SPACE, id=596, thread_id=7, space_id=93, old_file_path=./test/t2.ibd] [InnoDB] DDL log post ddl: begin for thread id: 7 [InnoDB] DDL log replay: [DDL record: DELETE SPACE, id=596, thread_id=7, space_id=93, old_file_path=./test/t2.ibd] [InnoDB] DDL log replay: [DDL record: DROP Id=595, thread_id=7, table_id=1119] [InnoDB] DDL log post ddl: end for thread id: 7

First record the data that needs to be deleted in ddl log, then submit it, and then perform the real deletion of table objects and files in the final post-ddl phase.

Code implementation:

The main implementation code is concentrated in the file storage/innobase/log/log0ddl.cc, which includes the logic of inserting records and replay into the log_ DDL table.

The hidden innodb_log_ DDL table is structured as follows

Def- > add_field (0, "id", "id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT"); def- > add_field (1, "thread_id", "thread_id BIGINT UNSIGNED NOT NULL"); def- > add_field (2, "type", "type INT UNSIGNED NOT NULL"); def- > add_field (3, "space_id", "space_id INT UNSIGNED"); def- > add_field (4, "page_no", "page_no INT UNSIGNED") Def- > add_field (5, "index_id", "index_id BIGINT UNSIGNED"); def- > add_field (6, "table_id", "table_id BIGINT UNSIGNED"); def- > add_field (7, "old_file_path", "old_file_path VARCHAR" COLLATE UTF8_BIN ") Def- > add_field (8, "new_file_path", "new_file_path VARCHAR (512) COLLATE UTF8_BIN"); def- > add_index (0, "index_pk", "PRIMARY KEY (id)"); def- > add_index (1, "index_k_thread_id", "KEY (thread_id)"); record type

According to different types of operations, they can be divided into the following categories:

FREE_TREE_LOG

The purpose is to release the index btree, the entry function log_DDL::write_free_tree_log, which is called when creating the index and deleting the table

For the delete index operations involved in drop table, the insert operation of log ddl is placed in the parent transaction and either committed or rolled back together

For the indexed case, the log ddl needs to be committed separately, and the parent transaction deletes the record tag, so that if the ddl is rolled back, the remaining index can be deleted later.

DELETE_SPACE_LOG

Entry function: Log_DDL::write_delete_space_log

It is also used to record the deletion of tablespace operations in two cases:

Drop table/tablespace, the written record is committed with the parent transaction and replay in the post-ddl phase

Tablespace is created, and the written record is committed separately and deleted by the parent transaction mark. If the parent transaction rolls back, the participating tablespace is deleted through replay.

RENAME_SPACE_LOG

Entry function: Log_DDL::write_rename_space_log

Used to record rename operations, for example, if we rename table T1 into T2, we record the reverse operation T2 rename to T1.

In the function Fil_shard::space_rename (), you always write ddl log first and then do the real rename operation. The process of writing the log is also an independent transaction commit, and the parent transaction does the uncommitted delete operation.

DROP_LOG

Entry function: Log_DDL::write_drop_log

It is used to record delete table object operations. No file layer operations are involved here. Write ddl log is executed in the parent transaction.

RENAME_TABLE_LOG

Entry function: Log_DDL::write_rename_table_log

Used to record the reverse operation of the rename table object, similar to rename space, it is also an independent transaction commit ddl log, and the parent transaction flag is deleted

REMOVE_CACHE_LOG

Entry function: Log_DDL::write_remove_cache_log

Used to handle the cleanup of memory table objects, independent transaction commit, parent transaction tag deletion

ALTER_ENCRYPT_TABLESPACE_LOG

Entry function: Log_DDL::write_alter_encrypt_space_log

Used to record changes to tablespace encryption attributes, independent transaction commit. Modify the encryption tag in tablespace page0 after writing ddl log

To sum up, multiple transactions may be committed during the ddl process, which can be divided into three categories:

The independent transaction writes ddl log and commits, and the parent transaction flag is deleted. If the parent transaction commits, the ddl log is also deleted. If the parent transaction rolls back, roll back the ddl according to the reverse operation of ddl log.

Independent transactions write ddl log and commit, (currently only ALTER_ENCRYPT_TABLESPACE_LOG)

Write the ddl log using the parent transaction and commit at the end of the ddl. Need to be processed in the post-ddl phase

Post_ddl

As mentioned above, some ddl log are committed with the parent transaction, some are executed again in the post-ddl phase, and the post_ddl occurs after the parent commits or rollback: if the transaction is rolled back, the reverse operation is done according to the ddl log, and if the transaction is committed, the final really irreversible operation is done in the post-ddl phase (such as deleting the file)

Entry function: Log_DDL::post_ddl-- > Log_DDL::replay_by_thread_id

According to the thread thread id that executes the ddl, through the secondary index on the innodb_log_ DDL table, find the log id, then find its corresponding record entry on the clustered index, and then replay these operations. After completing the ddl, clean up the corresponding record.

Crash recovery

After the crash recovery ends, the ha_post_recover interface function is called, which in turn calls the function Log_DDL::recover () in innodb, which is the same replay record, and deletes the record after the end. But the ALTER_ENCRYPT_TABLESPACE_LOG type is not deleted at this step, but is added to an array ts_encrypt_ddl_records, and then resume_alter_encrypt_tablespace is called to resume the operation.

At this point, I believe you have a deeper understanding of "what is the use of MySQL8.0 's Crash Safe DDL?" you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.