MySQL Master-Slave Replication: Setting Up

Share this article

Very recently, at, having reached a database of 25Gb after just six months of operation, we decided to start using replication for our backend persistent storage.

Why would you want to do that

Replication of MySQL can be a solution to various problems that you might want to solve:
  • Scale-out solutions
  • Data security
  • Analytics
  • Long-distance data distribution
  • Backup taken from slaves, rather than from master
MySQL documentation on replication, that can be found here, is quite explanatory and gives more detail about these uses.

Where have we been?

Before implementing replication, we had a configuration such as the following: what we had We had a debian squeeze machine (name: Plato) with MySQL 5.1. installed and serving all of Fraudpointer traffic. When we decided to implement replication it was already 25Gb. A lot of transactions took place per minute, and we were facing two major problems. 1)    We wanted to take daily backups and backup put a lot of load on the machine. Though the backup utility that we used (Percona xtrabackup) didn’t lock the database, the machine was getting quite unresponsive during backup time. 2)   We wanted to run heavy queries and get reports, very frequently. Just this was a good reason to start discussing replication.

Where are we after replication?

After MySQL replication, we are now in the following picture: what we want We now have two machines with MySQL installed. The old one, Plato, is now acting as the Master and the new one, Ares, is now acting as the Slave of the replication scheme. In our setup, the advantages of this configuration are as follows: 1)    We can get the daily backups from the Slave. No load on the production Master machine for this task. 2)   We can get our reports from the Slave node. 3)   If the Slave node is down, this is not a problem at all. Replication is performed asynchronously and when the Slave node is up and live after a downtime, it continues replication from the point it has been paused. 4)   Records that are created or updated at Master machine, are almost instantly “transferred” to Slave machine (even if asynchronously).

How did we do that?

Here is the process that we followed to carry out the replication:


  1. Both Master and Slave are debian squeeze machines.
  2. Master has static IP:
  3. Slave has static IP:
  4. Master and Slave are on the same LAN.
  5. Master has MySQL installed and you have the root password to connect to it from ‘localhost’. MySQL does not allow remote connections by default, and that was our case too.
  6. Slave does not have MySQL installed yet.
  7. Master debian allows incoming connections on 3306 port, the standard MySQL listening port.



In the first phase of the work, the overview of what we did is as follows: 1)    On Master
  1. Allow remote connections
  2. Set bind-address
  3. Enable binary logging
  4. Establish a unique server ID
  5. Restart server
  6. Create a user for replication
2)   On Slave
  1. Install MySQL
  2. Stop MySQL
  3. Set bind-address
  4. Enable binary logging
  5. Set unique server ID

Detailed Actions

On Master

1)    Allow remote connections Since you are about to change the bind address on your MySQL server, you are essentially allowing access to your MySQL server from “remote” machines. If you do not do that, after the change of the bind address and restarting of your MySQL server, users from remote or local machines will not be able to connect to your server. All the remote and local machine IPs need to be granted access for the users that access MySQL from these machines. The following mysql commands (which can be issued using mysql shell)
mysql> grant all on *.* to bar@’’ identified by ‘bar_password’;

mysql> flush privileges;
allow the user ‘bar’ to connect to this MySQL server and to all databases from the machine with IP ’’. This is necessary even if the user connects from the machine that MySQL server is installed. Hence, connect to your MySQL server using mysql and set these commands accordingly before proceeding to the following.  Don’t forget that this is true for the ‘root’ user too, even if he connects from the local machine. 2)   Set bind-address to the IP of the machine. This is done inside the my.cnf file. bind-address is a configuration parameter of the section [mysqld]. Set it to the IP of your Master machine. In our case, it was Note that if you have enabled skip-networking you need to disable it. 3)   Set binary log file pattern The parameter is called log_bin and it should exist in the [mysqld] section. Note that the full path to the binary log file should be given. Example: /var/log/mysql/mysql-bin.log. Important gotcha: make sure that the folder/disk has enough space to write the binary logs there. 4)   Set server id This is the Master replication server ID. It has to be unique among all the ids used in the whole replication setup. For example, set this to “100” and make sure that you set “101” on your Slave (see later). The parameter is server-id and should be set in the section [mysqld]. 5)   innodb_flush_log_at_trx_commit and sync_binlog These should be set to “1”, both of them. According to MySQL documentation for the greatest possible durability and consistency in a replication setup using InnoDB with transactions … you have to do this. More on this you can find here
. These parameters are set in section [mysqld]. 6)   RESTART MySQL Server Having done all the necessary changes on the configuration file of MySQL Server and having granted access to all existing users to be able to connect using their IP, you can restart your MySQL Server. 7)   Create user for replication You need to create a user that will have the right to work as the replication/slave client. The following commands:
mysql> create user ‘repl’@’’ identified by ‘slavepassword’;

mysql> grant all on *.* to ‘repl’@’’;

mysql> flush privileges;
are creating the user ‘repl’ and give him the right to do replication from the slave machine with IP using the specified password.

On Slave

1)    Install MySQL Server On Slave machine you need to install MySQL Server. 2)   Stop MySQL server You do not need to have it running, for the time being. Proceed with changing MySQL configuration parameters to support Slave replication. 3)   bind-address Set bind-address to the value of the IP of your Slave machine. In our case was The parameter should be set in the [mysqld] section. 4)   log_bin Set the full path to the binary log file. For example: /var/mysql/log/mysql-bin.log. The parameter is set in the [mysqld] section of the my.cnf file. Important gotcha:: make sure that your binary log folder has lots of space. You will need it. 5)   server-id You need to give the server ID for the Slave node for this replication setup. Put for example “101”. It has to be different from the server IDs of the Master and other Slave nodes in your configuration. Do not start Slave MySQL server. You still do not need to do that. You are done for now. Phase 1 is finished. In Phase 2, we will start replication.

Frequently Asked Questions on MySQL Master-Slave Replication

What is the significance of the server-id in MySQL replication?

The server-id is a unique identifier for each server in a MySQL replication setup. It is crucial because it allows the master server to differentiate between multiple slave servers. If two servers have the same server-id, it can lead to data inconsistency and replication errors. Therefore, it’s essential to ensure that each server in your replication setup has a unique server-id.

How can I check if MySQL replication is working correctly?

You can check the status of MySQL replication by running the ‘SHOW SLAVE STATUS’ command on the slave server. This command provides detailed information about the replication status, including whether it’s running, the last error message, and the master log file position. If the ‘Slave_IO_Running’ and ‘Slave_SQL_Running’ values are both ‘Yes’, it means the replication is working correctly.

What is the role of the binary log in MySQL replication?

The binary log plays a crucial role in MySQL replication. It records all changes to the database, including data modifications (insert, update, delete) and schema changes (create, alter, drop). The master server uses the binary log to transmit these changes to the slave servers, ensuring that they have the same data as the master.

How can I resolve replication errors in MySQL?

Resolving replication errors in MySQL depends on the specific error message. However, common solutions include checking the network connectivity between the master and slave servers, ensuring that the master and slave servers have unique server-ids, and verifying that the binary log file and position specified in the ‘CHANGE MASTER TO’ command are correct. If the error persists, you may need to consult the MySQL documentation or seek help from a MySQL expert.

Can I use MySQL replication for load balancing?

Yes, MySQL replication can be used for load balancing. By distributing read queries across multiple slave servers, you can reduce the load on the master server and improve the overall performance of your database system. However, keep in mind that write queries must still be executed on the master server.

What is the difference between synchronous and asynchronous replication in MySQL?

In synchronous replication, the master server waits for confirmation from the slave server before committing a transaction. This ensures data consistency but can impact performance. On the other hand, in asynchronous replication, the master server doesn’t wait for confirmation from the slave server, leading to better performance but a risk of data inconsistency if the master server fails before the slave server has replicated the changes.

How can I set up multiple slave servers in MySQL replication?

To set up multiple slave servers in MySQL replication, you need to configure each slave server with a unique server-id and specify the master server’s details using the ‘CHANGE MASTER TO’ command. Then, start the replication process on each slave server using the ‘START SLAVE’ command. Remember to ensure that the binary log file and position are correct on each slave server.

Can I pause and resume MySQL replication?

Yes, you can pause and resume MySQL replication using the ‘STOP SLAVE’ and ‘START SLAVE’ commands, respectively. This can be useful when you need to perform maintenance tasks on the slave server or troubleshoot replication issues.

What is the impact of MySQL replication on database performance?

MySQL replication can have both positive and negative impacts on database performance. On the positive side, it can improve read performance by distributing read queries across multiple slave servers. However, it can also increase the load on the master server due to the overhead of managing the binary log and transmitting changes to the slave servers.

How can I ensure data consistency in MySQL replication?

Ensuring data consistency in MySQL replication involves several strategies. First, make sure that all write queries are executed on the master server. Second, regularly check the replication status using the ‘SHOW SLAVE STATUS’ command. If there are any replication errors, resolve them promptly to prevent data inconsistency. Finally, consider using tools like pt-table-checksum and pt-table-sync from the Percona Toolkit to verify and correct data consistency.

Panayotis MatsinopoulosPanayotis Matsinopoulos
View Author

Currently, spending most of the day as CTO for Book&Table. Otherwise, full stack engineer for Fraudpointer and Wyngle. Also, blogging here and teaching computer programming.

Share this article
Read Next
Get the freshest news and resources for developers, designers and digital creators in your inbox each week