Introduction

MySQL replication is a powerful feature that enhances data availability, scalability, and disaster recovery capabilities. However, to maintain the integrity and security of your replicated data, it is essential to configure replication securely. This article will guide you through setting up secure replication in MySQL on Linux servers, using SSL encryption and user authentication best practices.

Prerequisites

Before we start, ensure you have the following:

  • Two Linux servers (Master and Slave) with MySQL (version 5.6 or later) installed.

  • Root or sudo access to both servers.

  • Basic knowledge of MySQL commands and configuration.

Step 1: Configuring the Master Server

  1. Update MySQL Configuration

    Edit the MySQL configuration file, typically located at /etc/my.cnf or /etc/mysql/my.cnf:

    sudo nano /etc/my.cnf

    Add or modify the following settings under the [mysqld] section:

    server-id = 1
    
log_bin = mysql-bin
    
binlog_do_db = your_database_name
    
require_secure_transport = ON

    • server-id: Unique identifier for the server.

    • log_bin: Enables binary logging.

    • binlog_do_db: Specifies the database to replicate.

    • require_secure_transport: Ensures connections using secure transport (SSL).

  2. Create a Replication User

    Log into MySQL and create a replication user with the REPLICATION SLAVE privilege:

    CREATE USER 'replicator'@'%' IDENTIFIED BY 'your_secure_password';
    
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
    
FLUSH PRIVILEGES;

  3. Generate SSL Certificates

    MySQL supports SSL connections to secure data transferred between the Master and Slave. Create a directory for storing SSL certificates:

    sudo mkdir /etc/mysql/ssl
    
sudo chmod 700 /etc/mysql/ssl

    Generate the necessary SSL certificates:

    sudo openssl genrsa 2048 > /etc/mysql/ssl/server-key.pem
    
sudo openssl req -new -x509 -key /etc/mysql/ssl/server-key.pem -out /etc/mysql/ssl/server-cert.pem -days 365
    
sudo openssl req -newkey rsa:2048 -days 365 -nodes -x509 -keyout /etc/mysql/ssl/ca-key.pem -out /etc/mysql/ssl/ca-cert.pem

    Adjust permissions:

    sudo chown mysql:mysql /etc/mysql/ssl/*
    
sudo chmod 600 /etc/mysql/ssl/server-key.pem

  4. Update MySQL with SSL Settings

    Add the following lines to your my.cnf configuration:

    ssl-ca = /etc/mysql/ssl/ca-cert.pem
    
ssl-cert = /etc/mysql/ssl/server-cert.pem
    
ssl-key = /etc/mysql/ssl/server-key.pem

  5. Restart MySQL

    Restart the MySQL service to apply the changes:

    sudo systemctl restart mysql

Step 2: Configuring the Slave Server

  1. Update MySQL Configuration

    Edit the /etc/my.cnf file on the Slave server:

    sudo nano /etc/my.cnf

    Add the following settings:

    server-id = 2
    
require_secure_transport = ON

  2. Configure SSL for the Slave

    Just like on the Master, generate SSL certificates for the Slave server as shown previously and make the configurations:

    ssl-ca = /etc/mysql/ssl/ca-cert.pem
    
ssl-cert = /etc/mysql/ssl/server-cert.pem
    
ssl-key = /etc/mysql/ssl/server-key.pem

  3. Restart MySQL

    Restart the MySQL service:

    sudo systemctl restart mysql

  4. Set Up Replication

    Log into the Slave MySQL console:

    mysql -u root -p

    Use the following command to set up replication:

    CHANGE MASTER TO
    
   MASTER_HOST='master_server_ip',
    
   MASTER_USER='replicator',
    
   MASTER_PASSWORD='your_secure_password',
    
   MASTER_SSL=1,
    
   MASTER_SSL_CA='/etc/mysql/ssl/ca-cert.pem',
    
   MASTER_SSL_CERT='/etc/mysql/ssl/server-cert.pem',
    
   MASTER_SSL_KEY='/etc/mysql/ssl/server-key.pem',
    
   MASTER_LOG_FILE='mysql-bin.000001',
    
   MASTER_LOG_POS=0;

    Adjust MASTER_LOG_FILE and MASTER_LOG_POS based on the Master’s binary log information, which can be found by running SHOW MASTER STATUS; on the Master.

  5. Start Replication

    Execute the following command on the Slave:

    START SLAVE;

  6. Verify Replication Status

    To ensure replication is running smoothly, check the slave status:

    SHOW SLAVE STATUS\G

    Look for Slave_IO_Running and Slave_SQL_Running. Both should be set to Yes.

Conclusion

Configuring secure replication in MySQL on Linux servers is crucial for maintaining data integrity and privacy. By following the steps outlined above, you can ensure that your data is transmitted securely between the Master and Slave servers. Keep your MySQL installation updated, monitor your replication, and conduct regular checks to maintain optimal performance and security.

For more tips and tutorials, stay tuned to WafaTech Blog!