Over time, the audit log can contain a lot of useful information. However, how filtering this information and searching for specific events, for instance in order to determine the possible cause of a problem, can be very tedious if done manually.
For example, suppose that someone reported that some data changes are missing (and you suspect some INSERT or UPDATE queries failed) and you want to determine what might be the cause of those transaction failures. All queries are recorded to the audit log file, so you just need to get retrieve all queries of a given type that failed (with a MySQL error) and analyze them.
This can be achieved using common 'grep' command line tools, but likely involves the use of very complex regular expression to filter the desired data. Fortunately, the mysqlauditgrep utility allows to perform this kind of task in a much easier and simple way taking advantage of the knowledge of the structure and semantics of the audit log files.
The goal is display all INSERT and UPDATE queries that failed (independently of error) from the current audit log file.
It is assumed that the audit.log
file
exists and is located in the directory
/MySQL/SERVER/data/
. The below example
show how easy it is to perform the desired search with the
mysqlauditgrep utility.
shell> mysqlauditgrep --query-type=INSERT,UPDATE --status=1-9999 /MySQL/SERVER/data/audit.log
+--------+---------------------+-------+-------------------------------------------------------+---------------+
| STATUS | TIMESTAMP | NAME | SQLTEXT | CONNECTION_ID |
+--------+---------------------+-------+-------------------------------------------------------+---------------+
| 1046 | 2013-08-01T18:20:46 | Query | INSERT INTO tbl_not_exist (a,b,c) VALUES(1,2,3) | 37 |
| 1146 | 2013-08-01T18:21:03 | Query | INSERT INTO mysql.tbl_not_exist (a,b,c) VALUES(1,2,3) | 37 |
| 1054 | 2013-08-01T18:23:10 | Query | INSERT INTO test.t1 (a,b,not_col) VALUES(1,2,3) | 37 |
| 1146 | 2013-08-01T18:26:14 | Query | UPDATE tbl_not_exist SET a = 1 | 37 |
| 1054 | 2013-08-01T18:26:53 | Query | UPDATE test.t1 SET not_col = 1 | 37 |
+--------+---------------------+-------+-------------------------------------------------------+---------------+
As expected, the use of the mysqlauditgrep
utility requires the specification of the target audit log
file to search and a few options corresponding to the needed
search criteria. In this case, the
--query-type
option was
used to restrict the displayed results to specific types of
queries (i.e., only INSERT and UPDATE), and the
--status
option was
used to specify the considered MySQL error codes (i.e., all
ranging from 1 to 9999).
The --query-type
option
allows the specification of a comma separated list of
different SQL statements. Apart from INSERT and UPDATE the
list of supported values for this option also includes:
CREATE, ALTER, DROP, TRUNCATE, RENAME, GRANT, REVOKE, SELECT,
DELETE, COMMIT, SHOW, SET, CALL, PREPARE, EXECUTE, DEALLOCATE
The --status
option
accepts a comma-separated list of non-negative integers
(corresponding to MySQL error codes) or intervals marked with
a dash. For example: 1051,1100-1199,1146. In this particular
case, the range value 1-9999 was used to include all MySQL
error codes and display all unsuccessful commands. To retrieve
only successful command (no errors) simply use the value 0 for
the --status
option.
The value specified for the
--query-type
option are
case insensitive, therefore you can mix lowercase and
uppercase to specify the list of query types. For example,
'insert,Update' produces the same result as using
'INSERT,UPDATE'. Of course the use of non-supported values
raises an appropriate error.
Many other options and search criteria are provided by the
mysqlauditgrep utility, check them in order
to use the more appropriate one to meet your needs. Note that
the utility provides the
--pattern
option to
search entries in the audit log file using regular
expressions, like common grep tools. By default, this option
uses standard SQL pattern matching (used by 'LIKE' comparison
operator), unless the
--regexp
option is used
to allow more powerful standard regular expressions (POSIX
extended).