Mysql – enable query log for single database

loginsMySQLmysql-5.6

I am having many database schema in mysql 5.6 server, now the problem over here is that I want to catch the queries for one schema only.

I can not enable query log for entire server as one of my schema is highly loaded and it will impact the server.

Is their any way, any tool through which I could only log the queries by single schema.

I found benchmarking graph which shows the impact on transactions/second when query log is enabled.

enter image description here
enter image description here

Best Answer

Interesting question and a +1. I was interested by this because I can see several use cases for this fuctionality.

Unfortunately, for your case where you cannot switch on general logging, there is only one, rather inadequate, workaround.

That is to use the SQL_LOG_OFF variable to disable logging for a given connection. An ideal solution would have been to have an "SQL_LOG_ON" variable as one can do in Oracle (equivalent) - maybe you could try and switch logging off for all but the connection(s) of interest?

Furthermore, and regretably, this requires the SUPER privilege. Again, this may not (even probably not) be possible in your case.

Depending on the severity of your problem, working hours and server load at given times, you may be able to find a use for Percona's pt-query-digest which can help with log analysis. Small comfort, but as usual PostgreSQL is streets ahead of MySQL (1, 2).

If you'd care to file a feature request, I'd be happy to follow up with a "me-too" if you post the link back here.

Related Question