Replicate MySQL Database: 2 Easy Steps

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

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 Data Replication using Hevo’s No-code Data Pipelines

Hevo Data, a No-code Data Pipeline, can help you replicate data from 100+ sources swiftly to a database/data warehouse of your choice. Hevo is fully-managed and completely automates the process of monitoring and replicating the changes on the secondary database rather than making the user write the code repeatedly. Its fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss.

Hevo provides you with a truly efficient and fully-automated solution to replicate and manage data in real-time and always have analysis-ready data in your desired destination. It allows you to focus on key business needs and perform insightful analysis using BI tools. 

Get Started with Hevo for Free

Have a look at the amazing features of Hevo:

  • Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
  • Minimal Learning: Hevo, with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
  • Live Monitoring: Hevo allows you to monitor the data flow so you can check where your data is at a particular point in time.
  • Data Transformation: It provides a simple interface to perfect, modify, and enrich the data you want to export. 
  • Schema Management: Hevo takes away the tedious task of schema management & automatically detects schema of incoming data and maps it to the destination schema.
  • Completely Managed Platform: Hevo is fully managed. You need not invest time and effort to maintain or monitor the infrastructure involved in executing codes.
Sign up here for a 14-Day Free Trial!

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.

Steps 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.

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.

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. These methods, however, can be challenging especially for a beginner & this is where Hevo saves the day.

Visit our Website to Explore Hevo

Hevo Data, a No-code Data Pipeline, helps you transfer data from a source of your choice in a fully-automated and secure manner without having to write the code repeatedly. Hevo, with its strong integration with 100+ sources & BI tools, allows you to not only export & load data but also transform & enrich your data & make it analysis-ready in a jiff.

Give Hevo a shot! Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. Check out the pricing details to get a better understanding of which plan suits you the most.

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