MySQL5.5 SSL（サーバ認証）の設定

MySQLをSSL通信（サーバ認証）できるように設定します。手順としては、以下のステップを踏みます。

①MySQLサーバの鍵ペアと証明書を発行する。
②MySQLの設定ファイル(my.ini)を修正する（サーバ証明書と鍵のパスを指定）。
③MySQLを再起動する。
④MySQLの接続確認する。

(1)現在のMySQLのSSL設定情報を確認します。もちろん、デフォルトでは無効になってます。

mysql> show variables like 'have_ssl';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_ssl      | DISABLED |
+---------------+----------+
1 row in set (0.00 sec)

(2)OpenSSLを使用し、MySQLサーバに対する鍵ペアと証明書を発行します。
■CAの鍵ペアと証明書の作成
C:\tomcat>c:\OpenSSL-Win64\bin\openssl.exe req -new -x509 -keyout "ca-key.pem" -
out "ca-cert.pem"
Loading 'screen' into random state - done
Generating a 1024 bit RSA private key
.........++++++
..........++++++
writing new private key to 'ca-key.pem'
Enter PEM pass phrase:
Verifying - Enter PEM pass phrase:
-----
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) [AU]:JP
State or Province Name (full name) [Some-State]:.
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:.
Organizational Unit Name (eg, section) []:
Common Name (eg, YOUR name) []:ca.yasuyasu.com
Email Address []:

■サーバ証明書と鍵ペアの作成
C:\tomcat>c:\OpenSSL-Win64\bin\openssl.exe req -new -keyout "server-key.pem" -ou
t "server-req.pem" -days 3600
Loading 'screen' into random state - done
Generating a 1024 bit RSA private key
.............................++++++
.......++++++
writing new private key to 'server-key.pem'
Enter PEM pass phrase:
Verifying - Enter PEM pass phrase:
-----
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) [AU]:JP
State or Province Name (full name) [Some-State]:.
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:.
Organizational Unit Name (eg, section) []:
Common Name (eg, YOUR name) []:server.yasuyasu.com
Email Address []:

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
C:\tomcat>c:\OpenSSL-Win64\bin\openssl.exe  rsa -in "server-key.pem" -out "serve
r-key.pem"
Enter pass phrase for server-key.pem:
writing RSA key

C:\tomcat>c:\OpenSSL-Win64\bin\openssl.exe x509 -req -days 3600 -CA "ca-cert.pem
" -CAkey "ca-key.pem" -CAserial "serial.conf" -in "server-req.pem" -out "server-
cert.pem"
Loading 'screen' into random state - done
Signature ok
subject=/C=JP/CN=server.yasuyasu.com
Getting CA Private Key
Enter pass phrase for ca-key.pem:
unable to write 'random state'

(3)MySQLの設定ファイル(my.ini)を修正します。太字箇所を追加します。
★パスの区切りは、"\"ではなく、"/"であることに注意！
[mysqld]

ssl-ca=C:/tomcat/ca-cert.pem 
ssl-cert=C:/tomcat/server-cert.pem
ssl-key=C:/tomcat/server-key.pem

(4)MySQLを再起動します。

(5)MySQLのSSL設定を確認します。

c:\>mysql -uroot -ppassword --ssl-ca=c:\tomcat\ca-cert.pem
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.14-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, 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> show variables like 'have_ssl';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_ssl      | YES   |
+---------------+-------+
1 row in set (0.00 sec)