安装内部PKI并强制使用TLS访问和MySQL的主从复制
安装内部PKI
1、在node-1（主机）上创建CA
- 默认的文件是
/yxy/下自己建立的
yxy/
├── app/
├── backup/
├── cache/
├── config/ # 统一管理配置文件，比如mysql 就再下面创建 mysql/my.cnf
├── data/ # 这个是用来放启动数据文件,比如 mysql 就是 mysql-run/mysql.sock
├── home/
├── log/ # 用来放日志的 如： mysql/yxy-mysql-error.log yxy是我自定义的用户组yxy的mysql用户
├── package/ # 这个是用来方从 windwos上传的离线安装包
├── run/
├── scripts/
├── seripts/ # 注意：这个目录名可能是 scripts 的拼写错误
├── server/ # 属主是 root，属组是 yxy
└── tmp/
install -d -o root -g root -m 0700 /yxy/backup/mysql
install -d -o root -g root -m 0700 /yxy/config/mysql/tls
umask 077
openssl genrsa -out /yxy/backup/mysql/yxy-mysql-ca-key.pem 4096
openssl req -x509 -new -key /yxy/backup/mysql/yxy-mysql-ca-key.pem -sha256 -days 3650 -out /yxy/config/mysql/tls/ca.pem -subj '/CN=YXY-MYSQL-Teaching-CA'
chmod 0600 /yxy/backup/mysql/yxy-mysql-ca-key.pem
chown root:yxy /yxy/config/mysql/tls/ca.pem
chmod 0640 /yxy/config/mysql/tls/ca.pem
openssl x509 -in /yxy/config/mysql/tls/ca.pem -noout -subject -issuer -dates
sha256sum /yxy/config/mysql/tls/ca.pem
在node-1 生成并签发服务器证书
umask 077
openssl genrsa \
-out /yxy/config/mysql/tls/server-key.pem \
2048
openssl req -new \
-key /yxy/config/mysql/tls/server-key.pem \
-out /tmp/yxy-node-1-mysql.csr \
-subj '/CN=yxy-node-1'
openssl x509 -req \
-in /tmp/yxy-node-1-mysql.csr \
-CA /yxy/config/mysql/tls/ca.pem \
-CAkey /yxy/backup/mysql/yxy-mysql-ca-key.pem \
-CAcreateserial \
-out /yxy/config/mysql/tls/server-cert.pem \
-days 825 \
-sha256 \
-extfile <(printf '%s\n' \
'subjectAltName=DNS:yxy-node-1,IP:192.168.109.82' \ # 这个地方修改为自己的 虚拟机的 ip地址
'extendedKeyUsage=serverAuth')
chown root:yxy \
/yxy/config/mysql/tls/server-key.pem \
/yxy/config/mysql/tls/server-cert.pem
chmod 0640 \
/yxy/config/mysql/tls/server-key.pem \
/yxy/config/mysql/tls/server-cert.pem
restorecon -RFv /yxy/config/mysql/tls
openssl verify \
-CAfile /yxy/config/mysql/tls/ca.pem \
/yxy/config/mysql/tls/server-cert.pem
在 node-2 (副机)上生成私钥和CSR
install -d -o root -g yxy -m 0750 /yxy/config/mysql/tls
umask 077
openssl genrsa \
-out /yxy/config/mysql/tls/server-key.pem \
2048
openssl req -new \
-key /yxy/config/mysql/tls/server-key.pem \
-out /tmp/yxy-node-2-mysql.csr \
-subj '/CN=yxy-node-2'
在node-3(另外一台副机)上生成 私钥和CSR
install -d -o root -g yxy -m 0750 /yxy/config/mysql/tls
umask 077
openssl genrsa \
-out /yxy/config/mysql/tls/server-key.pem \
2048
openssl req -new \
-key /yxy/config/mysql/tls/server-key.pem \
-out /tmp/yxy-node-3-mysql.csr \
-subj '/CN=yxy-node-3'
在Windows中管理中转两份CSR
New-Item -ItemType Directory -Force 'D:\yxy\project\lnmj\.tmp-mysql-pki'
scp root@192.168.109.83:/tmp/yxy-node-2-mysql.csr 'D:\yxy\project\lnmj\.tmp-mysql-pki\yxy-node-2-mysql.csr'
scp root@192.168.109.84:/tmp/yxy-node-3-mysql.csr 'D:\yxy\project\lnmj\.tmp-mysql-pki\yxy-node-3-mysql.csr'
scp 'D:\yxy\project\lnmj\.tmp-mysql-pki\yxy-node-2-mysql.csr' root@192.168.109.82:/tmp/yxy-node-2-mysql.csr
scp 'D:\yxy\project\lnmj\.tmp-mysql-pki\yxy-node-3-mysql.csr' root@192.168.109.82:/tmp/yxy-node-3-mysql.csr
在node-1 中对 node-2 和 node-3的证书分别签发，然后传给 windows ，从windows 中传给 node-2 和 node-3
test -s /tmp/yxy-node-2-mysql.csr
openssl x509 -req \
-in /tmp/yxy-node-2-mysql.csr \
-CA /yxy/config/mysql/tls/ca.pem \
-CAkey /yxy/backup/mysql/yxy-mysql-ca-key.pem \
-CAcreateserial \
-out /tmp/yxy-node-2-server-cert.pem \
-days 825 \
-sha256 \
-extfile <(printf '%s\n' \
'subjectAltName=DNS:yxy-node-2,IP:192.168.17.12' \
'extendedKeyUsage=serverAuth')
openssl verify \
-CAfile /yxy/config/mysql/tls/ca.pem \
/tmp/yxy-node-2-server-cert.pem
test -s /tmp/yxy-node-3-mysql.csr
openssl x509 -req \
-in /tmp/yxy-node-3-mysql.csr \
-CA /yxy/config/mysql/tls/ca.pem \
-CAkey /yxy/backup/mysql/yxy-mysql-ca-key.pem \
-CAcreateserial \
-out /tmp/yxy-node-3-server-cert.pem \
-days 825 \
-sha256 \
-extfile <(printf '%s\n' \
'subjectAltName=DNS:yxy-node-3,IP:192.168.17.13' \
'extendedKeyUsage=serverAuth')
openssl verify \
-CAfile /yxy/config/mysql/tls/ca.pem \
/tmp/yxy-node-3-server-cert.pem
[!Note]
传递的指令和之前从node-2中传递给windows是一样的，只是ip地址和传递对象换了一下，以下就不再过多书写冗余的指令。
依次在 node-2 和 node-3 中安装证书
test -s /tmp/yxy-node-2-server-cert.pem
test -s /tmp/yxy-mysql-ca.pem
test -s /yxy/config/mysql/tls/server-key.pem
install -o root -g yxy -m 0640 \
/tmp/yxy-node-2-server-cert.pem \
/yxy/config/mysql/tls/server-cert.pem
install -o root -g yxy -m 0640 \
/tmp/yxy-mysql-ca.pem \
/yxy/config/mysql/tls/ca.pem
chown root:yxy /yxy/config/mysql/tls/server-key.pem
chmod 0640 /yxy/config/mysql/tls/server-key.pem
restorecon -RFv /yxy/config/mysql/tls
openssl verify \
-CAfile /yxy/config/mysql/tls/ca.pem \
/yxy/config/mysql/tls/server-cert.pem
sha256sum /yxy/config/mysql/tls/ca.pem
需要在屏幕上显示的
OK即可代表安装成功
确认每个节点的证书与私钥是匹配的
openssl x509 \
-in /yxy/config/mysql/tls/server-cert.pem \
-pubkey -noout | sha256sum
openssl pkey \
-in /yxy/config/mysql/tls/server-key.pem \
-pubout | sha256sum
输出的结果是一样的就是认为是匹配的
最后将证书的路径写入MySQL TLS配置中，默认是在/etc/my.cnf,本人的是改为了/yxy/config/mysql/my.cnf
sed -i \
-e '/^ssl-ca=/d' \
-e '/^ssl-cert=/d' \
-e '/^ssl-key=/d' \
-e '/^require_secure_transport=/d' \
/yxy/config/mysql/my.cnf
sed -i '/^\[mysqld\]$/a\
ssl-ca=/yxy/config/mysql/tls/ca.pem\
ssl-cert=/yxy/config/mysql/tls/server-cert.pem\
ssl-key=/yxy/config/mysql/tls/server-key.pem\
require_secure_transport=ON' \
/yxy/config/mysql/my.cnf
chown root:yxy /yxy/config/mysql/my.cnf
chmod 0640 /yxy/config/mysql/my.cnf
grep -nE \
'^(ssl-ca|ssl-cert|ssl-key|require_secure_transport)=' \
/yxy/config/mysql/my.cnf
验证配置并重启MySQL
/yxy/server/mysql/bin/mysqld \
--defaults-file=/yxy/config/mysql/my.cnf \
--validate-config
systemctl restart yxy-mysql
systemctl --no-pager --full status yxy-mysql
验证 MySQL已加载了TLS
本人用的16位随机密码当作
MySQL密码，同时也是模拟使用安全性来完成
# wI4dwbXCLpzRe8Ha
/yxy/server/mysql/bin/mysql \
--protocol=socket \
--socket=/yxy/data/mysql-run/mysql.sock \
-uroot -p \
-e "SHOW VARIABLES WHERE Variable_name IN \
('require_secure_transport','ssl_ca','ssl_cert','ssl_key','tls_version');"
tail -n 100 /yxy/log/mysql/yxy-mysql-error.log | \
grep -iE 'ssl|tls|certificate|private key|unable|error' || true
MySQL的主从复制
最后的结果如下
浙公网安备 33010602011771号