2

I am trying to verify database histories. For that, I need to log and parse read-write accesses to a database.

I know MySQL mainly offers two types of logs - general query log and binary log. General query log is not helpful for me since it does not log end timestamps. Wheresas, binary log is perfect for my work, but it does not log read access, i.e. SELECT queries.

So I want to know if there is a way to force binary logging such that it logs non-modifying queries too.

Improve this question

1 Answer 1

Reset to default
3

The binary log only records changes to data or metadata. There is no option to make it log SELECT queries.

You overlooked the MySQL slow query log, which does record the timestamp and the duration of the query. You can make the slow query log include all queries by setting long_query_time=0 so that it logs all queries that take 0 seconds or more (that is, all of them).

Be careful! In a typical system, the number of SELECT queries is a lot more than the number of other queries. Your logs could grow very rapidly. Make sure to configure log rotation, or you'll fill up your disk.

Other than that, it sounds like you need an audit log. See https://dev.mysql.com/doc/refman/5.7/en/audit-log.html for the MySQL Enterprise audit log plugin (this requires a support subscription from Oracle).

There are at least a couple of open-source implementations of audit log plugins for MySQL. For example: https://www.percona.com/doc/percona-server/LATEST/management/audit_log_plugin.html

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thanks. This really helps. I would prefer slow-query to server-audit because server audit does not log duration or end timestamp. Also, is there any way to record the read values or "returned rows"?
@rnbcoder Yes, the slow query log should include a field for "rows sent". Take a look at if for yourself to confirm it contains the info you need.
can you help me understand the slow query fields? It has start_time, query_time, lock_time. Is the duration of query execution -- [start_time + lock_time, start_time + lock_time + query_time] ?
The query doesn't begin executing until it has waited lock_time seconds to acquire the locks it needs. Think of this as the time standing in line at the supermarket. Once it begins executing, that lasts query_time seconds. This is like how long it takes the clerk to process your groceries once you reach the head of the line. So from the app's perspective, the time lasted lock_time+query_time but the query execution was only the latter part.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.