1) 生成SSL证书
[root@srv1 ~]# cd /etc/pki/tls/certs
[root@node1 certs]# make mariadb.key
umask 77 ; \
/usr/bin/openssl genrsa -aes128 2048 > mariadb.key
Generating RSA private key, 2048 bit long modulus
.........................................................................................+++
..................+++
e is 65537 (0x10001)
Enter pass phrase: # 输入口令
Verifying - Enter pass phrase: # 输入口令
[root@node1 certs]# openssl rsa -in mariadb.key -out mariadb.key
Enter pass phrase for mariadb.key: # 将私钥的保护口令移除
writing RSA key
[root@node1 certs]# make mariadb.csr
umask 77 ; \
/usr/bin/openssl req -utf8 -new -key mariadb.key -out mariadb.csr
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]:1000cc.net
Organizational Unit Name (eg, section) []:tech
Common Name (eg, your name or your server's hostname) []:node1.1000cc.net
Email Address []:
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
[root@node1 certs]# openssl x509 -in mariadb.csr -out mariadb.crt -req -signkey mariadb.key -days 3650
Signature ok
subject=/C=CN/ST=BeiJing/L=BeiJing/O=1000cc.net/OU=tech/CN=node1.1000cc.net
Getting Private key
2) 配置MariaDB
[root@node1 ]# cp /etc/pki/tls/certs/mariadb.key /etc/pki/tls/certs/mariadb.crt /etc/pki/tls/certs/ca-bundle.crt /etc/opt/rh/rh-mariadb103/pki/
[root@srv1 ~]# chown mysql. /etc/opt/rh/rh-mariadb103/pki/*
[root@srv1 ~]# vim /etc/opt/rh/rh-mariadb103/my.cnf.d/mariadb-server.cnf
# 于[mysqld]区段最后追加如下内容
ssl-ca=/etc/opt/rh/rh-mariadb103/pki/ca-bundle.crt
ssl-cert=/etc/opt/rh/rh-mariadb103/pki/mariadb.crt
ssl-key=/etc/opt/rh/rh-mariadb103/pki/mariadb.key
[root@srv1 ~]# systemctl restart rh-mariadb103-mariadb
3) 验证
(1) 验证SSL状态
[root@srv1 ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.27-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 variables like '%ssl%';
+---------------------+---------------------------------------------+
| Variable_name | Value |
+---------------------+---------------------------------------------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | /etc/opt/rh/rh-mariadb103/pki/ca-bundle.crt |
| ssl_capath | |
| ssl_cert | /etc/opt/rh/rh-mariadb103/pki/mariadb.crt |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | /etc/opt/rh/rh-mariadb103/pki/mariadb.key |
| version_ssl_library | OpenSSL 1.0.2k-fips 26 Jan 2017 |
+---------------------+---------------------------------------------+
10 rows in set (0.002 sec)
MariaDB [(none)]> exit
(2) 使用SSL登录
[root@srv1 ~]# mysql -u root -p --ssl
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.3.27-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 status like 'ssl_cipher';
+---------------+---------------------------+
| Variable_name | Value |
+---------------+---------------------------+
| Ssl_cipher | DHE-RSA-AES256-GCM-SHA384 |
+---------------+---------------------------+
1 row in set (0.01 sec)
# 如果没有使用ssl
[root@srv1 ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.2.22-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 status like 'ssl_cipher';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Ssl_cipher | |
+---------------+-------+
1 row in set (0.01 sec)
4) 指定账户强制使用SSL
[root@srv1 ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.3.27-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.
(1) 对新建账户强制使用SSL
MariaDB [(none)]> create user snow identified by 'password' require ssl;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> select user,host,ssl_type from mysql.user;
+--------+-----------+----------+
| user | host | ssl_type |
+--------+-----------+----------+
| root | localhost | |
| root | 127.0.0.1 | |
| root | ::1 | |
| thomas | % | |
| snow | % | ANY |
+--------+-----------+----------+
5 rows in set (0.00 sec)
(2) 对现有账户强制使用SSL
MariaDB [(none)]> grant usage on *.* to 'thomas'@'%' require ssl;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> select user,host,ssl_type from mysql.user;
+--------+-----------+----------+
| user | host | ssl_type |
+--------+-----------+----------+
| root | localhost | |
| root | 127.0.0.1 | |
| root | ::1 | |
| thomas | % | ANY |
| snow | % | ANY |
+--------+-----------+----------+
5 rows in set (0.00 sec)
(3) 取消现有账户使用SSL
MariaDB [(none)]> grant usage on *.* to 'thomas'@'%' require none;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> select user,host,ssl_type from mysql.user;
+--------+-----------+----------+
| user | host | ssl_type |
+--------+-----------+----------+
| root | localhost | |
| root | 127.0.0.1 | |
| root | ::1 | |
| thomas | % | |
| snow | % | ANY |
+--------+-----------+----------+
5 rows in set (0.00 sec)
# 以下指令也可以取消SSL使用
MariaDB [(none)]> ALTER USER 'thomas'@'%' REQUIRE NONE;
Query OK, 0 rows affected (0.00 sec)
|
