Alex Rivera | Logout

Keeping integrity between two separate data stores during backups (MySQL and MongoDB)

Asked 2012-03-08T17:05:42.217
11

I have an application I designed where relational data sits and fits naturally into MySQL. I have other data that has a constantly evolving schema and doesn't have relational data, so I figured the natural way to store this data would be in MongoDB as a document. My issue here is one of my documents references a MySQL primary ID. So far this has worked without any issues. My concern is that when production traffic comes in and we start working with backups, that there might be inconsistency for when the document changes, it might not point to the correct ID in the MySQL database. The only way to guarantee it to a certain degree would be to shutdown the application and take backups, which doesn't make much sense.

There has to be other people that deploy a similar strategy. What is the best way to ensure data integrity between the two data stores, particularly during backups?

mysql mongodb backup
Edit
Report

1 Answer

0

There really is no way to do it without some kind of outside check or enforcement.

If you really need to ensure perfect integrity between the two, one way to do this is to use timestamps for both your mysql data (all records) and mongo records, then back up each one filtered by the timestamps using the tools for each to select only the the records existing right before the scheduled backup (see http://www.electrictoolbox.com/mysqldump-selectively-dump-data/ for how to use mysqldump with a WHERE clause and http://www.mongodb.org/display/DOCS/Import+Export+Tools#ImportExportTools-mongodump to dump a MongoDB collection with a query)

Depending on how you're actually using each of your data stores, you may be able to do something else... For example, if you are only writing to your MongoDB and never updating or deleting, then it would be reasonable to backup your MySQL database, then backup you MongoDB (which may now have some extra records in it because it is backed up afterwards) and then purge the MongoDB records that do not correspond to anything in MySQL. As I said, it depends on how you're using them.

But the timestamp thing will work regardless - you just have the extra overhead of the timestamps.

answered 2012-03-26T19:02:18.133

Your Answer