merge/sync two mysql databases

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.)

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 .....

Featured Post

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …

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…

In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster.
To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment.
To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…