Master Slave on Mysql for Muthoot CRM

Requirement

CRM Reports to be shifted to a Read only - Replicated database for enhancing the Mysql Performance and avoiding performance bottlenecks on live Server .

Details

Master Server - CRM Pilot Server ( 182.71.4.23 / 192.168.1.152 )

Slave Server - CRM Pilot Server ( 182.71.4.20 / 192.168.1.131 )

Data bases to be Replicated are

crmmuth_db_pilotlive        
crmmuth_erp_pilotlive       
crmmuth_finance_pilotlive   
crmmuth_muthoothr_pilotlive

Configurations

Added in /etc/my.cnf ( Master )

server-id = 1
log-bin = mysql-bin
binlog-do-db = crmmuth_db_pilotlive
binlog-do-db = crmmuth_erp_pilotlive
binlog-do-db = crmmuth_finance_pilotlive
binlog-do-db = crmmuth_muthoothr_pilotlive

on Master create replication user

mysql>GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'tetra321'; 
mysql>FLUSH PRIVILEGES;
mysql>FLUSH TABLES WITH READ LOCK;
mysql>UNLOCK TABLES;

Restart Mysql on master

On Slave

Added in /etc/my.cnf ( Slave )

server-id = 2
replicate-do-db = crmmuth_db_pilotlive
replicate-do-db = crmmuth_erp_pilotlive
replicate-do-db = crmmuth_finance_pilotlive
replicate-do-db = crmmuth_muthoothr_pilotlive

Restart Mysql on Slave

At master note the details of file and posiion

mysql> show master status; +------------------+----------+--------------------------------------------------------------------------------------------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------------------------------------------------------------------------------------------+------------------+ | mysql-bin.000001 | 106 | crmmuth_db_pilotlive,crmmuth_erp_pilotlive,crmmuth_finance_pilotlive,crmmuth_muthoothr_pilotlive | | +------------------+----------+--------------------------------------------------------------------------------------------------+------------------+

On master take the mysql dump of the mentioned databases

mysqldump -u root -p --databases crmmuth_db_pilotlive crmmuth_erp_pilotlive crmmuth_finance_pilotlive crmmuth_muthoothr_pilotlive > dump_4_db_for_slave.sql

On Slave , import the databases;

mysql -p < dump_4_db_for_slave.sql

Once Done

mysql>SLAVE STOP;
mysql>CHANGE MASTER TO MASTER_HOST='192.168.1.152', MASTER_USER='repl', MASTER_PASSWORD='tetra321', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=106;
mysql>START SLAVE;
mysql>SHOW SLAVE STATUS\G; 

Error if any

Incase you get error on SHOW SLAVE STATUS\G

mysql>stop slave ; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START;