phpDevelopment | Blog - Bringing you the best of the webdevelopement ecosphere

One of the most used RDBMS (relational database management system) is MySQL. MySQL stands for “My Structured Query Language“. It currently has more than 6 million installation world-wide and can be installed on almost any OS varying from UNIX systems to Windows. Thus, chances are you are already working with it.

Now, there comes a time when your fresh website explodes in terms of traffic and your server cannot face it. In such case, you should consider two things:

• Revise all your queries and try to optimize them
• Use Database Replication

We won’t talk about query optimizer since there are a lot of tutorials and sites that describe how exactly to do it and it should be your primary goal in the process of making your website run smoother.

What is Database Replication

Database replication is the process of sharing information between redundant sources, to improve the overall performance and reliability.

This means that you can have your database on one server, manage all Write dependent operation on that server and use a separate server for all the Reading operations. A practical example would be a search engine.

A search engine could consist of a crawler (the back-office) and the actual UI which is displayed to the user. The user would read information from the database while the crawler would write that information actively. This way, you could have the crawler set on a server (the master server) and the actual application on another server (the slave). Every time the master server writes data to the database, it will be almost instantaneously available to the slave server, thus the user will see no difference but your overall performance will be boosted.

Database replication can be of two types (master-master or master-slave). The simplest and the most spread scenario is to the master-slave type. Let’s see how we can create such a system on our own servers…

Prerequisites

In the next few chapters i will assume the following:

• You are in a Linux environment (i will be talking about CentOS but with a bit of research regarding each file path, you can set it up on any Linux distribution)
• You have access to SSH as root (or a user with root privileges)
• You have access to the MySQL root user
• You have an empty database on both of your master and slave server.

Configuring the Master Server

• Login to your Master server via SSH as root.
• Go to your /etc folder ( run the following command: cd /etc )
• Make a backup of your my.cnf file found here ( run the following command: cp /etc/my.cnf /etc/my.cnf.old )
• Edit the file /etc/my.cnf with nano ( run the following command: nano /etc/my.cnf )
• Add the following lines somewhere in the section [mysqld]

  log-bin=mysql-bin
  server-id=1
  

• Restart MySQL (run the following command: /etc/init.d/mysql restart )

Adding a user with replication privileges to the master server

• Login as root to your MySQL account ( run the following command: mysql -u root -p )
• Enter your password
• Grant replication privileges to a user for a database with the following command: (replace some_password with a real password and some_user with a random username you prefer)

  GRANT REPLICATION SLAVE ON *.* TO
  'some_user'@'%' IDENTIFIED BY 'some_password';
  FLUSH PRIVILEGES;
  

• Get the MySQL log file ID (it is mandatory that you fetch this so that you will have only one log file for the entire operation)

  SHOW MASTER STATUS;
  

• If you did everything ok you should now have a table in front of you with at least 2 columns: File and Position. Write the values of these columns somewhere.
• Unlock the MySQL tables with the following command

  UNLOCK TABLES;
  

• Exit the MySQL interface by running

  quit
  

Configuring the Slave Server

• Login to your Slave server via SSH as root.
• Go to your /etc folder (run the following command: cd /etc )
• Make a backup of your my.cnf file found here ( run the following command: cp /etc/my.cnf /etc/my.cnf.old )
• Edit the file /etc/my.cnf with nano ( run the following command: nano /etc/my.cnf )
• Add the following lines somewhere in the section [mysqld]

  server-id=2
  

• Restart MySQL (run the following command: /etc/init.d/mysql restart )

Setting up the link between the Slave and the Master

• Login as root to your MySQL account ( run the following command: mysql -u root -p )
• Enter your password
• Run the following LONG command:

  mysql> CHANGE MASTER TO
       > MASTER_HOST='master_host_name'
       > MASTER_USER='replication_user_name'
       > MASTER_PASSWORD='replication_password'
       > MASTER_LOG_FILE='recorded_log_file_name'
       > MASTER_LOG_POS=recorded_log_position
  

• Quit and restart mysql.

Conclusion

If everything was setup ok, you should now be able to go to your Master server, insert data into your replication database, and then, when you go to your Slave server, you should be able to see each row inserted in real time.

Would you like to have an article like this about master-master replication?