How to merge two MySQL databases of same structure

问题:

Here's my situation. I've got my current database (let's call it current_db) that is up to date, but its data are incomplete because of a recent crash. Some datas have been deleted, and this delete goes from 2 years ago to yesterday.

I've got a backup of this database (let's call it backup_db), from November 2013, whose datas are complete up to November 2013. Since current_db holds some data from between November 2013 and February 2014, I don't want to just scrap it, and work from the backup. So I'd like to import current_db into backup_db ignoring duplicated data.

I've seached for methods to do that, but couldn't find any relevant ones. I've come across a few SELECT queries, but they're all simplistic ones. My database holds 20 tables, and I don't really see myself building an immense query to import all of this. Is there any other way ?

Thanks

回答1:

1. Use phpMyAdmin ( Install if you still don't use it )
2. Go to the current database
3. click import and import the other database

Possible Issues :

• The max file upload size in phpmyadmin may be 2MB.To solve this increase the maximum file upload size php.ini

  Assume you have a schema s1 and a schema s2.

To insert all rows of a table in s1 into a table in s2, while overwriting existing lines, you can use:

REPLACE INTO s2.table_name
SELECT * FROM s1.table_name;

If you do not want to touch existing lines:

INSERT INTO s2.table_name
SELECT * FROM s1.table_name
ON DUPLICATE KEY IGNORE;

Comment here if you have any issues.