MySQL Audit Plugin now available in Percona Server 5.5 and 5.6

野性酷女 2022-09-20 15:17 9阅读 0赞

本文转载自：  **http://www.mysqlperformanceblog.com/2014/05/07/mysql-audit-plugin-now-available-in-percona-server-5-5-and-5-6/**

# MySQL Audit Plugin now available in Percona Server 5.5 and 5.6 #

May 7, 2014 by [ Alexander Rubin][Alexander Rubin] [13 Comments][]

[Tweet][]

[![The MySQL Audit Plugin is now available for free in Percona Server][]][The MySQL Audit Plugin is now available for free in Percona Server 1]The new [ Percona Server 5.5.37-35.0][Percona Server_5.5.37-35.0] and [ Percona Server 5.6.17-65.0-56][Percona 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 in [ MySQL Enterprise][MySQL Enterprise].

***EDIT:** * Just to be clear, this implementation is ***alternative*** to the MySQL Enterprise Audit Log Plugin. Percona [ re-implemented][re-implemented] the Audit Plugin code as GPL as Oracle’s code was closed source.

***EDIT 2:*** I should also mention: two other Open Source Audit Plugin implementations existed for a while: [McAfee MySQL Audit Plugin][] and [MariaDB Audit Plugin for MySQL][]. Both these implementation use their own audit log formats different from what Oracle’s implementation is using. Percona’s implementation is the first to be a drop-in replacement for MySQL Enterprise Audit Plugin.

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 of *all queries, *which can help a lot for troubleshooting of MySQL and even for performance audit.

Originally, the only “easy” option was to [ enable general log][enable 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 show [ the overhead of MySQL general and slow log][the overhead of MySQL general and slow log]. The main benefit of MySQL Log Audit plugin is that it logs all queries [ asynchronously][asynchronously] (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 as[ decribed in the doc][decribed in the doc]:

MySQL

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)

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div style="line-height:15px!important"> 
     <div>
       1 
     </div> 
     <div>
       2 
     </div> 
     <div>
       3 
     </div> 
     <div>
       4 
     </div> 
     <div>
       5 
     </div> 
     <div>
       6 
     </div> 
     <div>
       7 
     </div> 
     <div>
       8 
     </div> 
     <div>
       9 
     </div> 
     <div>
       10 
     </div> 
    </div> </td> 
   <td> 
    <div style="line-height:15px!important"> 
     <div>
       mysql 
      <span>&gt;</span> 
      <span> </span> 
      <span>select</span> 
      <span> </span> 
      <span>version</span>(); 
     </div> 
     <div>
       +-------------+ 
     </div> 
     <div>
       | 
      <span> </span> 
      <span>version</span>() 
      <span>&nbsp;&nbsp; </span>| 
     </div> 
     <div>
       +-------------+ 
     </div> 
     <div>
       | 
      <span> </span>5.5.37 
      <span>-</span>35.0 
      <span> </span>| 
     </div> 
     <div>
       +-------------+ 
     </div> 
     <div>
       1 
      <span> </span> 
      <span>row</span> 
      <span> </span> 
      <span>in</span> 
      <span> </span> 
      <span>set</span> 
      <span> </span>(0.00 
      <span> </span>sec) 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div>
       mysql 
      <span>&gt;</span> 
      <span> </span> 
      <span>INSTALL PLUGIN</span> 
      <span> </span> audit_log 
      <span> </span> 
      <span>SONAME</span> 
      <span> </span> 
      <span>'audit_log.so'</span>; 
     </div> 
     <div>
       Query 
      <span> </span>OK, 
      <span> </span>0 
      <span> </span> 
      <span>rows</span> 
      <span> </span>affected 
      <span> </span>(0.00 
      <span> </span>sec) 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

Now can see all MySQL audit plugin options:

MySQL

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:

<table>     
 <thead> 
  <tr> 
   <th>Value</th> 
   <th>Meaning</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code style="color:rgb(2,103,137); font-weight:bold">ASYNCHRONOUS</code></td> 
   <td>Log asynchronously, wait for space in output buffer</td> 
  </tr> 
  <tr> 
   <td><code style="color:rgb(2,103,137); font-weight:bold">PERFORMANCE</code></td> 
   <td>Log asynchronously, drop request if insufficient space in output buffer</td> 
  </tr> 
  <tr> 
   <td><code style="color:rgb(2,103,137); font-weight:bold">SEMISYNCHRONOUS</code></td> 
   <td>Log synchronously, permit caching by operating system</td> 
  </tr> 
  <tr> 
   <td><code style="color:rgb(2,103,137); font-weight:bold">SYNCHRONOUS</code></td> 
   <td>Log synchronously, call&nbsp;<code style="color:rgb(14,64,117)">sync()</code>&nbsp;after each request</td> 
  </tr> 
 </tbody> 
</table>

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

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div style="line-height:15px!important"> 
     <div>
       1 
     </div> 
     <div>
       2 
     </div> 
     <div>
       3 
     </div> 
     <div>
       4 
     </div> 
     <div>
       5 
     </div> 
     <div>
       6 
     </div> 
     <div>
       7 
     </div> 
    </div> </td> 
   <td> 
    <div style="line-height:15px!important"> 
     <div>
       mysql 
      <span>&gt;</span> 
      <span> </span> 
      <span>show</span> 
      <span> </span> 
      <span>global</span> 
      <span> </span> 
      <span>variables</span> 
      <span> </span> 
      <span>like</span> 
      <span> </span> 
      <span>'%plugin%'</span>; 
     </div> 
     <div>
       +---------------+------------------------------+ 
     </div> 
     <div>
       | 
      <span> </span>Variable_name 
      <span> </span>| 
      <span> </span> 
      <span>Value</span> 
      <span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span>| 
     </div> 
     <div>
       +---------------+------------------------------+ 
     </div> 
     <div>
       | 
      <span> </span> 
      <span>plugin_dir</span> 
      <span>&nbsp;&nbsp;&nbsp;&nbsp;</span>| 
      <span> </span>/usr 
      <span>/</span> 
      <span>local</span> 
      <span>/</span>mysql 
      <span>/</span>lib 
      <span>/</span>plugin/ 
      <span> </span>| 
     </div> 
     <div>
       +---------------+------------------------------+ 
     </div> 
     <div>
       1 
      <span> </span> 
      <span>row</span> 
      <span> </span> 
      <span>in</span> 
      <span> </span> 
      <span>set</span> 
      <span> </span>(0.00 
      <span> </span>sec) 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

Copy the file:

Shell

\# cp audit\_log.so /usr/local/mysql/lib/plugin/

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div style="line-height:15px!important"> 
     <div>
       1 
     </div> 
    </div> </td> 
   <td> 
    <div style="line-height:15px!important"> 
     <div> 
      <span># cp audit_log.so /usr/local/mysql/lib/plugin/</span> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

Install plugin:

Shell

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)

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div style="line-height:15px!important"> 
     <div>
       1 
     </div> 
     <div>
       2 
     </div> 
     <div>
       3 
     </div> 
     <div>
       4 
     </div> 
     <div>
       5 
     </div> 
     <div>
       6 
     </div> 
     <div>
       7 
     </div> 
    </div> </td> 
   <td> 
    <div style="line-height:15px!important"> 
     <div> 
      <span>Server </span> 
      <span>version</span> 
      <span>:</span> 
      <span> </span> 
      <span>5.5.37</span> 
      <span> </span> 
      <span>MySQL </span> 
      <span>Community </span> 
      <span>Server</span> 
      <span> </span> 
      <span>(</span> 
      <span>GPL</span> 
      <span>)</span> 
     </div> 
     <div> 
      <span>mysql</span> 
      <span>&gt;</span> 
      <span> </span> 
      <span>INSTALL </span> 
      <span>PLUGIN </span> 
      <span>audit_log </span> 
      <span>SONAME</span> 
      <span> </span> 
      <span>'audit_log.so'</span> 
      <span>;</span> 
     </div> 
     <div> 
      <span>Query </span> 
      <span>OK</span> 
      <span>,</span> 
      <span> </span> 
      <span>0</span> 
      <span> </span> 
      <span>rows </span> 
      <span>affected</span> 
      <span> </span> 
      <span>(</span> 
      <span>0.00</span> 
      <span> </span> 
      <span>sec</span> 
      <span>)</span> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <span>Server </span> 
      <span>version</span> 
      <span>:</span> 
      <span> </span> 
      <span>5.6.17</span> 
      <span> </span> 
      <span>MySQL </span> 
      <span>Community </span> 
      <span>Server</span> 
      <span> </span> 
      <span>(</span> 
      <span>GPL</span> 
      <span>)</span> 
     </div> 
     <div> 
      <span>mysql</span> 
      <span>&gt;</span> 
      <span> </span> 
      <span>INSTALL </span> 
      <span>PLUGIN </span> 
      <span>audit_log </span> 
      <span>SONAME</span> 
      <span> </span> 
      <span>'audit_log.so'</span> 
      <span>;</span> 
     </div> 
     <div> 
      <span>Query </span> 
      <span>OK</span> 
      <span>,</span> 
      <span> </span> 
      <span>0</span> 
      <span> </span> 
      <span>rows </span> 
      <span>affected</span> 
      <span> </span> 
      <span>(</span> 
      <span>0.00</span> 
      <span> </span> 
      <span>sec</span> 
      <span>)</span> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

**Using MySQL audit plugin**

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

XHTML

<AUDIT\_RECORD "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", /> <AUDIT\_RECORD "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"="" /> <AUDIT\_RECORD "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"="" />

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div style="line-height:15px!important"> 
     <div>
       1 
     </div> 
     <div>
       2 
     </div> 
     <div>
       3 
     </div> 
     <div>
       4 
     </div> 
     <div>
       5 
     </div> 
     <div>
       6 
     </div> 
     <div>
       7 
     </div> 
     <div>
       8 
     </div> 
     <div>
       9 
     </div> 
     <div>
       10 
     </div> 
     <div>
       11 
     </div> 
     <div>
       12 
     </div> 
     <div>
       13 
     </div> 
     <div>
       14 
     </div> 
     <div>
       15 
     </div> 
     <div>
       16 
     </div> 
     <div>
       17 
     </div> 
     <div>
       18 
     </div> 
     <div>
       19 
     </div> 
     <div>
       20 
     </div> 
     <div>
       21 
     </div> 
     <div>
       22 
     </div> 
     <div>
       23 
     </div> 
     <div>
       24 
     </div> 
     <div>
       25 
     </div> 
     <div>
       26 
     </div> 
     <div>
       27 
     </div> 
     <div>
       28 
     </div> 
     <div>
       29 
     </div> 
     <div>
       30 
     </div> 
     <div>
       31 
     </div> 
     <div>
       32 
     </div> 
     <div>
       33 
     </div> 
     <div>
       34 
     </div> 
    </div> </td> 
   <td> 
    <div style="line-height:15px!important"> 
     <div> 
      <span>&lt;AUDIT_RECORD</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"NAME"</span> 
      <span>=</span> 
      <span>"Audit"</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"RECORD"</span> 
      <span>=</span> 
      <span>"1_2014-04-30T00:04:42"</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"TIMESTAMP"</span> 
      <span>=</span> 
      <span>"2014-04-30T00:04:42 UTC"</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"MYSQL_VERSION"</span> 
      <span>=</span> 
      <span>"5.5.37-35.0"</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"STARTUP_OPTIONS"</span> 
      <span>=</span> 
      <span>"--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"</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"OS_VERSION"</span> 
      <span>=</span> 
      <span>"x86_64-Linux"</span> 
      <span>,</span> 
     </div> 
     <div> 
      <span>/&gt;</span> 
     </div> 
     <div> 
      <span>&lt;AUDIT_RECORD</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"NAME"</span> 
      <span>=</span> 
      <span>"Query"</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"RECORD"</span> 
      <span>=</span> 
      <span>"2_2014-04-30T00:04:42"</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"TIMESTAMP"</span> 
      <span>=</span> 
      <span>"2014-04-30T00:04:42 UTC"</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"COMMAND_CLASS"</span> 
      <span>=</span> 
      <span>"install_plugin"</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"CONNECTION_ID"</span> 
      <span>=</span> 
      <span>"1"</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"STATUS"</span> 
      <span>=</span> 
      <span>"0"</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"SQLTEXT"</span> 
      <span>=</span> 
      <span>"INSTALL PLUGIN audit_log SONAME 'audit_log.so'"</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"USER"</span> 
      <span>=</span> 
      <span>"root[root] @ localhost []"</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"HOST"</span> 
      <span>=</span> 
      <span>"localhost"</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"OS_USER"</span> 
      <span>=</span> 
      <span>""</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"IP"</span> 
      <span>=</span> 
      <span>""</span> 
     </div> 
     <div> 
      <span>/&gt;</span> 
     </div> 
     <div> 
      <span>&lt;AUDIT_RECORD</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"NAME"</span> 
      <span>=</span> 
      <span>"Query"</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"RECORD"</span> 
      <span>=</span> 
      <span>"3_2014-04-30T00:04:42"</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"TIMESTAMP"</span> 
      <span>=</span> 
      <span>"2014-04-30T00:05:07 UTC"</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"COMMAND_CLASS"</span> 
      <span>=</span> 
      <span>"show_variables"</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"CONNECTION_ID"</span> 
      <span>=</span> 
      <span>"1"</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"STATUS"</span> 
      <span>=</span> 
      <span>"0"</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"SQLTEXT"</span> 
      <span>=</span> 
      <span>"show global variables like '%audit%'"</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"USER"</span> 
      <span>=</span> 
      <span>"root[root] @ localhost []"</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"HOST"</span> 
      <span>=</span> 
      <span>"localhost"</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"OS_USER"</span> 
      <span>=</span> 
      <span>""</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"IP"</span> 
      <span>=</span> 
      <span>""</span> 
     </div> 
     <div> 
      <span>/&gt;</span> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

…

Shell

<AUDIT\_RECORD "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"="" /> <AUDIT\_RECORD "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" /> <AUDIT\_RECORD "NAME"="Query" "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"="" />

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div style="line-height:15px!important"> 
     <div>
       1 
     </div> 
     <div>
       2 
     </div> 
     <div>
       3 
     </div> 
     <div>
       4 
     </div> 
     <div>
       5 
     </div> 
     <div>
       6 
     </div> 
     <div>
       7 
     </div> 
     <div>
       8 
     </div> 
     <div>
       9 
     </div> 
     <div>
       10 
     </div> 
     <div>
       11 
     </div> 
     <div>
       12 
     </div> 
     <div>
       13 
     </div> 
     <div>
       14 
     </div> 
     <div>
       15 
     </div> 
     <div>
       16 
     </div> 
     <div>
       17 
     </div> 
     <div>
       18 
     </div> 
     <div>
       19 
     </div> 
     <div>
       20 
     </div> 
     <div>
       21 
     </div> 
     <div>
       22 
     </div> 
     <div>
       23 
     </div> 
     <div>
       24 
     </div> 
     <div>
       25 
     </div> 
     <div>
       26 
     </div> 
     <div>
       27 
     </div> 
     <div>
       28 
     </div> 
     <div>
       29 
     </div> 
     <div>
       30 
     </div> 
     <div>
       31 
     </div> 
     <div>
       32 
     </div> 
     <div>
       33 
     </div> 
     <div>
       34 
     </div> 
     <div>
       35 
     </div> 
     <div>
       36 
     </div> 
     <div>
       37 
     </div> 
     <div>
       38 
     </div> 
     <div>
       39 
     </div> 
     <div>
       40 
     </div> 
    </div> </td> 
   <td> 
    <div style="line-height:15px!important"> 
     <div> 
      <span>&lt;</span> 
      <span>AUDIT</span> 
      <span>_</span>RECORD 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"NAME"</span> 
      <span>=</span> 
      <span>"Query"</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"RECORD"</span> 
      <span>=</span> 
      <span>"10_2014-04-30T00:04:42"</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"TIMESTAMP"</span> 
      <span>=</span> 
      <span>"2014-04-30T12:33:20 UTC"</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"COMMAND_CLASS"</span> 
      <span>=</span> 
      <span>"grant"</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"CONNECTION_ID"</span> 
      <span>=</span> 
      <span>"2"</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"STATUS"</span> 
      <span>=</span> 
      <span>"0"</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"SQLTEXT"</span> 
      <span>=</span> 
      <span>"grant all on sbtest.* to sb@localhost identified by 'sb'"</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"USER"</span> 
      <span>=</span> 
      <span>"root[root] @ localhost []"</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"HOST"</span> 
      <span>=</span> 
      <span>"localhost"</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"OS_USER"</span> 
      <span>=</span> 
      <span>""</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"IP"</span> 
      <span>=</span> 
      <span>""</span> 
     </div> 
     <div> 
      <span>/</span> 
      <span>&gt;</span> 
     </div> 
     <div> 
      <span>&lt;</span> 
      <span>AUDIT</span> 
      <span>_</span>RECORD 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"NAME"</span> 
      <span>=</span> 
      <span>"Connect"</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"RECORD"</span> 
      <span>=</span> 
      <span>"11_2014-04-30T00:04:42"</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"TIMESTAMP"</span> 
      <span>=</span> 
      <span>"2014-04-30T12:34:53 UTC"</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"CONNECTION_ID"</span> 
      <span>=</span> 
      <span>"3"</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"STATUS"</span> 
      <span>=</span> 
      <span>"0"</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"USER"</span> 
      <span>=</span> 
      <span>"sb"</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"PRIV_USER"</span> 
      <span>=</span> 
      <span>"sb"</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"OS_LOGIN"</span> 
      <span>=</span> 
      <span>""</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"PROXY_USER"</span> 
      <span>=</span> 
      <span>""</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"HOST"</span> 
      <span>=</span> 
      <span>"localhost"</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"IP"</span> 
      <span>=</span> 
      <span>""</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;</span> 
      <span>"DB"</span> 
      <span>=</span> 
      <span>"sbtest"</span> 
     </div> 
     <div> 
      <span>/</span> 
      <span>&gt;</span> 
     </div> 
     <div> 
      <span>&lt;</span> 
      <span>AUDIT</span> 
      <span>_</span>RECORD 
     </div> 
     <div> 
      <span></span> 
      <span>"NAME"</span> 
      <span>=</span> 
      <span>"Query"</span> 
     </div> 
     <div> 
      <span></span> 
      <span>"RECORD"</span> 
      <span>=</span> 
      <span>"1292_2014-04-30T00:04:42"</span> 
     </div> 
     <div> 
      <span></span> 
      <span>"TIMESTAMP"</span> 
      <span>=</span> 
      <span>"2014-04-30T12:45:07 UTC"</span> 
     </div> 
     <div> 
      <span></span> 
      <span>"COMMAND_CLASS"</span> 
      <span>=</span> 
      <span>"select"</span> 
     </div> 
     <div> 
      <span></span> 
      <span>"CONNECTION_ID"</span> 
      <span>=</span> 
      <span>"32"</span> 
     </div> 
     <div> 
      <span></span> 
      <span>"STATUS"</span> 
      <span>=</span> 
      <span>"1146"</span> 
     </div> 
     <div> 
      <span></span> 
      <span>"SQLTEXT"</span> 
      <span>=</span> 
      <span>"SELECT pad FROM sbtest8 WHERE id=5036031"</span> 
     </div> 
     <div> 
      <span></span> 
      <span>"USER"</span> 
      <span>=</span> 
      <span>"sb[sb] @ localhost []"</span> 
     </div> 
     <div> 
      <span></span> 
      <span>"HOST"</span> 
      <span>=</span> 
      <span>"localhost"</span> 
     </div> 
     <div> 
      <span></span> 
      <span>"OS_USER"</span> 
      <span>=</span> 
      <span>""</span> 
     </div> 
     <div> 
      <span></span> 
      <span>"IP"</span> 
      <span>=</span> 
      <span>""</span> 
     </div> 
     <div> 
      <span>/</span> 
      <span>&gt;</span> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

* 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.

***EDIT***: Clear text passwords issue only applies to MySQL 5.5 version.  As of MySQL 5.6.3, passwords in statements written to the general query log are rewritten by the server not to occur literally in plain text (quote from the documentation).

In MySQL 5.6 version here is what we will see:

XHTML

<AUDIT\_RECORD "NAME"="Query" "RECORD"="14\_2014-05-05T21:56:20" "TIMESTAMP"="2014-05-07T13:51:04 UTC" "COMMAND\_CLASS"="grant" "CONNECTION\_ID"="2" "STATUS"="0" "SQLTEXT"="GRANT ALL PRIVILEGES ON \`test\`.\* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '\*1E752E353CAA631945738535152AE894E47F5A48'" "USER"="root\[root\] @ localhost \[\]" "HOST"="localhost" "OS\_USER"="" "IP"="" />

*  The file can grow very large on disk:

Shell

ls -lah /var/lib/mysql/audit.log -rw-rw---- 1 mysql mysql 7.1G May 4 07:30 /var/lib/mysql/audit.log

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div style="line-height:15px!important"> 
     <div>
       1 
     </div> 
     <div>
       2 
     </div> 
    </div> </td> 
   <td> 
    <div style="line-height:15px!important"> 
     <div> 
      <span>ls</span> 
      <span> </span> 
      <span>-</span> 
      <span>lah</span> 
      <span> </span> 
      <span>/</span> 
      <span>var</span> 
      <span>/</span> 
      <span>lib</span> 
      <span>/</span> 
      <span>mysql</span> 
      <span>/</span> 
      <span>audit</span> 
      <span>.log</span> 
     </div> 
     <div> 
      <span>-</span> 
      <span>rw</span> 
      <span>-</span> 
      <span>rw</span> 
      <span>--</span> 
      <span>--</span> 
      <span> </span> 
      <span>1</span> 
      <span> </span> 
      <span>mysql </span> 
      <span>mysql</span> 
      <span> </span> 
      <span>7.1G</span> 
      <span> </span> 
      <span>May</span> 
      <span> </span> 
      <span>4</span> 
      <span> </span> 
      <span>07</span> 
      <span>:</span> 
      <span>30</span> 
      <span> </span> 
      <span>/</span> 
      <span>var</span> 
      <span>/</span> 
      <span>lib</span> 
      <span>/</span> 
      <span>mysql</span> 
      <span>/</span> 
      <span>audit</span> 
      <span>.log</span> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

***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 to [this 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 with [sysbench][] OLTP 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:

<table> 
 <thead> 
  <tr> 
   <th>Test</th> 
   <th>Overhead</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Plugin +&nbsp; audit_log_policy = ALL</td> 
   <td>~15% overhead</td> 
  </tr> 
  <tr> 
   <td>Plugin +&nbsp; audit_log_policy = LOGINS</td> 
   <td>~0% overhead (sysbench only connects once, so there may be bigger overhead here)</td> 
  </tr> 
  <tr> 
   <td>General_log</td> 
   <td>~62% overhead</td> 
  </tr> 
 </tbody> 
</table>

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.

**注：近期参加MySQL运维学习，老师推荐该文章作为学习和技术提高的扩展阅读，先记录到自己的博客中，随后慢慢消化、学习、提高。本文章与“字符集和权限安全”主题相关。**

[Alexander Rubin]: http://www.mysqlperformanceblog.com/author/alexanderrubin/
[13 Comments]: http://www.mysqlperformanceblog.com/2014/05/07/mysql-audit-plugin-now-available-in-percona-server-5-5-and-5-6/#comments
[Tweet]: http://twitter.com/share
[The MySQL Audit Plugin is now available for free in Percona Server]: /images/20220823/767a0b4b7f3248049d43e7334dd76408.png
[The MySQL Audit Plugin is now available for free in Percona Server 1]: http://www.mysqlperformanceblog.com/wp-content/uploads/2014/05/percona_server.jpeg
[Percona Server_5.5.37-35.0]: http://www.mysqlperformanceblog.com/2014/05/06/percona-server-5-5-37-35-0-now-available/
[Percona Server 5.6.17-65.0-56]: http://www.mysqlperformanceblog.com/2014/05/06/percona-server-5-6-17-65-0-now-available/
[MySQL Enterprise]: https://dev.mysql.com/doc/refman/5.5/en/audit-log-plugin.html
[re-implemented]: http://www.percona.com/doc/percona-server/5.5/management/audit_log_plugin.html
[McAfee MySQL Audit Plugin]: https://github.com/mcafee/mysql-audit
[MariaDB Audit Plugin for MySQL]: https://mariadb.com/kb/en/mariadb-audit-plugin-117-release-notes/
[enable general log]: http://dev.mysql.com/doc/refman/5.5/en/query-log.html
[the overhead of MySQL general and slow log]: http://www.mysqlperformanceblog.com/2009/02/10/impact-of-logging-on-mysql%E2%80%99s-performance/
[asynchronously]: https://dev.mysql.com/doc/refman/5.5/en/audit-log-plugin-options-variables.html#sysvar_audit_log_strategy
[decribed in the doc]: https://dev.mysql.com/doc/refman/5.5/en/audit-log-plugin-installation.html
[options we can tweak here]: https://dev.mysql.com/doc/refman/5.5/en/audit-log-plugin-options-variables.html
[mysqlauditgrep]: http://dev.mysql.com/doc/mysql-utilities/1.3/en/mysqlauditgrep.html
[this bug]: http://bugs.mysql.com/bug.php?id=71838
[sysbench]: http://sysbench.sourceforge.net/