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
