I am trying to perform a point in time on a specific database using a snapshot and mysql's transaction logs. I am using the following to pull statements from the time of the snapshot to the time of recovery:

mysqlbinlog --database=**database** --start-datetime="$start_datetime" --stop-datetime="$stop_datetime" list_of_binlog_file_names

The resulting statements that mysqlbinlog produces include INSERT/UPDATE/DELETE statements for another database that has the form:

INSERT INTO **database**_reporting.tablename VALUES (data);

So functionally I'm getting statements from 2 different databases, one is our production database, the other is our reporting database, differentiated by nomenclature by the '_reporting'. It would appear to me that our application is inserting to the secondary database while still using the primary one and the binlogs associate both statements with the primary database.

Am I correct that mysqlbinlog is going to read out statements for both databases as long as they are executed after a USE primary_database;? Is there a way to separate out the ones for the reporting database using mysqlbinlog? I have searched quite a bit- I may be barking up the wrong tree here. Let me know if I can clarify the question.

1 Answer 1


Since the --database option does not screen out queries that explicitly name the database with the table, you should redirect that to grep and locate primary_database and INSERTs, UPDATEs, DELETEs:

mysqlbinlog --database=**database** --start-datetime="$start_datetime" --stop-datetime="$stop_datetime" list_of_binlog_file_names | grep "primary_database\." | grep "[IUD][NPE][SDL][EA][RT][TE]"

This may not be a perfect solution. Perhaps a good perl or python script and nail this one.

  • 1
    Conversely, the --database option will also fail to include queries against the specified database if a different database was the current database (via USE) when the query was run... it only matches the "current" database with statement-based logging. With row-based logging, the --database flag works more as expected, but mysqlbinlog shows the actual rows changed, not the raw queries. dev.mysql.com/doc/refman/5.6/en/… Jul 9, 2013 at 20:38
  • Thanks! I'm getting the developers to fix their code, and grep -v to get rid of the unneeded stuff did the trick!
    – Stephen
    Jul 10, 2013 at 3:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.