Mysql(Mariadb) 主从更改为SSL加密方式

          最近半年太忙,没有更新多少内容,今天测试了Mysql主从的SSL加密访问方式。现将测试笔记列于此,深层原理未细究,如有错误之处,烦请指出。

一。概述:

1.架构: 2主2从,从同时只连1个主,可以自由在这2个主库之间做切换。
2.假设2主2从,已经配好,使用的是GTID的连接方式。本文只需要将此改为SSL连接
3.具体信息如下:

master1:  
hostname: db01   ip: 10.100.31.141   
master2:  
hostname: db02   ip: 10.100.31.142
slave1:  
hostname: db11   ip: 10.100.31.151
slave2:  
hostname: db12   ip: 10.100.31.152

4. 在2个master机器上重新创建 slave user,需要加require ssl
grant replication slave,replication client on *.* to slave@'%' identified by 'slave' require ssl;
5. 版本信息:
[root@db01 ~]# mysql --version
mysql  Ver 15.1 Distrib 10.1.8-MariaDB, for Linux (x86_64) using readline 5.1
[root@db01 ~]# cat /etc/issue
CentOS release 6.6 (Final)
Kernel \r on an \m

二。操作系统中配置SSL


1.配置MASTER1的SSL

1.1.在master1创建CA服务器
cd /etc/pki/CA
rm -rf *
mkdir private  newcerts certs crl
(umask 077;openssl genrsa -out private/cakey.pem 2048)
openssl req -new -x509 -key private/cakey.pem -out cacert.pem -days 36500
touch index.txt serial crlnumber
echo 01 > serial
==============================================================================
[root@db01 CA]# openssl req -new -x509 -key private/cakey.pem -out cacert.pem -days 36500
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]:exiao
Organizational Unit Name (eg, section) []:mysql
Common Name (eg, your name or your server's hostname) []:db01
Email Address []:
==============================================================================

1.2. 为master1(db01)本身签发证书

# mkdir /data01/mysql/ssl
# cd /data01/mysql/ssl/
(umask 077;openssl genrsa -out master_db01.key 2048)
openssl req -new -key master_db01.key -out master_db01.csr -days 36500
openssl ca -in master_db01.csr -out master_db01.crt -days 36500

==============================================================================
[root@db01 ssl]# openssl req -new -key master_db01.key -out master_db01.csr -days 36500
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]:exiao
Organizational Unit Name (eg, section) []:mysql
Common Name (eg, your name or your server's hostname) []:db01
Email Address []:


Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
-----------
[root@db01 ssl]# openssl ca -in master_db01.csr -out master_db01.crt -days 36500
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: Dec 18 02:36:24 2015 GMT
            Not After : Nov 24 02:36:24 2115 GMT
        Subject:
            countryName               = CN
            stateOrProvinceName       = Beijing
            organizationName          = exiao
            organizationalUnitName    = mysql
            commonName                = db01
        X509v3 extensions:
            X509v3 Basic Constraints: 
                CA:TRUE
            Netscape Comment: 
                OpenSSL Generated Certificate
            X509v3 Subject Key Identifier: 
                8A:18:2B:84:E6:B8:8E:D9:AB:07:90:D5:5D:63:8C:9B:A3:DB:6E:00
            X509v3 Authority Key Identifier: 
                keyid:B5:4F:20:EC:C7:CA:B4:A1:AA:1B:F1:B9:91:0F:85:12:2D:59:68:29


Certificate is to be certified until Nov 24 02:36:24 2115 GMT (36500 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


==============================================================================


1.3 为master1自已提供CA证书


cp /etc/pki/CA/cacert.pem /data01/mysql/ssl/cacert_db01.pem

2.配MASTER2的SSL

2.1.在master2创建CA服务器
cd /etc/pki/CA
rm -rf *
mkdir private  newcerts certs crl
(umask 077;openssl genrsa -out private/cakey.pem 2048)
openssl req -new -x509 -key private/cakey.pem -out cacert.pem -days 36500
touch index.txt serial crlnumber
echo 01 > serial


2.2. 为master1(db02)本身签发证书

# mkdir /data01/mysql/ssl
# cd /data01/mysql/ssl/
(umask 077;openssl genrsa -out master_db02.key 2048)
openssl req -new -key master_db02.key -out master_db02.csr -days 36500
openssl ca -in master_db02.csr -out master_db02.crt -days 36500

2.3 为master2自已提供CA证书
cp /etc/pki/CA/cacert.pem /data01/mysql/ssl/cacert_db02.pem

3.配置SLAVE1的SSL

3.1.在slave1(db11)上生成证书请求

# mkdir /data01/mysql/ssl
# cd /data01/mysql/ssl/
(umask 077;openssl genrsa -out slave_db11.key 2048)
openssl req -new -key slave_db11.key  -out slave_db11.csr -days 36500
scp slave_db11.csr 10.100.31.141:/root
scp slave_db11.csr 10.100.31.142:/root
==============================================================================
[root@db11 ssl]# openssl req -new -key slave_db11.key  -out slave_db11.csr -days 36500
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]:exiao
Organizational Unit Name (eg, section) []:mysql
Common Name (eg, your name or your server's hostname) []:db11
Email Address []:


Please enter the following 'extra' attributes
to be sent with your certificate request
==============================================================================

3.2 master1为slave1签发证书
#master1(db01)上操作
cd /root/
openssl ca -in slave_db11.csr -out db01-slave_db11.crt -days 36500
scp db01-slave_db11.crt 10.100.31.151:/data01/mysql/ssl/

==============================================================================
[root@db01 ~]# openssl ca -in slave_db11.csr -out db01-slave_db11.crt -days 36500
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: Dec 18 03:25:35 2015 GMT
            Not After : Nov 24 03:25:35 2115 GMT
        Subject:
            countryName               = CN
            stateOrProvinceName       = Beijing
            organizationName          = exiao
            organizationalUnitName    = mysql
            commonName                = db11
        X509v3 extensions:
            X509v3 Basic Constraints: 
                CA:TRUE
            Netscape Comment: 
                OpenSSL Generated Certificate
            X509v3 Subject Key Identifier: 
                FD:CB:77:04:C5:54:47:24:06:C0:3E:AC:5D:CC:6C:F2:3F:1E:EA:C6
            X509v3 Authority Key Identifier: 
                keyid:B5:4F:20:EC:C7:CA:B4:A1:AA:1B:F1:B9:91:0F:85:12:2D:59:68:29


Certificate is to be certified until Nov 24 03:25:35 2115 GMT (36500 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.3 master1为slave1(db11)提供CA的证书
scp /etc/pki/CA/cacert.pem 10.100.31.151:/data01/mysql/ssl/cacert_db01.pem

3.4 master2为slave1签发证书
#master2(db02)上操作
cd /root/
openssl ca -in slave_db11.csr -out db02-slave_db11.crt -days 36500
scp db02-slave_db11.crt 10.100.31.151:/data01/mysql/ssl/
3.5 master2为slave1(db11)提供CA的证书
scp /etc/pki/CA/cacert.pem 10.100.31.151:/data01/mysql/ssl/cacert_db02.pem

4. 配置SLAVE2的SSL

4.1.在slave2(db12)上生成证书请求

# mkdir /data01/mysql/ssl
# cd /data01/mysql/ssl/
(umask 077;openssl genrsa -out slave_db12.key 2048)
openssl req -new -key slave_db12.key  -out slave_db12.csr -days 36500
scp slave_db12.csr 10.100.31.141:/root
scp slave_db12.csr 10.100.31.142:/root

4.2 master1为slave2签发证书
#master1(db01)上操作
cd /root/
openssl ca -in slave_db12.csr -out db01-slave_db12.crt -days 36500
scp db01-slave_db12.crt 10.100.31.152:/data01/mysql/ssl/
4.3 为slave1(db12)提供CA的证书
scp /etc/pki/CA/cacert.pem 10.100.31.152:/data01/mysql/ssl/cacert_db01.pem

4.4 master2为slave2签发证书
#master2(db02)上操作
cd /root/
openssl ca -in slave_db12.csr -out db02-slave_db12.crt -days 36500
scp db02-slave_db12.crt 10.100.31.152:/data01/mysql/ssl/

4.5 master2为slave2(db12)提供CA的证书
scp /etc/pki/CA/cacert.pem 10.100.31.152:/data01/mysql/ssl/cacert_db02.pem

5.修改4台机器的以下目录权限

#分别在4台机器上都执行
chown -R mysql:mysql /data01/mysql/ssl/

三。mysql主从中配置SSL

1.master1中添加如下参数后重启数据库

#SSL
ssl
ssl-ca= /data01/mysql/ssl/cacert_db01.pem
ssl-cert=/data01/mysql/ssl/master_db01.crt
ssl-key=/data01/mysql/ssl/master_db01.key
ssl_cipher = DHE-RSA-AES256-SHA

2.master2中同上

#SSL
ssl
ssl-ca= /data01/mysql/ssl/cacert_db02.pem
ssl-cert=/data01/mysql/ssl/master_db02.crt
ssl-key=/data01/mysql/ssl/master_db02.key
ssl_cipher = DHE-RSA-AES256-SHA

3.手工连接测试

mysql -uslave -p -h10.100.31.141 --ssl=1 --ssl_ca='/data01/mysql/ssl/cacert_db01.pem'

MariaDB [(none)]> status;
--------------
mysql  Ver 15.1 Distrib 10.1.8-MariaDB, for Linux (x86_64) using readline 5.1


Connection id:		108
Current database:	
Current user:		slave@10.100.31.142
SSL:			Cipher in use is DHE-RSA-AES256-SHA
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server:			MariaDB
Server version:		10.1.8-MariaDB-log Source distribution
Protocol version:	10
Connection:		10.100.31.141 via TCP/IP
Server characterset:	utf8
Db     characterset:	utf8
Client characterset:	utf8
Conn.  characterset:	utf8
TCP port:		3306
Uptime:			30 min 8 sec


MariaDB [(none)]> show variables like '%ssl%';
+---------------------+------------------------------+
| Variable_name       | Value                        |
+---------------------+------------------------------+
| have_openssl        | NO                           |
| have_ssl            | YES                          |
| ssl_ca              | /data01/mysql/ssl/cacert.pem |
| ssl_capath          |                              |
| ssl_cert            | /data01/mysql/ssl/master.crt |
| ssl_cipher          | DHE-RSA-AES256-SHA           |
| ssl_crl             |                              |
| ssl_crlpath         |                              |
| ssl_key             | /data01/mysql/ssl/master.key |
| version_ssl_library | YaSSL 2.3.8                  |

4. 主从配置SSL

slave1上设置:

CHANGE MASTER TO
MASTER_HOST='10.100.31.141',           
MASTER_USER='slave',                 
MASTER_PASSWORD='slave',
MASTER_USE_GTID=slave_pos,
master_ssl=1,
master_ssl_ca='/data01/mysql/ssl/cacert_db01.pem',
master_ssl_cert='/data01/mysql/ssl/db01-slave_db11.crt',
master_ssl_key='/data01/mysql/ssl/slave_db11.key';

start slave;

MariaDB [(none)]> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.100.31.141
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000030
          Read_Master_Log_Pos: 829
               Relay_Log_File: relay-bin.000003
                Relay_Log_Pos: 19067946
        Relay_Master_Log_File: mysql-bin.000015
             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: 19067654
              Relay_Log_Space: 26107784
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /data01/mysql/ssl/cacert_db01.pem
           Master_SSL_CA_Path: 
              Master_SSL_Cert: /data01/mysql/ssl/db01-slave_db11.crt
            Master_SSL_Cipher: 
               Master_SSL_Key: /data01/mysql/ssl/slave_db11.key
        Seconds_Behind_Master: 768062
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: 11
               Master_SSL_Crl: /data01/mysql/ssl/cacert_db01.pem
           Master_SSL_Crlpath: 
                   Using_Gtid: Slave_Pos
                  Gtid_IO_Pos: 0-11-131063
      Replicate_Do_Domain_Ids: 
  Replicate_Ignore_Domain_Ids: 
                Parallel_Mode: conservative
1 row in set (0.00 sec)

切换到master2试试:

stop slave;
CHANGE MASTER TO
MASTER_HOST='10.100.31.142',           
MASTER_USER='slave',                 
MASTER_PASSWORD='slave',
MASTER_USE_GTID=slave_pos,
master_ssl=1,
master_ssl_ca='/data01/mysql/ssl/cacert_db02.pem',
master_ssl_cert='/data01/mysql/ssl/db02-slave_db11.crt',
master_ssl_key='/data01/mysql/ssl/slave_db11.key';
start slave;

经确认检查,同样没有问题。

四。其他说明

1.SLAVE2也使用了类似的方法连接测试,也没有问题。 2.本文忽略了1个内容,就是2个master之间是互为主备的关系,在目前的基础上也可以加上SSL,也经实际测试,没有问题。限于影响文章阅读以及实际没有使用场景不再列出。
相关文章
相关标签/搜索
每日一句
    每一个你不满意的现在,都有一个你没有努力的曾经。
公众号推荐
   一个历史类的公众号,欢迎关注
一两拨千金