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
作用:启用DDL日志打印,InnoDB会在执行DDL操作时将相关信息记录到MySQL的错误日志中。这将增加日志文件的大小,并可能影响性能。
适用场景:在开发和测试环境中,或者在生产环境中进行故障排查时,可以使用该设置来跟踪DDL操作的执行情况。
innodb_print_ddl_logs = OFF
作用:禁用DDL日志打印,InnoDB不会在执行DDL操作时记录相关信息到错误日志中。这将减少日志文件的大小,并可能提升性能,因为不需要为每个DDL操作写入日志。默认值OFF。
适用场景:在生产环境中,尤其是在DDL操作频繁的情况下,建议将其设置为OFF以避免日志文件过大影响性能。
演示示例
基于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。
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语句就可以得到日志打印的内容。
先在数据库中创建测试库
1 2 |
create database demo; use demo; |
执行DDL语句,以下是一些常见的DDL语句:
CREATE:用于创建新的数据库对象。
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:用于修改现有的数据库对象。
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:用于删除表中的所有记录,但保留表的结构。
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:用于重命名数据库对象。
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:用于删除数据库对象。
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
注意事项
1、参数配置:如果innodb_print_ddl_logs = ON,确保log_error_verbosity = 3,否则将不会产生DDL日志。
2、性能影响:如果innodb_print_ddl_logs = ON,可能会对性能产生一定影响,尤其是在执行大量DDL操作时,因为每个DDL操作都会记录日志信息。
3、调试用途:该参数主要用于调试和监控目的。在生产环境中,建议在需要时启用,完成后及时关闭,以减少对性能和日志管理的影响。
总结
innodb_print_ddl_logs参数能够帮助数据库管理员和开发者在需要时详细记录和审计DDL操作。开启此参数时,应考虑到潜在的性能影响和日志文件的大小,合理配置以满足特定的需求。
附,MySQL my.cnf配置文件生成器:https://dbcnf.wlnmp.com/
原文链接:MySQL innodb_print_ddl_logs参数介绍,转载请注明来源!