エックスサーバにsshtunnelを利用してMySQLサーバにアクセスする方法

こんにちは。

pythonのsshtunnelモジュールを利用して、エックスサーバのMySQLサーバにアクセスしようとしているのですが、うまくいきません。

コードは下記になります。

from sshtunnel import SSHTunnelForwarder
import mysql.connector

with SSHTunnelForwarder(
    ("~~.xsrv.jp",10022),
    ssh_pkey="~/~/id_rsa",
    ssh_username="ssh_username",
    ssh_password="ssh_password",
    remote_bind_address=("mysql~~.xserver.jp", 3306)
) as ssh:
    config = {
    "user": "db_user",
    "host": "mysql~~.xserver.jp",
    "password": "db_password"
    }
    con_obj = mysql.connector.connect(**config)
    cur_obj = con_obj.cursor()
    cur_obj.execute("select * from table1;")
    a = cur_obj.fetchone()
    print(a)
    con_obj.close()
    cur_obj.close()

上記コードを実行すると次のようなエラーメッセージが出ます。

DatabaseError: 2003 (HY000): Can't connect to MySQL server on 'mysql~~.xserver.jp' (60)

ssh接続まではできていると思うですが、MySQLへの接続がうまくいっていないと思われます。

ご教示頂ければ幸いです。

(以下追記1)

色々と調べてみるとエックスサーバとそのMySQLの構成は以下のようになっているようです。
メインサーバ => IPアドレスA(複数のホスト名有りもIPアドレスは一つに紐づいている)
MySQLサーバ => IPアドレスB
MySQLユーザのホスト => メインサーバに紐づいている

なので、今まで実験して見た下記プログラムでうまくいっていないのかなと思うのですが、他に具体的なやり方浮かばず、立ち往生しています。
①SSHサーバ=メインサーバ、リモートサーバ=MySQLサーバ、python-mysqlのホスト設定=localhost
=>MySQLのユーザ設定から拒否されている
②SSHサーバ=メインサーバ、リモートサーバ=MySQLサーバ、python-mysqlのホスト設定=MySQLサーバ
=>MySQLのユーザ設定から拒否されている(このMySQLユーザが利用できるホストがおかしい)
③SSHサーバ=メインサーバ、リモートサーバ=メインサーバ、python-mysqlのホスト設定=MySQLサーバ
=>理由不明もうまくいかず...

(追記２)
全てエラー内容はCan't connect to MySQL server on '~~'となります。

セット内容とエラーコードは以下になります。

①SSHサーバ=メインサーバ、リモートサーバ=MySQLサーバ、python-mysqlのホスト設定=localhost

with SSHTunnelForwarder(
    ("~~xsrv.jp",10022),
    ssh_pkey="~/~~/id_rsa",
    ssh_username="ssh_username",
    ssh_password="ssh_password",
    remote_bind_address=("mysql~~.xserver.jp", 3306)
) as ssh:
    print("a")
    config = {
    "host": "localhost",
    "port": ssh.local_bind_port,
    "user": "db_user",
    "password": "db_password"
    }
    #DB処理

'a'は出力され、その後に以下エラーコードが出る

DatabaseError: 2003 (HY000): Can't connect to MySQL server on 'mysql~~.xserver.jp' (60)

②SSHサーバ=メインサーバ、リモートサーバ=MySQLサーバ、python-mysqlのホスト設定=MySQLサーバ

with SSHTunnelForwarder(
    ("~~xsrv.jp",10022),
    ssh_pkey="~/~~/id_rsa",
    ssh_username="ssh_username",
    ssh_password="ssh_password",
    remote_bind_address=("mysql5009.xserver.jp", 3306)
) as ssh:
    print("a")
    config = {
    "host": "mysql~~.xserver.jp",
    "port": ssh.local_bind_port,
    "user": "db_user",
    "password": "db_password"
    }
    #DB処理

'a'は出力され、以下エラーが出る

DatabaseError: 2003 (HY000): Can't connect to MySQL server on 'mysql~~.xserver.jp' (60)

③SSHサーバ=メインサーバ、リモートサーバ=メインサーバ、python-mysqlのホスト設定=MySQLサーバ

with SSHTunnelForwarder(
    ("~~xsrv.jp",10022),
    ssh_pkey="~/~~/id_rsa",
    ssh_username="ssh_username",
    ssh_password="ssh_password",
    remote_bind_address=("localhost", 3306)
) as ssh:
    print("a")
    config = {
    "host": "mysql~~.xserver.jp",
    "port": ssh.local_bind_port,
    "user": "db_user",
    "password": "db_password"
    }
    #DB処理

'a'は出力され、その後に以下エラーコードが出る

DatabaseError: 2003 (HY000): Can't connect to MySQL server on 'mysql~~.xserver.jp' (60)

DatabaseErrorで、print("a")は出力されているので、SSHは無事に繋がっているとは思うのですが、つなぐ場所がおかしいのかなと考えています。

SSHサーバに手動で繋いだ上だと"mysql -h mysql~~.xserver.jp -u db_user -p db_passwordで入れて言います。

また、手動でメインサーバにSSH接続した上で以下設定でmysql-connector-pythonを実行すると接続することができます。

config = {
"host": "mysql~~.xserver.jp",
"user": "db_user",
"password": "db_password"
    }

(mysql.connector.connetでhost=localhostと設定した場合は、もちろん'mysql~~.xserver.jp'の部分が'localhost'に変わります。)

結果的に解決策は分からなかったですが、いろんな環境を見直して改めて状況を整理してみようと思います。
一旦解決という形で締めさせてもらいます。

行動規範の内容に同意します

回答2件

ベストアンサー

sshtunnelモジュールもmysql.connectorモジュールも使った事がありませんが。

    config = {
    "user": "db_user",
    "host": "mysql~~.xserver.jp",
    "password": "db_password"
    }

「host」の部分で指定するのがデータベースサーバのホストであれば、これではトンネルを使わずに直に接続しにいってます。
トンネルを使うのであれば、トンネルの入り口である「localhost」を指定すべきではないでしょうか。

上記のやり方ではダメだったそうですが、もう一点確認していただきたい。

Python 3.6 から MySQL データベースへ SSH 接続を試みる

上記によると、ポート番号も指定する必要があるらしいです。

python
1    config = {
2    "user": "db_user",
3    "host": "localhost",
4    "port": ssh.local_bind_port,
5    "password": "db_password"
6    }

上記でもダメでしょうか。

「MySQLのユーザ設定から拒否されている」とは、「Access denied for user」でしょうか。
エラーメッセージは、出たままを伝えてください。

とりあえず、sshコマンドでメインサーバに接続し、メインサーバ上でmysqlコマンドを実行してみてください。

mysql -u db_user -h MySQLサーバ -p

上記のコマンドでパスワードを入力しても同様のエラーが出るようであれば、sshの問題ではありません。

MySQLもちょこっと触った程度なのであまり知りませんが、MySQLのユーザは、ユーザ名・ホストで管理されており、ホストはアクセス権限になっているそうです。

MySQLの使い方 - ユーザの作成

今回の場合は、MySQLサーバからの接続対象となるのはメインサーバになるので、「db_user@メインサーバ」(もしくは、メインサーバを含むフィルタリング)になるはずです。
とりあえずユーザも確認されたほうがよろしいのではないでしょうか。

そろそろ、自分もお手上げの状態になってきましたね。

とりあえず、①が正しいやり方のはずです。
(もっとも、私も間違いがあるかもしれませんので、適度に疑ってください)
もし可能であれば、MySQLではなく、httpなどの他のサービスで試してみるのも手でしょう。

mysql.connectorについて。

Python3でMySQL Connectorのインストール・接続方法【快適に使う方法を紹介】

上記サイトによると、mysql.connectorと言っても、色々と派生があるようです。
試しに自分のところのpipで確認したところ、

mysql-connector (2.1.6)                                         - MySQL driver written in Python
bottle-mysql-connector (0.0.4)                                  - MySQL integration for Bottle.
mysql-connector-python (8.0.13)                                 - MySQL driver written in Python
mysql-connector-repackaged (0.3.1)                              - MySQL driver written in Python
mysql-connector-async-dd (2.0.2)                                - mysql async connection
mysql-connector-python-rf (2.2.2)                               - MySQL driver written in Python
mysql-connector-python-dd (2.0.2)                               - MySQL driver written in Python
py_mysql_connector (0.1)                                        - MySQL database connector

というようになっています。

とりあえずうまくいっているというメインサーバのパッケージと見比べてみてあわせてみたり、他のパッケージを試してみてはいかがでしょうか。

また、mysql-connector-pythonの場合、パッケージのバージョンとMySQLのバージョンとの組み合わせもあるようです。
そこも確認してみてください。

もし可能であれば、pythonスクリプトではなく、sshコマンド＋mysqlコマンドで試してみるのも手です。

投稿2018/12/25 11:42

編集2018/12/27 12:47

katsuko

総合スコア3469

NERO06

2018/12/25 16:41

やって見たのですが、違うようでした。エックスサーバだと、ホストファイルがあるサーバとMySQLがあるサーバのIPアドレスが異なっており、そこが何かポイントなのかなと思うのですが、色々やって見てもうまくいかず、悩んでいます...

katsuko

2018/12/26 00:04

すいません、ちょっと調べてみましたが、「エックスサーバ mysql ssh」でググると、確かにサーバ名を指定していますね。ただ、「ssh port forwarding mysql」でググると、どこも「127.0.0.1」で接続と書いてあるので間違いではないと思うのだけど、エックスサーバは何が違うのだろう…。

NERO06

2018/12/26 15:18

上記の方法もやって見ましたが、うまくいきませんでした...。エックスサーバのMySQL関連の構成とその他の失敗事例について追記させて頂きましたので、よろしければそちらも拝見して頂ければ幸いです。

NERO06

2018/12/30 06:05

あの後も色々とやってみましたが、どうもうまくいきませんでした。 SSHサーバやトンネリングの接続の仕組みについてざっくりした理解で色々試しており、なんらかの知識の欠如、見落としがあると思うので、一度そういったところを整理した上で再度挑戦してみようと思います。整理やその他のことに時間がかかると思うので、この質問は一旦締めようと思います。 Katsukoさんには多々助言頂きましたので、ベストアンサーにさせて頂きました。どうもありがとうございました。

行動規範の内容に同意します