Solved

merge/sync two mysql databases

Posted on 2012-03-21
4
284 Views
Last Modified: 2012-03-26
I have two mysql databases with the same structure and a ton of tables.

I would like to merge the two.

(What happened was, I had to move to another hosting provider, and in the process, somebody placed an order on the old server, and my new database does not have this info. But the new database has new info, so I can't just replace it with the old one. I tried INSERT from TO, with individual tables, but there are too many linked tables, and I can't be sure everything was moved correctly.)

Maybe is there a widget out there that will sync these for me?
0
Comment
Question by:Kyle Hamilton
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 24

Assisted Solution

by:johanntagle
johanntagle earned 500 total points
ID: 37750107
Unfortunately I don't think there's a tool that can automatically fix this for you.  The problem is too specific to how the database is architected for a generally-available widget to address.  Also, if your app makes use of auto_increment integer primary keys, then you will likely have different records on the two databases that have the same keys.  I think you have two options:

If you have access to mysql binary logs on the old server, you can replay them from the time of transfer to see what DML SQLs were run, and you can run them on the new server.  Again, take note if there are auto-generated keys which you will need to assign new values to.

Other option is to import the old database to a different schema on the new server, then you do left outer joins on each table to determine what's on the old that isn't in the new.  You can make your SQL genrate the insert statement needed by using the concat function i.e. select concat('insert into tablename (col1, col2) values (', col1, ',', col2, ')') from .....
0
 
LVL 25

Accepted Solution

by:
Kyle Hamilton earned 0 total points
ID: 37750284
Thanks,

I ended up doing a little of two things:

INSERT IGNORE INTO `username_database`.`table` 
SELECT * 
FROM `username_databaseOld`.`table`

Open in new window


and a bunch of manual tweaking of id's and such...

What a pain the b*****

:)
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 37750298
Yup that's why for me it's better to just take a short downtime than to have to deal with such things. =)
0
 
LVL 25

Author Closing Comment

by:Kyle Hamilton
ID: 37764988
I'm accepting my solution not because it's a good one, but because it's what I did in the end to fix my issue. Thanks for your help.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question