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;