online prescription solutions
online discount medstore
pills online
buy lorazepam without prescription
xanax for sale
buy xanax without prescription
buy ambien without prescription
ambien for sale
buy modafinil without prescription
buy phentermine without prescription
modafinil for sale
phentermine for sale
lorazepam for sale
buy lexotan without prescription
bromazepam for sale
xenical for sale
buy stilnox without prescription
valium for sale
buy prosom without prescription
buy mefenorex without prescription
buy sildenafil citrate without prescription
buy adipex-p without prescription
librium for sale
buy restoril without prescription
buy halazepam without prescription
cephalexin for sale
buy zoloft without prescription
buy renova without prescription
renova for sale
terbinafine for sale
dalmane for sale
buy lormetazepam without prescription
nobrium for sale
buy klonopin without prescription
priligy dapoxetine for sale
buy prednisone without prescription
buy aleram without prescription
buy flomax without prescription
imovane for sale
adipex-p for sale
buy niravam without prescription
seroquel for sale
carisoprodol for sale
buy deltasone without prescription
buy diazepam without prescription
zopiclone for sale
buy imitrex without prescription
testosterone anadoil for sale
buy provigil without prescription
sonata for sale
nimetazepam for sale
buy temazepam without prescription
buy xenical without prescription
buy famvir without prescription
buy seroquel without prescription
rivotril for sale
acyclovir for sale
loprazolam for sale
buy nimetazepam without prescription
buy prozac without prescription
mogadon for sale
viagra for sale
buy valium without prescription
lamisil for sale
camazepam for sale
zithromax for sale
buy clobazam without prescription
buy diflucan without prescription
modalert for sale
diflucan for sale
buy alertec without prescription
buy zyban without prescription
buy serax without prescription
buy medazepam without prescription
buy imovane without prescription
mefenorex for sale
lormetazepam for sale
prednisone for sale
ativan for sale
buy alprazolam without prescription
buy camazepam without prescription
buy nobrium without prescription
mazindol for sale
buy mazindol without prescription
buy mogadon without prescription
buy terbinafine without prescription
diazepam for sale
buy topamax without prescription
cialis for sale
buy tafil-xanor without prescription
buy librium without prescription
buy zithromax without prescription
retin-a for sale
buy lunesta without prescription
serax for sale
restoril for sale
stilnox for sale
lamotrigine for sale

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.


11 Responses to “ MySQL: How do you set up master-master replication in MySQL? (CentOS, RHEL, Fedora) ”

  1. March 1st, 2008 | 1:24 am

    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. March 1st, 2008 | 1:50 am

    Karsten,

    Good catch. Thats what I get for copying pasting :) It has been corrected.

  3. Michael S Moody
    March 3rd, 2008 | 6:10 am

    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

  4. March 7th, 2008 | 9:48 am

    [...] offers answers to the question, how do you set up master-master replication in MySQL?, laying out the basics of this [...]

  5. April 17th, 2008 | 9:01 am

    [...] following simple directions. You can learn setting up two type of replication MySQL offers here:  master-master replication and master-slave replication. Once you have replication set up, you can start playing with it [...]

  6. hernan
    October 24th, 2008 | 9:34 am

    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

  7. pradeep
    October 22nd, 2010 | 12:24 am

    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

  8. Mahendran
    April 5th, 2011 | 6:06 am

    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

  9. October 14th, 2011 | 7:36 pm

    [...] following simple directions. You can learn setting up two type of replication MySQL offers here: master-master replication and master-slave replication. Once you have replication set up, you can start playing with it [...]

  10. August 22nd, 2012 | 9:25 am

    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.

  11. July 29th, 2013 | 8:32 pm

    [...] more: http://crazytoon.com/2008/02/29/mysql-how-do-you-set-up-mastermaster-replication-in-mysql-centos-rhe… Categories: Database, Information Technology, MySQL Replication, [...]

Leave a reply

*
To prove that you're not a bot, enter this code
Anti-Spam Image