Read complete story

Webkul has been rewarded with Top Selling Extension 2017 award at Magento Imagine 2018

Read more

Magento with MySQL Master Slave Architecture

In MySQL replication we can configure the database in such a way that single master database can replicate the data to multiple slaves, those will work as multiple read only databases.

Now the question arises why you need such type of architecture ?

If you have very less traffic on your magento store as well as there is no issue of system performance and backup then definitely you don’t but if you have a traffic of around 20000 users a day and want backup of your database to overcome the disastrous situations then it sounds like a medicine for your server. With this architecture we divide the read query traffic and write query traffic to different servers and increase the reliability and performance as compared to simple database architecture. As we know in simple database architecture if due to some reason our database goes down then the whole application hit the dead end which is definitely not a case in master slave architecture.

Now the next question which is popping up in your mind “what exactly master slave architecture is” ?

In Master Slave architecture we configure one database as a master and another database as a slave so that master database replicate its data to the slave which become the master when the primary master goes down and protect your application from hitting the dead end.

Now the question arises how it help in improving the reliability and performance ?

When an application communicate with the database, at that time database generates large amount of read query traffic as compared to write query thats why our database handle both type of traffic simultaneously which reduces the performance of the database as soon as the traffic on site increases. To overcome this problem master slave architecture divides the traffic over two databases in which it uses master for read or write queries and slave for only read queries. We can manage the traffic accordingly and improve the reliability and performance from application end.

 

Real Facts and Figures

Currently all the facts and figures are recorded while using siege with around 20 concurrent users per second. Performance will be recorded more clearly when we test it on large scale with around 10000 concurrent users per second.

image1
MySQL Queries when Magento and MySQL Database are on the same server

 


MySQL queries execution on master database

 


MySQL queries execution on slave database

 

MySQL Master Slave Implementation on Magento

1. Prepare three different servers using Ubuntu with Vagrant or any other virtualization method and install mysql server on two of the servers as well as Magento on one of the virtualized server.

 

2. Let there are three servers with given below IP addresses.

Magento -: 192.168.1.241

Master -: 192.168.1.242

SLAVE -: 192.168.1.243

 

3. Edit the MySql configuration file of Master Server which is located at

/etc/mysql/my.cnf

Edit the the line

bind-address = 127.0.0.1

to

bind-address = 192.168.1.242

uncomment the line “server-id = 1” and give a unique ID to the server

for example in our case we are giving unique ID which is 1.

uncomment the log_bin = /var/log/mysql/mysql-bin.log

now save the changes in the file and restart the mysql server by using command

sudo service mysql restart

 

4. Edit the MySql configuration file of slave server which is located at

/etc/mysql/my.cnf

Again assign the slave server ip by editing the line

bind-address = 127.0.0.1

to

bind-address = 192.168.1.243

uncomment the line “server-id = 1” and give a unique ID to the server for example in our case we have assigned “server-id = 2”

save the changes in the file and restart the mysql server using command

sudo service mysql restart

 

5. Again login to master mysql server using ssh and login the mysql command line with your credentials.

mysql -u root -p

 

6. Now we need a user for replication on master server so we create a user named as “replicate” by following command.

create user 'replicate'@'%' identified by 'password';

By this command we create a user which can login from any IP address with the given password.

 

7. Now we grant the replication privileges to slave on all the databases with the ‘replicate’ user

grant replication slave on *.* to 'replicate'@'%';

 

8. Now create a sample database for testing purpose.

create database languages;

create languages.oops (name varchar(20));

insert into languages.oops values ('java');

 

9. Now take the mysql dump of the whole database schema using command

mysqldump -uroot –all-databases –master-data > masterdump.sql

Now check the master_log_file and master_log_position by using command

grep CHANGE *sql | head -1

 

10. After that transfer the mysql dump file to the slave server using scp or any other utility.

 

11. Now login to Slave mysql command shell and tell the slave regarding master by following commands.

Change master to 
master_host = '192.168.1.242',
master_user = 'replicate',
master_password='password';
exit

 

12. After that restore the mysqldump on the slave mysql server

mysql -uroot -p < masterdump.sql

 

13. Now we have to login the mysql shell of slave server and start the slave by following command

start slave;

 

14. After that we can check the status of the slave by the command

show slave status;

 

15. Now you can add any element in the master mysql database and it automatically replicate on the slave server.

 

16. Now switch to the server on which you have installed the magento.

Edit the /app/etc/local.xml file of your magento

<default_setup>
    <connection>
        <host><![CDATA[192.168.1.242]]></host>
        <username><![CDATA[username]]></username>
        <password><![CDATA[password]]></password>
        <dbname><![CDATA[magento]]></dbname>
        <active>1</active>
    </connection>
</default_setup>
<default_read>
    <connection>
        <use/>
        <host><![CDATA[192.168.1.243]]></host>
        <username><![CDATA[username]]></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>

 

17. Now you can test the reliability and performance using munin or tcptrack utility of ubuntu.

 

Conclusion

MySQL Master Slave replication has pros as well as cons, it divides the traffic of read and write query but still slave performs the write queries locally because it executes all the updates perform on the master database, still this architecture helps you to increase the functionality as well as reliability by providing you a backup of your database in the form of slave. So, we can say that if you want backup as well as high performance you can implement this architecture on your magento store.

Comment

Add Your Comment

Be the first to comment.

Need Any Help?

Got Stuck with something serious or you wish to hire us for a while. We are here to hear from you, feel free to reach us and we'll get back to you as soon as possible.

Contact Us
css.php
Hire Us!
Brief us about your requirements and we'll get back to you.
Woo! Hooy!
We have just recieved your project brief and our expert will contact you shortly.
Send Again
Close