Configuring MySql Replication

This article will take you through configuring mysql cluster on CentOS, Since configuration is same this should work with other distros like ubuntu.

Replication Master Configuration
Add following in my.cnf
[mysqld]
log-bin=mysql-bin
server-id=1

ubuntu
service mysql start

centos
service mysqld start

Creating a User for Replication ON MASTER

mysql>GRANT REPLICATION SLAVE ON *.* TO 'repl'@'SLAVE-SERVER-IP/HOSTNAME' IDENTIFIED BY 'slavepass';
#OR
mysql>GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'repl';
#OR
mysql>GRANT RELOAD,REPLICATION SLAVE,SUPER,REPLICATION CLIENT ON *.* TO 'repl'@'%' IDENTIFIED BY 'repl';
 
mysql> flush privileges;

Obtaining the Replication Master Binary Log Coordinates -ON MASTER
mysql>FLUSH TABLES WITH READ LOCK;
mysql>SHOW MASTER STATUS;
note down this info for slave server requirement

Creating a Data Snapshot Using mysqldump ON MASTER
only if you already have data on master server which you want to replicate

mysql> FLUSH TABLES WITH READ LOCK;
mysqldump --all-databases --lock-all-tables >dbdump.db
#OR
mysqldump --all-databases --master-data >dbdump.db
mysql> UNLOCK TABLES;

Setting Up Replication with New Master and Slaves

mysql> UNLOCK TABLES; ON MASTER SERVER

Replication Slave Configuration
Add following in my.cnf
[mysqld]
server-id=2
master-host=192.168.1.1
master-user=repl
master-password=repl
master-connect-retry=60
#replicate-do-db=db1 --Enable only for single db replication. e.g db1


mysql>STOP SLAVE;
mysql>CHANGE MASTER TO MASTER_HOST='192.168.1.1', MASTER_USER='repl', MASTER_PASSWORD='repl', MASTER_LOG_FILE='mysql-bin.003', MASTER_LOG_POS=717; 
mysql>START SLAVE;

MASTER_LOG_FILE & MASTER_LOG_POS is tobe defined from SHOW MASTER STATUS

If there any problem with mysql replication
mysql>SHOW SLAVE STATUS \G
ensure followings are yes.
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

If not, to fix.
mysql>STOP SLAVE;
mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql>START SLAVE;

Refer For More details