How to locate MySQL bin log file file based upon content

I have binary logging enabled on MySQL 5.6

I need to find the log files that contain update statements for a specific table with a range of IDs (i.e. in MySQL, I would say "WHERE ID > 1234 and ID < 5678")

How can I do that?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Unfortunately, you've provided little detail about your setup, OS.

My.cnf or my.ini will define where your files could be.

Use show variables within mysql
The defaul location on a Linux/unix system is often in /var/mysql
On windows, it is often in the datadir which often is in the same path as where you installed mysql-server components.
SAbboushiAuthor Commented:
Sorry I wasn't clear.  I know where the bin files are; my question is regarding how to identify which of the files contain the relevant update statements without having to examine the contents of each bin file one at a time using   e.g. mysqlbinlog D:\Documents\MySQL\ENVY17-bin.001282 to display a log file.
The only way to know which file might have the entry requires the knowledge of when this update was done.  At that point an inspection of each binary file in the timeframe has tobe individually inspected.

If you are looking to revert, you have to start with restoring a backup, and the replaying/reapplying the transactions from binary log up-to-the time prior to when the update was issued.
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

SAbboushiAuthor Commented:
Thanks Arnold.

In my case, I am not looking to replay/reapply the binary files as you describe; I want to extract the relevant transactions into an sql file and import them.

Anyone else have any knowledge on what can search through the bin files and locate a matching string?
SAbboushiAuthor Commented:
I'm not that familiar with the different types of log files yet; I'm wondering whether another approach would be searching through some of the other log files to identify the dates associated with the transactions I am looking for, which will then point me towards the relevant bin log files per Arnold's suggestion?
The only way to have such data searchable is if you planned for it and included an auditing functionality, I.e. An auditing set of tables that record who and what commands were issued.

The longer you wait you might run out of time.

Get the data to a new/test system and begin the process of creating the database from the prior last backup.
Then search the logs for the data entry.......
The binlog includes the directive, it does not include the elements impacted by it.

The only way to see what change ..... Would require a restore to a time prior to the directive and then running a select query on the data.

The inclusion of an auditing trail functionality would the only way to achieve what you are looking for.
SAbboushiAuthor Commented:
Thanks Arnold, but the binlog has what I want.  Again, my objective is to identify the specific binlog file(s) that have the MySQL statements that I want to retrieve.

e.g. here is a line from a binlog file:
UPDATE `quran`.`word` SET `alt`='provision', `MMA`='meal' WHERE `ID`='37819'

Open in new window

I want to be able to locate that binlog file ("ENVY17-bin.001282") because it contains an update statement for the quran.word table for ID=37819

The only way I know how to do that is to use mysqlbinlog to examine one bin log at a time to see if the strings
UPDATE `quran`.`word`

Open in new window


Open in new window

are in the file.

I was hoping there was some utility that would search through all the binlog files or a file layout for reading the binlog files or some other way to locate that update statement.

I have other log files turned on e.g. general query log, but I haven't examined whether or not it (or other log files) might be useful to accomplish my objective.

I understand the process of restore + playback of binlog files for recovery -- that is NOT what I am asking about.
You can script the process
ls | while read a; do
If ( Mysqlbinlog [optional options] "$a" |grep "pattern" >/dev/null); then
       echo "$a"

SAbboushiAuthor Commented:
OK - that sounds promising

Script how?  And I think grep is unix; I'm on windows
You can get unix tools for windows, or use find /I "pattren"

A batch file

See if cygwin is an option?

I am still puzzled what it is you believe you will gain.

Are you looking for when an event occurred as a point of reference to identify the end of a restore point?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SAbboushiAuthor Commented:
OK - the process I understand you are suggesting:

Use a batch file to loop through all binlog files and pipe the output from mysqlbinlog for each file to a search string utility to identify matches

OK - works for me (although I'll need to figure out the specifics).


>> Are you looking for when an event occurred as a point of reference to identify the end of a restore point?

Since you asked:  I wish I understood the mechanics behind what you asked ; )

I haven't had the time to research, test and get comfortable yet with restore points and playing back binary logs.  I'm only doing backups at the file level right now.  Since I was only using MyISAM files, I could recover tables from file level backups.

But I've started to convert to InnoDB to utilize transactions, which means until I get new procedures in place for backups, I can no longer recover tables from file level backups.

Sometimes MySQL crashes and corrupts the table I am working on.  Until I can acquire the knowledge and do the testing to get comfortable with how to utilize the recovery mechanisms (as they were designed to be used), I see that worst case scenario, I can recover any changes to InnoDB tables through the bin log files.

At some point, I hope to figure out how to do this the... er... proper way.  Just have too much on my plate right now.
Mysqldump is the tool to use to perform mysql backup. It creates a text file with the database stracture and data
Create table with options

This can run while mysql is responding to queries.

Versus filesystem level backups, requires that mysql be stopped.
Craete a test database if one does not exist. Create tables, etc.

Mysqldump -u root test | more
And see

You can install, phpmyadmin which is a php based web interface into mysql.

You know that update tablename set parameter='gfgt'
Is the cause for the data corruption.

Going through the search process, you see that on Aug. 10 2014 at 10:00:00 am is when this update entry was register.

You then get all the binlog a prior to that date and run them sequential (replaying)
Mysqlbinlog filename1 | mysql

Until the one where the command was located where you will replay the commands until this entry is seen.

You still have to have a full backup from which point the binlogs will be replayed.
SAbboushiAuthor Commented:
Thanks - that was kind of you to write that up for me.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.