/var/net/sys/admin/blog
| More

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

Share

Leave a comment

Name: (Required)

E-mail: (Required)

Website:

Comment:

 

About FLT

This site is dedicated to everyone who likes to learn and explore the beautiful world of Linux. If you have comments and suggestions, please feel free to email at freelinuxtutorials@gmail.com. I am happy to serve and share things esp. that is free and enjoyable as Linux.