Useful MySQL Queries For Merging & Restoring Databases

Filed under: MySQL,Web Development — Tags: , , , — Chris @ 9:11 pm

My MySQL skills are very adequate. Even though I don’t know how to build a perfect database structure I can query most everything. That being said, I don’t do much advanced stuff on a regular enough basis to be able to write SQL with my eyes shut.

One thing I’ve never had to do is merge two MySQL backups. It turns out it’s not all that hard, but as soon as you have to deal with multiple databases it gets confusing quickly. So for my own future reference, and for others here’s what I had to do and how I did it.

The Goal

I had one old product database with multiple tables that included roughly 100 items that had been erased in the database that was currently live. I needed to retain these items in the new database.

The live database had about 100 new items that weren’t in the old database. This most recent database also had edits to older items that also existed in the old database, these edits also needed to be retained.

How It Was Done

First I created a backup of the current live database. Then I restored the old database in place of the live one. With the backup I had just created I went in and removed all the “Drop Table” and “Create Table” statements and replaced all the “Insert Into” statements to “Replace Into”. The Replace Into statement was a new one for me, hat tip to this forum post for that. What this did was added all the edits to any old products. It did not however insert any missing products in to the database.

So now I had an up to date databasebut without any newly added products.

To add the missing products I created a new temporary database and restored the backup I created earlier in to it.

Last but not least I used the following statement on all the tables that needed it:

SELECT * FROM schema.table

The “IGNORE” is the key here. It’ll skip any duplicates and insert the remaining rows. Once again, a new one for me which I’m sure I’ll have plenty of use of in the future.

I’m sure there were better ways of doing this and if I would have had to do this on a more complex database it wouldn’t have been a good solution. But I learned something and it worked in this situation.