SSHトンネルを介してXserverのMySQLに接続する方法

BoatRaceの成績データと選手データをCSV形式にしましたが、バラバラのCSVを結合するのは面倒なのでデータベースに入れていきます。なんとなく、、仕事の癖かわからないのですが、データベースはサーバーにおいて、皆で（？）使うもの、という固定概念があるので、（容量大きいので単にローカル持ち出来ないだけ）Xserver上のMySQLを使用して、データベースを作成し、その接続方法を記載します。

デフォルトではMySQLはセキュリティ上の仕様でローカルホストしか接続出来ません。つまり同じホストで実行されているアプリケーションのみしかアクセス出来ません。リモート接続を許可するようにMySQLを構成することオプションがありますが、これは管理者権限が必要であり、セキュリティリスクを引き起こす可能性があります。そしてXserverでは出来ません。

安全な代替手段は、ローカルPCからサーバーへSSHトンネルを作成してアクセスする事です。SSHトンネリングは、サービスポートを中継できるクライアントとサーバーマシンの間に暗号化されたSSH接続を作成する方法です。ローカルPCからサーバーにSSH接続し、そこからデータベースにアクセスする必要があります。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
# -*- coding: utf-8 -*-
from sshtunnel import SSHTunnelForwarder
import pymysql

class MySQLDatabase:

    def __init__(self):
        self.connection = None

    def open_connection(self):
        """Connect to MySQL Database."""
        # MySQL に接続
        with SSHTunnelForwarder(
                ('*****.xserver.jp', 10022),
                ssh_username='*****',
                ssh_password='*****',
                ssh_pkey='*****/*****.key',
                remote_bind_address=('*****.xserver.jp', 3306)
        ) as server:
            if self.connection is None:
                self.connection = pymysql.connect(
                                host='localhost',
                                port = server.local_bind_port,
                                user = '*****',
                                password = '*****',
                                database = '*****',
                                charset='utf8'
                            )
                print ("connect successful!!")
                self.connection.close()
                print ("disconnect successful!!")

if __name__ == "__main__":
    db = MySQLDatabase()
    db.open_connection()

ちなみに、私の場合、22,23行目の「host=‘localhost’,」「port = server.local_bind_port,」の指定が間違っており、接続できないエラーがずっと出ておりました。もし接続出来ない場合はご確認下さい。

そして、as serverを使わない場合はserver.start()とserver.stop()を使用します。

1
2
3
4
5
6
7
8
9
        server = SSHTunnelForwarder(
                ('*****.xserver.jp', 10022),
                ssh_username='*****',
                ssh_password='*****',
                ssh_pkey='*****/*****.key',
                remote_bind_address=('*****.xserver.jp', 3306))
 
        server.start()
        server.stop()