MySQL Master-Slave Replication on CentOS 7

Introduction

MySQL replication is the process of automatically copying data from one database server to one or more servers.

MySQL offers a variety of replication topologies, the most well-known of which is the Master/Slave topology, in which one database server operates as the master and one or more servers function as slaves. By default, replication is asynchronous, with the master sending events to its binary log that describe database changes and slaves requesting the events when they are ready.

In this article, we will show you how to set up a MySQL Master/Slave replication on CentOS 7 with one master and one slave server. The same procedures apply to MariaDB.
This replication architecture is best suited for read replication.

Basic requirements

In this example, we assume you have two servers running CentOS 7, and they can communicate with each other via a private network. If your hosting provider does not provide private IP addresses, you can use public IP addresses and configure the firewall to only allow traffic on port 3306 from trusted sources.

The IP addresses of the servers in this example are as follows:

Master IP: 10.10.0.44
Slave IP:  10.10.0.20

Install MySQL

You can follow this article on Installing MySQL on CentOS / Redhat 7/6 & Fedora 31/30.

Configure the Master Server

First, we will configure the master MySQL server with the following changes:

• Configure the MySQL server to listen on its private IP address.
• Create a distinct server ID.
• Turn on binary logging.

To achieve this, edit the MySQL configuration file and insert the following lines in the [mysqld] section:

$ sudo nano /etc/my.cnf

Master Server :/etc/my.cnf

bind-address           = 10.10.0.44
server-id              = 44
log_bin                = mysql-bin

Once done, restart the MySQL service for changes to take effect.

$ sudo systemctl restart mysqld

The next step is to set up a new replication user. Log in as the root user to the MySQL server:

$ mysql -uroot -p

Run the following SQL statements from within the MySQL prompt to create the slave1 user and grant the user the REPLICATION SLAVE privilege:

mysql> CREATE USER 'slave1'@'10.10.0.20' IDENTIFIED BY 'secret_password';

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'10.10.0.20';

While you’re still in the MySQL prompt, use the following command to output the binary filename and position.

mysql> SHOW MASTER STATUS\G

*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 1714
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

Make note of the file name “mysql-bin.000001” and position “1714”. You will need these values when configuring the slave server. These values may be different on your server.

Configure the Slave Server

Like the Master server, we will make the following changes to the slave server:

• Configure the MySQL server to listen on its private IP address.
• Create a distinct server ID.
• Turn on binary logging.

Open the MySQL configuration file and edit the following lines:

$ sudo nano /etc/my.cnf

Slave Server:/etc/my.cnf

bind-address           = 10.10.0.20
server-id              = 20
log_bin                = mysql-bin

Restart the MySQL service for changes to take effect.

$ sudo systemctl restart mysqld

The next step is to set the parameters that will be used by the slave server to connect to the master server. Log in to the MySQL shell as follows:

$ mysql -uroot -p

First, stop the slave threads:

mysql> STOP SLAVE;

Execute the following query to configure the slave to duplicate the master:

mysql> CHANGE MASTER TO
mysql> MASTER_HOST='10.10.0.44',
mysql> MASTER_USER='slave1',
mysql> MASTER_PASSWORD='secret_password',
mysql> MASTER_LOG_FILE='mysql-bin.000001',
mysql> MASTER_LOG_POS=1714;

Ensure that you are using the correct IP address, username, and password. The log file name and location must be the same values you got from the Master server.

After that, start the slave threads.

mysql> START SLAVE;

Test the Configuration

You should now have a functional Master/Slave replication configuration.

On the master server, we’ll create a new database to ensure that everything works as expected:

$ mysql -uroot -p

mysql> CREATE DATABASE test_db;

Log in to the slave MySQL shell with the following command:

$ mysql -uroot -p

To list all databases, use the following command:

mysql> SHOW DATABASES;

Output:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test_db            |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

Conclusion

In this article, we demonstrated how to set up a MySQL Master/Slave replication on CentOS 7.

If you have any questions, please leave a comment.