MySQL: How do you set up master-master replication in MySQL? (CentOS, RHEL, Fedora)

Setting up master-master replication in MySQL is very similar to how we set up master/slave replication. You can read up about how to setup master/slave replication in my previous post: How to set up master/slave replication in MySQL. There is obviously pros and cons about using master/master replication. But this is not a post which discuses advantages and disadvantages for using master/master replication. One of the differences between master/master set up and master/slave is that in master/master set up, you have true redundancy. If one server dies, second server can take all the inserts/selects. In master/slave setup, if master dies, you will have to go through steps to make slave become the master. Master/master set up we are going to set up is essentially master/slave and slave/master. Meaning, if you had two servers, db0 and db1, your setup will be db0(master)/db1(slave) and also db0(slave)/db1(master). Here are some assumptions:

Master1 server ip: 10.0.0.1
Master2 server ip: 10.0.0.2
Slave username: slaveuser
Slave pw: slavepw
Your data directory is: /usr/local/mysql/var/

Let us go through the steps you must take on Master1 to enable it to act as master and slave by using following configuration which goes under [mysqld] section:

# let's make it so auto increment columns behave by having different increments on both servers
auto_increment_increment=2
auto_increment_offset=1
# Replication Master Server
# binary logging is required for replication
log-bin=master1-bin
binlog-ignore-db=mysql
binlog-ignore-db=test
# required unique id between 1 and 2^32 - 1
server-id = 1
#following is the slave settings so this server can connect to master2
master-host = 10.0.0.2
master-user = slaveuser
master-password = slavepw
master-port = 3306

Following is the configuration which goes on master2 under [mysqld] section:

# let's make it so auto increment columns behave by having different increments on both servers
auto_increment_increment=2
auto_increment_offset=2
# Replication Master Server
# binary logging is required for replication
log-bin=master2-bin
binlog-ignore-db=mysql
binlog-ignore-db=test
# required unique id between 1 and 2^32 - 1
server-id = 2
#following is the slave settings so this server can connect to master2
master-host = 10.0.0.1
master-user = slaveuser
master-password = slavepw
master-port = 3306

On master1 server, go to mysql> prompt and add the appropriate user:

mysql> grant replication slave on *.* to slaveuser@'10.0.0.2' identified by 'slavepw';

On master2 server do the same but allow right ip:

mysql> grant replication slave on *.* to slaveuser@'10.0.0.1' identified by 'slavepw';

Restart both of the master servers and check slave status:

mysql> show slave status\G

That is all you have to do to set up the replication. Of course there are a lot more configuration options but this should get your replication going and you can tweak from here on.

————————————-
DISCLAIMER: Please be smart and use code found on internet carefully. Make backups often. And yeah.. last but not least.. I am not responsible for any damage caused by this posting. Use at your own risk.

16 thoughts on “MySQL: How do you set up master-master replication in MySQL? (CentOS, RHEL, Fedora)

  1. Karsten Thygesen

    Hi

    I believe, that the server-id should be different in the two master setup’s. One should have server-id=1 and the other server-id=2 or something similar.

    But thanks for a nice roundup!

    Karsten

  2. Michael S Moody

    I generally find it better to do auto-increment = 10, instead of odd/evens like this, in case I need to swap in another master with a different server-id on a regular basis. This gives me 10-19 as possibiliets for id’s, vs just even/odd.

    Michael

  3. Pingback: Log Buffer #87: a Carnival of the Vanities for DBAs

  4. Pingback: MySQL Replication Series: How does MySQL replication works? | Technology: Learn and Share

  5. hernan

    I have a master-master replication, and change a table and a field in an master but I jenera error and comunication is almost loss.

    can you help me, how I can do to add another field to a table without losing comunication

  6. pradeep

    Hi! i am new in mysql and trying to establish master master replication in mysql

    on first server i have added these lines to mysqld section of my.cnf(mysql Ver 14.12 Distrib 5.0.77, for redhat-linux-gnu (i686) using readline 5.1)

    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    log-bin=/var/lib/mysql/mysql-bin.log
    binlog-do-db=sample1
    binlog-ignore-db=mysql
    binlog-ignore-db=information_schema
    binlog-ignore-db=test2
    binlog-ignore-db=sample3
    binlog-ignore-db=example3
    binlog-ignore-db=endpoints
    binlog-ignore-db=meetme
    binlog-ignore-db=test
    binlog-ignore-db=sample2

    server-id=1
    master-host = 192.xxx.x.xxx
    master-user = abc
    master-password = abc_pass
    master-connect-retry = 60

    relay-log = /var/lib/mysql/slave-relay.log
    relay-log-index = /var/lib/mysql/slave-relay-log.index

    On second server i have added these lines to mysqld section of my.cnf(mysql Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (i486) using readline 6.1)

    server-id=2

    master-host = 192.xxx.x.xxx
    master-user = xyz
    master-password = xyz_pass
    master-connect-retry = 60

    relay-log = /var/lib/mysql/slave-relay.log
    relay-log-index = /var/lib/mysql/slave-relay-log.index

    log-bin = /var/lib/mysql/mysql-bin.log
    binlog-do-db = sample1
    binlog-ignore-db=mysql
    binlog-ignore-db=information_schema
    binlog-ignore-db=phpmyadmin

    on first server slave status is

    *************************** 1. row ***************************
    Slave_IO_State: Connecting to master
    Master_Host: 192.xxx.x.xx
    Master_User: abc
    Master_Port: 3306
    Connect_Retry: 60
    Master_Log_File: mysql-bin.000001
    Read_Master_Log_Pos: 36158
    Relay_Log_File: slave-relay.000001
    Relay_Log_Pos: 98
    Relay_Master_Log_File: mysql-bin.000001
    Slave_IO_Running: No
    Slave_SQL_Running: Yes
    Replicate_Do_DB:
    Replicate_Ignore_DB:
    Replicate_Do_Table:
    Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
    Replicate_Wild_Ignore_Table:
    Last_Errno: 0
    Last_Error:
    Skip_Counter: 0
    Exec_Master_Log_Pos: 36158
    Relay_Log_Space: 98
    Until_Condition: None
    Until_Log_File:
    Until_Log_Pos: 0
    Master_SSL_Allowed: No
    Master_SSL_CA_File:
    Master_SSL_CA_Path:
    Master_SSL_Cert:
    Master_SSL_Cipher:
    Master_SSL_Key:
    Seconds_Behind_Master: NULL
    1 row in set (0.00 sec)

    ERROR:
    No query specified

    On second server slave status is

    *************************** 1. row ***************************
    Slave_IO_State: Connecting to master
    Master_Host: 192.xxx.x.xxx
    Master_User: xyz
    Master_Port: 3306
    Connect_Retry: 60
    Master_Log_File:
    Read_Master_Log_Pos: 4
    Relay_Log_File: slave-relay.000003
    Relay_Log_Pos: 4
    Relay_Master_Log_File:
    Slave_IO_Running: No
    Slave_SQL_Running: Yes
    Replicate_Do_DB:
    Replicate_Ignore_DB:
    Replicate_Do_Table:
    Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
    Replicate_Wild_Ignore_Table:
    Last_Errno: 0
    Last_Error:
    Skip_Counter: 5
    Exec_Master_Log_Pos: 0
    Relay_Log_Space: 106
    Until_Condition: None
    Until_Log_File:
    Until_Log_Pos: 0
    Master_SSL_Allowed: No
    Master_SSL_CA_File:
    Master_SSL_CA_Path:
    Master_SSL_Cert:
    Master_SSL_Cipher:
    Master_SSL_Key:
    Seconds_Behind_Master: NULL
    Master_SSL_Verify_Server_Cert: No
    Last_IO_Errno: 1045
    Last_IO_Error: error connecting to master ‘[email protected]:3306’ – retry-time: 60 retries: 86400
    Last_SQL_Errno: 0
    Last_SQL_Error:
    1 row in set (0.00 sec)

    ERROR:
    No query specified

    Do’nt know why Slave_IO_Running is no.

    i have tried a lot to start replication but i am still unable to start replication.
    i have also commented
    #bind-address = 127.0.0.1
    and
    #skip-external-locking

    i need your help. Please send your suggestions and solution for this problem.

    Thanks in advance.

    regards,
    pradeep

  7. Mahendran

    i have done the above setup but it act as master/slave…but i need master to master replication ie if i create a database in one server it will replicate on another and wise-versa …my requirement is only for redundancy…
    please advice me what i have to do for master to master replication….

    thanks in advance
    Mahendran.R

  8. Pingback: MySQL Replication Series: How does MySQL replication work? | Fredonfire

  9. Paul Preston

    Good article – Thank you.

    Few notes:
    – In MySQL 5.1 relay log should be used
    – MySQL 5.1 logs a warning when it starts up:
    120822 17:18:18 [Warning] ‘for replication startup options’ is deprecated and will be removed in a future release. Please use ”CHANGE MASTER” instead.

  10. Pingback: MySQL: How do you set up master-master replication in MySQL? (CentOS, RHEL, Fedora) | Fredonfire

  11. fenty puma

    I together with my friends were following the best advice located on the website and then quickly I got an awful suspicion I never thanked the web site owner for those secrets. These boys appeared to be joyful to study them and now have simply been enjoying these things. We appreciate you genuinely considerably thoughtful as well as for choosing certain exceptional useful guides most people are really eager to learn about. Our own sincere apologies for not expressing appreciation to sooner.

  12. golden goose sneakers

    I actually wanted to compose a brief remark to appreciate you for the remarkable steps you are showing on this site. My incredibly long internet look up has at the end of the day been compensated with wonderful know-how to go over with my friends and family. I ‘d believe that most of us visitors actually are really lucky to live in a fabulous network with many awesome individuals with insightful plans. I feel rather happy to have encountered the webpage and look forward to really more excellent times reading here. Thank you once more for everything.

  13. adidas ultra boost 3.0

    I precisely needed to thank you so much all over again. I do not know the things I might have accomplished in the absence of these tips discussed by you directly on this situation. It had become the frustrating crisis for me personally, however , observing the very specialised technique you processed the issue took me to jump over contentment. I’m just happier for your work and even pray you find out what a great job you’re putting in training many people through your webpage. I’m certain you’ve never met any of us.

  14. moncler

    I definitely wanted to construct a remark to be able to say thanks to you for all of the precious facts you are giving out at this website. My long internet search has at the end been honored with beneficial know-how to exchange with my family. I ‘d believe that we website visitors are very much blessed to live in a useful site with very many brilliant people with insightful concepts. I feel very privileged to have used the web page and look forward to plenty of more enjoyable minutes reading here. Thanks again for all the details.

  15. balenciaga sneakers

    A lot of thanks for all of your effort on this web page. My niece enjoys making time for investigation and it’s obvious why. Most people learn all concerning the compelling medium you present very useful things on your web site and as well encourage participation from others on that idea while our favorite girl has always been starting to learn so much. Take pleasure in the remaining portion of the new year. You are performing a wonderful job.

Leave a Reply

Your email address will not be published. Required fields are marked *