Master Slave on Mysql for Muthoot CRM

2014-04-14T13:36:49Z

Sachinverma: /* Details */

[[Category:Tetra Clients]]
[[category: Muthoot group]]

==Master Slave of Mysql for Muthoot CRM Application==

==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;

TetraWiki - User contributions [en]

Master Slave on Mysql for Muthoot CRM