In high-traffic Web applications, a master-slave database architecture is used to optimize performance by separating read and write operations and to prevent loss of data in case of a server failure. Let’s see how to set it up in a MySQL database management system.

How Does it Work?

The main idea is to have the same content stored in multiple databases, where one database will be used for write operations only (master), and the other(s) will be used exclusively for read operations (slave). In other words, when a web application wants to insert content to the database, it will do that by writing into the master database, but when it needs to fetch the information, it will do that by reading from one of the slave databases.

This approach has many advantages, such as improved performance and availability. On the other hand, it also takes up more space, is more complicated to set up and can lead to data conflicts which will need to be addressed. We will talk more about this further below.

Requirements

I assume that you have already installed LAMP or LEMP stack. Also, you will need multiple IP addresses (one for each database), SSH access and the root account. In order to keep things as simple as possible, the master-slave architecture that will be described in this tutorial will contain two databases ? one master and one slave database.

?

Master Database Configuration

First, we will need to edit the MySQL configuration:

# sudo vi /etc/mysql/my.cnf

Note that, depending on Linux distribution, the configuration file location might be different. Now, find the following line:

bind-address = 127.0.0.1

And change it to match you master database’s IP address. So, if your master database IP address is 11.22.33.445, this line would look like:

bind-address = 11.22.33.445

Next, we need to configure the server ID. It can be any number, but it must be unique:

server-id = 1

Since the slave database would copy all of the changes that are registered in the log, we will uncomment this line:

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

The last change to the MySQL configuration file will determine which databases are going to be replicated on the slave server:

binlog_do_db = mydatabase01

To replicate multiple databases, do the following:

binlog_do_db = mydatabase01binlog_do_db = mydatabase02

You can also go the other way around and exclude certain databases from replication:

binlog-ignore-db = "mysql"

Now, save the configuration file and restart MySQL service for the changes to take effect:

# sudo service mysql restart

We need to grant replication privileges to the slave database. Let’s open the MySQL shell and run the following commands:

# mysql -u root ?pmysql > GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';mysql > FLUSH PRIVILEGES; 

At this point, we need to ensure that the data in the slave database is the same as in the master database. We will lock the tables, so no new data can be written:

# mysql -u root -pmysql> FLUSH TABLES WITH READ LOCK;mysql> SHOW MASTER STATUS; 

This command will show an output, where you need to write down the number shown in “position” column, as we will need it later. After that, we will perform a database dump:

# mysqldump -u root -p --databases [database-1] [database-2] ...  > /home/myuser/mydatabase01.sql

Finally, we will unlock the database:

mysql> UNLOCK TABLES; 

Slave Database Configuration

Same as above, we will edit the MySQL configuration file first:

# sudo vi /etc/mysql/my.cnf

Note that this time we are editing it on the slave server. The configuration should look like this:

bind-address = 0.0.0.0server-id = 2master-host =  [IP-of-master-db]master-user = [replication-username]master-password = [replication-password]master-connect-retry = 60relay-log  = /var/log/mysql/mysql-relay-bin.loglog_bin = /var/log/mysql/mysql-bin.logbinlog_do_db = mydatabase01

The dump from above has to be imported into the slave database. We will copy it to the slave server first:

scp /home/myuser/mydatabase01.sql [IP-of-db02]:/root/myuser/

And then we will import it and restart the MySQL service after the dump has been completed:

# mysql -u root -p < /root/myuser/mydatabase01.sql# service mysqld restart

Let's finalize the slave database configuration:

mysql> SLAVE STOP;mysql> CHANGE MASTER TO MASTER_HOST='[IP-of-master-db]',MASTER_USER='[replication-username]',MASTER_PASSWORD='[replication-password]',MASTER_LOG_FILE='[file-listed-on-master-status]',MASTER_LOG_POS=[log-position-listed-on-master-status]; 

Note that the number in the last row of this command is the same number that you have written down after "SHOW MASTER STATUS" command. Now, the slave server is properly configured and we can start it:

mysql> START SLAVE; 

Let's check its status:

mysql> SHOW SLAVE STATUSG

If the Slave_IO_State shows "Waiting for master to send event", everything works fine. If it shows "Connecting to Master", check the MySQL log file or try the following command:

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START; 

At this point, everything is set up. The data inserted into the master database should be immediately visible in the slave database.

Next Steps

This is an example of the simplest master-slave database architecture. More complex systems could contain a larger number of databases. They could also have the load balancing software in order to spread the load evenly across the servers.