[thelist] Sync 2 MySQL databases

Vail, Warren Warren.Vail at schwab.com
Wed Nov 10 13:06:53 CST 2004

Other than line by line, well, the odds are against that.  How many rows and
how many tables are you talking about?  You are facing several problems with
a mysql database.

1.  Autoincrement fields in both databases probably produced the same keys
in each database.
2.  Any tables that use those auto increment values for maintaining a
relationship between two tables will need to be dealt with.

PHP may provide some good answers for you since implementing a fix with PHP
will be faster than with almost any other language.  I am assuming that for
now your application is all on one box.  I would write an application to
link to the old box and the new box to perform the following;
1.  Isolate the rows on the old box that need to be moved.
2.  Insert the autoincrement rows on the new box identifying their new key
3.  Update any existing rows in other tables that need to include the new
auto increment keys, and insert any new rows for other tables with these new
4.  I would also send a copy of every query to a file for review, just in
case anything has to be fixed manually, and this file will help you identify
rows that need to be fixed.
5.  Finally, after everything is cleaned up, I would sit down and think
about the lessons learned from all this, and how you could have avoided it.
This is probably the most valuable part of the exercise, and is experience
that cannot be gotten in any classroom.

Note: one trick I often employ, yes I've done this too, is to write the code
to generate the update queries and comment out the actual execution of the
query, but write the query text to a file (or display it), and go over the
queries with a fine toothed comb, looking for syntax errors and bad values.
Without actually performing the updates, I have the ability to do multiple
passes to correct error in my code, and there will be some, without creating
an even bigger mess.  Uncommenting the executions and one more pass (you are
committed now), and the cleanup is complete.

Hope this helps,

Warren Vail

> -----Original Message-----
> From: thelist-bounces at lists.evolt.org 
> [mailto:thelist-bounces at lists.evolt.org] On Behalf Of Russ
> Sent: Tuesday, November 09, 2004 7:58 PM
> To: thelist at lists.evolt.org
> Subject: [thelist] Sync 2 MySQL databases
> All,
> I've recently moved some sites from one box to another, and 
> due to circumstances that I could not control, sites on both 
> boxes were receiving data--and the final resting place does 
> not have an all-inclusive set of data.
> That said, I'd like to know if there's a possible, 
> non-line-by-line way of verifying the data from the last 24 
> hours and then updating the current database with any of the 
> duplications (although it occurs to me that I'm going to have 
> data with the same IDs).
> I've a feeling I'm a lost cause here, but if anyone has an 
> insight, I'd appreciate it.
> Thanks,
> Russ
> -- 
> * * Please support the community that supports you.  * * 
> http://evolt.org/help_support_evolt/
> For unsubscribe and other options, including the Tip Harvester 
> and archives of thelist go to: http://lists.evolt.org 
> Workers of the Web, evolt ! 

More information about the thelist mailing list