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
Hevo Data, a No-code Data Pipeline, can help you achieve the same result with absolute ease. It is a completely automated solution and it requires minimal supervision.
Get Started with Hevo for free
Check out Hevo’s awesome features:
- Completely Automated: The Hevo platform can be set up in just a few minutes and requires minimal maintenance.
- Real-time Data Transfer: Hevo provides real-time data migration, so you can have analysis-ready data always.
- 100% Complete & Accurate Data Transfer: Hevo’s robust infrastructure ensures reliable data transfer with zero data loss.
- Scalable Infrastructure: Hevo has in-built integrations for 100+ sources that can help you scale your data infrastructure as required.
- 24/7 Live Support: The Hevo team is available round the clock to extend exceptional support to you through chat, email, and support call.
- Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
- Live Monitoring: Hevo allows you to monitor the data flow so you can check where your data is at a particular point in time.
Sign up here for a 14-day Free Trial!
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!