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:
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:yum install -y @mysqlmysql_secure_installation
Setup the MySQL Master Server
Once the MySQL installation process is completed, run the following command to open the MySQL configuration file:vim /etc/my.cnf
Add the lines mentioned below to the “mysqld” section.
Bind-address = 172.17.0.8
Server-id = 1
Then MySQL service should be restarted.systemctl restart mysqld
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.mysql -u root -p
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
Then restart the service.systemctl restart mysqld
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?
Methods to Replicate MySQL Database.
Method 1: Using Master-Slave Approach to Replicate MySQL Database. ... .
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. ... .
Method 2: Using Hevo Data to Replicate MySQL Database..
How to setup MySQL database replication?
Step 1: Install MySQL on Master and Slave Server. We will start off by installing the MySQL database on both the master and slave servers. ... .
Step 2: Secure MySQL on Master and Slave Server. ... .
Step 3: Configure the Master Node (Server) ... .
Step 4: Configure the Slave Node (Server) ... .
Step 4: Testing MySQL Master-Slave Replication..
What are the replication options in MySQL?
There are two core types of replication format, Statement Based Replication (SBR), which replicates entire SQL statements, and Row Based Replication (RBR), which replicates only the changed rows. You can also use a third variety, Mixed Based Replication (MBR).
How to create a replication user in MySQL?
For example, to set up a new user, repl , that can connect for replication from any host within the example.com domain, issue these statements on the source: mysql> CREATE USER 'repl'@'%. example.com' IDENTIFIED BY 'password'; mysql> GRANT REPLICATION SLAVE ON *. * TO 'repl'@'%.