手作業になりやすい「踏み台サーバ経由でMySQLを実行するプロセス」をコードで完結させてみた

データベースを利用する、割と日常的に発生しやすい作業の一つに「踏み台サーバ経由でDBに向けてクエリを投げる」というものがあります。

作業自体は特に大したこともなく、淡々とSSH経由で踏み台サーバにログインすることが殆どだと思われます。結果をファイルに落としこむという手順が増えても、恐らくはさほど手間ではないはずです。

ただ、以下の様な条件が挟まった場合、案外そうとも言い切れません。

• 一定期間中に頻度高めで行う
• 結果を元にして別のシステムからデータを参照する

今回、そんなわりとよくある作業にある程度手間な条件が挟まったとしても、変わらず少ない手数で完遂することを目的にコードへ落としてこんでみました。

ライブラリのインストール

今回は動作検証に用いた環境の都合で、Python2を利用します。

% cat << EOF >> requirements.txt
mysql-python
sshtunnel
EOF
% pipenv install -r requirements.txt  --python 2.7.10

実装

検証の過程で、DBへの接続情報を別ファイルに分離させています。

実行

% pipenv shell
% python main.py

main.py

#!/usr/bin/env python
# -*- coding: utf-8 -*-
 
import MySQLdb
import csv
import sshtunnel

db_conf = {}
execfile("db.conf", db_conf)

def get_conf():
    return {
        'host': db_conf['host'],
        'port': 3306,
        'user': db_conf['user'],
        'passwd': db_conf['passwd'],
        'db': db_conf['db'],
        'charset': 'utf8',
    }
    
def dump_to_csv():
    
    column_names = ["title", "id", "name", "description"]
    sql = " ".join(["SELECT", ",".join(column_names), "FROM example"])
    db_conf = get_conf()

    with open("dump.csv", 'wb') as csvfile:
        csv_writer = csv.writer(csvfile)
        csv_writer.writerow(column_names)

        try:
            print("SSH Tunnel Start")
            server = sshtunnel.SSHTunnelForwarder(
                ("XX.XX.XX.XX", 22),　#踏み台IP
                ssh_username="ec2-user",
                ssh_pkey="~/.ssh/server.pem",
                local_bind_address=('127.0.0.1', 3306),
                remote_bind_address=(db_conf["host"], 3306)
            )
            server.start()
            print("SSH Tunnel Started")
            
            print("MySQL Make Connect")
            db_conf["host"] = '127.0.0.1'
            cnn = MySQLdb.connect(**db_conf)
            cur = cnn.cursor()
            print("MySQL Maked Connect")
            
            print("SQL executing")
            cur.execute(sql)
            result = cur.fetchall()
            print("SQL executed")
            
            for row in result:
                csv_writer.writerow([unicode(s).encode("utf-8") for s in row])
                
            cur.close()
            cnn.close()

            server.stop()
            print("SSH Tunnel Stoped")
            
        except Exception as e:
            print(e)
            return

if __name__ == "__main__":
   dump_to_csv()

db.conf

user = "xxxxx"
passwd = "xxxxxxxxxxxxxxxxx"
host = "xxxxxxxxxxxxxxxxxxxxx.rds.amazonaws.com"
db = 'database'

概要

検証時に途中でインデントを挟むのが面倒になったためsshtunnel.SSHTunnelForwarderはWithではなくstart関数の呼び出しで実行しています。

SSHTunnelForwarderにて実施した動作は、local_bind_addressとremote_bind_addressを利用してリモートDBのIPとポートをローカルに結び付けるというものです。結びつけたローカルのポートに対してMySQLdbを接続してデータを取り出します。

DBから取得した結果を元に他のデータを取得する場合は、54〜55行目辺りに処理を挟むことになると思います。

まとめ

定期的に手作業として行っているケースに適用することで、作業負担の減少に繋がれば幸いです。実際にはファイアウォール等環境によってうまくいかないケースも考えられますが、部分的にでも参考にして頂ければと思います。