Detailed introduction to the testing of MySQL audit plug-in (mcafee and mariadb versions)

Test server configuration: Dell R730; 24 cores; 64G memory; ssd disk.

Centos version: 6.4; MySQL version: Community 5.6.12; Test database size: 24G.

sysbench parameters: 64 threads, 10 tables, each table is pre-initialized with 10 million data, read and write mixed OLTP mode. Running on the same machine as mysql.

Test duration: 5 minutes/scenario.

The plug-in is not installed

OLTP test statistics:
queries performed:
read: 15377012
write: 4393432
other: 2196716
total: 21967160
transactions: 1098358 (3661.01 per sec.)
read/write requests: 19770444 (65898.21 per sec.)
other operations: 2196716 (7322.02 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)

Mcafee plug-in official website address: https://github.com/mcafee/mysql-audit/wiki

Use version: v1.0.9

Installation

INSTALL PLUGIN AUDIT SONAME 'libaudit_plugin.so';

Enable

set global audit_json_file=1;

Disable

set global audit_json_file=0;

Restart mysql

The plug-in will not be uninstalled and logging will not be enabled.

Uninstall

Directly execute UNINSTALL PLUGIN AUDIT; uninstallation will report an error: Uninstall AUDIT plugin disabled.

At the same time, it was found that Variable 'audit_uninstall_plugin' is a read only variable

You need to add audit_uninstall_plugin=1 to my.cnf and restart mysql.

After restarting, execute UNINSTALL PLUGIN AUDIT twice; to uninstall.

After the uninstallation is completed, audit_uninstall_plugin=1 needs to be deleted from my.cnf, otherwise an error will be reported next time mysql is started: [ERROR] /data/mysql/bin/mysqld: unknown variable 'audit_uninstall_plugin=1'

Log format: json

{"msg-type":"activity","date":"1484795122970","thread-id":"557","query-id":"61687115","user":"root","priv_user":"root","ip":"127.0.0.1","cmd":"select",
"objects":[{"db":"sysbench_test","name":"sbtest7","obj_type":"TABLE"}],"query":"SELECT c FROM sbtest7 WHERE id=5015211"}

Only logs of successful operations will be recorded

OLTP test statistics:
queries performed:
read: 8376872
write: 2393392
other: 1196696
total: 11966960
transactions: 598348 (1994.38 per sec.)
read/write requests: 10770264 (35898.81 per sec.)
other operations: 1196696 (3988.76 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)

Mariadb plug-in official website address: https://mariadb .com/kb/en/mariadb/about-the-

Using version: 1.1.7

Install

INSTALL PLUGIN server_audit SONAME 'server_audit.so';

Enable

set global server_audit_logging=1;
set global server_audit_file_rotate_size=1073741824; set global server_audit_file_rotations=4;

Disable

set global server_audit_logging=0;

Restart mysql

The plug-in will not be uninstalled and logging will not be enabled. However, all parameters will be reset, and you need to perform the required parameter configuration when you enable it again.

Uninstall

UNINSTALL PLUGIN server_audit;

Uninstalling the plug-in does not require restarting mysql.

Log format: fixed format text

20170119 10:39:19,localhost.localdomain,root,127.0.0.1,375,8330400,QUERY,sysbench_test,'SELECT c FROM sbtest5 WHERE id=5037936',0

All operations will be recorded. SQL injection can be logged.

OLTP test statistics:
queries performed:
read: 9098362
write: 2599532
other: 1299766
total: 12997660
transactions: 649883 (2166.16 per sec.)
read/write requests: 11697894 (38990.84 per sec.)
other operations: 1299766 (4332.32 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)

Summary:

mcafee’s audit plug-in:

Performance dropped by about 46%, generating log 3.0 G

Uninstalling the plug-in requires restarting mysql. The plug-in does not automatically scroll, and additional cleaning tasks need to be deployed. There is a risk of insufficient disk space due to failure of the cleaning task. It is difficult to coordinate the analysis and cleaning tasks of pulling logs to other servers, and the cleaning tasks will have a certain degree of coupling.

Mariadb audit plug-in:

The performance dropped by about 41%, generating 1864M logs.

The performance is slightly better than mcafee's plug-in. Audit logs have automatic scrolling. Easy to uninstall. In terms of compatibility, the version test of 5.6.12 is not suitable for versions 1.1.7 and above. If used, the MySQL daemon will restart mysql indefinitely. Officially, versions 1.2.0 and above must be used in MySQL 5.6.17 and above, and must be tested before use. .

Percona’s audit plug-in:

It is not adapted to versions earlier than 5.6.17 and has not been tested yet.

The above is the detailed introduction of the MySQL audit plug-in test (mcafee and mariadb versions). For more related content, please pay attention to the PHP Chinese website (www.php.cn)!