mysql基于SSL的加密复制

mysql基于SSL的复制加密

一、为什么要使用基于SSL复制？

​ 在默认的主从复制过程或远程连接到MySQL/MariaDB所有的链接通信中的数
据都是明文的，外网里访问数据或则复制，存在安全隐患。通过SSL/TLS加密的
方式进行复制的方法，来进一步提高数据的安全性
​
参看：https://mariadb.com/kb/en/library/replication-with-secure-connections/

二、搭建基于SSL的加密复制

环境要求

默认情况下ssl都是关闭的，要是have_ssl显示NO，则表示数据库不支持SSL，需要重新编译安装来支持它，显示为DISABLED表示支持SSL，但没有开启。

MariaDB [(none)]> show variables like '%ssl%';
+---------------------+------------------------------+
| Variable_name       | Value                        |
+---------------------+------------------------------+
| have_openssl        | NO                           |
| have_ssl            | DISABLED                     |

这样的数据库是不行滴！永远无法读取不到openssl 生成的key

SSL error: Unable to get private key from ‘/etc/my.cnf.d/ssl/master.key’

在master 上操作

1、 生成证书

1.1 创建证书目录

root@z1:/etc/my.cnf.d/ssl# mkdir /etc/my.cnf.d/ssl -p
root@z1:~# cd /etc/my.cnf.d/ssl

1.2 生成CA私钥

root@z1:/etc/my.cnf.d/ssl# openssl genrsa 2048 > cakey.pem

1.3 CA为自己颁发证书

root@z1:/etc/my.cnf.d/ssl# openssl req -new -x509 -key cakey.pem -out cacert.pem -days 3650

1.4 为master 生成私钥并且生成证书请求

root@z1:/etc/my.cnf.d/ssl# openssl req -newkey rsa:1024  -days 365 -nodes -keyout master.key > master.csr

1.5 为master颁发证书

root@z1:/etc/my.cnf.d/ssl# openssl x509 -req -in master.csr -CA cacert.pem -CAkey cakey.pem -set_serial 01 > master.crt

1.6 为slave 生成私钥并且生成证书请求

root@z1:/etc/my.cnf.d/ssl# openssl req -newkey rsa:1024  -nodes -keyout slave.key > slave.csr

1.7 为slave颁发证书

root@z1:/etc/my.cnf.d/ssl# openssl x509 -req -in slave.csr -CA cacert.pem -CAkey cakey.pem -set_serial 02 > slave.crt

1.8 为master 和slave 的key 加上读权限

root@z1:/etc/my.cnf.d/ssl# chmod +r master.key  slave.key 

2、修改配置文件

vim /etc/my.cnf

[mysqld]
log-bin
server_id=1
ssl-ca=/etc/mysql/cacert.pem
ssl-cert=/etc/mysql/master.crt
ssl-key=/etc/mysql/master.key

（因为my.cnf.d文件夹无法访问，所以才改了文件夹）

重启 mysql 这时候若遇到此类错误，则修改证书放在可访问的文件夹即可

2020-02-05T13:20:28.412799Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL_CTX_set_default_verify_paths failed

3. 创建必须使用ssl加密 才能登录的账号

MariaDB [(none)]> grant replication slave on *.* to 'r1'@'192.168.%'identified by '123' require  ssl;
Query OK, 0 rows affected (0.00 sec)

4.创建完全备份，把备份文件和slave相关的证书发送给slave

root@z1:~# mysqldump  -A -F --master-data=1 --single-transaction > all_bak.sql

root@z1:/etc/my.cnf.d/ssl# scp slave.crt  slave.key  cacert.pem   192.168.1.102:/etc/mysql

在 slave 上操作

1.导入数据库并且设置复制的配置信息

root@z2:~# mysql < all_bak.sql 

CHANGE MASTER TO
MASTER_HOST='192.168.1.101',
MASTER_USER='r1',
MASTER_PASSWORD='123',
MASTER_PORT=3306,
MASTER_LOG_FILE='z1-bin.000002', MASTER_LOG_POS=154,
MASTER_SSL=1,
MASTER_SSL_CA = '/etc/mysql/cacert.pem',
MASTER_SSL_CERT = '/etc/mysql/slave.crt',
MASTER_SSL_KEY = '/etc/mysql/slave.key';

2.测试复制账号能否登录

root@z2:/etc/mysql# mysql -h192.168.1.101 -ur1 -p123 --ssl-ca=cacert.pem --ssl-cert=slave.crt --ssl-key=slave.key

3.配置从服务器的证书信息

[mysqld]
server-id=2
read_only
ssl-ca=/etc/mysql/cacert.pem
ssl-cert=/mysql/slave.crt
ssl-key=/mysql/slave.key

验证

mysql> show variables like '%ssl%' ;
+---------------+-----------------------+
| Variable_name | Value                 |
+---------------+-----------------------+
| have_openssl  | YES                   |
| have_ssl      | YES                   |
| ssl_ca        | /etc/mysql/cacert.pem |
| ssl_capath    |                       |
| ssl_cert      | /etc/mysql/slave.crt  |
| ssl_cipher    |                       |
| ssl_crl       |                       |
| ssl_crlpath   |                       |
| ssl_key       | /etc/mysql/slave.key  |
+---------------+-----------------------+
9 rows in set (0.01 sec)

4. 开启复制线程

mysql> start slave ;

mysql> show slave status \G ;

             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes


  Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /etc/mysql/cacert.pem
           Master_SSL_CA_Path: 
              Master_SSL_Cert: /etc/mysql/slave.crt
            Master_SSL_Cipher: 
               Master_SSL_Key: /etc/mysql/slave.key