22

Does MySQL keep a transaction log and if so where could I find it?

A number of rows have mysteriously been deleted from a table and I want to try and see how and when it occurred.

Chris
  • 6,914
  • 5
  • 54
  • 80
php-b-grader
  • 3,191
  • 11
  • 42
  • 53

3 Answers3

17

If you turned on - mysql can track binary log, which contains all the modifications (to be clear - it contains the queries that had changed something).

But anyway, it is useless if you do not have the initial dump, which precedes the binlog turning on. Also i suppose if you made the dump and turned on binlog - you would not ask such question :-S

zerkms
  • 249,484
  • 69
  • 436
  • 539
  • 1
    I've checked PHPMyAdmin and there are statistics for binary logs which look like they've been running for over 1 week... Is there a config that will point me to the file location (on a linux system) – php-b-grader Aug 03 '10 at 06:58
  • log-bin=mysql-bin -- it is the sample line from my home server config file. so i have mysql-bin* files within my data directory. btw, as i said, if you have no the dump that has been taken just before you turned on binlog - then binlogs are useless. also - you should look at query log (if you have them) as Amber offered. – zerkms Aug 03 '10 at 07:01
3

Short answer: not by default.

In order to have accurate logging results, you need to have started the server in binary logging mode first, using the parameter --log-bin=your-file-name. It then creates a binary log file {your-file-name.seq} where seq is a sequence number for subsequent log files (recommended to put your file name there, with explicit directory location).

More information on the MySQL site explaining all the details: https://dev.mysql.com/doc/refman/5.7/en/binary-log.html

Mark Storer
  • 15,672
  • 3
  • 42
  • 80
Stephan van Ingen
  • 51
  • 3
1

If you're using MySql on Windows, there's a file located in C:\Program Files\MySQL\MySQL Server 5.0\data (assuming a C: drive for the installation target and MySql version 5.0), that is called %COMPUTERNAME%.log that contains the commands that have been executed.

Rob
  • 45,296
  • 24
  • 122
  • 150