1. Mysql5.7 configuration file my.ini configuration example
[mysql] #Set the mysql client default character set default-character-set=utf8 [mysqld] #Set the mysql port number and adjust it to the non-default port 3306 port=3807 #Set to the program installation directory of mysql basedir="C:\Program Files\MySQL\MySQL Server 5.7" #Set the storage directory for the data of the mysql database datadir="D:\ProgramData\MySQL\MySQL Server 5.7\data" #Maximum number of connections allowed max_connections=500 #Character set utf8 used by the server character-set-server=utf8 #The default storage engine that will be used when creating a new table default-storage-engine=INNODB #When the server memory is 32G, it can be set to 2G. When the server memory is 16G, it can be set to 1G. innodb_buffer_pool_size=2G #Enable binlog logging log-bin=mysql-bin #mysql5.7.3 and later versions enable binlog logs, and the server-id is set to 2 digits of year, month and day. server-id=220915 #mysql5.7 and above, use SQL_MODE to enable strict mode to ensure data integrity and consistency sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION #The string connection function does not limit the size. The configuration of -1 in mysql5.7 is no longer effective. It needs to be configured to a specific value. 18446744073709551615 is the theoretical maximum value. #group_concat_max_len=-1 group_concat_max_len = 18446744073709551615 #mysql turns on custom function trust. When mysql turns on binlog, this function must be turned on when restoring a custom function. log_bin_trust_function_creators=1 #Set the mysql event to the enabled state and set it when using it #event_scheduler=ON #When restoring the database, configure the maximum size of a statement insert into tablename values (...) max_allowed_packet=32M #When the mysql engine is innodb, the lock table waiting time is adjusted to 300 seconds, and the default is 50 seconds. innodb_lock_wait_timeout=300 #Skip password verification #skip-grant-tables #Timeout for login connection wait_timeout= 1200 #If the client has no new query request for more than 300 seconds, it will automatically disconnect from the client and control the data of long-term idle links. interactive_timeout=300 #Set the maximum time to wait to acquire a lock to 300 seconds. When exceeded, the MySQL server will automatically give up waiting and return an error message to avoid long lock waiting. lock_wait_timeout=300 After #stop slave, no stop information from the main service is received for more than 300 seconds, and the log continues to be copied. By default, the value of rpl_stop_slave_timeout is 31536000 seconds (i.e. 1 year) #rpl_stop_slave_timeout=300 #Indicates that select ... into outfile and load data [local]infile are not allowed to be used for data import and export operations. local-infile=0 #Disable symbolic links, data can only be stored in the datadir directory, and mysql5.5.8 and above are set to 0 symbolic-links=0 #Load identity authentication: password complexity verification plug-in plugin-load-add=validate_password.dll #The server loads the above plug-in when it starts and prevents the plug-in from being deleted while the server is running. validate-password=FORCE_PLUS_PERMANENT #Guarantee the length of user password validate_password_length=8 #The number of digits in the password validate_password_number_count=1 #The number of uppercase and lowercase alphabetic characters in the password validate_password_mixed_case_count=1 #The number of special characters in the password validate_password_special_char_count=1 #Change your password at least once every 90 days #default_password_lifetime = 90 #Load log audit function plug-in plugin-load-add=server_audit.dll #The server loads the above plug-in when it starts and prevents the plug-in from being deleted while the server is running. server_audit=FORCE_PLUS_PERMANENT #Enable audit log function server_audit_logging=on #The default storage path of the audit log does not need to be written. It defaults to the data file. #server_audit_file_path="D:/ProgramData/MySQL/MySQL Server 5.7/data/audit.log" #Set the audit log file size. Default is 1000000, 1073741824=1GB server_audit_file_rotate_size=1073741824 #The number of audit logs to store (delete circularly after reaching the maximum number) server_audit_file_rotations=20 #Enable mysql debugging log #general_log = 1 #Set the mysql debug log storage location #general_log_file="D:/ProgramData/MySQL/MySQL Server 5.7/data/general.log" [mysqld_safe] log_error="D:/ProgramData/MySQL/MySQL Server 5.7/data/log.txt"
2. Related plug-ins
When turning on identity authentication and audit logs, corresponding plug-ins are required. The corresponding plug-ins need to be uploaded to the “C:\Program Files\MySQL\MySQL Server 5.7\lib\plugin” directory of Mysql
Identity authentication plug-in: validate_password.dll
Audit log plug-in: server_audit.dll
Download the plug-in corresponding to Mysql5.7: https://download.csdn.net/download/XiaoXiao_RenHe/88518078?spm=1001.2014.3001.5503
3. Description of audit log settings
server_audit_output_type: Specifies the log output type, which can be SYSLOG or FILE
server_audit_logging: enable or disable auditing
server_audit_events: Specifies the type of recorded events, which can be multiple values separated by commas (connect, query, table). If the query cache (query cache) is turned on, the query returns data directly from the query cache, and there will be no table records.
server_audit_file_path: If server_audit_output_type is FILE, use this variable to set the file to store the log. You can specify the directory. By default, it is stored in the server_audit.log file in the data directory.
server_audit_file_rotate_size: Limit the size of log files
server_audit_file_rotations: Specifies the number of log files. If it is 0, the log will never be rotated.
server_audit_file_rotate_now: Force log file rotation
server_audit_incl_users: Specify which users’ activities will be recorded, connect will not be affected by this variable, this variable has a higher priority than server_audit_excl_users
server_audit_syslog_facility: Default is LOG_USER, specify facility
server_audit_syslog_ident: Set the 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: Define the syslogd priority for logging
server_audit_excl_users: User behaviors in this list will not be recorded, and connect will not be affected by this setting.
server_audit_mode: identifies the version, used for development and testing
Key points server_audit_events
If the value is omitted, all event types will be recorded in the audit log. You can select the event type to record.
Such as server_audit_events = query,table,query_ddl,query_dml
Event type
CONNECT: Connects, disconnects, and failed connections, including error codes
QUERY: A query executed as plain text and its results, including queries that failed due to syntax or permission errors
TABLE: Table affected by query execution
QUERY_DDL: Same as QUERY, but only filters DDL type queries (create, alter, drop, rename, and truncate statements, except create/drop[procedure/function/user] and rename user (which are not DDL )
QUERY_DML: Same as QUERY, but only filters DML type queries (do, call, load data/xml, delete, insert, select, update, handler and replace statements)
QUERY_DCL: Same as QUERY, but only filters DCL type queries (create user, drop user, rename user, grant, revoke and set password statements)
QUERY_DML_NO_SELECT: Same as QUERY_DML, but SELECT queries are not logged. (Starting from version 1.4.4) (do, call, load data/xml, delete, insert, update, handler and replace statements)
Note
1. If the query cache is enabled and the query returns data directly from the query cache, there will be no table records.
2. Since there are other types of queries besides DDL and DML, using the Query DDL and Query DML options together is not equivalent to using queries. Starting with version 1.3.0 of the Audit plugin, there is a QUERY_DCL option for logging DCL types of queries (such as GRANT and REVOKE statements). In the same version, the server_audit_query_log_limit variable was added to be able to set the length of the log records. Previously, log entries would be truncated due to long query strings.
Part 3 Reference Materials
Mysql5.7 installation server_audit audit_server_audit_events-CSDN blog
The knowledge points of the article match the official knowledge files, and you can further learn related knowledge. MySQL entry skill treeSQL advanced skillsCTE and recursive query 77787 people are learning the system