MySQL DB Synchronization.

Submitted by hemanth on Tue, 01/19/2010 - 16:44

DB replication/reflection is on the most import process while achieving a distributed system, which helps in the DB synchronization between remote hosts.

The concept is pretty simple :

1.Setting up the Master.

2.Setting up the Slave.

3.Let the Slave receive events from master and update DB.

Step 1 : In the master

Edit the mysql configuration : vi /etc/mysql/my.cnf

Find the lines below and change the bind-address to your machine IP.

# Instead of skip-networking the default is now to listen only on localhost which is more compatible and is not less secure.
bind-address =

# Set the master with in [mysqld]

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
binlog_do_db            = <DB_TO_MIRROR>

#Create a user say 'replicator' who takes care of the rest

<b>$ mysql -u root -p</b>
mysql> grant replication slave on *.* to 'replicator'@'%' identified by 'password';
mysql> flush tables with read lock;

WARNING : Don't close the mysql client
Open a new terminal, take a dump and scp it to slave.
mysqldump -u root -p DB2replicate > DB2replicate.sql
scp  DB2replicate.sql remoteuser@slave:/home/slave/

Grant access to the master from the slave
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO slave@"IP/255.255.255.0" identified by 'password';
mysql> show master status\G;
*************************** 1. row ************************
        File: mysql-bin.000467
        Position: 106
        Binlog_Do_DB: <Your_DB>
        Binlog_Ignore_DB: 
 
NOTE DOWN THE POSITION.

Now quit the mysql client :
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql> quit;
Bye

Allow connection form the slave to the master over the network:
sudo iptables -A INPUT -s ip_of_slave_server -p tcp -m tcp --dport 3306 -j ACCEPT
sudo iptables -A INPUT -p tcp -m tcp --dport 3306 -j REJECT --reject-with icmp-port-unreachable

Step 2 : In the slave
Check if you can connect to the master:

$ mysql -u replicator -p -h MASTER_IP

P.S : In case of failure check if the binding address is correct in the master and restart mysql in the master.

In the slaves my.cnf as done in the master

server-id       =  2
master_host     =  ip_of_master
master_user     =  replicator
master_password =  password
replicate_do_db =  database_to_be_replicated

Restart mysql: sudo /etc/init.d/mysql restart
mysql> CHANGE MASTER TO MASTER_HOST='MASTER_IP', MASTER_USER='replicator', MASTER_PASSWORD='PASSWORD', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=<AS_NOTED>;
 
mysql> slave start;
 
mysql> show slave status\G;
*************************** 1. row ************************
Slave_IO_State: Waiting for master to send event

P.S : In case of any change in the salve do :

mysql> stop slave;
mysql> reset slave;
mysql> start slave;
mysql> show slave status\G;

Step 3 : Slave will take care of itself!

EOS :O).

Share this