Migrate to Percona software for MySQL – an open source, production-ready, and enterprise-grade MySQL alternative.
This blog explains how the MariaDB Auditing Plugin can help monitor database activity for security, accountability, and troubleshooting.
Auditing is essential for monitoring your database environment. It provides accountability for actions taken and data accessed, and helps deter inappropriate behavior.
Auditing also allows you to investigate suspicious activity. For example, if a user is deleting data, administrators can track connections and deletion events. You can also detect unauthorized access or privilege escalation.
MySQL 5.5.3+ provides an Audit Plugin API that allows plugins to capture events such as:
Available plugins include:
Note: Versions below 1.2 of the MariaDB plugin can be unstable and may log plaintext passwords.
The MariaDB Auditing Plugin works with MariaDB, Percona Server, and MySQL. It integrates with the MySQL audit API and is suitable when enterprise plugins are unavailable.
Download from: https://mariadb.com/products/connectors-plugins
Check plugin directory:
|
1 |
SHOW GLOBAL VARIABLES LIKE 'plugin_dir'; |
Packaging is recommended for automation and upgrades.
|
1 2 3 |
$ mkdir mariadb-server-audit-plugin-1.2.0 $ cd mariadb-server-audit-plugin-1.2.0 $ mkdir -p usr/lib/mysql/plugin |
|
1 |
$ cp /path/to/server_audit.so usr/lib/mysql/plugin |
|
1 |
$ dh_make --createorig |
|
1 |
$ cd debian/ ; rm -f *.ex |
|
1 2 |
$ echo "usr/lib/mysql/plugin/server_audit.so" > debian/install $ echo "usr/lib/mysql/plugin/server_audit.so" > debian/source/include-binaries |
|
1 |
$ dpkg-buildpackage -us -uc |
|
1 |
$ dpkg-deb -W mariadb-server-audit-plugin_1.2.0-1_amd64.deb |
|
1 |
$ service mysql stop |
|
1 |
$ dpkg -i mariadb-server-audit-plugin_1.2.0-1_amd64.deb |
|
1 2 3 4 5 |
plugin-load=server_audit=server_audit.so server_audit=FORCE_PLUS_PERMANENT server_audit_events=CONNECT,QUERY_DDL server_audit_logging=ON server_audit_excl_users='root' |
Choose one logging method:
|
1 2 3 4 5 6 7 |
# File server_audit_output_type=FILE server_audit_file_path=/var/log/mysql/audit.log # Syslog server_audit_output_type=SYSLOG server_audit_syslog_facility=LOG_LOCAL6 |
|
1 2 3 |
$ service mysql start $ mysql SHOW PLUGINS; |
|
1 |
SELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME='SERVER_AUDIT'G |
|
1 |
$ tail server_audit.log |
|
1 2 3 4 5 6 7 |
/var/log/mysql-audit.log { daily rotate 7 missingok create 640 syslog adm compress } |
The MariaDB Auditing Plugin is easy to deploy and integrates well with existing logging systems.
It helps detect access control issues, enforce audit policies, and troubleshoot performance or application problems by capturing executed SQL queries.
