How you end up on Google querying “Merge two Databases”… Like any good story that may be a long tale. I’m writing this because a CubeCart site I maintain was on a server that crashed and the host ran FSCK. The first clue things didn’t go quite as well for us as the host seemed to think they did was that the entire site pointed to my custom RoundCube webmail sub-domain install, then we found SSL didn’t work because we’d been moved to a new server with a new IP and… then the site operator noticed she was missing 24 days of orders and new customers. And that is how these stories begin.
Status: Functional database missing 24 days of orders, new user registrations, products and related data. Database had 2 days of new data (since crash).
Backup current: Backup your current live database (in my case contains two days of new data).
Restore from backup: Restore your most recent pre-event database backup. If you don’t have a backup, or not a very current one, contact your web host as they may. Request they name it differently than the live database so they don’t overwrite it.
Databases in play: One current active database (the target), one restored or backup database copy which contains the missing data (the source).
Tools: phpMyAdmin and an editor for SQL script (Notepad++ for me).
Goal: Update active DB with missing data from restored/backup version.
- If database is active as in my case – kick out admins, close web storefront (maintenance message) – early morning, late at night.
- Backup both your active/target and restored/source DB’s using phpMyAdmin (Export Tab)
- Identify affected tables. For me I had many: images, categories, customers, products, product descriptions etc.
- Open two instances of phpMyAdmin – tabs or side by side browser windows – one with LiveDB one with RestoredDB.
- Sort Table data by ID and compare the number of rows to see if there is a difference in the number and or ID’s – write it down, these are the tables to merge.
- With the Table selected in the LiveDB that you want to update – click the SQL tab in phpMyAdmin.
- Paste the code below replacing the DB and tables names with yours.
- Click GO to update the active DB with missing data from backup.
- Rinse, lather & repeat per table that you need to update.
- That’s it!
SQL code to use
Change the Database (username_restoredDB) and Table (CubeCart_customer) to yours
1 2 3 | INSERT IGNORE INTO `username_liveDB`.`CubeCart_customer` SELECT * FROM `username_restoredDB`.`CubeCart_customer` |
What the above SQL code does:
INSERT – insert data into the the Live DB username_liveDB
IGNORE – ignore duplicate data = we’re going to insert data only if it doesn’t already exist.
SELECT – select all (‘*’) data from your Table ‘CubeCart_customer’ in the example above.
FROM – the restored DB username_restoredDB
Information derived from:
– How to merge two MySQL databases
– MySQL Doc – 12.2.5. INSERT Syntax
2 thoughts on “Merge Data from two Databases – MySQL”
Thanks, this is awesome and helped restore lost homepage and other posts. I didn’t know the homepage was called Home-2 and deleted it from the database as I thought it was created by hackers. I created another database for the previous year’s database backup (the latest one that I had saved) . I imported the last saved backup to this new folder, then copied the table back to the original database. Obviously, it would be better if I could copy just the one post back, but this worked.
This was super helpful and worked like a charm for me, thank you!!!