Replicate MySQL Database: 2 Easy Methods

on Data Integration, Tutorials • January 23rd, 2022 • Write for Hevo

Replicate MySQL Database

One of the biggest challenges that most organizations face today is ensuring high availability and accessibility of data over the complex set of networks they have in place. Having around-the-clock and real-time access to crucial business data can help organizations carry out processes seamlessly and maintain a steady revenue flow. Organizations, thus have a growing need to scale their systems & provide the support for accessing data seamlessly. This article focuses on how to Replicate MySQL Database.

MySQL is a widely used Database by big organizations to run their business activities. Data replication is one such technique that allows users to access data from numerous sources such as servers, sites, etc. in real-time. 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 MySQL Database seamlessly. 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!

Table of Contents

What is MySQL?

Replicate MySQL Database-MySQL Logo.
Image Source: logo.wine

MySQL is one of the most popular, widely-used and robust open-source RDBMS (Relational Database Management System), made available free of cost under the GNU public license, also known as the premium proprietary version. It allows users to leverage SQL (Structured Query Language) based queries to access, add and remove data from their desired database.

Michael Widenius originally developed MySQL at MySQL AB, a Swedish-based company, later acquired by Sun Microsystems and then by Oracle. Many prominent organizations such as Facebook, YouTube, Flickr, etc. use MySQL to manage their data needs.

Key Features of MySQL

  • Secure: MySQL provides users with enterprise-grade security functionalities, allowing only authorized personnel to access the data.
  • Compatible: MySQL houses support for all significant modern platforms and operating systems such as Windows, Linux, Unix, etc. 
  • Scalable: It has a highly scalable & robust architecture that ensures high performance, even when working with complex queries and large volumes of data.
  • Smooth Experience: MySQL is effortless to download, install, easy to use, and is also available free of cost.

For further information on MySQL, you can check the official website here.

What is Replication in MySQL?

Replicate MySQL Database-Replicate MySQL Database.
Image Source: severalnines.com

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.

Simplify MySQL Data Replication Using Hevo’s No-code Data Pipelines

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

MySQL allows users to use the master-slave mechanism to replicate their data across numerous MySQL databases and thereby ensure high data availability at all times.

Method 2: Using Hevo Data to Replicate MySQL Database

Hevo Data, an Automated Data Pipeline, provides you with a hassle-free solution to perform the MySQL Replication with an easy-to-use no-code interface. Hevo is fully managed and completely automates the process of not only replicating data from MySQL but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code.

Hevo’s fault-tolerant Data Pipeline offers a faster way to move your data from MySQL and 100+ other data sources(including 40+ free data sources) into Data Warehouses, Databases, BI Tools, or any other destination of your choice. Hevo will take full charge of the data replication process, allowing you to focus on key business activities.

Prerequisites

  • Working knowledge of MySQL.
  • Working knowledge of MySQL Workbench.
  • A MySQL database.
  • A general idea of data replication.
  • A general idea of MySQL commands.

Methods to Replicate MySQL Database

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

Method 1: 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

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:

Replicate MySQL Database- Updating the Configuration file.

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:

Replicate MySQL Database-Checking the Master DB status.

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:

Replicate MySQL Database-Unlocking tables in MySQL.

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:

Replicate MySQL Database: Updating the Master-Slave Configuration in MySQL.

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

Method 2: Using Hevo Data to Replicate MySQL Database

Replicate MySQL Database: Hevo Logo
Image Source

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:

  • Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
  • 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.
  • Quick Setup: Hevo with its automated features, can be set up in minimal time. Moreover, with its simple and interactive UI, it is extremely easy for new customers to work on and perform operations.
  • 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.
  • Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
  • 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.
  • Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.

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

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 Data provides an Automated No-code Data Pipeline that empowers you to overcome the above-mentioned limitations. Hevo caters to 100+ 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!

No-code Data Pipeline For Your Data Warehouse