【Python・MySQL】サーバーにあるMySQLへローカルPCからSSH経由で接続するまとメモ

ITO です。こんばんは！お盆もすぎて少しずつ涼しくなってきたようなきてないような。

先日エクセルファイルにまとめられたデータをデータベース (MySQL) に移行する機会がありました。11万行ぐらいあって手作業でやったらタイプのし過ぎでキーボードが壊れそうだったので、python でやってみました。

その時のローカル PC から遠～く離れたレンタルサーバー上にある MySQL に接続する方法をメモメモします。

状況と流れ

自宅の PC (windows10) からレンタルサーバーで動いている MySQL に python を使って接続します。python のバージョンは 3.7 です。

利用しているレンタルサーバーはエックスサーバー です。

python で mysql に接続する際は mysql-connector-python というライブラリ (パッケージ) を pip でインストールして、MySQL connector を使います。

インポートするときは mysql-connector-python ではなく、、、mysql.connector です。

MySQL を python で扱うためのライブラリはいくつかありますが、今回は MySQL の公式でもある Oracle (オラクル) が出している上記の MySQL Connector を使います！

公式サイトによると、最新の mysql connector 8.0 系のバージョンでも python 3.9 までにしか対応していないようです。

流れ

MySQL の接続は以下の流れでやります。

1. MySQL があるサーバーに SSH 接続 (SSHポート転送)
2. SSH 接続した状態で MySQL に接続

手順①について。。。Xserver 上で動いている MySQL には基本的に外部から接続は出来ません。エックスサーバーに限らず、セキュリティ的にそうなっているサーバーも多いかと思います。

MySQL があるのと同じところ (ホスト) からしかアクセスできないので、まず初めに自宅 PC とサーバーを SSH 接続します。

サーバーに SSH 接続

まずは Xserver に自分のパソコンから SSH で接続します。

エックスサーバーで SSH 接続を利用するにはエックスサーバー側で事前の準備が必要です。

SSH設定 | レンタルサーバーならエックスサーバー

レンタルサーバー「エックスサーバー」のご利用マニュアル｜エックスサーバーではSSH接続機能を提供しています。当マニュアルではお客様のサーバーアカウントへSSHで接続するための手順を記載しています。

www.xserver.ne.jp

上記の公式サイトの解説ページにやり方がのっています。

解説ページに従って準備した、秘密鍵とパスフレーズを使います！

いざ Python で SSH

python で ssh をするために sshtunnel というライブラリを pip インストールアンドインポートして、その中の SSHTunnelForwarder クラスを利用します。

import sshtunnel

# SSH 接続
with sshtunnel.SSHTunnelForwarder(
    ("サーバのホスト名", 接続ポート番号), 
    ssh_username="ユーザーID", 
    ssh_private_key_password="秘密鍵を生成したときのパスフレーズ", 
    ssh_pkey="秘密鍵ファイルのパス", 
    remote_bind_address=("目的地サーバのホスト名", 目的地サーバのポート番号)
    ) as server:

    # SSH接続確認
    print(f"local bind port: {server.local_bind_port}")

    # 以下データベース関連の処理

各引数について。

第一引数

SSH で接続するサーバーのホスト名とポート番号のタプル。ホスト名の部分は IP アドレスでもいけます。

エックスサーバーでの SSH なのでホスト名は「サーバーID.xsrv.jp」、ポート番号は 10022 となります。

ssh_username

これは文字通りユーザー名です。エックスサーバーなのでおそらく サーバーID を指定します。

ssh_private_key_password

エックスサーバー側での SSH 事前準備で秘密鍵生成時にパスフレーズを入力したと思います。それです。忘れた場合は再度ひみつ鍵を生成しましょう！

ssh_pkey

秘密鍵ファイルのパス。相対パスでも絶対パスでもオッケーです。エックスサーバー側での事前準備で秘密鍵の生成が完了すると「サーバーID.key」というファイルがダウンロードされているはずです。

remote_bind_address

目的地サーバーとありますが、ここにデータベースサーバーの情報を書きます。

ここで言う目的地サーバーとは「直接接続はできないけど、SSHで接続したサーバーからなら接続が可能なサーバー」です。

実は今使っているこのクラスは名前 (SSHTunnelForwarder) から連想されるように SSHポート転送 (SSHポートフォワーディング) をするためのものであります。

SSH で繋がっている間は localhost の local_bind_port にアクセスすると、転送されてこの引数で指定したサーバーに接続できます。

なのでこの引数にデータベースサーバーの情報を渡します。

エックスサーバーの MySQL にアクセスするときの接続先ホスト名は「mysql○○.xserver.jp」だと思います。○○の部分は人によって違うのでエックスサーバーのサーバーパネルにログインして確認してください。

接続先ポート番号は MySQL デフォルトの 3306 だと思いますが、MySQL で 「show variables like “port” 」を実行することで確認できます。

コマンドプロンプトで MySQL に接続して先述のコマンドで確認したところ確かに 3306 でした！

MySQLと繋がる

ついに今回のメイン！

さっきの SSH 接続している with ブロックの中で mysql の接続処理をかいていきます。

既にご紹介した通り、mysql に接続するのに MySQL Connector を利用します。

いざ python で mysql 接続

pip インストールを完了しまして、mysql.connector をインポートします。

pip でインストールするときとスクリプトで import するときの名前が違うので注意です。

そして mysql.connector の connect 関数を使います。

下記のコードには mysql に関するコードしか書いていませんが、実際に使うときは上で書いた ssh の with 文の中です。この記事の後の方にソースコード全体をのせています！

import mysql.connector

# データベース接続
cnx = mysql.connector.connect(
    host="127.0.0.1", 
    port=server.local_bind_port, 
    user="データベースのユーザー名", 
    password="そのユーザーのパスワード", 
    database="扱いたいデータベース名", 
    charset="utf8",
    use_pure=True
    )

# 接続確認
print(f"sql connection status: {cnx.is_connected()}")

# 終了
cnx.close()

こちらも各引数について。

host

データベースサーバーのホストを指定する。今は ssh ポート転送 (ssh ポートフォーワーディング) のおかげで localhost にアクセスすれば OK なのでこの引数にはローカルホストを指定。

“127.0.0.1” でも “localhost” でもどっちでもおけです！

port

データベースサーバーのポート番号。こちらも SSH ポート転送してくれるので、SSHTunnelForwarder オブジェクトの「 local_bind_port 」を指定します。

user、password、database、charset

それぞれデータベースへのアクセス権限があるユーザー名、そのユーザーのパスワード、操作対象のデータベース名、文字コードです。分かりやすいです。

use_pure

このライブラリを使うにあたって、C 拡張実装か純粋 python 実装かを選ぶための引数。わざわざこの引数を指定する必要はなさそうなのですが、これを True にしていないと私の環境では「SSL connection error」が発生しました。

MySQL Connector のバージョンによってデフォルトの値が異なるようで、バージョン 8.0.11 以降は False となっており、それ以前では True となっているようです。

公式ドキュメントにのっています。公式ドキュメントでは他のオプション引数についても詳しくのっています！

https://dev.mysql.com/doc/connector-python/en/connector-python-connectargs.html

dev.mysql.com

ソースコード全体

SSH 接続をしてから MySQL にアクセスして、確認のために SELECT 文を実行しています。

import mysql.connector
import sshtunnel


def main():

    # SSH 接続
    with sshtunnel.SSHTunnelForwarder(
        ("ito.xsrv.jp", 10022), 
        ssh_username="ito", 
        ssh_private_key_password="itopassword", 
        ssh_pkey="ito.key", 
        remote_bind_address=("mysql12345.xserver.jp", 3306)
        ) as server:

        # SSH接続確認
        print(f"local bind port: {server.local_bind_port}")

        ## 以下データベース関連の処理 ##
        
        # データベース接続
        cnx = mysql.connector.connect(
            host="127.0.0.1", 
            port=server.local_bind_port, 
            user="dbusername", 
            password="dbuserpassword", 
            database="dbname", 
            charset="utf8",
            use_pure=True
            )

        # 接続確認
        print(f"sql connection status: {cnx.is_connected()}")

        # データベース操作用カーソル
        cur = cnx.cursor(buffered=True)

        sql = "SELECT * FROM achiga"
        cur.execute(sql)
        # 取得
        rows = cur.fetchall()

        for row in rows:
            print(row)

        # 終了
        cur.close()
        cnx.close()


if __name__ == "__main__":
    main()

実行した結果が以下です。

ちゃんとデータベースに接続できてるっぽいです。achiga テーブルの情報が取得できています！

これでローカルPC の python からでもいろいろデータベースを操作できそうです！