一、下载audit插件

由于社区版不带审计插件（Audit Plugin），我们可以安装MariaDB的插件。
我们可以百度一下你安装的MySQL版本相当于MariaDB的哪个版本？然后下载相对应的MariaDB版本。我们这里以MySQL5.7.30版本为例，这个版本相当于MariaDB 10.2的版本，在MariaDB 10.2版本中找一个靠后的版本，我这里下载的是MariaDB 10.2.38.

下载地址：https://repo.huaweicloud.com/mariadb/mariadb-10.2.38/bintar-linux-x86_64/mariadb-10.2.38-linux-x86_64.tar.gz
这个包有点大，可以先下载到本地然后再上传到服务器。

二、安装audit插件

2.1、查看当前MySQL的插件安装目录

mysql> show variables like "%plugin_dir%";
+---------------+--------------------------+
| Variable_name | Value                    |
+---------------+--------------------------+
| plugin_dir    | /usr/lib64/mysql/plugin/ |
+---------------+--------------------------+
1 row in set (0.00 sec)

mysql>

MySQL插件安装目录为/usr/lib64/mysql/plugin/

2.2、将MariaDB插件server_audit.so，拷贝到MySQL的plugin安装目录

可以用install命令，也可以直接cp一份到plugin安装目录，然后给与执行权限。

[root@rshine src]# cd mariadb-10.2.38-linux-x86_64                    # 切换到解压后的mariadb安装包目录
[root@rshine mariadb-10.2.38-linux-x86_64]# ls
bin  COPYING  CREDITS  data  include  INSTALL-BINARY  lib  man  mysql-test  README.md  README-wsrep  scripts  share  sql-bench  support-files  THIRDPARTY
[root@rshine mariadb-10.2.38-linux-x86_64]# install lib/plugin/server_audit.so /usr/lib64/mysql/plugin/
[root@rshineg mariadb-10.2.38-linux-x86_64]# ls /usr/lib64/mysql/plugin/
adt_null.so                         connection_control.so  ha_example.so     keyring_udf.so     locking_service.so  mysqlx.so           semisync_master.so  validate_password.so
authentication_ldap_sasl_client.so  debug                  innodb_engine.so  libmemcached.so    mypluglib.so        rewrite_example.so  semisync_slave.so   version_token.so
auth_socket.so                      group_replication.so   keyring_file.so   libpluginmecab.so  mysql_no_login.so   rewriter.so         server_audit.so
[root@rshine mariadb-10.2.38-linux-x86_64]#

2.3、安装 server_audit.so插件

mysql> install plugin server_audit SONAME  server_audit.so ;
Query OK, 0 rows affected (0.00 sec)

mysql> show plugins;
+----------------------------+----------+--------------------+----------------------+---------+
| Name                       | Status   | Type               | Library              | License |
+----------------------------+----------+--------------------+----------------------+---------+
| SERVER_AUDIT               | ACTIVE   | AUDIT              | server_audit.so      | GPL     |
+----------------------------+----------+--------------------+----------------------+---------+
46 rows in set (0.00 sec)

mysql>

2.4、查看audit配置

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)

mysql>

2.5、配置audit永久有效

在配置文件中配置audit，使得MySQL启动时自动加载audit。在MySQL配置文件my.cnf中[mysqld]下添加以下配置

# audit log                                          # 注释以下为audit配置
server_audit_logging=ON             # 随MySQL启动自动加载
server_audit_events=connect,table,query_ddl,query_dcl,query_dml_no_select    # 指定要记录SQL语句，除查询语句外
#server_audit_events=connect,query,table,query_ddl,query_dml,query_dcl,query_dml_no_select
server_audit_file_rotate_now=ON            # 开启日志轮转
server_audit_file_rotate_size=100000000        # 日志文件达到100M就轮转
server_audit_file_path=/data0/logs/logs_audit/server_audit.log        # 审计日志文件存储路径

重启MySQL，使得配置文件生效。

查看最终生效的配置

mysql> show variables like "%audit%";
+-------------------------------+-------------------------------------------------------+
| Variable_name                 | Value                                                 |
+-------------------------------+-------------------------------------------------------+
| server_audit_events           | CONNECT,TABLE,QUERY_DDL,QUERY_DCL,QUERY_DML_NO_SELECT |
| server_audit_excl_users       |                                                       |
| server_audit_file_path        | /data0/logs/logs_audit/server_audit.log               |
| server_audit_file_rotate_now  | ON                                                    |
| 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                                              |
+-------------------------------+-------------------------------------------------------+
16 rows in set (0.01 sec)

mysql>