For this example, let assume the following
- ServerA IP Address 10.1.1.20
- ServerB IP Address 10.1.1.30
- ServerC IP Address 10.1.1.40
- You will create a user for replication
- username
replicator@'10.1.1.%' - password
r3plic4t0R - ServerA's Master will be ServerC
- ServerB's Master will be ServerA
- ServerC's Master will be ServerB
- There are no databases on the three Servers
- There are three tables to ignore
- db1.tb1
- db2.tb2
- db3.tb3
Step 01) Add these options under [mysqld] in /etc/my.cnf on ServerA
[mysqld]
server-id=20
log-bin=mysql-bin
relay-log=relay-bin
log-slave-updates
replicate-ignore-table=db1.tb1
replicate-ignore-table=db2.tb2
replicate-ignore-table=db3.tb3
Step 02) Add these options under [mysqld] in /etc/my.cnf on ServerB
[mysqld]
server-id=30
log-bin=mysql-bin
relay-log=relay-bin
log-slave-updates
replicate-ignore-table=db1.tb1
replicate-ignore-table=db2.tb2
replicate-ignore-table=db3.tb3
Step 03) Add these options under [mysqld] in /etc/my.cnf on ServerC
[mysqld]
server-id=40
log-bin=mysql-bin
relay-log=relay-bin
log-slave-updates
replicate-ignore-table=db1.tb1
replicate-ignore-table=db2.tb2
replicate-ignore-table=db3.tb3
Step 05) service mysql restart on ServerA, ServerB, ServerC
Step 06) Create GRANT for replication user on ServerA, ServerB, ServerC
GRANT SELECT,REPLICATION SLAVE,REPLICATION CLIENT
ON *.* to replicator@'10.1.1.%'
IDENTIFIED BY 'r3plic4t0R';
Step 07) Run SHOW MASTER STATUS; on ServerC
Step 08) Run this CHANGE MASTER TO command on ServerA
CHANGE MASTER TO
MASTER_HOST='10.1.1.40',
MASTER_PORT=3306,
MASTER_USER='replicator',
MASTER_PASSWORD='r3plic4t0R',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=<PositionNumberFromStep07>;
Step 09) Run SHOW MASTER STATUS; on ServerA
Step 10) Run this CHANGE MASTER TO command on ServerB
CHANGE MASTER TO
MASTER_HOST='10.1.1.20',
MASTER_PORT=3306,
MASTER_USER='replicator',
MASTER_PASSWORD='r3plic4t0R',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=<PositionNumberFromStep09>;
Step 11) Run SHOW MASTER STATUS; on ServerB
Step 12) Run this CHANGE MASTER TO command on ServerC
CHANGE MASTER TO
MASTER_HOST='10.1.1.30',
MASTER_PORT=3306,
MASTER_USER='replicator',
MASTER_PASSWORD='r3plic4t0R',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=<PositionNumberFromStep11>;
Step 13) START SLAVE; on ServerA
Step 14) Wait 5 seconds, then SHOW SLAVE STATUS\G on ServerA
If Slave_IO_Running=Yes and Slave_SQL_Running=Yes, replication is working
Step 15) START SLAVE; on ServerB
Step 16) Wait 5 seconds, then SHOW SLAVE STATUS\G on ServerB
If Slave_IO_Running=Yes and Slave_SQL_Running=Yes, replication is working
Step 17) START SLAVE; on ServerC
Step 18) Wait 5 seconds, then SHOW SLAVE STATUS\G on ServerC
If Slave_IO_Running=Yes and Slave_SQL_Running=Yes, replication is working