Set Up Database Replication In MySQL

Configure the MySQL Master Server

Step 1 : edit /etc/mysql/my.cnf file. 

#skip-networking

#bind-address            = 127.0.0.1

(add below line in /etc/mysql/my.cnf file)

server-id               = 1

log_bin                 = /var/log/mysql/mysql-bin.log

binlog_do_db            = replicationdb

step 2 :  Restart Mysql server

#/etc/init.d/mysql restart

Step 3 : create a user with replication privileges:

#mysql -u root -p

mysql> GRANT REPLICATION SLAVE ON *.* TO ’replicationuser’@'%’ IDENTIFIED BY ‘<some_password>’; 

mysql>FLUSH PRIVILEGES;

 mysql>USE replicationdb;

mysql>FLUSH TABLES WITH READ LOCK;

mysql>SHOW MASTER STATUS;

Result of above command:

+—————+———-+————–+——————+

| File          | Position | Binlog_do_db | Binlog_ignore_db |

+—————+———-+————–+——————+

| mysql-bin.005 | 180      | replicationdb    |                  |

+—————+———-+————–+——————+

1 row in set (0.00 sec)

 Please remeber above information will need in slave server configuration 

  mysql>quit;

Step 4 : Dump replicationdb from the Master server 

#mysqldump -u root -p<password> replicationdb > replicationdb.sql 

Above command create  dump of replicationdb in the file replication.sql. Transfer this file to your slave server!

Step  5 : Unlock the tables

#mysql -u root -p

mysql>UNLOCK TABLES;

mysql>quit;

 

Configure The Slave Server

Step 1 : Create the database replicationdb

#mysql -u root -p
mysql>CREATE DATABASE replicationdb;
mysql>quit;

Step 2 : Extract the dump database in replicationdb database

#mysql -u root -p<password> replicationdb < /path/to/replicationdb.sql 

Step 3 : Add the below lines into /etc/mysql/my.cnf

server-id=2

master-host=192.168.10.175

master-user=replicationuser

master-password=secret

replicate-do-db=replicationdb

Step 3 : Restart MySQL:

# /etc/init.d/mysql restart

Step 4 :  Allow slave user to connet to Remote Master server

# mysql -u root -p

mysql>SLAVE STOP;

mysql>CHANGE MASTER TO MASTER_HOST=’192.168.10.175′, MASTER_USER=’replicationuser’, MASTER_PASSWORD=’<some_password>’, MASTER_LOG_FILE=’mysql-bin.005′, MASTER_LOG_POS=180;

mysql>START SLAVE;

mysql>quit;

Configuration is Complete now!  So whenever replicationdb is updated on the master server, all changes will be replicated to replicationdb on the slave server.

Enjoy !!!!!