1 overview

This article mainly describes how to use SSL encryption during master/slave replication, and binlog encryption (MySQL 8.0.14+) implementation.

2 the environment

  • MySQL 8.0.25
  • Docker
  • A master from

3 Preparing containers

To enable ssl-encrypted connections, add — SSL parameter to primary library 3306 and secondary library 3307

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
Copy the code

After starting the container, enter the container and install vim and Net-Tools:

apt install vim net-tools
Copy the code

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
);
Copy the code

4 SSLThe connection to

4.1 make sureSSLopen

You can enter MySQL to ensure that SSL is enabled:

show variables like '%ssl%';
Copy the code

4.2 to generateCAAnd a private key

By default, /var/lib/mysql already has a generated certificate and private key file. If you do not want to use the default certificate, you can generate it again:

sudo mkdir /mysql
sudo chown mysql:mysql /mysql
mysql_ssl_rsa_setup --datadir=/mysql
Copy the code

The generated files include:

  • ca-key.pem:CAThe private key
  • ca.pem: Self-signedCAcertificate
  • client-key.pem: Private key used for client connection
  • client-cert.pem: Certificate used for client connection
  • server-key.pem: Private key of the server
  • server-cert.pem: Server certificate
  • public_key.pem/private_key.pem: Indicates the public and private keys of the key pair

Only ca.pem, server private key certificate, and client private key certificate are required. Pem /server-key.pem/server-client.pem needs to be configured only in the primary database because the primary database serves as the server and the secondary database serves as the client. Pem /client-key.pem/client-cert.pem must be uploaded to the slave library through SCP.

4.3 Modifying Permission

MySQL cannot use SSL properly because of permission error:

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

4.4 Modifying a Configuration File

Modify the main library configuration file as follows:

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

The change from the library configuration file is as follows:

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

Pem /server-cert.pem of the master library must be copied to the slave library and configured with [mysqld] of the slave library:

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

5 Perform other configurations for primary/secondary replication

The following are some of the most common and simplest configurations for master/slave replication. The master library is configured with only the ID and the library to be replicated:

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

The configuration of the slave library is as follows:

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

After modification, restart the master and slave libraries.

6 Create a master/slave replication user

Create a master/slave replication user in the master database (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';
Copy the code

7 Modify the secondary database configuration to point to the primary database

First check the state of the primary library:

show master status;
Copy the code

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

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;
Copy the code

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;
Copy the code

8 Start the slave library and test it

Start slave/start replica (8.0.22+) to start the replication function of the slave library:

start slave
# or
start replica
Copy the code

Use after startup is complete

show slave stauts\G
Copy the code

To view slave library status:

If Slave_IO_Running keeps Connecting, the possible cause is:

  • The primary library address, port, username, password, etc. configured in the secondary library is incorrect
  • SSLConfiguration error, such as using the wrongclient-key.pem
  • Firewall problem

Check logs by yourself. The log location can pass

show variables like 'log_error'
Copy the code

Look at it.

When there is no problem, try to insert data into the main library:

use test;
insert into user values('111'.1);
Copy the code

In the library can be queried:

use test;
select * from user;
Copy the code

9 binlogThe encryption

Since 8.0.14, MySQL has provided encryption for binlogs. By default, binlogs are not encrypted, and encryption is required using keyring plugins or components:

The implementation steps are as follows:

  • The installationkeyring_fileThe plug-in
  • Modify the configuration
  • test

9.1 Installing Plug-ins

MySQL provides the installation of the following plug-ins:

Since you are using the Community edition, the community edition only supports the Keyring_file plug-in, for example.

The master and slave libraries modify the configuration files as follows:

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

MySQL > restart MySQL

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

You need to be in the ACTIVE state to be successful.

9.2 Modifying Configurations

Binlog encryption is controlled by a system variable, binlog_encryption, which needs to be manually enabled:

set global binlog_encryption=ON;
set persist binlog_encryption=ON;
Copy the code

Viewing logs after this function is enabled:

show binary logs;
Copy the code

You can see the encrypted binlog:

The previously unencrypted binlog can be manually migrated and deleted.

After encrypting binlog, there is no need to modify the configuration of the master/slave replication. The master/slave replication still takes effect, as shown below:

The primary library inserts a user from the library and can still select to.

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