LoginSignup
71

More than 3 years have passed since last update.

MySQLでSSL接続を有効にする

Last updated at Posted at 2014-08-25

環境

  • AmazonLinux AMI release 2014.03
  • MySQL 5.5
  • OpenSSL 1.0.0

MySQLのインストール

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

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

mysqlサーバの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       |          |
+---------------+----------+
7 rows in set (0.00 sec)

SSL証明書の作成

サーバ証明書の作成

$ 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

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

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

$ 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を再起動

$ sudo /etc/init.d/mysqld restart

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

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へ接続

$ mysql -u root -p --ssl-ca=/path/to/ca-cert.pem
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> grant all on test.* ssluser@'localhost' identified by ${password} require ssl;

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

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

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

※ 接続できない

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

$ mysql -u ssluser -p --ssl-ca=/path/to/ca-cert.pem
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
6.3.9.5 Setting Up SSL Certificates and Keys for MySQL
13.7.1.3 GRANT Syntax

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
71