mysql5.7 opens auditing

Environment

Centos 7.9
mysql 5.7.40

Download

wget "https://github.com/trellix-enterprise/mysql-audit/releases/download/v1.1.13/audit-plugin-mysql-5.7-1.1.13-1008-linux-x86_64.zip"
unzip audit-plugin-mysql-5.7-1.1.13-1008-linux-x86_64.zip

Installation

mysql> show variables like "%plugin%";
+-----------------------------------------------+--------------------------+
| Variable_name | Value |
+-----------------------------------------------+--------------------------+
| default_authentication_plugin | mysql_native_password |
| plugin_dir | /usr/lib64/mysql/plugin/ |
| replication_optimize_for_static_plugin_config | OFF |
+-----------------------------------------------+--------------------------+
3 rows in set (0.00 sec)
#Check the plugin directory and copy the plugin
cp audit-plugin-mysql-5.7-1.1.13-1008/lib/libaudit_plugin.so /usr/lib64/mysql/plugin/
#Plugin authorization
chmod +x /usr/lib64/mysql/plugin/libaudit_plugin.so
chown mysql:mysql /usr/lib64/mysql/plugin/libaudit_plugin.so
mysql> INSTALL PLUGIN AUDIT SONAME 'libaudit_plugin.so';
ERROR 1123 (HY000): Can't initialize function 'AUDIT'; Plugin initialization function failed.
#An error will be reported at this time, just configure offset

Configure offset

# which mysqld
/usr/sbin/mysqld
# bash audit-plugin-mysql-5.7-1.1.13-1008/utils/offset-extract.sh /usr/sbin/mysqld
//offsets for: /usr/sbin/mysqld (5.7.40)
{"5.7.40","024070f0adc00465dceb91f7ccd08073", 7832, 7880, 3640, 4800, 456, 360, 0, 32, 64, 160, 544, 7996, 4368, 3656, 3664, 3668, 6080, 2072, 8, 7064, 7104, 7088, 13480, 148, 672, 0},
# Configure offset to my.cnf
audit_offsets = 7832, 7880, 3640, 4800, 456, 360, 0, 32, 64, 160, 544, 7996, 4368, 3656, 3664, 3668, 6080, 2072, 8, 7064, 7104, 7088, 13480, 148, 672, 0

Configure other parameters

# Configure other parameters to my.cnf
audit_json_file = on
plugin-load=audit = libaudit_plugin.so
audit_record_cmds = 'select'
audit_json_log_file = /var/lib/mysql/mysql_audit_json_file.log

Check if it works

# Check if the plugin is enabled
mysql> show plugins;
+----------------------------+----------+--------------------+--------------------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+--------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
.............................................................................................
| AUDIT | ACTIVE | AUDIT | libaudit_plugin.so | GPL |
+----------------------------+----------+--------------------+--------------------+---------+

# View the version of the audit plug-in
mysql> show global status like '%audit%';
+------------------------+-------------+
| Variable_name | Value |
+------------------------+-------------+
| Audit_protocol_version | 1.0 |
| Audit_version | 1.1.13-1008 |
+------------------------+-------------+
2 rows in set (0.01 sec)

# View the audit log path
mysql> SHOW GLOBAL VARIABLES LIKE 'audit_json_file';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| audit_json_file | ON |
+-----------------+-------+
1 row in set (0.00 sec)

View logs

tailf /var/lib/mysql/mysql_audit_json_file.log

Uninstall plugin

You need to add audit_uninstall_plugin=1 under [mysqld] in my.cnf and restart mysql. After the restart is complete, execute UNINSTALL PLUGIN AUDIT; twice to uninstall
After uninstalling, delete audit_uninstall_plugin=1 and restart

Daily log cutting

cat > /etc/logrotate.d/audit << EOF
/var/lib/mysql/mysql_audit_json_file.log {
create 600 mysql mysql
missingok
daily
copytruncate
rotate 180
notifempty
compress
dateext
}
EOF
# Test log cutting
logrotate -f /etc/logrotate.d/audit