Mysql Replication setup

Mysql Replication

Replication enables data from one MySQL database server (the master) to be replicated to one or more MySQL database servers (the slaves).

Replication configuration:

To configure a Master instance, simply ensure a unique server id is assigned and that binary logging is enabled in the instance's my.cnf file.

server_id = <unique number>
log_bin = /opt/mysql/instance/<instance name>

On slave instance,we need to enable unique server id in my.cnf file

server_id = <unique number>

server id should be different on master and slaves

Where:

server_id: a unique number among all participating replication nodes

log_bin: binary log directory

For more on binlogs http://dev.mysql.com/doc/refman/5.0/en/binary-log.html

To replicate a new or existing instance, follow basic steps:

1) Create a fresh master and slave instance using the standard architecture and the parameters listed above.
2) Create a replication user on the master.

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'IP_ADDRESS' IDENTIFIED BY "PASSWORD"
3) Create a consistent snapshot of the Master node (this will lock the DBs in the mysql instance while running),This needs to be run on master server

MYSQLDUMP -UROOT -PPASSWORD -A -R --MASTER-DATA=2 > /BACKUP/INSTANCENAME.DMP

4) Move consistent snapshot from master to slave using scp.

SCP /BACKUP/INSTANCENAME.DMP ROOT@IP_ADDRESS:/BACKUP

5) Import the snapshot into the Slave

MYSQL -UROOT -PPASSD < /BACKUP/INSTANCENAME.DMP

6) Determine the MASTER_LOG_FILE and MASTER_LOG_POS values from the dmp file.

HEAD -50 /BACKUP/INSTANCENAME.DMP | GREP "CHANGE MASTER TO MASTER_LOG_FILE"

7) Issue a CHANGE MASTER ... command on the SLAVE, this will point the slave to the master.

CHANGE MASTER TO MASTER_HOST='IP_ADDRESS or INSTANCE_NAME of master server',
MASTER_PORT=3306,
MASTER_USER='repl',
MASTER_PASSWORD='password',
{Copy the output from step 6 (skip the first three words) and insert here}

8) Issue a START SLAVE command on the slave to begin replicating

START SLAVE;