Setup Database Master/Master Replication on MySQL Server

Starting on DB01

cat /etc/my.cnf

[mysqld]

log-bin=mysql-bin
server-id=1
replicate-wild-do-table=replicateddb.%
replicate-ignore-db=test

[mysqld_safe]

err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Restart the service to apply the changes

service mysqld restart

Login to MySQL

mysql –u root –p

Create replication user account with the host set to the Slave Server’s IP.  Make sure the password is 32 characters or less!

mysql> CREATE USER 'replication'@'10.33.34.201' IDENTIFIED BY 'slavepass';

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'10.33.34.201';

mysql> flush privileges;

Run this to setup a read lock and get the binary position of the logs

mysql> FLUSH TABLES WITH READ LOCK;

mysql> SHOW MASTER STATUS;

Mark down the name in the “File” number in the “Position” column returned in this image it’s 120:

Now moving over to DB02, everything should be the same up to the my.cnf configuration

cat /etc/my.cnf

[mysqld]

log-bin=mysql-bin
server-id=2
replicate-wild-do-table=replicateddb.%
replicate-ignore-db=test

sql_mode=NO_ENGINE_SUBSTITUTION

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Setup your databases again on the secondary and import the backup you have in order to have a speedier setup.

Login to mysql on DB02

mysql> CHANGE MASTER TO

->     MASTER_HOST='10.33.34.200',
->     MASTER_USER='replication',
->     MASTER_PASSWORD='slavepass',
->     MASTER_LOG_FILE='recorded_log_file_name',
->     MASTER_LOG_POS=recorded_log_position;

Start the slave on DB02 MySQL

mysql> start slave;

Run this to get the status of the slave

mysql> show slave status\G

Setup the user account for replication the other way

mysql> CREATE USER 'replication'@'10.33.34.200' IDENTIFIED BY 'slavepass';

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'10.33.34.200';

mysql> FLUSH TABLES WITH READ LOCK;

This time, obtain the File Name and Position on DB02

mysql> show master status;

Back on DB01, unlock the tables and setup the Master records to point back to DB02

mysql> UNLOCK TABLES;

mysql> CHANGE MASTER TO

->     MASTER_HOST='10.33.34.201',
->     MASTER_USER='replication',
->     MASTER_PASSWORD='slavepass',
->     MASTER_LOG_FILE='recorded_log_file_name',
->     MASTER_LOG_POS=recorded_log_position;

mysql> start slave;

Finally, back on DB02, unlock the tables

mysql> UNLOCK TABLES;

 

Enjoy.
Mm.,

2 Comments

Mohan (August 13, 2015)

Can Database Master-Slave be upgraded to master Master configuration.?

maxmouse (August 28, 2015)

This article shows the full steps for Master/Master NOT Master/Slave.

Comments are closed.