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

多くのWebサイトとアプリケーションは、同じマシンでホストされているWebサーバーとデータベースバックエンドから始まります。ただし、時間の経過とともに、このような設定は煩雑になり、スケーリングが困難になる可能性があります。一般的な解決策は、リモートデータベースを設定してこれらの機能を分離し、サーバーとデータベースをそれぞれのマシン上で独自のペースで拡張できるようにすることです。

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

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

bind-addressディレクティブで始まる行に移動します。次のようになります。

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

. . .
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アドレスを参照するには、このディレクティブを変更する必要があります。トラブルシューティングの目的で、このディレクティブを*、::、または0.0.0.0のいずれかのワイルドカードIPアドレスに設定できます。

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

. . .
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
. . .

注： MySQLの特定のバージョンでは、bind-addressディレクティブがデフォルトでmysqld.cnfファイルに含まれていない場合があります。この場合、ファイルの下部に次の強調表示された行を追加します。

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

. . .
[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log
bind-address            = 0.0.0.0

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

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

sudo systemctl restart mysql

リモートホストからデータベースに接続するために使用する予定の既存のMySQLユーザーアカウントがある場合は、localhostではなくリモートサーバーから接続するようにそのアカウントを再構成する必要があります。これを行うには、MySQLクライアントを root MySQLユーザーとして、または別の特権ユーザーアカウントで開きます。

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';

注：このコマンドは、MySQLのデフォルトの認証プラグインcaching_sha2_passwordで認証するユーザーを作成します。ただし、PHPの一部のバージョンには、このプラグインで問題を引き起こす可能性のある既知の問題があります。

このデータベースをPHPアプリケーション（たとえばphpMyAdmin）で使用する場合は、代わりに古いmysql_native_passwordプラグインで認証するリモートユーザーを作成することをお勧めします。

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

よくわからない場合は、次のコマンドを使用して、caching_sha2_pluginで認証し、後でALTERで認証するユーザーをいつでも作成できます。

ALTER USER 'sammy'@'remote_server_ip' IDENTIFIED WITH mysql_native_password BY 'password';

次に、新しいユーザーに特定のニーズに適した特権を付与します。次の例では、CREATE、ALTER、およびDROPデータベース、テーブル、およびユーザーにグローバル権限を付与し、INSERTに権限を付与します。サーバー上の任意のテーブルからのUPDATE、およびDELETEデータ。また、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のデフォルトポート）も開く必要があります。

1つの特定のマシンからのみデータベースサーバーにアクセスすることを計画している場合は、次のコマンドを使用して、そのマシンにデータベースにリモート接続するための排他的アクセス許可を付与できます。 remote_IP_addressは、接続する予定のマシンの実際のIPアドレスに置き換えてください。

sudo ufw allow from remote_IP_address to any port 3306

将来、他のマシンからデータベースにアクセスする必要がある場合は、このコマンドを使用して、アドホックベースでデータベースへのアクセスを許可できます。それぞれのIPアドレスを含めることを忘れないでください。

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

警告：このコマンドを使用すると、誰でもMySQLデータベースにアクセスできます。データベースに機密データが保持されている場合は、実行しないでください。

sudo ufw allow 3306

これに続いて、別のマシンからリモートでデータベースにアクセスしてみてください。

注：特定のIPアドレスからの接続のみを許可するファイアウォールルールを追加した場合は、そのアドレスに関連付けられたマシンでデータベースにアクセスする必要があります。

mysql -u user -h database_server_ip -p

データベースにアクセスできる場合は、構成ファイルのbind-addressディレクティブに問題があることが確認されます。ただし、bind-addressを0.0.0.0に設定すると、任意のIPアドレスからサーバーに接続できるため、安全ではないことに注意してください。一方、それでもデータベースにリモートでアクセスできない場合は、他の何かが問題を引き起こしている可能性があります。いずれの場合も、 Ubuntu 18.04 でMySQLを使用してサイトのパフォーマンスを最適化するリモートデータベースを設定する方法に関するガイドに従って、より安全なリモートデータベース構成を設定すると役立つ場合があります。