Setting Circular Replication in mysql

Question

I have 3 MySQL servers say A,B,C.

What I want to do is that I want to make all of them master as well as slave.If there is update on any of the MySQL Servers it should be replicated to all of the Servers.

I have studied about the circular replication and found it can be implemented with it.

Can anybody please give me all the steps to accomplish the Replication as stated between three servers.

One More Point i want to ignore some of the tables from a database also.

Also What are caveats of this type of replication?

Answer 1

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

UPDATE 2012-05-07 12:22 EDT

The only reason to use auto_increment_offset/auto_increment_increment in this setup would be to do writes to all masters and reads from any master.

There is only one scenrio with this setup where you are not obligated to use auto_increment_offset/auto_increment_increment:

For any given database mydb - If you write to mydb on ServerA only, then read from mydb on ServerA - If you write to mydb on ServerB only, then read from mydb on ServerB - If you write to mydb on ServerC only, then read from mydb on ServerC

Answer 2

Rolando told you how to do it and his answer is excellent. The truth is, circular replication is an awful, terrible, terrible idea. Don't do it. Ever. "Absolutes are never true," but in this case, it's pretty close.

Instead of having a single point of failure, now you have three.

What if one of the servers goes down? Now replication is broken everywhere.

How do you replace it easily and align the binary log positions? You can't without doing some very complicated and error prone binary log analysis.

So, now you have to restore all three servers from a backup. Wait, was that backup taken at the same moment in time? Nope, because there's no way to make a transactional backup across multiple servers. So, you're left with broken replication and attempting to fix it with pt-table-checksum and pt-table-sync...or rebuilding the entire cluster from scratch.

Just because you can do something, doesn't mean you should. Don't do this, really. I'm not kidding.