MySQL Master-Slave Replication Setup

MySQL Master-Slave Replication

Replication enables data from one MySQL database server (the master) to be replicated to one or more MySQL database servers (the slaves). Replication is asynchronous - slaves need not be connected permanently to receive updates from the master. This means that updates can occur over long-distance connections and even over temporary or intermittent connections such as a dial-up service. Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database.

Benefits:-

1. Robustness is increased with a master/slave setup. In the event of problems with the master, you can switch to the slave as a backup.
    
2. Better response time for clients can be achieved by splitting the load for processing client queries between the master and slave servers. SELECT queries may be sent to the slave to reduce the query processing load of the master. Statements that modify data should still be sent to the master so that the master and slave do not get out of synchrony. This load-balancing strategy is effective if no updating queries dominate, but that is the normal case.
   
3.  Another benefit of using replication is that you can perform database backups using a slave server without disturbing the master. The master continues to process updates while the backup is being made.

Pre-Requisite:-

• In Master conf file:

         [mysqld]
         log-bin=mysql-bin
         server-id=1

• In Slave conf file:

         [mysqld]
         log-bin=mysql-bin
         server-id=2

         Post above changes need to restart the MySQL service on both server.

• Grant access rights for replication on Master:

         Grant replication slave on *.* to 'repliation_user'@'slave_ip' identified by ‘password’;
         Flush Privileges;

Steps to Perform:-

• Take a Full backup of master as follows:

       

          shell   > mysqldump –root –p –A --events --routines --triggers --single-transaction --master-data > All_DB_Backup_date.sql

• Transfer the file from Master to Slave

• On Slave: Restore the above full taken Backup.

          Shell > mysql –uroot –p < All_DB_Backup_date.sql (backup file name)

• Post completion of restoration Initiate Sync of Replication:

          Mysql > stop slave;

          Mysql >  

CHANGE MASTER TO MASTER_HOST='x.x.x.x’,  MASTER_USER='repliation_user', MASTER_PASSWORD='repli_password',  MASTER_LOG_FILE='mysql-bin.xxxxxx', MASTER_LOG_POS=xxxxxxxxx;

Note: for above master_log_file and master_log_pos, check mysqldump file with less command.

          Mysql > start slave;

          Mysql > show slave status \G;

• Check below 3 parameters of the above command out put:-

          Slave_IO_Running: Yes                  ------------ > (Should be Yes)

          Slave_SQL_Running: Yes              ------------- > (Should be Yes)

          Seconds_Behind_Master: 0            ---------- > (ideally it should be 0, however this is  subjective.  This parameter indicates the lag in seconds behind master)