MySQL Streaming Replication: Step-by-Step Guide

Introduction

MySQL is a very popular relational database that is typically used as a transaction store. MySQL user base boasts of many industry stalwarts like Facebook, Github, etc. who all use MySQL streaming replication. It is a common choice for enterprises because of its comprehensive SQL layer, flexibility, and its granular authentication layer.

With its configurations at the highest setting, MySQL can even accommodate up to 256 TB of data. It supports table-level encryption as well as binary log-level encryption. In production scenarios, where MySQL is the primary transactional database, there is often a requirement to replicate the data on a real-time basis. The reason for this could be the need for a simple backup or to run an analysis on the data without degrading the performance of the main database. This post details the steps to set up MySQL streaming replication.

Table of Contents

• Prerequisites
• Setting-up MySQL Streaming Replication
  • Assign the service id for the master
  • Enabling binary logs in the server
  • Creating a user for replication
  • Getting the binary log position to start replication
  • Setting up the server-id for the slave instance
  • Configuring the slave instance
  • Starting the slave threads
• Conclusion

Prerequisites

• A master MySQL instance with root permissions
• A MySQL instance to act as the slave
• A basic understanding of databases

Setting-up MySQL Streaming Replication

MySQL streaming replication works on the basis of binary logs. These are files that contain a log of all the activities that happen in a MySQL instance. Setting up MySQL streaming replication involves configuring the source database to enable binary logs and then configuring the slave to use it as the source of data.

• Assigning the Service-id for the Master Server

The default service id for MySQL server instance is 0. This has to be changed to a value other than 0 to enable replication. For now, let us assign this to 1. Login to MySQL server and execute the below command

SET GLOBAL server_id = 1;

• Enabling Binary Logs in the Server

This step requires a server restart to take effect. MySQL uses a configuration file called my.cnf. Exit the shell and open the configuration file using your favourite editor. In the ‘mysqld’ section, add the below values.

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

Now, restart the server using the below command

service mysqld restart

• Creating a User for MySQL Streaming Replication

The slave instance will use this user to connect to the master instance.

mysql> CREATE USER 'repuser' IDENTIFIED BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repuser';

• Getting the Binary Log Position to Start MySQL Streaming Replication

This step is needed only if your master already had a binary log enabled before this exercise. In such cases, you need the binary log position to configure the replication in the slave instance. This can be found by logging into the shell and executing a command. Before executing the command, lock the master instance using the below command.

FLUSH TABLES WITH READ LOCK;

Now execute the below command to get the position and file name.

mysql > SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 71       | test         | manual,mysql     |
+------------------+----------+--------------+------------------+

The above result shows the file name and position of the binary log. In case this is empty, you should use ‘’ and 4 as the file name and position while configuring the slave instance.

After noting down the values, unlock the table for writes using the below command.

UNLOCK TABLES;

• Setting-up the Server-id for the Slave Instance

As mentioned above, you need to change the default server-id of 0 to set up replication.  The value of the id does not matter and it can be any number between 1 and 2^32 without conflicts. It would depend on your topology design as well. Login to the slave instance and execute the below command.

SET GLOBAL server_id = 2;

• Configuring the Slave Instance with Master Details

In this step, you will set the master connection details and the binary log position details. Use the below command to complete this.

mysql> CHANGE MASTER TO
    ->     MASTER_HOST = 'master_host_name',
    ->     MASTER_USER = 'repuser',
    ->     MASTER_PASSWORD = 'password',
    ->     MASTER_LOG_FILE = 'rmysql-bin.000004 ',
    ->     MASTER_LOG_POS = 71;

If the master was not already enabled, you can use ‘’ and 4 as values for log name and position respectively.

• Starting the Slave Threads

Start the slave threads using the below command.

mysql> START SLAVE;

That concludes the steps to set up MySQL streaming replication. Once the slave threads start running, all changes in the master instance will be replicated asynchronously to slave instances.

Conclusion

In this post, you learned how to set up MySQL streaming replication. This post assumed a scenario where both master and slaves were being set up from scratch. In reality, you may face the below challenges while executing this.

• In some cases, the master may already have data. In such cases, you should use mysqldump command to create a dump file and load it to the slave instance before starting the slave threads. 
• The above approach requires you to manually configure many details and you will need intricate knowledge of how MySQL works. Configuring such replication will require you to have MySQL administration skills as well as data engineering skills. 
• In most situations, data has to be transformed before loading to the destination database. Such transformations cannot be handled by this approach.

Visit our Website to Explore Hevo

If the above limitations are a deal-breaker for you, a completely managed ETL tool like Hevo can be a good choice to execute MySQL streaming replication. Hevo is a No-code Data Pipeline that provides a simple user interface that spares you from the configuration nightmares. It supports complex transformations and can help you set up an ETL pipeline with the lowest time to production possible.

Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You may also have a look at the amazing price, which will assist you in selecting the best plan for your requirements.

What are your thoughts on MySQL streaming replication? Let us know in the comments!