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