How does one combine two databases with different incremental IDs?

I've got a master/slave MySQL setup that I write stats to. There was an issue, and I had to temporarily 开发者_JAVA百科write to another database setup for 24 hours. Now I'd like to bring those stats from the temporary store into the master/slave setup.

Those tables each used an incrementing ID as a key. Is there a way to load the stats without overwriting two similar IDs and then re-work that incrementing ID column so that the numbers are again consistant and incremental?

Depending on your circumstances, one option might be to run something like:

UPDATE [table] SET id=id+[number of records in backup table] WHERE id>=[first duplicate id] ORDER BY id DESC` 

on the master/slave database to create a gap in the id sequence, then when you insert the records from the backup they'll fill the gap and the ids will be sequential and in correct date order.

Obviously you'll have to be careful to use the correct numbers in the query for each table, and make sure you update any tables that use these ids as foreign keys. I'd strongly recommend practicing this manoeuvre on a backup first.

Most easy solution : relocate the auto-increment of the table on your master to current value + number of rows in your temp storage. This needs to be done when your logging app is down, or else it will become messy. That's done in a matter of minutes. You can the restart your app.

Then you add the difference between the first id on the temp db and the old value of the auto-increment on the master to the ids on your temp db.

Finally simply insert your data from the temp db to the table on the master, and it's done!