MySQL Master-Master replication

***NOTE : If you are using mysql 5.5 or below, use the below link for configuration :

https://gopukrish.wordpress.com/2014/05/29/mysql-master-master-replication/

 

For MySQL servers above 5.5, use the below method :

Suppose servers are

server1=0.0.0.1
Server2=0.0.0.2

In Server1, change the /etc/mysql/my.cnf

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
#bind-address           = 127.0.0.1

Server2, change the /etc/mysql/my.cnf

server-id               = 2
log_bin                 = /var/log/mysql/mysql-bin.log
#bind-address           = 127.0.0.1

In server1,

mysql> CREATE USER 'slave1'@'0.0.0.2' IDENTIFIED BY 'password33';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'0.0.0.2';

FLUSH TABLES WITH READ LOCK;

mysqldump -u root -p --all-databases --master-data > /data_dump.sql

copy to server2. Restore the database to the server2

CHANGE MASTER TO   MASTER_HOST='0.0.0.1',   MASTER_USER='slave1',   MASTER_PASSWORD='password33',   MASTER_PORT=3306,   MASTER_LOG_FILE='mysql-bin.000002',   MASTER_LOG_POS=107;

start slave;

In server1,

unlock tables;

In server2,

mysql> CREATE USER 'slave2'@'0.0.0.1' IDENTIFIED BY 'password33';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave2'@'0.0.0.1';

FLUSH TABLES WITH READ LOCK;

show master status\G

In server1,

mysql> CHANGE MASTER TO   MASTER_HOST='0.0.0.2',   MASTER_USER='slave2',   MASTER_PASSWORD='password33',   MASTER_PORT=3306,   MASTER_LOG_FILE='mysql-bin.000001',   MASTER_LOG_POS=537678;

In server2,

unlock tables;

If you have any user permissions and want to see the users and their permissions, run the below query in mysql command prompt :

SELECT user, host FROM user order by user, host;

Now create a database in server1 and check whether it is replicated in server2. If so, drop the database from server2 and confirm that it is removed from the server1 as well.

To create test database :

create database gtest;

To create test table :

CREATE TABLE gtest(id INT, name VARCHAR(20), email VARCHAR(20));

To Delete database :

drop database gtest;

To delete table

drop table gtest

Thanks 🙂