[MySQL] MySQL 외부 접속 불가능 현상 해결 방법 (접근 권한)

목차

1. 증상

1.1. ERROR 1130 (HY000): Host 'Host IP' is not allowed to connect to this MariaDB server

# mysql -u root -h HostIP -p --port 3306
Enter password: ********
ERROR 1130 (HY000): Host 'Host IP' is not allowed to connect to this MariaDB server

원인: 외부에서 접속할 경우 접속 권한이 없는 경우 다음과 같은 에러가 발생합니다.

해결: MySQL(MariaDB)가 설치된 서버에서 권한을 추가해 줍니다. (이 게시물의 3번을 참조해주세요)

1.2. ERROR 2002 (HY000): Can't connect to MySQL server on 'Host IP' (10061)

# mysql -u root -h HostIP -p --port 3306
Enter password: ************
ERROR 2002 (HY000): Can't connect to MySQL server on 'cnwizdev.iptime.org' (10061)

원인: 10061 오류는 서버의 해당 포트가 OPEN 상태가 아닌 경우에 발생합니다.

= MySQL(MariaDB) 이 서비스되고 있지 않다고 볼 수 있습니다.

해결: 다음 명령어를 이용해 서비스 상태를 확인하고 Mysql(MariaDB)를 서비스 시작(재시작) 해 줍니다.

mysql(mariadb)프로세스가 실행 중인지 확인
# ps -ef | grep mysql
# ps -ef | grep mariadb

포트가 사용되고 있는지 확인(기본 포트 3306)
# netstat -ntap | grep LISTEN
# netstat -ntap | grep 3306

서비스 상태 확인
# service mariadb status
# service mysql status

# systemctl status mysql
# systemctl status mariadb

1.3. ERROR 2002 (HY000): Can't connect to MySQL server on 'Host IP' (10060)

# mysql -u root -h HostIP -p --port 3306
Enter password: ************
ERROR 2002 (HY000): Can't connect to MySQL server on 'cnwizdev.iptime.org' (10060)

원인: 10060 오류는 로컬이 아닌 외부에 있는 MYSQL에 연결을 시도하는 경우에도 서버에서 10060 포트가 OPEN 상태가 아닌 경우에 오류가 발생합니다.

= 서비스 포트가 막혀 있는 경우에 발생합니다. (기본 3306 포트)

해결: 방화벽 포트를 추가해 줍니다. (이 게시물의 2번을 참조해주세요)

 

---

2. 방화벽 설정(firewall)

- 필자는 CentOS 7 기준으로 게시글을 작성했습니다.

- CentOS 7의 경우 iptables 대신 netfilter를 기본 방화벽으로 사용합니다. CentOS 7의 netfilter의 관리 프로그램은 firewalld입니다.

2.1. 방화벽 리스트 확인

MySQL(MariaDB)의 기본 포트 3306 포트가 열려 있는지 확인해 봅니다.

# firewall-cmd --zone=public --list-all
public (active)
  target: default
  icmp-block-inversion: no
  interfaces: enp0s3
  sources:
  services: dhcpv6-client ssh
  ports:
  protocols:
  masquerade: no
  forward-ports:
  source-ports:
  icmp-blocks:
  rich rules:

2.2. 방화벽 포트 추가

MySQL(MariaDB)의 기본 포트 3306 포트를 추가합니다.

포트를 추가합니다.
# firewall-cmd --permanent --zone=public --add-port=3306/tcp
success

적용을 위해 서비스 재구동 합니다.
# firewall-cmd --reload
success

리스트를 확인합니다.
# firewall-cmd --zone=public --list-all
public (active)
  target: default
  icmp-block-inversion: no
  interfaces: enp0s3
  sources:
  services: dhcpv6-client ssh
  ports: 3306/tcp
  protocols:
  masquerade: no
  forward-ports:
  source-ports:
  icmp-blocks:
  rich rules:

2.3. 방화벽 포트 삭제

포트를 잘 못 추가했을 경우 다음 명령어를 이용해 삭제합니다.

포트를 삭제합니다.
#firewall-cmd --permanent --zone=public --remove-port=3306/tcp

적용을 위해 서비스 재구동 합니다.
#firewall-cmd --reload

 

---

3. MySQL(MariaDB) 외부 접근 권한 설정

- MySQL(MariaDB)가 설치되어 있는 서버에서 DB의 root 계정으로 접속해 외부 접근 권한을 적용합니다.

- 필자는 root 계정의 외부 접근을 허용하겠습니다.

# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.0.31-MariaDB Source distribution

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]>​

3.1. 권한 조회

접근 가능한 계정 정보를 조회합니다.

SELECT host, user, password FROM mysql.user

MariaDB [(none)]> SELECT host, user, password FROM mysql.user;
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *77C169F7A700AC5F4588FDCA02BF551CD2340C49 |
| 127.0.0.1 | root | *77C169F7A700AC5F4588FDCA02BF551CD2340C49 |
+-----------+------+-------------------------------------------+
2 rows in set (0.00 sec)

위의 쿼리로 조회해보면 root 계정으로 외부 접근 권한이 없는 것을 확인할 수 있습니다.

3.2. 권한 추가

★ 권한을 추가 또는 삭제 후 FLUSH PRIVILEGES; 를 입력해 적용해 줍니다.

3.2.1. 모든 IP 접근 허용

grant all privileges on *.* to 'root'@'%' identified by 'root의 패스워드';

: root@% = % 는 모두 접근이 가능하다는 것을 뜻합니다.

MariaDB [(none)]> SELECT host, user, password FROM mysql.user;
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *77C169F7A700AC5F4588FDCA02BF551CD2340C49 |
| 127.0.0.1 | root | *77C169F7A700AC5F4588FDCA02BF551CD2340C49 |
+-----------+------+-------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> grant all privileges on *.* to 'root'@'%' identified by '1234';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> SELECT host, user, password FROM mysql.user;
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *77C169F7A700AC5F4588FDCA02BF551CD2340C49 |
| 127.0.0.1 | root | *77C169F7A700AC5F4588FDCA02BF551CD2340C49 |
| %         | root | *A4B6157319038724E3560894F7F932C8886EBFCF |
+-----------+------+-------------------------------------------+
3 rows in set (0.00 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

3.2.2. 특정 IP 접근 허용

grant all privileges on *.* to 'root'@'특정 IP' identified by 'root의 패스워드';

MariaDB [(none)]> grant all privileges on *.* to 'root'@'192.168.0.2' identified by '1234';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SELECT host, user, password FROM mysql.user;
+-------------+------+-------------------------------------------+
| host        | user | password                                  |
+-------------+------+-------------------------------------------+
| localhost   | root | *77C169F7A700AC5F4588FDCA02BF551CD2340C49 |
| 192.168.0.2 | root | *A4B6157319038724E3560894F7F932C8886EBFCF |
| 127.0.0.1   | root | *77C169F7A700AC5F4588FDCA02BF551CD2340C49 |
| %           | root | *A4B6157319038724E3560894F7F932C8886EBFCF |
+-------------+------+-------------------------------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

3.3.3. 특정 IP 대역 접근 허용

grant all privileges on *.* to 'root'@'IP대역' identified by 'root의 패스워드';

MariaDB [(none)]> grant all privileges on *.* to 'root'@'192.168.50.%' identified by '1234';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant all privileges on *.* to 'root'@'192.168.%' identified by '1234';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SELECT host, user, password FROM mysql.user;
+--------------+------+-------------------------------------------+
| host         | user | password                                  |
+--------------+------+-------------------------------------------+
| localhost    | root | *77C169F7A700AC5F4588FDCA02BF551CD2340C49 |
| 192.168.0.2  | root | *A4B6157319038724E3560894F7F932C8886EBFCF |
| 127.0.0.1    | root | *77C169F7A700AC5F4588FDCA02BF551CD2340C49 |
| %            | root | *A4B6157319038724E3560894F7F932C8886EBFCF |
| 192.168.50.% | root | *A4B6157319038724E3560894F7F932C8886EBFCF |
| 192.168.%    | root | *A4B6157319038724E3560894F7F932C8886EBFCF |
+--------------+------+-------------------------------------------+
6 rows in set (0.00 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

3.3. 권한 삭제

DELETE FROM mysql.user WHERE Host='접근 권한' AND User='사용자';

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

MariaDB [(none)]> SELECT host, user, password FROM mysql.user;
+--------------+------+-------------------------------------------+
| host         | user | password                                  |
+--------------+------+-------------------------------------------+
| localhost    | root | *77C169F7A700AC5F4588FDCA02BF551CD2340C49 |
| 192.168.0.2  | root | *A4B6157319038724E3560894F7F932C8886EBFCF |
| 127.0.0.1    | root | *77C169F7A700AC5F4588FDCA02BF551CD2340C49 |
| 192.168.50.% | root | *A4B6157319038724E3560894F7F932C8886EBFCF |
| 192.168.%    | root | *A4B6157319038724E3560894F7F932C8886EBFCF |
+--------------+------+-------------------------------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)