SSL – variable.jp [データベース,パフォーマンス,運用]

MySQLのmysqlbinlogコマンドでは、MySQL5.6からリモートにあるバイナリーログを読み取る事が可能になりましたが、SSLを利用した通信の暗号化はサポートされておりませんでした。

次期メジャーバージョンのMySQL5.7では、ここら辺も機能拡張されておりmysqlbinlogコマンドを利用して、リモートのバイナリーログを読み込み場合にもSSLを利用した通信の暗号化を行う事が出来るようになりました。
Firewall、セキュリティ機器、セグメント分割された環境で、何処まで必要になるかは状況次第ですが、
リモートサーバーが自然災害対策の為に、自分でインフラを準備する必要が無いPublicクラウド上にある場合などに通信の暗号化が出来ると安心ですね。
※　MySQLのレプリケーションでは、以前からSSLを利用した通信の暗号化が可能です。

参照：　
MySQL 5.7: mysqlbinlog now supports SSL

MySQL5.7 mysqlbinlogマニュアル
4.6.7 mysqlbinlog — Utility for Processing Binary Log Files

MySQL5.6: mysqlbinlogコマンドでリモートデータベースからログを読みこむ機能の確認

4.6.8. mysqlbinlog — バイナリログファイルを処理するためのユーティリティー

[環境] リモートからのログ読み込み基本動作確認
MySQL： 5.6.24　（SSLの確認は別途）

特定ログファイルの読み込み

ログ取得元サーバー


[admin@GA01 ~]$ mysql -u root -p -e 'show master logs';
Enter password: 
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000117 |   1636700 |
| mysql-bin.000118 |       844 |
| mysql-bin.000119 |       222 |
| mysql-bin.000120 |       518 |
| mysql-bin.000121 |       470 |
| mysql-bin.000122 |       222 |
+------------------+-----------+
[admin@GA01 ~]$ ls -l /usr/local/mysql/data/mysql-bin.000117
-rw-rw----. 1 mysql mysql 1636700  7月  6 10:21 /usr/local/mysql/data/mysql-bin.000117
[admin@GA01 ~]$

ログ読み込みクライアントサーバー
ログが読みこまれているのが確認出来る。バイナリーのまま持ってくる場合は、
”–raw”オプションを付けてあげれば良い。


[admin@misc01 tmp]$ /usr/local/mysql/bin/mysqlbinlog --read-from-remote-server \
> --host=192.168.56.201 --user=admin --password  \
> mysql-bin.000117 > RemoteLogBackup_`date "+%Y%m%d_%H%M%S"`.sql
Enter password: 
[admin@misc01 tmp]$ ls -l
合計 3632
-rw-rw-r--. 1 admin admin 3715955  7月  9 11:06 RemoteLogBackup_20150709_110610.sql
[admin@misc01 tmp]$ cat RemoteLogBackup_20150709_110610.sql | grep CREATE
CREATE DATABASE `world` /*!40100 DEFAULT CHARACTER SET utf8 */
CREATE TABLE `City` (
CREATE TABLE `Country` (
CREATE TABLE `CountryLanguage` (
CREATE DATABASE `audit_information`  /*!40100 DEFAULT CHARACTER SET utf8 */
CREATE DATABASE `audit_information`  /*!40100 DEFAULT CHARACTER SET utf8mb4 */
CREATE TABLE `audit_parsed` (
CREATE TABLE `audit_history` (
[admin@misc01 tmp]$

指定したログファイル以降のログも全て持ってくる場合


[admin@misc01 tmp]$ /usr/local/mysql/bin/mysqlbinlog --read-from-remote-server \
> --host=192.168.56.201 --user=admin --password  --to-last-log \
> mysql-bin.000117 > RemoteLogBackup_`date "+%Y%m%d_%H%M%S"`.sql
Enter password: 
[admin@misc01 tmp]$ ls -l
合計 7268
-rw-rw-r--. 1 admin admin 3715955  7月  9 11:06 RemoteLogBackup_20150709_110610.sql
-rw-rw-r--. 1 admin admin 3722407  7月  9 11:46 RemoteLogBackup_20150709_114625.sql
[admin@misc01 tmp]$ cat RemoteLogBackup_*.sql | grep CREATE
CREATE DATABASE `world` /*!40100 DEFAULT CHARACTER SET utf8 */
CREATE TABLE `City` (
CREATE TABLE `Country` (
CREATE TABLE `CountryLanguage` (
CREATE DATABASE `audit_information`  /*!40100 DEFAULT CHARACTER SET utf8 */
CREATE DATABASE `audit_information`  /*!40100 DEFAULT CHARACTER SET utf8mb4 */
CREATE TABLE `audit_parsed` (
CREATE TABLE `audit_history` (
CREATE DATABASE `world` /*!40100 DEFAULT CHARACTER SET utf8 */
CREATE TABLE `City` (
CREATE TABLE `Country` (
CREATE TABLE `CountryLanguage` (
CREATE DATABASE `audit_information`  /*!40100 DEFAULT CHARACTER SET utf8 */
CREATE DATABASE `audit_information`  /*!40100 DEFAULT CHARACTER SET utf8mb4 */
CREATE TABLE `audit_parsed` (
CREATE TABLE `audit_history` (
[admin@misc01 tmp]$

ログの中身を確認してみると、先頭行のポジションが同じである事が確認出来る。


[admin@misc01 tmp]$ cat RemoteLogBackup_20150709_110610.sql | grep \# | head 
# at 4
#290328 15:21:27 server id 1  end_log_pos 120 CRC32 0x3a600c07  Start: binlog v 4, server v 5.6.24-enterprise-commercial-advanced-log created 290328 15:21:27 at startup
# at 120
#290328 15:21:27 server id 1  end_log_pos 151 CRC32 0xe9d5507f  Previous-GTIDs
# [empty]
# at 151
#380119 12:14:09 server id 1  end_log_pos 222 CRC32 0x5aae6a2b  Previous-GTIDs
# 8560c2ac-e1dc-11e4-88ff-0800275399c1:1-612
# at 222
#150703 11:57:42 server id 1  end_log_pos 270 CRC32 0xb9607b03  GTID    last_committed=0        sequence_number=0

[admin@misc01 tmp]$ cat RemoteLogBackup_20150709_114625.sql | grep \# | head 
# at 4
#700101  9:00:00 server id 1  end_log_pos 0     Rotate to mysql-bin.000117  pos: 4
# at 4
#290328 15:21:27 server id 1  end_log_pos 120 CRC32 0x3a600c07  Start: binlog v 4, server v 5.6.24-enterprise-commercial-advanced-log created 290328 15:21:27 at startup
# at 120
#290328 15:21:27 server id 1  end_log_pos 151 CRC32 0xe9d5507f  Previous-GTIDs
# [empty]
# at 151
#380119 12:14:09 server id 1  end_log_pos 222 CRC32 0x5aae6a2b  Previous-GTIDs
# 8560c2ac-e1dc-11e4-88ff-0800275399c1:1-612
[admin@misc01 tmp]$

ログの中身を確認してみると、特定のログ以降の全てのログを読みこんだログは、
最終変更までのバイナリーログが読みこまれている事が確認出来る。

[admin@misc01 tmp]$ cat RemoteLogBackup_20150709_110610.sql | grep \# | tail -n 20
#150703 19:06:58 server id 1  end_log_pos 1634541 CRC32 0x49844ce7      GTID    last_committed=0        sequence_number=0
# at 1634541
#150703 19:06:58 server id 1  end_log_pos 1634716 CRC32 0x271b8e30      Query   thread_id=6     exec_time=0     error_code=0
# at 1634716
#150703 19:07:50 server id 1  end_log_pos 1634764 CRC32 0x92c5292d      GTID    last_committed=0        sequence_number=0
# at 1634764
#150703 19:07:50 server id 1  end_log_pos 1634875 CRC32 0x1b73a468      Query   thread_id=6     exec_time=0     error_code=0
# at 1634875
#150703 19:08:03 server id 1  end_log_pos 1634923 CRC32 0x62aa0722      GTID    last_committed=0        sequence_number=0
# at 1634923
#150703 19:08:03 server id 1  end_log_pos 1635101 CRC32 0x10ed801d      Query   thread_id=6     exec_time=0     error_code=0
# at 1635101
#150703 19:08:21 server id 1  end_log_pos 1635149 CRC32 0x805c979c      GTID    last_committed=0        sequence_number=0
# at 1635149
#150703 19:08:21 server id 1  end_log_pos 1636236 CRC32 0x78f51539      Query   thread_id=6     exec_time=0     error_code=0
# at 1636236
#150703 19:08:41 server id 1  end_log_pos 1636284 CRC32 0xf6128e4b      GTID    last_committed=0        sequence_number=0
# at 1636284
#150703 19:08:41 server id 1  end_log_pos 1636700 CRC32 0xeda14d3e      Query   thread_id=6     exec_time=0     error_code=0
# End of log file
[admin@misc01 tmp]$ cat RemoteLogBackup_20150709_114625.sql | grep \# | tail -n 20
# at 151
#380119 12:14:09 server id 1  end_log_pos 222 CRC32 0x1bbf07f9  Previous-GTIDs
# 8560c2ac-e1dc-11e4-88ff-0800275399c1:1-5933
# at 222
#150708 14:30:37 server id 1  end_log_pos 270 CRC32 0x7fdb0132  GTID    last_committed=0        sequence_number=0
# at 270
#150708 14:30:35 server id 1  end_log_pos 370 CRC32 0xdc99f8eb  Query   thread_id=1     exec_time=0     error_code=0
# at 370
#150708 14:30:35 server id 1  end_log_pos 470 CRC32 0xa8b91492  Query   thread_id=1     exec_time=0     error_code=0
# at 4
#700101  9:00:00 server id 1  end_log_pos 0 CRC32 0xc2515710    Rotate to mysql-bin.000122  pos: 4
# at 4
#700101  9:00:00 server id 1  end_log_pos 120 CRC32 0x07fa7f88  Start: binlog v 4, server v 5.6.24-enterprise-commercial-advanced-log created 700101  9:00:00
# at 120
#700101  9:00:00 server id 1  end_log_pos 151 CRC32 0x218282e8  Previous-GTIDs
# [empty]
# at 151
#380119 12:14:09 server id 1  end_log_pos 222 CRC32 0xd7150767  Previous-GTIDs
# 8560c2ac-e1dc-11e4-88ff-0800275399c1:1-5934
# End of log file
[admin@misc01 tmp]$

リモートデータベースからログを読み込み、定期的に遠隔地に保存してしておく方法の一つとしても使えそうです。

サイト運営でドメイン有効期限、SSL有効期限は忘れないようにしなければいけない。
業者に自動更新を頼んでおくと自動更新してもらえるが、個人で運営している場合は自分で工夫して運用する必要あり。

$ echo "" | openssl s_client -connect ssl.kakaku.com:443 | openssl x509 -enddate -noout | sed 's/notAfter\=//'

$ echo "" | openssl s_client -connect ssl.kakaku.com:443 | openssl x509 -enddate -noout
depth=3 C = US, O = "VeriSign, Inc.", OU = Class 3 Public Primary Certification Authority
verify return:1
depth=2 C = US, O = "VeriSign, Inc.", OU = VeriSign Trust Network, OU = "(c) 2006 VeriSign, Inc. - For authorized use only", CN = VeriSign Class 3 Public Primary Certification Authority - G5
verify return:1
depth=1 C = US, O = "VeriSign, Inc.", OU = VeriSign Trust Network, OU = Terms of use at https://www.verisign.com/rpa (c)10, CN = VeriSign Class 3 International Server CA - G3
verify return:1
depth=0 C = JP, ST = Tokyo, L = Shibuya-ku, O = kakaku.com Inc, OU = Technology Platform Dept, OU = Terms of use at www.verisign.com/rpa (c)05, CN = ssl.kakaku.com
verify return:1
DONE
notAfter=Apr  9 23:59:59 2013 GMT
$
$ echo "" | openssl s_client -connect google.co.jp:443 | openssl x509 -enddate -noout
depth=2 C = US, O = Equifax, OU = Equifax Secure Certificate Authority
verify return:1
depth=1 C = US, O = Google Inc, CN = Google Internet Authority
verify return:1
depth=0 C = US, ST = California, O = Google Inc, CN = google.com
verify return:1
DONE
notAfter=Jan 17 12:55:04 2013 GMT
$

参考：
[Nagios] : Check SSL Certificate Expiration Date

Zabbix Fun – Tracking SSL Certificate Expiration Times

Tag Archives: Ssl

mysqlbinlogによるリモートバイナリーログ活用

SSL有効期限確認