Monday, February 25, 2013

MySQL Replication


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…