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 ;openssl genrsa > /etc/my.cnf.d/ssl/cakey.pem) #创建私钥文件
Generating RSA private key, bit long modulus
................................+++
..+++
e is (0x10001)
[root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# ll /etc/my.cnf.d/ssl/
total
-rw------- root root Nov : cakey.pem
[root@node102.yinzhengjie.org.cn ~]#
创建私钥文件
[root@node102.yinzhengjie.org.cn ~]# ll /etc/my.cnf.d/ssl/
total
-rw------- root root Nov : 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 #创建自签名证书
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 ( 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
-rw-r--r-- root root Nov : cacert.pem #通过私钥自签名的证书
-rw------- root root Nov : cakey.pem #私钥
[root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]#
[root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]#
通过私钥生成CA自签名的证书
[root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# ll
total
-rw-r--r-- root root Nov : cacert.pem
-rw------- root root Nov : 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: -days -nodes -keyout master.key > master.csr #为master节点申请证书及私钥
Generating a 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 ( 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
-rw-r--r-- root root Nov : cacert.pem
-rw------- root root Nov : cakey.pem
-rw-r--r-- root root Nov : master.csr
-rw-r--r-- root root Nov : master.key
[root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]#
[root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]#
为master节点申请证书及私钥
[root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# openssl req -newkey rsa: -days -nodes -keyout slave.key > slave.csr #为slave节点申请证书及私钥文件
Generating a 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 ( 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
-rw-r--r-- root root Nov : cacert.pem
-rw------- root root Nov : cakey.pem
-rw-r--r-- root root Nov : master.csr
-rw-r--r-- root root Nov : master.key
-rw-r--r-- root root Nov : slave.csr
-rw-r--r-- root root Nov : slave.key
[root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]#
[root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]#
为slave节点申请证书及私钥文件
[root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# ll
total
-rw-r--r-- root root Nov : cacert.pem
-rw------- root root Nov : cakey.pem
-rw-r--r-- root root Nov : master.csr
-rw-r--r-- root root Nov : master.key
-rw-r--r-- root root Nov : slave.csr
-rw-r--r-- root root Nov : 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 -CA cacert.pem -CAkey cakey.pem -set_serial > 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
-rw-r--r-- root root Nov : cacert.pem
-rw------- root root Nov : cakey.pem
-rw-r--r-- root root Nov : master.crt
-rw-r--r-- root root Nov : master.csr
-rw-r--r-- root root Nov : master.key
-rw-r--r-- root root Nov : slave.csr
-rw-r--r-- root root Nov : slave.key
[root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]#
[root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]#
为master证书签名(颁发证书)
[root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# openssl x509 -in master.crt -noout -text #查看master颁发证书信息
Certificate:
Data:
Version: (0x0)
Serial Number: (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 :: GMT
Not After : Nov :: 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: ( bit)
Modulus:
:d0::c3:2d:c3::f7:fb:b6:::d7:cf:6f:
:5b:::::8d:c4::::6f:ba::b4:
::f9::d7::8c::a1:6f:5d:9b:ca:d9:d2:
::bf::de:ee:bd:::ed:b4::ab:d2::
0e:f2:d3:::7e::::::e3:2c:d5:dc:
:fa::f0::::b7:b1:::1d::a8::
f4:ad:fd::bd:0a:f1:b1:c8:c0:d2::2f:fd::
1b::af::ff:1a:4a:c9::::2f:2f:e0:5d:
::5f::4b:6f::4e:6d::8c:5d:c3::4f:
dc::5f:4e::1b::9c:f4:ab:ea:bb:::1e:
ce::ae:c3:0f:::1d:::a0::e6:6e::
f4::::8c::::e4:ab::5f::c8:ea:
ef::e4:::6b:bf:df:b8::7e:ab:0f:ec:c3:
:e0:0d::3d:d9:6a:e1:ff:ab:3a::9a:3b::
b1:c9:6a::a5:ec::d9:6c:fe::bb::6a:6b:
6b::d5::4c:d2::0a:6e:4f:f9::9c:0e::
5a:da:0b:d4::dc:7d:7d:9f:f9:cc:4f:7e:df::
:
Exponent: (0x10001)
Signature Algorithm: sha256WithRSAEncryption
:::d3:a5:bf:2a::7f::::9c::3a::::
df:d4::ca:fb:d5:::c4:9f:a9::::3b:b7:cc:c6:
:::a6:c7:fc:ca:::e6:c9:ac:aa:::1d:5c::
:c2:af::1b:a6::5e:b6::a7:c4:e8:2a::a3:d7:bf:
:ad::f5:f0::6e:::7e:::8d:c4:4b:::4d:
0b:f3:cf:::3d:a3::3e::de:7e:::fe::3c:dd:
::d6:8b:4f:::2d:e0:a6:::b1:a5::5d::d7:
1f::2e::3c::9a:3a::f4:::a4:::c9::a6:
:::b6:::4c:e5::c0::0b::7c:7b:bb:::
9c:f2:fe:8c::d6:d7:f2:::::af::ad:0d:2d:b2:
::6a:0a:c4::7c:9f::7b::4d:1f:1f:9f:b4::cb:
::ae:cb:f3:8b:f7:7e::5d:de::::a8:7f:fe::
:9e:a3:a1:4e:ee::ae:cf:a6::be::1a:d8:::ef:
:0b::7e:2d::f5:2f:9b:9d:ac:8e:6a::7a::ca:5d:
:a1:d7:
[root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]#
查看master颁发证书信息
[root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# ll
total
-rw-r--r-- root root Nov : cacert.pem
-rw------- root root Nov : cakey.pem
-rw-r--r-- root root Nov : master.crt
-rw-r--r-- root root Nov : master.csr
-rw-r--r-- root root Nov : master.key
-rw-r--r-- root root Nov : slave.csr
-rw-r--r-- root root Nov : 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 -CA cacert.pem -CAkey cakey.pem -set_serial > 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
-rw-r--r-- root root Nov : cacert.pem
-rw------- root root Nov : cakey.pem
-rw-r--r-- root root Nov : master.crt
-rw-r--r-- root root Nov : master.csr
-rw-r--r-- root root Nov : master.key
-rw-r--r-- root root Nov : slave.crt
-rw-r--r-- root root Nov : slave.csr
-rw-r--r-- root root Nov : slave.key
[root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]#
[root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]#
为slave证书签名(颁发证书)
[root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# openssl x509 -in slave.crt -noout -text
Certificate:
Data:
Version: (0x0)
Serial Number: (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 :: GMT
Not After : Nov :: 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: ( bit)
Modulus:
:b0::b3::f7:::8e:ec::c2::4c::
d9:2e::d6:dc:8b:aa:::9a:::e5::ea:
6b:3c:ba:::::e4::::bc::bb::
:bc::d7::cc::3c:af::c9::ff:af::
:::8b:ce::ec::c8:6c:fa:7b::::
::bb:3e:4a:::c0:7b::1f:::fb:ab:
::fe:7a:9d:e6::cc:d8:db:bb:b8::e3:e9:
c5:ba:5e::9f::4f:3a:fb:ea:bb:d9:5a:c1:3e:
:3a:fc::::d1::b6:9e:e2::6a:6d:ef:
af:f8::b7:4a:::fa:f0::f0:ee::fd::
:8f:d3::ca::e7:3e::6b:::::eb:
d1:c6:ef:fc:::f8::::db:::9b:ca:
6c:::c4::2a::d6::9b:ef::::a8:
:::d8:f2:2c:e5:ea::3b::3d:db:::
5b:a9:::f8:f1:da:fc:ec:2d::d0:::dc:
2c:9c::ce:b9:9c:d2::::4b::9d:e5::
:f6:d7:bc:c7:5e::6e::cf:ad::f2:f8:b9:
:f1
Exponent: (0x10001)
Signature Algorithm: sha256WithRSAEncryption
:b9:d6:::7f:8f::::e5::5a::b0:a8:::
bf::5f:a2:a7:c0:c5::bb:e4:d7:::f1::1a:::
d0:6c:ab:1d:1f::5f:d7::7d:d3:9a:6a:::0b:9d:af:
dd:ad:cf:::2d:7c::7c::cc:bb:::::d8::
::f4:cf::7c::ef:b9:fa:a6::::9c:e2:e4::
a0:1c::ca:8e:e6::bd:::2d:4e::6c:1e:0f::f5:
0c::f2:ad:ec:a5:f6:4f:a8:c2:4a::f1::::a6::
e0::d5:3e::dd:e7::2c::c8:0e:b2::::4f:d3:
::fb:b3::c1:d4:a8:ff:4d::f7:b3:1e:::cf:c3:
3c:::b5::f1::f1:5b:a7:f2:5b::e1:::bc::
2e:bf:4d::7c:::2c:ac::0a:5d::9c:3a::1d:4b:
bc:4a:4c::7a:d6::3f:::::9d:6b:a6:7b:f4::
fc:8c:da::0e::4b:::2c::9e:0c:5e:::a1:1b:
::5f:::6f:::2b:1a:e0:e9:df:0b:2e:f2:f1:dd:
e7::9e:4f
[root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]#
查看slave颁发证书信息
[root@node102.yinzhengjie.org.cn /etc/my.cnf.d/ssl]# ll
total
-rw-r--r-- root root Nov : cacert.pem
-rw------- root root Nov : cakey.pem
-rw-r--r-- root root Nov : master.crt
-rw-r--r-- root root Nov : master.csr
-rw-r--r-- root root Nov : master.key
-rw-r--r-- root root Nov : slave.crt
-rw-r--r-- root root Nov : slave.csr
-rw-r--r-- root root Nov : 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]#
验证master与slave证书的有效性
[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 :: CST from 172.30.1.102 on ssh:notty
There was failed login attempt since the last successful login.
Last login: Sun Nov :: 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
[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 % .6MB/s :
slave.crt % .7MB/s :
slave.key % .3MB/s :
[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 :: from 172.30.1.102
[root@node103.yinzhengjie.org.cn ~]#
[root@node103.yinzhengjie.org.cn ~]# ll /etc/my.cnf.d/ssl/
total
-rw-r--r-- root root Nov : cacert.pem
-rw-r--r-- root root Nov : slave.crt
-rw-r--r-- root root Nov : slave.key
[root@node103.yinzhengjie.org.cn ~]#
[root@node103.yinzhengjie.org.cn ~]#
将master生成的slave证书文件拷贝至slave节点指定位置
2>.master服务端配置
[root@node102.yinzhengjie.org.cn ~]# cat /etc/my.cnf
[mysqld]
server-id =
binlog_format = row
log_bin = /data/mysql/logbin/master-
ssl = #启用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 ~]# cat /etc/my.cnf
[root@node102.yinzhengjie.org.cn ~]# ll /var/lib/mysql/
total
[root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# ll /data/mysql/logbin/
total
[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
-rw-rw---- mysql mysql Nov : aria_log.
-rw-rw---- mysql mysql Nov : aria_log_control
-rw-rw---- mysql mysql Nov : ibdata1
-rw-rw---- mysql mysql Nov : ib_logfile0
-rw-rw---- mysql mysql Nov : ib_logfile1
drwx------ mysql mysql Nov : mysql
srwxrwxrwx mysql mysql Nov : mysql.sock
drwx------ mysql mysql Nov : performance_schema
drwx------ mysql mysql Nov : test
[root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# ll /data/mysql/logbin/
total
-rw-rw---- mysql mysql Nov : master-102.000001
-rw-rw---- mysql mysql Nov : master-102.000002
-rw-rw---- mysql mysql Nov : master-102.000003
-rw-rw---- mysql mysql Nov : master-.index
[root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# systemctl start mariadb
[root@node102.yinzhengjie.org.cn ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , 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 | |
| master-102.000002 | |
| master-102.000003 | |
+-------------------+-----------+
rows in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SELECT user,host FROM mysql.user;
+------+----------------------------+
| user | host |
+------+----------------------------+
| root | 127.0.0.1 |
| root | :: |
| | localhost |
| root | localhost |
| | node102.yinzhengjie.org.cn |
| root | node102.yinzhengjie.org.cn |
+------+----------------------------+
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, 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 | :: |
| | localhost |
| root | localhost |
| | node102.yinzhengjie.org.cn |
| root | node102.yinzhengjie.org.cn |
+---------+----------------------------+
rows in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> QUIT
Bye
[root@node102.yinzhengjie.org.cn ~]#
创建基于SSL加密认证且具有复制权限的用户，用于主从复制
[root@node102.yinzhengjie.org.cn ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , 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 | |
| master-102.000002 | |
| master-102.000003 | |
+-------------------+-----------+
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 | | | |
+-------------------+----------+--------------+------------------+
row in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> QUIT
Bye
[root@node102.yinzhengjie.org.cn ~]#
MariaDB [(none)]> SHOW MASTER STATUS; #查看master节点当前二进制日志所在位置便于slave节点复制
3>.slave服务端配置
[root@node103.yinzhengjie.org.cn ~]# mysql -usslcopy -pyinzhengjie -h node102.yinzhengjie.org.cn #尽管我们输出了正确的用户名称和密码依旧报错加密失败，因为默认是没有加密认证的，我们需要指定响应的证书文件进行认证操作。
ERROR (): 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
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , 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.-MariaDB, for Linux (x86_64) using readline 5.1 Connection id:
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.-MariaDB MariaDB Server
Protocol version:
Connection: node102.yinzhengjie.org.cn via TCP/IP
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8
Conn. characterset: utf8
TCP port:
Uptime: min sec Threads: Questions: Slow queries: Opens: Flush tables: Open tables: Queries per second avg: 0.021
-------------- MariaDB [(none)]> QUIT
Bye
[root@node103.yinzhengjie.org.cn ~]#
在slave节点验证基于SSL加密认证的复制用户是否可以正常使用
[root@node103.yinzhengjie.org.cn ~]# cat /etc/my.cnf
[mysqld]
server-id =
binlog_format = row
read-only = on
relay_log = relay-log-
relay_log_index = relay-log-.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 ~]# cat /etc/my.cnf
[root@node103.yinzhengjie.org.cn ~]# ll /var/lib/mysql/
total
[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
-rw-rw---- mysql mysql Nov : aria_log.
-rw-rw---- mysql mysql Nov : aria_log_control
-rw-rw---- mysql mysql Nov : ibdata1
-rw-rw---- mysql mysql Nov : ib_logfile0
-rw-rw---- mysql mysql Nov : ib_logfile1
drwx------ mysql mysql Nov : mysql
srwxrwxrwx mysql mysql Nov : mysql.sock
drwx------ mysql mysql Nov : performance_schema
drwx------ mysql mysql Nov : test
[root@node103.yinzhengjie.org.cn ~]#
[root@node103.yinzhengjie.org.cn ~]#
[root@node103.yinzhengjie.org.cn ~]# systemctl start mariadb
[root@node103.yinzhengjie.org.cn ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , 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=, #注意，以下4项可以不配置,但是得在"/etc/my.cnf"配置文件中写上相应的信息,具体的格式可参考master节点的配置文件。
-> MASTER_SSL=,
-> 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, rows affected (0.01 sec) MariaDB [(none)]>
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** . row ***************************
Slave_IO_State:
Master_Host: 172.30.1.102
Master_User: sslcopy
Master_Port:
Connect_Retry:
Master_Log_File: master-102.000003
Read_Master_Log_Pos:
Relay_Log_File: relay-log-103.000001
Relay_Log_Pos:
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:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: None
Until_Log_File:
Until_Log_Pos:
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:
Last_IO_Error:
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
row in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> START SLAVE;
Query OK, rows affected (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** . row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.30.1.102
Master_User: sslcopy
Master_Port:
Connect_Retry:
Master_Log_File: master-102.000003
Read_Master_Log_Pos:
Relay_Log_File: relay-log-103.000002
Relay_Log_Pos:
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:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: None
Until_Log_File:
Until_Log_Pos:
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:
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno:
Last_IO_Error:
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
row in set (0.00 sec) MariaDB [(none)]>
配置slave节点与master进行数据同步详细步骤戳这里
4>.验证基于SSL配置的主从复制是否成功
[root@node102.yinzhengjie.org.cn ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , 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 |
+--------------------+
rows in set (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> CREATE DATABASE db1;
Query OK, row affected (0.00 sec) MariaDB [(none)]> CREATE DATABASE db2;
Query OK, row affected (0.00 sec) MariaDB [(none)]> CREATE DATABASE db3;
Query OK, row affected (0.00 sec) MariaDB [(none)]>
MariaDB [(none)]> CREATE DATABASE devops;
Query OK, 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() NOT NULL,sex ENUM('boy','girl') DEFAULT 'boy'
,age TINYINT UNSIGNED,mobile CHAR(),address VARCHAR());Query OK, rows affected (0.00 sec) MariaDB [devops]> INSERT INTO students (name,age,mobile,address) VALUES ('Jason Yin',,,'beijing'),('Jay','',,'*');
Query OK, rows affected (0.00 sec)
Records: Duplicates: Warnings: MariaDB [devops]> SELECT * FROM students;
+----+-----------+------+------+--------+---------+
| id | name | sex | age | mobile | address |
+----+-----------+------+------+--------+---------+
| | Jason Yin | boy | | | beijing |
| | Jay | boy | | | * |
+----+-----------+------+------+--------+---------+
rows in set (0.00 sec) MariaDB [devops]> QUIT
Bye
[root@node102.yinzhengjie.org.cn ~]#
master节点创建多个测试数据库
[root@node103.yinzhengjie.org.cn ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is
Server version: 5.5.-MariaDB MariaDB Server Copyright (c) , , 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 |
+--------------------+
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 |
+------------------+
row in set (0.00 sec) MariaDB [devops]>
MariaDB [devops]> SELECT * FROM students;
+----+-----------+------+------+--------+---------+
| id | name | sex | age | mobile | address |
+----+-----------+------+------+--------+---------+
| | Jason Yin | boy | | | beijing |
| | Jay | boy | | | * |
+----+-----------+------+------+--------+---------+
rows in set (0.00 sec) MariaDB [devops]>
MariaDB [devops]> QUIT
Bye
[root@node103.yinzhengjie.org.cn ~]#
slave节点发现数据同步成功