MySQL データベース

【MySQL】シェルからMySQLのSQLを実行する方法、サンプル

普段MｙSQLを使う時は、直接DBサーバにアクセスするかSQLエディタを利用してSQLを実行していますが、大量のSQLを実行する作業であったり、パフォーマンステストなどで数百万～数億件のデータを投入するときには、一つ一つ手作業で対応するにも限界があります。

そういうケースでは、シェルスクリプト(bash/sh)からMySQLを遠隔操作できると、とても作業を効率化できます。

例えば実行するSQL文をシェルスクリプトに記述し、それを順番に実行するスクリプトを組めば、自分のそのシェルスクリプトを1回実行するだけで、処理が終わるまでの間ほかの作業に手を付けることができます。

今回はシェルからMySQLに対してSQL文を発行する方法と、サンプルプログラムを作成しましたので、その紹介をします。

Version

  • OS：CentOS6.8
  • データベース：MySQL5.6.28

シェルからMySQLのSQLを実行する

書き方としては2種類ありますが、一番シンプルな例は以下の通りになります。

$ echo '[実行するSQL文]' | mysql -u [ユーザ名] -p
Enter password:[パスワード]

別の書き方として、

$ mysql -u [ユーザ名] -p  < '[SQL文、またはSQLが書かれたファイルパス]'

上記の通りになりますが、単純にコマンドを叩いて実行する場合は1つ目の方法が良いかと思います。

スクリプトとしてプログラムを作成する場合は、2つ目の書き方の方が個人的には都合がいいです。

 

サンプルプログラム

上記で説明したシェルからMySQLのSQLを実行する方法を応用して、サンプルプログラム作成しました。

プログラムの紹介の前に、上記で説明したSQLの実行方法では少し不備があります。

シェルスクリプトで処理を自動化するにあたって、パスワードを聞かれるなどの対話を極力なくすために、接続情報など設定を作成しておき、mysqlの接続に「--defaults-extra-file」のオプションを付けて設定ファイルを指定する必要があります。

設定ファイルは「mysql.conf」として、とりあえず「/var/tmp」に作成しておきます

$vi /var/tmp/mysql.conf
# 内容は以下！
[client]
user = ユーザ名
password = パスワード
# 必要に応じて別ホスト経由で接続する場合に記事↓
host = IPアドレス

実行する場合は以下のコマンドになります。

ただし注意点として、「--defaults-extra-file」オプションの指定は一番最初に書かないとエラーになります。

 

まずは単純にMySQLに対して対象のテーブルのデータを全件出力するサンプルです。

#!/bin/bash
# production

echo "[INFO] 処理開始"
# 定数の定義
MYSQL_SCHEMA="db"
ROOT_DIRECTORY="/var/tmp"
CMD_MYSQL="mysql --defaults-extra-file=$ROOT_DIRECTORY/mysql.conf -t --show-warnings $MYSQL_SCHEMA"

# SQLの指定
# カラムにアスタリスク（*）を使うとうまくいかなかったので、一つずつ指定
QUERY="SELECT id, name, age FROM samlpe_table "
# シェルを実行、実行ログを受け取る
VALUE=`echo ${QUERY} | ${CMD_MYSQL}`
# 処理の終了コードを取得
RESULT=$?
echo $VALUE
# 結果のチェック
if [ $RESULT -eq 0 ]; then
    echo "[INFO] 処理終了"
    exit 0
else
    echo "[ERROR] 予期せぬエラーが発生 異常終了"
    exit 1
fi

さらに、上記の例だとSELECT文を一回投げるだけで味気ないので、例えばINSERT分を複数発行して最後にコミットする場合は以下のように書きます。

#!/bin/bash
# production

echo "[INFO] 処理開始"
MYSQL_SCHEMA="db"
ROOT_DIRECTORY="/var/tmp"
CMD_MYSQL="mysql --defaults-extra-file=$ROOT_DIRECTORY/mysql.conf -t --show-warnings $MYSQL_SCHEMA"

# SQLの実行
# 複数のクエリを発行する場合は、
# 複数行の文字列にクエリをセミコロン(;)で繋げて指定する
${QUERY} <<EOF
    INSERT INTO sample_table VALUES(2, tro, 14);
    INSERT INTO sample_table VALUES(3, hanako, 13);
    commit;
EOF
VALUE=`echo ${QUERY} | ${CMD_MYSQL}`

# 処理の終了コードを取得
RESULT=$?
# 結果のチェック
if [ $RESULT -eq 0 ]; then
    echo "[INFO] 処理終了"
    exit 0
else
    echo "[ERROR] 予期せぬエラーが発生 異常終了"
    exit 1
fi

最後に、ログファイルの出力などを盛り込んだサンプルプログラムを紹介します。

#!/bin/bash
# production

# 定数の定義
MYSQL_SCHEMA="db"
ROOT_DIRECTORY="/var/tmp"
CMD_MYSQL="mysql --defaults-extra-file=$ROOT_DIRECTORY/mysql.conf -t --show-warnings $MYSQL_SCHEMA"
LOG_NAME=${ROOT_DIRECTORY}/mysql-sh.log
# 実行時間の取得
PID=$$_`date '+%H%M%S'`
# ログ出力
exec 1> >(awk '{print strftime("[%Y-%m-%d %H:%M:%S]") "[""'$PID'""]" $0} {fflush()} ' >>$LOG_NAME)
exec 2> >(awk '{print strftime("[%Y-%m-%d %H:%M:%S]") "[""'$PID'""]" $0} {fflush()} ' >>$LOG_NAME)

function selectQuery() {
    local QUERY="SELECT id FROM sample_table"
    local VALUE
    VALUE=`echo ${QUERY} | ${CMD_MYSQL}`
    if [[ $? -eq 0 ]]; then
        echo "[INFO] server mode maintenance"
        return 0
    else
        echo "[ERROR] server mode not maintenance : ${VALUE}"
        return 1
    fi
}

# 結果のチェック
function checkResult() {
    if [ $1 -eq 1 ]; then
        echo "[ERROR] $0 abnormal 終了"
        exit 1
    elif [ $1 -eq 2 ]; then
        echo "[INFO] $0 終了"
        exit 0
    fi
}

# 実際の処理開始
echo "[INFO] $0 開始"

selectQuery; checkResult $?

echo "[INFO] $0 終了"

上記のサンプルの「selectQuery()」の関数を、各々の実行したいSQLにしてもらえれば、いろいろ応用が利くかと思います、、、たぶん。

-MySQL, データベース