Windows下如何配置MySQL SSL安全传输
发布网友
发布时间：2022-04-07 17:45
共2个回答
懂视网
时间：2022-04-07 22:06
由于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-07 19:14
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、客户端连接时同样要记得加载证书
