Configuring Master/Master replication
The following
procedure can be used to setup MySQL in a master/master replication
configuration. We'll call the two MySQL
servers Master1 and Master2. In a dual master setup each
server functions as both a master and a slave to the other server. The following assumptions
are being made:
·
You have two MySQL servers running the same MySQL
version.
·
Neither server is currently active (i.e. they are
not currently accepting inserts or deletions or modifications of any type.)
·
Master1 refers to the first server that has data
that you wish to replicate.
·
Master2 refers to the second server.
·
Master/Slave or Master/Master replication has not
already been implemented.
NOTE: Change IP
addreses and Server-ID’s in the following steps to match your environment.
1.
Configure
Master1 by adding the following to /etc/my.cnf
#Replication Setup
#set server id to the last octet of the
servers ip address
server-id = 150
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1
# change this to a path/name
appropriate to your system
log-bin = /usr/local/mysql/data/mysql-bin
log-bin-index =
/usr/local/mysql/data/mysqld-bin.index
relay-log = /usr/local/mysql/data/mysqld-relay-bin
relay-log-index =
/usr/local/mysql/data/mysqld-relay-bin.index
binlog-format = MIXED
binlog-cache-size = 1M
expire-logs-days = 7
sync-binlog = 1
skip-slave-start
log-slave-updates
2.
Configure
Master2 by adding the following to /etc/my.cnf.
#Replication Setup
#set server id to the last octet of the
servers ip address
server-id
= 148
replicate-same-server-id
= 0
auto-increment-increment
= 2
auto-increment-offset
= 2
# change this to a path/name
appropriate to your system
log-bin = /usr/local/mysql/data/mysql-bin
log-bin-index =
/usr/local/mysql/data/mysqld-bin.index
relay-log = /usr/local/mysql/data/mysqld-relay-bin
relay-log-index =
/usr/local/mysql/data/mysqld-relay-bin.index
binlog-format = MIXED
binlog-cache-size = 1M
expire-logs-days = 7
sync-binlog = 1
skip-slave-start
log-slave-updates
3.
Start MySQL on both servers.
Shell> cd
/usr/local/mysql
Shell> ./bin/mysqld_safe --user=mysql &
4.
Backup the Database on Master 1
Shell> mysqldump -u root -ppassword –-log-error=dump.log
--all-databases -–master_data > master1_snapshot_master_data.sql
5.
Capture the master_log_file name, and position from the newly
created backup
Shell>grep
MASTER_LOG_FILE master1_snapshot_master_data.sql | head -1
CHANGE MASTER TO
MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=390;
6.
Install the Database on Master2
Shell> mysql
–uroot –ppassword < master1_snapshot_master_data.sql
7.
Create
slave user on Master 1:
Adjust the IP address to reflect your systems.
mysql>
use mysql;
mysql> CREATE
USER 'slave_user'@'10.48.92.%' IDENTIFIED BY 'password';
mysql> GRANT
REPLICATION SLAVE ON *.* to 'slave_user'@'10.48.92.%' identified by 'password';
mysql> FLUSH
PRIVILEGES;
8.
Create
slave user on Master 2:
Adjust the IP address to reflect your systems.
mysql>
use mysql;
mysql> CREATE
USER 'slave_user'@'10.48.92.%' IDENTIFIED BY 'password';
mysql> GRANT
REPLICATION SLAVE ON *.* to 'slave_user'@'10.48.92.%' identified by 'password';
mysql> FLUSH
PRIVILEGES;
9.
Synchronize the Servers,
Master1:
mysql>
STOP SLAVE;
Master2: Set Master 2 to be a slave of Master 1
Set the value of “MASTER_LOG_FILE “ and “MASTER_LOG_POS” with the values discovered in step 5 above.
mysql>
STOP SLAVE;
mysql>
SHOW MASTER STATUS\G;
mysql>
CHANGE MASTER TO MASTER_HOST=’<MASTER1-IP>’, MASTER_USER=’slave_user’,
MASTER_PASSWORD=’password’, MASTER_LOG_FILE=’mysqld-bin.XXXXXX’, MASTER_LOG_POS=NNNN;
Master1: Set Master 1 to be a slave of Master 2
We don’t need the Position: value because NOPS only writes to MASTER 1.
mysql>
CHANGE MASTER TO MASTER_HOST =’<MASTER2-IP>’, MASTER_USER=’slave_user’,
MASTER_PASSWORD=’password’;
Master2:
mysql>
START SLAVE;
Master1:
mysql>
START SLAVE;
10. Verify Replication is running.
Execute the following on both
Master1 and Master2. For “SHOW SLAVE STATUS\G;” look for “Seconds_Behind_Master: 0”. For “SHOW PROCESSLIST;” look for “Master has sent all binlog to
slave; waiting for binlog to be updated”
mysql> SHOW SLAVE
STATUS\G;
…snip…
Seconds_Behind_Master: 0
…snip…
mysql> SHOW
PROCESSLIST;
…snip…
Master has sent all binlog to slave;
waiting for binlog to be updated
Slave has read all relay log; waiting
for the slave I/O thread to update it
…snip…
