Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
@RolandoMySQLDBA:Need your expert comments.. –  Abdul Manaf May 7 '12 at 14:43
add comment

2 Answers

up vote 4 down vote accepted

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

share|improve this answer
    
thanks !!!.I also want to ignore some of the tables to not be replicated on any of the Servers so for this what i should do.? –  Abdul Manaf May 7 '12 at 15:53
    
Updated my answer to show how to ignore three tables –  RolandoMySQLDBA May 7 '12 at 15:57
    
One more think does we also need to set the auto_increment and auto_increment_offset? –  Abdul Manaf May 7 '12 at 16:01
    
Update my answer on auto_increment usage –  RolandoMySQLDBA May 7 '12 at 16:22
    
+1 for the detailed answer, Rolando. I added my slightly biased thoughts on circular replication in my answer. –  Aaron Brown May 8 '12 at 1:36
add comment

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.

share|improve this answer
    
+1 for Your Candor and "Buyer Beware" Answer !!! –  RolandoMySQLDBA May 8 '12 at 2:30
    
I have to agree with your answer because I have had to do just what you are saying with some clients. In some cases, if you do not remember to precede the output of pt-table-sync with "SET SQL_LOG_BIN=0;", unintended changes may cascade and you end up chasing your tail like a crazed dog. Error prone, indeed !!! –  RolandoMySQLDBA May 8 '12 at 2:35
    
@Aaron : Thanks...+1. –  Abdul Manaf May 8 '12 at 3:41
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.