Master Slave on Mysql for Muthoot CRM - Revision history

Biswajit: /* Configurations */

2014-04-14T15:32:20Z

Configurations

← Older revision		Revision as of 15:32, 14 April 2014
Line 62:		Line 62:

	mysql> show master status;		mysql> show master status;
	+------------------+----------+--------------------------------------------------------------------------------------------------+------------------+
	\| File \| Position \| Binlog_Do_DB \| Binlog_Ignore_DB \|		+------------------+----------+--------------------------------------------------------------------------------------------------+------------------+
	+------------------+----------+--------------------------------------------------------------------------------------------------+------------------+		\| File \| Position \| Binlog_Do_DB \| Binlog_Ignore_DB \|
	\| mysql-bin.000001 \| 106 \| crmmuth_db_pilotlive,crmmuth_erp_pilotlive,crmmuth_finance_pilotlive,crmmuth_muthoothr_pilotlive \| \|		+------------------+----------+--------------------------------------------------------------------------------------------------+------------------+
	+------------------+----------+--------------------------------------------------------------------------------------------------+------------------+		\| 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		On master take the mysql dump of the mentioned databases
Line 82:		Line 83:
	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>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>START SLAVE;
	mysql>SHOW SLAVE STATUS\G;		mysql>SHOW SLAVE STATUS\G;

	==Error if any ==		==Error if any ==

Sachinverma: /* Details */

2014-04-14T13:36:49Z

Details

← Older revision		Revision as of 13:36, 14 April 2014
Line 13:		Line 13:
	==Details==		==Details==

	Master Server - CRM Pilot Server ( 182.71.4.23 / 192.168.0.152 )		Master Server - CRM Pilot Server ( 182.71.4.23 / 192.168.1.152 )

	Slave Server - CRM Pilot Server ( 182.71.4.20 / 192.168.0.131 )		Slave Server - CRM Pilot Server ( 182.71.4.20 / 192.168.1.131 )


Line 23:		Line 23:
	crmmuth_erp_pilotlive		crmmuth_erp_pilotlive
	crmmuth_finance_pilotlive		crmmuth_finance_pilotlive
	crmmuth_muthoothr_pilotlive		crmmuth_muthoothr_pilotlive

	== Configurations ==		== Configurations ==

Biswajit at 08:58, 22 March 2014

2014-03-22T08:58:34Z

← Older revision		Revision as of 08:58, 22 March 2014
Line 13:		Line 13:
	==Details==		==Details==

	Master Server - CRM Pilot Server ( 182.71.4.23 / 192.168.0.		Master Server - CRM Pilot Server ( 182.71.4.23 / 192.168.0.152 )

	Slave Server - CRM Pilot Server ( 182.71.4.20 / 192.168.0.		Slave Server - CRM Pilot Server ( 182.71.4.20 / 192.168.0.131 )

Biswajit at 08:56, 22 March 2014

2014-03-22T08:56:35Z

← Older revision		Revision as of 08:56, 22 March 2014
Line 1:		Line 1:
	[[Category:Tetra Clients]]		[[Category:Tetra Clients]]
	[[category: Muthoot ~~Group~~]]		[[category: Muthoot group]]

Biswajit: Created page with "Category:Tetra Clients category: Muthoot Group ==Master Slave of Mysql for Muthoot CRM Application== ==Requirement == CRM Reports to be shifted to a Read only -..."

2014-03-22T08:54:30Z

Created page with "Category:Tetra Clients category: Muthoot Group ==Master Slave of Mysql for Muthoot CRM Application== ==Requirement == CRM Reports to be shifted to a Read only -..."

New page

[[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.0.

Slave Server - CRM Pilot Server ( 182.71.4.20 / 192.168.0.

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;