A master-master setup is an extension of that concept, it’s your standard master-slave setup, but the master database is made to be a slave to the original slave database — so each master is also a slave.
Setup Details
We shall be using the below listed servers and test databases for achieving the configuration.
Master-A: 123.123.123.1
Master-B: 123.123.123.2
Steps Involved
The complete set up actually involves following steps.
Enable binary logging for both databases
Export data from database A
Import data into database B
Setup an SSH tunnel between database A and B
Setup replication permissions
Make B a slave of A
Make A a slave of B
Step 1. Setting Up MySQL Server Master-A
To enable binary logging, edit the MySQL configuration file and add following lines under
[mysqld] section,
$ vim /etc/mysql/my.cnf OR nano /etc/mysql/my.cnf [mysqld] log-bin=mysql-bin binlog-do-db=test_db server-id=1
Restart MySQL server for changes to take effect.
$ service mysql restart
Create an mysql account on Master-1 server with REPLICATION SLAVE privileges through which replication client will connect to master.
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'pass123'; mysql> FLUSH PRIVILEGES;
Block write statement on all the tables, so no changes can be made after taking backup.
mysql> use test_db; mysql> FLUSH TABLES WITH READ LOCK;
Check the current binary log file name (File) and current offset (Position) value using following command.
mysql> SHOW MASTER STATUS;
Take a backup of database and copy it to another mysql server.
$ mysqldump -u root -p test_db > test_db.sql
$ scp test_db.sql xxx.xxx.xxx.xx:/opt/
After completing backup remove the READ LOCK from tables, So that changes can be made.
mysql> UNLOCK TABLES;
Step 2. Setting Up MySQL Server Master-B
Edit mysql Master-2 configuration file and add following values under [mysqld] section.
$ vim /etc/mysql/my.cnf OR nano /etc/mysql/my.cnf [mysqld] log-bin=mysql-bin binlog-do-db=test_db server-id=2
Note: server-id will never be similar with other master or slave servers.
Restart MySQL server. If you have already configured replication use –skip-slave-start in start so as not to connect immediately to master server.
$ service mysql restart
Restore database backup taken from master server.
$ mysql -u root -p test_db < /opt/test_db.sql
Create an mysql account on Master-1 server with REPLICATION SLAVE privileges through which replication client will connect to master.
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'pass123';
mysql> FLUSH PRIVILEGES;
Check the current binary log file name (File) and current offset (Position) value using following command.
mysql > SHOW MASTER STATUS;
Setup option values on slave server using following command.
mysql>CHANGE MASTER TO MASTER_HOST='123.123.123.1', MASTER_USER='repl_user',MASTER_PASSWORD='pass123', MASTER_LOG_FILE='mysql-bin.000011',MASTER_LOG_POS=332;
Step 3: Complete Setup on MySQL Master-1
Login to MySQL Master-1 server and execute following command.
mysql> CHANGE MASTER TO MASTER_HOST='123.123.123.2', MASTER_USER='repl_user',MASTER_PASSWORD='pass123', MASTER_LOG_FILE='mysql-bin.000022',MASTER_LOG_POS=847;
Step 4: Start SLAVE on Both Servers
Execute following command on both servers to start replication slave process.
mysql> SLAVE START;
MySQL Master-Master Replication has been configured successfully on your system