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'