MySql Replication - Master Master - Producing Conflict

Conflict is when the Master database are not in sync.

MySQL Master Master have a cool conflict. When the latency between two server is high you could get a case where two database
could be modifying the same row and instead of clobbering each other. The data from the database swaped instead.

Server A            Server B

row 1: c            row 1: c

change row 1 = a    change row 1 = b

High Latency

get change from b   get change from a

set row 1 = b       set row 1 = a 

row 1: b            row 1: a

Trying it your Self

sql1.example.com

create database test;

use test; 

CREATE TABLE animals (
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=MyISAM;

insert into animals set name = "chicken";

Now both database will have one animal in the animals table.

id:1, name:chicken

sql1.example.com

stop slave

sql2.example.com

stop slave

sql1.example.com

update animals set name = "dog" where id=1;

sql2.example.com

update animals set name = "cat" where id=1;

sql1.example.com

start slave

sql2.example.com

start slave

Now the bug kick in and we get a dog in sql2 and a cat in sql1

We can also produce conflicting by simultaneously insert a record in each server with conflicting primary/unique key. 

MySql Replication - Master Master - From Scratch

Installing MySQL Replication

sql1.example.com (ubuntu), sql2.example.com (ubuntu)

aptitude install mysql-server mysql-client

Configuring MySQL

sql1.example.com: my.conf

server-id                       = 1
log-slave-updates
replicate-same-server-id        = 0
auto_increment_increment        = 5
auto_increment_offset           = 1
report-host                     = sql2.example.com
master-host                     = sql2.example.com
master-user                     = username
master-password                 = password
innodb_flush_log_at_trx_commit  = 1
sync_binlog                     = 1
skip-slave-start                = true
binlog-format                   = ROW


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

relay-log = /var/log/mysql/relay.log
relay-log-info-file = /var/log/mysql/relay-log.info
relay-log-index = /var/log/mysql/relay-log.index

#comment out

#bind-address 127.0.0.1

sql2.example.com: my.conf

server-id                       = 2
log-slave-updates
replicate-same-server-id        = 0
auto_increment_increment        = 5
auto_increment_offset           = 2
report-host                     = sql1.example.com
master-host                     = sql1.example.com
master-user                     = username
master-password                 = password
innodb_flush_log_at_trx_commit  = 1
sync_binlog                     = 1
skip-slave-start                = true
binlog-format                   = ROW


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

relay-log = /var/log/mysql/relay.log
relay-log-info-file = /var/log/mysql/relay-log.info
relay-log-index = /var/log/mysql/relay-log.index

#comment out

#bind-address 127.0.0.1

Restarting MySQL
For newer ubuntu 9.04++, use the following it faster

sql1.example.com , sql2.example.com

restart mysql

On older machine use the following

sql1.example.com , sql2.example.com

/etc/init.d/mysql restart

Creating the replication account

sql1.example.com

mysql -u root -p
CREATE USER 'username'@'sql2.example.com' 

IDENTIFIED BY 'password';
   GRANT ALL PRIVILEGES ON *.* 

TO 'username'@'sql2.example.com' 

WITH GRAND OPTION;
FLUSH PRIVILEGES;

sql2.example.com

CREATE USER 'username'@'sql1.example.com' 

IDENTIFIED BY 'password';
   GRANT ALL PRIVILEGES ON *.* 

TO 'username'@'sql1.example.com' 

WITH GRAND OPTION;
FLUSH PRIVILEGES;

Starting Replication

sql1.example.com, sql2.example.com

mysql -u username -ppassword -e "start slave"

Finish