Set Up A New Slave in My SQL Circular Replication

Mysql Replication - Conceptual Overview

Purpose - The purpose of this doc is to add a new slave host when there is already circular replication set up between two mysql servers. Both are acting as Master ad Slave for each other.

Before - A  to B, B to A
After -  A to B, B to A, B to C

Here C is a new slave for which B will be the master.


Steps-

---

---

Prerequisites: Make sure the Port 3306 is open for replication. if in same VLAN then not needed.

In case replication breaks due to some error, start the slave server with option slave-skip-errors = all
See : http://dev.mysql.com/doc/refman/5.0/en/replication-options-slave.html 

Step1: [B] Stop the running slave for ‘A’.

mysql>STOP SLAVE;

Step2: [B]

mysql>SHOW MASTER STATUS\G.

Save ‘File’ and ‘Position’ from the resultset for future reference.
‘File’ will be used for ‘MASTER_LOG_FILE’ and ‘Position’ will be used for ‘MASTER_LOG_POS’ in ‘CHANGE MASTER TO ………’ command (will be performed
later) on slave.

Step3: [B] Create mysql dump of  the database (xyz) for which you want to set up the replication : (Make sure you already have performed step 1 & 2)

mysqldump –uroot –p<MYSQL_PASSWORD> xyz > SQL_DUMP_FILE_NAME

Step4: [B] Start the stopped slave (Step1) for ‘A’.

mysql>START SLAVE;

Step5: [B] Send this sql dump file to Slave C using secure copy command from Master B:

scp <SQL_DUMP_FILE> <USERNAME_OF_SLAVEHOST C>@<SLAVE_HOST C>:<DIRECTORY_WHERE_YOU_WANT_TO_PASTE>

Step6: [B] Create user for replication to C and grant replication slave privilege.

mysql>CREATE USER 'replication'@'<Hostname of C>' IDENTIFIED BY ‘XXXXXX’

mysql>GRANT REPLICATION SLAVE ON *.* to 'replication'@'Hostname of C' IDENTIFIED BY ‘XXXXXX’

Step7: [C] Drop ’xyz’ database, if already exists and create new empty ’xyz’ database.

mysql>DROP DATABASE xyz;

mysql>CREATE DATABASE xyz;

Step8: [C] Import the sql dump file of step 3.

mysql -uroot -p<MYSQL_PASSWORD> xyz < SQL_DUMP_FILE_NAME

Step9: [C] Edit mysql configuration file my.cnf (/etc/my.cnf) and restart mysql server

# vi /etc/my.cnf

Under [mysqld] section, add server-id=<unique server id>

# /etc/init.d/mysqld restart

Step10: [C] Now stop the slave,configure the master for this slave and then start the slave.

mysql>STOP SLAVE;

mysql>CHANGE MASTER TO 
      MASTER_HOST='<Hostname of B>', 
      MASTER_USER='replication', 
      MASTER_PASSWORD='XXXXXX', 
      MASTER_LOG_FILE=’<File_FROM_STEP 2>', 
      MASTER_LOG_POS=< Position_FROM_STEP 2>;

mysql>START SLAVE;

Step11: [Slave] Now check whether replication is started.

mysql> SHOW SLAVE STATUS\G

If value of ‘Slave_IO_State’ and ‘Slave_IO_Running’ are as follows, it means the replication is started.
Slave_IO_State: Waiting for master to send event
Slave_IO_Running: Yes