Turn on the log auditing function for MARIADB

If a lot of operations or development are assigned to the database operation permissions, one day the table or the field is lost can not find who did, this pot can only be transported to the back, so it is necessary to save the database operation record.

Here's how to do it:

Software version:

MariaDB10.0.17 ( comes with server_audit plugin )

MariaDBaudit log written to file

installation Server_audit Plugins

Log In MariaDB , execute:

> Show variables like '%plugin% '; View the directory where the plugin resides

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M02/82/EB/wKiom1dk6YPwqqdyAAAg_sTOgmo192.png "title=" 1.png " alt= "Wkiom1dk6ypwqqdyaaag_stogmo192.png"/>

cd/usr/local/mariadb/lib/plugin/ See if there is a call server_audit.so the file.

if not, go to Http://www.skysql.com/downloads/mariadb-audit-plugin-beta go to download and put it in the /usr/local/mariadb/lib/plugin/directory .

Installing plugins

> INSTALL PLUGIN server_audit SONAME ' server_audit.so ';

or edit /etc/my.cnf Add

[mysqld]plugin-load=server_audit=server_audit.so

and then restart mariadb .

To view related global variables

> Show variables like '%audit% ';

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M00/82/EA/wKioL1dk6aOhXP4JAABTaRqx77Q343.png "title=" 2.png " alt= "Wkiol1dk6aohxp4jaabtarqx77q343.png"/>

Audit function for turning on logs

> Set global server_audit_logging=on;

However, after the service restarts, this problem can be avoided by adding the configuration file:

[Mysqld]server_audit_logging=on

To view the running status of the audit plug-in:

> Show global status Like '%audit% ';

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M00/82/EB/wKiom1dk6eSSuN6OAAAl-4C8Os0225.png "title=" 3.png " alt= "Wkiom1dk6essun6oaaal-4c8os0225.png"/>

Server_audit_active:on (indicates that the Server_audit plugin is running);

Server_audit_current_log:server_audit.log (audit log path and log name);

Server_audit_last_error: Error message;

server_audit_writes_failed: Number of log entries not logged due to errors

Setting variables

> Set global server_audit_events= ' connect,query,table ';> set global server_audit_incl_users = ' test_user,root '; > Set GLOBAL server_audit_excl_users= ' Lirl ';   > Set globalserver_audit_file_rotate_size=200000000; (units: bytes) > Set GLOBAL server_audit_file_rotations=9;

To prevent the Server_audit plugin from being uninstalled, you need to add the following in the configuration file:

[Mysqld]

Server_audit=force_plus_permanent

Restart MySQL to take effect.

Or we directly in the/ETC/MY.CNF [MYSQLD] Paragraph add the following content, and then restart the MARIADB, the above all the messy settings can be effective, save so many steps.

# Audit Plugin settingsplugin-load = Server_audit.soserver_audit = Force_plus_permanentserver_audit_events = ' CONNECT, Query,table ' server_audit_logging = onserver_audit_incl_users = Test1,rootserver_audit_file_rotate_size =10Gserver_ Audit_file_path =/usr/local/mariadb/var/server_audit.log

After restarting Mariadb, we tail-f/usr/local/mariadb/var/server_audit.log to monitor this file.

Another Xshell terminal, login to MySQL can see the log in the refresh, such as query modification operations are recorded.

How to uninstall Server_audit:

> UNINSTALL PLUGIN server_audit;> Show variables like '%audit% '; Verify that you are finished uninstalling

Steps to uninstall:

1, need to first in the configuration file Server_audit related configuration items commented out, and then restart MARIADB.

2, then to execute uninstall PLUGIN Server_audit, to uninstall the plugin.

3, after the completion of the uninstall plug-in, execute show variables like '%audit% '; you can still see the available parameters for this plugin and restart mariadb again.

MariaDBthe audit log is written toSyslog

And the configuration method written to the log file is basically the same, that is, an explicit way to specify how the log is stored. A simple demonstration can be done.

The operation is as follows:

> INSTALL PLUGIN server_audit SONAME ' server_audit.so ';> set globalserver_audit_output_type=syslog;> set globalserver_audit_events= ' connect,query,table ';> SET GLOBAL server_audit_logging=on;> show variables like '% Audit% ';

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/82/EA/wKioL1dk6kHw6P8cAABcp352QAQ173.png "style=" float: none; "title=" 4.png "alt=" Wkiol1dk6khw6p8caabcp352qaq173.png "/>

> Show status like '%audit% ';

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M02/82/EA/wKioL1dk6kKBaRJaAAAldGJdd_4781.png "style=" float: none; "title=" 5.png "alt=" Wkiol1dk6kkbarjaaaaldgjdd_4781.png "/>

Restart Rsyslog Service

#/etc/init.d/rsyslog Restart

and then connect to MySQL perform some database and table operations, and you can tail-f/var/log/message inside See the contents of the operation.

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M02/82/EB/wKiom1dk6kLSaVJRAACoskaaKtU794.png "style=" float: none; "title=" 6.png "alt=" Wkiom1dk6klsavjraacoskaaktu794.png "/>

The default manipulation logs are played in the /var/log/message inside, not convenient for us to view, can be modified under /etc/rsyslog.conf ,

in the *.info;mail.none;authpriv.none;cron.none/var/log/messages Add a line below:

if $programname = = ' Mysql-server_auditing ' Then/var/log/mariadb_audit_log

#/etc/init.d/rsyslog Restart Restart Rsyslog service

In this case, you can export the audit log to a separate file /var/log/mariadb_audit_log inside.

( Note: The audit log is written again in /var/log/messages , written again in /var/log/mariadb_audit_log , not just in the /var/log/mariadb_audit_log )

Add:MariaDB Audit Pluginand theInit-connect+binlogCompare

1 scenario requires a user to have at least log table insert permissions, each add a new user to be authorized, it seems more troublesome, and mariadb Audit Plugin mariadb Audit Plugin

2 ), Init-connect+binlog The scenario cannot have a Super the user of the permission to conduct the audit, and MariaDB Audit Plugin conduct audits for all users, including the ability to Super permission of the user;

3 ), Init-connect+binlog The scenario needs to be restarted after modifying the configuration file MySQL effective, and MariaDB Audit Plugin can be configured online, without restarting the service to take effect;

4 ), Init-connect+binlog program Audit information output to Binlog in which MariaDB Audit Plugin you can choose to export the audit information to syslog or a custom path;

Turn on the log auditing function for MARIADB