PHPでSSHサーバ経由でMySQLに接続したい。(How to connect to MySQL over SSH)

Question

APサーバ環境

• PHP 7.3.12

DB/SSHサーバ環境

• CentOS 7.7.1908
• MySQL 8.0.19

実現したいこと

PHPアプリケーション内でSSHポートフォワーディング(トンネル)を構築して、
PHPアプリケーションからMySQLに接続したいです。

試したこと

こちらを参考にし、下記コードを書いてみました。

<?php
  $ret;
  $output;
  try {
    $cmd = 'ssh -f -N -L 19000:server.ne.jp:3306 [e-mail] -i ./key/id_rsa 2>&1';
    exec($cmd,$output,$ret);
    var_dump($output, $ret);
    echo '<br>';
  } catch (Exception $e) {
    exit('捕捉した例外:'.$e->getMessage());
  }

  $dsn = 'mysql:dbname=mydb;host=127.0.0.1;charset=utf8mb4';
  $user = 'username';
  $pass = 'P@ssw0rd';
  try {
      $pdo = new PDO($dsn,$user,$pass,
          [
              PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
              PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
          ]
      );
  } catch (PDOException $e) {
    exit('データベース接続失敗。'.$e->getMessage());
  }
?>

上記コードを実行すると以下、画面に表示されます。

array(1) { [0]=> string(29) "Host key verification failed." } int(255)
データベース接続失敗。SQLSTATE[HY000] [2002] Connection refused

教えていただきたいこと(わからないこと)

• execコマンドの第3引数で成功したかどうかわかるとのことで表示してみたら255と返ってきましたが、PHP初心者のため、何が原因で失敗しているのか、この先何を試したらよいか分かりません。。。
• その他、どうすればSSH経由でMySQLに接続できるか教えていただきたく。

補足

• 当方手元のPCはwindowsです。TeraTermからSSHポートフォワーディングを行い、ODBC接続でMySQLに接続できることは確認しています。(ので、コマンドの接続情報やポート番号等は間違いありません。)
• MySQLのユーザの認証方法は「mysql_native_password」です。

よろしくお願いいたします。

Comments

[CHERRY]

コマンドの標準エラー出力を標準出力にリダイレクトすると　$output には、どのようなメッセージが表示されますか？

[Arsnova]

ご連絡ありがとうございます。
先ほど質問を修正しました。そちらにも記載しましたが、$outputには
array(1) { [0]=> string(29) "Host key verification failed." } int(255)
と出力されるようになりました。
このエラーメッセージで検索してみようと思います。
手元のPCでTeraTermで秘密鍵を使って接続はできてはいます。

Answer 1

まず、手動で下記のsshコマンドが動くかどうかを確認しましょう。

$ ssh -f -N -L 19000:server.ne.jp:3306 [e-mail] -i ./key/id_rsa

ここで、19000:server.ne.jp:3306 に書かれているホスト名(server.ne.jp)は、ssh接続した端末（サーバー）から見てのホスト名です。上記の記述だと同じホスト名になっているので、以下のように書くこともできます。

$ ssh -f -N -L 19000:localhost:3306 [e-mail] -i ./key/id_rsa

また、[e-mail] となっているのは、rootでssh接続することになりますが、ssh側でrootログインの許可は与えていますか? セキュリティ上の理由で、多くの場合、rootではsshログインできなくなっています。この点も確認してください。

また、以下のDSNですが、以下は間違いです。

$dsn = 'mysql:dbname=mydb;host=server.ne.jp;charset=utf8mb4';

おそらく以下のようにすべきです。参照元の記事もそうなっています（ポート番号等は違いますが）。

$dsn = 'mysql:dbname=db;host=127.0.0.1;port=19000;charset=utf8mb4';

こちらも、事前に、以下のようにmysqlコマンドで接続を確認しておくとよいでしょう。

$ mysql -u root -h 127.0.0.1 -P 19000 -p

初心者の方ほど事前確認を怠りがちですが、段階的に確認していかないとうまくはいきません。難易度の高いことを試す場合はなおさらです。

Answer 2

CHERRY様にご質問いただいた後、前に進み始めましたので、
以下私の解決手順等を記載致します。
(エラーメッセージは記憶で調べて書いているので正確ではないかもしれません。)

発生した課題と解決策

#####Host key verification failed.
下記execコマンドでのエラー内容が「array(1) { [0]=> string(29) "Host key verification failed." } int(255)」だった。

    $cmd = 'ssh -f -N -L 19000:server.ne.jp:3306 [e-mail] -i ./key/id_rsa 2>&1';
    exec($cmd,$output,$ret);
    var_dump($output, $ret);

解決策としては、known_hostsにフィンガープリントを追記しました。もっとエレガントな方法があると思いますが、私の場合は、手元のPCのTeraTermのknown_hostsをテキストエディタで開いてAPサーバの方にコピペしました。

execコマンドでフリーズ

上記課題を解決した後、実行したらexecのところでフリーズしました。
下記で解決しました。リダイレクトするところで「>/dev/null 2>&1」と書いたら動きました。

$cmd = 'ssh -f -N -L 19000:localhost:3306 [e-mail] -i ./key/id_rsa >/dev/null 2>&1';

公式のヘルプに該当すると思われる記載があります。

プログラムがこの関数で始まる場合、 バックグラウンドで処理を続けさせるには、 プログラムの出力をファイルや別の出力ストリームにリダイレクトする必要があります。 そうしないと、プログラムが実行を終えるまで PHP はハングしてしまいます。

Permissions ｘｘｘｘ for 'ｘｘｘｘｘｘid_rsa' are too open.

鍵ファイルの権限が600である必要があるようです。下記のようなコマンドで解決しました。

$ chmod 0600 ｘｘｘｘid_rsa

DB接続部分でエラー

すみません。エラーメッセージをメモしていませんでしたが上記エラーを解決した段階でDB接続できない状態でした。下記修正で解決しました。

  // 修正前
  $dsn = 'mysql:dbname=mydb;host=127.0.0.1;charset=utf8mb4';

  // 修正後
  $dsn = 'mysql:dbname=mydb;host=127.0.0.1:19000;charset=utf8mb4';

確かにポートフォワーディングしているので当然ですね。19000を追加することにより接続できました。
ご返信くださいましたCHERRY様、ockeghem様(ミサ曲がお好きでしょうか。)、ありがとうございました。