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.
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
Karsten,
Good catch. Thats what I get for copying pasting 🙂 It has been corrected.
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
Pingback: Log Buffer #87: a Carnival of the Vanities for DBAs
Pingback: MySQL Replication Series: How does MySQL replication works? | Technology: Learn and Share
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
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
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
Pingback: MySQL Replication Series: How does MySQL replication work? | Fredonfire
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.
Pingback: MySQL: How do you set up master-master replication in MySQL? (CentOS, RHEL, Fedora) | Fredonfire
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.
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.
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.
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.
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.