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. This ensures data consistency and availability across multiple machines.

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. This setup is crucial for tasks like read scaling, backups, and disaster recovery.

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. This is a fundamental skill for database administrators and developers working with MySQL. Understanding and implementing MySQL Master-Slave Replication on CentOS 7 is essential for building robust and scalable database solutions.

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. Security is paramount, so always restrict access to your database servers.

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. This guide will walk you through the installation process, ensuring you have a properly functioning MySQL server on both the master and slave.

Configure the Master Server

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

• Bind MySQL to the master server’s IP address.
• Set a unique server ID for the master.
• Enable 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. This information is critical for synchronizing the slave server.

mysql> SHOW MASTER STATUSG

*************************** 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:

• Bind MySQL to the slave server’s IP address.
• Set a unique server ID for the slave.
• Enable 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. Setting up MySQL Master-Slave Replication on CentOS 7 is a valuable skill.

Alternative Solutions for MySQL Replication

While the traditional Master-Slave replication provides a solid foundation, other approaches offer enhanced features and flexibility. Here are two alternative solutions:

1. Group Replication

Group Replication is a more advanced replication technology that provides high availability and fault tolerance. Unlike traditional Master-Slave replication, Group Replication allows multiple servers to act as masters, forming a distributed, self-managing system. Any member of the group can accept writes, and data is automatically replicated across the group, ensuring consistency.

Explanation:

Group Replication uses a consensus algorithm (e.g., Paxos) to ensure that all members of the group agree on the order of transactions. This eliminates the single point of failure inherent in the traditional Master-Slave setup. If one server fails, the other members automatically take over, ensuring continuous operation. Group Replication can be synchronous or asynchronous, offering different levels of data consistency and performance. This is a powerful alternative to MySQL Master-Slave Replication on CentOS 7.

Implementation Example (Conceptual):

Setting up Group Replication is more complex than traditional Master-Slave. It involves configuring each server to be part of the group and defining the group’s parameters. The exact configuration varies depending on the MySQL version.

-- Example (Conceptual - Requires specific Group Replication setup steps)
-- On each server:

-- Configure group name
SET GLOBAL group_replication_group_name = 'my_replication_group';

-- Configure seed members (other servers in the group)
SET GLOBAL group_replication_bootstrap_group = OFF; -- Only on the first server

-- Start Group Replication
START GROUP_REPLICATION;

Benefits:

• High Availability: No single point of failure.
• Fault Tolerance: Automatic failover in case of server failure.
• Data Consistency: Guaranteed data consistency across the group.
• Multi-Master Support: Any server can accept writes.

Drawbacks:

• Complexity: More complex to set up and manage than Master-Slave.
• Performance Overhead: The consensus algorithm can introduce some performance overhead.

2. MySQL Router with Read/Write Splitting

MySQL Router is a lightweight middleware that intelligently routes client connections to the appropriate MySQL server. It can be configured to perform read/write splitting, directing read queries to slave servers and write queries to the master server. This approach improves performance by distributing the load across multiple servers.

Explanation:

MySQL Router acts as a proxy between the client application and the MySQL servers. It analyzes incoming queries and forwards them to the appropriate server based on pre-defined rules. For example, SELECT statements can be routed to the slave servers, while INSERT, UPDATE, and DELETE statements are routed to the master. This configuration is especially useful for applications with a high read-to-write ratio. MySQL Router can also handle failover, automatically redirecting traffic to a healthy master server if the primary master fails. This approach complements MySQL Master-Slave Replication on CentOS 7.

Implementation Example:

1. Install MySQL Router: Download and install MySQL Router on a separate server or on one of the MySQL servers.

2. Configure MySQL Router: Create a configuration file (e.g., mysqlrouter.conf) that defines the connection parameters for the master and slave servers and the routing rules.

[routing:read_only]
bind_address = 0.0.0.0:3307
destinations = 10.10.0.20:3306  # Slave IP and Port
mode = read-only

[routing:read_write]
bind_address = 0.0.0.0:3308
destinations = 10.10.0.44:3306  # Master IP and Port
mode = read-write

[metadata_cache]
router_id = 1
bootstrap_servers = 10.10.0.44:3306  # Master IP and Port for metadata retrieval

3. Start MySQL Router: Start the MySQL Router service using the configuration file.

mysqlrouter --config /path/to/mysqlrouter.conf

4. Update Application Connection String: Modify the application’s connection string to point to the MySQL Router’s IP address and the appropriate port (3307 for read-only, 3308 for read-write).

Benefits:

• Improved Performance: Read/write splitting distributes the load.
• Simplified Application Code: The application doesn’t need to be aware of the replication topology. MySQL Router handles the routing.
• Failover Support: Can automatically redirect traffic in case of master failure.

Drawbacks:

• Additional Component: Requires installing and managing MySQL Router.
• Potential Latency: The router adds a small amount of latency to each query.

These alternative solutions provide different ways to enhance MySQL replication, depending on the specific requirements of your application and infrastructure. Choosing the right approach depends on factors like desired level of availability, performance requirements, and complexity tolerance. Understanding these alternatives allows for a more informed decision beyond the basic MySQL Master-Slave Replication on CentOS 7 setup.