Heim Datenbank MySQL-Tutorial MySQL Audit Plugin now available in Percona Server 5.5 and 5_MySQL

MySQL Audit Plugin now available in Percona Server 5.5 and 5_MySQL

Jun 01, 2016 pm 01:15 PM

The MySQL Audit Plugin is now available for free in Percona ServerThe newPercona Server 5.5.37-35.0andPercona Server 5.6.17-65.0-56, announced yesterday (May 6), both include the open source version of the MySQL Audit Plugin. The MySQL Audit Plugin is used to log all queries or connections (“audit” MySQL usage). Until yesterday’s release, the MySQL Audit Plugin was only available inMySQL Enterprise.

Logging all MySQL usage is very important for a number of applications, for example:

  • Required: applications which deals with sensitive data (credit cards, medical records, etc); required for security compliances (i.e. HIPAA)
  • Very helpful: multi-tenants applications or MySQL as a service; MySQL administrators can audit the MySQL usage from the security and performance standpoint
  • Very helpful: investigating and troubleshooting; it is great to have a full log ofall queries, which can help a lot for troubleshooting of MySQL and even for performance audit.

Originally, the only “easy” option was toenable general log. (Other options included using binary logs which does not include select queries or enabling queries “trace” in the application or MySQL connector). However, logging all queries using a general log may dramatically decrease performance in the highly loaded MySQL applications: Aleksandr Kuzminsky published a benchmark in 2009 to showthe overhead of MySQL general and slow log. The main benefit of MySQL Log Audit plugin is that it logs all queriesasynchronously(can be changed in the config). I’ve decided to try the new audit plugin in Percona Server and measure the performance impact of the new plugin compared to enabling the general log for the CPU bound applications.

How to start with MySQL Audit Plugin

First, we will need to enable (or “install”) MySQL audit plugin asdecribed in the doc:

mysql> select version();+-------------+| version() |+-------------+| 5.5.37-35.0 |+-------------+1 row in set (0.00 sec)mysql> INSTALL PLUGIN audit_log SONAME 'audit_log.so';Query OK, 0 rows affected (0.00 sec)
Nach dem Login kopieren

mysql>selectversion();

+-------------+

|version()  |

+-------------+

|5.5.37-35.0

+-------------+

1rowinset(0.00

mysql>INSTALL PLUGINaudit_logSONAME'audit_log.so';

QueryOK,0rowsaffected

Now can see all MySQL audit plugin options:

mysql> show global variables like '%audit%';+--------------------------+--------------+| Variable_name| Value|+--------------------------+--------------+| audit_log_buffer_size| 1048576|| audit_log_file | audit.log|| audit_log_flush| OFF|| audit_log_format | OLD|| audit_log_policy | ALL|| audit_log_rotate_on_size | 0|| audit_log_rotations| 0|| audit_log_strategy | ASYNCHRONOUS |+--------------------------+--------------+8 rows in set (0.00 sec)
Nach dem Login kopieren

mysql>showglobalvariableslike'%audit%';

+--------------------------+--------------+

|Variable_name            |Value        |

+--------------------------+--------------+

|audit_log_buffer_size    |1048576      |

|audit_log_file          |audit.log    |

|audit_log_flush          |OFF          |

|audit_log_format        |OLD          |

|audit_log_policy        |ALL          |

|audit_log_rotate_on_size|0            |

|audit_log_rotations      |0            |

|audit_log_strategy      |

+--------------------------+--------------+

8rowsinset(0.00

There are a bunch of options we can tweak here, the most important for MySQL performance are:

  • audit_log_buffer_size; this buffer is used to cache the queries (for asynchronous operation).
  • audit_log_strategy; All options are listed in the documentation page:
Value Meaning
ASYNCHRONOUS Log asynchronously, wait for space in output buffer
PERFORMANCE Log asynchronously, drop request if insufficient space in output buffer
SEMISYNCHRONOUS Log synchronously, permit caching by operating system
SYNCHRONOUS Log synchronously, call sync() after each request

The most useful option in my mind is ASYNCHRONOUS, providing us with good balance between performance and not loosing transactions if the output buffer is not large enough.

  •  audit_log_policy; we can log all queries or MySQL logins only (very useful if we only need to audit MySQL connections)

Open Source Audit Plugin in MySQL Community server

You can also use Percona Open Source version of Audit Plugin in MySQL community version (5.5.37 and 5.6.17). Simply download the linux tarball of Percona Server and copy the  audit_log.so to your MySQL plugin dir.

Find plugin dir:

mysql> show global variables like '%plugin%';+---------------+------------------------------+| Variable_name | Value|+---------------+------------------------------+| plugin_dir| /usr/local/mysql/lib/plugin/ |+---------------+------------------------------+1 row in set (0.00 sec)
Nach dem Login kopieren

mysql>showglobalvariableslike'%plugin%';

+---------------+------------------------------+

|Variable_name|Value                        |

+---------------+------------------------------+

|plugin_dir    |/usr/local/mysql/lib/plugin/

+---------------+------------------------------+

1rowinset(0.00

Copy the file:

# cp audit_log.so /usr/local/mysql/lib/plugin/
Nach dem Login kopieren

# cp audit_log.so /usr/local/mysql/lib/plugin/

Install plugin:

Server version: 5.5.37 MySQL Community Server (GPL)mysql> INSTALL PLUGIN audit_log SONAME 'audit_log.so';Query OK, 0 rows affected (0.00 sec)Server version: 5.6.17 MySQL Community Server (GPL)mysql> INSTALL PLUGIN audit_log SONAME 'audit_log.so';Query OK, 0 rows affected (0.00 sec)
Nach dem Login kopieren

Serverversion:5.5.37MySQLCommunityServer(GPL)

mysql>INSTALLPLUGINaudit_logSONAME'audit_log.so';

QueryOK,0rowsaffected(0.00sec)

Serverversion:5.6.17MySQLCommunityServer(GPL)

mysql>INSTALLPLUGINaudit_logSONAME'audit_log.so';

QueryOK,0rowsaffected(0.00sec)

Using MySQL audit plugin

When plugin is enabled, it will log entries in audit.log file in XML format. Example:

<audit_record utc></audit_record><audit_record utc plugin audit_log soname localhost></audit_record><audit_record utc global variables like localhost></audit_record>
Nach dem Login kopieren
  "NAME"="Audit"

  "RECORD"="1_2014-04-30T00:04:42"

  "TIMESTAMP"="2014-04-30T00:04:42 UTC"

  "MYSQL_VERSION"="5.5.37-35.0"

  "STARTUP_OPTIONS"="--basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/lib/mysql/localhost.localdomain.pid --socket=/var/lib/mysql/mysql.sock"

  "OS_VERSION"="x86_64-Linux",

/>  "NAME"="Query"

  "RECORD"="2_2014-04-30T00:04:42"

  "TIMESTAMP"="2014-04-30T00:04:42 UTC"

  "COMMAND_CLASS"="install_plugin"

  "CONNECTION_ID"="1"

  "STATUS"="0"

  "SQLTEXT"="INSTALL PLUGIN audit_log SONAME 'audit_log.so'"

  "USER"="root[root] @ localhost []"

  "HOST"="localhost"

  "OS_USER"=""  "IP"=""/>  "NAME"="Query"

  "RECORD"="3_2014-04-30T00:04:42"

  "TIMESTAMP"="2014-04-30T00:05:07 UTC"

  "COMMAND_CLASS"="show_variables"

  "CONNECTION_ID"="1"

  "STATUS"="0"

  "SQLTEXT"="show global variables like '%audit%'"

  "USER"="root[root] @ localhost []"

  "HOST"="localhost"

  "OS_USER"=""  "IP"=""/>

<audit_record utc all on sbtest. to sb identified by localhost></audit_record><audit_record utc></audit_record><audit_record utc pad from sbtest8 where id='5036031"' localhost></audit_record>
Nach dem Login kopieren
  "NAME"="Query"

  "RECORD"="10_2014-04-30T00:04:42"

  "TIMESTAMP"="2014-04-30T12:33:20 UTC"

  "COMMAND_CLASS"="grant"

  "CONNECTION_ID"="2"

  "STATUS"="0"

  "SQLTEXT"="grant all on sbtest.* to sb@localhost identified by 'sb'"

  "USER"="root[root] @ localhost []"

  "HOST"="localhost"

  "OS_USER"=""  "IP"=""/>  "NAME"="Connect"

  "RECORD"="11_2014-04-30T00:04:42"

  "TIMESTAMP"="2014-04-30T12:34:53 UTC"

  "CONNECTION_ID"="3"

  "STATUS"="0"  "USER"="sb"

  "PRIV_USER"="sb"

  "OS_LOGIN"=""

  "PROXY_USER"=""

  "HOST"="localhost"

  "IP"=""

  "DB"="sbtest"

/>"RECORD"="1292_2014-04-30T00:04:42"

"TIMESTAMP"="2014-04-30T12:45:07 UTC"

"COMMAND_CLASS"="select"

"CONNECTION_ID"="32"

"STATUS"="1146"

"SQLTEXT"="SELECT pad FROM sbtest8 WHERE id=5036031"

"USER"="sb[sb] @ localhost []"

"HOST"="localhost"

"OS_USER"="""IP"=""/>

 Important notes: 

  • As all queries will be logged here, the passwords from “GRANT” will also be saved in clear text (as you can see above). It is very important to secure the file on disk.
  • The file can grow very large on disk:
ls -lah /var/lib/mysql/audit.log-rw-rw---- 1 mysql mysql 7.1G May 4 07:30 /var/lib/mysql/audit.log
Nach dem Login kopieren

ls-lah/var/lib/mysql/audit.log

-rw-rw----1mysqlmysql7.1GMay407:30/var/lib/mysql/audit.log

Searching the Audit Log entries

MySQL utilities provide a useful tool, mysqlauditgrep, to search / grep the logs file.  Unfortunately, I was not able to make it work (tried both v. 1.3 and v 1.4)  with audit plugin format created by Percona server. According tothis bug  it can’t parse the “new” audit format. In my case, mysqlauditgrep will return a parsing error when I use the default format and returned no results when I set the “audit_log_format=NEW”. It will be nice to use the mysqlauditgrep as it looks like a very powerful tool, but for now our searching options are limited to conventional linux grep (which is not very easy for XML documents) or custom application to parse/search XML.

Performance overhead of Audit Log Plugin and General Log 

Finally, I wanted to measure the overhead of the Audit Log Plugin compared to General Log. I did a quick benchmark withsysbenchOLTP test (CPU bound workload) with 4 modes:

  1. Audit Plugin disabled (to measure baseline)
  2. Audit Plugin enabled and logs all queries
  3. Audit Plugin enabled and logs only logins
  4. General Log enabled, Audit Plugin disabled

Here are the results:

Test Overhead
Plugin +  audit_log_policy = ALL ~15% overhead
Plugin +  audit_log_policy = LOGINS ~0% overhead (sysbench only connects once, so there may be bigger overhead here)
General_log ~62% overhead

As we can see here, audit log is not free from overhead, however, it is much smaller than enabling general_log to log all and every query. Those are quick benchmark results and more tests are need for more accurate measurements. Also, as always, your milage can vary.

Nice to have features

What I would love to have for audit plugin is the ability to log only some specific actions. For example, only log activity from a specific user or access to a specific table (i.e. a table with a sensitive data), etc. This will give more control and less overhead (=better performance).

Conclusion

The MySQL Audit Plugin is a great feature – it is a valuable tool for MySQL security and performance audits. The performance overhead may be a concern for a highly loaded systems, however, it looks reasonable and is much better than using general log to log all queries.

If you use general log or any other audit plugins, please share your experience in the comments.

Erklärung dieser Website
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn

Heißer Artikel

R.E.P.O. Energiekristalle erklärten und was sie tun (gelber Kristall)
2 Wochen vor By 尊渡假赌尊渡假赌尊渡假赌
Repo: Wie man Teamkollegen wiederbelebt
3 Wochen vor By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Abenteuer: Wie man riesige Samen bekommt
3 Wochen vor By 尊渡假赌尊渡假赌尊渡假赌

Heißer Artikel

R.E.P.O. Energiekristalle erklärten und was sie tun (gelber Kristall)
2 Wochen vor By 尊渡假赌尊渡假赌尊渡假赌
Repo: Wie man Teamkollegen wiederbelebt
3 Wochen vor By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Abenteuer: Wie man riesige Samen bekommt
3 Wochen vor By 尊渡假赌尊渡假赌尊渡假赌

Heiße Artikel -Tags

Notepad++7.3.1

Notepad++7.3.1

Einfach zu bedienender und kostenloser Code-Editor

SublimeText3 chinesische Version

SublimeText3 chinesische Version

Chinesische Version, sehr einfach zu bedienen

Senden Sie Studio 13.0.1

Senden Sie Studio 13.0.1

Leistungsstarke integrierte PHP-Entwicklungsumgebung

Dreamweaver CS6

Dreamweaver CS6

Visuelle Webentwicklungstools

SublimeText3 Mac-Version

SublimeText3 Mac-Version

Codebearbeitungssoftware auf Gottesniveau (SublimeText3)

Reduzieren Sie die Verwendung des MySQL -Speichers im Docker Reduzieren Sie die Verwendung des MySQL -Speichers im Docker Mar 04, 2025 pm 03:52 PM

Reduzieren Sie die Verwendung des MySQL -Speichers im Docker

Wie verändern Sie eine Tabelle in MySQL mit der Änderungstabelleanweisung? Wie verändern Sie eine Tabelle in MySQL mit der Änderungstabelleanweisung? Mar 19, 2025 pm 03:51 PM

Wie verändern Sie eine Tabelle in MySQL mit der Änderungstabelleanweisung?

So lösen Sie das Problem der MySQL können die gemeinsame Bibliothek nicht öffnen So lösen Sie das Problem der MySQL können die gemeinsame Bibliothek nicht öffnen Mar 04, 2025 pm 04:01 PM

So lösen Sie das Problem der MySQL können die gemeinsame Bibliothek nicht öffnen

Führen Sie MySQL in Linux aus (mit/ohne Podman -Container mit Phpmyadmin) Führen Sie MySQL in Linux aus (mit/ohne Podman -Container mit Phpmyadmin) Mar 04, 2025 pm 03:54 PM

Führen Sie MySQL in Linux aus (mit/ohne Podman -Container mit Phpmyadmin)

Was ist SQLite? Umfassende Übersicht Was ist SQLite? Umfassende Übersicht Mar 04, 2025 pm 03:55 PM

Was ist SQLite? Umfassende Übersicht

Ausführen mehrerer MySQL-Versionen auf macOS: Eine Schritt-für-Schritt-Anleitung Ausführen mehrerer MySQL-Versionen auf macOS: Eine Schritt-für-Schritt-Anleitung Mar 04, 2025 pm 03:49 PM

Ausführen mehrerer MySQL-Versionen auf macOS: Eine Schritt-für-Schritt-Anleitung

Was sind einige beliebte MySQL -GUI -Tools (z. B. MySQL Workbench, PhpMyAdmin)? Was sind einige beliebte MySQL -GUI -Tools (z. B. MySQL Workbench, PhpMyAdmin)? Mar 21, 2025 pm 06:28 PM

Was sind einige beliebte MySQL -GUI -Tools (z. B. MySQL Workbench, PhpMyAdmin)?

Wie konfiguriere ich die SSL/TLS -Verschlüsselung für MySQL -Verbindungen? Wie konfiguriere ich die SSL/TLS -Verschlüsselung für MySQL -Verbindungen? Mar 18, 2025 pm 12:01 PM

Wie konfiguriere ich die SSL/TLS -Verschlüsselung für MySQL -Verbindungen?

See all articles