MySQL Workbenchでリモートサーバー上のMySQLにSSH接続する方法【接続できない場合の対処法あり】

リモートサーバー上に構築したMySQLのデータを見たいときに、ターミナルからリモートサーバーにSSH接続して、コマンド実行してMySQLにログインして、SQL文実行して… とするのは面倒ですよね。
MySQL Workbenchを使えばお使いのmacOSやWindowsから簡単にリモートサーバー上のMySQLに接続でき、さらにデータ参照も手軽に行えてしまいます。
MySQLを使っているならMySQL Workbenchは必需品です。
  • macOS Catalina
  • MySQL Ver 14.14 Distrib 5.7.27
  • MySQL Workbench 8.0.18.CE

MySQL Workbench

MySQL WorkbenchはMySQL公式のデータベース設計ツールです。
macOS用のMySQL対応データベース設計ツールは他にphpMyAdminやSequel Proがありますが、公式だけあって最も使いやすいツールだと思います。
#1.png 150KB

個人的にmacOSのダークモードに対応しているのはポイント高いです😊

前提

ターミナルからsshコマンドでリモートサーバーに接続できる状態であるものとします。
以下のコマンドで接続できればOKです。
$ ssh remote-server

インストール

それではさっそくインストールから行っていきましょう。
MySQL Workbenchにアクセスします。
#2.png 157KB

「ダウンロードはこちら」をクリックします。
#3.png 110KB

Downloadボタンをクリックします。
#4.png 81.8KB

Loginボタンをクリックします。
#5.png 4.98KB

Oracleプロファイルにログインします。
#6.png 30.8KB

Download Nowボタンをクリックします。
#7.png 57.1KB

ダウンロードしたダンプファイルを開き、MySQL Workbenchをアプリケーションに追加します。
これでインストールは完了です。

事前確認

後述のSSH接続設定でリモートサーバーへの接続情報が必要になるので事前に確認しておきます。
$ cat ~/.ssh/config
Host remote-server
  HostName xxx.xxx.xxx.xxx
  User user
  Port 10022
  IdentityFile  ~/.ssh/remote-server/id_rsa

SSH接続設定

MySQL Workbenchを起動してSSH接続設定を行っていきましょう。
#8.png 169KB

起動すると初期画面が表示されます。
「MySQL Connections」の横の「+」ボタンをクリックします。
#9.png 108KB

Connection MethodをStandard TCP/IP over SSHに変更します。
#10.png 28.5KB

各パラメーターを以下のとおり入力します。
  • Connection Name:任意の名前
  • SSH Hostname:リモートサーバーのIPアドレスとポート(事前確認参照)
  • SSH Username:SSH接続のユーザー名(事前確認参照)
  • SSH Passwored:SSH接続のパスワード(事前確認参照、Store in Keychain ...ボタンをクリックして表示されたPasswordに入力)
  • SSH Key File:SSH接続の鍵ファイル(事前確認参照)
  • MySQL Hostname:127.0.0.1
  • MySQL Server Port:3306
  • Username:root
  • Password:MySQLのパスワード(Store in Keychain ...ボタンをクリックして表示されたPasswordに入力)
  • Default Schema:データベース名

すべて入力するとこんな感じになります。
#11.png 63.7KB

保存する前にTest Connectionボタンをクリックします。
#12.png 59.1KB

もう一度SSH接続のパスワードを入力します。
#13.png 14.1KB

接続に成功しました!

接続できない場合

環境によっては以下のようなメッセージが表示されて接続できない場合があります。
  • Could not connect...
  • Failed to connect...
  • Access denied...
そんなときはMySQLのユーザー権限を追加すると接続できるようになるかもしれません。

リモートサーバーにSSH接続します。
$ ssh remote-server
MySQLにログインします。
$ mysql -u root -p
Enter password: 
...

mysql>
まず、現在のユーザー権限はどうなっているか確認します。
mysql> SHOW GRANTS;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
root@localhostというユーザー権限があります。
root@localhostとは<ユーザー名>@<接続元>という意味で、「接続元がlocalhostのrootユーザーはすべての権限を持っている」という意味になります。

MySQL WorkbenchはまずSSH接続でリモートサーバーに接続し、リモートサーバーから自分の中のMySQLに接続しているので、通常だったらroot@localhostというユーザー権限だけで接続できるはずです。
しかし、環境によっては接続元が外部のユーザー権限でないと接続エラーになる場合もあるかもしれません。

ユーザー権限を追加するには以下のコマンドを実行します。
mysql> GRANT ALL ON *.* TO root@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected, 1 warning (0.00 sec)
コマンドの意味は以下のとおりです。
  • GRANT:権限を与える
  • ALL:すべての権限(CRUD)
  • *.*:全データベースの全テーブル
  • root@'%':すべての接続元のrootユーザー('%'はワイルドカードで、使用する場合は''で囲む)
  • IDENTIFIED BY:rootユーザーの場合はパスワードが必須
例はかなりセキュリティリスクの高いユーザー権限ですので、実際には権限や対象のデータベースを絞ったり、root以外のユーザーにしたほうがいいと思います。
mysql> SHOW GRANTS FOR root@'%';
+-------------------------------------------+
| Grants for root@%                         |
+-------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' |
+-------------------------------------------+
1 row in set (0.00 sec)
ユーザー権限が追加されました。

おまけ:ユーザー・ユーザー権限の削除

ユーザーの削除。
mysql> DROP USER root@'%';
ユーザー権限の削除。
mysql> REVOKE ALL ON *.* FROM root@'%';
これはユーザー権限だけ削除されるため、ユーザーは残ります。
mysql> SHOW GRANTS FOR root@'%';
+----------------------------------+
| Grants for root@%                |
+----------------------------------+
| GRANT USAGE ON *.* TO 'root'@'%' |
+----------------------------------+
1 row in set (0.01 sec)
権限がないことを表すUSAGEに変更されています。

まとめ

MySQL WorkbenchはMySQLを使うならほぼ必須といってもいいかもしれません。
わかりやすいGUIインターフェースなので使い方に戸惑うことも少ないと思います。
ぜひMySQL Workbenchを導入して使いこなせるようになりましょう!