アナコンダ+jupyter+mysql+ssh

アナコンダ環境作ろう。

[本日ゴール]
・サーバーにjupyter-notebook を入れる
・そこからDBサーバーにSSH接続
・pandasでデータ表示する

[環境]
Ubuntu 16.04.4 LTS
※さくらのVPS借りて、今しがたセットアップしたばっかりの環境。
vimだけ入れた（sudo apt-get install vim）

ではやりませうか。

１）アップデート

sudo apt-get update
sudo apt-get upgrade -y

２）アナコンダ

wget https://repo.continuum.io/archive/Anaconda3-5.1.0-Linux-x86_64.sh
　※2018/02/25の最新がこれでした。
bash Anaconda3-5.1.0-Linux-x86_64.sh
　※いろいろ聞かれるけど、気にせず「yes」でいきましょうか。
source ~/.bashrc
conda update conda

３）外部からのjupyter-notebook への接続設定

jupyter-notebook --generate-config

$ ipython3
In [1]: from notebook.auth import passwd
In [2]: passwd()
Enter password:
Verify password:
Out[2]: 'sha1:xxxxxxxxxxxxxxx

vim ~/.jupyter/jupyter_notebook_config.py'
最下部に以下を追加
c.IPKernelApp.pylab = 'inline'
c.NotebookApp.ip = '*'
c.NotebookApp.open_browser = False
c.NotebookApp.port = 8888
c.NotebookApp.password = u'sha1:xxxxxxxxxx'

jupyter-notebook &

これで http://xxxxx:8888/ でjupyter-notebookにアクセスできます。

４）ライブラリ追加

SSH接続
conda install -c conda-forge sshtunnel
MySQL接続
conda install -c https://conda.anaconda.org/anaconda mysql-connector-python

５）DBサーバーにSSH接続
まずDBサーバーにSSH接続する。

jupyter-notebookを開いて、以下を入力

from sshtunnel import SSHTunnelForwarder
server = SSHTunnelForwarder(
    (SERVER_IP, SSH_PORT),
    ssh_host_key = None,
    ssh_username = SSH_USERNAME,
    ssh_password = SSH_PASSWD,
    ssh_pkey = None,
    remote_bind_address = ( '127.0.0.1' , MYSQL_PORT),
    local_bind_address = ('127.0.0.1', LOCAL_PORT)
)
server.start()

今回は、「jupyter-notebookのサーバー」から直接「DBサーバー」にアクセスする。どちらもサクラVPSで、SSH接続で認証はユーザー名とパスワード。

パラメータは以下を参考に変更のこと。
SERVER_IP　　　： DBサーバーのホスト名 or IP
SSH_PORT　　　： DBサーバーのSSHポート。通常22
SSH_USERNAME： DBサーバーのSSHのユーザー名
SSH_PASSWD　　：DBサーバーのSSHのユーザー名
MYSQL_PORT　　：DBサーバーのmysqlのポート。通常3306
LOCAL_PORT　　：任意。なんでものいので3307とか。

ちなみに、もし、
「jupyter-notebookのサーバー」から「Webサーバー」を介して「DBサーバー」にアクセスする場合は

server = SSHTunnelForwarder(
    (SERVER_IP, SSH_PORT),
    ssh_host_key = None,
    ssh_username = SSH_USERNAME,
    ssh_password = SSH_PASSWD,
    ssh_pkey = None,
    remote_bind_address = ( DB_SERVER_IP , MYSQL_PORT),
    local_bind_address = ('127.0.0.1', LOCAL_PORT)
)
server.start()

パラメータは以下を参考に変更のこと。
SERVER_IP　　　： Webサーバーのホスト名 or IP
SSH_PORT　　　： WebサーバーのSSHポート。通常22
SSH_USERNAME： WebサーバーのSSHのユーザー名
SSH_PASSWD　　：WebサーバーのSSHのユーザー名
DB_SERVER_IP　　： DBサーバーのホスト名 or IP
MYSQL_PORT　　：DBサーバーのmysqlのポート。通常3306
LOCAL_PORT　　：任意。なんでものいので3307とか。

６）MySQLに接続

import mysql.connector
dbh = mysql.connector.connect(
    host='localhost',
    port=LOCAL_PORT
    db = DB_NAME
    user = DB_USER,
    password = DB_PASSWD,
    charset='utf8')

パラメーターは任意に変更のこと
LOCAL_PORT：SSHの時に指定したポート。3307とか。
DB_NAME　　：データベースの名前
DB_USER　　：データベースのユーザー
DB_PASSWD：データベースのパスワード

７）クエリー発行する

import numpy as np
import pandas as pd

sql = "SELECT * from XXXX;"
table_df = pd.read_sql(sql, dbh)

できた。

片付けも忘れずに。

dbh.close()
server.stop()