How to Setup MySQL Replication in RHEL, Rocky and AlmaLinux

[‘

n

Data replication is the process of copying your data across multiple servers to improve data availability and enhance the reliability and performance of an application. In MySQL replication, data is copied from a database from the master server to other nodes in real-time to ensure consistency of data and also to provide backup and redundancy.

n

In this guide, we demonstrate how you can set up MySQL (Master-Slave) replication in RHEL-based distributions such as CentOS, Fedora, Rocky Linux, and AlmaLinux.

n

MySQL Replication Setup

n

So, here is our MySQL replication lab setup.

n

MySQL Master - 10.128.0.14rnMySQL Slave - 10.128.15.211rn

n

Let’s get started…

n

Step 1: Install MySQL on Master and Slave Server

n

We will start off by installing the MySQL database on both the master and slave servers.

n

$ sudo dnf install @mysqlrn

n

Install MySQL in Rocky Linux
Install MySQL in Rocky Linux

n

Once the installation is complete, make a point to start the database server.

n

$ sudo systemctl start mysqldrn

n

Then enable it to start to system startup or upon reboot.

n

$ sudo systemctl enable mysqldrn

n

Thereafter, confirm that the MySQL database server is running as shown:

n

$ sudo systemctl status mysqldrn

n

Check MySQL Running Status
Check MySQL Running Status

n

Step 2: Secure MySQL on Master and Slave Server

n

The next step is to secure the MySQL database on both the master and slave servers. This is because the default settings are insecure and present some loopholes which can easily be exploited by hackers.

n

So, to harden MySQL, run the command:

n

$ sudo mysql_secure_installationrn

n

First, you will be required to set the MySQL root password. Be sure to provide a strong root password, preferably with more than 8 characters which are a mix of uppercase, lowercase, special and numeric characters.

n

Secure MySQL in Rocky Linux
Secure MySQL in Rocky Linux

n

For the remaining prompts, type in 'Y' to tweak the database server to the recommended settings.

n

Confirm MySQL Settings
Confirm MySQL Settings

n

Once you have finished installing and hardening MySQL on the master and slave node, the next is to configure the master node.

n

Step 3: Configure the Master Node (Server)

n

The next step is to configure the Master node and grant the slave node access to it. First, we need to edit the mysql-server.cnf configuration file.

n

$ sudo vim /etc/my.cnf.d/mysql-server.cnfrn

n

Add the following lines under the [mysqld] section.

n

bind-addresst = 10.128.0.14rnserver-id t = 1rnlog_bintt = mysql-binrn

n

Once done, save the changes and exit. Then restart the MySQL server.

n

$ sudo sysemctl restart mysqldrn

n

Next, log into MySQL shell.

n

$ sudo mysql -u root -prn

n

Execute the following commands to create a database user that will be used to bind the master and slave for replication.

n

mysql> CREATE USER 'replica'@'10.128.15.211' IDENTIFIED BY '[emailxa0protected]';rnmysql> GRANT REPLICATION SLAVE ON *.*TO 'replica'@'10.128.15.211';rn

n

Create Database Replication User
Create Database Replication User

n

Apply the changes and exit the MySQL server.

n

mysql> FLUSH PRIVILEGES;rnmysql> EXIT;rn

n

Verify the status of the master.

n

mysql> SHOW MASTER STATUS\Grn

n

Verify MySQL Status
Verify MySQL Status

n

Take note of the Filename and Position. You will need this later on when setting up the slave for replication. In our case, we have the filename as mysql-bin.000001 and Position 1232.

n

Step 4: Configure the Slave Node (Server)

n

Now, head back to the Slave node. Once again, edit the mysql-server.cnf configuration file.

n

$ sudo vim /etc/my.cnf.d/mysql-server.cnfrn

n

As before, paste these lines under the [mysqld] section. Change the IP address to correspond to the slave’s IP. Also, assign a different server-id. Here we have assigned it the value of 2.

n

bind-addresst = 10.128.15.211rnserver-idt = 2rnlog_bin t = mysql-binrn

n

Save the changes and exit the file. Then restart the database server.

n

$ sudo systemctl restart mysqldrn

n

To configure the Slave node to replicate from the Master node, log in to the Slave’s MySQL server.

n

$ sudo mysql -u root -prn

n

First and foremost, stop the replication threads:

n

mysql> STOP SLAVE;rn

n

Stop MySQL Slave
Stop MySQL Slave

n

Then execute the following command to configure the slave node to replicate databases from the master.

n

mysql> CHANGE MASTER TOrn     MASTER_HOST='10.128.0.14' ,rn     MASTER_USER='replica' ,rn     MASTER_PASSWORD='[emailxa0protected]' ,rn     MASTER_LOG_FILE='mysql-bin.000001' ,rn     MASTER_LOG_POS=1232;rn

n

Note that the MASTER_LOG_FILE and MASTER_LOG_POS flags correspond to the file and Position values from the Master node at the end of Step 1.

n

The MASTER_HOST, MASTER_USER, and MASTER_PASSWORD correspond to the Master IP address, the replication user, and the replication user’s password respectively.

n

Configure MySQL Slave Replication
Configure MySQL Slave Replication

n

Then start the slave replication threads:

n

mysql> START SLAVE;rn

n

Start MySQL Slave
Start MySQL Slave

n

Step 4: Testing MySQL Master-Slave Replication

n

Now, to test if replication between the master and slave node is working, log in to the MySQL database server on the master node:

n

$ sudo mysql -u root -prn

n

Create a test database. Here, our test database is called replication_db.

n

mysql> CREATE DATABASE replication_db;

n

Verify the existence of the database.

n

mysql> SHOW DATABASES;rn

n

Create Database on MySQL Master
Create Database on MySQL Master

n

Now, head over to the slave node, log in to the MySQL server and confirm that the replication_db database is present. From the output below, we can see that the database is present. This is confirmation that replication has taken place from the Master to the slave node.

n

mysql> SHOW DATABASES;rn

n

Check MySQL Master Slave Replication
Check MySQL Master-Slave Replication

n

And that’s it, we have successfully demonstrated how you can set up a MySQL master-slave replication model that can replicate databases from the master node to the slave node.

n

‘]