MySQL master-slave replication encryption and binlog encryption

Time:2021-8-3

1 Overview

This article mainly describes how to use in the process of master-slave replicationSSLEncryption, andbinlogEncryption(MySQL 8.0.14+)Implementation of.

2 Environment

  • MySQL 8.0.25
  • Docker
  • One master and one slave

3 prepare the container

First pull the image and start the main library3306, from library3307, plus--sslParameter to turn onSSLFunctions of 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, enter the container and install itvimandnet-tools

apt install vim net-tools

Simultaneous entryMySQLPrepare 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 SSLConnection preparation

4.1 ensureSSLopen

First, you can enterMySQLensureSSLFunction on:

show variables like '%ssl%';

MySQL master-slave replication encryption and binlog encryption

4.2 generationCAAnd private key

By default, in/var/lib/mysqlThere are already generated certificate and private key files under. If you don’t want to use the default, you can regenerate them:

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

MySQL master-slave replication encryption and binlog encryption

The generated files include:

  • ca-key.pemCAPrivate key
  • ca.pem: self signedCAcertificate
  • 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

The actual use only needs to useca.pem, server private key certificate and client private key certificate. During master-slave replication, the master database acts as the server and the slave database acts as the clientca.pem/server-key.pem/server-client.pemYou only need to configure it in the main library, notca.pem/client-key.pem/client-cert.pemNeed to passscpTransfer to slave library.

4.3 modification authority

Permission errors can causeMySQLCannot be used normallySSLFunctions of:

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

4.4 modifying configuration files

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 master library. If you need to connect to yourself, you need to connect to the master libraryserver-key.pem/server-cert.pemCopy to the slave library and configure the slave library[mysqld]

[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 general and simple configurations of master-slave replication. The master library is only configuredidAnd libraries to be replicated:

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

The slave library is configured as follows:

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

After modification, restart the master and slave libraries.

6 user who creates master-slave replication

Users who create master-slave replication in the master library (specific)ipPlease useifconfigView:

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 slave library configuration to point to the master library

First, check the status of the main library:

show master status;

MySQL master-slave replication encryption and binlog encryption

holdFileandPositionRecord it and use it from the librarychange master to/change replication source to8.0.23+)Set main 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 slave library and test

have access tostart slave/start replica8.0.22+)To enable replication from the Library:

start slave
#Or
start replica

Use after startup

show slave stauts\G

To view slave library status:

MySQL master-slave replication encryption and binlog encryption

Need to display twoYesTo be successful, ifSlave_IO_RunningAlways showConnecting, possibly due to:

  • The master library address, port, user name, password, etc. configured in the slave library are incorrect
  • SSLConfiguration error, such as using the wrongclient-key.pem
  • Firewall problem

Please check the log by yourself. The log location can be accessed through

show variables like 'log_error'

see.

When there are no problems, try inserting data into the main library:

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

You can query from the Library:

use test;
select * from user;

MySQL master-slave replication encryption and binlog encryption

9 binlogEncryption of

from8.0.14Start,MySQLProvides access tobinlogBy default,binlogThere is no encryption. Encryption needs to be usedkeyringPlug in or component:

MySQL master-slave replication encryption and binlog encryption

The implementation steps are as follows:

  • installkeyring_fileplug-in unit
  • Modify configuration
  • test

9.1 installing plug-ins

MySQLProvides installation of the following plug-ins:

MySQL master-slave replication encryption and binlog encryption

Since the community version is used, the community version only supportskeyring_filePlug in, take this as an example.

The master library and slave library modify configuration files as follows:

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

After restart, enterMySQLsee:

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

MySQL master-slave replication encryption and binlog encryption

Need to be inACTIVEStatus, this is success.

9.2 modify configuration

binlogEncryption through a system variablebinlog_encryptionControl, manual opening required:

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

View log after opening:

show binary logs;

You can see that it is encryptedbinlog

MySQL master-slave replication encryption and binlog encryption

And there was no encryption beforebinlogData can be manually migrated and deleted.

Encryption completedbinlogThe configuration of master-slave replication does not need to be modified after replication. Master-slave replication is still effective, as shown in the following figure:

MySQL master-slave replication encryption and binlog encryption

The master library inserts a user, and the slave library can stillselectYes.

10 reference links