MYSQL5.7とLogstash7.4のSSL接続

＜背景＞
さくらサーバー環境で稼働しているMYSQLの社外販売情報を、AWS側へセキュアに転送し、
kibanaのグラフツールを使用して社外からもマーケティング情報として営業が利用できないか検討しました。

・AWSインスタンス
　ubuntu 18.04
　elastic search7.4
　logstash7.4

・さくらサーバー
　mysql5.7（ここで本番用データベースが稼働中）

という環境で、AWSインスタンスからさくらのMYSQLのデータを、SSL接続設定したので参考にしてみてください。
ElastichsearchのNODE間接続というのもあるようなのですが、まだ知識がないので、MYSQLのSSL接続化で
実現してみました。

１．SWをインストール

　以下のサイトから、Elasticsearch、Logstash　をダウンロードし、AWSインスタンスにインストール。
　java環境は、Openjdk-8　としました。

　https://www.elastic.co/jp/downloads/

２．jdbcドライバーをダウンロード

　以下のサイトから、「mysql-connector-java-8.0.12.jar」をAWSインスタンスの/usr/share/logstash　へダウンロード。

３．jksを作成

　JDBCドライバでSSL接続するには、接続先サーバーのpemファイルを使用して、jksファイルを作成し、AWSインスタンスに保存する必要があります。

そのために、MYSQLのSSL設定を確認する必要があります。
自分の環境は既にSSL設定が構築されていましたので、証明書の作成などは必要ありませんでした。

自分の環境は以下のようでした。

mysql> SHOW VARIABLES LIKE '%ssl%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| have_openssl  | YES             |
| have_ssl      | YES             |
| ssl_ca        | ca.pem          |
| ssl_capath    |                 |
| ssl_cert      | server-cert.pem |
| ssl_cipher    |                 |
| ssl_crl       |                 |
| ssl_crlpath   |                 |
| ssl_key       | server-key.pem  |
+---------------+-----------------+
9 rows in set (0.02 sec)

上記の情報をもとに、以下のコマンド１～３を実行し、pemからjksを作成します。

１　keytool -import -file ./ca.pem -alias elastic1 -keystore elastickey.jks
２　penssl pkcs12 -export -inkey ./server-key.pem -in ./server-cert.pem -name elastic -out ./temp.p12
３　keytool -importkeystore -srckeystore ./temp.p12 -srcstoretype pkcs12 -destkeystore elastickey.jks

１の　./ca.pemは、SQL結果のssl_ca
　　　　elastic1　は適当な別名
　　　　elastickey.jks　は作成するjksファイル名

２の　./server-key.pem　は、SQL結果のssl_key
　　　./server-cert.pem　は、SQL結果のssl_cert
　　　elastic　は適当な別名（１とは別にしました。）

３の　elastickey.jks　は１のjksファイル名と同じ

作成する上で、2度パスワードを設定しますが、両方とも同じにして、下記、４の設定ファイルでセットする
パスワード（YYYYYY）と同じにしました。

作成された、elastickey.jks　を、下の４で設定する、AWSインスタンス側に保存しました。
（/home/ubuntu/elastickey.jks）

４．logstashの設定ファイルを編集

　/etc/logstash/conf.d/ に/etc/logstash/logstash-sample.conf を、logstash.confとしてコピーして、
　以下のように記述を変更。

input {
  jdbc {
    jdbc_driver_library => "/usr/share/logstash/mysql-connector-java-8.0.12.jar"
    jdbc_driver_class => "com.mysql.cj.jdbc.Driver"
　　jdbc_connection_string => 'jdbc:mysql://x.x.x.x:3306/model_on?useSSL=true&requireSSL=true&verifyServerCertificate=true&trustCertificateKeyStoreUrl=file:///home/ubuntu/elastickey.jks&trustCertificateKeyStoreType=JKS&trustCertificateKeyStorePassword=YYYYYY'
 (x.x.x.x は本番DBのIP、その他のパラメータはSSL接続時に必要な設定項目）
    jdbc_user => "xxxxxxxx"　（MYSQLサーバーへ接続するMYSQLのユーザーID）
    jdbc_password => "xxxxxxxx"　（そのパスワード）
statement => ""　（ここにDBから抽出するSelect文）
type => "download"
  }
}

output {
  if [type] == "download" {
    elasticsearch {
      manage_template => false
      hosts => ["localhost:9200"]
      index => ""　（RDBでいうところのElasticsearch側のテーブル　任意に指定可能）
      document_type => "%{type}"
      document_id => "%{id}"
    }
    stdout {codec => rubydebug {metadata => true }}
  }
}

５．logstashの設定反映

　AWSインスタンスからDBサーバーへ接続できるようにするために、DBサーバー側で以下のSQLを実行しておく必要があります。

　mysql> grant all privileges on [データベース名].* to [user名]@"接続元IP" identified by 'パスワード' with grant option;
（user名は、４の設定内で登録したユーザー）

上記設定を追加後、以下のコマンドを実行し、４の設定ファイルを反映、実行。
　sudo /usr/share/logstash/bin/logstash -f /etc/logstash/conf.d/logstash.conf
　
その結果、AWSのElasticsearch側に、DBサーバーからselectで抽出したデータがSSL通信で送信され、indexが作成されます。
セキュリティを確保するために、接続するユーザーは必ずSSL接続という条件でユーザー登録ができます。
（mysql> create user user1 identified by 'Password' require ssl;）

あとは、tcpdumpで実際にSSL通信できているか確認してください。
サーバー側で、　sudo tcpdump -X port 3306