At Percona Managed Services, we manage Percona MySQL, Community MySQL, and MariaDB. Sometimes, we might need to enable audit logging and share the logs for client MySQL Community 8.0.x servers.
There are several ways to enable audit logs. One is to use the MySQL Enterprise audit logging plugin (audit_log.so), but it only supports the MySQL Enterprise version, which requires a license and is not open source.
At Percona, we prefer open source alternatives. That’s why we developed the Percona audit logging plugin, which provides monitoring and logging of connection and query activity that was performed on a specific server. The plugin supports the following log formats: XML, JSON, and CSV. This plugin is available by default if you are using the free and open source Percona Distribution for MySQL.
In this blog, we’ll show you how to install the Percona audit logging plugin on MySQL Community 8.0.x.
1. Install MySQL Community server 8.0.x
If you need to know how to install a specific version of MySQL 8 server on Debian, etc. Please check this link:
How to Install or Upgrade Percona Server for MySQL/MySQL 8 to a Specific Version on Debian/Ubuntu
In our case, we installed the MySQL Community server 8.0.36 on Debian 12.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
root@deb12m8:~/8036/8036# mysql Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 10 Server version: 8.0.36 MySQL Community Server - GPL Copyright (c) 2000, 2024, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> |
2. Get the Percona audit logging plugin from binary tarball
You can get the Percona audit logging plugin from the tarball file:
https://docs.percona.com/percona-server/8.0/binary-tarball-install.html .
For the tarball naming convention, please check this document link:
https://docs.percona.com/percona-server/8.0/binary-tarball-names.html
In our test, we need to download Percona-Server-8.0.36-28-Linux.x86_64.glibc2.35.tar.gz, and get the audit logging plugin.
|
1 2 3 |
#wget https://downloads.percona.com/downloads/Percona-Server-8.0/Percona-Server-8.0.36-28/binary/tarball/Percona-Server-8.0.36-28-Linux.x86_64.glibc2.35.tar.gz root@d12m8:~# tar -tzf Percona-Server-8.0.36-28-Linux.x86_64.glibc2.35.tar.gz |grep audit_log.so Percona-Server-8.0.36-28-Linux.x86_64.glibc2.35/lib/plugin/audit_log.so |
The Percona audit logging plugin audit_log.so file is here in the tarball:
Percona-Server-8.0.36-28-Linux.x86_64.glibc2.35/lib/plugin/audit_log.so
We can just extract this audit_log.so file as below:
|
1 2 3 4 5 6 |
#mkdir -p Percona-Server-8.0.36-28-Linux.x86_64.glibc2.35/lib/plugin/ root@d12m8:~# tar -xvf Percona-Server-8.0.36-28-Linux.x86_64.glibc2.35.tar.gz --wildcards --no-anchored '*audit_log.so*' Percona-Server-8.0.36-28-Linux.x86_64.glibc2.35/lib/plugin/audit_log.so root@d12m8:~# ls -al Percona-Server-8.0.36-28-Linux.x86_64.glibc2.35/lib/plugin/audit_log.so -rw-r--r-- 1 root root 709696 Feb 21 12:16 Percona-Server-8.0.36-28-Linux.x86_64.glibc2.35/lib/plugin/audit_log.so root@d12m8:~# |
3. Current plugin settings
|
1 2 3 4 5 6 7 8 9 10 11 12 |
root@deb12m8:~/8036/8036# mysql -e"show global variables like 'plugin_dir';" +---------------+------------------------+ | Variable_name | Value | +---------------+------------------------+ | plugin_dir | /usr/lib/mysql/plugin/ | +---------------+------------------------+ mysql> select * from mysql.plugin; Empty set (0.01 sec) root@deb11m8:~/8036/8036# ls -l /usr/lib/mysql/plugin/audit* ls: cannot access '/usr/lib/mysql/plugin/audit*': No such file or directory root@deb11m8:~/8036/8036# |
We do not have audit_log.so file, for now, and we need to:
4. Copy the Percona audit logging plugin file audit_log.so to the plugin directory and install the plugin
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
root@deb12m8:~/8036# cp Percona-Server-8.0.36-28-Linux.x86_64.glibc2.35/lib/plugin/audit_log.so /usr/lib/mysql/plugin/ root@deb12m8:~/8036# ls -al /usr/lib/mysql/plugin/audit_log.so -rw-r--r-- 1 root root 699416 Jun 25 22:33 /usr/lib/mysql/plugin/audit_log.so root@deb11m8:~/8036# mysql>install plugin audit_log soname 'audit_log.so'; root@d12m8:/usr/lib/mysql/plugin# mysql Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 9 Server version: 8.0.36 MySQL Community Server - GPL Copyright (c) 2000, 2024, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> install plugin audit_log soname 'audit_log.so'; Query OK, 0 rows affected (0.31 sec) mysql>select * from mysql.plugin; +-----------+--------------+ | name | dl | +-----------+--------------+ | audit_log | audit_log.so | +-----------+--------------+ 1 row in set (0.00 sec) |
5. Add audit parameter and restart the database service to take effect
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
vi /etc/mysql/mysql.conf.d/mysqld.cnf audit_log_policy = LOGINS audit_log_format = JSON audit_log_file = /var/log/mysql/audit.log audit_log_rotate_on_size = 1024M audit_log_rotations = 7 audit_log_exclude_accounts='orchestrator'@'10*' plugin_dir=/usr/lib/mysql/plugin/ plugin-load=audit_log.so #systemctl restart mysql #mysql> Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> show global variables like 'audit%'; +----------+-----------------------------------------+ | Variable_name | Value | audit_log_buffer_size | 1048576 | audit_log_exclude_commands | | audit_log_exclude_databases | | audit_log_exclude_accounts | orchestrator'@'10.*'| | audit_log_file | /var/log/mysql/audit.log | audit_log_flush | OFF | audit_log_format | JSON | audit_log_handler | FILE | audit_log_include_accounts | | audit_log_include_commands | | audit_log_include_databases | | audit_log_policy | LOGINS | audit_log_rotate_on_size | 1073741824 | audit_log_rotations | 7 | audit_log_strategy | ASYNCHRONOUS | audit_log_syslog_facility | LOG_USER | audit_log_syslog_ident | percona-audit | audit_log_syslog_priority | LOG_INFO +-----------------------+----------------------------------+ 18 rows in set (0.06 sec) |
We can see the audit log has been populated.
|
1 2 3 4 |
root@d12m8:/usr/lib/mysql/plugin# tail -f /var/log/mysql/audit.log {"audit_record":{"name":"Audit","record":"1_2024-06-26T04:15:10","timestamp":"2024-06-26T04:15:10Z","mysql_version":"8.0.36","startup_optionsi":"","os_version":"x86_64-Linux"}} {"audit_record":{"name":"Connect","record":"2_2024-06-26T04:15:10","timestamp":"2024-06-26T04:15:17Z","connection_id":"8","status":0,"user":"root","priv_user":"root","os_login":"","proxy_user":"","host":"localhost","ip":"","db":""}} {"audit_record":{"name":"Quit","record":"3_2024-06-26T04:15:10","timestamp":"2024-06-26T04:18:07Z","connection_id":"8","status":0,"user":"root","priv_user":"root","os_login":"","proxy_user":"","host":"localhost","ip":"","db":""}} |
For details, please refer to the docs:
https://docs.percona.com/percona-server/8.0/audit-log-plugin.html#audit_log_syslog_ident
Conclusion
I hope this is helpful for your daily MySQL and MariaDB management or if you need to enable the audit log in MySQL 8.0.* servers.
Percona offers secure, tested, open source software complete with advanced features like backup, monitoring, and encryption only otherwise found in MySQL Enterprise Edition.
Learn Why Customers Choose Percona for MySQL
About the Author
Larry XiaLarry is part of Percona's Managed Service team working as a Tier 1 MySQL DBA. Before joining Percona, Larry worked on different technologies for many years. He likes cooking and traveling.
Thanks for this post Mr. Larry Xia/Percona team.
Does this same strategy work for MySQL 5.7 ?
Hello Larry,
Can we use Percona MySQL 8.0.40 audit log plugin for all MySQL Community Edition minor versions within the 8.0 series?