우분투 MySQL 서버 외부접속 허용 feat. AWS

우분투에서 MySQL을 설치하고 외부(웹서버 또는 워크밴치 등의 MySQL 매니징 프로그램) 접속 허용하는 법에 대해 알아보겠습니다.

본 포스팅은 AWS 기준이지만 여타 다른 종류의 서버도 쉽게 적용하실 수 있을거예요.

※ 이 포스팅에서는 어느 곳에서든지 접속을 허용하는 세팅을 주로 작성하겠지만 보안상 방화벽에서도, MySQL에서도 특정 아이피만 허용하여 사용하시는 것을 추천드립니다.

 

1. 방화벽 인바운드 포트 허용

2. MySQL 계정 및 DB 설정

  2-1. MySQL 접속 및 계정 확인

  2-2. 계정 추가

  2-3. DB 권한 확인

  2-4. DB 생성 및 권한 설정

3. MySQL 설정파일 수정

4. MySQL 서비스 재시작

 

1. 방화벽 인바운드 포트 허용

먼저 외부에서 MySQL에 접속할 때 사용하는 Port를 열어줘야 합니다.

AWS의 경우 보안그룹으로 들어가 해당 MySQL 서버에 인바운드 규칙을 추가해줍니다.

인바운드 규칙에 3306포트 관련 부분을 등록합니다.

MySQL 포트를 따로 설정하지 않은 경우

TCP 3306 ::/0

TCP 3306 0.0.0.0/0

부분을 참고하여 추가합니다.

MySQL 포트를 변경하신 분은 3306 부분을 변경하신 포트로 설정해주시면 됩니다.

 

2. MySQL 설정

※ [[[[[명령어 입력]]]]] 부분에서 괄호 다섯 번('[[[[[', ']]]]]') 부분은 빼고 입력해야합니다.

입력해야하는 부분이 잘 보이게 하려고 붙인겁니다. ㅎ;

 

현재 host가 %로 설정된 계정이 있고 그 계정이 사용 가능한 DB가 있다면 이 과정을 생략하셔도 됩니다.

2-1. mysql 접속 및 계정 확인

먼저 mysql을 실행하고 현재 계정을 확인해 봅시다.

내가 사용할 계정이 이미 host가 %로 등록되어 있다면 2-2.를 건너뛰고 2-3.으로 가시기 바랍니다.

# [[[[[mysql -u root -p]]]]]
Enter password: [[[[[비밀번호 입력]]]]]
Welcome to the MySQL monitor.

......

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

mysql> [[[[[use mysql;]]]]]
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> [[[[[select host, user from user;]]]]]
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | debian-sys-maint |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
4 rows in set (0.00 sec)

mysql>

1) mysql -u root -p

- 명령어를 실행하고 비밀번호를 입력하여 mysql에 접속합니다.

2) use mysql;

- mysql database를 선택합니다.

3) select host, user from user;

- 현재 계정의 정보를 일부 확인합니다.

- host는 접속을 시도하는 곳이고, user는 계정의 아이디라고 보시면 됩니다.

- host가 localhost이면 서버 내부에서 접근이 가능하고 특정 아이피 주소면 그 아이피에서만 접근이 가능합니다. 어디에서나 접근이 가능하게 하고 싶다면 %를 등록해야 합니다.

- 한 계정으로 여러 아이피를 접근허용하려면 계정추가를 같은 아이디로 여러 번 하시면 됩니다. (추가하는 법은 아래에서)

 

2-2. 계정 추가

이번엔 외부에서 접속할 계정을 추가해 봅시다.

mysql> [[[[[create user '추가_계정_아이디'@'%' identified by '추가_계정_비밀번호';]]]]]
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> [[[[[show variables like 'val%';]]]]]
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.00 sec)

mysql> [[[[[set global validate_password_policy=LOW;]]]]]
Query OK, 0 rows affected (0.00 sec)

mysql> [[[[[show variables like 'val%';]]]]]
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password_check_user_name    | OFF   |
| validate_password_dictionary_file    |       |
| validate_password_length             | 8     |
| validate_password_mixed_case_count   | 1     |
| validate_password_number_count       | 1     |
| validate_password_policy             | LOW   |
| validate_password_special_char_count | 1     |
+--------------------------------------+-------+
7 rows in set (0.00 sec)

mysql> [[[[[create user '추가_계정_아이디'@'%' identified by '추가_계정_비밀번호';]]]]]
Query OK, 0 rows affected (0.00 sec)

mysql> [[[[[select host, user from user;]]]]]
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | newid            |
| localhost | debian-sys-maint |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
5 rows in set (0.00 sec)

mysql>

1) create user '추가_계정_아이디'@'%' identified by '추가_계정_비밀번호';

- 추가_계정_아이디 : 아이디 (여기서는  newid 를 사용)

- 추가_계정_비밀번호 : 비밀번호

- 만약에 명령어를 실행했는데 위와 같이 ERROR 1819 (HY000): Your password does not satisfy the current policy requirements 문구가 나타났다면 생성하려고 입력한 비밀번호가 설정된 validate_password_policy 기준에 미치지 못하기 때문에 입니다.

이 문구가 나타나지 않았다면 6) 으로 가시면 됩니다.

- 계정을 볼 때는 '추가_계정_아이디'@'범위' 이렇게 계정 아이디와 범위를 한 세트로 봐야합니다.

계정의 아이디가 같아도 범위가 다르다면 여러 개 등록할 수 있습니다. 아래는 예시입니다.

// 계정등록 예
+-----------------+------------------+
| host            | user             |
+-----------------+------------------+
| 111.222.333.444 | newid            |
| 111.222.333.445 | newid            |
| 666.777.888.999 | newid            |
| localhost       | newid            |
| localhost       | debian-sys-maint |
| localhost       | doonerf          |
| localhost       | mysql.session    |
| localhost       | mysql.sys        |
| localhost       | root             |
+-----------------+------------------+

2) show variables like 'val%';

- 계정을 추가하려는데 ERROR 1819 문구를 보셨다면 현재 비밀번호 등록에 대한 정책을 볼 필요가 있습니다.

- show variables;라는 명령어를 실행하시면 여러 항목이 나타나는데 그 중에 val로 시작하는 항목만 보기 위한 명령어입니다.

3) set global validate_password_policy=LOW;

- 전역에 validate_password_policy 항목을 LOW로 설정합니다. 이 명령어를 실행하기 전 MEDIUM의 경우 대소문자, 숫자, 특수문자를 모두 포함하여 validate_password_length 이상의 글자 수로 비밀번호를 설정해야 하지만 LOW의 경우 validate_password_length 기준만 충족하면 비밀번호를 등록할 수 있습니다.

- 본인이 보안을 위해 설정을 바꾸지 않으려면 다시 set global validate_password_policy=기존값;을 실행하시면 됩니다.

4) show variables like 'val%';

- 2)와 같습니다. 수정된 validate_password_policy를 확인하기 위해 한 번 더 실행해줬습니다.

5) create user '추가_계정_아이디'@'%' identified by '추가_계정_비밀번호';

- 1)과 같습니다. 만약에 또 ERROR 1819 문구를 보셨다면 validate_password_length 이상의 글자 수로 비밀번호를 설정했는지 확인해 봅시다.

6) select host, user from user;

- 등록된 계정을 확인합니다. 추가된 계정이 목록에 나타났다면 성공입니다.

 

2-3. DB 권한 확인

사용할 계정이 특정 DB에 접근이 가능한 상태인지 확인해봅시다.

이미 사용하고자 하는 DB에 대한 권한이 있다면 이 과정을 건너뛰고 3.으로 가시기 바랍니다.

mysql> [[[[[show grants for 'newid'@'%';]]]]]
+-----------------------------------+
| Grants for newid@%                |
+-----------------------------------+
| GRANT USAGE ON *.* TO 'newid'@'%' |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> [[[[[show databases;]]]]]
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql>

1) show grants for '계정아이디'@'%';

- 계정아이디 부분에는 외부접속시 사용하실 MySQL 아이디를 쓰시면 됩니다.

- 이 명령어는 해당 계정의 권한을 확인할 때 사용합니다.

- 이 명령어를 실행했는데 외부접속시 사용할 DB에 대한 권한설정 내용이 나타났다면 이 과정을 건너뛰셔도 됩니다. 3.번으로 가세요.

- 위의 예제처럼 GRANT USAGE ON *.* TO '계정아이디'@'%' 한 줄만 나온다면 사용할 DB가 있는지 확인해봅니다. 확인 후 사용할 DB가 따로 존재한다면 2-4.에서 권한만 설정하고 DB 생성 부분은 건너뛰셔도 됩니다. 2)번으로..

2) show databases;

- 현재 생성되어 있는 DB목록을 확인합니다.

 

2-4. DB 생성 및 권한 설정

외부접속시 사용할 계정이 외부접속 후 사용할 DB에 대한 권한을 이미 가지고 있다면 이 과정을 생략하셔도 됩니다.

그렇지 않은 분들은 이 과정에서 DB를 생성해보고 권한도 부여해 보겠습니다.

mysql> [[[[[create database newdb;]]]]]
Query OK, 1 row affected (0.01 sec)

mysql> [[[[[show databases;]]]]]
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| newdb              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> [[[[[grant all privileges on newdb.* to 'newid'@'%';]]]]]
Query OK, 0 rows affected (0.01 sec)

mysql> [[[[[flush privileges;]]]]]
Query OK, 0 rows affected (0.00 sec)

mysql> [[[[[show grants for 'newid'@'%';]]]]]
+--------------------------------------------------+
| Grants for newid@%                               |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO 'newid'@'%'                |
| GRANT ALL PRIVILEGES ON `newdb`.* TO 'newid'@'%' |
+--------------------------------------------------+
2 rows in set (0.00 sec)

mysql>

1)  create database DB이름;

- 외부접속시 사용할 DB를 추가합니다. (여기서는  newdb ) 이미 같은 이름의 DB가 있다면 ERROR 1007 (HY000): Can't create database 'DB이름'; database exists 오류가 나타납니다.

2) show databases;

- 잘 추가 됐다면 결과값 목록에 해당 DB가 있다는걸 확인하실 수 있습니다.

3) grant all privileges on DB이름.* to '계정아이디'@'%';

- DB에 대한 권한을 계정에 추가합니다.

4) flush privileges;

- 부여된 권한을 바로 적용하기 위해 써주는 명령어입니다.

- apache로 치면 service apache2 reload 같은 명령어라고 생각하시면 됩니다.

5) show grants for '계정아이디'@'%';

- 권한을 다시 확인해보면 DB에 대한 권한 내용이 추가 된 것을 확인할 수 있습니다.

6) 모든 작업이 끝났으면 exit; 명령어로 mysql에서 나오시면 됩니다.

 

3. MySQL 설정파일 수정

외부접속을 허용하기 위해서 mysql 설정 파일의 내용을 수정해줘야 합니다.

먼저 아래의 명령어로 파일을 열어줍니다.

굳이 vi(vim)가 아니어도 선호하는 에디터로 열고 수정하시면 됩니다.

# vi /etc/mysql/mysql.conf.d/mysqld.cnf

파일을 열고 조금만 내려가보시다 보면 bind-address = 127.0.0.1을 찾으실 수 있습니다.

물론 이미 이 값을 수정했다면 127.0.0.1이 아닌 다른 값이 입력되어 있을 수 있습니다.

이 부분을 아래와 같이 0.0.0.0으로 수정해 줍니다.

skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 0.0.0.0
#
# * Fine Tuning
#
key_buffer_size         = 16M

수정하셨다면 저장 후 에디터를 나오시면 됩니다.

vi 저장 후 에디터 종료 [ Esc키 > :wq 입력 > Enter키 ]

 

4. MySQL 서비스 재시작

mysql을 재시작하여 방금 수정한 설정을 적용할 수 있도록 합니다.

물론 아래의 명령어가 아닌 systemctl restart mysql 등을 실행하셔도 됩니다.

# service mysql restart

 

 

모든 과정이 끝났습니다.

 

수고하셨습니다.

 

원하던 서버나 프로그램에서 접속이 되는지 확인해보세요.

 

즐프!