こんにちは。インサイトテクノロジーの松尾です！
本投稿では、Insight SQL Testing で Aurora MySQL のバージョンアップ互換性確認テストを行う際にDatabase Activity Streams(DAS, データベースアクティビティストリーム)へ出力された情報を使用する方法を紹介します！
本投稿自体はノウハウ的なものの紹介になりますので、Insight SQL Testing を使った Aurora MySQL のバージョンアップ互換性確認テストの全般的な話題については以下の投稿を参照いただければと思います。
また、Database Activity Streamsではなく通常の監査ログを用いたい場合は、以下を参照ください。
はじめに
Insight SQL Testing で Aurora MySQL のバージョンアップ互換性確認テストを行う際には、一般ログ(general log)の出力を有効にし、そこで出力されたクエリの情報からSQLを抽出します。
ご存じのように Database Activity Streams でも SQL の情報が出力されます。さて、代用は可能でしょうか？
なお、Database Activity Streamsを使用するには、サポートしているバージョン(Aurora MySQL 2であれば2.11以降)やインスタンスタイプ(t系はサポート対象外)である必要があります。ご注意ください。
一般ログに出力されるクエリとDatabase Activity Streamsに出力されるクエリの違い
一般ログとDatabase Activity Streamsの出力とは以下のような違いがあります。
- Aurora MySQLではDatabase Activity Streamsの非同期モードのみをサポート
- 非同期モードでは負荷をかけないように動作するもののクエリを全部取得できない場合がある
- 「改行を伴うクエリ」の出力について一般ログとは以下のような相違
- 一般ログ：そのまま出力
- Database Activity Streams：一行にして出力(※監査ログと同じ)
同期モード/非同期モードについてはAWSドキュメントを参照ください。(※Aurora MySQLでは非同期モードのみ)
後者の改行のあるクエリについては実例を見てみましょう。
Dbeaverから以下のようなクエリを実行してみます。
一般ログとDatabase Activity Streamsではそれぞれ以下のように出力され、Database Activity Streamsの出力では、どこまでがコメントかがわからなくなります。
一般ログ：
2024-03-24T01:49:27.073572Z 419 Query /* ApplicationName=DBeaver 24.0.0 - SQLEditor <Script-7.sql> */ select
*
-- line comment
from
emp
LIMIT 0, 200
Database Activity Streams：
{
"type": "DatabaseActivityMonitoringRecord",
"clusterId": "cluster-XXXXXXXXXXXXXXXXXXXXXXXXXX",
"instanceId": "db-XXXXXXXXXXXXXXXXXXXXXXXXXX",
"databaseActivityEventList": [
{
"logTime": "2024-03-24 10:00:19.292274+00",
"type": "record",
"clientApplication": null,
"pid": 267,
"dbUserName": "user1",
"databaseName": "demo1",
"remoteHost": "xx,xx,xx,xx",
"remotePort": "5345",
"command": "QUERY",
"commandText": "/* ApplicationName=DBeaver 24.0.0 - SQLEditor <Script-7.sql> */ select * -- line comment from emp LIMIT 0, 200",
"paramList": null,
"objectType": "TABLE",
"objectName": "",
"statementId": 316869,
"substatementId": 1,
"exitCode": "0",
"sessionId": "33636",
"rowCount": 0,
"serverHost": "xxxxxxxxxxxxxxxxxxxxxx",
"serverType": "MySQL",
"serviceName": "Amazon Aurora MySQL",
"serverVersion": "5.7.mysql_aurora.2.11.4",
"startTime": "2024-03-24 10:00:19.292200+00",
"endTime": "2024-03-24 10:00:19.292275+00",
"transactionId": "0",
"dbProtocol": "MySQL",
"netProtocol": "TCP",
"errorMessage": "",
"class": "MAIN"
}
]
}
とはいえ、Database Activity Streamsの出力を使いたい
とはいえ、以下のような場合に、Database Activity Streamsの出力を利用したいというケースはあると思います。
- Database Activity Streamsで監査情報を既に取得しているような場合
- DBサーバーへの負荷を減らしたい場合
- 多少の取りこぼしを許容できる場合
そこで本投稿では、Database Activity Streamsの出力を Insight SQL Testing の評価SQLセット(テストに使用するSQL)として取り込む方法を紹介します。
Aurora MySQL のDatabase Activity Streamsの出力を評価SQLセットとして使用するには
この投稿の執筆時点(2024年3月時点)ではAurora MySQL のDatabase Activity Streamsの出力を評価SQLセットとして直接取り込む機能はありません。そのため、評価SQLセットをCSVファイルから作成する機能を使用します。
CSVファイルの形式と必要なカラムはあらかじめ決められており、Database Activity Streamsの出力からそのCSVファイルを作成します。
※なおマニュアルに記載のある「マイニングサーチ出力CSV形式」というのは弊社独自の用語で一般用語ではありません
処理自体は単純ですが面倒な処理なので、Pythonで簡単なスクリプトを作成しました。
大まかな処理の流れとしては以下のようになっています。
- 前提：Database Activity Streamsの出力をテキストファイルで取得できている
- 処理
- ログイン日時情報の作成 (CONNECTの情報、なければ、SQLの一番最初の時刻、を使用)
- SQL情報の作成と出力
Database Activity Streamsの出力をテキストファイルで取得
ところで、Database Activity Streams は単純にオプションを有効にするだけでは、Kinesis stream へデータが垂れ流されるだけとなます。そこからテキストファイルへ出力したりS3へ出力させたりするには作りこみが必要となります。
本投稿では、以下の記事で紹介されている、Kinesis stream へアクセスして内容を標準出力へ出す Python スクリプトを少しだけ修正したものを用意したのでそれを利用してDatabase Activity Streamsの出力をテキストファイルで用意します。コードは先ほど紹介した Github のレポジトリに入れてあります。
python3 das_to_json.py cluster-XXXXXXXXXXXXXXXXXXXX | tee das_json_file.txt
実行すると以下のようなjsonの羅列がファイルへ出力されます。(プログラムをCtrl+Cなどで終了するまで出力され続けます)
{"type":"DatabaseActivityMonitoringRecord","clusterId":"cluster-XXXXXXXXXXXXXX","instanceId":"db-XXXXXXXXXXXXXX","databaseActivityEventList":[{"logTime":"2024-03-24 10:00:02.740463+00","type":"record","clientApplication":null,"pid":267,"dbUserName":"user1","databaseName":"demo1","remoteHost":"xx.xx.xx.xx","remotePort":"5345","command":"CONNECT","commandText":"","paramList":null,"objectType":"TABLE","objectName":"","statementId":0,"substatementId":1,"exitCode":"0","sessionId":"33632","rowCount":0,"serverHost":"XXXXXXXXXXXXXX","serverType":"MySQL","serviceName":"Amazon Aurora MySQL","serverVersion":"5.7.mysql_aurora.2.11.4","startTime":"2024-03-24 10:00:02.740367+00","endTime":"2024-03-24 10:00:02.740463+00","transactionId":"0","dbProtocol":"MySQL","netProtocol":"TCP","errorMessage":"","class" : "MAIN"},{"logTime":"2024-03-24 10:00:02.852526+00","type":"record","clientApplication":null,"pid":267,"dbUserName":"user1","databaseName":"demo1","remoteHost":"xx.xx.xx.xx","remotePort":"5345","command":"QUERY","commandText":"SET sql_mode='STRICT_TRANS_TABLES'","paramList":null,"objectType":"TABLE","objectName":"","statementId":316673,"substatementId":1,"exitCode":"0","sessionId":"33632","rowCount":0,"serverHost":"XXXXXXXXXXXXXX","serverType":"MySQL","serviceName":"Amazon Aurora MySQL","serverVersion":"5.7.mysql_aurora.2.11.4","startTime":"2024-03-24 10:00:02.852441+00","endTime":"2024-03-24 10:00:02.852527+00","transactionId":"0","dbProtocol":"MySQL","netProtocol":"TCP","errorMessage":"","class" : "MAIN"}]}
{"type":"DatabaseActivityMonitoringRecord","clusterId":"cluster-XXXXXXXXXXXXXX","instanceId":"db-XXXXXXXXXXXXXX","databaseActivityEventList":[{"logTime":"2024-03-24 10:00:02.935598+00","type":"record","clientApplication":null,"pid":267,"dbUserName":"user1","databaseName":"demo1","remoteHost":"xx.xx.xx.xx","remotePort":"5345","command":"QUERY","commandText":"/* ApplicationName=DBeaver 24.0.0 - Metadata */ use demo1","paramList":null,"objectType":"TABLE","objectName":"","statementId":316676,"substatementId":1,"exitCode":"0","sessionId":"33632","rowCount":0,"serverHost":"XXXXXXXXXXXXXX","serverType":"MySQL","serviceName":"Amazon Aurora MySQL","serverVersion":"5.7.mysql_aurora.2.11.4","startTime":"2024-03-24 10:00:02.935428+00","endTime":"2024-03-24 10:00:02.935599+00","transactionId":"0","dbProtocol":"MySQL","netProtocol":"TCP","errorMessage":"","class" : "MAIN"},{"logTime":"2024-03-24 10:00:03.106562+00","type":"record","clientApplication":null,"pid":267,"dbUserName":"user1","databaseName":"demo1","remoteHost":"xx.xx.xx.xx","remotePort":"5345","command":"QUERY","commandText":"SET character_set_results = NULL","paramList":null,"objectType":"TABLE","objectName":"","statementId":316679,"substatementId":1,"exitCode":"0","sessionId":"33633","rowCount":0,"serverHost":"XXXXXXXXXXXXXX","serverType":"MySQL","serviceName":"Amazon Aurora MySQL","serverVersion":"5.7.mysql_aurora.2.11.4","startTime":"2024-03-24 10:00:03.106432+00","endTime":"2024-03-24 10:00:03.106562+00","transactionId":"0","dbProtocol":"MySQL","netProtocol":"TCP","errorMessage":"","class" : "MAIN"},{"logTime":"2024-03-24 10:00:03.195852+00","type":"record","clientApplication":null,"pid":267,"dbUserName":"rdsadmin","databaseName":"","remoteHost":"localhost","remotePort":"5345","command":"CONNECT","commandText":"","paramList":null,"objectType":"TABLE","objectName":"","statementId":0,"substatementId":1,"exitCode":"0","sessionId":"33634","rowCount":0,"serverHost":"XXXXXXXXXXXXXX","serverType":"MySQL","serviceName":"Amazon Aurora MySQL","serverVersion":"5.7.mysql_aurora.2.11.4","startTime":"2024-03-24 10:00:03.170339+00","endTime":"2024-03-24 10:00:03.195851+00","transactionId":"0","dbProtocol":"MySQL","netProtocol":"TCP","errorMessage":"","class" : "MAIN"}]}
{"type":"DatabaseActivityMonitoringRecord","clusterId":"cluster-XXXXXXXXXXXXXX","instanceId":"db-XXXXXXXXXXXXXX","databaseActivityEventList":[{"logTime":"2024-03-24 10:00:02.962786+00","type":"record","clientApplication":null,"pid":267,"dbUserName":"user1","databaseName":"demo1","remoteHost":"xx.xx.xx.xx","remotePort":"5345","command":"DISCONNECT","commandText":"","paramList":null,"objectType":"TABLE","objectName":"","statementId":0,"substatementId":1,"exitCode":"0","sessionId":"33629","rowCount":0,"serverHost":"XXXXXXXXXXXXXX","serverType":"MySQL","serviceName":"Amazon Aurora MySQL","serverVersion":"5.7.mysql_aurora.2.11.4","startTime":"2024-03-24 10:00:02.962737+00","endTime":"2024-03-24 10:00:02.962786+00","transactionId":"0","dbProtocol":"MySQL","netProtocol":"TCP","errorMessage":"","class" : "MAIN"},{"logTime":"2024-03-24 10:00:03.134318+00","type":"record","clientApplication":null,"pid":267,"dbUserName":"user1","databaseName":"demo1","remoteHost":"xx.xx.xx.xx","remotePort":"5345","command":"QUERY","commandText":"SET autocommit=1","paramList":null,"objectType":"TABLE","objectName":"","statementId":316680,"substatementId":1,"exitCode":"0","sessionId":"33633","rowCount":0,"serverHost":"XXXXXXXXXXXXXX","serverType":"MySQL","serviceName":"Amazon Aurora MySQL","serverVersion":"5.7.mysql_aurora.2.11.4","startTime":"2024-03-24 10:00:03.134210+00","endTime":"2024-03-24 10:00:03.134319+00","transactionId":"0","dbProtocol":"MySQL","netProtocol":"TCP","errorMessage":"","class" : "MAIN"},{"logTime":"2024-03-24 10:00:03.220004+00","type":"record","clientApplication":null,"pid":267,"dbUserName":"user1","databaseName":"demo1","remoteHost":"xx.xx.xx.xx","remotePort":"5345","command":"QUERY","commandText":"/* ApplicationName=DBeaver 24.0.0 - SQLEditor <Script-7.sql> */ SET autocommit=1","paramList":null,"objectType":"TABLE","objectName":"","statementId":316684,"substatementId":1,"exitCode":"0","sessionId":"33633","rowCount":0,"serverHost":"XXXXXXXXXXXXXX","serverType":"MySQL","serviceName":"Amazon Aurora MySQL","serverVersion":"5.7.mysql_aurora.2.11.4","startTime":"2024-03-24 10:00:03.219920+00","endTime":"2024-03-24 10:00:03.220004+00","transactionId":"0","dbProtocol":"MySQL","netProtocol":"TCP","errorMessage":"","class" : "MAIN"}]}
{"type":"DatabaseActivityMonitoringRecord","clusterId":"cluster-XXXXXXXXXXXXXX","instanceId":"db-XXXXXXXXXXXXXX","databaseActivityEventList":[{"logTime":"2024-03-24 10:00:03.250164+00","type":"record","clientApplication":null,"pid":267,"dbUserName":"user1","databaseName":"demo1","remoteHost":"xx.xx.xx.xx","remotePort":"5345","command":"QUERY","commandText":"/* ApplicationName=DBeaver 24.0.0 - SQLEditor <Script-7.sql> */ use demo1","paramList":null,"objectType":"TABLE","objectName":"","statementId":316687,"substatementId":1,"exitCode":"0","sessionId":"33633","rowCount":0,"serverHost":"XXXXXXXXXXXXXX","serverType":"MySQL","serviceName":"Amazon Aurora MySQL","serverVersion":"5.7.mysql_aurora.2.11.4","startTime":"2024-03-24 10:00:03.250029+00","endTime":"2024-03-24 10:00:03.250164+00","transactionId":"0","dbProtocol":"MySQL","netProtocol":"TCP","errorMessage":"","class" : "MAIN"},{"logTime":"2024-03-24 10:00:03.420272+00","type":"record","clientApplication":null,"pid":267,"dbUserName":"user1","databaseName":"demo1","remoteHost":"xx.xx.xx.xx","remotePort":"5345","command":"QUERY","commandText":"SET autocommit=1","paramList":null,"objectType":"TABLE","objectName":"","statementId":316690,"substatementId":1,"exitCode":"0","sessionId":"33635","rowCount":0,"serverHost":"XXXXXXXXXXXXXX","serverType":"MySQL","serviceName":"Amazon Aurora MySQL","serverVersion":"5.7.mysql_aurora.2.11.4","startTime":"2024-03-24 10:00:03.420191+00","endTime":"2024-03-24 10:00:03.420273+00","transactionId":"0","dbProtocol":"MySQL","netProtocol":"TCP","errorMessage":"","class" : "MAIN"}]}
なお、自動でS3に保存したい場合は、以下の記事などが参考になると思います。(試してません)
スクリプトを使った処理の実施例
前述の処理やS3の自動保存などで、上記出力の形式でテキストファイルとして保存したもの用意します。
python3 aumy_das_json_to_mscsv.py das_json_file.txt output.csv
output.csvの内容
"Host","Database","SID","Serial","Logged In","Logged Out","DB User","SQL Start Time","SQL Start Time(Micro Sec)","SQL Text","Bind Variables","Object","Elapsed Time","Program","Client Information - Host"
"XXXXXXXXXXXXXX","demo1","33628","","20240324100001","20240324100002","user1","20240324100001","942541","SET autocommit=1","","","","","xx.xx.xx.xx"
"XXXXXXXXXXXXXX","demo1","33627","","20240324100001","20240324100002","user1","20240324100001","590161","/* mysql-connector-j-8.2.0 (Revision: 06a1f724497fd81c6a659131fda822c9e5085b6c) */SELECT @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout","","","","","xx.xx.xx.xx"
"XXXXXXXXXXXXXX","demo1","33627","","20240324100001","20240324100002","user1","20240324100001","703146","SHOW WARNINGS","","","","","xx.xx.xx.xx"
"XXXXXXXXXXXXXX","demo1","33629","","20240324100002","20240324100002","user1","20240324100002","207029","SET character_set_results = NULL","","","","","xx.xx.xx.xx"
できた output.csv を Insight SQL Testing で取り込みます。
以下のように評価SQLセットとしてCSVを取り込むことができました。
もちろん、この評価SQLセットを使ってアセスメントを実行すると、いつものようにアセスメント結果を見ることができます。
また、先述の行コメントが SQL にある場合はどうなるでしょうか？
これは SQL として要素が足りないものとなりエラーになりました。どのように行コメントが記述されていたかによると思いますが、場合によっては、実行できてしまうけど、本来のSQLとは異なる、というケースもあるかもしれません。実際に行コメントを使っているSQLがないかは、--でフィルタなどをして検索して確認する必要がありそうです。
おわりに
本投稿では、Insight SQL Testing で Aurora MySQL のバージョンアップ互換性確認テストを行う際にDatabase Activity Streams(DAS, データベースアクティビティストリーム)へ出力された情報を使用する方法を紹介しました。Database Activity Streamsを既に取得している場合や、DBへの負荷を減らしたい場合などに可能な選択肢となると思います。利用にあたっては、上述のような制限があることも念頭におき、ご利用をご検討ください。
更新情報
- 2024/4/1: Insight Database Testingの製品名がInsight SQL Testingへ変更になったのでそれにあわせて更新しました