MySQL master-slave replication encryption and binlog encryption implementation

1 Overview

This article mainly describes how to use SSL for encryption in the process of master-slave replication, and the implementation of binlog encryption ( MySQL 8.0.14+ ).

2 Environment

• MySQL 8.0.25
• Docker
• One master and one slave

3 Prepare the container

First pull the image and start it, the main library 3306 , the slave library 3307 , and the --ssl parameter needs to be added to enable the function of SSL encrypted connection:

docker pull mysql
docker run -itd -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 --name mysql-master mysql --ssl
docker run -itd -p 3307:3306 -p 33061:33060 -e MYSQL_ROOT_PASSWORD=123456 --name mysql-slave mysql --ssl

After starting the container go into the container and install vim and net-tools :

apt install vim net-tools

At the same time enter MySQL to prepare the data source:

create database test;
use test;
create table user(
    id int primary key auto_increment,
    name varchar(30) not null,
    age int not null
);

4 SSL connection preparation

4.1 Make sure SSL enabled

First, you can enter MySQL to ensure that SSL function is enabled:

show variables like '%ssl%';

4.2 Generate CA and private key

By default, there are already generated certificate and private key files under /var/lib/mysql . If you do not want to use the default ones, you can regenerate them:

sudo mkdir /mysql
sudo chown mysql:mysql /mysql
mysql_ssl_rsa_setup --datadir=/mysql

The generated files include:

• ca-key.pem : CA private key
• ca.pem : Self-signed CA certificate
• client-key.pem : The private key used by the client when connecting
• client-cert.pem : The certificate used by the client when connecting
• server-key.pem : server-side private key
• server-cert.pem : server-side certificate
• public_key.pem / private_key.pem : the public and private keys of the key pair

The actual use only needs to use five files: ca.pem , server private key certificate, client private key certificate. When master-slave replication, the master library acts as the server and the slave library acts as the client, so ca.pem / server-key.pem / server-client.pem only needs to be configured in the master library, and ca.pem / client-key.pem / client-cert.pem needs to be transferred from the scp to the slave library three times.

4.3 Modify permissions

Permission errors will cause MySQL to not use the functions of SSL normally:

sudo chown mysql:mysql /mysql/*.pem
sudo chmod 400 /mysql/*.pem
sudo chmod 444 /mysql/ca.pem

4.4 Modify the configuration file

Modify the configuration file of the main library as follows:

[mysqld]
ssl_ca=/mysql/ca.pem
ssl_cert=/mysql/server-cert.pem
ssl_key=/mysql/server-key.pem

And the configuration file from the library is modified as follows:

[client]
ssl-ca=/mysql/ca.pem
ssl-cert=/mysql/client-cert.pem
ssl-key=/mysql/client-key.pem

At this time, the slave library cannot connect to itself, but can only connect to the master library. If you need to connect yourself, you need to copy server-key.pem / server-cert.pem of the master library to the slave library, and configure [mysqld] of the slave library:

[mysqld]
ssl_ca=/mysql/ca.pem
ssl_cert=/mysql/server-cert.pem
ssl_key=/mysql/server-key.pem

5 Other configurations of master-slave replication

The following are some of the most common and simplest configurations of master-slave replication. The master library is only configured with id and the library that needs to be copied:

[mysqld]
server-id=1                
binlog-do-db=test         

The configuration of the slave library is as follows:

[mysqld]
server-id=2            
replicate-do-db=test   

After modification, restart the master library and slave library.

6 Create a user for master-slave replication

Create a master-slave replication user in the master library (please use ip to view ifconfig ):

create user 'repl'@'172.17.0.3' identified with mysql_native_password by '123456' require ssl;
grant replication slave on *.* to 'repl'@'172.17.0.3';

7 Modify the configuration of the slave library to point to the master library

First check the status of the main library:

show master status;

Record File and Position , and use change master to / change replication source to ( 8.0.23+ ) in the slave library to set the master library information:

change master to
master_host = '172.17.0.2',
master_user = 'repl',
master_password = '123456',
master_log_file = 'binlog.000005',
master_log_pos = 156,
master_ssl = 1;

or

change replication source to
source_host = '172.17.0.2',
source_user = 'repl',
source_password = '123456',
source_log_file = 'binlog.000005',
source_log_pos = 156;  
source_ssl = 1;

8 Start the slave library and test

You can use start slave / start replica ( 8.0.22+ ) to start the replication function from the library:

start slave
# 或
start replica

Use after startup

show slave stauts\G

Check the status of the slave library:

It needs to display two Yes to be successful. If Slave_IO_Running always displays Connecting , the possible reasons are:

• The main library address, port, user name, password, etc. configured in the slave library are wrong
• SSL is misconfigured, such as using the wrong client-key.pem
• firewall problem

Please check the log for yourself, the log location can be checked by

show variables like 'log_error'

Check.

After there is no problem, try inserting data in the main library:

use test;
insert into user values('111',1);

You can query it from the library:

use test;
select * from user;

9 binlog encryption

Starting from 8.0.14 , MySQL provides encryption for binlog . By default, binlog is not encrypted, and encryption requires the use of keyring plugins or components:

The implementation steps are as follows:

• Install keyring_file plugin
• Change setting
• test

9.1 Install the plugin

MySQL provides installation of the following plugins:

Since the community version is used, the community version only supports keyring_file plug-in. Take this as an example.

The main library and the slave library modify the configuration files as follows:

[mysqld]
early-plugin-load=keyring_file.so
keyring_file_data=/mysql/keyring

After restarting, enter MySQL to view:

select plugin_name,plugin_status from information_schema.plugins where plugin_name like 'keyring%';

It needs to be in the ACTIVE state, so that's a success.

9.2 Modify the configuration

The encryption of binlog is controlled by a system variable binlog_encryption , which needs to be turned on manually:

set global binlog_encryption=ON;
set persist binlog_encryption=ON;

View the log after opening:

show binary logs;

You can see that it is encrypted binlog :

The binlog that was not encrypted before can be deleted after manually migrating the data.

After encrypting binlog , it is not necessary to modify the configuration of master-slave replication, and the master-slave replication still takes effect, as shown in the following figure:

The main library inserted a user from the library can still select .

10 Reference Links

• MySQL-17.3.2 Encrypting Binary Log Files and Relay Log Files
• MySQL-6.3.1 Configuring MySQL to Use Encrypted Connections
• MySQL-4.4.3 mysql_ssl_rsa_setup — Create SSL/RSA Files
• MySQL-6.4.4.6 Using the keyring_file File-Based Keyring Plugin
• MySQL-6.4.4.3 Keyring Plugin Installation
• MySQL-6.4.4.2 Keyring Component Installation
• MySQL-5.6.2 Obtaining Server Plugin Information
• MySQL High Availablity-Binary log encryption at rest
• StackOverflow-MySQL - SSL is required but the server doesn't support it