CODEWITHSUNDEEP
mysql
Joshua Enfield
Joshua Enfield

Reputation: 18308

MySQL - Transaction Logging - binlog? general query log? log to database?

We would like to turn on query logging so we can find queries that change data.

It it possible to do transaction logs to a database? what is the difference between binlog and the general query log?

Upvotes: 1

Views: 3257

Answers (2)

Zimbabao
Zimbabao

Reputation: 8250

binlog is used to record all the changes happening in a database. Main uses of bin log is to do replication (Keep other copies of database up-to-date) and backup. You can retrieve all the write queries from binlog using mysqlbinlog utility.

General query log is used mainly for server debigging or slow query monitoring.

For your use case you can enable bin log with statement mode

Upvotes: 1

Ike Walker
Ike Walker

Reputation: 65577

If enabled, the binary log includes all queries that modify data. But you should be aware of the format. It's stored in binary format, so you need to convert it to get the actual SQL. Also, if you're using row-based replication with MySQL 5.1 or later, then you may not be able to get the actual SQL statements that ran.

The general query log includes all queries, even SELECTs that do not modify data.

You should take a look at mk-query-digest, which offers several different ways to monitor queries.

Upvotes: 2

Related Questions