MARIADB Replication--encrypted replication

Background

It was also a whim to grab the bag to play, but did not expect to accidentally caught the mariadb copy of the plaintext message. So I went to the official MySQL document to see if there is any information about data encryption, after all, the copy process of the plaintext is still very insecure; the implementation of the encrypted copy is found in the MySQL reference document, and the implementation process begins.

Environment

CA	192.168.99.131
Master	192.168.99.135
Slave	192.168.99.150

CA: Used to issue certificates to master and slave nodes
Maser: As the primary Node database server
Slave: As slave node database server

The overall frame chart is as follows:

The experimental process CA generates certificates directly to master and slave

1. Self-signed CA

#找个目录生成 cd /etc/my.cnf.d/ssl/    openssl genrsa 2048 > cakey.pem    openssl req -new -x509 -key cakey.pem -out cacert.pem -days 3650

2. Generate the master node's private key and request file

    openssl req -newkey rsa:2048 -days 365 -nodes -keyout master.key > master.csr    openssl x509 -req  -in master.csr -CA cacert.pem -CAkey cakey.pem -set_serial 01 > master.crt  

3. Generate the private key and request file for the slave node

   openssl req -newkey rsa:2048 -days 365 -nodes -keyout slave.key > slave.csr    openssl x509 -req  -in slave.csr -CA cacert.pem -CAkey cakey.pem -set_serial 02 > slave.crt

4. Copy the files separately on the corresponding node

#master #cacert.pem master.crt master.key scp cacert.pem master.crt master.key 192.168.99.135:/etc/my.cnf.d/ssl/#slave#cacert.pem slave.crt slave.key scp cacert.pem slave.crt slave.key 192.168.99.150:/etc/my.cnf.d/ssl/

5. Configure the Master node

#编辑配置文件vim /etc/my.cnf.d/server.cnf [mysqld]#数据目录（看个人情况）datadir=/data/mysql#二进制日志文件路径及命名（个人情况）log_bin=/data/binlog/mysql-bin#库表独立文件（看个人爱好，一般推荐分开）innodb_file_per_table#指定编号server_id=1#开启ssl功能ssl# 证书配置信息ssl-ca=/etc/my.cnf.d/ssl/cacert.pemssl-cert=/etc/my.cnf.d/ssl/master.crtssl-key=/etc/my.cnf.d/ssl/master.key

Start the database after configuration is complete

    systemctl start mariadb

Access to the database, authorized backup account only allows encrypted backups to view current binary log information

mysqlMariaDB [(none)]> grant replication slave on *.* to [email protected]‘192.168.99.150‘ identified by ‘slave‘ require ssl;MariaDB [(none)]>show master status;

6. Configure the slave node

# 编辑配置文件vim /etc/my.cnf.d/server.cnf [mysqld]datadir=/data/mysqllog_bin=/data/binlog/mysql-bininnodb_file_per_table# 下面是重点# server-id唯一不能重复，其他配置与主类似server_id=2sslssl-ca=/etc/my.cnf.d/ssl/cacert.pemssl-cert=/etc/my.cnf.d/ssl/slave.crtssl-key=/etc/my.cnf.d/ssl/slave.key

Start the database

    systemctl start mariadb

Go to Database Settings Main Library point

mysql change master to \ master_host=‘192.168.99.135‘, master_user=‘slave‘, master_password=‘slave‘, master_log_file=‘mysql-bin.000003‘, master_log_pos=553, master_ssl=1;

7. Start the backup and view the backup status

MariaDB [(none)]> start slave;MariaDB [(none)]> show slave status;

8. Verifying that encryption is implemented

#在主数据库进行操作，同时进行抓包检测MariaDB [(none)]> create database db1;Query OK, 1 row affected (0.03 sec)MariaDB [(none)]> create database db2;Query OK, 1 row affected (0.00 sec)MariaDB [(none)]> create database db3;Query OK, 1 row affected (0.00 sec)MariaDB [(none)]> create database db4;Query OK, 1 row affected (0.00 sec)MariaDB [(none)]> create database db5;Query OK, 1 row affected (0.00 sec)# 最终验证发现加密后确实不再有明文的复制过程的传送，实现了数据的加密。

MARIADB Replication--encrypted replication