Geek Answers Handbook

How to merge two MySQL databases of the same structure

Here is my situation. I have a current database (let it be called current_db), current, but its data is incomplete due to a recent crash. Some data has been deleted, and this deletion occurs from 2 years ago until yesterday.

I have a backup copy of this database (let it be called backup_db), from November 2013, whose data was completed before November 2013. Since current_db contains some data for the period from November 2013 to February 2014, I do not want to just abandon it and work with the backup. Therefore, I would like to import current_db into backup_db, ignoring duplicate data.

I used methods for this, but did not find suitable ones. I came across several SELECT queries, but they are all simplistic. My database contains 20 tables, and I really don’t see how I create a huge query to import all of this. Is there another way?

thanks

+6
source share
1 answer
  • Use phpMyAdmin (install if you are still not using it)
  • Go to current database
  • click import and import another database

Possible problems:

  • The maximum file upload size in phpmyadmin can be 2 MB. To solve this problem, increase the maximum upload size of the php.ini file

    Suppose you have s1 and s2.

To insert all the rows of a table in s1 into a table in s2, when overwriting existing rows, 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 problems.

+5
source

More articles:


All Articles