Joined: 17 Mar 2006
|Posted: Sun Sep 12, 2010 4:16 pm Post subject: How to merge two MySQL databases
I thought that we ought to post this in the forum for the benefit of other users, since I have just recently received a request for help with merging two MySQL databases into one...
A request recently landed on my desk from a client who had a fairly large links database. They had deleted the database, after first making and downloading a backup, and started afresh with a new and empty database, but after a week or so, they decided that they would like to restore the old database. Now that's a simple enough operation, but they had since had some new entries in their newer database which they wanted to keep, so they wanted help with merging the two MySQL databases into one while retaining the existing structure and data. This is the advice that I gave...
How to merge two MySQL databases
First you will need to download a backup of your existing database (the newer one), I am assuming here that you already have a backup of the other database which you want to merge with the existing one. If not, you need to download a backup of both (best to always be on the safe side and keep regular backups of your MySQL databases on your local hard drive). So, you will need to log into your cPanel (web hosting control panel), use the Backup link to go to the Backup page. Then select the database that you need to download by using the links under the section entitled Download a MySQL Database Backup (in fact, if you have not already done so, download each and every database that is shown there right now). You should be faced with a prompt to download the database(s), and you should allow the download and select an appropriate place on your hard drive to save each one.
Now we need to go to the folder on your hard drive which contains the database backup(s) that you've just downloaded. And rename the newest one to something slightly different (I'd just add the letter a to the existing file name, but do NOT change the file extensions). For example, let's say that your newest database backup is called linksdb1.gz, this should be renamed to linksdb1a.gz
Now that you've renamed one of the database backups, you will need to upload that renamed backup to your web space, so go now click the Browse button under the section entitled Restore a MySQL Database on the Backup page from which we downloaded the backups. In the window that comes up, browse to the folder and select the renamed backup and click Open. Then you will see the path and file name in the box to the left of the Browse button, now click the Upload button right below that and your database will be restored (let the page finish loading to ensure that the upload has completed before doing anything else).
Now you must repeat this step for the old database, which will replace the existing database on the server with the old one. We will then get to work on merging the newer data into the older database so we have not lost any of the newer entries.
Now that you've restored the renamed database, go back to the cPanel home page (use your browser's Back button and then use the link top right of the cPanel page called Home).
Now use the MySQL Databases link to go to the MySQL interface, and scroll down to the bottom of the page and click on the phpMyAdmin link which will open the phpMyAdmin page in a new browser window.
Now we need to select the renamed database that you restored as above (in the example I gave above it was named linksdb1a.gz - to select this database on the phpMyAdmin page, look to the left pane and select the database from the drop down list (where it says (Databases) ... ). You should see all of your existing databases in that drop down list, and you select the database we want by simply clicking on the one which we renamed. The phpMyAdmin page will now reload and show the database structure in the main pane (the right part of the window) together with a list of tables in the database in the left pane. Don't worry about the left pane because we will now be working in the main window (the right part of the window).
The next step is to find the table which holds the data which you want to merge. Lets say in this instance, the links are contained in the table called dbs_links. We'll keep this to hand because we will need this when entering the SQL code to merge the information into the existing database.
Now we need to click on the tab called SQL, its at the top of the main window. You'll now see a different page in the main window, and we will now be entering a SQL instruction in the large input box called Run SQL query/queries on database username_linksdb1 (note that the username part will be replaced with your control panel username, this is always used as a prefix on every MySQL database name :: and the linksdb1 part will be replaced with the actual name of your database). Now enter the following code (replacing the username part with your cPanel username) ...
Highlight and press CTRL+C to copy this code, then paste it using CTRL+V
|INSERT IGNORE INTO `username_linksdb1a.gz`.`dbs_links`
NOTE: don't forget to change username to your own control panel username. Remember that we determined the data that we wished to merge from the appropriate table, in this example we used dbs_links as we can see in the code, so you need to replace that with the appropriate table name in your databases.
Note the use of the IGNORE element in the code above, it ensures that duplicate entries are skipped. If this was left out, you may get an error message during the merge which will prevent all records from being merged.
Now click the Go button at the bottom right of the input box (where you've just entered this code). The page will then refresh and show a results box above the input box that we just used. It should tell you how many rows have been inserted into the database.
Multiple Domain Hosting Plans ~ Domain Name Registration ~ Support