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 'mypassword';
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
show master status\G

note down the master values that we need to enter in the slave.

copy the bakcup to server2 and run

CHANGE MASTER TO   MASTER_HOST='0.0.0.1',   MASTER_USER='slave1',   MASTER_PASSWORD='mypassword',   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 'mypassword';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave2'@'0.0.0.1';

FLUSH TABLES WITH READ LOCK;

show master status\G

Note down the master values that we need to enter in the next command

In server1,

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

In server2,

unlock tables;

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 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s