[轉錄] 如何在MySQL Community版本安裝audit plugin.

主透過安裝mariadb audit plugin, 讓MySQL可以記錄各種SQL 動作 : Connect, Query, Table

ref:

http://www.pkfavantedge.com/it-compliance/pci-dss-logging-in-mysql-community-version-with-mariadb-plugin/

https://mariadb.com/kb/en/the-mariadb-library/mariadb-audit-plugin-system-variables/

1.Download the latest plugin from the links given above and you should see the download directory as below. Choose the latest version. We used server_audit-1.4.0.tar.gz. in centOS. We can use the wget command that is:

wget https://downloads.mariadb.com/Audit-Plugin/MariaDB-Audit-Plugin/server_audit-1.4.0.tar.gz

2.Extract the tar file by using the command

tar -xvzf <file name>

3. Login into MySQL and locate the Plugin Directory of MY SQL using the command below

SHOW GLOBAL VARIABLES LIKE 'plugin_dir';

4.Copy the plugin to plugin directory in MySQL based on your linux server (64 bit/32 bit).

&ltcp server_audit-1.4.0/linux-x86-64/server_audit.so /usr/lib64/mysql/plugin/

&lcchown -R mysql.mysql /usr/lib64/mysql/plugin/server_audit.so

5. Install the MariaDB Audit Plugin into the MySQL Server by this command inside MySQL

&ltINSTALL PLUGIN 'plugin name' SONAME 'filename.so';

6. Once Installation is complete, we'll start the daemon with the following command in the command line:

sudo systemctl start mariadb

7.  The command systemctl doesn't display the outcome of all service management commands, so to be sure we succeed, we'll use the following command:

sudo systemctl status mariadb

If MariaDB has successfully started, the output should contain "Active: active (running)"

&lNext, let's take a moment to ensure that MariaDB starts at boot, using the systemctl enable command, which will create the necessary symlinks: sudo systemctl enable mariadb

8.Next, we'll turn our attention in configuring the syslog FormatSet the Type of Action that will be log (within MySQL)

Connect: connecting and disconnecting to/from the server will be added to the log. An unsuccessful connect will be logged as a failed connect including the error code.

Query: full statement including the values will be logged

Table: Any operation on a table triggered by query will result in an event the MariaDB Audit Plugin can catch to log it directly

SET GLOBAL server_audit_events='CONNECT, QUERY,TABLE';

You need to have root privilege to be able to change the Audit Plugin variables.  With this changed we are ready to enable the auditing, which we now will do by using the following command within MySQL:

SET GLOBAL server_audit_logging=ON;

The full set of variables is found on this page: https://mariadb.com/kb/en/mariadb/server_audit-system-variables/

To make the changes to the configuration of the MariaDB Audit Plugin permanent, we now need to add these settings to my.cnf. This ensures that the same configuration will be used after server restart.

Under [mysqld] in my.cnf, add in

server_audit_events=CONNECT, QUERY, TABLE

server_audit_logging=On