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接続したいところです。
こちらの記事はなかの人（suz-lab）監修のもと掲載しています。
元記事は、こちら