How to setup MySQL Master-Slave Replication on magento

Wednesday, April 11, 2012
This is an article to show you ,how you can set MySql Master-slave replication in magento for scalability ,handle failover and performance.
You will have to use Slave database for read (select queries) Operations and Master database for write( insert and update queries).
Make changes in the following config file of magento:
app/etc/local.xml

<default_setup>
<connection>
<host><![CDATA[Master-host]]></host>
<username><![CDATA[user]]></username>
<password><![CDATA[pass]]></password>
<dbname><![CDATA[magentodb]]></dbname>
<active>1</active>
</connection>
</default_setup>
<default_read>
<connection>
<use/>
<host><![CDATA[Slave-host]]></host>
<username><![CDATA[user]]></username>
<password><![CDATA[pass]]></password>
<dbname><![CDATA[magento]]></dbname>
<type>pdo_mysql</type>
<model>mysql4</model>
<initStatements>SET NAMES utf8</initStatements>
<active>1</active>
</connection>
</default_read>

Prior to this setup , you must have to configure your mysql master and slave server.
Configuration for master server
edit /etc/my.cnf

add below content in the file:
[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
binlog_do_db = magento_demo
binlog_ignore_db = mysql

then restart your mysql server.
Configuration for slave server
edit /etc/my.cnf

add below content in the file:
[mysqld]
server-id=2
log-bin=mysql-bin
master-host=192.168.1.2
master-user=username
master-password=111111
master-port=3306
replicate-do-db=magento_demo
replicate-ignore-db=mysql
master-connect-retry=60

then restart your mysql server.

0 comments:

Post a Comment