Magento MySQL Master/Slave

Submitted by Eric on Tue, 03/27/2018 - 23:46
Mysql replication

This is a really simple tutorial on how you can set up Magento to work with multiple MySQL servers in case you have Master/Slave configuration.

First you will need a MySQL master/slave configuration, if you don't have one you can always use this tutorial to configure: https://dev.mysql.com/doc/refman/5.7/en/replication-howto.html

If you are using a Database service like AWS RDS this is much easier, just use the replica DNS in the configuration below.

Now on Magento, open the file app/etc/local.xml and you will find this part on the file:

            <default_setup>
                <connection>
                    <host><![CDATA[10.x.x.x]]></host>
                    <username><![CDATA[magento]]></username>
                    <password><![CDATA[PassWord]]></password>
                    <dbname><![CDATA[magento]]></dbname>
                    <initStatements><![CDATA[SET NAMES utf8]]></initStatements>
                    <model><![CDATA[mysql4]]></model>
                    <type><![CDATA[pdo_mysql]]></type>
                    <pdoType><![CDATA[]]></pdoType>
                    <active>1</active>
                </connection>
            </default_setup>

This is the main MySQL connection configuration. On this part you will change the information to match your Master MySQL configuration.

To set up the Slave configuration, let's add this right after the last part:

			<default_read>
				<connection>
					<use/>
					<host><![CDATA[10.x.x.x]]></host>
					<username><![CDATA[magento]]></username>
					<password><![CDATA[PassWord]]></password>
					<dbname><![CDATA[magento]]></dbname>
					<type>pdo_mysql</type>
					<model>mysql4</model>
					<initStatements>SET NAMES utf8</initStatements>
					<active>1</active>
				</connection>
			</default_read>

You can have as many slaves you want. 

I hope you find this tutorial helpful and if you have any questions let us know.


FacebookTwitterGoogle+Share