How To Set Up Replication in MySQL: 2 Easy Methods

MySQL is a widely used Database by big organizations to run their business activities. Companies Replicate MySQL Database to tackle the challenge of maintaining high data availability. This article aims at providing you with a comprehensive step-by-step guide to help you replicate the MySQL database to another server.

Upon a complete walkthrough of the content, you will have in-depth knowledge of MySQL, how it handles replication and you will be able to Replicate MySQL Database to backup/transfer your data with ease!

Methods to Replicate MySQL Database

You can easily Replicate your MySQL Database by implementing the following methods.

Method 1: Using Hevo Data to Replicate MySQL Database

Hevo Data, a No-code Data Pipeline, helps you replicate data from MySQL to Data Warehouses, Business Intelligence Tools, or any other destination of your choice in a completely hassle-free & automated manner. Hevo supports data ingestion for replication from MySQL servers via Binary logs (BinLog).

A binary log is a collection of log files that records information about data modifications and data object modifications made on a MySQL server instance. Hevo utilizes the Binary logs to perform your MySQL Data Replication.

To learn more, check out Hevo’s documentation for MySQL replication.

Check out what makes Hevo amazing:

• Auto Schema Mapping: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data from MySQL and replicates it to the destination schema.
• Transformations: Hevo provides preload transformations through Python code. It also allows you to run transformation code for each event in the Data Pipelines you set up. You need to edit the event object’s properties received in the transform method as a parameter to carry out the transformation. Hevo also offers drag and drop transformations like Date and Control Functions, JSON, and Event Manipulation to name a few. These can be configured and tested before putting them to use for aggregation.
• Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.

With continuous real-time data movement, Hevo allows you to replicate your MySQL data along with your other data sources and seamlessly load it to the destination of your choice with a no-code, easy-to-setup interface. Try our 14-day full-feature access free trial!

Get Started with Hevo for Free

Method 2: Using Master-Slave Approach to Replicate MySQL Database

You can use the master-slave mechanism to Replicate MySQL Database using the following steps:

• Step 1: Adjusting Your Source Server’s Firewall
• Step 2: Configuring the Master Server
• Step 3: Creating a Replication User
• Step 4: Configuring the Slave Server

Step 1: Adjusting Your Source Server’s Firewall

Before getting started, you need to configure a firewall on both your servers with UFW. However, the source’s firewall will block connection attempts from the MySQL replica instances. Hence, you need to allow connections from your replica through the source’s firewall. You can do so by including a UFW Rule on your source server.

Running this particular command allows any connections originating from the replica server’s IP address — represented by replica_server_ip — to MySQL’s default port number, 3306:

sudo ufw allow from replica_server_ip to any port 3306

You need to replace replica_server_ip with your replica server’s actual IP address. Upon successful addition, you’ll see the following output:

Rule added

Step 2: Configuring the Master Server

To Replicate MySQL Database effectively, you first need to configure the master server or the primary database. To do this, log in to the master server and open the configuration file, usually found at the following path:

/etc/mysql/my.cnf

Once you’ve opened the file, modify the bind-address of the master server by changing the value to the actual IP address of the master server:

bind-address = 127.0.0.1 
to 
bind-address = 198.1.12.123

Update the “mysqld” section to notify MySQL about the master server and specify the log-based and other parameters. To do this, open the configuration’s file and update it by adding the following lines code:

Here, new_database is the database that we want to replicate.

Once you’ve made all the necessary changes, you need to restart the MySQL server to bring the changes into effect. You can do this by using the following lines of code:

sudo service mysql restart

Step 3: Creating a Replication User

With the changes now into effect, you need to create a new user with replication privileges, thereby allowing it to perform replication. To do this, open the MySQL shell and execute the following lines of code:

mysql -u root -p
Enter password:

Now, create a slave user for your MySQL database and add a password to help identify it. To do this, you can use the following lines of code:

mysql> CREATE USER ‘slaveuser’@’%’ IDENTIFIED BY ‘PASSWORD’;

With your slave user now set up, provide it with the necessary privileges using the following line of code:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slaveuser'@'%';

To bring the changes into effect, flush the privileges using the following line of code:

mysql> FLUSH PRIVILEGES; 

Check the status of your database and then use the global read lock to lock the tables and prevent any write operations from taking place when the log status capture and backup is occurring. To do this, you can use the following lines of code:

mysql> USE new_database;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;

Once you’ve executed the commands, you will be able to see the following output on your screen:

You can now use the mysqldump command to replicate the entire master database to the slave server. To do this, you can use the following lines of code:

mysqldump -u root -p new_database> new_database.sql
Enter password:

Once you’ve executed the mysqldump command, a new backup file, known as the “new_database.sql”. Translate this file to the slave server to complete the backup process.

With your back up file now ready, you can now unlock the tables using the following lines of code:

This is how you can configure your master server to Replicate MySQL Database.

Step 4: Configuring the Slave Server

With your master server now ready & configured, you now need to configure the slave server to Replicate MySQL Database successfully. To do this, login to the slave server using your credentials such as username and password, and then create a new database to store the data of the master server using the following lines of code:

mysql -u root -p
Enter password:
mysql> CREATE DATABASE new_database;
mysql> quit;

To transfer the data from the master server, import the backup SQL file into the slave server. You can do this using the following lines of code:

mysql -u root -p new_database < /PATH_TO_new_database.sql
Enter password:

Once you’ve imported the SQL file, you now need to configure the slave server by providing the server details, log file path, etc. To do this, open the configurations file and update the following parameters:

server-id = 2
log_bin = /data/mysql/mysql-bin.log
binlog_do_db = new_database

Once you’ve made all the necessary changes, now restart the slave server to bring the changes into effect. To do this, you can use the following lines of code:

sudo service mysql restart

To complete the MySQL replication process, open a new terminal and update the master-slave configurations as follows:

This is how you can configure the slave server and Replicate MySQL Database successfully.

Before we wrap up let’s cover some basics.

What is Replication in MySQL?

Data replication refers to the process of copying data from one server to another to ensure high data availability and accessibility, thereby making the system fault-tolerant. MySQL leverages master-slave mechanism to achieve replication, allowing users to copy data from their MySQL database (master-server) into one or more MySQL databases (slave-servers).

With data replication in place, MySQL distributes the load over multiple databases & helps access and analyse data seamlessly, thereby making the system fault-tolerant.

In the master-slave mechanism of replication, the data replication takes place as a one-way process, and hence it allows users to copy data only from the master server and store it in the slave-servers. Here, the master server is responsible for carrying out the write operations, whereas the slaves handle the read operations.

Types of MySQL Replication

There are several varieties of replication modes, including:

Asynchronous replication

The master and slave databases are loosely connected in this manner. Events are sent to the slave by the master, although the slave might not use them right away. In the case that the master fails before the slave has implemented every event, this might result in possible data discrepancies.

Synchronous replication

Using this strategy, before committing the changes locally, the master waits for the slave to acknowledge receipt and successful application of each event. This guarantees that the slave’s data is always current with the master’s, but the added delay may have an adverse effect on performance.

Semi-Synchronous replication 

a reconciliation of synchronous and asynchronous replication. The owner waits for a minimum of one slave to confirm receipt of an event before committing the change locally.

Formats for replication

There are two primary replication formats that MySQL supports. Let’s examine each of these formats in more detail to learn how they function and what advantages and disadvantages they have.

Statement-based replication

In statement-based replication, modifications to a database are stored as SQL statements, which are subsequently replicated to every replica. Next, in the same order as the master database, the copies carry out the identical statements. Simple, uncomplicated replication scenarios are best served by the user-friendly and effective SBR replication format.

It does, however, have certain restrictions. For instance, it might not be able to effectively handle some kinds of non-deterministic assertions, which could lead to issues with replication.

Row-based replication

Row-based replication stores modifications to a database in the same way that individual data rows are modified. Replication conflicts can be resolved more readily and with greater flexibility using this sort of replication than with SBR since it can manage more complicated data changes. It’s a solid option for demanding replication circumstances because it can handle complicated data structures better.

But because RBR needs greater network bandwidth and storage capacity to send the extra data, it is also more resource-intensive than SBR.

Conclusion

This article teaches you how to Replicate MySQL Database with ease. It provides in-depth knowledge about the concepts behind every step to help you understand and implement them efficiently. While you can use the Master-Slave method to set up MariaDB replication as described in this post, it is quite effort-intensive and requires in-depth technical expertise& this is where Hevo saves the day.

Hevo is the only real-time ELT No-code Data Pipeline platform that cost-effectively automates data pipelines that are flexible to your needs. Hevo caters to 150+ data sources (including 40+ free sources) and can seamlessly perform MySQL Replication in real-time. Hevo’s fault-tolerant architecture ensures a consistent and secure replication of your MariaDB data. It will make your life easier and make data replication hassle-free.

Learn more about Hevo

Want to take Hevo for a spin? Sign up for a 14-day free trial and experience the feature-rich Hevo suite firsthand.

Tell us about your experience of learning how to Replicate the MySQL Database! Share your thoughts in the comments section below!