【コピペだけでOK】MySQLへの外部接続を許可する方法

「データベースの外部接続を許可するのは面倒」
「設定ファイルの修正をするのは怖い」

もし、上記のように思っているなら、この記事をご覧ください。
MySQLの外部接続をコピペだけで済ますことができます。

MySQLの設定ファイルなども触りません。
それほど簡単であるにも関わらず、セキュリティのこともちゃんと考えています。

本記事の内容

外部接続用のユーザーを作成する
ファイアウォールでポート開放
外部接続確認

それでは、上記に沿って解説していきます。

外部接続用のユーザーを作成する

MySQLの初期設定を適切に行うと、rootでのリモート接続は不可となります。
セキュリティ上、それは仕方がありません。

上記のように設定したインストールについては、次の記事で解説しています。

そこで、リモート接続を認めるユーザーを別で作成することになります。
このユーザーのことを外部接続用のユーザーと呼びます。

ここでは、外部接続用のユーザーを作成していきます。
作成していく上で、次の3つがポイントになります。

認証プラグイン
認証ホスト
権限

それぞれを下記で説明します。

認証プラグイン

まずは、デフォルトの認証プラグインを確認します。
次のクエリを実行します。

mysql> show variables like 'default_authentication_plugin';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
1 row in set (0.00 sec)

MySQL 8.0.4以降では、caching_sha2_passwordがデフォルトになりました。
それ以前のバージョンでは、mysql_native_passwordがデフォルトでした。

新しい認証方式であるcaching_sha2_passwordの方が、セキュリティ的には良いのでしょう。
しかし、caching_sha2_passwordに対応しているクライアントばかりではありません。

例えば、PHPなら7.2.4（7.1.16）より前のバージョンではアウトです。
それ以前のバージョンは、caching_sha2_passwordに未対応になります。

そのため、そのままではPHPからMySQLに接続ができません。
このような状況への対策として、次の2つの方法が存在します。

デフォルトの認証プラグインを変更する
認証プラグインを指定してユーザーを作成する

デフォルトには、それなりに意味があります。
だから、それをむやみやたらに変更するのは避けます。
したがって、2の認証プラグインを指定する方法でユーザーを作成します。

権限

今回は、外部接続用のユーザーを作成しています。
そのユーザーにどこまで権限を付与するのかは、それぞれの状況で異なります。

そのため、ここはプログラマー（開発者）視点で行きます。
プログラムからの利用の場合、データベースはすでにあります。
つまり、インフラ（システム管理者）からデータベースは付与される形です。

そのデータベースの中で、DB操作を行うことになります。
下記を見ると様々な権限があります。
https://dev.mysql.com/doc/refman/8.0/ja/privileges-provided.html

状況に応じて、これらから選択して付与するのが理想でしょう。
でも、理想は実現するのは困難です。

よって、今回は全権限を付与することにします。
ただし、他のデータベースやMySQL自体に影響は与えてはいけません。
あくまで、指定されたデータベースの中だけでの全権限です。

まとめ

上記で3つのポイントを確認してきました。
そして、確認した内容通りにユーザー作成を行います。

ここでの作業は、mysqlのrootで行います。
ユーザー作成は、次のクエリとなります。

mysql> CREATE USER 'dbuser'@'%' IDENTIFIED WITH mysql_native_password BY 'Pass@word1';
Query OK, 0 rows affected (0.00 sec)

認証プラグインは、mysql_native_passwordを指定しています。
何も指定しないと、デフォルトのcaching_sha2_passwordとなります。
「%」を指定して、どのホストからでも接続を許可しています。

次に、やりたい放題させるデータベースを作成します。

mysql> CREATE DATABASE free_db;
Query OK, 1 row affected (0.00 sec)

最後に、権限を付与します。

mysql> GRANT ALL PRIVILEGES ON free_db.* TO 'dbuser'@'%';
Query OK, 0 rows affected (0.01 sec)

上記で行った更新をすぐに反映させます。

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

ユーザー一覧で確認します。

mysql> select Host, User from mysql.user;
+-----------+------------------+
| Host      | User             |
+-----------+------------------+
| %         | dbuser           |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
5 rows in set (0.00 sec)

あとは、新規に作成したユーザーでログインできればOKです。

$ mysql -u dbuser -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.25 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

以上、外部接続用のユーザーの作成について説明しました。
次は、ファイアウォールの開放を行います。

ファイアウォールでポート開放

MySQLの初期ポートを変更していないなら、次のコマンドで状況を確認可能です。

$ sudo ufw status | grep '3306'

何も表示されないなら、「3306」は未開放という状況になります。
なお、「ufw」コマンドが動かない場合は、次の記事をご覧ください。
「ファイアウォールの設定」の箇所に、「ufw」のインストールについて説明があります。

「ufw」コマンドが動くなら、次のコマンドを実行します。

sudo ufw allow 3306

確認します。

$ sudo ufw status | grep '3306'
3306                       ALLOW       Anywhere
3306 (v6)                  ALLOW       Anywhere (v6)

ポートが、開放されています。
あと、ここで認証ホストの設定（IPアドレスでのアクセス制御）を行うのもありです。
私なら、ここで行わずにhosts.allowで簡単に済ませます。

以上、ファイアウォールの開放について説明しました。
最後は、外部接続確認を行います。

外部接続確認

動作確認を行うためには、先ほど作業していたサーバーとは別のサーバーにアクセスします。
「mysql」コマンドが利用できるなら、次のコマンドで確認できます。

$ mysql -h 192.168.33.30 -u dbuser -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.25 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

「192.168.33.30」が、先ほどまで作業していたサーバーのIPです。
これはこれでOKですが、どうせならプログラムからの接続も確認しましょう。

Pythonでの接続確認は、次の記事で解説しています。