MySQL 和 MariaDB 的 SSL 加密复制 ERROR 2026 (HY000): SSL connection error: error:00000001:lib(0):func(0):reason(1)

查看 SSL。

show variables like '%ssl%'; 

配置证书:

openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.pem

openssl req -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pem > server-req.pem
openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

openssl req -newkey rsa:2048 -days 1000 -nodes -keyout client-key.pem > client-req.pem
openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

需要注意的是MySQL和MariaDB不同:

MySQL在生成上面证书的时候需要输入大量用户信息,在CA上创建证书要注意所有的用户信息要和CA中的一致,从国家到部门都要相同,否则会造成证书无法使用,直接全部默认回车即可。如果用户信息一样则MariaDB会报错:

ERROR 2026 (HY000): SSL connection error: error:00000001:lib(0):func(0):reason(1)

MariaDB在生成上面证书的时候也是需要输入大量用户信息,和MySQL不同的是输入的用户信息服务端和客户端不能一致。即服务端输入和客户端输入不一样。https://mariadb.com/kb/en/mariadb/documentation/user-account-management/ssl-connections/mariadb-ssl-connection-issues/

验证

openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem

rsa 加密 key

openssl rsa -in server-key.pem -out server-key.pem
openssl rsa -in client-key.pem -out client-key.pem

必须 SSL 访问

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY '123456' REQUIRE SSL;

测试和查看:

mysql -urepltest -p -h192.168.3.5 --ssl-ca=certs/ca-cert.pem --ssl-key=certs/client-key.pem --ssl-cert=certs/client-cert.pem
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 69
Server version: 5.5.5-10.1.28-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.7.21, for osx10.13 (x86_64) using  EditLine wrapper

Connection id:		69
Current database:
Current user:		repltest@192.168.3.9
SSL:			Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.5.5-10.1.28-MariaDB MariaDB Server
Protocol version:	10
Connection:		192.168.3.5 via TCP/IP
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8
Conn.  characterset:	utf8
TCP port:		3306
Uptime:			1 hour 2 min 12 sec

Threads: 3  Questions: 150  Slow queries: 0  Opens: 20  Flush tables: 2  Open tables: 3  Queries per second avg: 0.040
--------------

mysql> exit
Bye

change master to

CHANGE MASTER TO MASTER_HOST='192.168.3.5', MASTER_USER='repl', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=999, MASTER_SSL=1, MASTER_SSL_CA = '/etc/mysql/ca-cert.pem', MASTER_SSL_CERT = '/etc/mysql/client-cert.pem', MASTER_SSL_KEY = '/etc/mysql/client-key.pem'
08-14 10:49