MySQL Utilities

3.6.4 How do you find connections by the user 'root' in the audit log and show the results in CSV format?

The audit log plugin can be used to record information about different type of events which one might need to monitor or keep a record in a different format. For example, a security record with the list of all logins performed to the database serve might need to be kept to later track the responsible for some change. Moreover, the retrieved information might need to be converted to a specific format (such as CSV) to feed another application.


The goal of this task is to retrieve from the audit log the information of all the connections established by the root user to the MySQL Server, and display the resulting information in the comma-separated-value (CSV) format.

Besides the search/filter functionalities using different criteria, the mysqlauditgrep utility also provides a feature to display the resulting information in different formats (including CSV). This allows this task to be performed easily with in a single step.

It is assumed that the audit.log file exists and is located in the directory /MySQL/SERVER/data/.

Example Execution

shell> mysqlauditgrep --user=root --event-type=Connect \
          --format=CSV /MySQL/SERVER/data/audit.log



To perform this operation the mysqlauditgrep utility requires the indication of the target audit log file as expected, two criteria search options, and one formatting option to convert the output to the desired format. In this case, the --users option was applied to search the records for the specified user (i.e., "root") and the --event-type option to retrieve only event of a specific type (i.e., "connect"). The --format option is the one used to define the output format of the obtained search results.

In this example, only the "Connect" value was used for the --event-type option which correspond to the logging in event (when a client connects). Nevertheless, this option accepts a comma separated list of event types with the following supported values (beside "Connect"): Audit, Binlog Dump, Change user, Close stmt, Out, Connect, Create DB, Daemon, Debug, Delayed, insert, Drop DB, Execute, Fetch, Field List, Init DB, Kill, Long Data, NoAudit, Ping, Prepare, Processlist, Query, Quit, Refresh, Register Slave, Reset stmt, Set option, Shutdown, Sleep, Statistics, Table Dump, Time.

In terms of output formats the following are supported beside CSV: GRID (used by default), TAB, VERTICAL and RAW (corresponding to the original XML format of the audit log file).

Permissions Required

The user must have permissions to read the audit log on disk.

Tips and Tricks

The values for the --event-type and --format options are case insensitive, therefore lowercase and uppercase can be mixed to specify these values as long as a supported event type name or format is used. Unlike them, the value specified for the --users option is case-sensitive, so be careful not to mix upper and lower cases here.

It is possible to find some event type values with a space in the middle, for example like "Binlog Dump" or "Init DB". If one of such values needs to be specified for the --event-type option then it must be surrounded by double (") or single (') quotes depending on the operating system.