MySQL 5.7 Operational audits

MySQL 5.7 Operational audits

Note: PERCONA,MCAFEE,MARIADB has an audit plugin available

1. Download

#wget https://downloads.mariadb.org/interstitial/mariadb-10.1.21/bintar-linux-x86_64/mariadb-10.1.21-linux-x86_64.tar.gz

2. Unzip the #tar-xf/mariadb-10.1.21-linux-x86_64.tar.gz and locate the server_audit.so (typically in the/lib/plugin directory) Copy the server_audit.so to the Mysql/lib/plugin directory under the MySQL installation directory and pay attention to the permissions issue

#cp server_audit.so mysql/lib/plugin#chown mysql:mysql server_audit.so #修改属主和数组 #chmod 755 server_audit.so

3. Installing plugins

Mysql>install plugin server_audit soname ' server_audit.so '; Query OK, 0 rows affected, 1 warning (1.72 sec)

4. View plug-in status

Mysql> SELECT * from mysql.plugin;+--------------+-----------------+| name | DL |+--------------+-----------------+| Server_audit | server_audit.so |+--------------+-----------------+1 row in Set (0.01 sec) mysql>show Global variables like '%server_ Audit% ';

omitted here:

Parameter description:

Server_audit_output_type: Specifies the log output type, which can be a syslog or file

Server_audit_logging: Start or close an audit

Server_audit_events: Specifies the type of event to log, multiple values (connect,query,table) that can be separated by commas, and if query cache is turned on, the query returns data directly from the query cache without table records

Server_audit_file_path: If Server_audit_output_type is a file, use this variable to set the files that store the log, you can specify the directory, which is stored in the Server_audit.log file of the data directory by default.

Server_audit_file_rotate_size: Limit the size of log files

Server_audit_file_rotations: Specifies the number of log files that will never be rotated if the journal is 0 days

Server_audit_file_rotate_now: Force log file rotation

Server_audit_incl_users: Specifies which user's activity will be logged, and connect will not be affected by this variable, which is higher than the server_audit_excl_users priority

Server_audit_syslog_facility: Default is Log_user, specify facility

Server_audit_syslog_ident: Set ident as part of each syslog record

Server_audit_syslog_info: The specified info string is added to the syslog record

Server_audit_syslog_priority: Define SYSLOGD priority for logging

Server_audit_excl_users: User behavior for this list will not be logged and connect will not be affected by this setting

Server_audit_mode: Identity version for development testing

Start related plug-in parameters

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

Note: The default is to save the MySQL data file directory together with the name Server_audit.log

Note: Uninstalling plugins

Mysql>uninstall plugin Server_sudit;

To see if the log file has been successfully logged

# tail-f server_audit.log20170310 15:56:23,node02,root,192.168.2.71,7,6,query,, ' Set global server_audit_file_rotate _now=on ', 020170310 15:56:26,node02,root,192.168.2.71,7,7,query,, ' show global variables like \ '%server_audit%\ ', 020170310 15:58:10,node02,root,192.168.2.71,7,8,query,, ' SHOW databasles ', 106420170310 15:58:14,node02,root, 192.168.2.71,7,9,query,, ' SHOW DATABASES ', 0

5. Write the parameters to the MY.CNF configuration file, you must restart the MySQL service

Vim/etc/my.cnf

# # # # #server_audit # #

server_audit_logging=onserver_audit_events= ' Query_dml,query_ddl ' Server_audit_file_path =/var/logserver_audit_ File_rotate_size=2gserver_audit_file_rotations=30

Restart

#systemctl Restart Mysqld.service

This article is from the "10,000-hour Law" blog, be sure to keep this source http://daisywei.blog.51cto.com/7837970/1905207

MySQL 5.7 Operational audits