Shulou(Shulou.com)05/31 Report--

This article is to share with you about the use of the MySQL5.7 audit function windows system. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

MySQL5.7 audit function windows system

The audit function of MySQL can mainly record all operations on the database, including login, connection, addition, deletion, modification and query of the table, etc., to facilitate responsibility traceability, problem search, of course, certain aspects will also affect the efficiency of the database. According to the different versions of MySQL, there are two versions: enterprise version and community version. The audit function found online is basically based on the collapse of linux system (the company is based on Windows). It took a long time to download a plug-in for windows system. This article is mainly based on the windows system.

A. Enterprise version of MySQL Enterprise Edition (fee) comes with AUDIT audit function.

B, community version of MySQL Community Server (free) need to download their own plug-ins.

There are mainly three McAfee MySQL Audit Plugin, Percona Audit Log Plugin, and MariaDB Audit Plugin plug-ins that provide audit for the community version.

Here is the main story:

MariaDB Audit Plugin

Download the corresponding version of the installation package on the MariaDB official website, obtain the corresponding .dll plug-in (linux system .so plug-in) from the installation package, copy it to your own mysql plug-in library, install the plug-in, turn on the audit function, and configure the my.ini file. The details are as follows,

1. The correspondence between MySQL and the version of MariaDB is very important, which has suffered a great loss before. The lower version is easy to cause the database to collapse. The mysql database version 5.7.21 and the MariaDB version 5.5.57 are selected.

Download path https://downloads.mariadb.org/mariadb/5.5/.

2. Obtain the corresponding mysql plug-in (server_audit.dll) from this path, in the mariadb-5.5.57-winx64\ lib\ plugin\ directory. Copy to the corresponding MySQL plug-in library C:\ Program Files\ MySQL\ MySQL Server 5.7\ lib\ plugin.

3. Log in to mysql and execute the following command to view the location of the plug-in file corresponding to the mysql data.

Mysql > SHOW GLOBAL VARIABLES LIKE 'plugin_dir'

4. Execute the following command to install the audit plug-in.

Mysql > INSTALL PLUGIN server_audit SONAME 'server_audit.dll'

5. The installation is successful. You can view the initialization parameter configuration by using the following command.

Mysql > show variables like'% audit%'

6. Execute the following command to enable mysql audit. For more information on configuration parameters, see the end of the article.

Note: specify which actions are recorded in the log file

Set global server_audit_events='CONNECT,QUERY,TABLE,QUERY_DDL'

Remarks: enable the audit function

Set global server_audit_logging=on

Note: default storage path, can not be written, default to the data file

Set global server_audit_file_path = / data/mysql/auditlogs/

Note: set the file size

Set global server_audit_file_rotate_size=200000000

Specify the number of log files. If 0, the log will never be rotated.

Set global server_audit_file_rotations=200

Force log file rotation

Set global server_audit_file_rotate_now=ON

7. After executing the above command, show variables like'% audit%'; can view the audit configuration instructions.

8. You can view the log file server_audit.log under data file. The default file path C:\ ProgramData\ MySQL\ MySQL Server 5.7\ Data; mainly depends on the location of your ProgramData directory

You can view the various operations from it, and you can find out when and what actions that user has performed, so as to facilitate accountability.

9. The parameters configured by the command line in the console only have an effect on this service, and the service restart configuration has all been initialized, so you need to add the corresponding configuration information to the my.ini file (C:\ ProgramData\ MySQL\ MySQL Server 5.7) for long-term configuration. The configuration information is as follows. If you need more parameters, you can add them yourself. It needs to be added under "mysqld", and the restart service configuration takes effect.

# remarks: prevent server_audit plug-ins from being uninstalled

Server_audit=FORCE_PLUS_PERMANENT

# Note: specify which actions are recorded in the log file

Server_audit_events='CONNECT,QUERY,TABLE,QUERY_DDL'

Server_audit_logging=on

Server_audit_file_rotate_size=200000001

Server_audit_file_rotations=200

Server_audit_file_rotate_now=ON

10. Uninstall the mysql audit plug-in and execute the following command.

Mysql > UNINSTALL PLUGIN server_audit

Mysql > show variables like'% audit%'

Empty set (0.00 sec)

Important:

Add these configurations after the server_audit plug-in is installed and already running, otherwise adding too early will easily cause the database service to fail to start normally

Parameter configuration description:

For details, please refer to: https://mariadb.com/kb/en/mariadb/server_audit-system-variables/

Server_audit_output_type: specifies the log output type, which can be SYSLOG or FILE

Server_audit_logging: turn audit on or off

Server_audit_events: specifies the type of event to be recorded. Multiple values (connect,query,table) separated by commas can be used. If query cache (query cache) is enabled, the query returns data directly from the query cache, and there will be no table record.

Server_audit_file_path: if server _ audit_output_type is FILE, use this variable to set the file to store logs. You can specify a 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: specify the number of log files. If 0, the log will never be rotated.

Server_audit_file_rotate_now: force log file rotation

Server_audit_incl_users: specifies which users' activities will be recorded. Connect will not be affected by this variable, which has a higher priority than server_audit_excl_users.

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 will be added to the syslog record

Server_audit_syslog_priority: defines the syslogd priority for logging

Server_audit_excl_users: the user behavior of this list will not be recorded and connect will not be affected by this setting

Server_audit_mode: identifies the version for development testing

Thank you for reading! This is the end of this article on "what is the use of MySQL5.7 audit function windows system". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.