※ 本ページはプロモーションが含まれています。
このページの目的と動作環境
MySQLサーバ（MariaDBも）にリモートマシンからmysqlコマンド（-hオプション）でリモートログインを試したら、エラーが発生してできませんでした。
$ mysql -h 192.168.33.10 -utestuser -p
Enter password:
ERROR 1045 (28000): Access denied for user 'testuser'@'192.168.33.1' (using password: YES)
本記事では、別マシンからMySQLへリモート接続するために色々と試してみたので、試した事にについて自分の備忘録も兼ねて書いています。
◾️動作環境とバージョン情報についてです。
MySQL(MariaDB)の動作環境ですが、MacPCにMySQLをインストールして、また、MacPC上に仮想マシンのCentOS7マシン（Vagrant）上にMariaDBをインストールしています。
そして、MacPCからmysqlコマンドでCentOSマシンのMariaDBに接続を試したり、反対にCentOSマシンからmysqlコマンドでMacPCのMySQLに接続を試したりしました。
MacPCとCentOS7のバージョン情報やローカルIPアドレスについてです。
OS：macOS Big Sur（バージョン11.7.2）
プライベートIPアドレス：192.168.33.1
MySQL：MySQL Community Server 8.0.31
$ mysql -V
mysql Ver 8.0.31 for macos12 on x86_64 (MySQL Community Server - GPL)
プライベートIPアドレス：192.168.33.10
MariaDB：10.6
$ mysql -V
mysql Ver 15.1 Distrib 10.6.11-MariaDB, for Linux (x86_64) using readline 5.1
私の環境のMariaDBはmariadb,mysqlコマンドの両方を使えますが、説明では全てmysqlコマンドを使っています。
MySQLサーバへリモート接続できないユーザ設定
まず、MySQLサーバへリモート接続できなかったMySQLユーザについてです。
rootユーザでMySQLサーバへログインして、新規にtestuserユーザを作成しました。
> CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'testpass';
Query OK, 0 rows affected (0.002 sec)
> create database testdb;
Query OK, 1 row affected (0.000 sec)
> GRANT ALL PRIVILEGES ON `testdb`.* TO "testuser"@"localhost";
Query OK, 0 rows affected (0.002 sec)
ユーザ名はtestuser、パスワードはtestuser、あとtestdbというDBを作成して、そのtestdbの操作権限をtestuserに付与しました。
これで同じマシン上からtestuserでMySQLにログインする事ができました。
$ mysql -utestuser -p
（あと、別マシンからリモート接続しようとしたけど、それもログインできませんでした。）
$ mysql -h 192.168.33.10 -utestuser -p
Enter password:
ERROR 1045 (28000): Access denied for user 'testuser'@'192.168.33.10' (using password: YES)
この状況で、testuserで-hオプション無しでMySQLサーバへログインして、現在の自分（testuser）に付与された権限をSHOW GRANTSコマンドで確認します。（current_user()は現在ログインしているユーザ名です。）
> SHOW GRANTS FOR current_user();
+-----------------------------------------------------------------------------------------------------------------+
| Grants for testuser@localhost |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `testuser`@`localhost` IDENTIFIED BY PASSWORD '*00E247AC5F9AF26AE0194B41E1E769DEE1429A29' |
| GRANT ALL PRIVILEGES ON `testdb`.* TO `testuser`@`localhost` |
> SHOW GRANTS;
+-----------------------------------------------------------------------------------------------------------------+
| Grants for testuser@localhost |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `testuser`@`localhost` IDENTIFIED BY PASSWORD '*00E247AC5F9AF26AE0194B41E1E769DEE1429A29' |
| GRANT ALL PRIVILEGES ON `testdb`.* TO `testuser`@`localhost` |
この権限結果を見て気づいたのですが、ユーザが「`testuser`@`localhost`」となっていて、これだとtestuserユーザでlocalhost（同じマシン）からmysqlコマンドでログイン可能という事なので、別マシンからtestuserユーザでリモートログインできない設定になっていました。（同じマシンから-hオプションで自分のローカルIPアドレスを指定してもログインできない。）
ちなみにMySQLのユーザ一覧は、mysql.user（mysqlデータベースのuserテーブル）で確認できます。
私の環境でroorユーザでログインして実行した結果です。
> select host, user from mysql.user;
+-----------+-------------+
| Host | User |
+-----------+-------------+
| localhost | mariadb.sys |
| localhost | mysql |
| localhost | root |
| localhost | testuser |
+-----------+-------------+
という事で、MySQLのtestuserユーザは「`testuser`@`localhost`」なので、現状別マシンからはリモートログインできない事がわかりました。
(広告)アマゾンでSQL・DB（データベース）関連の本(Kindle)を探す！
別マシンからmysqlコマンドでMySQLサーバにリモート接続できるユーザを作成する
現状、MySQLのtestuserユーザは別マシンからリモート接続できないので、一度testuserユーザを削除して、再度別マシンからMySQLサーバにリモート接続できるtestuserユーザを作成する事にします。
rootユーザでログインして、testuserユーザを削除します。
> drop user 'testuser'@'localhost';
> CREATE USER 'testuser'@'%' IDENTIFIED BY 'testpass';
Query OK, 0 rows affected (0.007 sec)
> GRANT ALL PRIVILEGES ON `testdb`.* TO "testuser"@"%";
Query OK, 0 rows affected (0.002 sec)
mysql.userを確認します。
> select host, user from mysql.user;
+-----------+-------------+
| Host | User |
+-----------+-------------+
| % | testuser |
| localhost | mariadb.sys |
| localhost | mysql |
| localhost | root |
+-----------+-------------+
> SHOW GRANTS FOR `testuser`@`%`;
+---------------------------------------------------------------------------------------------------------+
| Grants for testuser@% |
+---------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `testuser`@`%` IDENTIFIED BY PASSWORD '*00E247AC5F9AF26AE0194B41E1E769DEE1429A29' |
| GRANT ALL PRIVILEGES ON `testdb`.* TO `testuser`@`%` |
+---------------------------------------------------------------------------------------------------------+
新規ユーザ「`testuser`@`%`」を作成したので、別マシンからtestuserユーザでMySQLサーバにリモート接続を試してみます。
$ mysql -h 192.168.33.10 -utestuser -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
〜
「`testuser`@`%`」だと、全てのホストからtestuserでMySQLサーバに接続できるので、セキュリティを考慮して自分のローカルネットワーク内のマシンだけに限定したい場合、作成するユーザを「`testuser`@`192.168.33.%`」とできます。
> CREATE USER 'testuser'@'192.168.33.%' IDENTIFIED BY 'testpass';
> GRANT ALL PRIVILEGES ON `testdb`.* TO "testuser"@"192.168.33.%";
> CREATE USER 'testuser'@'192.168.33.%' IDENTIFIED BY 'testpass' , 'testuser'@'localhost' IDENTIFIED BY 'testpass';
> GRANT ALL PRIVILEGES ON `testdb`.* TO "testuser"@"192.168.33.%", "testuser"@"localhost";
> select user, host from mysql.user;
+-------------+--------------+
| User | Host |
+-------------+--------------+
| testuser | 192.168.33.% |
| mariadb.sys | localhost |
| mysql | localhost |
| root | localhost |
| testuser | localhost |
+-------------+--------------+
> CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'testpass';
> CREATE USER 'testuser'@'192.168.33.1' IDENTIFIED BY 'testpass';
> GRANT ALL PRIVILEGES ON `testdb`.* TO "testuser"@"192.168.33.1", "testuser"@"localhost";
> CREATE USER 'testuser'@'host1.local-hogehoge765.com' IDENTIFIED BY 'testpass';
以上、本記事ではMySQLサーバにリモートの別ホストからmysqlコマンドで接続できるためのユーザ作成について説明しました。ただ、ここで説明した話はユーザ権限のコントロールの話です。それ以外でも、例えばDBサーバ自体が別ホストからのリモート接続を拒否している場合もあると思うので、その場合は今回説明した設定をしてもMySQLサーバにリモート接続はできないと思います。