2011年8月15日月曜日

MySQLにSSLで接続

スズキです。

MySQLにインターネット経由で接続するような場合は、SSL接続にしたいところです。

ということで、試してみました。

まずは、現状の確認です。SSL接続が無効になっていることがわかります。

mysql> SHOW VARIABLES LIKE '%ssl%';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_openssl  | DISABLED |
| have_ssl      | DISABLED |
| ssl_ca        |          |
| ssl_capath    |          |
| ssl_cert      |          |
| ssl_cipher    |          |
| ssl_key       |          |
+---------------+----------+

SSL接続には、当然、証明書類が必要になるので、下記のディレクトリで作成します。

# pwd
/tmp/ssl

まずは、CAのキーを作成します。

# openssl genrsa -out ca-key.pem 2048
Generating RSA private key, 2048 bit long modulus
.+++
...............................+++
e is 65537 (0x10001)

そして、CAの証明書の作成です。

# openssl req -new -x509 -nodes -days 1000 -key ca-key.pem -out ca-cert.pem
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) [GB]:JP
State or Province Name (full name) [Berkshire]:Tokyo
Locality Name (eg, city) [Newbury]:SHibuya-ku
Organization Name (eg, company) [My Company Ltd]:
[root@ip-10-150-174-7 ssl]# openssl req -new -x509 -nodes -days 1000 -key ca-key.pem -out ca-cert.pem
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) [GB]:JP
State or Province Name (full name) [Berkshire]:Tokyo
Locality Name (eg, city) [Newbury]:Shibuya-ku
Organization Name (eg, company) [My Company Ltd]:SUZ-LAB
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your server's hostname) []ca.suz-lab.com
Email Address []:

今度は、MySQLサーバのキーとCSRの作成です。

# openssl req -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pem -out server-req.pem
Generating a 2048 bit RSA private key
.................................................................................+++
...........................................................+++
writing new private key to 'server-key.pem'
-----
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) [GB]:JP
State or Province Name (full name) [Berkshire]:Tokyo
Locality Name (eg, city) [Newbury]:Shibuya-ku
Organization Name (eg, company) [My Company Ltd]:SUZ-LAB
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your server's hostname) []:mysql.suz-lab.com
Email Address []:

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

最後に、MySQLサーバの証明書を作成します。

# openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -out server-cert.pem -set_serial 01
Signature ok
subject=/C=JP/ST=Tokyo/L=Shibuya-ku/O=SUZ-LAB/CN=mysql.suz-lab.com
Getting CA Private Key

上記で作成したファイルは下記の通りです。

# ls
ca-cert.pem  ca-key.pem  server-cert.pem  server-key.pem  server-req.pem

これらのファイルを、下記のように"/etc/my.cnf"に記述します。

[mysqld]
...
ssl-ca=/tmp/ssl/ca-cert.pem
ssl-cert=/tmp/ssl/server-cert.pem
ssl-key=/tmp/ssl/server-key.pem

MySQLをリスタートすると、下記のように今度はSSLが有効になっていることが確認できます。

mysql> SHOW VARIABLES LIKE '%ssl%';
+---------------+--------------------------+
| Variable_name | Value                    |
+---------------+--------------------------+
| have_openssl  | YES                      |
| have_ssl      | YES                      |
| ssl_ca        | /tmp/ssl/ca-cert.pem     |
| ssl_capath    |                          |
| ssl_cert      | /tmp/ssl/server-cert.pem |
| ssl_cipher    |                          |
| ssl_key       | /tmp/ssl/server-key.pem  |
+---------------+--------------------------+

クライアントからの接続に関しては、下記のようにSSL接続専用のユーザーを作成し、

mysql> GRANT ALL PRIVILEGES ON *.* TO ssluser@'%' IDENTIFIED BY 'sslpass' REQUIRE SSL;

接続時にCAの証明書を指定すると、無事接続することができます。

# mysql -h xxx.xxx.xxx.xxx -u ssluser --ssl-ca=/tmp/ssl/ca-cert.pem -p

MySQLのリージョン間レプリケーションはSSL接続したいところです。
--------
http://www.suz-lab.com

0 コメント: