Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer 1

up vote 0 down vote accepted

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.

share|improve this answer
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/… –  Michael - sqlbot Jul 9 '13 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 '13 at 3:49
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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