[MySQL] 2. MySQL 설치 후 접속 설정

 

 

 

 

 

 

 

지난 포스팅의 마지막 부분에서 필자가 보였던대로, 막 설치가 끝난 MySQL은 설치된 서버에서만 직접 접속이 가능할 뿐, 외부에서의 접속은 불가하다. 또한 MySQL은 인가받지 않은 사용자가 데이터로 접근하는 것을 막기 위해 계정을 생성하고 데이터베이스마다 권한을 부여하는 기능도 제공하고 있다(이는 PostgreSQL이나 MariaDB 등도 마찬가지이긴 하다).

 

이번 포스팅에서는 MySQL의 사용자 계정 관리 및 외부 접근 허용 등 설치 후 세팅에 대한 내용에 대해 작성해보려한다. 

 

*  본 내용을 참고하시는 분들은 아래의 패키지가 설치되어 있는지 필히 확인하자

-  firewalld   : Linux 방화벽 관리 패키지

-  vim           : Linux 텍스트 편집기 패키지 (vi)

   -> 설치가 되어 있지 않다면 아래의 명령어로 firewalld 패키지 설치를 진행하자

sudo apt-get install firewalld vim

 

 

1. 외부 접근 설정: MySQL의 설정 파일 열기 및 bind-address 수정

 

필자가 항상 Linux 프로그램에서 언급한대로, OS에 상관없이 모든 프로그램의 설정 파일은 예외적인 경우가 아니라면 /etc/ 폴더 아래에 존재한다. MySQL도 마찬가지다. 설치가 정상적으로 되었다면 /etc/mysql이라는 폴더가 존재할 것인데, 이 폴더를 탐색해보면 아래와 같이 mysql.conf.d라는 폴더가 하나 보일것이다.

 

 

 

cnf는 config의 약자인데, 저 폴더 외에도 cnf라는 이름을 가진 파일이 몇몇 존재하는 것이 보이나, 엑기스가 아니라 내용 상으로는 지금 단계에서는 볼 만한 것이 없다. 다시 mysql.conf.d 폴더의 내부를 탐색해보자. 

 

설정과 관련된 파일 중 진짜 보스는 mysql.conf.d 폴더 내의 mysqld.cnf다. mysqld.cnf는 mysql 서비스 구동과 관련된 설정정보가 저장되어 있다. DB로 접근하는 IP나 포트 지정부터 시작해서, 프로그램 동작과 관련된 로그 기록 시 최소 레벨을 지정하거나 로그 파일의 경로를 지정하는 등의 설정을 진행할 수 있다.

 

현재 포스팅의 목적은 외부로부터의 접근 설정이므로, 이 부분에 대해 먼저 알아보자. mysqld.cnf 파일을 vi 편집기로 열어, 내용을 살펴보자. 

sudo vi /etc/mysql/mysql.cnf.d/mysqld.cnf

파일을 열면 아래와 같은 내용이 화면에 나타난다. 

 

 

파일의 31번 째 줄을 보면 설치 직후 외부에서의 DB 접근이 왜 불가능했는지 알 수 있는데, bind-address, 즉 DB 서비스가 자신에게 접근하는 IP 주소를 localhost(127.0.0.1)로만 설정했기 때문이다. 현재 필자의 Ubuntu Linux가 외부와 통신하는데 사용하는 IP는 192.30.1.4이므로, 내부에서만 접근 가능한 localhost IP로 bind-address가 설정된 이상 DB 접근은 불가했던 것이다. 이 IP를 실제 필자의 Linux IP로 변경시키려한다. 변경 후, 파일을 저장하고 프롬프트로 나온다.

 

 

설정을 변경했으니, 변경된 설정 내용이 적용되도록 mysql 서비스를 재기동한다. 아래의 명령어로.

sudo systemctl restart mysql

 

정상적으로 변경되었다면, 서비스 status 확인 시, running이 화면에 표시될 것이다. 그게 아니라면 설정 파일을 잘못 수정한 것이니 오타가 존재하는지 다시 한 번 확인해보자. 

 

 

이제 윈도우 CMD 등에서 아래의 명령어를 통해 외부에서 DB 접근이 가능한지 확인해보자.

curl -v telnet://[각자 설정한 Linux IP]:3306

 

지난 포스팅의 마지막 부분과 달리, 아래와 같이  Connected to  문구 뒤에 Time Out이나 Connection Refused가 없다면 외부에서의 접속은 가능한 상태이다. 

 

* Connection Refused:  DB Bind-address와 다른 IP로 접근하여 MySQL에서 접속을 거부함.

* Connection Time out:  Linux 방화벽에 의한 접근 거부.

 

만약, Connection Time Out 문구가 출력되는 분들이라면 Linux의 자체 방화벽에서 접근을 허용하지 않고 있는 것이니, Linux에서 아래의 명령어로 DB 포트(3306)로의 접근을 허용해주도록 하자. 참고로 아래의 명령어를 사용하기 위해서는 Linux에 firewalld 패키지가 설치되어 있어야 한다. 

firewall-cmd --add-port=3306/tcp

 

명령어가 제대로 적용되었다면 success란 문구가 출력되며, 이 이후로 다시 curl 명령어를 실행하면 정상적으로 접속되는 것을 확인할 수 있다.

 

 

 

 

2. MySQL 사용자 계정 생성

 

DB 접속을 위한 포트까지 개방이 완료되었으나, 아직까지 window 등지에서 외부 접속을 시도하면 여전히 DB Shell로 접근이 불가능함으로 확인할 수 있다.

 

이유는 MySQL의 계정과 관련된 설정 때문이다. MySQL은 접근 가능한 계정 정보를 내부에 내장된 테이블에 보유하고 있는데, 이 테이블에는 "A라는 계정은 특정 IP에서만 접근이 가능해!" 라는 설정이 되어 있기 때문이다. 

 

Linux 로 이동하여 MySQL Shell로 들어가보자. 

sudo mysql -u root 

 

MySQL Shell 로 접속이 되었다면 아래의 쿼리를 날려보자. MySQL의 전체 Database 목록을 보여주는 쿼리다.

SHOW DATABASES;

 

정상적으로 쿼리가 실행되었다면 Database 목록이 나타나며, 그 중 mysql이라는 데이터 베이스가 목록에 존재함을 확인할 수 있다.

 

이제 mysql이라는 이름의 데이터베이스 안에 어떤 정보가 저장되어 있는지 확인해보자. 이 데이터베이스 사용을 위해 아래와 같이 쿼리를 입력하자

USE mysql;

 

Database Changed라는 문구가 아래와 같이 출력되면 정상적으로 mysql 데이터베이스로 접근한 상태이다. 

 

 

이제 이 데이터베이스의 Table을 보자. 테이블 중에는 MySQL의 계정 정보를 저장하는 Table인 user 테이블이 존재한다. 아래의 쿼리로 데이터베이스 내 모든 Table 목록을 출력해보자. 

SHOW TABLES;

 

user 테이블은 목록의 가장 아래에 존재한다.

 

 

 

user Table에는 현재 MySQL의 계정 및 접속 설정 정보가 저장되어 있다. 아래의 쿼리로 Table 내 저장된 데이터 정보를 확인해보자.

SELECT host, user, authentication_string FROM user;
/* host: 계정이 접근 가능한 IP 대역
   user: MySQL 계정명
   password: MySQL 접속을 위한 비밀번호*/#### 권한추가

 

정상적으로 쿼리가 동작하면, 목록 중 가장 아래의 2번째 열에, 필자가 접속한 root 계정이 보이는 것을 확인할 수 있다.

 

 

현재 설정을 보면, root 계정은 비밀번호 설정이 되어 있지 않고, localhost로만 접근, 즉 DB 서버 내에서만 접근이 가능하도록 설정되어 있는 상태이다. 보통 Linux 계열의 root 계정은 모든 권한을 가지고 있는 Super User인 경우가 많기 때문에, 테이블에서 localhost, root 열의 정보를 변경하는 것은 권고 사항이 아니다. (그리고... 참고로 필자가 그렇게 변경해봤는데... MySQL 다시 설치하기 싫으면 직접 변경하는 것은 자제하도록 하자...)

 

대신 외부 접속을 위한 새 계정을 만들거나, 굳이 root로 접속하고자 한다면 Table에 user 명을 root로 하는 새 데이터를 넣어주면 된다. 아래와 같이. 

/*
사용자 계정 생성
CREATE USER '[username]'@'[접속 허용 IP 및 대역]' {IDNETIFIED BY '[PASSWORD']}
*/
CREATE USER 'remote_user'@'192.30.1.1'
 
/*
root 계정의 접속 정보를 Table 에 추가
*/
CREATE USER 'root'@'192.30.1.1'

 

쿼리가 정상적으로 입력되면, 아래와 같이 localhost, root 이외에도, 필자가 생성한 remote_user는 물론 root 역시 192.30.1.1를 host 값으로 가지는 행이 생성된다.

 

이 상태에서 MySQL Shell을 빠져나와 서비스를 재기동하고, 생성한 두 계정으로 windows에서 접속을 실행하면, 정상적으로 접속이 이루어짐을 확인할 수 있다.

 

 

 

 

3. MySQL 사용자 비밀번호 설정하기

 

현재까지 진행한 설정은, 외부에서 root와 remote_user 계정에 대해 아무런 인증 절차 없이 192.30.1.1 PC에서 접속이 가능하도록 만든 것이다. 그런데, 아무리 혼자 사용하는 PC라고 하더라도 로그인 계정에 비밀번호를 설정하지 않는다는 것은 보안적으로 큰 문제가 된다. 가령 누가 필자가 자리를 비운 사이 DB에 접속해서 데이터를 변경하거나, 심하면 모든 데이터를 싸그리 날려버리는 것도 가능하기 때문이다. 따라서 일반적인 경우라면 MySQL 계정 생성 시, 비밀번호도 함께 지정한다. 

 

생성된 계정에 대한 비밀번호 지정은 아래와 같이 쿼리를 작성하여 실행하면 된다.

ALTER USER '[username]'@'[접속 Host IP 또는 대역]' IDENTIFIED BY '[password]'

 

쿼리 실행 후, select 문으로 변경 사항을 확인해보자. MySQL 8버전에서는 입력한 암호가 자동으로 암호화되어 Table에 저장된다. 

 

이 상태에서 다시 한 번 서비스를 재기동하고 외부 Windows에서 DB 서버 접속을 시도해보자. 'remote_user'의 경우 비밀번호를 지정하지 않고 접속을 시도하면 접속을 거부당하게 된다.

 

 

 

대신, 접속 시 명령어에 옵션 '-p'를 추가하면, 비밀번호를 입력하는 프롬프트가 출력되는데, 여기에 설정했던 비밀번호를 입력하면 정상적으로 접속이 이루어짐을 확인할 수 있다.

 

 

 

지금까지는 필자가 계정을 생성하고 비밀번호를 변경하는 방식으로 소개했지만, 만약 계정 생성 무렵 비밀번호를 생성하고자 한다면 아래와 같이 쿼리를 사용하면 된다.

CREATE USER '[username]'@'[접속 host IP 또는 대역]' IDENTIFIED BY '[비밀번호]';

 

 

 

 

4. 사용자에게 데이터베이스 권한 부여하기

 

현재 필자가 외부 접속이 가능하도록 만든 모든 계정은, localhost의 root 계정과 달리, 일부 테이블을 볼 수가 없다.

 

이는 필자가 나중에 생성한 'root'@'192.30.1.1' 계정에 대해, Database 및 Table을 조회하거나 변경하는 등의 권한을 부여하지 않아 발생한 일이다. 권한과 관련된 내용은 추후 포스팅에서 더 자세하게 다룰 예정이니, 우선은 MySQL 서버의 모든 Database와 Table에 대해 조회를 할 수 있는 권한을 'root'@'192.30.1.1'에 부여해보려한다. 

 

우선 아래의 쿼리로 현재 부여된 권한부터 보자.

SELECT host, 
       user, 
       authentication_string, 
       Grant_priv, 
       Show_db_priv, 
       Create_priv, 
       Delete_priv, 
       Create_user_priv 
FROM user WHERE user='root';

 

이 쿼리는 username과 접속 가능한 host 외에도, DB조회, 생성, 삭제, 사용자 추가 등의 권한을 보는 쿼리다. 물론, 필자가 조회한 'root'@'192.30.1.4' 계정은 권한 부여를 아무것도 하지 않았기 때문에 모두 'N'으로 표시가 되어 있다. 반면, 기본적으로 생성이 되는 'root'@'localhost'는 모든 권한이 부여되어 있다

 

그럼, 저 권한들만 제대로 부여된다면, localhost의 root와 동일하게 모든 데이터베이스에 대한 조회를 진행하는 것이 가능할 것이다. 우선 세부적인 권한은 여기서 다 못다루니, 전체 권한을 주는 방향으로 진행해보려한다. 

 

전체 권한을 부여하는 쿼리는 아래와 같다.

/* 일부 권한만 부여하는 경우, ALL 대신 다른 값을 사용한다 */
GRANT ALL PRIVILEGES ON [database_name].[table_name] TO '[username]'@'[접속 host IP 또는 대역]';

 

데이터베이스와 테이블도 별도로 지정할 수 있으나, 전체 데이터에 대해 권한을 부여하기 위해, *.*으로 대체한다.

권한을 부여한 뒤, 다시 SELECT 쿼리로 결과를 확인해보자.

 

쿼리 입력 전과 달리, grant_priv를 제외한 모든 priv에 권한이 'Y'로 부여된 것이 확인된다. 이제 다시 MySQL 서비스를 재구동 시킨 뒤, 외부에서 root 계정으로 접속하여 전체 Database가 조회되는지 확인해보자.

 

 

---

 

다음 포스팅에서는 본격적으로 MySQL 쿼리에 대해 포스팅 할 예정이다.

 

 

 

END.