MySQL/MariaDB数据库的复制加密
MySQL/MariaDB数据库的复制加密
作者：尹正杰
版权声明：原创作品，谢绝转载！否则将追究法律责任。
一.MySQL的安全问题
1>.基于SSL复制
在默认的主从复制过程或远程连接到MySQL/MariaDB所有的链接通信中的数据都是明文的，外网里访问数据或则复制，存在安全隐患。通过SSL/TLS加密的方式进行复制的方法，来进一步提高数据的安全性
2>. 配置实现步骤概述
主服务器开启SSL：[mysqld] 加一行ssl 主服务器配置证书和私钥；并且创建一个要求必须使用SSL连接的复制账号 从服务器使用CHANGER MASTER TO 命令时指明ssl相关选项 博主推荐阅读: https://mariadb.com/kb/en/library/replication-with-secure-connections/
二.复制加密实战案例
1>.主服务器配置证书和私钥
[root@node102.yinzhengjie.org.cn ~]# mkdir /etc/my.cnf.d/ssl [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# (umask 066;openssl genrsa 2048 > /etc/my.cnf.d/ssl/cakey.pem) #创建私钥文件 Generating RSA private key, 2048 bit long modulus ................................+++ ..+++ e is 65537 (0x10001) [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# ll /etc/my.cnf.d/ssl/ total 4 -rw------- 1 root root 1675 Nov 10 23:28 cakey.pem [root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# ll /etc/my.cnf.d/ssl/ total 4 -rw------- 1 root root 1675 Nov 10 23:28 cakey.pem [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# cd /etc/my.cnf.d/ssl/ [root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# [root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# openssl req -new -x509 -key cakey.pem -out cacert.pem -days 3650 #创建自签名证书 You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [XX]:CN State or Province Name (full name) []:beijing Locality Name (eg, city) [Default City]:beijing Organization Name (eg, company) [Default Company Ltd]:yinzhengjie.org.cn Organizational Unit Name (eg, section) []:devops Common Name (eg, your name or your server's hostname) []:ca.yinzhengjie.org.cn Email Address []: [root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# [root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# ll total 8 -rw-r--r-- 1 root root 1383 Nov 10 23:33 cacert.pem #通过私钥自签名的证书 -rw------- 1 root root 1675 Nov 10 23:28 cakey.pem #私钥 [root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# [root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]#
[root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# ll total 8 -rw-r--r-- 1 root root 1383 Nov 10 23:33 cacert.pem -rw------- 1 root root 1675 Nov 10 23:28 cakey.pem [root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# [root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# [root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# [root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# openssl req -newkey rsa:2048 -days 365 -nodes -keyout master.key > master.csr #为master节点申请证书及私钥 Generating a 2048 bit RSA private key ..................+++ ..........+++ writing new private key to 'master.key' ----- You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [XX]:CN State or Province Name (full name) []:beijing Locality Name (eg, city) [Default City]:beijing Organization Name (eg, company) [Default Company Ltd]:yinzhengjie.org.cn Organizational Unit Name (eg, section) []:devops Common Name (eg, your name or your server's hostname) []:node102.yinzhengjie.org.cn Email Address []: Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []: An optional company name []: [root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# [root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# ll total 16 -rw-r--r-- 1 root root 1383 Nov 10 23:33 cacert.pem -rw------- 1 root root 1675 Nov 10 23:28 cakey.pem -rw-r--r-- 1 root root 1045 Nov 10 23:39 master.csr -rw-r--r-- 1 root root 1704 Nov 10 23:39 master.key [root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# [root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]#
[root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# openssl req -newkey rsa:2048 -days 365 -nodes -keyout slave.key > slave.csr #为slave节点申请证书及私钥文件 Generating a 2048 bit RSA private key .................................+++ .+++ writing new private key to 'slave.key' ----- You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [XX]:CN State or Province Name (full name) []:beijing Locality Name (eg, city) [Default City]:beijing Organization Name (eg, company) [Default Company Ltd]:yinzhengjie.org.cn Organizational Unit Name (eg, section) []:devops Common Name (eg, your name or your server's hostname) []:node103.yinzhengjie.org.cn Email Address []: Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []: An optional company name []: [root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# [root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# ll total 24 -rw-r--r-- 1 root root 1383 Nov 10 23:33 cacert.pem -rw------- 1 root root 1675 Nov 10 23:28 cakey.pem -rw-r--r-- 1 root root 1045 Nov 10 23:39 master.csr -rw-r--r-- 1 root root 1704 Nov 10 23:39 master.key -rw-r--r-- 1 root root 1045 Nov 10 23:44 slave.csr -rw-r--r-- 1 root root 1704 Nov 10 23:44 slave.key [root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# [root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]#
[root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# ll total 24 -rw-r--r-- 1 root root 1383 Nov 10 23:33 cacert.pem -rw------- 1 root root 1675 Nov 10 23:28 cakey.pem -rw-r--r-- 1 root root 1045 Nov 10 23:39 master.csr -rw-r--r-- 1 root root 1704 Nov 10 23:39 master.key -rw-r--r-- 1 root root 1045 Nov 10 23:44 slave.csr -rw-r--r-- 1 root root 1704 Nov 10 23:44 slave.key [root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# [root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# openssl x509 -req -in master.csr -days 365 -CA cacert.pem -CAkey cakey.pem -set_serial 01 > master.crt #为master证书签名(颁发证书) Signature ok subject=/C=CN/ST=beijing/L=beijing/O=yinzhengjie.org.cn/OU=devops/CN=node102.yinzhengjie.org.cn Getting CA Private Key [root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# [root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# ll total 28 -rw-r--r-- 1 root root 1383 Nov 10 23:33 cacert.pem -rw------- 1 root root 1675 Nov 10 23:28 cakey.pem -rw-r--r-- 1 root root 1265 Nov 10 23:49 master.crt -rw-r--r-- 1 root root 1045 Nov 10 23:39 master.csr -rw-r--r-- 1 root root 1704 Nov 10 23:39 master.key -rw-r--r-- 1 root root 1045 Nov 10 23:44 slave.csr -rw-r--r-- 1 root root 1704 Nov 10 23:44 slave.key [root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# [root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]#
[root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# openssl x509 -in master.crt -noout -text #查看master颁发证书信息 Certificate: Data: Version: 1 (0x0) Serial Number: 1 (0x1) Signature Algorithm: sha256WithRSAEncryption Issuer: C=CN, ST=beijing, L=beijing, O=yinzhengjie.org.cn, OU=devops, CN=ca.yinzhengjie.org.cn Validity Not Before: Nov 10 15:49:10 2019 GMT Not After : Nov 9 15:49:10 2020 GMT Subject: C=CN, ST=beijing, L=beijing, O=yinzhengjie.org.cn, OU=devops, CN=node102.yinzhengjie.org.cn Subject Public Key Info: Public Key Algorithm: rsaEncryption Public-Key: (2048 bit) Modulus: 00:d0:13:c3:2d:c3:91:f7:fb:b6:91:86:d7:cf:6f: 09:5b:92:53:23:05:8d:c4:89:77:75:6f:ba:47:b4: 70:05:f9:17:d7:03:8c:23:a1:6f:5d:9b:ca:d9:d2: 17:85:bf:01:de:ee:bd:80:30:ed:b4:09:ab:d2:76: 0e:f2:d3:17:08:7e:26:25:38:40:28:e3:2c:d5:dc: 25:fa:92:f0:66:17:83:b7:b1:59:29:1d:27:a8:72: f4:ad:fd:91:bd:0a:f1:b1:c8:c0:d2:02:2f:fd:08: 1b:45:af:50:ff:1a:4a:c9:83:52:93:2f:2f:e0:5d: 84:51:5f:90:4b:6f:70:4e:6d:77:8c:5d:c3:96:4f: dc:47:5f:4e:62:1b:08:9c:f4:ab:ea:bb:32:65:1e: ce:08:ae:c3:0f:80:46:1d:42:09:a0:47:e6:6e:38: f4:91:11:77:8c:99:67:19:e4:ab:29:5f:30:c8:ea: ef:74:e4:54:16:6b:bf:df:b8:87:7e:ab:0f:ec:c3: 51:e0:0d:65:3d:d9:6a:e1:ff:ab:3a:72:9a:3b:57: b1:c9:6a:60:a5:ec:87:d9:6c:fe:35:bb:35:6a:6b: 6b:80:d5:68:4c:d2:74:0a:6e:4f:f9:24:9c:0e:57: 5a:da:0b:d4:74:dc:7d:7d:9f:f9:cc:4f:7e:df:06: 82:11 Exponent: 65537 (0x10001) Signature Algorithm: sha256WithRSAEncryption 75:28:94:d3:a5:bf:2a:21:7f:98:76:58:9c:68:3a:80:55:84: df:d4:52:ca:fb:d5:00:66:c4:9f:a9:95:82:93:3b:b7:cc:c6: 34:01:19:a6:c7:fc:ca:40:70:e6:c9:ac:aa:53:68:1d:5c:17: 46:c2:af:76:1b:a6:40:5e:b6:76:a7:c4:e8:2a:17:a3:d7:bf: 06:ad:48:f5:f0:81:6e:09:05:7e:47:49:8d:c4:4b:12:63:4d: 0b:f3:cf:38:52:3d:a3:30:3e:13:de:7e:67:65:fe:19:3c:dd: 78:40:d6:8b:4f:17:71:2d:e0:a6:43:73:b1:a5:27:5d:05:d7: 1f:41:2e:50:3c:47:9a:3a:12:f4:40:01:a4:40:24:c9:09:a6: 05:56:22:b6:18:01:4c:e5:65:c0:22:0b:73:7c:7b:bb:03:00: 9c:f2:fe:8c:59:d6:d7:f2:52:60:38:08:af:83:ad:0d:2d:b2: 94:50:6a:0a:c4:04:7c:9f:42:7b:17:4d:1f:1f:9f:b4:36:cb: 38:36:ae:cb:f3:8b:f7:7e:88:5d:de:81:69:42:a8:7f:fe:70: 47:9e:a3:a1:4e:ee:00:ae:cf:a6:29:be:57:1a:d8:84:84:ef: 01:0b:61:7e:2d:37:f5:2f:9b:9d:ac:8e:6a:34:7a:95:ca:5d: 37:a1:d7:48 [root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]#
[root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# ll total 28 -rw-r--r-- 1 root root 1383 Nov 10 23:33 cacert.pem -rw------- 1 root root 1675 Nov 10 23:28 cakey.pem -rw-r--r-- 1 root root 1265 Nov 10 23:49 master.crt -rw-r--r-- 1 root root 1045 Nov 10 23:39 master.csr -rw-r--r-- 1 root root 1704 Nov 10 23:39 master.key -rw-r--r-- 1 root root 1045 Nov 10 23:44 slave.csr -rw-r--r-- 1 root root 1704 Nov 10 23:44 slave.key [root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# [root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# openssl x509 -req -in slave.csr -days 365 -CA cacert.pem -CAkey cakey.pem -set_serial 02 > slave.crt #为slave证书签名(颁发证书) Signature ok subject=/C=CN/ST=beijing/L=beijing/O=yinzhengjie.org.cn/OU=devops/CN=node103.yinzhengjie.org.cn Getting CA Private Key [root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# [root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# ll total 32 -rw-r--r-- 1 root root 1383 Nov 10 23:33 cacert.pem -rw------- 1 root root 1675 Nov 10 23:28 cakey.pem -rw-r--r-- 1 root root 1265 Nov 10 23:49 master.crt -rw-r--r-- 1 root root 1045 Nov 10 23:39 master.csr -rw-r--r-- 1 root root 1704 Nov 10 23:39 master.key -rw-r--r-- 1 root root 1265 Nov 10 23:54 slave.crt -rw-r--r-- 1 root root 1045 Nov 10 23:44 slave.csr -rw-r--r-- 1 root root 1704 Nov 10 23:44 slave.key [root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# [root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]#
[root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# openssl x509 -in slave.crt -noout -text Certificate: Data: Version: 1 (0x0) Serial Number: 2 (0x2) Signature Algorithm: sha256WithRSAEncryption Issuer: C=CN, ST=beijing, L=beijing, O=yinzhengjie.org.cn, OU=devops, CN=ca.yinzhengjie.org.cn Validity Not Before: Nov 10 15:56:54 2019 GMT Not After : Nov 9 15:56:54 2020 GMT Subject: C=CN, ST=beijing, L=beijing, O=yinzhengjie.org.cn, OU=devops, CN=node103.yinzhengjie.org.cn Subject Public Key Info: Public Key Algorithm: rsaEncryption Public-Key: (2048 bit) Modulus: 00:b0:25:b3:56:f7:91:05:8e:ec:91:c2:21:4c:31: d9:2e:05:d6:dc:8b:aa:78:06:9a:31:70:e5:68:ea: 6b:3c:ba:82:50:84:11:e4:97:95:06:bc:38:bb:43: 67:bc:42:d7:99:cc:79:3c:af:21:c9:07:ff:af:79: 51:41:55:8b:ce:21:ec:49:c8:6c:fa:7b:91:58:85: 95:17:bb:3e:4a:42:96:c0:7b:28:1f:87:00:fb:ab: 51:10:fe:7a:9d:e6:07:cc:d8:db:bb:b8:61:e3:e9: c5:ba:5e:87:9f:93:4f:3a:fb:ea:bb:d9:5a:c1:3e: 52:3a:fc:08:92:87:d1:38:b6:9e:e2:65:6a:6d:ef: af:f8:66:b7:4a:46:26:fa:f0:44:f0:ee:66:fd:43: 93:8f:d3:91:ca:12:e7:3e:60:6b:72:18:43:75:eb: d1:c6:ef:fc:08:00:f8:72:46:13:db:13:50:9b:ca: 6c:08:38:c4:02:2a:88:d6:08:9b:ef:78:24:95:a8: 75:29:25:d8:f2:2c:e5:ea:05:3b:56:3d:db:20:17: 5b:a9:00:00:f8:f1:da:fc:ec:2d:53:d0:86:44:dc: 2c:9c:84:ce:b9:9c:d2:73:38:21:4b:64:9d:e5:78: 62:f6:d7:bc:c7:5e:74:6e:11:cf:ad:90:f2:f8:b9: 20:f1 Exponent: 65537 (0x10001) Signature Algorithm: sha256WithRSAEncryption 35:b9:d6:09:11:7f:8f:52:05:32:e5:83:5a:48:b0:a8:38:01: bf:51:5f:a2:a7:c0:c5:96:bb:e4:d7:81:32:f1:79:1a:00:78: d0:6c:ab:1d:1f:48:5f:d7:35:7d:d3:9a:6a:39:35:0b:9d:af: dd:ad:cf:94:04:2d:7c:65:7c:49:cc:bb:45:13:72:85:d8:90: 13:11:f4:cf:69:7c:72:ef:b9:fa:a6:75:19:39:9c:e2:e4:15: a0:1c:98:ca:8e:e6:80:bd:04:43:2d:4e:17:6c:1e:0f:85:f5: 0c:11:f2:ad:ec:a5:f6:4f:a8:c2:4a:19:f1:45:47:09:a6:41: e0:66:d5:3e:46:dd:e7:14:2c:24:c8:0e:b2:83:25:45:4f:d3: 21:72:fb:b3:31:c1:d4:a8:ff:4d:67:f7:b3:1e:27:84:cf:c3: 3c:08:69:b5:98:f1:88:f1:5b:a7:f2:5b:49:e1:97:48:bc:34: 2e:bf:4d:52:7c:52:55:2c:ac:98:0a:5d:37:9c:3a:03:1d:4b: bc:4a:4c:20:7a:d6:85:3f:31:69:80:98:9d:6b:a6:7b:f4:01: fc:8c:da:64:0e:01:4b:55:26:2c:46:9e:0c:5e:05:66:a1:1b: 65:17:5f:25:00:6f:17:69:2b:1a:e0:e9:df:0b:2e:f2:f1:dd: e7:85:9e:4f [root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]#
[root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# ll total 32 -rw-r--r-- 1 root root 1383 Nov 10 23:33 cacert.pem -rw------- 1 root root 1675 Nov 10 23:28 cakey.pem -rw-r--r-- 1 root root 1265 Nov 10 23:49 master.crt -rw-r--r-- 1 root root 1045 Nov 10 23:39 master.csr -rw-r--r-- 1 root root 1704 Nov 10 23:39 master.key -rw-r--r-- 1 root root 1265 Nov 10 23:56 slave.crt -rw-r--r-- 1 root root 1045 Nov 10 23:44 slave.csr -rw-r--r-- 1 root root 1704 Nov 10 23:44 slave.key [root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# [root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# openssl verify -CAfile cacert.pem master.crt #验证master证书的有效性 master.crt: OK [root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# [root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# openssl verify -CAfile cacert.pem slave.crt #验证slave证书的有效性 slave.crt: OK [root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# [root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# openssl verify -CAfile cacert.pem slave.crt slave.crt #咱们也可以同时验证多个 slave.crt: OK slave.crt: OK [root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# [root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]#
[root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# ssh node103.yinzhengjie.org.cn root@node103.yinzhengjie.org.cn's password: Last failed login: Mon Nov 11 05:21:56 CST 2019 from 172.30.1.102 on ssh:notty There was 1 failed login attempt since the last successful login. Last login: Sun Nov 10 10:48:50 2019 from 172.30.1.254 [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]# mkdir /etc/my.cnf.d/ssl [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]# ll /etc/my.cnf.d/ssl/ total 0 [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]# exit logout Connection to node103.yinzhengjie.org.cn closed. [root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# [root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# scp cacert.pem slave.crt slave.key node103.yinzhengjie.org.cn:/etc/my.cnf.d/ssl/ root@node103.yinzhengjie.org.cn's password: cacert.pem 100% 1383 1.6MB/s 00:00 slave.crt 100% 1265 1.7MB/s 00:00 slave.key 100% 1704 2.3MB/s 00:00 [root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# [root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# ssh node103.yinzhengjie.org.cn root@node103.yinzhengjie.org.cn's password: Last login: Mon Nov 11 05:22:14 2019 from 172.30.1.102 [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]# ll /etc/my.cnf.d/ssl/ total 12 -rw-r--r-- 1 root root 1383 Nov 11 05:23 cacert.pem -rw-r--r-- 1 root root 1265 Nov 11 05:23 slave.crt -rw-r--r-- 1 root root 1704 Nov 11 05:23 slave.key [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]#
2>.master服务端配置
[root@node102.yinzhengjie.org.cn ~]# cat /etc/my.cnf [mysqld] server-id = 102 binlog_format = row log_bin = /data/mysql/logbin/master-102 ssl = 1 #启用SSL加密功能，该选项也可以不配置，因为只要我们配置了下面三项该功能也会自动开启哟~ ssl-ca = /etc/my.cnf.d/ssl/cacert.pem #指定CA证书文件 ssl-cert = /etc/my.cnf.d/ssl/master.crt #指定master证书文件 ssl-key = /etc/my.cnf.d/ssl/master.key #指定master的私钥 character-set-server = utf8mb4 default_storage_engine = InnoDB datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock [mysqld_safe] log-error = /var/log/mariadb/mariadb.log pid-file = /var/run/mariadb/mariadb.pid !includedir /etc/my.cnf.d [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# ll /var/lib/mysql/ total 0 [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# ll /data/mysql/logbin/ total 0 [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# systemctl start mariadb [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# ll /var/lib/mysql/ total 37852 -rw-rw---- 1 mysql mysql 16384 Nov 11 05:36 aria_log.00000001 -rw-rw---- 1 mysql mysql 52 Nov 11 05:36 aria_log_control -rw-rw---- 1 mysql mysql 18874368 Nov 11 05:36 ibdata1 -rw-rw---- 1 mysql mysql 5242880 Nov 11 05:36 ib_logfile0 -rw-rw---- 1 mysql mysql 5242880 Nov 11 05:36 ib_logfile1 drwx------ 2 mysql mysql 4096 Nov 11 05:36 mysql srwxrwxrwx 1 mysql mysql 0 Nov 11 05:36 mysql.sock drwx------ 2 mysql mysql 4096 Nov 11 05:36 performance_schema drwx------ 2 mysql mysql 6 Nov 11 05:36 test [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# ll /data/mysql/logbin/ total 940 -rw-rw---- 1 mysql mysql 26813 Nov 11 05:36 master-102.000001 -rw-rw---- 1 mysql mysql 921736 Nov 11 05:36 master-102.000002 -rw-rw---- 1 mysql mysql 245 Nov 11 05:36 master-102.000003 -rw-rw---- 1 mysql mysql 111 Nov 11 05:36 master-102.index [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 5 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> MariaDB [(none)]> SHOW MASTER LOGS; +-------------------+-----------+ | Log_name | File_size | +-------------------+-----------+ | master-102.000001 | 26813 | | master-102.000002 | 921736 | | master-102.000003 | 245 | +-------------------+-----------+ 3 rows in set (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> SELECT user,host FROM mysql.user; +------+----------------------------+ | user | host | +------+----------------------------+ | root | 127.0.0.1 | | root | ::1 | | | localhost | | root | localhost | | | node102.yinzhengjie.org.cn | | root | node102.yinzhengjie.org.cn | +------+----------------------------+ 6 rows in set (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO sslcopy@'172.30.1.10%' IDENTIFIED BY 'yinzhengjie' REQUIRE SSL; #创建基于SSL加密认证且具有复制权限的用户，用于主从复制 Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> SELECT user,host FROM mysql.user; +---------+----------------------------+ | user | host | +---------+----------------------------+ | root | 127.0.0.1 | | sslcopy | 172.30.1.10% | | root | ::1 | | | localhost | | root | localhost | | | node102.yinzhengjie.org.cn | | root | node102.yinzhengjie.org.cn | +---------+----------------------------+ 7 rows in set (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> QUIT Bye [root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 121 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> MariaDB [(none)]> SHOW MASTER LOGS; +-------------------+-----------+ | Log_name | File_size | +-------------------+-----------+ | master-102.000001 | 26813 | | master-102.000002 | 921736 | | master-102.000003 | 416 | +-------------------+-----------+ 3 rows in set (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> SHOW MASTER STATUS; #查看master节点当前二进制日志所在位置便于slave节点复制 +-------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-------------------+----------+--------------+------------------+ | master-102.000003 | 416 | | | +-------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> QUIT Bye [root@node102.yinzhengjie.org.cn ~]#
3>.slave服务端配置
[root@node103.yinzhengjie.org.cn ~]# mysql -usslcopy -pyinzhengjie -h node102.yinzhengjie.org.cn #尽管我们输出了正确的用户名称和密码依旧报错加密失败，因为默认是没有加密认证的，我们需要指定响应的证书文件进行认证操作。 ERROR 1045 (28000): Access denied for user 'sslcopy'@'node103.yinzhengjie.org.cn' (using password: YES) [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]# mysql -usslcopy -pyinzhengjie -h node102.yinzhengjie.org.cn --ssl-ca=/etc/my.cnf.d/ssl/cacert.pem --ssl-cert=/etc/my.cnf.d/ssl/slave.crt --ssl-key=/etc/my.cnf.d/ssl/slave.key #在上面输出正确用户名和密码的前提下基于SSL相关验证，发现登录成功啦~ Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 67 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> MariaDB [(none)]> STATUS #查看当前连接状态 -------------- mysql Ver 15.1 Distrib 5.5.64-MariaDB, for Linux (x86_64) using readline 5.1 Connection id: 67 Current database: Current user: sslcopy@node103.yinzhengjie.org.cn SSL: Cipher in use is DHE-RSA-AES256-GCM-SHA384 #很显然，这里是基于SSL加密认证的 Current pager: stdout Using outfile: '' Using delimiter: ; Server: MariaDB Server version: 5.5.64-MariaDB MariaDB Server Protocol version: 10 Connection: node102.yinzhengjie.org.cn via TCP/IP Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8 Conn. characterset: utf8 TCP port: 3306 Uptime: 10 min 4 sec Threads: 1 Questions: 13 Slow queries: 0 Opens: 0 Flush tables: 2 Open tables: 26 Queries per second avg: 0.021 -------------- MariaDB [(none)]> QUIT Bye [root@node103.yinzhengjie.org.cn ~]#
[root@node103.yinzhengjie.org.cn ~]# cat /etc/my.cnf [mysqld] server-id = 103 binlog_format = row read-only = on relay_log = relay-log-103 relay_log_index = relay-log-103.index character-set-server = utf8mb4 default_storage_engine = InnoDB datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock [mysqld_safe] log-error = /var/log/mariadb/mariadb.log pid-file = /var/run/mariadb/mariadb.pid !includedir /etc/my.cnf.d [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]#
[root@node103.yinzhengjie.org.cn ~]# ll /var/lib/mysql/ total 0 [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]# systemctl start mariadb [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]# ll /var/lib/mysql/ total 37852 -rw-rw---- 1 mysql mysql 16384 Nov 11 05:59 aria_log.00000001 -rw-rw---- 1 mysql mysql 52 Nov 11 05:59 aria_log_control -rw-rw---- 1 mysql mysql 18874368 Nov 11 05:59 ibdata1 -rw-rw---- 1 mysql mysql 5242880 Nov 11 05:59 ib_logfile0 -rw-rw---- 1 mysql mysql 5242880 Nov 11 05:59 ib_logfile1 drwx------ 2 mysql mysql 4096 Nov 11 05:59 mysql srwxrwxrwx 1 mysql mysql 0 Nov 11 05:59 mysql.sock drwx------ 2 mysql mysql 4096 Nov 11 05:59 performance_schema drwx------ 2 mysql mysql 6 Nov 11 05:59 test [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]#
[root@node103.yinzhengjie.org.cn ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 2 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='172.30.1.102', -> MASTER_USER='sslcopy', -> MASTER_PASSWORD='yinzhengjie', -> MASTER_LOG_FILE='master-102.000003', -> MASTER_LOG_POS=416, #注意，以下4项可以不配置,但是得在"/etc/my.cnf"配置文件中写上相应的信息,具体的格式可参考master节点的配置文件。 -> MASTER_SSL=1, -> MASTER_SSL_CA = '/etc/my.cnf.d/ssl/cacert.pem', -> MASTER_SSL_CERT = '/etc/my.cnf.d/ssl/slave.crt', -> MASTER_SSL_KEY = '/etc/my.cnf.d/ssl/slave.key'; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> MariaDB [(none)]> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Master_Host: 172.30.1.102 Master_User: sslcopy Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-102.000003 Read_Master_Log_Pos: 416 Relay_Log_File: relay-log-103.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: master-102.000003 Slave_IO_Running: No Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 416 Relay_Log_Space: 245 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: Yes Master_SSL_CA_File: /etc/my.cnf.d/ssl/cacert.pem Master_SSL_CA_Path: Master_SSL_Cert: /etc/my.cnf.d/ssl/slave.crt Master_SSL_Cipher: Master_SSL_Key: /etc/my.cnf.d/ssl/slave.key Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0 1 row in set (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> START SLAVE; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.30.1.102 Master_User: sslcopy Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-102.000003 Read_Master_Log_Pos: 416 Relay_Log_File: relay-log-103.000002 Relay_Log_Pos: 530 Relay_Master_Log_File: master-102.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 416 Relay_Log_Space: 822 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: Yes Master_SSL_CA_File: /etc/my.cnf.d/ssl/cacert.pem Master_SSL_CA_Path: Master_SSL_Cert: /etc/my.cnf.d/ssl/slave.crt Master_SSL_Cipher: Master_SSL_Key: /etc/my.cnf.d/ssl/slave.key Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 102 1 row in set (0.00 sec) MariaDB [(none)]>
4>.验证基于SSL配置的主从复制是否成功
[root@node102.yinzhengjie.org.cn ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 143 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> CREATE DATABASE db1; Query OK, 1 row affected (0.00 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)]> MariaDB [(none)]> CREATE DATABASE devops; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> USE devops Database changed MariaDB [devops]> MariaDB [devops]> CREATE TABLE students(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(30) NOT NULL,sex ENUM('boy','girl') DEFAULT 'boy' ,age TINYINT UNSIGNED,mobile CHAR(11),address VARCHAR(50));Query OK, 0 rows affected (0.00 sec) MariaDB [devops]> INSERT INTO students (name,age,mobile,address) VALUES ('Jason Yin',18,10000,'beijing'),('Jay','40',10086,'Taiwan'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [devops]> SELECT * FROM students; +----+-----------+------+------+--------+---------+ | id | name | sex | age | mobile | address | +----+-----------+------+------+--------+---------+ | 1 | Jason Yin | boy | 18 | 10000 | beijing | | 2 | Jay | boy | 40 | 10086 | Taiwan | +----+-----------+------+------+--------+---------+ 2 rows in set (0.00 sec) MariaDB [devops]> QUIT Bye [root@node102.yinzhengjie.org.cn ~]#
[root@node103.yinzhengjie.org.cn ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 5 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | db2 | | db3 | | devops | | mysql | | performance_schema | | test | +--------------------+ 8 rows in set (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> USE devops Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [devops]> MariaDB [devops]> SHOW TABLES; +------------------+ | Tables_in_devops | +------------------+ | students | +------------------+ 1 row in set (0.00 sec) MariaDB [devops]> MariaDB [devops]> SELECT * FROM students; +----+-----------+------+------+--------+---------+ | id | name | sex | age | mobile | address | +----+-----------+------+------+--------+---------+ | 1 | Jason Yin | boy | 18 | 10000 | beijing | | 2 | Jay | boy | 40 | 10086 | Taiwan | +----+-----------+------+------+--------+---------+ 2 rows in set (0.00 sec) MariaDB [devops]> MariaDB [devops]> QUIT Bye [root@node103.yinzhengjie.org.cn ~]#
本文来自博客园，作者：尹正杰，转载请注明原文链接：https://www.cnblogs.com/yinzhengjie/p/11832212.html，个人微信: "JasonYin2020"（添加时请备注来源及意图备注，有偿付费）
当你的才华还撑不起你的野心的时候，你就应该静下心来学习。当你的能力还驾驭不了你的目标的时候，你就应该沉下心来历练。问问自己，想要怎样的人生。
