I recently had a challenge dropped in my lap. A site owners web guy moved on and he requested my assistance in figuring out how to sync his databases.
Each database is on a separate server. I thought no problem, right. I launched naivicat, entered each servers credentials and verified the databases names, tables and all looked quite simple. Well, just because it is simple does not mean it is easy.
I proceeded to set up navicat to perform a sync, done it a hundred times. I ran a preview only to discover that a couple of the tables had a different column count and navicat will not sync tables with un-matching number of columns. Also, one table (same col count though) has no primary key. I felt like more of an idiot. I even tried a data transfer. OOPS! Restore B/U. After over 60 hours of trying to figure this out, I am stumped but, will never give up.
This is a HUGE site, live with 150,000 users and each DB is around 300MB. There is no monetary gain.
We only need to update columns with matching headers. Insert new rows, update rows. One direction.
Source Target
+---+---+---+ +---+---+---+---+
| a | b | d | | a | b | c | f |
+---+---+---+ +---+---+---+---+
| x | x | x | | x | x | x | x |
| x | x | x | | x | x | x | x |
+---+---+---+ +---+---+---+---+
So, realizing navicat was not the tool for this nut, I started searching the net to find a hard code solution. I ran across DBI, DBD::mysql modules and mk-table-sync to perform a sync very specifically --column and possibly GroupBy options.
I can write perl and php routines for web apps and have for years when required but, at only a novice level and only for "normal" web site applications. Time to teach this old dog to learn some new tricks.
I would appreciate any type of solution / suggestions to our problem here. Coded or GUI'ed.
Stackoverflow has long been a great resource that has helped me overcome many barriers in my skill level. I thank you all and always look forward to learning from those here.