Search code examples
mysqlverificationbinary-loggeneral-log

SELECT queries in MySQL binary_log

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.

Solution

  • 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