How to Setup MySQL Replication in RHEL/Centos

In this article, I will demonstrate how to setup MySQL replication between Master and Slave database servers. This will use two servers, one of which will replicate data from the other (Master to Slave). Use this setup if you want enhanced reliability and performance out of your systems configuration.

Please refer to one of our other tutorials if you need help setting up an RHEL based system.

For this guide, we will be using this MySQL replication lab setup.

MySQL Master - 192.168.56.1
MySQL Slave  - 192.168.56.2 

Step 1: Install MySQL on Master and Slave Server

Start off by installing the MySQL database on both the master and slave servers.

$ sudo dnf install @mysql

or

$ sudo yum install mysql-server

Once the installation is complete, start the database service.

$ sudo systemctl start mysqld

Then enable it to start with systemctl. Enabling it will tell the service to start in the boot sequence.

$ sudo systemctl enable mysqld

Then confirm that the MySQL database server is running:

$ sudo systemctl status mysqld

Step 2: Secure MySQL on Master and Slave Server

It’s recommended to secure your new database server with the following command:

$ sudo mysql_secure_installation

To begin, you’ll need to set the MySQL root password. Make sure you use a strong root password, preferably one that contains a combination of uppercase, lowercase, special, and numeric characters and is longer than 8 characters.

For the remaining prompts, type in ‘Y’ to configure the database server to the recommended settings.

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

Step 3: Configure the Master Node (Server)

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

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

Add the following lines under the [mysqld] section.

bind-address	 = 192.168.56.1
server-id 	 = 1
log_bin		 = mysql-bin

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

$ sudo sysemctl restart mysqld

Next, log into MySQL shell.

$ sudo mysql -u root -p

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

mysql> CREATE USER 'replica'@'192.168.56.2' IDENTIFIED BY 'Password';
mysql> GRANT REPLICATION SLAVE ON *.*TO 'replica'@'192.168.56.2';

Apply the changes and exit the MySQL server.

mysql> FLUSH PRIVILEGES;
mysql> EXIT;

Verify the status of the master.

mysql> SHOW MASTER STATUS\G

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 binlog.000001 and Position 854.

Step 4: Configure the Slave Node (Server)

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

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

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.

bind-address	 = 192.168.56.2
server-id	 = 2
log_bin 	 = mysql-bin

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

$ sudo systemctl restart mysqld

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

$ sudo mysql -u root -p

Begin by stopping the replication threads:

mysql> STOP SLAVE;

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

mysql> CHANGE MASTER TO
     MASTER_HOST='192.168.56.1' ,
     MASTER_USER='replica' ,
     MASTER_PASSWORD='Password' ,
     MASTER_LOG_FILE='binlog.000001' ,
     MASTER_LOG_POS=854;

The MASTER LOG FILE and MASTER LOG POS flags match the file and position values from the Master node at the end of Step 1.

MASTER HOST, MASTER USER, and MASTER PASSWORD are the Master IP address, replication user, and password, respectively.

The slave replication threads should then be started:

mysql> START SLAVE;

Step 4: Testing MySQL Master-Slave Replication

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

$ sudo mysql -u root -p

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

mysql> CREATE DATABASE replication_db;

Verify the existence of the database.

mysql> SHOW DATABASES;

Now go to the slave node, log in to the MySQL server, and double-check that the replication db database exists. We can see that the database is present in the output below. This indicates that replication from the Master to the slave node has occurred.

mysql> SHOW DATABASES;