MySQL Master Slave Replication: 7 Easy Steps – Hevo Data

MySQL replication is a process that allows you to copy/replicate data from one server to another at the same time. It is done primarily to increase data availability. One of the main reasons people opt for MySQL master-slave replication is for data recovery. In the event of any catastrophe or hardware failure, MySQL replication ensures that an accurate backup is in place all the time.

In this article, you will look in detail at the MySQL master-slave replication process along with a step-by-step guide on how to achieve replication.

Table of Contents

What is

• MySQL

?

• What is master-slave replication?

Purpose of Master-Slave

• Replication Prerequisites

Steps to achieve MySQL

• master-slave

replication

• using Hevo’s no-code data pipeline for seamless

MySQL replication

• ConclusionWhat

is MySQL

? MySQL is

one of the most popular and widely used open source RDBMS (Relational Database Management System). MySQL is available for free under the GNU Public License and is also available as a premium proprietary version. MySQL was originally developed by Michael Widenius at MySQL AB, a Sweden-based company. In 2012, Sun Microsystems acquired MySQL AB, and later Oracle acquired Sun Microsystems.

MySQL is used for a variety of applications and is primarily based on SQL (structured query language). MySQL is widely used in applications built with PHP. It is also being used by some of the popular websites, including Twitter, Facebook, Mediawiki, YouTube and Flickr, etc.

What is master-slave replication?

The master-slave replication process allows database administrators to replicate or copy data stored on more than one server simultaneously. This helps the database administrator create a live backup of the database all the time. During some situations, when the master-slave has any problems, he can instantly change the slave database and keep the application running. The replication process ensures that your application does not face any kind of downtime.

In this replication, there are several types of replication processes. You can have a single master and multiple slaves or multiple masters and multiple slaves, etc.

In this process, it is always a unidirectional or unidirectional data transmission. The data is first stored in the master and then copied to the slaves. Therefore, the write operation is performed only on the master database. The reading operation is performed on both the master and the slave. Slaves can also be used for data accessibility to reduce the load on the master database.

One

of

the primary purposes of opting for a master-slave replication system is to have a standby system with a live backup that can be promoted as master when the original master server fails. Apart from this, there are several benefits as described below

:

• Scalability: All query requests to the database can be routed to multiple database servers to reduce the load on the server and allow faster access. Most web applications and sites found today come loaded with more read operations than write to the database. Therefore, website administrators need to provide the perfect setup for fast loading of details on the website.
• Performance: All database write operations are performed on the master database. Once these changes are made to the master database, they are upgraded from master to slave. But read requests from websites can be shared among multiple slaves to increase website performance.

Backup

• : You can replicate the last database snapshot to another database and create a backup in just a couple of minutes. Data corruption is greatly reduced as the master server runs smoothly and provides 99.9% uptime. This allows applications to process large numbers of read or write operations seamlessly.
• Analysis and Bench-Marking: This process allows database analysts to run all kinds of data analysis tests and experiments on slaves without disturbing the master.

Prerequisites

To set up a MySQL master-slave

replication, you must have the following:

• 2 VM (virtual machine) or VPS (virtual private server) with root access

.

• Working Internet

.

Steps to achieve

MySQL master-slave replication For this demonstration purpose,

you will call master as root@repl-master and slave as root@repl-slave

.

For this demonstration, suppose the IP address for master and slave is as follows

: Master server: 12.34.56.111

Slave server: 12.23.34.222

1. Master Configuration

The first thing you must accomplish in the replication process is to install and configure the master server. If you have not installed

MySQL, you can install MySQL using the following command: root@repl-master:~# sudo apt-get update root@repl-master:~# sudo apt-get install mysql-server mysql-client -y root@repl-master:~# sudo mysql_secure_installation After the MySQL

installation process is complete, use the following command to edit the MySQL configuration file

: root@repl-master:~# sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Then, in the same file, locate the line that contains bind-address = 127.0.0.1 and replace that IP address with the IP address of the master replication server. Then, the line will look like this: bind-address=12.34.56.111

Next, look

for the following lines in the file: server-id=1 log_bin=/var/log/mysql/mysql-bin.log

You will see that the above lines have been commented out, simply uncomment these lines and exit the editing interface by clicking CTRL+X. Save the changes and restart the MySQL service for the changes to take effect. Restart the MySQL

service

using the following command

: root@repl-master:~# sudo service mysql restart

2. Create

a new slave user

The next step is to create a new user for your slave server. Use the following command to create it

: root@repl-master:~# mysql -uroot -p; mysql> CREATE THE USER ‘SLAVE’@’12.34.56.789’ IDENTIFIED BY ‘SLAVE_PASSWORD’; mysql> GRANT REPLICATION SLAVE IN . To ‘slave’@’12.34.56.222 ‘; mysql> FLUSH PRIVILEGES; MYSQL> FLUSH TABLES WITH READ LOCK;

You will use the following command to know the current status of the master server:

mysql> SHOW MASTER STATUS;

This command will also tell the slave to follow the master from this position.

3. Move

data from master to slave

Now that you have marked the position, you can start moving data from master to slave. You must create a MySQL dump file to move the data. Use

the following command to create the dump file: root@repl-master:~# mysqldump -u root -p -all-databases -master-data > data.sql

To copy the dump file to the slave, use

the following command: scp data.sql root@12.34.56.222

Unlock the tables with the following command:

mysql> UNLOCK TABLES;

4. Configure

the slave server

Now, all you need to do is configure the slave server and test if replication is working. Make sure MySQL is installed. Open the configuration file on your

slave server and update these lines: root@repl-slave:~# sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

In the same way you did for the master server, you need to bind the IP address and uncomment those two lines for the slave server. Now, restart the MySQL server using the following command

: root@repl-slave:~# sudo service mysql restart

5. Import

data dump Use the following command to import the

dump file to the

slave server: root@repl-slave:~# mysql -uroot -p < data.sql

After the data is imported, you must stop MySQL on the slave server using the following command: root@repl-slave

:~# mysql -uroot -p; mysql> STOP SLAVE;

Finally you have imported the dump files and updated the master IP address, password, log file name and position, to allow the master to communicate with the slave without any problems.

6

. Start Slave Server

Next, use the “Start Slave” command to start operating the slave server.

START SLAVE; 7. Try MySQL

Master Slave

Replication To test if your MySQL master slave

replication works, simply create a database on your master server and see if it replicates to the slave server. If you can see the database on the slave, then it is working fine.

Create a test database on a master server named ‘sampledb’.

CREATE SAMPLEDB DATABASE;

Now log in to your slave server and list the databases, and if you see the “sampledb” there, then the master slave replication process works fine.

Log in to your slave server and use the following command to enumerate all databases: show databases

; Using

Hevo’s

no-code data pipeline for seamless MySQL

,

an automated no-code data pipeline provides a hassle-free solution and helps you replicate your data from MySQL to any database, data warehouse, or destination of your choice in minutes. Hevo is fully managed and fully automates the process of not only loading data from the desired source, but also enriching the data and transforming it into a form ready for analysis without having to write a single line of code.

Hevo’s pre-built integration with MySQL and 100+ data sources in databases, files, analytics engines, etc. gives users the flexibility to bring in data of all kinds as seamlessly as possible, without having to write a single line of code. Hevo takes over your data transfer process and allows you to focus your engineering bandwidth on key business activities.

More reasons to love

Hevo: Secure: Hevo

• has a fault-tolerant architecture that ensures data is handled securely and consistently without data loss

.

• Auto Schema Mapping: Hevo eliminates the tedious task of schema management and automatically detects the schema of incoming data from MySQL files and maps it to the target schema.
• Quick setup: Hevo with its automated functions, can be set up in minimal time. In addition, with its simple and interactive user interface, it is extremely easy for new customers to work and perform trades.
• Pre- and post-upload transformations: With Transformations in Hevo, you can prepare data in several ways before uploading it to the destination. Hevo offers numerous data transformations, including cleaning, reexpression, data filtering, normalization, etc.
• Hevo is designed to scale: As the number of sources and volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
• Live Support: The Hevo team is available twenty-four hours a day to provide exceptional support to their customers via chat, email, and support calls.

With continuous real-time data movement, upload your data from MySQL to your target store with Hevo’s easy-to-configure, no-code interface. Try our 14-day all-access free trial.

Get started with Hevo for free

Conclusion

The above article has provided you with enough information on how to set up a MySQL master-slave replication. The step-by-step guide is specially provided to help beginners understand the process and begin testing the replication process in their environment. But this process requires hard and extensive work as you have to manually set up a lot of details. So, if you want to bypass this difficulty and experience a smooth process, try Hevo.

Hevo Data provides an automated code-free data pipeline that allows you to overcome the limitations mentioned above. You can leverage Hevo to seamlessly replicate your MySQL data in real-time without writing a single line of code. Hevo’s Data Pipeline enriches your data and manages the transfer process in a fully automated and secure way. Hevo caters to 100+ data sources (including 40+ free sources) and can transfer data directly to data warehouses, business intelligence tools, or any other destination of your choice seamlessly. It will make your life easier and make data migration seamless.

Learn more about Hevo

You can post your experience and feedback on the MySQL master-slave replication process in the comments section below.