Sync MySQL tables via ruby gem TableSyncer

by tux
6 min read
Add Comment

There are ways to sync two MySQL tables in a non-GUI method such as mysql triggers or by Maatkit’s MySQL Table Sync, but it’s not that user-friendly.

Of course, if  you want the GUI-type, there’s always the ever dependable phpMyAdmin or the NaviCat program.

On this tutorial, I will be implementing the “TableSyncer” tool, a ruby gem built that was originally detailed on http://code.google.com/p/ruby-roger-useful-functions/wiki/TableSyncer

This was tested on CentOS 5.3 32-bit running in Pentium4@3.0GHz 1G machine.

Here are the step-by-step procedure:

1. Download rubygem by “yum” or by source. This is how I built the rubygem
#wget http://rubyforge.org/frs/download.php/60718/rubygems-1.3.5.tgz
tar zxvf rubygems-1.3.5.tgz

2. Run ruby setup.rb
#ruby setup.rb
[root@freelinuxtutorials rubygems-1.3.5]# ruby setup.rb

3. If you encounter error such as this, you need to install ruby-related packages
RubyGems 1.3.5 installed

sample error:
./lib/rubygems/custom_require.rb:31:in `gem_original_require’: no such file to load —
rdoc/rdoc (LoadError)
from ./lib/rubygems/custom_require.rb:31:in `require’
from ./lib/rubygems/commands/setup_command.rb:352:in `run_rdoc’
from ./lib/rubygems/commands/setup_command.rb:247:in `install_rdoc’
from ./lib/rubygems/commands/setup_command.rb:120:in `execute’
from ./lib/rubygems/command.rb:257:in `invoke’
from ./lib/rubygems/command_manager.rb:132:in `process_args’
from ./lib/rubygems/command_manager.rb:102:in `run’
from ./lib/rubygems/gem_runner.rb:58:in `run’
from setup.rb:35

4. Install ruby packages

#[root@freelinuxtutorials rubygems-1.3.5]# yum install ruby*

5. If successful, here’s sample output:

[root@freelinuxtutorials rubygems-1.3.5]# ruby setup.rb
RubyGems 1.3.5 installed

=== 1.3.5 / 2009-07-21

Bug fixes:

* Fix use of prerelease gems.
* Gem.bin_path no longer escapes path with spaces. Bug #25935 and #26458.

Deprecation Notices:

* Bulk index update is no longer supported (the code currently remains, but not
the tests)
* Gem::manage_gems was removed in 1.3.3.
* Time::today was removed in 1.3.3.

RubyGems installed the following executables:
/usr/bin/gem

6. Now install table_syncher via “gem” command

#[root@freelinuxtutorials rubygems-1.3.5]# gem install table_syncher
ERROR:  could not find gem table_syncher locally or in a repository

* If error, then you need to download the gem and run the gem command
#wget http://rubyforge.org/frs/download.php/48927/table_syncer-0.3.1.gem

7. Install table_syncer

#[root@freelinuxtutorials src]# gem install table_syncer-0.3.1.gem
Welcome to the wonderful world of table syncer:

For more information on table_syncer, see http://table_syncer.rubyforge.org
run table_syncer –help for more info

comments/feedback/suggestions rogerpack2005@gmail.com
Successfully installed table_syncer-0.3.1
1 gem installed
Installing ri documentation for table_syncer-0.3.1…
Installing RDoc documentation for table_syncer-0.3.1…

6. run
[root@freelinuxtutorials src]# table_syncer –help
[root@freelinuxtutorials src]# find / -name “table_syncer.rb” -print
/usr/lib/ruby/gems/1.8/gems/table_syncer-0.3.1/lib/table_syncer.rb

8. Find the table_syncer.rb

#[root@freelinuxtutorials src]find / -name “table_syncer.rb” -print

9. Edit table_syncer.rb based on your settings, a backup of orig file is recommended:

#[root@freelinuxtutorials src]# cd /usr/lib/ruby/gems/1.8/gems/table_syncer-0.3.1/lib/
[root@freelinuxtutorials lib]# ls
table_syncer  table_syncer.rb
[root@freelinuxtutorials lib]# cp table_syncer.rb table_syncer.rb.orig

#[root@freelinuxtutorials lib]vi /usr/lib/ruby/gems/1.8/gems/table_syncer-0.3.1/lib/table_syncer.rb

local_db = {:host => ‘127.0.0.1’, :user => ‘root’, :password => ‘dbpass’, :db =>

‘dbname’}
local_db2 = {:host => ‘127.0.0.1’, :user => ‘root’, :password => ‘dbpass”, :db =>

‘dbname2”}

*This setting denotes that your defining dbname1 to dbname2 on your local MySQL

10. You need to install the mysql gem module in able to connect to your database

#[root@freelinuxtutorials]# gem install mysql
ERROR:  Error installing mysql:
mysql requires Ruby version >= 1.8.6

If you get an error same as above, you need to upgrade your ruby to 1.8.6

HOW TO UPGRADE RUBY:

You need to create a ruby repository on /etc/yum.repos.d containg the 1.8.6 rpms

a. #[root@freelinuxtutorials rubygems-1.3.5]# vi /etc/yum.repos.d/ruby.repo

Add these lines:

[ruby]
name=ruby
baseurl=http://repo.premiumhelp.eu/ruby/
gpgcheck=0
enabled=0

b.  To include it in your repository,you need to use the parameter -enablerepo=ruby

#[root@freelinuxtutorials rubygems-1.3.5]# yum –enablerepo=ruby list *RUBY*
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: mirror-fpt-telecom.fpt.net
* updates: mirror-fpt-telecom.fpt.net
* addons: mirror-fpt-telecom.fpt.net
* extras: mirror-fpt-telecom.fpt.net
ruby

|  951 B     00:00

primary.xml.gz
| 6.1 kB     00:00
ruby                                                           18/18
Installed Packages
ruby.i386                                               1.8.5-5.el5_3.7
installed
ruby-devel.i386                                         1.8.5-5.el5_3.7
installed
ruby-docs.i386                                          1.8.5-5.el5_3.7
installed
ruby-irb.i386                                           1.8.5-5.el5_3.7
installed
ruby-libs.i386                                          1.8.5-5.el5_3.7
installed
ruby-mode.i386                                          1.8.5-5.el5_3.7
installed
ruby-rdoc.i386                                          1.8.5-5.el5_3.7
installed
ruby-ri.i386                                            1.8.5-5.el5_3.7
installed
ruby-tcltk.i386                                         1.8.5-5.el5_3.7
installed
Available Packages
eruby.i386                                              1.0.5-6.1
base
eruby-devel.i386                                        1.0.5-6.1
base
eruby-libs.i386                                         1.0.5-6.1
base
ruby.i686                                               1.8.6.111-1
ruby
ruby-devel.i686                                         1.8.6.111-1
ruby
ruby-docs.i686                                          1.8.6.111-1
ruby
ruby-irb.i686                                           1.8.6.111-1
ruby
ruby-libs.i686                                          1.8.6.111-1
ruby
ruby-mode.i686                                          1.8.6.111-1
ruby
ruby-mysql.i686                                         2.7.4-1
ruby
ruby-postgres.i686                                      0.7.1-6
ruby
ruby-rdoc.i686                                          1.8.6.111-1
ruby
ruby-ri.i686                                            1.8.6.111-1
ruby
ruby-tcltk.i686                                         1.8.6.111-1
ruby
rubygems.noarch                                         0.9.4-2
ruby
subversion-ruby.i386                                    1.4.2-4.el5_3.1
updates

c. Default package of ruby on CentOS 5.3 is ver. 1.8.5 , so to upgrade, execute command like:
#yum –enablerepo=ruby upgrade

d. Now to check if it’s already upgrade to 1.8.6X
#[root@freelinuxtutorials rubygems-1.3.5]# rpm -qa |grep ruby

11. Install now the mysql gem

[root@freelinuxtutorials rubygems-1.3.5]# gem install mysql
This is a successful install output:

Building native extensions.  This could take a while…
Successfully installed mysql-2.8.1
1 gem installed
Installing ri documentation for mysql-2.8.1..

12. Now table_syncer is now installed on  your machine, to verify

[root@freelinuxtutorials rubygems-1.3.5]# which table_syncer
/usr/bin/table_syncer

13. table_syncer now ready for action

#[root@freelinuxtutorials rubygems-1.3.5]# table_syncer –from=local_db –to=local_db2

–tables=darwin
from db: local_db
to db: local_db2
db1 => db2

14. You need to preview to check for errors before committing

previewing (no changes made) run

127.0.0.1:db1 [“table1”]
=> 127.0.0.1:db2 [“table1”]
connecting to to DB…db2connected
now connecting to from DB db1connected
start previewing (no changes made) table table1********************

————ERROR==

Empty the “to” database and retry
ERROR detected a duplicated id (or the lack of id at all) in cat_locale — aborting

[consider clearing [DELETE FROM
cat_locale in the ‘to’ database and trying again, if in a pinch

To fix that error, sometimes you need to empty the target table via truncate then try again.

Succesful Output:

———————————-

[root@freelinuxtutorials rubygems-1.3.5]# table_syncer –from=local_db –to=local_db2 –tables=table1 –commit
from db: local_db
to db1: local_db2
db => db2

—COMMITTING—- run

127.0.0.1:db1 [“table1”]
=> 127.0.0.1:db2 [“table1”]
Continue (y/n)?y

done —COMMITTING—- table1 — updated 0, created 891, deleted 0

……………….
–summary–
table1 — updated 0, created 891, deleted 0
total transfer time 0.594881

About the author

tux

View all posts

Leave a Reply