MySQL Master Master Replication

Want to support HowtoForge? Become a subscriber!
 
Submitted by sheikhsa (Contact Author) (Forums) on Sun, 2006-09-10 08:31. :: MySQL

MySQL Master Master Repliction Tutorial

This tutorial describes how to set up MySQL master-master replication. We need to replicate MySQL servers to achieve high-availability (HA). In my case I need two masters that are synchronized with each other so that if one of them drops down, other could take over and no data is lost. Similarly when the first one goes up again, it will still be used as slave for the live one.

Here  is a basic step by step tutorial, that will cover the mysql master and slave replication and also will describe the mysql master and master replication.

Notions: we will call system 1 as master1 and slave2 and system2 as master2 and slave 1.

Step 1:

Install mysql on master 1 and slave 1. configure network services on both system, like

Master 1/Slave 2 ip: 192.168.16.4

Master 2/Slave 1 ip : 192.168.16.5 

 

Step 2:

On Master 1, make changes in my.cnf:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1

log-bin
binlog-do-db=<database name>  # input the database which should be replicated
binlog-ignore-db=mysql            # input the database that should be ignored for replication
binlog-ignore-db=test

server-id=1

[mysql.server]
user=mysql
basedir=/var/lib


[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


Step 3:

On master 1, create a replication slave account in mysql.

mysql> grant replication slave on *.* to 'replication'@192.168.16.5 \
identified by 'slave';

and restart the mysql master1. 

 

Step 4:

Now edit my.cnf on Slave1 or Master2 :

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1

server-id=2

master-host = 192.168.16.4
master-user = replication
master-password = slave
master-port = 3306

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


 Step 5:

Restart mysql slave 1 and at

mysql> start slave;
mysql> show slave status\G;


*************************** 1. row ***************************

             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.16.4
                Master_User: replica
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: MASTERMYSQL01-bin.000009
        Read_Master_Log_Pos: 4
             Relay_Log_File: MASTERMYSQL02-relay-bin.000015
              Relay_Log_Pos: 3630
      Relay_Master_Log_File: MASTERMYSQL01-bin.000009
           Slave_IO_Running: Yes
          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: 4
            Relay_Log_Space: 3630
            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: 1519187

1 row in set (0.00 sec)

Above highlighted rows must be indicate related log files and  Slave_IO_Running and   Slave_SQL_Running: must be to YES.

 

Step 6:

On master 1:

mysql> show master status;
+------------------------+----------+--------------+------------------+
| File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------------+----------+--------------+------------------+
|MysqlMYSQL01-bin.000008 |      410 | adam         |                  |
+------------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

The above scenario is for master-slave, now we will create a slave master scenario for the same systems and it will work as master master.

 

Step 7:

On Master2/Slave 1, edit my.cnf and master entries into it:

 [mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
server-id=2

master-host = 192.168.16.4
master-user = replication
master-password = slave
master-port = 3306

log-bin                     #information for becoming master added
binlog-do-db=adam

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

 

Step 8:

Create a replication slave account on master2 for master1:

mysql> grant replication slave on *.* to 'replication'@192.168.16.4 identified by 'slave2';

 

Step 9:

Edit my.cnf on master1 for information of its master.

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1


log-bin
binlog-do-db=adam
binlog-ignore-db=mysql
binlog-ignore-db=test

server-id=1
#information for becoming slave.
master-host = 192.168.16.5
master-user = replication
master-password = slave2
master-port = 3306

[mysql.server]user=mysqlbasedir=/var/lib 

 

Step 10:

Restart both mysql master1 and master2.

On mysql master1:

mysql> start slave;

On mysql master2: 

mysql > show master status;

On mysql master 1:

mysql> show slave status\G;


*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.16.5
                Master_User: replica
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: Mysql1MYSQL02-bin.000008
        Read_Master_Log_Pos: 410
             Relay_Log_File: Mysql1MYSQL01-relay-bin.000008
              Relay_Log_Pos: 445
      Relay_Master_Log_File: Mysql1MYSQL02-bin.000008
           Slave_IO_Running: Yes
          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: 410
            Relay_Log_Space: 445
            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: 103799
1 row in set (0.00 sec)

ERROR:
No query specified

Check for the hightlighted rows, make sure its running. Now you can create tables in the database and you will see changes in slave. Enjoy!! 


Please do not use the comment function to ask for help! If you need help, please use our forum.
Comments will be published after administrator approval.
Submitted by Al (not registered) on Mon, 2012-03-19 20:42.
a step by step for creating replication with examples is listed at How to create Replication on Mysql: Step by step
Submitted by Aamir Hussain (not registered) on Mon, 2011-05-23 13:51.
Hi, its really a great tutorial can anybody tell me how i can check High Availability in case of  failover?
Submitted by Ramanath (not registered) on Fri, 2011-03-11 09:37.

Master2/Slave1 is showing both slave staus and master status.but master1/slave2 is showing only master status and if i given start slave command then the below error is coming.So please help me

ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO

Submitted by Adam (not registered) on Sat, 2011-01-15 18:32.

Hi,

 At the end of the session you're getting the "ERROR: No query specified" because your "show slave status" ends with "\G;".

The "\G" token ends a query, so following that you have zero bytes and then another end query token, same as if you'd done "show slave status;;"

Submitted by Anonymous (not registered) on Fri, 2011-03-11 13:43.

ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO

pls help me...............

Submitted by Anonymous (not registered) on Wed, 2011-04-20 13:41.

try "FLUSH SLAVE", then do your "CHANGE MASTER TO ..." and then "START SLAVE" again, this worked for me,

 

Hopefully it works for you too.

Submitted by Luis Loriente (not registered) on Mon, 2010-11-08 23:10.

Great post, I have applied this configuration and works fine, but I think there is a problem if we want a point-in-time recovery for data-loss or accidental corruption of database.

 To get this in an usual configuration (just one mysql server) we have to start from a snapshot with mysqldump (or other) and then apply the log_bin of the server to the point we want, or we can manipulate this log-bin to extract undesired lines.

 With master-master configuration we have two log-bin groups (one from each server) to apply to the initial mysqldump snapshot, so we would need some kind of tool to merge these two log_bin groups in chronological order to get just one and apply it to the initial snapshot.

Due to the master-master configuration we can't activate the log_slave_updates which would generate a full log_bin of changes.

Has anyone thought about this?

Submitted by Eggert (not registered) on Mon, 2010-09-27 15:54.

While this is a great tutorial, there seems to be a glitch in step 2:

binlog-do-db=<database name>  # input the database which should be replicated

should read:

replicate-do-db=<database name>  # input the database which should be replicated

Agree?

Submitted by Michael Pawlowsky (not registered) on Tue, 2011-04-19 02:52.

From the mysql.com site:

 The server evaluates the --binlog-do-db and --binlog-ignore-db options in the same way as it does the --replicate-do-db and --replicate-ignore-db options. For information about how this is done, seeSection 15.2.3.1, “Evaluation of Database-Level Replication and Binary Logging Options”.

Submitted by grozours (not registered) on Sun, 2010-06-13 19:44.

I suppose you have the same mysql username and password for both databases.

I suppose you have the same replication username and password for both databases. 

 

An environment script  mysql-env.sh :

export mysql_username=your_mysql_user

export mysql_password=your_mysql_user_password

export master1=hostname_of_master1

export master2=hostname_of_master2

export master1_ip=ip_of_master1

export master2_ip=ip_of_master2

export replication_user=your_replication_user

export replication_password=your_replication_password

 

Check mysql replication status script check-mysql-replication.sh:

 

#!/bin/bash

source mysql-env.sh


Master1_Slave_IO_Running=$(mysql -Bse "show slave status\G" -u ${mysql_username} -p${mysql_password} -h $master1 | grep Slave_IO_Running | awk '{ print $2 }';)

Master1_Slave_SQL_Running=$(mysql -Bse "show slave status\G" -u ${mysql_username} -p${mysql_password} -h $master1 | grep Slave_SQL_Running | awk '{ print $2 }';)

Master1_Slave_Master_Log_File=$(mysql -Bse "show slave status\G" -u ${mysql_username} -p${mysql_password} -h $master1 | grep "\ Master_Log_File" | awk '{ print $2 }';)

Master1_Slave_Read_Master_Log_Pos=$(mysql -Bse "show slave status\G" -u ${mysql_username} -p${mysql_password} -h $master1 | grep Read_Master_Log_Pos | awk '{ print $2 }';)


Master2_Slave_IO_Running=$(mysql -Bse "show slave status\G" -u ${mysql_username} -p${mysql_password} -h $master2 | grep Slave_IO_Running | awk '{ print $2 }';)

Master2_Slave_SQL_Running=$(mysql -Bse "show slave status\G" -u ${mysql_username} -p${mysql_password} -h $master2 | grep Slave_SQL_Running | awk '{ print $2 }';)

Master2_Slave_Master_Log_File=$(mysql -Bse "show slave status\G" -u ${mysql_username} -p${mysql_password} -h $master2 | grep "\ Master_Log_File" | awk '{ print $2 }';)

Master2_Slave_Read_Master_Log_Pos=$(mysql -Bse "show slave status\G" -u ${mysql_username} -p${mysql_password} -h $master2 | grep Read_Master_Log_Pos | awk '{ print $2 }';)


Master1_Master_File=$(mysql -Bse "show master status\G" -u ${mysql_username} -p${mysql_password} -h $master1 | grep File | awk '{ print $2 }';)

Master1_Master_Position=$(mysql -Bse "show master status\G" -u ${mysql_username} -p${mysql_password} -h $master1 | grep Position | awk '{ print $2 }';)


Master2_Master_File=$(mysql -Bse "show master status\G" -u ${mysql_username} -p${mysql_password} -h $master2 | grep File | awk '{ print $2 }';)  

Master2_Master_Position=$(mysql -Bse "show master status\G" -u ${mysql_username} -p${mysql_password} -h $master2 | grep Position | awk '{ print $2 }';)


# Master 1($master1) / Slave 1($master2)


echo "";

echo "-------------------------------------------";

echo "Master 1($master1) / Slave 1($master2) Status :";

echo "-------------------------------------------";

echo "Master : $Master1_Master_File - Position : $Master1_Master_Position"

echo "Slave  : $Master2_Slave_Master_Log_File - Position : $Master2_Slave_Read_Master_Log_Pos"

echo "Slave_IO_Running  : $Master1_Slave_IO_Running"

echo "Slave_SQL_Running : $Master1_Slave_SQL_Running"

echo "";

if [ "$Master1_Master_File" == "$Master2_Slave_Master_Log_File" ] && [ "$Master1_Master_Position" == "$Master2_Slave_Read_Master_Log_Pos" ] && [ "$Master1_Slave_IO_Running" == "Yes" ] && [ "$Master1_Slave_SQL_Running" == "Yes" ];

then

echo "Status OK";

else

echo "Status KO";

fi

echo "";



# Master 2($master2) / Slave 2($master1)


echo "-------------------------------------------";

echo "Master 2($master2) / Slave 2($master1)"

echo "-------------------------------------------";

echo "Master : $Master2_Master_File - Position : $Master2_Master_Position"

echo "Slave  : $Master1_Slave_Master_Log_File - Position : $Master1_Slave_Read_Master_Log_Pos"

echo "Slave_IO_Running  : $Master2_Slave_IO_Running"

echo "Slave_SQL_Running : $Master2_Slave_SQL_Running"

echo "";

if [ "$Master2_Master_File" == "$Master1_Slave_Master_Log_File" ] && [ "$Master2_Master_Position" == "$Master1_Slave_Read_Master_Log_Pos" ] && [ "$Master2_Slave_IO_Running" == "Yes" ] && [ "$Master2_Slave_SQL_Running" == "Yes" ];

then   

        echo "Status OK";

else   

        echo "Status KO";

fi

echo ""; 

 

 

 

Restore mysql replication script restore-mysql-replication.sh: 

 #!/bin/bash

source mysql-env.sh


ps -ef | grep -v grep | grep -q  mysql-$master1

if [ "$?" -eq "1" ];

then

screen -dmS mysql-$master1 mysql -u ${mysql_username} -p${mysql_password} -h $master1

fi


ps -ef | grep -v grep | grep -q  mysql-$master2

if [ "$?" -eq "1" ];

then

screen -dmS mysql-$master2 mysql -u ${mysql_username} -p${mysql_password} -h $master2

fi


screen -S mysql-$master1 -X -p0 eval "stuff 'STOP SLAVE;'^M"

screen -S mysql-$master1 -X -p0 eval "stuff 'FLUSH TABLES WITH READ LOCK;'^M"


screen -S mysql-$master2 -X -p0 eval "stuff 'STOP SLAVE;'^M"

screen -S mysql-$master2 -X -p0 eval "stuff 'FLUSH TABLES WITH READ LOCK;'^M"


Master2_Master_File=$(mysql -Bse "show master status\G" -u ${mysql_username} -p${mysql_password} -h $master2 | grep File | awk '{ print $2 }';)

Master2_Master_Position=$(mysql -Bse "show master status\G" -u ${mysql_username} -p${mysql_password} -h $master2 | grep Position | awk '{ print $2 }';)


screen -S mysql-$master1 -X -p0 eval "stuff 'CHANGE MASTER TO MASTER_HOST="'"'${master2_ip}'"'", MASTER_PORT=3306,MASTER_USER="'"'${replication_user}'"'", MASTER_PASSWORD="'"'${replication_password}'"'"';^M"

screen -S mysql-$master2 -X -p0 eval "stuff 'CHANGE MASTER TO MASTER_HOST="'"'${master1_ip}'"'", MASTER_PORT=3306,MASTER_USER="'"'${replication_user}'"'", MASTER_PASSWORD="'"'${replication_password}'"'"';^M"



screen -S mysql-$master1 -X -p0 eval "stuff 'CHANGE MASTER TO MASTER_LOG_FILE="'"'$Master2_Master_File'"'",MASTER_LOG_POS=$Master2_Master_Position;'^M"


Master1_Master_File=$(mysql -Bse "show master status\G" -u ${mysql_username} -p${mysql_password} -h $master1 | grep File | awk '{ print $2 }';)

Master1_Master_Position=$(mysql -Bse "show master status\G" -u ${mysql_username} -p${mysql_password} -h $master1 | grep Position | awk '{ print $2 }';)


screen -S mysql-$master2 -X -p0 eval "stuff 'CHANGE MASTER TO MASTER_LOG_FILE="'"'$Master1_Master_File'"'",MASTER_LOG_POS=$Master1_Master_Position;'^M"


screen -S mysql-$master2 -X -p0 eval "stuff 'START SLAVE;'^M"

screen -S mysql-$master2 -X -p0 eval "stuff 'UNLOCK TABLES;'^M"


screen -S mysql-$master1 -X -p0 eval "stuff 'START SLAVE;'^M"

screen -S mysql-$master1 -X -p0 eval "stuff 'UNLOCK TABLES;'^M"


screen -S mysql-$master2 -X -p0 eval "stuff 'quit;'^M"

screen -S mysql-$master1 -X -p0 eval "stuff 'quit;'^M"

Submitted by Charles-Alban Allard (not registered) on Thu, 2010-09-09 13:18.
Thank you for those scripts.
On the first script, there is a little error, I think.

The end shoul be:



# Master 1($master1) / Slave 1($master2)


echo "";

echo "-------------------------------------------";

echo "Master 1($master1) / Slave 1($master2) Status :";

echo "-------------------------------------------";

echo "Master : $Master1_Master_File - Position : $Master1_Master_Position"

echo "Slave  : $Master2_Slave_Master_Log_File - Position : $Master2_Slave_Read_Master_Log_Pos"

echo "Slave_IO_Running  : $Master2_Slave_IO_Running"

echo "Slave_SQL_Running : $Master2_Slave_SQL_Running"

echo "";

if [ "$Master2_Master_File" == "$Master1_Slave_Master_Log_File" ] && [ "$Master2_Master_Position" == "$Master1_Slave_Read_Master_Log_Pos" ] && [ "$Master2_Slave_IO_Running" == "Yes" ] && [ "$Master2_Slave_SQL_Running" == "Yes" ];

then   

        echo "Status OK";

else   

        echo "Status KO";

fi

echo "";


# Master 2($master2) / Slave 2($master1)


echo "-------------------------------------------";

echo "Master 2($master2) / Slave 2($master1)"

echo "-------------------------------------------";

echo "Master : $Master2_Master_File - Position : $Master2_Master_Position"

echo "Slave  : $Master1_Slave_Master_Log_File - Position : $Master1_Slave_Read_Master_Log_Pos"

echo "Slave_IO_Running  : $Master1_Slave_IO_Running"

echo "Slave_SQL_Running : $Master1_Slave_SQL_Running"

echo "";

if [ "$Master1_Master_File" == "$Master2_Slave_Master_Log_File" ] && [ "$Master1_Master_Position" == "$Master2_Slave_Read_Master_Log_Pos" ] && [ "$Master1_Slave_IO_Running" == "Yes" ] && [ "$Master1_Slave_SQL_Running" == "Yes" ];

then

echo "Status OK";

else

echo "Status KO";

fi

echo "";

Submitted by fosiul (registered user) on Mon, 2009-11-23 18:27.

Today , While trying this article, I was keep getting this error for Step 9( Changing Master1 as Slaves for Master2)

---------------------------

091123 18:59:13 [ERROR] Slave I/O: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id

option must be used on slave but this does not always make sense; please check the manual before using it). Error_code: 1593
 --------------------------------
I don't know if I was doing anything wrong or not.
but by adding  "log-slave-updates" solved my issue.
so it was like this :
server-id=1
#information for becoming slave.
master-host = 192.168.16.5
master-user = replication
master-password = slave2
master-port = 3306
log-slave-updates
 
Ref :
(a) http://dev.mysql.com/doc/refman/5.0/en/replication-options-slave.html#option_mysqld_replicate-same-server-id
 
(b) http://dev.mysql.com/doc/refman/5.0/en/replication-options-slave.html#option_mysqld_log-slave-updates

 Thanks

Submitted by Hassan (not registered) on Wed, 2009-11-11 09:39.

do you have any steps for us to setup load balancer with master-master replication what you described above?

 if we are using PHP5, where does the connection point to in the master-master replication?

 I'm looking for failover in this setup, how does it work?

regards

Submitted by Glen Bremner-Stokes (not registered) on Thu, 2009-07-16 21:12.

Thank you for a great post and for taking the time to put it out there.

I now have my sugarcrm system on the web and a local sugarcrm on my laptop working bi-directionally. So I can update a record in either system and the other is instantly updated.

Very very cool and really easy following your instructions. Well done. 

 

Submitted by Chris Dean (not registered) on Wed, 2009-05-06 12:41.

Here are some additional notes on issues you might encounter during this process:

  1. If your first Master DB (Master 1) contains views (at the time of writing) you'll not be able to use the command "load data from master" to clone Master 1 to Master 2 due to this bug: and you'll have to replicate the existing system some other way e.g. copy and paste data files or stream data via mysqldump

  2. If you use auto incremeneting id's and you're using Master Master with both masters active you might want to consider setting the following options on Master 1 and Master 2 so that the auto increment values don't end up clashing in the case where an insert happens to occur at exactly the same time on both servers:

    Make Master 1 only auto-increment odd numbers by adding this to my.cnf under [mysqld]:
    auto_increment_increment= 2
    auto_increment_offset   = 1


    Make Master 2 only auto-increment even numbers by adding this to my.cnf under [mysqld]:
    auto_increment_increment= 2
    auto_increment_offset   = 2

    This is particularly important if you're using auto increment id columns as primary keys

Submitted by Anonymous (not registered) on Mon, 2009-01-26 09:06.
Dear, I have done the Multi master Replication with the proceedure described above. No records are being replicated on both servers. and the status of the Slave shows: Slave_IO_Running: NO Slave_SQL_Running: Yes And same thing are shown on both Servers when looking at the 'show slave status\G;' Please help me out how i can rectify this problem. Regards, Majid
Submitted by Lars (not registered) on Wed, 2011-01-26 09:35.

Please make sure that you have given each server a uniqe server-id


Submitted by tom (not registered) on Wed, 2008-12-03 01:55.

hi adam,

thanks for putting this up. just one possible typo:

in step 2 you have

....
 old_passwords=1

log-bin
binlog-do-db=<database name>  # input the database which should be replicated
....

shouldn't log-bin be followed with a filename like ?

log-bin    =   /var/log/mysql/mysql-bin.log

cheers,
tom

 

 

Submitted by Fabio Cecaro (not registered) on Thu, 2008-11-27 10:53.

Hi sheikhsa,

In a Master-Master replication there are many problems with the autonumber field tables.

Because when a client insert a value in a table in a master on the other master we can have another client with another insert in the same table. So we have a possible conflict when the replication mechanism replies these two records, because if this ID is relationate with another table I think we corrupting the relational logical.

We must fix addend two lines in the my.cnf of two masters

auto_increment_increment

auto_increment_offset

Example in this case we can set to the first Master the values:

auto_increment_increment = 2

auto_increment_offset = 1

 and in the other:

auto_increment_increment = 2

auto_increment_offset = 2.

So in one master we generate only the pairs number and  dispair in the other, when the replication come we haven't conflicts.

Submitted by Marco Fretz (not registered) on Tue, 2008-10-21 14:38.

thank you for this great how-to. works pretty well for me.

 

 

Submitted by da644 (registered user) on Thu, 2007-06-14 15:39.

One thing that is missing from the above, is how to deal with auto-increments so that you do not get collisions if you add data to both masters at the same time. There is an easy way to deal with this if you are using MySQL 5.0.2 or above.

In the my.cnf file on server1 add:

auto_increment_increment=2
auto_increment_offset=1

In the my.cnf file on server2 add:

 auto_increment_increment=2
auto_increment_offset=2

This will make the auto-increment on server1 go, 1,3,5,7,9,etc... and on server2 go, 2,4,6,8,etc... thereby preventing collisions.

Andrew
www.andrewdixon.co.uk

Submitted by krex (not registered) on Fri, 2009-08-14 07:19.

 Try setting on both servers my.cnf:

auto_increment_increment=1

auto_increment_offset=2

Submitted by Andreas (not registered) on Sun, 2008-10-05 15:44.

How will this setup solve collisions in data changes?

"First wins?"

"Don't care?"

Submitted by Anonymous (not registered) on Fri, 2009-04-17 17:49.

for the autoincrement field if you run it buck wild on both u get data for a single field from both servers.

this breaks replication. error duplicate yadda yadda yadda

serverID 1

auto_increment_increment        2                               
auto_increment_offset            1   

------------------------------------------------                             

serverID 2

auto_increment_increment        2                               
auto_increment_offset            2

serverID 1 will start at 1 and increment 2 always giving odd numbers

serverID2 will start at 2 and increment by 2 always giving even numbers.

this will prevent the autoincrement field having duplicates when cross replicating. 

Submitted by Fred59 (not registered) on Wed, 2010-03-17 16:38.

Thanks for this good howto.

Despite writing different auto_increment_offset in master 1 and master2, all my records are always with even number. How is it possible ?

Submitted by Anonymous (not registered) on Thu, 2009-09-17 06:44.

Do you think this setting is required for active-passive  master-master replication?

I don't think as there will be no simulteneous writes that may require alternating auto increment requied!
Submitted by Will Fitch (not registered) on Thu, 2009-03-05 03:50.
Agreed. InnoDB's row-level locking (or any locking for that matter) doesn't propagate via the replication protocol.  This is a big disadvantage and the only solution is to implement a solution on the client code.
Submitted by Matt (not registered) on Thu, 2008-10-16 08:18.

It doesn't.  If both nodes attempt to edit the same record at the same time, you could have problems.  Not sure on the behavior, but most likely the last one to update would win.

 The post described a way to deal with auto-increment data type, which is often use to uniquely index new records.  The clear use of increment interval and offset allows new records to be created without collision.

Submitted by Anonymous (not registered) on Thu, 2010-10-07 14:49.

 I read many comments about how good is using autoincrement to avoid collision.

No one cares about data?  IDs are important for a lot of applications. Correlative IDs are expected usually. What would you think if you see a table an IDs are only even? "I'm loosing data!"

Since it's not the default behaviour for databases, it could create odd situations...

Submitted by Ross (not registered) on Sun, 2010-10-17 04:00.
I think it's a mistake to use id's for anything other than unique identification. I specifically think it's a mistake to attempt to extract business logic from an auto-generated id column. If you want an ordinal column that correlates to row creation time, add a creation_date column with a time value in it.
Submitted by Iain (not registered) on Wed, 2012-09-05 03:59.
Heh, +1 on that front.  We had a system at work that used sequential ID numbers on entries (nothing to do with row IDs in a database but...), then we ran out of usable ID space, and switched to a random "try some numbers till you find a free one" (and later increased the usable ID space anyway).  Turns out people were using it to count the number of new entries created every day and suddenly got nothing, then 1000x the normal volume due to high random numbers.  Tsk tsk, told em how to do it *properly* (there's a created date field, they just weren't using it...)
Submitted by Mohammad Khan (not registered) on Wed, 2013-01-23 18:22.
Hi,

I am following the tutorial and trying to set up master master replication.In step 4, I added

master - host = <IP>

to .ini file on machine 2 ( which is Master 2). The IP I am adding here is the IP of machine1(
which is master1).

However, when restart mysql server on machine 2, It does not start and following error
is shown in logs:

unknown variable master-host=<IP>.

Any suggestions to resolve this issue will be appreciated.

Thanks.
Submitted by Anonymous (not registered) on Thu, 2013-03-07 13:35.

There are breaking changes in Mysql 5.6 see:

 http://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html