MySQLへのリモートアクセスを許可する方法

導入

多くのウェブサイトやアプリケーションは、ウェブサーバーとそれをサポートするデータベースを単一のマシンでホストするところから始まります。しかし、時間の経過とともに、このような構成は煩雑になり、拡張が困難になる可能性があります。一般的な解決策は、リモートデータベースを運用することでこれらの機能を分離し、サーバーとデータベースをそれぞれ別のマシンで独自のペースで拡張できるようにすることです。.

MySQLデータベースをリモートでセットアップする際にユーザーが遭遇する最も一般的な問題の一つは、MySQLインスタンスがローカル接続のみをリッスンするように設定されていることです。これはMySQLのデフォルト設定ですが、リモートデータベースのセットアップには機能しません。MySQLはサーバーにアクセスできる外部IPアドレスをリッスンする必要があるためです。これを有効にするには、mysqld.cnfファイルを開いてください。

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

bind-address コマンドで始まる行に移動します。次のようになります。

. . .
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1
. . .

デフォルトでは、この値は127.0.0.1に設定されており、サーバーはローカル接続のみをリッスンします。外部IPアドレスを参照するには、このディレクティブを変更する必要があります。トラブルシューティングのために、このディレクティブをワイルドカードIPアドレス（*、::、または0.0.0.0）に設定することもできます。

. . .
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 0.0.0.0
. . .

この行を変更したら、ファイルを保存して閉じます (nano で編集した場合は、CTRL + X、Y、ENTER の順に押します)。.

次に、MySQL サービスを再起動して、mysqld.cnf に加えた変更を適用します。

sudo systemctl restart mysql

リモートホストからデータベースに接続するために使用するMySQLユーザーアカウントがある場合は、そのアカウントをローカルホストではなくリモートサーバーから接続するように再設定する必要があります。これを行うには、MySQLクライアントをMySQL rootユーザーまたは他の権限を持つアカウントで開きます。

sudo mysql

root に対してパスワード認証が有効になっている場合は、代わりに次のコマンドを使用して MySQL シェルにアクセスする必要があります。

mysql -u root -p

ユーザーホストを変更するには、MySQLコマンドのRENAME USERを使用します。以下のコマンドを実行します。sammyをMySQLユーザー名に、remote_server_ipをリモートサーバーのIPアドレスに置き換えてください。

RENAME USER 'sammy'@'localhost' TO 'sammy'@'remote_server_ip';

あるいは、次のコマンドを使用して、リモートホストからのみ接続する新しいユーザーアカウントを作成することもできます。

CREATE USER 'sammy'@'remote_server_ip' IDENTIFIED BY 'password';

次に、新しいユーザーに、特定のニーズに合わせて適切な権限を付与します。次の例では、データベース、テーブル、ユーザーの作成、変更、削除を行うグローバル権限に加え、サーバー上の任意のテーブルに対してデータの挿入、更新、削除を行う権限をユーザーに付与します。また、SELECTによるデータのクエリ、REFERENCESキーワードによる外部キーの作成、RELOAD権限によるFLUSH操作の実行も許可します。ただし、ユーザーに必要な権限のみを付与し、必要に応じてユーザー権限を調整できるようにしてください。.

GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'sammy'@'remote_server_ip' WITH GRANT OPTION;

その後、FLUSH PRIVILEGESコマンドを実行することをお勧めします。これにより、以前のCREATE USERコマンドおよびGRANTコマンドによってサーバーが確保したメモリが解放されます。

FLUSH PRIVILEGES;

その後、MySQL クライアントを終了できます。

exit

最後に、データベースサーバーにファイアウォールが構成されていると仮定すると、MySQL トラフィックを許可するために、ポート 3306 (デフォルトの MySQL ポート) も開く必要があります。.

特定のマシンからのみデータベースサーバーにアクセスしたい場合は、以下のコマンドでそのマシンにリモートデータベースへの排他的な接続権限を与えることができます。remote_IP_address は、接続先のマシンの実際のIPアドレスに置き換えてください。

sudo ufw allow from remote_IP_address to any port 3306

将来、他のマシンからデータベースにアクセスする必要がある場合は、このコマンドを使用して一時的にアクセスを許可できます。その際、それぞれのIPアドレスを忘れずに指定してください。.

あるいは、次のコマンドを使用して、任意の IP アドレスから MySQL データベースへの接続を許可することもできます。

sudo ufw allow 3306

次に、別のデバイスからデータベースにリモートでアクセスしてみます。

mysql -u user -h database_server_ip -p

データベースにアクセスできる場合、設定ファイルのbind-addressディレクティブに問題があったことが確認できます。bind-addressを0.0.0.0に設定すると、あらゆるIPアドレスからサーバーへの接続が許可されるため、安全ではありませんのでご注意ください。それでもデータベースにリモートアクセスできない場合は、別の原因が考えられます。.