MySQL Replication Setup On Linux

October 12, 2014 · by dbversity · in MySQL

Introduction:

The purpose of this document is to describe the procedure to configure various replication setups.

Guidelines:

• It is recommended to take MySQL dump on master server when there is no activity. However it is not possible to stop transactions on production critical servers. So note down master status before taking the dump.

• The master (production server) needs to have binary logging turned on to facilitate replication.

• Make sure we have sufficient disk space where we are pointing bin log file location

• All the below cases taught about a new replication setups but if we are trying to build on existing production server it is not possible to lock the tables for holding transactions so we need to take the dump online after noting the master position. All the transactions happened on the master during the dump processes need to be skipped manually on slave while syncing the data.

• If we are trying to build replication on already exiting Master then we need to comment bin log purge before taking dump and master position.

Types of Replication:
1. A to B [Master – Slave Replication]
2. A to B to C to A [Circular Replication]
3. A to B and A to C [Single Master – Multi Slave Replication]
4. A to B and B to A [Not Recommended]

Procedure:
A to B:

There are two types in A to B replication:
1. Full DB Replication
2. Specific DB Replication
Full DB Replication:

Master:

1. Install MySQL as per the standard procedure
2. Edit my.cnf of Master (A) with below parameters
server-id = 8 # Should be unique id between 2 and 2^32 – 1
log-bin = /home/mysql5/mysql-binlogs/’hostname’binlog # required for replication
log-bin-index = / home/mysql5/mysql-binlogs/’hostname’binindex
set-variable = max_binlog_size= 1GB
3. Restart MySQL after above modifications are done
4. Stop transactions on Master by executing below commands and take master position
mysql>FLUSH TABLES WITH READ LOCK;
mysql>SHOW MASTER STATUS;
5. Take the dump on master with standard MySQL Dump command
6. Unlock the tables once dump is completed.
mysql> UNLOCK TABLES;
7. Grant privileges to Slave(B) to connect to Master to fetch the binary log updates
grant replication slave on *.* to repluser@SlaveIP identified by ‘rep12#$’;

Slave:

1. Install MySQL as per the standard procedure
2. Edit my.cnf of Slave (B) with below parameters
server-id = 2 # Should be unique id between 2 and 2^32 – 1
master-host = MasterIP # MUST BE SET
master-user = repluser # MUST BE SET
master-password = rep12#$ # MUST BE SET
master-port = 3306 # optional–defaults to 3306
master-connect-retry = 5 # Slave will try 5 times to connect to master incase of N/W failures
3. Restart MySQL and stop slave
mysql>stop slave;
4. Import data taken from Master
mysql> mysql change master to
MASTER_LOG_FILE=’mysql-bin.003′,
MASTER_LOG_POS=240;
6. Start slave
mysql>start slave;
7. Verify the replication by creating a DB on Master table.
mysql>show slave status;

Specific DB Replication:

Master:

Slave:

1. Install MySQL as per the standard procedure
2. Edit my.cnf of Slave (B) with below parameters
server-id = 2 # Should be unique id between 2 and 2^32 – 1
master-host = MasterIP # MUST BE SET
master-user = repluser # MUST BE SET
master-password = rep12#$ # MUST BE SET
master-port = 3306 # optional–defaults to 3306
master-connect-retry = 5 # Slave will try 5 times to connect to master incase of N/W failures
read-only # This will allow only reads on Slave DB
replicate-wild-do-table=CUST_%_DB.ZS_ROUTE # Replication will happen only for one table.
replicate-do-db = coredb # Replication will happen for one DB
3. Restart MySQL and stop slave
mysql>stop slave;
4. Import data taken from Master
mysql> mysql change master to
MASTER_LOG_FILE=’mysql-bin.003′,
MASTER_LOG_POS=240;
6. Start slave
mysql>start slave;
7. Verify the replication by creating a DB on Master table.
mysql>show slave status;

A to B to C to A[Circular Replication]
Building of A:

1. Install MySQL as per the standard procedure
2. Edit my.cnf of Master (A) with below parameters
###MASTER ENTRIES####
server-id = 1 # Should be unique id between 2 and 2^32 – 1
log-bin = /home/mysql5/mysql-binlogs/’hostname’binlog # required for replication
log-bin-index = / home/mysql5/mysql-binlogs/’hostname’binindex
set-variable = max_binlog_size= 1GB
log-slave-updates
###SLAVE ENTRIES####
master-host = IP of C # MUST BE SET
master-user = repluser # MUST BE SET
master-password = rep12#$ # MUST BE SET
master-port = 3306 # optional–defaults to 3306
master-connect-retry = 5 # Slave will try 5 times to connect to master incase of N/W failures
replicate-ignore-table=coredb.MM_BLASTER # Ignores table in replication
3. Restart MySQL after above modifications are done
4. Stop transactions on Master by executing below commands and take master position
mysql>FLUSH TABLES WITH READ LOCK;
mysql>SHOW MASTER STATUS;
5. Take the dump on master with standard MySQL Dump command
6. Unlock the tables once dump is completed.
mysql> UNLOCK TABLES;
7. Grant privileges to Slave(B) to connect to Master to fetch the binary log updates
grant replication slave on *.* to repluser@IP of B identified by ‘rep12#$’;

Building of B:

1. Install MySQL as per the standard procedure
2. Edit my.cnf of Master (A) with below parameters
###MASTER ENTRIES####
server-id = 2 # Should be unique id between 2 and 2^32 – 1
log-bin = /home/mysql5/mysql-binlogs/’hostname’binlog # required for replication
log-bin-index = / home/mysql5/mysql-binlogs/’hostname’binindex
set-variable = max_binlog_size= 1GB
log-slave-updates
###SLAVE ENTRIES####
master-host = IP of A # MUST BE SET
master-user = repluser # MUST BE SET
master-password = rep12#$ # MUST BE SET
master-port = 3306 # optional–defaults to 3306
master-connect-retry = 5 # Slave will try 5 times to connect to master incase of N/W failures
replicate-ignore-table=coredb.MM_BLASTER # Ignores table in replication
3. Restart MySQL after above modifications are done
4. Stop transactions on Master by executing below commands and take master position
mysql>FLUSH TABLES WITH READ LOCK;
mysql>SHOW MASTER STATUS;
5. Take the dump on master with standard MySQL Dump command
6. Unlock the tables once dump is completed.
mysql> UNLOCK TABLES;
7. Grant privileges to Slave(C) to connect to Master to fetch the binary log updates
grant replication slave on *.* to repluser@IP of C identified by ‘rep12#$’;

Building of C:

1. Install MySQL as per the standard procedure
2. Edit my.cnf of Master (A) with below parameters
###MASTER ENTRIES####
server-id = 3 # Should be unique id between 2 and 2^32 – 1
log-bin = /home/mysql5/mysql-binlogs/’hostname’binlog # required for replication
log-bin-index = / home/mysql5/mysql-binlogs/’hostname’binindex
set-variable = max_binlog_size= 1GB
log-slave-updates
###SLAVE ENTRIES####
master-host = IP of B # MUST BE SET
master-user = repluser # MUST BE SET
master-password = rep12#$ # MUST BE SET
master-port = 3306 # optional–defaults to 3306
master-connect-retry = 5 # Slave will try 5 times to connect to master incase of N/W failures
replicate-ignore-table=coredb.MM_BLASTER # Ignores table in replication
3. Restart MySQL after above modifications are done
4. Stop transactions on Master by executing below commands and take master position
mysql>FLUSH TABLES WITH READ LOCK;
mysql>SHOW MASTER STATUS;
5. Take the dump on master with standard MySQL Dump command
6. Unlock the tables once dump is completed.
mysql> UNLOCK TABLES;
7. Grant privileges to Slave(A) to connect to Master to fetch the binary log updates
grant replication slave on *.* to repluser@IP of A identified by ‘rep12#$’;

A to B and A to C [Single Master – Multi Slave Replication]

This is similar to A to B replication. All we need to do is build C same as B and sync with A.

MySQL Replication Setup On Linux

Leave a Reply

Categories

Categories