首页 » db » MySQL innodb_print_ddl_logs参数介绍

MySQL innodb_print_ddl_logs参数介绍

 
文章目录

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

在执行演示前,吴昊这里补充说明下DDL,在数据库管理系统中,DDL是 “Data Definition Language”(数据定义语言)的缩写。DDL包含用于定义和修改数据库结构的SQL语句。这些语句主要用于创建、修改和删除数据库对象,如表、索引和视图。所以我们只要执行DDL语句就可以得到日志打印的内容。

先在数据库中创建测试库

执行DDL语句,以下是一些常见的DDL语句:

CREATE:用于创建新的数据库对象。

此时在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:用于修改现有的数据库对象。

此时在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:用于删除表中的所有记录,但保留表的结构。

此时在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:用于重命名数据库对象。

此时在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:用于删除数据库对象。

此时在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参数介绍,转载请注明来源!

2