Is there any tool which is capable of exporting MySQL query log to JSON format (or any other structured format)?
In that case, go to McAfee audit plugin as it's compatible with MySQL from version 5.1 to latest GA. This plugin outputs in json nicely for you and you can filter nicely on the events you do and do not want.
There are many niche projects on Github to do that, most of them haven't been updated in years. Since percona-toolkit is a very common software used to analyze MySQL I feel that utilizing it is the best option. In the code below I use popular pt-query-digest
with --filter
hack to execute arbitrary code:
# pt-query-digest --filter to_json.pl --no-report slow.log > slow.json
# cat to_json.pl
print Transformers::encode_json($event) . "\n";
1
The 1
at the end of to_json.pl is important, and the code needs to be placed in its own file - CLI inline --filter code is put into parentheses ()
.
log_output=TABLE
, giving you more options for parsing (or just querying directly). It's a dynamic global variable so it can be changed without a restart. See dev.mysql.com/doc/refman/5.7/en/log-destinations.html. – dartonw Mar 28 '15 at 17:49