MySQL Master Slave Replication

MySQL Master Slave Replication

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.


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 =

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

bind-address =

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:


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 = = 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:


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:


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.

Share the Post:
Heading photo, Metadata.

What is Metadata?

What is metadata? Well, It’s an odd concept to wrap your head around. Metadata is essentially the secondary layer of data that tracks details about the “regular” data. The regular

XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several

data fivetran pricing

Fivetran Pricing Explained

One of the biggest trends of the 21st century is the massive surge in analytics. Analytics is the process of utilizing data to drive future decision-making. With so much of

kubernetes logging

Kubernetes Logging: What You Need to Know

Kubernetes from Google is one of the most popular open-source and free container management solutions made to make managing and deploying applications easier. It has a solid architecture that makes

ransomware cyber attack

Why Is Ransomware Such a Major Threat?

One of the most significant cyber threats faced by modern organizations is a ransomware attack. Ransomware attacks have grown in both sophistication and frequency over the past few years, forcing