0

Is there any tool which is capable of exporting MySQL query log to JSON format (or any other structured format)?

Improve this question
4
  • You could set 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
  • Switch to Percona Server and use the Audit plugin or install and use McAfee audit plugin. This doesnt provide slow log information but that's not not in your original requirements. – eroomydna Mar 31 '15 at 4:26
  • @eroomydna that would work, but switching server software to parse logs often isn't an option. – matt Apr 1 '15 at 8:40
  • @dartonw that would also work, but log_output=TABLE has query time resolution up to a second (I'm not sure if that's still true for 5.6/5.7). – matt Apr 1 '15 at 8:43
1

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.

Improve this answer
0

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 ().

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.