How to locate MySQL bin log file file based upon content

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.

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.

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?

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.

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'

Select allOpen 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`

Select allOpen in new window

and

`ID`='37819'

Select allOpen 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"
fi

done

OK - that sounds promising

Script how?  And I think grep is unix; I'm on windows

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).

Thanks--

>> 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
Insert

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.
.....

Thanks - that was kind of you to write that up for me.