When you need to have both MySQL servers synchronised and changes to Server 1 should be reflected on Server 2 and vise-versa then you need Master-Master replication. That’s easy to do, here 10 simple steps.
Prerequisites:
OS: Ubuntu Server (or any Linux server)
RDBMS: MySQL Community Edition
Let’s assume that:
Server 1 IP address: 192.168.3.138
Server 2 IP address: 192.168.3.123
We have two databases with the same names on both servers: ‘database_name’ on Server 1 and ‘database_name’ on Server 2;
STEPS:
1. Change /etc/my.cnf of Server 1
server_id = 1 log_bin = /var/log/mysql/mysql-bin.log log_bin_index = /var/log/mysql/mysql-bin.log.index relay_log = /var/log/mysql/mysql-relay-bin relay_log_index = /var/log/mysql/mysql-relay-bin.index expire_logs_days = 10 max_binlog_size = 100M log_slave_updates = 1 auto-increment-increment = 2 auto-increment-offset = 1
2. Change /etc/my.cnf of Server 2
server_id = 2 log_bin = /var/log/mysql/mysql-bin.log log_bin_index = /var/log/mysql/mysql-bin.log.index relay_log = /var/log/mysql/mysql-relay-bin relay_log_index = /var/log/mysql/mysql-relay-bin.index expire_logs_days = 10 max_binlog_size = 100M log_slave_updates = 1 auto-increment-increment = 2 auto-increment-offset = 2
3. If there is some data exists on Server 1 database then we need to export data from the DB on Server 1 and import to another DB on the Server 2 to be on the same page:
On Server 1:
mysql> FLUSH TABLES WITH READ LOCK; $> mysqldump -u root -p database_name > ~/database_name.sql mysql> UNLOCK TABLES;
On Server 2:
$> mysqladmin -u root -p create database_name $> mysql -uroot -p database_name < database_name.sql
4. Create replication users on both servers:
on Server 1
mysql> CREATE USER ‘replication’ IDENTIFIED BY ‘password’
on Server 2
mysql> CREATE USER ‘replication’ IDENTIFIED BY ‘password’
5. Set up replication permissions on both servers
on Server 1 (Server 2 IP Address is on top of this document):
GRANT REPLICATION SLAVE ON *.* TO ‘replication’@’192.168.3.123’ IDENTIFIED BY ‘password’;
on Server 2 (Server 1 IP Address is on top of this document):
GRANT REPLICATION SLAVE ON *.* TO ‘replication’@’192.168.3.138’ IDENTIFIED BY ‘password’;
(check this by doing following command:
from Server 1:
$> mysql -h 192.168.3.123 -P 3306 -u replication -p
from Server 2:
$> mysql -h 192.168.3.138 -P 3306 -u replication -p
If connection successful then everything is ok by far.
6. Check master status and remember information:
on Server 1 (138):
mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000013 | 1010 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
on Server 2 (123):
mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000010 | 314 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
7. Set up Slave Server 1 for Server 2:
mysql> CHANGE MASTER TO master_host=’192.168.3.123′, master_port=3306, master_user=’replication’, master_password=’password’, master_log_file=’mysql-bin.000010′, master_log_pos=314;
8. Set up Slave Server 2 for Server 1:
mysql> CHANGE MASTER TO master_host=’192.168.3.138′, master_port=3306, master_user=’replication’, master_password=’password’, master_log_file=’mysql-bin.000013′, master_log_pos=1010;
9. Start Slave on Server 1:
mysql> START SLAVE;
and check Slave status by this command:
mysql> SHOW SLAVE STATUS\G
10. Start Slave on Server 2:
mysql> START SLAVE;
and check Slave status by this command:
mysql> SHOW SLAVE STATUS\G
Now your to mysql servers are synchronised and all database changes will be reflected in both databases.