Master – Master Replication Between two MySQL Servers

The most important thing to consider in MySQL Master-Master replication is the auto increment field. In order to avoid getting data for a single field from both servers which breaks replication with the error message : duplicate entry. It is always good idea to set different auto increment for the servers. Let us consider Two server A and B are to be put into master master replication :

1. Edit /etc/my.cnf to reflect the following configuration :

server-id = 5 replicate-same-server-id = 0 auto-increment-increment = 2 // number of masters auto-increment-offset = 1 relay-log = /var/lib/mysql/serverA-relay-bin relay-log-index = /var/lib/mysql/serverA-relay-bin.index log-error = /var/log/mysql/mysql.err master-info-file = /var/lib/mysql/mysql-master.info relay-log-info-file = /var/lib/mysql/serverA-relay-log.info log-bin = /var/lib/mysql/ServerA-bin

Server B :

2. Edit /etc/my.cnf to reflect the following configuration :

server-id = 6 replicate-same-server-id = 0 auto-increment-increment = 2 auto-increment-offset = 2 relay-log = /var/lib/mysql/serverB-relay-bin relay-log-index = /var/lib/mysql/serverB-relay-bin.index log-error = /var/log/mysql/mysql.err master-info-file = /var/lib/mysql/mysql-master.info relay-log-info-file = /var/lib/mysql/serverB-relay-log.info log-bin = /var/lib/mysql/ServerB-bin

Server A will start at 1 and increment 2 always giving odd numbers****Server B will start at 2 and increment by 2 always giving even numbers. this will prevent the autoincrement field having duplicates when cross replicating. 3. Enable /disable binary logging from command line ( SET sql_log_bin = { 0 | 1 }4. Restart mysql on both the servers5. Create Mysql replication User Accounts on both the servers .

grant replication slave on . to 'replication'@ identified by 'password';

On your current live database, login and create lock and start dumping data.

1. Login To the Existing mysql database and issue the following command :

FLUSH TABLES WITH READ LOCK; Show master status;

Note the Binary Log Position and the Binary Log name from the result of above command.

mysqldump --all-databases --lock-all-tables  > dbdump.db UNLOCK TABLES;

2. Now you can copy the dump to the slave machine, import it and then issue the following command :

CHANGE MASTER TO MASTER_HOST=’masterservername or ip’, MASTER_LOG_FILE=’binarylogname’,MASTER_USER='replication_username', MASTER_PASSWORD='replication_user_password', MASTER_LOG_POS=’binarylogposition from step  above’; (NO QUOTES FOR LOG POSITION)

Note : If the server is already a slave, stop slave, import the database , execute the command above and  then start the slave.

FirewallD Rules:

firewall-cmd --get-services firewall-cmd --get-active-zones firewall-cmd --zone=public --add-service=mysql --permanent firewall-cmd --add-rich-rule 'rule family="ipv4" source address="slaveIP" service name="mysql" accept' --permanent firewall-cmd --reload

Create Database, table and test:

Create Database replicationtest;

CREATE TABLE users( ID int NOT NULL AUTO_INCREMENT, namevarchar(255) NOT NULL, PRIMARY KEY (ID) );