Author: Yao yuan
Focus on Oracle and MySQL databases for many years, Oracle 10G and 12C OCM, MySQL 5.6, 5.7, 8.0 OCP. Now Dingjia technology serves as a consultant to improve database training and technical support services for colleagues and customers.
Source: original contribution
*It is produced by aikesheng open source community. The original content cannot be used without authorization. For reprint, please contact Xiaobian and indicate the source.

background

The MySQL community version of Oracle does not have an audit plug-in. If you want to use the audit function, you can use the enterprise version, but it takes money. There are also some audit plug-ins of GPL protocol in the industry. Here we choose the audit plug-in of MariaDB.

1, Migrate and install

Version 10.1 of MariaDB corresponds to MySQL 5.7 of Oracle. We download the general version of Linux on its official website, download it and unzip it for about 1.3G:

# ll -h mariadb-10.1.46-linux-x86_64.tar 
-rw-rw-r-- 1 scutech scutech 1.3G Aug 19 18:19 mariadb-10.1.46-linux-x86_64.tar

We found the required audit plug-in:

./mariadb-10.1.46-linux-x86_64/lib/plugin/server_audit.so

Copy the so ending file to the MySQL plug-in directory, for example: / usr/lib/mysql/plugin /, and load it with the following command:

mysql>  install plugin server_audit SONAME 'server_audit.so';
mysql> show plugins;
......
| SERVER_AUDIT               | ACTIVE   | AUDIT              | server_audit.so      | GPL     |
+----------------------------+----------+--------------------+----------------------+---------+

You can see server through SHOW PLUGINS_ Audit is the last plugin.

2, Disposition

See the following for the configuration description of MariaDB's audit plug-in: https://mariadb.com/kb/en/mar...
The parameters related to the audit function are as follows:

mysql> show variables like '%audit%';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| server_audit_events           |                       |
| server_audit_excl_users       |                       |
| server_audit_file_path        | server_audit.log      |
| server_audit_file_rotate_now  | OFF                   |
| server_audit_file_rotate_size | 1000000               |
| server_audit_file_rotations   | 9                     |
| server_audit_incl_users       |                       |
| server_audit_loc_info         |                       |
| server_audit_logging          | OFF                   |
| server_audit_mode             | 1                     |
| server_audit_output_type      | file                  |
| server_audit_query_log_limit  | 1024                  |
| server_audit_syslog_facility  | LOG_USER              |
| server_audit_syslog_ident     | mysql-server_auditing |
| server_audit_syslog_info      |                       |
| server_audit_syslog_priority  | LOG_INFO              |
+-------------------------------+-----------------------+
16 rows in set (0.00 sec)

These parameter values can be SET with the SET statement:

SET GLOBAL server_audit_logging=ON;
SET global server_audit_events='connect,query';

In order to take effect after restart, you can add corresponding settings in the MySQL configuration file:

[server]
... 
server_audit_logging=ON
server_audit_events=connect,query
...

server_ audit_ The logging parameter is OFF by default. The audit function can be started only when this parameter is set to ON.
server_audit_events determines the recorded events. Here we record connect and query, that is, record the user's connection and query statements.

3, Documentation and format of audit records

Use the following command to force the switching of audit files:

mysql> set global server_audit_file_rotate_now =on;
Query OK, 0 rows affected (0.00 sec)

Generate a new audit file. The number after the file name indicates the serial number of the file:

root@infokist:/var/lib/mysql# ll server_au*
-rw-r----- 1 mysql mysql  26163 Aug 20 11:11 server_audit.log
-rw-r----- 1 mysql mysql 326651 Aug 20 11:09 server_audit.log.1

• server_audit_file_rotate_size: determines the size of each audit record file. When this threshold is reached, the audit record file will be automatically switched.
• server_audit_file_rotations: determines the number of audit record files. When this threshold is reached, the first audit record file will be overwritten. The default value is 9.
• server_audit_output_type: when it is set to file, it will be recorded as a file. The default directory is the datadir directory of MySQL, and the default file name is server_audit.log. When set to syslog, the audit record will pass the standard < syslog h> The API is sent to the local syslogd daemon.

The format of audit record file is:

[timestamp],[serverhost],[username],[host],[connectionid],[queryid],[operation],[database],[object],[retcode]

A corresponding example is as follows:

20200820 11:04:04,infokist,superuser,localhost,23,4759,QUERY,ds_db,'select count(*) from vm_zfs_storage',0