LoginSignup
4
0

More than 1 year has passed since last update.

トラストバンクAdvent Calendar 2022Day 3trustbank
@karura618(minato kenichiro)in株式会社トラストバンク

WinとMacで使える調査用のバッチを作った話

Last updated at Posted at 2022-12-02

はじめに

はじめまして株式会社トラストバンクでCTO室に所属してます湊（みなと）（@karura618）です。
人生初Qiita投稿なのでドキドキしてます。

何をしたか

問いあわせ調査などでDBのログを収集する機会が増えてきたので対話形式で必要な情報を入力すればSQLを実行し、実行結果をTSV形式で手元にダウンロードするバッチを作りました。
（SELECT INTO OUTFILE を使えればCSVファイルでの取得がもう少し簡単に作れるんですが運用上NGなため、後述の通りSQLの実行結果をほぼそのままTSVで取得してます。）

実施したこと

以下を自動化するシェルスクリプトを作ってみました。
（１）踏み台サーバを経由してDBサーバに接続する
（２）DBサーバのフォームディレクトリに、ローカル上にあるSQLファイルをアップロード
（３）DBサーバにてDBへアクセス。調査用のSQLファイルを実行
（４）実行結果をTSVファイルに出力
（５）TSVファイル内の余分な改行を削除。
（６）5のファイルを自身のPCにダウンロードする（重要）
（７）不要になったサーバ上のホームディレクトリにあるSQLファイルやTSVファイルを削除

特に、（１）と（６）が毎回手間なので、必要情報を入力したらあとはよしなにやってくれるようにしてます。

作ったものをどん。

で、作ったものがこちら。

私自身はWindowsを使っているのでteratermマクロ使うのも検討したのですが、社内エンジニアの多くがMacユーザーなため両方のOSで使えるようにDockerで本処理専用のコンテナを用意してみました。

ディレクトリ構成

C:.
│ docker-compose.yml
│ README.md
│
├─app
│ get.sql ← 実⾏したいSQL⽂を記載
│ get_query_result.sh ← SQL実⾏⽤シェルスクリプト
│
├─docker
│ Dockerfile
│
└─download
  result.tsv ← 実⾏したSQLの結果ファイル（TSV形式）

構成ファイル

docker-compose.yml

取得したクエリ結果をホストから直接参照したいので
コンテナ側で取得先となるディレクトリをホストのディレクトリとvolumesマウントしてます。

docker-compose.yml
version: '3.4'
services:
    app:
        build:
            context: ./
            dockerfile: ./docker/Dockerfile
        logging:
            driver: "json-file"
            options:
                max-size: "10m"
                max-file: "1"
        stdin_open: true
        volumes:
            - './app:/home/app'
            - './download:/home/download'

docker/Dockerfile

Bashが動く最低限の構成なので短いです。

FROM alpine:3.14

RUN apk upgrade --update \
 && apk --no-cache add bash libzip-dev autoconf build-base zip expect openssh

RUN set -eux; \
  mkdir -p "/home/app" \
  mkdir -p "/home/download"


WORKDIR /home/app

survey/get_query_result.sh

踏み台経由でDBサーバに接続するためちょっと作りが複雑

#!/bin/bash

IP1="〇〇〇.〇〇〇.〇〇〇.〇〇〇"
IP2="△△△.△△△.△△△.△△△"

REPLACE_COMMAND="sed -i -z 's/\\\r//g'"

while :
do
  read -p "ログインするユーザー名を入力してください。: " USER
  if [ "$USER" != "" ]; then
    break
  fi
done

while :
do
  read -s -p "SSHのパスワードを入力してください。: " PASS1
  if [ "$PASS1" != "" ]; then
    break
  fi
  echo ""
done
echo ""

while :
do
  read -s -p "MySQLのパスワードを入力してください。: " PASS2
  if [ "$PASS2" != "" ]; then
    break
  fi
  echo ""
done
echo ""

while :
do
  read -p "実行するSQLファイル名を入力してください。: " SQL_FILE
  if [ "$SQL_FILE" != "" ]; then
    if [ -e "$SQL_FILE" ]; then
      break
    else
      echo "$SQL_FILEが見つかりません。sqlディレクトリ配下に$SQL_FILEを配置してください。"
    fi      
  fi
done
echo ""


# （１）踏み台サーバを経由してDBサーバに接続する
# （２）DBサーバのフォームディレクトリに、ローカル上にあるSQLファイルをアップロード
expect -c "
spawn scp -r -o \"ProxyCommand ssh $USER@$IP1 -o UserKnownHostsFile=/dev/null -o StrictHostKeyChecking=no -W %h:%p\" -o UserKnownHostsFile=/dev/null -o StrictHostKeyChecking=no ./$SQL_FILE $USER@$IP2:/home/$USER
expect \"'s password:\"
send \"$PASS1\n\"
expect \"'s password:\"
send \"$PASS1\n\"
interact
"
# （３）DBサーバにてDBへアクセス。調査用のSQLファイルを実行
# （４）実行結果をTSVファイルに出力
# （５）TSVファイル内の余分な改行を削除。
expect -c "
set timeout 3600
spawn ssh -t $USER@$IP1 -o UserKnownHostsFile=/dev/null -o StrictHostKeyChecking=no \"ssh $USER@$IP2\"
expect \"password:\"
send \"$PASS1\n\"
expect \"password:\"
send \"$PASS1\n\"
expect \"Last login\"
send \"mysql -u $USER -p$PASS2 -h localhost database_name < /home/$USER/$SQL_FILE > /home/$USER/result.tsv\n\"
expect \"$USER\"
send \"$REPLACE_COMMAND /home/$USER/result.tsv\n\"
expect \"$USER\"
send \"exit\n\"
interact
"


# （６）5のファイルを自身のPCにダウンロードする（重要）
expect -c "
set timeout 3600
spawn scp -r -o \"ProxyCommand ssh $USER@$IP1 -o UserKnownHostsFile=/dev/null -o StrictHostKeyChecking=no -W %h:%p\" -o UserKnownHostsFile=/dev/null -o StrictHostKeyChecking=no $USER@$IP2:/home/$USER/result.tsv ../download
expect \"'s password:\"
send \"$PASS1\n\"
expect \"'s password:\"
send \"$PASS1\n\"
interact
"

# （７）不要になったサーバ上のホームディレクトリにあるSQLファイルやTSVファイルを削除
expect -c "
set timeout 600
spawn ssh -t $USER@$IP1 -o UserKnownHostsFile=/dev/null -o StrictHostKeyChecking=no \"ssh $USER@$IP2\"
expect \"password:\"
send \"$PASS1\n\"
expect \"password:\"
send \"$PASS1\n\"
expect \"Last login\"
send \"rm -rf /home/$USER/result.tsv\n\"
expect \"$USER\"
send \"rm -rf /home/$USER/$SQL_FILE\n\"
expect \"$USER\"
send \"exit\n\"
interact
"
echo 'SQLの実行結果ファイル「result.tsv」をダウンロードが完了しました。'

ポイント

ProxyCommand

ファイルのアップロード、ダウンロード時に「ProxyCommand」を使用。
踏み台サーバからDBサーバへのアクセスが毎回手間だったのでProxyCommandを使って多段SSH接続させてます。

StrictHostKeyChecking

上記ProxyCommandだけだとSCPやSSHでの接続の際に毎回確認メッセージが表示される（SCPの場合はメッセージが出ないため事前にknown_hostsに公開鍵を登録しておかないと接続できない。）。
このため、接続時にオプションとして「StrictHostKeyChecking=no」を指定し、ホスト鍵のチェックを無視しています。

UserKnownHostsFile

上記StrictHostKeyCheckingの指定だけだとホスト鍵が変更された場合にknown_hostsを編集する必要があります。
管理が面倒なのと、バッチ使用者にメンテナンスの手間を与えないためにそもそもknown_hostsへの書き込み自体を行いようにしています。

sed -i -z 's/\\r//g'

MySQLの実行結果をTSV出力すると、取得するテーブルによってはカラム内の値に改行が含まれていることがありエクセル等で開いたときにレイアウトがぐちゃぐちゃになります。

なので、取得したSQLの結果ファイルに対し、「sed -i -z 's/\\r//g'」を実行することでファイル内の不要な改行コードを削除しています。

終わりに

本記事書いてるときに知ったんですが最近はProxyCommandじゃなくProxyJumpを使うのが主流っぽい？
機会があったら試してみようと思います。

明日は @tb_yamamoto さんです！よろしくお願いいします！

ところで。。。

トラストバンクでは一緒に活躍いただけるエンジニアを募集中です！
https://www.wantedly.com/companies/trustbank/projects

参考サイト

security - ssh ProxyCommandを利用した多段ssh
security - sshのホスト鍵を無視する方法
karakaram-blog - https://www.karakaram.com/ssh-to-remote-hosts-through-a-bastion-with-proxyjump/

4
0
0

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
Sign upLogin
4
0