Does MySQL support database replication?

MySQL replication is a real-time mechanism that automatically copies or replicates data from one server to another backup server. Database administrators can use the master-slave replication procedure to replicate or copy data from multiple servers at the same time.

This allows the database administrator to create a continuous live backup of the database. They can switch over the slave database and keep the application up and running in various scenarios when the is down due to any difficulties. Your application will not experience any downtime because of the replication procedure.

How it Works

There are different types of replication procedures in this replication. You can have one master and many slaves, or numerous masters and many slaves, and so on.

It’s always a single or one-way data transmission in this operation. Data is initially saved in the master, then replicated to the slaves. As a result, the write operation is limited to the master database. Both the master and the slave perform the read operation. The slaves can also be utilized to minimize the strain on the master database by providing data accessibility.

The Purpose of Master-Slave Replication

One of the main advantages of a master-slave replication system is that it provides a standby system with a live backup that can be promoted to master status if the primary master server fails.

It also has several other advantages, such as:

• Scalability: To lessen the load on the server and provide faster access, all database query requests can be sent to numerous database servers. Most web applications and sites you’ll come across nowadays have more read operations than writing activities to the database. As a result, website administrators must give the ideal arrangement for the speedy loading of information on the site.
• Performance: The master database handles all database write operations. These changes are spread and promoted by the master to the slave after they are made to the master database. However, read requests from websites can be split across numerous slaves to improve the website’s speed.
• Backup: In only a few minutes, you can duplicate the most recent database snapshot to another database and create a backup. Data corruption is reduced because the master server is fault-free and has a 99.9% uptime. This enables apps to handle high numbers of reading and write operations with ease.
• Analytics and Benchmarking: Database analysts can use this approach to execute various data analysis tests and experiments on slaves without disrupting the master.

Steps to Achieve MySQL Master-Slave Replication

In this configuration, we’ll have two RHEL 8 servers with the following IP addresses:

The most recent version of MySQL 8.x is already included in RHEL 8’s default repository, and you may install it using the following yum command:

Setup the MySQL Master Server

Once the MySQL installation process is completed, run the following command to open the MySQL configuration file:

Add the lines mentioned below to the “mysqld” section.

Bind-address = 172.17.0.8

Server-id = 1

Log_bin =mysql-bin

Then MySQL service should be restarted.

We will now create a replication user. As a result, log in as the root user to your MySQL master server and enter the password.

Run the following commands to create the replica user while also providing the user slave access. Keep in mind to utilize your machine’s IP address.

You will now type the following command, which will print the binary filename and position.

Keep track of the generated filename msql-bin.000001 and its location 683.

Set Up the MySQL Slave Server

You should make the following changes to the mysql slave configuration file, just as you did when setting up the master:

Bind-address = 172.17.0.8

Server-id = 2

Log_bin =mysql-bin

Then restart the service.

The slave server must now be configured to replicate from the master server. Then, stop the replication thread and connect to the MySQL server.

mysql> STOP SLAVE;

Run the following query to set up the slave server to replicate from the master server.

Check that you’re using the right IP username and password. Use the file name and position that you received from the master server as well. Finally, run the command below to start the slave threads:

mysql> START SLAVE;

Put MySQL Master-Slave Replication to the Test

Now that you’ve completed the configuration of both the master and slave servers, it’s time to check to see if the configuration is correct and if replication is possible.

To do so, go to the master server and connect to the MySQL database server. Create a sample database.

Now go to the slave server and log in to the MySQL database server once more. Use the following command to list all of the databases.

Simple Set Up

You should be able to set up a master-slave replication in MySQL and start slave threads after reading this tutorial. You can now back up your data, keep it, and change it across several PCs. For more information, contact our experts today.

How to replicate a database in MySQL?

How to setup MySQL database replication?

What are the replication options in MySQL?

How to create a replication user in MySQL?