51工具盒子文章目录
innodb_print_ddl_logs参数用于控制InnoDB存储引擎在执行DDL操作时是否打印相关的日志信息。这个参数可以帮助数据库管理员跟踪和审计DDL语句的执行过程，以便于调试和性能分析。
本文基于MySQL8.4.3版本。MySQL8.4官方参考手册：https://dev.mysql.com/doc/refman/8.4/en/innodb-parameters.html#sysvar_innodb_print_ddl_logs
innodb_print_ddl_logs = ON {#title-0}
作用：启用DDL日志打印，InnoDB会在执行DDL操作时将相关信息记录到MySQL的错误日志中。这将增加日志文件的大小，并可能影响性能。
适用场景：在开发和测试环境中，或者在生产环境中进行故障排查时，可以使用该设置来跟踪DDL操作的执行情况。
innodb_print_ddl_logs = OFF {#title-1}
作用：禁用DDL日志打印，InnoDB不会在执行DDL操作时记录相关信息到错误日志中。这将减少日志文件的大小，并可能提升性能，因为不需要为每个DDL操作写入日志。默认值OFF。
适用场景：在生产环境中，尤其是在DDL操作频繁的情况下，建议将其设置为OFF以避免日志文件过大影响性能。
演示示例 {#title-2}
基于wlnmp源，安装MySQL8.4.3版本验证innodb_print_ddl_logs参数。
永久生效，修改my.cnf配置文件，使innodb_print_ddl_logs = ON。
临时生效，连接MySQL执行SET GLOBAL innodb_print_ddl_logs = ON;
连接MySQL，确保innodb_print_ddl_logs值为ON，log_error_verbosity = 3。
SHOW VARIABLES LIKE 'innodb_print_ddl_logs'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_print_ddl_logs | ON | +-----------------------+-------+ 1 row in set (0.01 sec) SHOW VARIABLES LIKE 'log_error_verbosity'; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | log_error_verbosity | 3 | +---------------------+-------+ 1 row in set (0.00 sec)
|----------------------------------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 | SHOW VARIABLES LIKE 'innodb_print_ddl_logs'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_print_ddl_logs | ON | +-----------------------+-------+ 1 row in set (0.01 sec) SHOW VARIABLES LIKE 'log_error_verbosity'; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | log_error_verbosity | 3 | +---------------------+-------+ 1 row in set (0.00 sec) |
在执行演示前，吴昊这里补充说明下DDL，在数据库管理系统中，DDL是 "Data Definition Language"（数据定义语言）的缩写。DDL包含用于定义和修改数据库结构的SQL语句。这些语句主要用于创建、修改和删除数据库对象，如表、索引和视图。所以我们只要执行DDL语句就可以得到日志打印的内容。
先在数据库中创建测试库
create database demo; use demo;
|-----|---------------------------------| | 1 2 | create database demo; use demo; |
执行DDL语句，以下是一些常见的DDL语句：
CREATE：用于创建新的数据库对象。
CREATE TABLE test_table (id INT PRIMARY KEY);
|---|-----------------------------------------------| | 1 | CREATE TABLE test_table (id INT PRIMARY KEY); |
此时在log_error日志中可以看到如下类似内容
2024-11-16T15:25:48.100731+08:00 10 [Note] [MY-012473] [InnoDB] DDL log insert : [DDL record: DELETE SPACE, id=33, thread_id=10, space_id=10, old_file_path=./demo/test_table.ibd]
2024-11-16T15:25:48.100988+08:00 10 [Note] [MY-012478] [InnoDB] DDL log delete : 33
2024-11-16T15:25:48.142090+08:00 10 [Note] [MY-012477] [InnoDB] DDL log insert : [DDL record: REMOVE CACHE, id=34, thread_id=10, table_id=1072, new_file_path=demo/test_table]
2024-11-16T15:25:48.142168+08:00 10 [Note] [MY-012478] [InnoDB] DDL log delete : 34
2024-11-16T15:25:48.143368+08:00 10 [Note] [MY-012472] [InnoDB] DDL log insert : [DDL record: FREE, id=35, thread_id=10, space_id=10, index_id=162, page_no=4]
2024-11-16T15:25:48.143424+08:00 10 [Note] [MY-012478] [InnoDB] DDL log delete : 35
2024-11-16T15:25:48.152232+08:00 10 [Note] [MY-012485] [InnoDB] DDL log post ddl : begin for thread id : 10
2024-11-16T15:25:48.152284+08:00 10 [Note] [MY-012486] [InnoDB] DDL log post ddl : end for thread id : 10
ALTER：用于修改现有的数据库对象。
ALTER TABLE test_table ADD COLUMN name VARCHAR(255);
|---|------------------------------------------------------| | 1 | ALTER TABLE test_table ADD COLUMN name VARCHAR(255); |
此时在log_error日志中可以看到如下类似内容
2024-11-16T15:27:03.736774+08:00 10 [Note] [MY-012485] [InnoDB] DDL log post ddl : begin for thread id : 10
2024-11-16T15:27:03.736895+08:00 10 [Note] [MY-012486] [InnoDB] DDL log post ddl : end for thread id : 10
TRUNCATE：用于删除表中的所有记录，但保留表的结构。
TRUNCATE TABLE test_table;
|---|----------------------------| | 1 | TRUNCATE TABLE test_table; |
此时在log_error日志中可以看到如下类似内容
2024-11-16T15:27:33.545726+08:00 10 [Note] [MY-012474] [InnoDB] DDL log insert : [DDL record: RENAME SPACE, id=36, thread_id=10, space_id=10, old_file_path=./demo/#sql-ib1072-2742343806.ibd, new_file_path=./demo/test_table.ibd]
2024-11-16T15:27:33.545816+08:00 10 [Note] [MY-012478] [InnoDB] DDL log delete : 36
2024-11-16T15:27:33.548942+08:00 10 [Note] [MY-012475] [InnoDB] DDL log insert : [DDL record: DROP, id=37, thread_id=10, table_id=1072]
2024-11-16T15:27:33.548990+08:00 10 [Note] [MY-012473] [InnoDB] DDL log insert : [DDL record: DELETE SPACE, id=38, thread_id=10, space_id=10, old_file_path=./demo/#sql-ib1072-2742343806.ibd]
2024-11-16T15:27:33.551244+08:00 10 [Note] [MY-012473] [InnoDB] DDL log insert : [DDL record: DELETE SPACE, id=39, thread_id=10, space_id=11, old_file_path=./demo/test_table.ibd]
2024-11-16T15:27:33.551286+08:00 10 [Note] [MY-012478] [InnoDB] DDL log delete : 39
2024-11-16T15:27:33.560669+08:00 10 [Note] [MY-012477] [InnoDB] DDL log insert : [DDL record: REMOVE CACHE, id=40, thread_id=10, table_id=1073, new_file_path=demo/test_table]
2024-11-16T15:27:33.560745+08:00 10 [Note] [MY-012478] [InnoDB] DDL log delete : 40
2024-11-16T15:27:33.561672+08:00 10 [Note] [MY-012472] [InnoDB] DDL log insert : [DDL record: FREE, id=41, thread_id=10, space_id=11, index_id=163, page_no=4]
2024-11-16T15:27:33.561744+08:00 10 [Note] [MY-012478] [InnoDB] DDL log delete : 41
2024-11-16T15:27:33.568147+08:00 10 [Note] [MY-012485] [InnoDB] DDL log post ddl : begin for thread id : 10
2024-11-16T15:27:33.568198+08:00 10 [Note] [MY-012479] [InnoDB] DDL log replay : [DDL record: DELETE SPACE, id=38, thread_id=10, space_id=10, old_file_path=./demo/#sql-ib1072-2742343806.ibd]
2024-11-16T15:27:33.570493+08:00 10 [Note] [MY-012479] [InnoDB] DDL log replay : [DDL record: DROP, id=37, thread_id=10, table_id=1072]
2024-11-16T15:27:33.571317+08:00 10 [Note] [MY-012486] [InnoDB] DDL log post ddl : end for thread id : 10
RENAME：用于重命名数据库对象。
RENAME TABLE test_table TO new_test_table;
|---|--------------------------------------------| | 1 | RENAME TABLE test_table TO new_test_table; |
此时在log_error日志中可以看到如下类似内容
2024-11-16T15:28:29.159905+08:00 10 [Note] [MY-012474] [InnoDB] DDL log insert : [DDL record: RENAME SPACE, id=42, thread_id=10, space_id=11, old_file_path=./demo/new_test_table.ibd, new_file_path=./demo/test_table.ibd]
2024-11-16T15:28:29.159985+08:00 10 [Note] [MY-012478] [InnoDB] DDL log delete : 42
2024-11-16T15:28:29.161600+08:00 10 [Note] [MY-012476] [InnoDB] DDL log insert : [DDL record: RENAME TABLE, id=43, thread_id=10, table_id=1073, old_file_path=demo/new_test_table, new_file_path=demo/test_table]
2024-11-16T15:28:29.161677+08:00 10 [Note] [MY-012478] [InnoDB] DDL log delete : 43
2024-11-16T15:28:29.168990+08:00 10 [Note] [MY-012485] [InnoDB] DDL log post ddl : begin for thread id : 10
2024-11-16T15:28:29.169045+08:00 10 [Note] [MY-012486] [InnoDB] DDL log post ddl : end for thread id : 10
DROP：用于删除数据库对象。
DROP TABLE new_test_table;
|---|----------------------------| | 1 | DROP TABLE new_test_table; |
此时在log_error日志中可以看到如下类似内容
2024-11-16T15:29:26.148568+08:00 10 [Note] [MY-012475] [InnoDB] DDL log insert : [DDL record: DROP, id=44, thread_id=10, table_id=1073]
2024-11-16T15:29:26.148695+08:00 10 [Note] [MY-012473] [InnoDB] DDL log insert : [DDL record: DELETE SPACE, id=45, thread_id=10, space_id=11, old_file_path=./demo/new_test_table.ibd]
2024-11-16T15:29:26.153862+08:00 10 [Note] [MY-012485] [InnoDB] DDL log post ddl : begin for thread id : 10
2024-11-16T15:29:26.153936+08:00 10 [Note] [MY-012479] [InnoDB] DDL log replay : [DDL record: DELETE SPACE, id=45, thread_id=10, space_id=11, old_file_path=./demo/new_test_table.ibd]
2024-11-16T15:29:26.164446+08:00 10 [Note] [MY-012479] [InnoDB] DDL log replay : [DDL record: DROP, id=44, thread_id=10, table_id=1073]
2024-11-16T15:29:26.165456+08:00 10 [Note] [MY-012486] [InnoDB] DDL log post ddl : end for thread id : 10
注意事项 {#title-3}
1、参数配置：如果innodb_print_ddl_logs = ON，确保log_error_verbosity = 3，否则将不会产生DDL日志。
2、性能影响：如果innodb_print_ddl_logs = ON，可能会对性能产生一定影响，尤其是在执行大量DDL操作时，因为每个DDL操作都会记录日志信息。
3、调试用途：该参数主要用于调试和监控目的。在生产环境中，建议在需要时启用，完成后及时关闭，以减少对性能和日志管理的影响。
总结 {#title-4}
innodb_print_ddl_logs参数能够帮助数据库管理员和开发者在需要时详细记录和审计DDL操作。开启此参数时，应考虑到潜在的性能影响和日志文件的大小，合理配置以满足特定的需求。
附，MySQL my.cnf配置文件生成器：https://dbcnf.wlnmp.com/
