7
5

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

SSH経由でmysqlにアクセスしてクエリ実行結果をTSVに出力するShellサンプル

Posted at

【注意】初めてまともに作成したshellスクリプトです。
SSH経由でmysqlにアクセスしてクエリ実行結果をTSVに出力します(たぶん)

下記について 調べながら 実装しています

変数・コマンドライン引数
if文
外部ファイルの読み込み
コマンドの実行
配列&配列を動的に参照

参考にさせていただいたサイト

fetch_query.sh
#!/bin/sh
PATH_TO_OUTPUT_DIR="/xxxx/yyyy/zzzzz/"

## (SSH経由でmysqlにアクセスしてクエリ実行結果をTSVに出力するサンプル)

if [ $# -ne 3 ]; then
 echo "[ERROR]Set parameter [param1], [param2], [output tsv file prefix]"
 exit 1
fi

param1=$1
param2=$2
tsv_prefix=$3
tsv_ymd=`date "+%Y%m%d"`

## Load setting files
## (各種設定ファイルを読み込む)
CONFIG="dblist.conf"
FETCH_QUERY="fetch_query.sql"

if [[ ! -f ${CONFIG} ]]; then
  echo "[ERROR] Not found file ${CONFIG}"
  exit 1
fi
if [[ ! -f ${FETCH_QUERY} ]]; then
  echo "[ERROR] Not found file ${FETCH_QUERY}"
  exit 1
fi

. ./${CONFIG}
. ./${FETCH_QUERY}

echo "[INFO] === START === (`date`)"

result_data_tsv="${tsv_prefix}_${param1}_${param2}_${tsv_ymd}.tsv"
query=${FETCH_TARGET_SQL}

## Import from remote.
## (配列の参照)
param2_db_con_str=$(eval echo '${LIST_'${param1}'_CON_LIST['${param2}']}')

echo "[INFO] Import data to ${result_data_tsv} from connection (${param2_db_con_str})"
echo "${query}" | ssh ${SSH_STR} mysql -N -B ${param2_db_con_str} > ${PATH_TO_OUTPUT_DIR}${result_data_tsv}
echo "[INFO] === END === (`date`)"

exit 0
dblist.conf
## (ssh接続※config管理してる場合※)
SSH_STR="ssh-tool"

## (接続文字列リスト)
LIST_1_CON_LIST=()
LIST_1_CON_LIST[0]="'-uuser' '-ppass' '-hlocalhost' '-P3306' 'sample_db'"
LIST_1_CON_LIST[1]=""
LIST_1_CON_LIST[2]=""
LIST_1_CON_LIST[3]=""

LIST_2_CON_LIST=()
LIST_2_CON_LIST[0]=""
LIST_2_CON_LIST[1]=""
LIST_2_CON_LIST[2]=""
LIST_2_CON_LIST[3]=""
fetch_query.sql
# 文字列を動的に埋め込むなら
FETCH_TARGET_SQL="select ${param1}, ${param2}, user_id from hogehoge"

上記『文字列を動的に埋め込むなら』がなぜか赤枠で囲まれているのは原因不明です

7
5
2

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
7
5

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?