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.
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 |
root@deb12m8:~/8036/8036# mysql<br>Welcome to the MySQL monitor. Commands end with ; or g.<br>Your MySQL connection id is 10<br>Server version: 8.0.36 MySQL Community Server - GPL<br><br>Copyright (c) 2000, 2024, Oracle and/or its affiliates.<br><br>Oracle is a registered trademark of Oracle Corporation and/or its<br>affiliates. Other names may be trademarks of their respective<br>owners.<br><br>Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.<br><br>mysql><br> |
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 |
#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<br>root@d12m8:~# tar -tzf Percona-Server-8.0.36-28-Linux.x86_64.glibc2.35.tar.gz |grep audit_log.so<br>Percona-Server-8.0.36-28-Linux.x86_64.glibc2.35/lib/plugin/audit_log.so<br> |
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 |
#mkdir -p Percona-Server-8.0.36-28-Linux.x86_64.glibc2.35/lib/plugin/<br>root@d12m8:~# tar -xvf Percona-Server-8.0.36-28-Linux.x86_64.glibc2.35.tar.gz --wildcards --no-anchored '*audit_log.so*'<br>Percona-Server-8.0.36-28-Linux.x86_64.glibc2.35/lib/plugin/audit_log.so<br>root@d12m8:~# ls -al Percona-Server-8.0.36-28-Linux.x86_64.glibc2.35/lib/plugin/audit_log.so<br>-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<br>root@d12m8:~#<br> |
|
1 |
root@deb12m8:~/8036/8036# mysql -e"show global variables like 'plugin_dir';"<br>+---------------+------------------------+<br>| Variable_name | Value |<br>+---------------+------------------------+<br>| plugin_dir | /usr/lib/mysql/plugin/ |<br>+---------------+------------------------+<br>mysql> select * from mysql.plugin;<br>Empty set (0.01 sec)<br><br>root@deb11m8:~/8036/8036# ls -l /usr/lib/mysql/plugin/audit*<br>ls: cannot access '/usr/lib/mysql/plugin/audit*': No such file or directory<br>root@deb11m8:~/8036/8036#<br> <br> |
We do not have audit_log.so file, for now, and we need to:
|
1 |
root@deb12m8:~/8036# cp Percona-Server-8.0.36-28-Linux.x86_64.glibc2.35/lib/plugin/audit_log.so /usr/lib/mysql/plugin/<br>root@deb12m8:~/8036# ls -al /usr/lib/mysql/plugin/audit_log.so<br>-rw-r--r-- 1 root root 699416 Jun 25 22:33 /usr/lib/mysql/plugin/audit_log.so<br>root@deb11m8:~/8036#<br>mysql>install plugin audit_log soname 'audit_log.so';<br><br>root@d12m8:/usr/lib/mysql/plugin# mysql<br>Welcome to the MySQL monitor. Commands end with ; or g.<br>Your MySQL connection id is 9<br>Server version: 8.0.36 MySQL Community Server - GPL<br>Copyright (c) 2000, 2024, Oracle and/or its affiliates.<br>Oracle is a registered trademark of Oracle Corporation and/or its<br>affiliates. Other names may be trademarks of their respective<br>owners.<br>Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.<br>mysql> install plugin audit_log soname 'audit_log.so';<br>Query OK, 0 rows affected (0.31 sec)<br>mysql>select * from mysql.plugin;<br>+-----------+--------------+<br>| name | dl |<br>+-----------+--------------+<br>| audit_log | audit_log.so |<br>+-----------+--------------+<br>1 row in set (0.00 sec)<br> |
|
1 |
vi /etc/mysql/mysql.conf.d/mysqld.cnf<br><br>audit_log_policy = LOGINS<br>audit_log_format = JSON<br>audit_log_file = /var/log/mysql/audit.log<br>audit_log_rotate_on_size = 1024M<br>audit_log_rotations = 7<br>audit_log_exclude_accounts='orchestrator'@'10*'<br>plugin_dir=/usr/lib/mysql/plugin/<br>plugin-load=audit_log.so<br>#systemctl restart mysql<br>#mysql><br>Oracle is a registered trademark of Oracle Corporation and/or its<br>affiliates. Other names may be trademarks of their respective<br>owners.<br>Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.<br>mysql> show global variables like 'audit%';<br>+----------+-----------------------------------------+<br>| Variable_name | Value <br>| audit_log_buffer_size | 1048576 <br>| audit_log_exclude_commands | <br>| audit_log_exclude_databases | <br>| audit_log_exclude_accounts | orchestrator'@'10.*'| <br>| audit_log_file | /var/log/mysql/audit.log <br>| audit_log_flush | OFF <br>| audit_log_format | JSON <br>| audit_log_handler | FILE <br>| audit_log_include_accounts | <br>| audit_log_include_commands | <br>| audit_log_include_databases | <br>| audit_log_policy | LOGINS <br>| audit_log_rotate_on_size | 1073741824 <br>| audit_log_rotations | 7 <br>| audit_log_strategy | ASYNCHRONOUS <br>| audit_log_syslog_facility | LOG_USER <br>| audit_log_syslog_ident | percona-audit <br>| audit_log_syslog_priority | LOG_INFO <br>+-----------------------+----------------------------------+<br>18 rows in set (0.06 sec)<br> |
We can see the audit log has been populated.
|
1 |
root@d12m8:/usr/lib/mysql/plugin# tail -f /var/log/mysql/audit.log<br>{"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"}}<br>{"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":""}}<br>{"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":""}}<br> |
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.
Resources
RELATED POSTS
