percona和mariadb的审计插件

最新推荐文章于 2026-05-08 05:07:46 发布

原创最新推荐文章于 2026-05-08 05:07:46 发布 · 3.3k 阅读

2 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#audit #审计 #插件 #mysql #登陆日志

MySQL 专栏收录该内容

28 篇文章

订阅专栏

本文对比了MariaDB和Percona两个MySQL分支的审计插件配置，介绍了它们的主要特性及使用方式，适合希望深入了解数据库审计机制的技术人员。

折腾了2个mysql优秀分支的审计插件，个人觉得mariadb的不错，比较直观，而percona的是类似于json格式的，可能对那些擅长文本处理的人比较合适，以下是2个插件的大概配置，其中没有对各个参数做出详细解释。如需更加详细内容，请参考官方文档。

Percona audit plugin:

mysql> INSTALL PLUGIN audit_log SONAME 'audit_log.so';

mysql> show variables like '%audit%';

+---------------------------+---------------+

|Variable_name | Value |

+---------------------------+---------------+

|audit_log_buffer_size | 1048576 |

|audit_log_file |audit.log |

|audit_log_flush | OFF |

|audit_log_format | OLD |

|audit_log_handler | FILE |

|audit_log_policy | LOGINS |

|audit_log_rotate_on_size | 0 |

|audit_log_rotations | 0 |

|audit_log_strategy |ASYNCHRONOUS |

|audit_log_syslog_facility | LOG_USER |

|audit_log_syslog_ident | percona-audit|

|audit_log_syslog_priority | LOG_INFO |

+---------------------------+---------------+

12 rowsin set (0.00 sec)

值	含义
ASYNCHRONOUS	异步日志，等待输出缓冲区空间
PERFORMANCE	异步日志，如果在输出缓冲区空间不足则降低请求
SEMISYBCHRONOUS	同步日志，使用操作系统缓存
SYNCHRONOUS	同步日志，每个请求过后都会调用sync()方法

Mariadb audit plugin:

mysql> install plugin server_audit soname 'server_audit.so';

mysql> show variables like '%audit%';

+-------------------------------+-----------------------+

|Variable_name |Value |

+-------------------------------+-----------------------+

|server_audit_events | |

|server_audit_excl_users | |

|server_audit_file_path |server_audit.log |

|server_audit_file_rotate_now | OFF |

|server_audit_file_rotate_size | 1000000 |

|server_audit_file_rotations | 9 |

|server_audit_incl_users | |

|server_audit_loc_info | |

|server_audit_logging | OFF |

|server_audit_mode | 1 |

|server_audit_output_type | file |

|server_audit_query_log_limit | 1024 |

|server_audit_syslog_facility |LOG_USER |

|server_audit_syslog_ident |mysql-server_auditing |

|server_audit_syslog_info | |

|server_audit_syslog_priority |LOG_INFO |

+-------------------------------+-----------------------+

16 rowsin set (0.00 sec)

mysql> set global server_audit_events='connect,query_ddl';

也可写在配置文件中：

[mysqld]

server_audit_events=connect,query

Type	Description	Introduced
CONNECT	Logs connects, disconnects and failed connects (including the error code).
QUERY	Queries issued and their results (in plain text), including failed queries due to syntax or permission errors.
TABLE	Which tables were affected by query execution.
QUERY_DDL	Works as the 'QUERY' value, but filters only DDL-type queries (CREATE, ALTER, etc).	MariaDB 5.5.42. MariaDB 10.0.17, MariaDB 10.1.4
QUERY_DML	Works as the 'QUERY' value, but filters only DML-type queries (INSERT, UPDATE, etc).	MariaDB 5.5.42, MariaDB 10.0.17, MariaDB 10.1.4
QUERY_DCL	Works as the 'QUERY' value, but filters only DCL-type queries (GRANT, REVOKE, etc.)	MariaDB 5.5.43, MariaDB 10.0.18, MariaDB 10.1.5

mysql> set global server_audit_logging=1;

timestamp	serverhost	username	host	connectionid	queryid	operation	database	object	retcode
20160516 15:31:18	centos-demo.ledo.com	thunder	10.237.13.148	21		QUERY		SHOW GLOBAL VARIABLES	0