Technology sharing | MySQL uses the MariaDB audit plug-in

Time:2021-3-2

Author: Yao yuan
Focus on Oracle, MySQL database for many years, Oracle 10g and 12C OCM, MySQL 5.6, 5.7, 8.0 OCP. Now, Dingjia technology works as a consultant to improve database training and technical support services for colleagues and customers.
Source: original contribution
*The original content is not allowed to be used without authorization. Please contact Xiaobian for reprint and indicate the source.

background

Oracle’s MySQL community version doesn’t come with audit plug-in. If you want to use the audit function, you can use the enterprise version, but it costs money. There are also some GPL audit plug-ins in the industry. Here we choose MariaDB audit plug-in.

1、 Migrate and install

Version 10.1 of MariaDB corresponds to MySQL 5.7 of Oracle. We can download the general version of Linux from its official website and extract 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 the server through show plugins_ Audit is the last plugin.

2、 Configuration

For the configuration of MariaDB’s audit plug-in, see: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 configuration file of MySQL

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

server_ audit_ The parameter logging is off by default. Only when this parameter is set to on can the audit function be started.
server_ audit_ Events determines the events to be recorded. Here we record connect and query, that is, record the user’s connection and query statements.

3、 Document and format of audit records

Use the following command to force switching of audit files:

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

A new audit file is generated. The number after the file name indicates the serial number of the file

[email protected]:/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 the threshold is reached, the audit record file will be automatically switched.
  • server_ audit_ file_ Rotations: determines the number of audit record files. When the 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 is recorded as a file. The default directory is the dataDir directory of MySQL, and the default file name is server_ audit.log . When it is set to syslog, audit records will be sent to the local syslogd daemon through the standard < syslog. H > API.

The format of audit record file is as follows:

[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