mysql audit_MySQL audit

Reading guide ：

MySQL The community version doesn't have an audit function , If you want to use MySQL Audit , Consider using middleware ( for example proxysql) Or is it MariaDB The audit plug-in . Here we use MariaDB The audit plug-in for example , Realization MySQL 5.7 Audit function of .

Version information

Operating system version                ：CentOS 7.4

MySQL Database version        ：MySQL 5.7.27 Community Edition

MariaDB Audit plug-in version ：1.4.0

Note:

1.MariaDB The audit plug-in has been updated , Different versions of audit plug-ins have different functions , The functions of each version are shown in ：https://mariadb.com/kb/en/mariadb-audit-plugin-options-and-system-variables/#server_audit_file_rotate_now

2. We are giving MySQL When the database installs the audit plug-in , Need from MariaDB Copy the audit plug-in inside .MariaDB The relationship between version and audit plug-in version is as follows ：https://mariadb.com/kb/en/mariadb-audit-plugin-versions/

3.MySQL There is no special relationship between the audit plug-in version and the audit plug-in version , It is recommended to download the latest .

( One ) Download and install

Direct download MariaDB Binary installation package for , The download link is as follows ：

Once the download is complete , Unzip the installation package . And then to plugin Under the path , The path is MariaDB Where plug-ins are stored ：

mariadb-10.1.23-linux-x86_64/lib/plugin

Under this path exist  server_audit.so  file , Copy it to MySQL Server .

stay MySQL Check the plug-in storage path on the database , as follows ：

mysql> show variables like 'plugin_dir';+---------------+------------------------------+

| Variable_name | Value |

+---------------+------------------------------+

| plugin_dir | /usr/local/mysql/lib/plugin/ |

+---------------+------------------------------+

take MariaDB The audit plug-in server_audit.so Store in this path

[root@pro1 plugin]# ll

...-rw-r--r--. 1 root root 176810 Jan 7 16:17server_audit.so

...

# Modify plug-in permissions

[root@pro1 plugin]#chownmysql:mysql server_audit.so

[root@pro1 plugin]#chmod 755server_audit.so

[root@pro1 plugin]# ll

...-rwxr-xr-x. 1 mysql mysql 176810 Jan 7 16:17server_audit.so

...

Enter the database to install the plug-in

mysql> INSTALL PLUGIN server_audit SONAME 'server_audit.so';

Query OK,0 rows affected (0.00 sec)

After installation , You can see the database plug-in

mysql>show plugins ;+----------------------------+----------+--------------------+-----------------+---------+

| Name | Status | Type | Library | License |

+----------------------------+----------+--------------------+-----------------+---------+

...| SERVER_AUDIT | ACTIVE | AUDIT | server_audit.so | GPL |

+----------------------------+----------+--------------------+-----------------+---------+

( Two ) Audit configuration

Audit configuration is to modify parameter information , It's important to note that it's best to put it in MySQL Parameter file of (my.cnf), If you only make changes at the database level , Parameters will be invalid after database restart .

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 |

+-------------------------------+-----------------------+

The meaning of key parameters is listed here ：

My final configuration is as follows ：

mysql> show variables like '%audit%';+-------------------------------+-----------------------+

| Variable_name | Value |

+-------------------------------+-----------------------+

| server_audit_events | CONNECT |

| server_audit_excl_users | |

| server_audit_file_path | server_audit.log |

| server_audit_file_rotate_now | OFF |

| server_audit_file_rotate_size | 100000000 |

| server_audit_file_rotations | 9 |

| server_audit_incl_users | |

| server_audit_loc_info | |

| server_audit_logging | ON |

| 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 |

+-------------------------------+-----------------------+

( 3、 ... and ) Audit log analysis

One line represents a completed audit log , The fields in the log are separated by commas . Various events will produce different audit records , The formats of these records are different . According to audit events , It is mainly divided into 3 class ：

(3.1) Connection audit

Main audit connection database 、 disconnect 、 Connection failure and other operations , The log format is as follows ：

[timestamp],[serverhost],[username],[host],[connectionid],0,CONNECT,[database],,0[timestamp],[serverhost],[username],[host],[connectionid],0,DISCONNECT,,,0[timestamp],[serverhost],[username],[host],[connectionid],0,FAILED_CONNECT,,,[retcode]

(3.2) Query audit

Audit select sentence , The log format is as follows ：

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

(3.3) Table related audit

If the audit is on TABLE event , Will create 、 Delete 、 Rename table and other operations , The format of the log is as follows ：

[timestamp],[serverhost],[username],[host],[connectionid],[queryid],CREATE,[database],[object],

[timestamp],[serverhost],[username],[host],[connectionid],[queryid],READ,[database],[object],

[timestamp],[serverhost],[username],[host],[connectionid],[queryid],WRITE,[database],[object],

[timestamp],[serverhost],[username],[host],[connectionid],[queryid],ALTER,[database],[object],

[timestamp],[serverhost],[username],[host],[connectionid],[queryid],RENAME,[database], [object_old]|[database_new].[object_new],

[timestamp],[serverhost],[username],[host],[connectionid],[queryid],DROP,[database],[object],

【 End 】