MySQL(MariaDB)にmysqlコマンドでリモート接続する

MySQLサーバに別マシンからリモート接続しようとしたらエラーが出てできなかったので、リモート接続できるように見直した設定について説明しています。MariaDBでも同じです。

このページの目的と動作環境

最終更新日:2023/1/23

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)
OS:Linux CentOS7.9
プライベート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
けど、-hオプションで自分のローカルIPアドレスを指定したら、エラーが出てログインできませんでした。
(あと、別マシンからリモート接続しようとしたけど、それもログインできませんでした。)
$ 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コマンドだけでも確認できます。
> 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    |
+-----------+-------------+
ホストがlocalhostのtestuserユーザが存在するのがわかります。

という事で、MySQLのtestuserユーザは「`testuser`@`localhost`」なので、現状別マシンからはリモートログインできない事がわかりました。

アマゾンでデータベース関連の本を探す [広告]

別マシンからmysqlコマンドでMySQLサーバにリモート接続できるユーザを作成する

現状、MySQLのtestuserユーザは別マシンからリモート接続できないので、一度testuserユーザを削除して、再度別マシンからMySQLサーバにリモート接続できるtestuserユーザを作成する事にします。

rootユーザでログインして、testuserユーザを削除します。

> drop user 'testuser'@'localhost';
そしてまたtestuserユーザを作成します。今度は別マシンからもリモートログインできるように「`testuser`@`%`」と指定します。
> 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)
「`testuser`@`%`」の`%`はワイルドカードなので、全てのホストからtestuserでMySQLサーバヘ接続できる設定になります。全てのマシンからMySQLサーバへ接続できるとなるとセキュリティ的に不安になりますが、私の環境では自分のPC上のMySQLはネットに公開しているわけでもないので問題ないです。

mysql.userを確認します。

> select host, user from mysql.user;
+-----------+-------------+
| Host      | User        |
+-----------+-------------+
| %         | testuser    |
| localhost | mariadb.sys |
| localhost | mysql       |
| localhost | root        |
+-----------+-------------+
あと、新規作成した「`testuser`@`%`」ユーザに付与された権限も確認します。
> 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.%";
ただ、これだけだとMySQLサーバが入っている同じマシンからログインする時に、-hオプション無しでログインする事ができないという面倒な事になるので、「'testuser'@'localhost' 」「'testuser'@'192.168.33.%'」の2つのユーザを作成した方が良いでしょう。
> 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    |
+-------------+--------------+
もしくは、「'testuser'@'localhost' 」と、リモートログインを許可する特定のホストのユーザ(「'testuser'@'192.168.33.1'」)を作成するとかでも良いと思います。
> 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";
あと、ユーザ名のHostの部分は、localhostやIPアドレスだけでなくドメイン名も指定可能です。
> CREATE USER 'testuser'@'host1.local-hogehoge765.com'  IDENTIFIED BY 'testpass';

以上、本記事ではMySQLサーバにリモートの別ホストからmysqlコマンドで接続できるためのユーザ作成について説明しました。ただ、ここで説明した話はユーザ権限のコントロールの話です。それ以外でも、例えばDBサーバ自体が別ホストからのリモート接続を拒否している場合もあると思うので、その場合は今回説明した設定をしてもMySQLサーバにリモート接続はできないと思います。