【Xserver VPS】Docker内MySQLに外部(Python)からSSHトンネル経由で接続したのでメモ

2026/02/04

VPS上のDockerコンテナで運用しているデータベースに対し、ローカル環境から安全にアクセスしデータを自動取得する仕組みを構築しました。通常、セキュリティの観点からDBポートを外部開放することは推奨されませんが、SSH経由でコマンドを実行する手法をとることで堅牢性を維持したままデータ抽出が可能です。

接続過程でいろいろな問題に直面したので、その注意点を踏まえてソースコードを解説付きで書き留めておきます。

import pymysql
import paramiko

# ==========================================
# 設定情報（環境に合わせて書き換えてください）
# ==========================================
# [注意] IPアドレスやパスは、環境固有の情報を正確に指定すること
SSH_HOST = 'xxx.xxx.xxx.xxx'  # VPSのグローバルIP
SSH_USER = 'server_user'      # SSH接続ユーザー名
KEY_PATH = "C:/path/to/your/private_key.pem" # 秘密鍵のフルパス

# [注意] docker-compose.ymlで定義したMYSQL_USER等と一致させる
DB_USER = 'db_user_name'
DB_PASS = 'db_password'
DB_NAME = 'target_database'

# [注意] docker-compose.ymlが存在する絶対パスまたはホームからの相対パス
TARGET_DIR = "~/your-project-dir" 

def main():
    # 1. 秘密鍵の読み込み
    # [Tips] Python 3.14+環境等では、paramikoの最新仕様により
    # DSSKey(DSA)が廃止されているため、RSAKey等の使用を推奨
    try:
        key = paramiko.RSAKey.from_private_key_file(KEY_PATH)
    except Exception as e:
        print(f"鍵読み込みエラー: {e}")
        return

    # 2. SSH接続の確立
    ssh_client = paramiko.SSHClient()
    ssh_client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
    
    try:
        print(f"Connecting to SSH: {SSH_HOST}...")
        ssh_client.connect(SSH_HOST, port=22, username=SSH_USER, pkey=key)
        
        print("SSH connection established. Fetching data from MySQL...")

        # 3. リモートコマンドの構築
        # 【重要：注意点】
        # ① cd {TARGET_DIR}: docker compose実行には.ymlがある階層への移動が必須
        # ② docker compose exec -T: -Tオプションを付けないと「Input is not a TTY」エラーの原因になる
        # ③ mysql -u... -p'...': パスワードに特殊文字が含まれる場合、シングルクォートで囲むのが安全
        # ④ -N -s: 出力から枠線や警告を除去し、純粋なデータのみを抽出する
        sql = (
            f"cd {TARGET_DIR} && "
            f"docker compose exec -T db mysql -u{DB_USER} -p'{DB_PASS}' {DB_NAME} "
            f"-N -s -e 'SELECT * FROM your_table_name LIMIT 10;'"
        )
        
        stdin, stdout, stderr = ssh_client.exec_command(sql)
        
        # [注意] MySQLの警告(Warning)はstderrに出るため、
        # "ERROR" という文字列が含まれている場合のみ異常として扱う
        result = stdout.read().decode().strip()
        error = stderr.read().decode()

        if "ERROR" in error:
            print(f"Database Error: {error}")
        elif result:
            print("--- 取得データ ---")
            print(result)
            
            # 4. データ保存（後続のExcel/分析作業用）
            filename = "data_export.csv"
            with open(filename, "w", encoding="utf-8") as f:
                f.write(result)
            print(f"\n成功: データを '{filename}' に書き出しました。")
        else:
            print("警告: 接続成功しましたがデータが取得できませんでした。パスやテーブル名を確認してください。")

    except Exception as e:
        print(f"実行エラー: {e}")
    finally:
        ssh_client.close()
        print("SSH connection closed.")

if __name__ == "__main__":
    main()

- Xserver VPS