***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 🙂
