Monday, April 9, 2012

How to replicate only one table from mysql database.

Install Ruby 1.9.2
Install gem rubyrep (1.2.0) using command
gem install rubyrep

Then create conf file with name   /etc/myrubyrep.conf and with following content.
-------------------------------------------------------------
RR::Initializer::run do |config|
config.left = {
:adapter  => 'mysql', # or 'pgsql'
:database => 'c11',
:username => 'root',
:password => '',
:socket => '/var/lib/mysql/mysql.sock'
}

config.right = {
:adapter  => 'mysql', # or 'pgsql'
:database => 'c12',
:username => 'root',
:password => '',
:socket => '/var/lib/mysql/mysql.sock'
}

config.include_tables 'table name'  # e.g. 'email_list_subscribers' only this will replicate
#config.include_tables /^e/  # regexp matches all tables starting with e
# config.include_tables /./ # regexp matches all tables
#config.options[:auto_key_limit] = 2
end
---------------------------------------------

Using this command you can replicate only one table from database.

[root@localhost ]#  rubyrep replicate  -c /etc/myrubyrep.conf
Verifying RubyRep tables
Checking for and removing rubyrep triggers from unconfigured tables
Verifying rubyrep triggers of configured tables
Starting replication..

For only scan and sync to check status use following commands:

[root@localhost tmp]#  rubyrep scan   -c /etc/myrubyrep.conf
              email_list_subscribers 100% .........................   0

[root@localhost tmp]#  rubyrep sync  -c /etc/myrubyrep.conf
              email_list_subscribers 100% .........................   0

If modified it will change to 0 to 1.
Cool thing... Please let me know if you guys have any questions.
@ Linux Administration Blog.! 

2 comments:

NishantH said...

Hi,

How to add more databases in rubyrep conf file.

We have around 16 database for now and in future number of database count will reach to 54. Using Rubyrep master-master replication setup, I m able to replicate one database to other remote server. but couldn't able to add more databases in that file. I don't want to maintain multiple configuration files for each database.

Please suggest, How to archive above.

Renu said...

Good Post! Thank you so much for sharing this pretty post, it was so good to read and useful to improve my knowledge as updated one, keep blogging.
Devops Training in electronic city