MySQL master-slave replication encryption and binlog encryption implementation

氷泠
中文

1 Overview

SSL for encryption in the process of master-slave replication, and the binlog encryption ( MySQL 8.0.14+ ).

2 Environment

  • MySQL 8.0.25
  • Docker
  • One master and one follower

3 Prepare the container

First pull the image and start it, the main library 3306 , the slave library 3307 , you also need to add the --ssl parameter to enable the SSL encrypted connection function:

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, enter the container and install vim and net-tools :

apt install vim net-tools

At the same time, enter MySQL 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 turned on

First, you can enter MySQL ensure that the SSL function is turned on:

show variables like '%ssl%';

在这里插入图片描述

4.2 Generate CA and private key

By default, /var/lib/mysql . If you don’t 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 when the client connects
  • client-cert.pem : The certificate used when the client connects
  • 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

In actual use, only ca.pem , server private key certificate, and client private key certificate are needed. When master-slave copying, the master library acts as the server and the slave library acts as the client. Therefore, ca.pem / server-key.pem / server-client.pem only needs to be configured in the main library, and ca.pem / client-key.pem / client-cert.pem need to be scp to the slave library through 060def1b888403.

4.3 Modify permissions

Permissions error causes MySQL not work properly SSL features:

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

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 main library. If you need to connect to yourself, you need to server-key.pem / server-cert.pem main library to the slave library, and configure the [mysqld] 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 simple configurations for master-slave replication. The master library is only configured with id and the libraries that need to be replicated:

[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 main library and the slave library.

6 Create a user for master-slave replication

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

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 main library

First check the status of the main library:

show master status;

在这里插入图片描述

File and Position 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 copy function from the library:

start slave
# 或
start replica

Use after startup

show slave stauts\G

View the status of the slave library:

在这里插入图片描述

You need to display two Yes be considered 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 configuration error, such as using the wrong client-key.pem
  • Firewall problem

Please check the log by yourself, the location of the log can be passed

show variables like 'log_error'

Check it out.

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

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

You can query from the library:

use test;
select * from user;

在这里插入图片描述

9 binlog encryption

Starting from 8.0.14 , MySQL provides the binlog . By default, binlog is not encrypted. Encryption requires the use of keyring plug-ins or components:

在这里插入图片描述

The implementation steps are as follows:

  • Install keyring_file plugin
  • Change setting
  • test

9.1 Install plugin

MySQL provides the installation of the following plug-ins:

在这里插入图片描述

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

Modify the configuration files of the main library and the slave library as follows:

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

After restarting, enter MySQL view:

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

在这里插入图片描述

Need to be in the ACTIVE state, this is a success.

9.2 Modify configuration

binlog encryption system by means of a variable binlog_encryption control, need to manually open:

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

Check the log after opening:

show binary logs;

You can see that it is encrypted binlog :

在这里插入图片描述

The previously unencrypted binlog can be deleted after manual data migration.

After encrypting binlog , there is no need 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 inserts a user from the library and can still be select .

10 Reference link

阅读 807
288 声望
631 粉丝
0 条评论
你知道吗?

288 声望
631 粉丝
文章目录
宣传栏