Python(Dockerコンテナ)からSSHで踏み台サーバ(EC2)を経由してDB(MySQL)サーバに接続する

背景

分析のためのデータを取得したいが、データがAWS RDSに格納されており、踏み台サーバ(EC2)経由でないと接続できない。MySQLWorkbenchからデータの取得はできているが、分析のコードを実行時に最新のデータを読み込みたい。

実行環境

• Ubuntu16.04
• Python3.6.7

ファイル構成

./
┣Dockerfile
┗bash_profile
./app/
┗read.py
./app/.ssh/
┗my.pem

Dockerfile

FROM ubuntu:16.04

# 環境変数の設定
USER root
ENV HOME=/root
ARG PYTHON_VERSION=3.6.7
ARG PYTHON_ROOT=${HOME}/local/python-${PYTHON_VERSION}
ENV PATH=${PYTHON_ROOT}/bin:${PATH}

# Pythonのインストール
ARG PYENV_ROOT=${HOME}/.pyenv
RUN apt-get update \
 && apt-get -y upgrade \
 && apt-get -y install \
    git \
    make \
    build-essential \
    libssl-dev \
    zlib1g-dev \
    libbz2-dev \
    libreadline-dev \
    libsqlite3-dev \
    wget \
    curl \
    llvm \
    libncurses5-dev \
    libncursesw5-dev \
    xz-utils \
    tk-dev \
    libffi-dev \
    liblzma-dev \
 && git clone https://github.com/pyenv/pyenv.git ${PYENV_ROOT} \
 && ${PYENV_ROOT}/plugins/python-build/install.sh \
 && /usr/local/bin/python-build -v ${PYTHON_VERSION} ${PYTHON_ROOT} \
 && rm -rf ${PYENV_ROOT}

# MySQLのインストール
RUN /bin/bash -c "debconf-set-selections <<< 'mysql-server mysql-server/root_password password YOUR_PASSWORD'" \
 && /bin/bash -c "debconf-set-selections <<< 'mysql-server mysql-server/root_password_again password YOUR_PASSWORD'" \
 && apt install -y mysql-server libmysqlclient-dev
VOLUME /var/run/mysqld
COPY bash_profile ${HOME}/.bash_profile

# Pythonライブラリのインストール
RUN pip install --upgrade pip \
 && pip install \
    sshtunnel \
    mysqlclient \
    sqlalchemy \
    pandas

ARG WORKDIR=${HOME}/app
VOLUME ${WORKDIR}
WORKDIR ${WORKDIR}

bash_profile

touch /var/run/mysqld/mysqld.sock
chown -R mysql:mysql /var/run/mysqld /var/lib/mysql
service mysql start
chmod 600 ./.ssh/*

コンテナは以下で立ち上げ

$ docker run -v $(pwd)/app:/root/app -it CONTAINER bash --login

SSHポートフォワード

sshtunnelを利用する。以下で、127.0.0.1:****から踏み台サーバ(SSH_HOST:SSH_PORT)を経由してDB(REMOTE_BIND_ADDRESS=(DB_HOST, DB_PORT))にアクセスできるようになる。ポート番号(****)はserver.local_bind_portに格納されている。

server = SSHTunnelForwarder(SSH_HOST, SSH_PORT, ssh_host_key=SSH_HOST_KEY,
                            ssh_username=SSH_USERNAME, ssh_password=SSH_PASSWORD,
                            ssh_pkey=SSH_PKEY,
                            remote_bind_address=REMOTE_BIND_ADDRESS)

利用後は閉じるのを忘れずに。

server.close()

DB読み込み

ORMとしてSQLAlchemyを利用し、pandasにデータを読みこむ。

con = sqlalchemy.create_engine(
    f'mysql+mysqldb://{DB_USER}:{DB_PASSWORD}@127.0.0.1:{server.local_bind_port}/{DB_NAME}',
    echo=True)
data = pd.read_sql('SELECT * FROM fuga', con)

文字コードを指定する場合は{server.local_bind_port}/{DB_NAME}?charset=utf8のように最後に追加する。

実行コード

read.py

from sshtunnel import SSHTunnelForwarder
import sqlalchemy
import pandas as pd

SSH_HOST = 'YOUR_SSH_HOST_IP'
SSH_PORT = 22
SSH_HOST_KEY = None
SSH_USERNAME = 'ec2-user'
SSH_PASSWORD = None
SSH_PKEY = './.ssh/my.pem'
DB_HOST = 'hoge.rds.amazonaws.com'
DB_PORT = 3306
REMOTE_BIND_ADDRESS = (DB_HOST, DB_PORT)
DB_USER = 'DB_USER_NAME'
DB_PASSWORD = 'DB_PASSWORD'
DB_NAME = 'DB_NAME'

with SSHTunnelForwarder(SSH_HOST, SSH_PORT, ssh_host_key=SSH_HOST_KEY,
                        ssh_username=SSH_USERNAME, ssh_password=SSH_PASSWORD,
                        ssh_pkey=SSH_PKEY,
                        remote_bind_address=REMOTE_BIND_ADDRESS) as server:
    con = sqlalchemy.create_engine(
        f'mysql+mysqldb://{DB_USER}:{DB_PASSWORD}@127.0.0.1:{server.local_bind_port}/{DB_NAME}',
        echo=True)
    data = pd.read_sql('SELECT * FROM fuga', con)