MySQL DB Synchronization.

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 =  

Create a user say ‘replicator’ who takes care of the rest

**$ mysql -u root -p** 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=;

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).