Mariadb – Logging remote transactions in mariadb

logmariadb

I know how to log all queries to a file in mariadb, is there a way for me to log only remote transactions?

I've got a situation where I have a server with black-box software that does a lot of work from a central server and a lot of work on it's own server (side-by-side with mariadb). The central server seems to be doing a crazy load of unnecessary labor and I want to be able to separate what it's doing from what the other processes are doing.

So, how can I log only queries done by users logged in from remote locations?

Best Answer

First of all- you should create different database users for different access roles of applications- even they use the same data. That will make easier to monitor each individual app. Even if you have to share the user name, you can create different accounts with different source ips (or ip ranges) to differentiate user activity.

You have several options for query auditing that can work better, faster and more fine-grained than the general query log, for example:

  • Install an audit plugin. This is basically the ability to filter the general log in an efficient way so you write the results you want. MySQL/MariaDB provide a general api, and there are several plugins that allow you some flexibility on that filtering, normally using for security auditing:
  • Enable and use the integrated profiling utility performance_schema. While this is usually used to monitor performance and problems, it is so flexible that you can filter by any way imaginable. By default it will only get you user usage summaries and digests, but you can enable extended logging for a single user/database/etc. https://dev.mysql.com/doc/refman/5.6/en/performance-schema-pre-filtering.html

  • Use the slow query log. You can change the slow_query log limit to log only queries that take more that certain amount of seconds, or to log only certain percent of all queries. It contains the user name. This has a bit more control over what exactly you want to log, and then you can use tools like pt-query-digest to summarize what was going on.

  • Use tcpdump. In extreme cases, you can sniff your own traffic (if it is not encrypted), to identify queries happening between 2 servers. As any network connection, you can set where you sniff, and what kind of traffic/port you use, so that can quickly tell you what is going on: https://www.percona.com/blog/2008/11/07/poor-mans-query-logging/ There are some utilities to convert traffic to a more usable format, including the above pt-query-digest tool.

Related Question