[리눅스] mysql 원격 접속 허용

mysql 원격 접속 허용

권한 확인

SELECT Host,User,plugin,authentication_string FROM mysql.user;

MariaDB [(none)]> SELECT Host,User,plugin,authentication_string FROM mysql.user;
+------------------+------------+--------+-----------------------+
| Host             | User       | plugin | authentication_string |
+------------------+------------+--------+-----------------------+
| localhost        | root       |        |                       |
| 127.0.0.1        | root       |        |                       |
| ::1              | root       |        |                       |
| localhost        | racktables |        |                       |
+------------------+------------+--------+-----------------------+
4 rows in set (0.000 sec)

MariaDB [(none)]>

권한 설정

특정 IP 대역, 특정 데이터베이스에 접근 허용

- 특정 IP 대역 : 192.168.0.0/24

- 특정 데이터베이스 : racktables_db

GRANT ALL PRIVILEGES ON 데이터베이스.* TO 'mysql_계정'@'아이피' IDENTIFIED BY 'mysql_계정_패스워드';

GRANT ALL PRIVILEGES ON *.* TO 'mysql_계정'@'%' IDENTIFIED BY 'mysql_계정_패스워드';

* 참고로 %은 모든 아이피를 포함하지만, localhost는 포함하지 않는다.

GRANT ALL PRIVILEGES ON racktables_db.* TO 'racktables'@'192.168.0.%' IDENTIFIED BY 'mysqlpassword!';

MariaDB [(none)]> GRANT ALL PRIVILEGES ON racktables_db.* TO 'racktables'@'192.168.0.%' IDENTIFIED BY 'mysqlpassword!';
Query OK, 0 rows affected (0.000 sec)

권한 확인

MariaDB [(none)]> SELECT Host,User,plugin,authentication_string FROM mysql.user;
+------------------+------------+--------+-----------------------+
| Host             | User       | plugin | authentication_string |
+------------------+------------+--------+-----------------------+
| localhost        | root       |        |                       |
| 127.0.0.1        | root       |        |                       |
| ::1              | root       |        |                       |
| localhost        | racktables |        |                       |
| 192.168.0.0/24   | racktables |        |                       |
+------------------+------------+--------+-----------------------+
5 rows in set (0.000 sec)

MariaDB [(none)]>

FLUSH PRIVILEGES;

원격지에서 mysql 서버 접속 확인

mac에 노트북에 mysql-client 설치

brew install mysql-client

> brew install mysql-client
...
==> mysql-client
mysql-client is keg-only, which means it was not symlinked into /opt/homebrew,
because it conflicts with mysql (which contains client libraries).

If you need to have mysql-client first in your PATH, run:
  echo 'export PATH="/opt/homebrew/opt/mysql-client/bin:$PATH"' >> ~/.zshrc

For compilers to find mysql-client you may need to set:
  export LDFLAGS="-L/opt/homebrew/opt/mysql-client/lib"
  export CPPFLAGS="-I/opt/homebrew/opt/mysql-client/include"

For pkg-config to find mysql-client you may need to set:
  export PKG_CONFIG_PATH="/opt/homebrew/opt/mysql-client/lib/pkgconfig"

echo 'export PATH="/opt/homebrew/opt/mysql-client/bin:$PATH"' >> ~/.zshrc

source ~/.zshrc

 

권한 삭제

DELETE FROM mysql.user WHERE Host='아이피' AND User='mysql_계정_패스워드';

DELETE FROM mysql.user WHERE Host='192.168.0.%' AND User='racktables';

MariaDB [(none)]> DELETE FROM mysql.user WHERE Host='192.168.0.%' AND User='racktables';
Query OK, 1 row affected (0.000 sec)