Implementing MySQL Auditing with init_connect, MariaDB Audit Plugin, and McAfee Audit Plugin

Background MySQL Community Edition lacks a built‑in audit plugin, so administrators often rely on binlog (which does not capture login events) or general_log (which is too verbose and performance‑heavy). The article presents three ways to achieve auditing.

1. Using init_connect Parameter

The init_connect system variable executes a string of SQL statements each time a client connects. By creating an audit table and inserting the current user and host information via init_connect , simple login‑level auditing can be achieved.

Implementation Steps

Create an audit table: CREATE TABLE `test_db`.`audit` ( `ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增ID', `USER` varchar(64) COLLATE utf8mb4_bin NOT NULL COMMENT '登录用户名', `ADRESS` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '登录IP', `LOGIN_TIME` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '登录时间', PRIMARY KEY (`ID`), KEY `IDX_USER` (`USER`), KEY `IDX_HOST` (`ADRESS`), KEY `IDX_LOGIN_TIME` (`LOGIN_TIME`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='普通用户审计日志表';

Grant INSERT privilege on the audit table to all regular users. ## Example grants mysql> grant insert on test_db.audit to 'user_1'@'%'; mysql> grant insert on test_db.audit to 'user_2'@'10.186.61.17';

Set the global init_connect string: mysql> set global init_connect="insert into test_db.audit(USER,ADRESS) values(current_user(),substring_index(user(),'@',-1));"; For permanence, add the same line to [mysqld] in my.cnf .

Result Observations Only users with INSERT rights are logged; syntax errors in init_connect abort connections; SUPER users are not logged; expired‑password users fail to connect without logging.

2. MariaDB Audit Plugin

The MariaDB server_audit plugin can be used with MySQL to record detailed events such as usernames, host, executed queries, accessed tables, and variable changes.

Implementation Steps

Download the MariaDB package and locate server_audit.so .

Copy the library to MySQL’s plugin directory and set proper ownership and permissions. cp ./mariadb/.../server_audit.so /data/mysql/3306/base/lib/plugin/ chown -R mysql:mysql server_audit.so chmod 755 server_audit.so

Install the plugin: mysql> install plugin server_audit soname 'server_audit.so';

Configure global variables such as server_audit_logging , server_audit_events , server_audit_file_path , etc., to control what is logged and where. SET GLOBAL server_audit_logging=ON; SET GLOBAL server_audit_events='connect,query'; SET GLOBAL server_audit_file_path='mysql_3306_audit.log'; Persist these settings in the [server] section of my.cnf .

Result Observations The plugin logs in CSV‑like format, captures connections, queries, errors, and supports log rotation. Pros: fine‑grained control, XML‑style detail, built‑in rotation. Cons: noticeable performance impact.

3. McAfee Audit Plugin

The McAfee audit plugin provides JSON‑formatted audit logs with configurable variables for event types, objects, and user whitelists.

Implementation Steps

Download libaudit_plugin.so from the official release page.

Copy it to MySQL’s plugin directory and set ownership/permissions. cp ./audit-plugin-mysql-5.7-1.1.11-985/lib/libaudit_plugin.so /data/mysql/6666/base/lib/plugin/ chown -R mysql:mysql libaudit_plugin.so chmod 755 libaudit_plugin.so

Install the plugin: mysql> INSTALL PLUGIN AUDIT SONAME 'libaudit_plugin.so';

Configure variables such as audit_json_file , audit_json_log_file , audit_record_cmds , etc. SET GLOBAL audit_json_file=ON; SET GLOBAL audit_json_log_file='mysql_6666_audit.log'; Persist in [mysqld] of my.cnf .

Result Observations Logs are JSON objects containing detailed metadata (user, host, command, objects, status). Pros: clear JSON format, fine‑grained control, logs connection failures. Cons: some performance impact, does not record erroneous statements, lacks built‑in log rotation.

Conclusion

All three solutions enable MySQL auditing. For simple login tracking, the init_connect method is lightweight and easy. For comprehensive auditing, the MariaDB or McAfee plugins are preferable, each with its own strengths and trade‑offs. Choose based on audit requirements and test thoroughly before production deployment.