Mysql ssl 设置

Mysql 5.7
安装目录：/usr/local/mysql

1. 生成证书文件
/usr/local/mysql/mysql_ssl_rsa_setup --datadir=/usr/local/mysql/conf

2. 安装证书
vi /usr/local/mysql/conf/my.cnf

ssl-ca=/usr/local/mysql/conf/ca.pem
ssl-cert=/usr/local/mysql/conf/server-cert.pem
ssl-key=/usr/local/mysql/conf/server-key.pem

3. 测试是否工作
mysql -uroot -p --ssl-ca=/usr/local/mysql/conf/ca.pem

mysql> \s;
--------------
mysql  Ver 14.14 Distrib 5.7.26, for linux-glibc2.12 (x86_64) using  EditLine wrapper

Connection id: 5375
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.7.26 MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db     characterset: utf8mb4
Client characterset: utf8
Conn.  characterset: utf8
UNIX socket: /usr/local/mysql/mysql.sock
Uptime: 3 days 17 hours 15 min 47 sec

Threads: 7  Questions: 58943  Slow queries: 0  Opens: 527  Flush tables: 1  Open tables: 433  Queries per second avg: 0.183
--------------
红色表示已工作。

mysql> show variables like '%ssl%';
+---------------+---------------------------------------+
| Variable_name | Value                                 |
+---------------+---------------------------------------+
| have_openssl  | YES                                   |
| have_ssl      | YES                                   |
| ssl_ca        | /usr/local/mysql/conf/ca.pem          |
| ssl_capath    |                                       |
| ssl_cert      | /usr/local/mysql/conf/server-cert.pem |
| ssl_cipher    |                                       |
| ssl_crl       |                                       |
| ssl_crlpath   |                                       |
| ssl_key       | /usr/local/mysql/conf/server-key.pem  |
+---------------+---------------------------------------+
9 rows in set (0.02 sec)
红色表示已开启SSL连接

4. JConnect连接方式

-- 生成JKS文件
keytool -import -alias serverCACert -file ca.pem -keystore ca-keystore

keytool -import -alias client-key -file client-cert.pem -keystore client-keystore

JDBC示例：

        Class.forName("com.mysql.jdbc.Driver");
        Properties info = new Properties();
        info.put("user","root");
        info.put("password","1!Admin");
        info.put("clientCertificateKeyStoreType","JKS");
        info.put("clientCertificateKeyStoreUrl","file:///usr/local/mysql/conf/client-keystore");
        info.put("clientCertificateKeyStorePassword","123456");
        info.put("trustCertificateKeyStoreType","JKS");
        info.put("trustCertificateKeyStoreUrl","file:///usr/local/mysql/conf/ca-keystore");
        info.put("trustCertificateKeyStorePassword","123456");

        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/lesson_test?" +
                "useUnicode=true&characterEncoding=UTF-8&useOldAliasMetadataBehavior=true" +
                "&useSSL=true&verifyServerCertificate=true", info);
        System.out.println(con);

c3p0配置

    <bean id="paygateDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close" lazy-init="true">
		<property name="driverClass" value="com.mysql.jdbc.Driver" />
       	<property name="jdbcUrl" value="jdbc:mysql://127.0.0.1/lesson_test?seUnicode=true&amp;characterEncoding=utf-8&amp;useSSL=true&amp;verifyServerCertificate=true"/>
<property name="properties">
			<props>
				<prop key="user">root</prop>
				<prop key="password">1!Admin</prop>
				<prop key="clientCertificateKeyStoreType">JKS</prop>
				<prop key="clientCertificateKeyStoreUrl">file:///usr/local/mysql/conf/client-keystore</prop>
				<prop key="clientCertificateKeyStorePassword">123456</prop>
				<prop key="trustCertificateKeyStoreType">JKS</prop>
				<prop key="trustCertificateKeyStoreUrl">file:///usr/local/mysql/conf/ca-keystore</prop>
				<prop key="trustCertificateKeyStorePassword">123456</prop>
			</props>
		</property>
</bean>

Druid配置

DruidDataSource ds = new DruidDataSource();
        ds.setUrl("jdbc:mysql://localhost:3306/lesson_test?useUnicode=true&characterEncoding=UTF-8&useOldAliasMetadataBehavior=true&useSSL=true&verifyServerCertificate=true");
        ds.setUsername("root");
        ds.setPassword("1!Admin");
        ds.setDriverClassName("com.mysql.jdbc.Driver");
        ds.setLoginTimeout(3);
        ds.setPhyTimeoutMillis(3000);
        ds.setQueryTimeout(25);
        ds.setConnectionErrorRetryAttempts(3);
        ds.setMaxWait(3000);
        ds.getConnectProperties().put("clientCertificateKeyStoreType","JKS");
        ds.getConnectProperties().put("clientCertificateKeyStoreUrl","file:///usr/local/mysql/conf/client-keystore");
        ds.getConnectProperties().put("clientCertificateKeyStorePassword","123456");
        ds.getConnectProperties().put("trustCertificateKeyStoreType","JKS");
        ds.getConnectProperties().put("trustCertificateKeyStoreUrl","file:///usr/local/mysql/conf/ca-keystore");
        ds.getConnectProperties().put("trustCertificateKeyStorePassword","123456");
        DruidPooledConnection con = ds.getConnection();
        System.out.println(con);