Setup MySQL master-master replication (Windows and Linux)

Add firewall rules:

1. iptables -A INPUT -p tcp -s 192.168.14.13 --sport 3306 -d 192.168.14.3 --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT
2. iptables -A OUTPUT -p tcp -s 192.168.14.3 --sport 3306 -d 192.168.14.13 --dport 3306 -m state --state ESTABLISHED -j ACCEPT

The next step is to configure both mysql using the file my.cnf (usualy on /etc in GNU/Linux) :

on master1 (192.168.14.13), under the mysqld section we add the following :

server-id=1
master-host=192.168.14.3
master-user=repl
master-password=repl
master-port=3306
log-bin=MASTER1

binlog-do-db=replication_db
replicate-do-db=replication_db

binlog-do-db=replication_db2
replicate-do-db=replication_db2

auto_increment_increment      = 10
auto_increment_offset         = 1

and on master2 (192.168.14.3), under the same section :

server-id=2
master-host=192.168.14.13
master-user=repl
master-password=repl
master-port=3306
log-bin=MASTER2

binlog-do-db=replication_db
replicate-do-db=replication_db

binlog-do-db=replication_db2
replicate-do-db=replication_db2

auto_increment_increment      = 10
auto_increment_offset         = 2

explanations of these settings :

• server-id : this is an integer id helping to identify the server (must be unique in your replication farm!)
• master-host : specifies the ip/hostname of the MySQL acting as master for the current server
• master-user : specifies the user used to make the connection to the master
• master-password : is the user's password
• master-port : specifies on which port the master is listening
• log-bin : needed to start the binary logging process
• binlog-do-db : specifies on which databases the binary logging must be active (only those databases will be in the binary log)
• replicate-do-db : which database must be replicated by the server as slave.
• auto_increment_increment : controls the increment between successive AUTO_INCREMENT values.
• auto_increment_offset : determines the starting point for AUTO_INCREMENT column values.

The last two options are needed to prevent key collisions when using AUTO_INCREMENT columns, otherwise multiple masters may attempt to use the same AUTO_INCREMENT value when inserting rows.

optionally two extra options can be added :

• show-slave-auth-info : Display slave usernames and passwords in the output of SHOW SLAVE HOSTS on the master server.
• slave_compressed_protocol={0|1} : this option enable the use of compression for the slave/master protocol if both the slave and the master support it. The default is 0 (no compression). This accelerates the replication process

Creating the replication user on both nodes :

on master1 :

mysql> grant replication slave, replication client on *.* to repl@"192.168.14.3" identified by "repl";
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for repl@"192.168.14.3"; 
+--+
| Grants for repl@192.168.14.3                                                                                          |
+--+
| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'192.168.14.3' IDENTIFIED BY PASSWORD '5ec3db8f603fcb03' | 
+--+
1 row in set (0.00 sec)

on master2 :

mysql> grant replication slave, replication client on *.* to repl@"192.168.14.13" identified by "repl";
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for repl@"192.168.14.13";
+---+
| Grants for repl@192.168.14.13                                                                                          |
+---+
| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'192.168.14.13' IDENTIFIED BY PASSWORD '5ec3db8f603fcb03' | 
+---+
1 row in set (0.00 sec)

Now, restart your both mysql server.

On master1, run this:
mysql>stop slave;
mysql>show master status;
*************************** 1. row ***************************           
       File: MASTER1.000001
       Position: 98
   Binlog_Do_DB: replication

Binlog_Ignore_DB:
1 row in set (0.00 sec)

On master2, run this:
mysql>stop slave;
mysql>show master status;
*************************** 1. row ***************************           
       File: MASTER2.000001
       Position: 87
   Binlog_Do_DB: replication

Binlog_Ignore_DB:
1 row in set (0.00 sec)

mysql>CHANGE MASTER TO

MASTER_LOG_FILE=’MASTER1.000001’, -- The file you got when ran show master status on master1

On master1, run this:
mysql>CHANGE MASTER TO