|
TableSyncer
This is a ruby gem that allows you to easily sync two Mysql tables [either within the same database or, more usefully, between databases--even ssh tunneled remote ones].
IntroductionSometimes you want to sync data between two databases. This turns out to be non-trivial--and hard to do diffs between the two and 'smartly' update them. Navicat is a GUI solution that does this. This is a non-GUI solution. Which happens to be free (cheaper than Navicat) and open source. How it runsHere's an example: table_syncer --from=local_db --to=remote_db --tables=programs,programs_study_areas # run a preview table_syncer --from=local_db --to=remote_db --tables=programs,programs_study_areas --commit # cause the tables programs,programs_study_areas in remote_db to become identical to programs,programs_study_areas of local_db and that's it. To set it up sudo gem install table_syncer Then run table_syncer --help and it will tell you where the script is located ["edit database descriptions at..."] Now edit that script and replace the fake existing database descriptions with your own example: replace 'example_db_1' and 'example_db_2' with vi /Users/rogerpack/dev/ruby_186_installed/lib/ruby/gems/1.8/gems/table_syncer-0.2.0/lib/table_syncer.rb now add something like local_db = {:host => '127.0.0.1', :user => 'root', :password => '', :db => 'local_dev'}
ties_db = {:host => '127.0.0.1', :user => 'root', :password => '', :db => 'wilkboar_ties', :ssh_host => 'somewhere.come', :ssh_user => 'username there'} # ssh example
remote_db = {:user => 'user', :password => 'pass', :ssh_host => 'somehost.com', :ssh_port => 3022, :ssh_user => 'ssh_login_name', :ssh_local_to_host => '127.0.0.1', :ssh_local_to_port => '4040', :db => 'ds_production'} # the most complex example possibleNow if we don't pass the --commit directive, it does a "preview" of the run, so let's do a preview table_syncer --from=ties_db --to=local_db --tables=users and a commit table_syncer --from=ties_db --to=local_db --tables=users --commit You can also edit the array all_database_names to contain the names of your new connections when you run --help, it will output the names of existing databases You can sync all tables data thus: table_syncer -fties -evictim --tables=ALL_TABLES --commit # copy all data from ties to victim Structure SyncYou can do some structure sync of the database tables, as well. It currently sync's all columns except encodings and indexes, though the primary key is indexed by default. ex: table_syncer -fties -evictim --perform_structure_sync_only --tables=users # compare the two tables, see if they differ table_syncer -fties -evictim --perform_structure_sync_only --tables=users --commit # attempt to make victim:users' structure match ties:users' structure Use it with --tables=STRUCTURE_SYNC_THE_WHOLE_THING to drop any existing tables not found in the origin database and structure sync over all existing tables table_syncer -fties -evictim --perform_structure_sync_only --tables=STRUCTURE_SYNC_THE_WHOLE_THING # structure sync all tables from ties to victim. add --commit to attempt it. If you get warnings that the indices are mis-aligned [they...quite possibly are] then one way to overcome it is to drop the offending table first, then perform the structure sync that it gets recreated with the right indices. Note: you also need to still pass --commit for it to do the structure change. PrerequisitesNote: you'll want ruby installed with the ruby mysql gem sudo gem install mysql or sudo gem install mysqlplus Current LimitationsCan't do an ssh connected db to an ssh connected db on a different host. [i.e. from foreignhost1.com to foreignhost2.com--it currently only handles one] Once you do a commit, you can't go back. There is no transaction surrounding it, and no 'undo' file created, so be careful with the --commit directive! inserts and deletes are currently done serially, so if you have a LOT of them, it will be slow because of the RTT's back and forth. Ask if you want this faster. Doesn't work for "many to many" style tables currently--the only way it can is if you "delete from table_name" first--then it will copy the new ones all in safely. Leave a comment if you'd like any of these added. Leave a comment at all! CompetitorsGUI competitors: Navicat nice, costs money, SQLyog [nag-ware]. Also both aren't command line, when command line can be far faster for small transfers. Command line competitor: http://code.google.com/p/maatkit/ deep in there MIGHT be a table syncer--it's hard to tell, and probably less user friendly than this one. http://blog.behindlogic.com/2008/03/quicksync-synchronizer-for-anything.html looks interesting, too. |
Sign in to add a comment
this gem is superb, thank you, A question from a lazy one, If I just want to add new records without updating existing ones, where should I tweak the code? this is to say, just insert those with ids not existing in the target table, Thanks again,
ok, not so lazy, I read the code,
a quick fix would be inserting a check for a new option,
opts.on('-i', '--inserts_only', 'do_inserts_only') do
and then check for it at line 127 and skip the update if set to true,
correction: put the check on the if check at line ~380 after the increment
cool will roll that in
it's in SVN now