MySQL Master-Master Replication (below 5.5)

Follow the below steps to configure MySQL Master-Master replication (version < 5.5) between two servers :

I am using two servers with the following IP address :

Master 1 IP : 0.0.0.1
Master 2 IP : 0.0.0.2

STEP 1 :

Configure both the server’s configuration files under [mysqld] section :

*****In Master1 ******

Add the below lines to the my.cnf file :

bind-address = 0.0.0.0

server-id = 1

replicate-same-server-id=0
auto-increment-increment=2
auto-increment-offset=1

master-host = 0.0.0.2
master-user = slaveuser
master-password = mycomplexpassword
master-connect-retry = 60

log-bin=/var/lib/mysqllogs/bin-log
expire_logs_days = 10
max_binlog_size = 100M

relay-log=/var/lib/mysqllogs/relay-log
relay-log-index=/var/lib/mysqllogs/relay-log.index

*****In Master2 **********

Add the below lines to the my.cnf file :

bind-address = 0.0.0.0

server-id = 2

replicate-same-server-id=0
auto-increment-increment=2
auto-increment-offset=2

master-host = 0.0.0.1
master-user = slaveuser
master-password = mycomplexpassword
master-connect-retry = 60

log-bin=/var/lib/mysqllogs/bin-log
expire_logs_days = 10
max_binlog_size = 100M

relay-log=/var/lib/mysqllogs/relay-log
relay-log-index=/var/lib/mysqllogs/relay-log.index

STEP 2 :

Restart the mysql service on both the serves :

root@test1:~# service mysql restart
root@test2:~# service mysql restart

Points :

Make sure that the directory /var/lib/mysqllog exists with mysql:mysql permission
Check more errors in /var/log/mysql/error.log

STEP3 :

Configure the slave user in Master 1. Login to the mysql console in the Master 1 and run the below command :

root@test1:~# mysql -u root -p

mysql> GRANT replication slave ON *.* TO slaveuser@’0.0.0.2′ IDENTIFIED BY ‘mycomplexpassword’;

Take mysql backup in the master1 :

root@test1:~# mysqldump -u root -p –all-databases –master-data > ~/primary.sql

Move the backup to the master2 :

root@test1:~#scp -p 22 ~/primary.sql root@0.0.0.2:/root

STEP4 :

On Master2, restore the mysql backup which have been taken from Master1

Note : Stop the slave service in Master2 before restoring the backup.
root@test2:~# mysql -u root -p

mysql> GRANT replication slave ON *.* TO slaveuser@’0.0.0.1′ IDENTIFIED BY ‘mycomplexpassword’;

mysql> stop slave;
root@test2:~# mysql < /root/primary.sql

Now check the master’s log file and it’s position. You can check it from the mysqldump file itself.

root@test2:~# cat primary.sql | grep ‘CHANGE MASTER TO MASTER_LOG_FILE’ | more

Note down the fields MASTER_LOG_FILE= and MASTER_LOG_POS= , those contains the Master1 status. Instruct the slave to start with the fields we are already noted down.

Login to the mysql console in Master2

root@test2:~# mysql -u root -p

mysql> CHANGE MASTER TO master_host=’0.0.0.1′, master_port=3306, master_user=’slaveuser’, master_password=’mycomplexpassword’, MASTER_LOG_FILE=’bin-log.000001′,

MASTER_LOG_POS=25427;

mysql> START SLAVE;

mysql> SHOW SLAVE STATUS\G

mysql>SHOW MASTER STATUS\G

Note down the MASTER_LOG_FILE= and MASTER_LOG_POS= from the Master2 status which we need to enter in the Master1 while starting the slave.

STEP5 :

On Master1, Start the client with the Master2’s status :

root@test1:~# mysql -u root -p

mysql> slave stop;

mysql> CHANGE MASTER TO master_host = ‘0.0.0.2’, master_port=3306, master_user=’slaveuser’, master_password=’mycomplexpassword’, master_log_file=’bin-log.000001′,

master_log_pos=10363106;

mysql> start slave;

mysql> SHOW SLAVE STATUS\G

Now test the replication by creating a sample database in server1. Check whether the database is replicated to the second server. If it

is, try to drop it in master2 and confirm that the replicated database is removed from the Master1 server.

To create database in master1

mysql> create database theG;

To Remove the database in master2

mysql> drop database theG;

Enjoy !!! 🙂

MySQL

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