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
exists and is located in the directory
mysqlauditgrep --user=root --event-type=Connect \ --format=CSV /MySQL/SERVER/data/audit.logSTATUS,NAME,TIMESTAMP,CONNECTION_ID,HOST,USER,PRIV_USER,IP 0,Connect,2013-08-01T15:24:26,33,localhost,root,root,127.0.0.1 0,Connect,2013-08-01T15:24:26,34,localhost,root,root,127.0.0.1 0,Connect,2013-08-01T15:24:26,35,localhost,root,root,127.0.0.1 0,Connect,2013-08-01T15:24:26,36,localhost,root,root,127.0.0.1 0,Connect,2013-08-01T18:18:43,37,localhost,root,root,127.0.0.1 0,Connect,2013-08-01T18:49:46,38,,root,root,192.168.1.104 1045,Connect,2013-08-01T19:18:08,39,localhost,root,,127.0.0.1
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
In this example, only the "Connect" value was used for the
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).
The values for the
--format options are
case insensitive, therefore lower and upper cases 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
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
then it must be surrounded by double (") or single (') quotes
depending on the operating system.