MySQLでSSL接続を有効にする


## 環境

* AmazonLinux AMI release 2014.03
* MySQL 5.5
* OpenSSL 1.0.0

## MySQLのインストール

### パッケージでのインストール

```shell
$ sudo yum install mysql-server
$ sudo /etc/init.d/mysqld start
```

### mysqlサーバのSSL対応の確認

```mysql
mysql> show variables like '%ssl%';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_openssl  | DISABLED |
| have_ssl      | DISABLED |
| ssl_ca        |          |
| ssl_capath    |          |
| ssl_cert      |          |
| ssl_cipher    |          |
| ssl_key       |          |
+---------------+----------+
7 rows in set (0.00 sec)
```

## SSL証明書の作成

### サーバ証明書の作成
```shell
$ openssl genrsa 2048 > ca-key.pem
$ openssl req -new -x509 -nodes -days 3600 \
    -key ca-key.pem -out ca-cert.pem
$ openssl req -newkey rsa:2048 -days 3600  \
    -nodes -keyout server-key.pem -out server-req.pem
$ openssl rsa -in server-key.pem -out server-key.pem
$ openssl x509 -req -in server-req.pem -days 3600 \
    -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 \
    -out server-cert.pem
```

### クライアント証明書の作成

※ クライアント認証を有効にする場合に使用する

```shell
$ openssl req -newkey rsa:2048 -days 3600 \
    -nodes -keyout client-key.pem -out client-req.pem
$ openssl rsa -in client-key.pem -out client-key.pem
$ openssl x509 -req -in client-req.pem -days 3600 \
    -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 \
    -out client-cert.pem
```

## MySQLへ設定追加

### /etc/my.cnfへ以下を追加

```/etc/my.cnf
[mysqld]

...(省略)...

ssl-ca = /path/to/ca-cert.pem
ssl-cert = /path/to/server-cert.pem
ssl-key = /path/to/server-key.pem
```

### mysqldを再起動

```shell
$ sudo /etc/init.d/mysqld restart
```

### mysqlサーバのSSL対応を再度、確認

```mysql
mysql> show variables like '%ssl%';
+---------------+--------------------------+
| Variable_name | Value                    |
+---------------+--------------------------+
| have_openssl  | YES                      |
| have_ssl      | YES                      |
| ssl_ca        | /path/to/ca-cert.pem     |
| ssl_capath    |                          |
| ssl_cert      | /path/to/server-cert.pem |
| ssl_cipher    |                          |
| ssl_key       | /path/to/server-key.pem  |
+---------------+--------------------------+
7 rows in set (0.00 sec)
```

## 動作確認

### mysqlへ接続

```shell
$ mysql -u root -p --ssl-ca=/path/to/ca-cert.pem
```

```mysql
mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.5.38, for Linux (x86_64) using  deadline 5.1

Connection id:          1030
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.5.38 MySQL Community Server (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 10 days 13 hours 13 min 24 sec

Threads: 2  Questions: 12840  Slow queries: 8  Opens: 258  Flush tables: 1  Open tables: 251  Queries per second avg: 0.014
--------------
```

### SSL接続必須のアカウントを作成

```mysql
mysql> grant all on test.* ssluser@'localhost' identified by ${password} require ssl;
```

※ SSL接続を必須とする場合は、アカウント作成時に、sslオプションを指定する。

### mysqlへ接続(オプションなし)

```shell
$ mysql -u ssluser -p
ERROR 1045 (28000): Access denied for user 'ssluser'@'localhost' (using password: YES)

```

※ 接続できない

### mysqlへ接続(オプションあり)

```shell
$ mysql -u ssluser -p --ssl-ca=/path/to/ca-cert.pem
```

```mysql
mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.5.38, for Linux (x86_64) using  deadline 5.1

Connection id:          1043
Current database:
Current user:           ssluser@localhost
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.5.38 MySQL Community Server (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 10 days 13 hours 50 min 34 sec

Threads: 2  Questions: 12927  Slow queries: 8  Opens: 258  Flush tables: 1  Open tables: 251  Queries per second avg: 0.014
--------------
```

## 参考
[6.3.9.3 Using SSL Connections]
(http://dev.mysql.com/doc/refman/5.5/en/using-ssl-connections.html)
[6.3.9.5 Setting Up SSL Certificates and Keys for MySQL]
(http://dev.mysql.com/doc/refman/5.5/en/creating-ssl-certs.html)
[13.7.1.3 GRANT Syntax]
(http://dev.mysql.com/doc/refman/5.5/en/grant.html)