如何配置MySQL SSL?
发布网友
发布时间：2022-04-25 00:04
共3个回答
懂视网
时间：2022-04-29 23:04
由于mysql的主从复制是明文传送的，但如果在生产环境中跨网络我们使用的主从还是明文传送的话，就保证不了数据的安全性，为了解决这一问题，我们需要加密进行传送，也就是基于SSL的加密方法进行传输数据。@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
修改配置文件
在Master上：
vi /etc/my.cnf
[mysqld]
log-bin=master-bin
binlog_format=mixed
server-id=1
ssl
/etc/init.d/mysqld restart
在Slave上：
vi /etc/my.cnf
[mysqld]
log-bin=slave-bin
binlog_format=mixed
server-id=10
ssl
/etc/init.d/mysqld restart
(1)将master服务器自己做成CA服务器
[root@nan86 tmp]# cd /etc/pki/CA/
[root@nan86 CA]# (umask 077;openssl genrsa -out private/cakey.pem 2048)
Generating RSA private key, 2048 bit long modulus
.......+++
........+++
e is 65537 (0x10001)
[root@nan86 CA]# openssl req -new -x509 -key private/cakey.pem -out cacert.pem -days 365
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) []:ShangHai
Locality Name (eg, city) [Default City]:PuDong
Organization Name (eg, company) [Default Company Ltd]:Allen
Organizational Unit Name (eg, section) []:Tech
Common Name (eg, your name or your server‘s hostname) []:master.allen.com
Email Address []:
[root@nan86 CA]# touch index.txt
[root@nan86 CA]# echo 01>serial
(2)为master创建证书申请并由CA服务器签发证书
[root@nan86 CA]# mkdir /usr/local/mysql/ssl
[root@nan86 CA]# cd /usr/local/mysql/ssl/
[root@nan86 ssl]# (umask 077;openssl genrsa -out master.key 2048)
Generating RSA private key, 2048 bit long modulus
.................+++
................................................................................................................+++
e is 65537 (0x10001)
[root@nan86 ssl]# openssl req -new -key master.key -out master.csr -days 365
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) []:ShangHai
Locality Name (eg, city) [Default City]:PuDong
Organization Name (eg, company) [Default Company Ltd]:Allen
Organizational Unit Name (eg, section) []:Tech
Common Name (eg, your name or your server‘s hostname) []:master.allen.com
Email Address []:master@allen.com
Please enter the following ‘extra‘ attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
[root@nan86 ssl]# openssl ca -in master.csr -out master.crt -days 365
Using configuration from /etc/pki/tls/openssl.cnf
Check that the request matches the signature
Signature ok
Certificate Details:
Serial Number: 1 (0x1)
Validity
Not Before: Feb 24 10:41:53 2014 GMT
Not After : Feb 24 10:41:53 2015 GMT
Subject:
countryName = CN
stateOrProvinceName = ShangHai
organizationName = Allen
organizationalUnitName = Tech
commonName = master.allen.com
emailAddress = master@allen.com
X509v3 extensions:
X509v3 Basic Constraints:
CA:FALSE
Netscape Comment:
OpenSSL Generated Certificate
X509v3 Subject Key Identifier:
AA:9B:97:2D:72:00:38:55:DB:77:86:3E:87:1C:30:C4:12:BA:69:F0
X509v3 Authority Key Identifier:
keyid:F4:DD:53:C2:28:E4:C9:1B:F0:CA:1F:5F:79:35:E5:32:4E:7A:55:39
Certificate is to be certified until Feb 24 10:41:53 2015 GMT (365 days)
Sign the certificate? [y/n]:y
1 out of 1 certificate requests certified, commit? [y/n]y
Write out database with 1 new entries
Data Base Updated
(3)为slave服务器创建证书申请
在slave
[root@nan85 ~]# mkdir /usr/local/mysql/ssl
[root@nan85 ~]# cd /usr/local/mysql/ssl/
[root@nan85 ssl]# (umask 077;openssl genrsa -out slave.key 2048)
Generating RSA private key, 2048 bit long modulus
.......................................+++
.........+++
e is 65537 (0x10001)
[root@nan85 ssl]# openssl req -new -key slave.key -out slave.csr -days 365
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) []:ShangHai
Locality Name (eg, city) [Default City]:PuDong
Organization Name (eg, company) [Default Company Ltd]:Allen
Organizational Unit Name (eg, section) []:Tech
Common Name (eg, your name or your server‘s hostname) []:slave.allen.com
Email Address []:
Please enter the following ‘extra‘ attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
(4)为slave服务器签署证书
#####将证书申请请求拷贝到CA服务器签署
slave上
[root@nan85 ssl]# scp slave.csr 10.10.54.86:/tmp/
在master上
[root@nan86 tmp]# openssl ca -in /tmp/slave.csr -out /tmp/slave.crt -days 365
Using configuration from /etc/pki/tls/openssl.cnf
Check that the request matches the signature
Signature ok
Certificate Details:
Serial Number: 2 (0x2)
Validity
Not Before: Feb 24 11:09:32 2014 GMT
Not After : Feb 24 11:09:32 2015 GMT
Subject:
countryName = CN
stateOrProvinceName = ShangHai
organizationName = Allen
organizationalUnitName = Tech
commonName = slave.allen.com
X509v3 extensions:
X509v3 Basic Constraints:
CA:FALSE
Netscape Comment:
OpenSSL Generated Certificate
X509v3 Subject Key Identifier:
7C:5A:7F:49:88:29:8C:C3:33:7B:E7:46:71:C8:68:47:60:F5:69:27
X509v3 Authority Key Identifier:
keyid:F4:DD:53:C2:28:E4:C9:1B:F0:CA:1F:5F:79:35:E5:32:4E:7A:55:39
Certificate is to be certified until Feb 24 11:09:32 2015 GMT (365 days)
Sign the certificate? [y/n]:y
1 out of 1 certificate requests certified, commit? [y/n]y
Write out database with 1 new entries
Data Base Updated
#####签署好证书申请拷贝到slave服务器
在主上：
[root@nan86 tmp]# scp /tmp/slave.crt 10.10.54.85:/usr/local/mysql/ssl/
(5)将CA证书拷贝到slave服务器一份并为master拷贝一份
主机：
[root@nan86 tmp]# scp /etc/pki/CA/cacert.pem 10.10.54.85:/usr/local/mysql/ssl/
[root@nan86 tmp]# cp /etc/pki/CA/cacert.pem /usr/local/mysql/ssl/
(6)修改master和slave服务器证书属主、属组为"mysql"用户
主机：
[root@nan86 tmp]# chown -R mysql.mysql /usr/local/mysql/ssl/
[root@nan86 tmp]# ll /usr/local/mysql/ssl/
total 20
-rw-r--r-- 1 mysql mysql 1330 2月 24 19:17 cacert.pem
-rw-r--r-- 1 mysql mysql 4532 2月 24 18:42 master.crt
-rw-r--r-- 1 mysql mysql 1054 2月 24 18:39 master.csr
-rw------- 1 mysql mysql 1679 2月 24 18:36 master.key
从机：
[root@nan85 ssl]# chown -R mysql.mysql /usr/local/mysql/ssl/
[root@nan85 ssl]# ll /usr/local/mysql/ssl/
total 20
-rw-r--r-- 1 mysql mysql 1330 2月 24 19:16 cacert.pem
-rw-r--r-- 1 mysql mysql 4456 2月 24 19:13 slave.crt
-rw-r--r-- 1 mysql mysql 1009 2月 24 18:47 slave.csr
-rw------- 1 mysql mysql 1675 2月 24 18:45 slave.key
(7)在master与slave服务器修改主配置文件开启SSL加密功能
####修改master数据库
[root@nan86 tmp]# vim /etc/my.cnf
ssl_ca=/usr/local/mysql/ssl/cacert.pem
ssl_cert=/usr/local/mysql/ssl/master.crt
ssl_key=/usr/local/mysql/ssl/master.key
[root@nan86 tmp]# /etc/init.d/mysqld restart
####修改slave数据库
[root@nan85 ssl]# vim /etc/my.cnf
ssl_ca=/usr/local/mysql/ssl/cacert.pem
ssl_cert=/usr/local/mysql/ssl/slave.crt
ssl_key=/usr/local/mysql/ssl/slave.key
[root@nan85 ssl]# /etc/init.d/mysqld restart
(8)在master服务器查看SSL加密是否开启，然后创建授权一个基于密钥认证的用户
mysql> show variables like ‘%ssl%‘;
+---------------+---------------------------------+
| Variable_name | Value |
+---------------+---------------------------------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | /usr/local/mysql/ssl/cacert.pem |
| ssl_capath | |
| ssl_cert | /usr/local/mysql/ssl/master.crt |
| ssl_cipher | |
| ssl_key | /usr/local/mysql/ssl/master.key |
+---------------+---------------------------------+
mysql> grant replication client,replication slave on *.* to ‘slave‘@‘10.10.54.85‘ identified by ‘slave‘ require ssl;
Query OK, 0 rows affected (1.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
(9)查看master服务器二进制日志文件和事件位置用于slave服务器链接从这个位置开始复制
mysql> show master status;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000021 | 806 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
(10)测试使用加密用户指定的密钥链接服务器
在slave上
[root@nan85 ssl]# mysql -uroot -p123 -h 10.10.54.86 --ssl-ca=/usr/local/mysql/ssl/cacert.pem --ssl-cert=/usr/local/mysql/ssl/slave.crt --ssl-key=/usr/local/mysql/ssl/slave.key
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 33
Server version: 5.5.30-log Source distribution
Copyright (c) 2000, 2013, 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>
(11)查看slave服务器SSL是否开启并连接master服务器
#####产看是否开启SSL
mysql> show variables like ‘%ssl%‘;
+---------------+---------------------------------+
| Variable_name | Value |
+---------------+---------------------------------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | /usr/local/mysql/ssl/cacert.pem |
| ssl_capath | |
| ssl_cert | /usr/local/mysql/ssl/slave.crt |
| ssl_cipher | |
| ssl_key | /usr/local/mysql/ssl/slave.key |
+---------------+---------------------------------+
#####链接master服务器
change master to
master_host=‘10.10.54.86‘,master_user=‘slave‘,master_password=‘slave‘,
master_log_file=‘master-bin.000021‘,master_log_pos=806,master_ssl=1,
master_ssl_ca=‘/usr/local/mysql/ssl/cacert.pem‘,
master_ssl_cert=‘/usr/local/mysql/ssl/slave.crt‘,
master_ssl_key=‘/usr/local/mysql/ssl/slave.key‘;
#####获取命令帮助
###@@@@@@@@@@@
mysql> help change master to
| MASTER_SSL = {0|1} #是否使用SSL功能
| MASTER_SSL_CA = ‘ca_file_name‘ #CA证书位置
| MASTER_SSL_CERT = ‘cert_file_name‘ #指定自己的证书文件
| MASTER_SSL_KEY = ‘key_file_name‘ #指定自己的密钥文件
(12)查看slave服务器的状态
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 10.10.54.86
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000021
Read_Master_Log_Pos: 353
Relay_Log_File: nan85-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: master-bin.000021
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Replicate_Do_DB: a
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: 353
Relay_Log_Space: 107
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File: /usr/local/mysql/ssl/cacert.pem
Master_SSL_CA_Path:
Master_SSL_Cert: /usr/local/mysql/ssl/slave.crt
Master_SSL_Cipher:
Master_SSL_Key: /usr/local/mysql/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)
ERROR:
No query specified
基于SSL复制的结果验证
(1)在master服务器上创建数据库
[root@nan86 tmp]# mysql -uroot -p123456 -e ‘create database slave;‘
[root@nan86 tmp]# mysql -uroot -p123456 -e ‘show databases‘;
+--------------------+
| Database |
+--------------------+
| information_schema |
| employees |
| mysql |
| performance_schema |
| slave |
| test |
+--------------------+
(2)登录slave服务器验证slave数据库是否存在
[root@nan85 ssl]# mysql -uroot -p123456 -e ‘show databases‘;
+--------------------+
| Database |
+--------------------+
| information_schema |
| employees |
| mysql |
| performance_schema |
| slave |
| test |
+--------------------+参考资料：
http://my.oschina.net/u/1454868/blog/202770
本文出自 “笔记” 博客，请务必保留此出处http://sunflower2.blog.51cto.com/8837503/1651701
Mysql---基于SSL的主从复制
标签：mysql ssl 主从复制
热心网友
时间：2022-04-29 20:12
当然，启用MySQL
SSL之后，由于每个数据包都需要加密和解密，这个对MySQL的性能是有不小影响的，读者们在使用的时候，要根据实际情况斟酌。
MySQL客户端登录服务器时候的密码不是明文传输，有加密策略处理。
笔者是在
ubuntu12.04
系统上使用MySQL
5.5版本测试的，其他环境请读者自行匹配。
配置MySQL服务器证书
编辑
/etc/mysql/my.cnf
文件
#
ssl-ca=/etc/mysql/cacert.pem
#
ssl-cert=/etc/mysql/server-cert.pem
#
ssl-key=/etc/mysql/server-key.pem
把上面三行默认证书配置注释打开，使用自己的证书。笔者就使用上次在搭建自己的CA服务
–
OpenSSL
CA
实战文章中生成的证书
ssl-ca=/home/yunweipai/user_certs/ca_cert.cer
ssl-cert=/home/yunweipai/user_certs/web.cer
ssl-key=/home/yunweipai/user_certs/web_key_plain.pem
这里需要注意的是，在ubuntu上，配置证书后如果不生效，参考这里解决方法
注意上面配置的
web_key_plain.pem
文件，由于MySQL不支持加密后的私钥，因此我们使用命令
openssl
rsa
-in
web_key.pem
-passin
pass:Yunweipai@123
-out
web_key_plain.pem
将私钥解密。MySQL不支持私钥加密的原因是从安全性角度考虑，因为如果要用户传递一个加密的私钥，那么必须要用户传密码，那么MySQL怎么存储这个密码呢？这就引出了我们在密码存储和传输的安全建议里面提到的一系列问题了。
所以MySQL为了简化实现，就不支持私钥加密。
指定客户端连接方式
MySQL服务端在对客户端授权的时候，可以通过选项指定客户端连接MySQL
服务器的SSL级别，参考MySQL赋权的
REQUIRE值：
ssl_option
SSL:
不认证客户端，客户端不需要提供证书
X509:
客户端必须发送一个有效的X509证书
issuer:
客户端的证书是否是服务器所配置的CA颁发的（在我们场景下是ca_cert.cer颁发的证书）
subject:
认证证书的subject（关于证书的subject在之前的文章有介绍）
cipher:
指定加密算法
这些选项可以叠加使用，如：X509|issuser
客户端连接（SSL方式）
mysql
客户端连接
mysql
-u
root
-pChangeme_123
-P
3306
--ssl-ca=/home/yunweipai/user_certs/ca_cert.cer
Welcome
to
the
MySQL
monitor.
Commands
end
with
;
or
\g.
Your
MySQL
connection
id
is
36
Server
version:
5.5.43-0ubuntu0.12.04.1
(Ubuntu)
Copyright
(c)
2000,
2015,
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.5.43,
for
debian-linux-gnu
(i686)
using
readline
6.2
Connection
id:
36
Current
database:
Current
user:
root@localhost
SSL:
Cipher
in
use
is
DHE-RSA-AES256-SHA
Current
pager:
stdout
Using
outfile:
''
Using
delimiter:
;
Server
version:
5.5.43-0ubuntu0.12.04.1
(Ubuntu)
Protocol
version:
10
Connection:
Localhost
via
UNIX
socket
Server
characterset:
latin1
Db
characterset:
latin1
Client
characterset:
latin1
Conn.
characterset:
latin1
UNIX
socket:
/var/run/mysqld/mysqld.sock
Uptime:
29
sec
Threads:
1
Questions:
109
Slow
queries:
0
Opens:
48
Flush
tables:
1
Open
tables:
41
Queries
per
second
avg:
3.758
--------------
JDBC连接
在jdbc字符串中增加下面参数
useSSL=true&verifyServerCertificate=false
这么就不需要客户端配置证书了，配置就简单很多。因为mysql本身有账号口令认证，因此不需要证书认证。
热心网友
时间：2022-04-29 21:30
MySQL默认的数据通道是不加密的，在一些安全性要求特别高的场景下，我们需要配置MySQL端口为SSL，使得数据通道加密处理，避免敏感信息泄漏和被篡改。
当然，启用MySQL SSL之后，由于每个数据包都需要加密和解密，这个对MySQL的性能是有不小影响的，读者们在使用的时候，要根据实际情况斟酌。
Windows下如何配置MySQL SSL安全传输：
1、首先明确你的MySQL版本是否支持或是否开启SSL:show global variables like 'have_%ssl';
2、确认OpenSSL安装并加入PATH系统路径环境变量。
3、正确创建号证书。
4、通过my.cnf或者命令行在mysql启动的时候加载证书配置，例如:
mysqld --ssl-ca=ca-cert.pem --ssl-cert=server-cert.pem --ssl-key=server-key.pem。
5、客户端连接时同样要记得加载证书。
