MariaDB 10.6に外部のホストから接続する方法

2022年1月9日

環境
Windows 10 64bit
MariaDB 10.6.4

操作方法
1.現在の状況を確認します

MariaDB [(none)]> select user, host from mysql.user;
+-------------+-----------------+
| User        | Host            |
+-------------+-----------------+
| arkgame03   | %               |
| root        | 127.0.0.1       |
| root        | ::1             |
| root        | desktop-2nbj8il |
| arkgame22   | localhost       |
| mariadb.sys | localhost       |
| root        | localhost       |
+-------------+-----------------+
7 rows in set (0.001 sec)

2.外部のホストからアクセス可能にします
書式
grant all privileges
on データベース名.* to ユーザー名@"IPアドレス"
identified by 'パスワード’ with grant option;

操作例

MariaDB [(none)]> grant all privileges
    -> on testdb.* to arkgame22@" 172.20.10.4"
    -> identified by '123#@' with grant option;
Query OK, 0 rows affected (0.080 sec)

設定確認

MariaDB [(none)]> select user, host from mysql.user where user='arkgame22';
+-----------+--------------+
| User      | Host         |
+-----------+--------------+
| arkgame22 |  172.20.10.4 |
| arkgame22 | localhost    |
+-----------+--------------+
2 rows in set (0.001 sec)

3.どこでもホストからアクセス可能にします
書式
grant all privileges
on *.* to root@"%"
identified by 'パスワード’ with grant option;
操作例

MariaDB [(none)]> grant all privileges
    -> on *.* to root@"%"
    -> identified by '12345@' with grant option;
Query OK, 0 rows affected (0.049 sec)

設定確認

MariaDB [(none)]> select user, host from mysql.user where user='root';
+------+-----------------+
| User | Host            |
+------+-----------------+
| root | %               |
| root | 127.0.0.1       |
| root | ::1             |
| root | desktop-2nbj8il |
| root | localhost       |
+------+-----------------+
5 rows in set (0.001 sec)

4.接続中ユーザーの権限情報を確認します

MariaDB [(none)]> SHOW GRANTS;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED BY PASSWORD '*39CEF9ED87552612EC8ED3CB95922DB17CA607C3' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                                                          |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.017 sec)