FORTIS FORTUNA ADIUVAT

general log 기능은 DB사용자의 쿼리 실행(사용) 내역을 파일(file)이나 테이블(Table) 기록하여 확인 할 수 있도록 합니다. ISMS-P 심사에서도 쿼리 실행 log를 관리하고 있는지 확인 하는데요. ISMS-P를 준비하시는 분들에게 도움이 되었으면 합니다 ㅎㅎ

general log 기능을 사용하기 위해서는 먼저 root 계정으로 로그인이 필요합니다. (DB는 mysql로 설정합니다)

# mysql -uroot -p mysql
Enter password:

root 계정으로 로그인 후에 general log 기능이 켜져 있는지 확인 합니다.

MariaDB [mysql]> SHOW VARIABLES LIKE '%general%';

쿼리 실행 결과 아래와 같이 조회가 되는데요. 현재는 general log 기능 꺼져 있습니다.

- general_log : OFF (꺼짐) / ON(켜짐)

- general_log_file : general log 파일로 기록 시 로그 기록 위치

+------------------+------------------------------------------+
| Variable_name    | Value                                    |
+------------------+------------------------------------------+
| general_log      | OFF                                      |
| general_log_file | /rdsdbdata/log/general/mysql-general.log |
+------------------+------------------------------------------+

그리고 다음 쿼리를 실행해 log 기록이 어디로 설정이 되어 있는지 확인 합니다.

MariaDB [mysql]> SHOW VARIABLES LIKE '%log_output%';

쿼리 실행 결과 아래와 같이 결과가 나오는데요. 저는 table에 기록되도록 되어있습니다.

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | TABLE |
+---------------+-------+

table에 기록 될때에는 mysql 데이터 베이스의 general_log에 기록됩니다.(mysql.general_log)

현재는 log기능이 꺼져있어 기록이 되지 않지만, 기능을 ON시키고 조회까지 해보도록 하겠습니다.

** log_output 설정 하기전에 먼저 general_log 기능을 먼저 비활성화(OFF)를 하고 진행 해야 합니다.

# general_log 기능을 비활성화
MariaDB [mysql]> set global general_log = OFF;

# general_log가 mysql DB의 general_log TABLE에 log 기록
MariaDB [mysql]> set global log_output = 'TABLE';

# general_log가 /rdsdbdata/log/general/mysql-general.log 위치에 FILE로 기록
MariaDB [mysql]> set global log_output = 'FILE';

# general_log가 TABLE과 FILE에 둘다 기록
MariaDB [mysql]> set global log_output = 'TABLE,FILE';

# general_log 기능을 활성화
MariaDB [mysql]> set global general_log = ON;

위와 같이 설을 통해 general log기능을 활성 화 시킬 수 있습니다. TABLE, FILE, TABLE+FILE(둘다) 중 하나로 로그 기록이 가능합니다.

설정에 따라 장단점이 있지만 보통은 TABLE 기록을 설정 후 crontab 또는 procedure와 스케쥴러를 사용하여 관리하는 것이 편리합니다.(관리하는 방법은 별도로 포스팅 하도록 하겠습니다 ^^)

** crontab 또는 procedure 스케쥴러를 이용하여 관리하는 이유

- FILE : 파일 용량이 너무 커져서 서버 용량(자원)을 너무 많이 사용 할 수 있음. 또, 파일이 너무 커져 파일 열기가 어려워짐(파일을 쪼개어 열 수도 있지만 관리가 어려움).

- TABLE : general_log TABLE에 log 데이터가 너무 많이져 조회(SELECT)시 DB에 부하가 걸리 수 있음.

자!! 이제 general log기능을 설정 했으면 확인 해보도록 하겠습니다! ㅎㅎ

MariaDB [mysql]> SHOW VARIABLES LIKE '%general%';
+------------------+------------------------------------------+
| Variable_name    | Value                                    |
+------------------+------------------------------------------+
| general_log      | ON                                       |
| general_log_file | /rdsdbdata/log/general/mysql-general.log |
+------------------+------------------------------------------+

쿼리를 실행하면 general_log가 ON으로 활성화 되어있는 것을 확인 할 수 있습니다. 자 이제 general_log TABLE에 log가 잘 기록 되고 있는지 확인 해 보도록 하겠습니다.

MariaDB [mysql]> SELECT * FROM general_log LIMIT 100;

위 쿼리를 실행 결과 다음과 같이 조회되면 정상 적으로 general_log 기능이 활성화 되어있다는 것을 확인 할 수 있습니다.

여기까지 general log 설정 및 table에 log 쌓고 조회 설정하기를 알아 봤습니다. 문의 사항있으시면 댓글로 남겨주시면 감사 하겠습니다^^

구성환경
OS : Linux CentOS7
DBMS : MariaDB 10.x

이번 포스팅은 CentOS에서 설치한 MariaDB를 외부에서 접속 하기위해 방화벽 세팅 방법에 대해서 포스팅 하겠습니다.

현재 열려 있는 포트확인

[root@localhost ~]# netstat -tnlp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 0.0.0.0:111             0.0.0.0:*               LISTEN      1097/rpcbind        
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      26130/sshd          
tcp        0      0 127.0.0.1:631           0.0.0.0:*               LISTEN      1521/cupsd          
tcp6       0      0 :::3306                 :::*                    LISTEN      74557/mysqld        
tcp6       0      0 :::111                  :::*                    LISTEN      1097/rpcbind        
tcp6       0      0 :::80                   :::*                    LISTEN      74627/httpd         
tcp6       0      0 :::22                   :::*                    LISTEN      26130/sshd          
tcp6       0      0 ::1:631                 :::*                    LISTEN      1521/cupsd

netstat를 통해 현재 리눅스에서 열려 있는 포트를 확인 합니다. netstat에서 -tnlp옵션을 주었는데요.

다음과 같은 의미를 가지고 있습니다.

• t : TCP 프로토콜을 사용하는 포트
• n : 10진수 숫자로 표기
• l : 열린포트(Listening) 상태인 포트
• p : 상세 정보 표기

만약 netstat가 실행 되지 않는다면, yum install net-tools로 설치 해주면 됩니다.

방화벽에 포트 설정

방화벽 관리를 위해서는 firewall-cmd 명령어를 사용하는데요. 간단하게 방화벽 관련 정보 조회, 추가, 삭제 등 방화벽 관리를 위한 명령어를 정리해 보았습니다. 이제 아래 정리해 놓은 명령어를 통해서 MariaDB(MySQL)포트인 3306포트를 등록 해주도록 하겠습니다.

# 기본 존(zone) 조회
[root@localhost ~]# firewall-cmd --get-default-zone

# 활성화된 존(zone) 조회
[root@localhost ~]# firewall-cmd --get-active-zones

#사용 가능한 모든 방화벽 포트 설정 조회
[root@localhost ~]# firewall-cmd --list-all

#public 존에 속한 사용 가능한 모든 방화벽 포트 설정 조회
[root@localhost ~]# firewall-cmd --zone=public --list-all

# 방화벽 포트 추가( 옵션 추가 )
# --zone=[존이름] : --zone 옵션이 없으면 Default Zone에 저장 다른 존에 저장하려면 옵션 입력
# --permanent : 방화벽 설정 내용 재부팅 후 적용
[root@localhost ~]# firewall-cmd --add-port=[포트번호]/[프로토콜]
또는
[root@localhost ~]# firewall-cmd --add-service=[서비스명]

# 방화벽 포트 삭제
# --zone=[존이름] : --zone 옵션이 없으면 Default Zone에 저장 다른 존에 저장하려면 옵션 입력
# --permanent : 방화벽 설정 내용 재부팅 후 적용
[root@localhost ~]# firewall-cmd --remove-port=[포트번호]/[프로토콜]
또는
[root@localhost ~]# firewall-cmd --remove-service=[서비스명]

# 방화벽 실행 여부 확인
[root@localhost ~]# firewall-cmd --state

# 방화벽 재시작
[root@localhost ~]# firewall-cmd --reload

방화벽에 3306 포트를 열어 주기 위해서 "firewall-cmd --add-port=[포트번호]/[프로토콜]" 또는 "firewall-cmd --add-service=[서비스명]" 사용 하면 되는데요. 저는 위에 설명 한것과 같이 "--zone"과 "--permanent" 옵션을 사용하여 포트를 추가 해 주겠습니다.

[root@localhost ~]# firewall-cmd --zone=public --permanent --add-port=3306/tcp
또는
[root@localhost ~]# firewall-cmd --zone=public --permanent --add-service=mysql
[root@localhost ~]# firewall-cmd --reload

"--permanent"옵셥을 주었기 때문에 "firewall-cmd --reload"를 사용하여 방화벽을 재시작 해주어야 설정한 내용이 적용 됩니다. "--permanent"사용하지 않는다면 방화벽 재시작 없이 바로 설정내용이 적용 됩니다.

"--zone"옵션을 사용하여 존(zone)은 public으로 설정 됩니다. "--zone"을 사용하지 않는다면 Default값으로 지정된 존(zone)으로 저장됩니다. Default Zone 확인은 "firewall-cmd --get-default-zone" 확인 할 수 있습니다.

이제 설정 내용이 잘 적용 되었는지 확인 해 보겠습니다. 확인 하기 위해서는 "firewall-cmd --list-all" 명령어를 통해 확인 가능합니다. 특정 존(zone)의 설정 내용을 확인 하기 위해서는 "firewall-cmd --zone=public --list-all"와 같이 "--zone=[존이름]"옵션을 사용해 줍니다.

[root@localhost ~]# firewall-cmd --list-all
또는
[root@localhost ~]# firewall-cmd --zone=public --list-all

방화벽에 포트를 추가 했을때 위와 같이 "--add-port=3306/tcp"와 "--add-service=mysql" 결과 값이 조금은 다를 수 있습니다. service로 추가 할 것인지 이니면 포트로 직접 추가 할 것 인지 인데요. 이 부분은 상황에 따라 편하신 방법으로 하시면 됩니다. 이제 windows 환경에서 mariaDB에 접속 해 보도록 하겠습니다. 저는 DB 관리 툴 인 HeidiSQL을 사용하였는데요. 꼭 HeidiSQL이 아니더라도 DBeaver 등 다양한 무료 툴이 있으니 편하신 툴로 사용 하시면 됩니다.

DB 접속을 위해 호스트명/IP, 사용자, 암호, 포트 정보를 입력 해 줍니다. 그리고 열기를 클릭 해 줍니다.

MariaDB Server에 접속 성공 하게 된다면 위와 같은 결과를 볼 수 있습니다. 하지만 포트가 열려 있지 않거나 잘 못 열려 있다면 접속이 되지 않고 아래와 같은 결과가 나오는데요.

이럴 경우에는 위에 정리된 명령어로 포트 및 존(Zone)을 확인 하시고 다시 추가 및 방화벽 재시작을 해보시면 될 꺼 같습니다. 이상 포스팅을 마치겠습니다. 긴글 읽어 주셔서 감사합니다^^

구성환경
OS : Linux CentOS7
DBMS : MariaDB 10.x

1. yum update

MariaDB를 설치하기 전에 먼저 update를 해줍시다. yum을 update하기 위해서는 관리자(root)로 로그인 하던가 아니면 sudo를 사용하여 아래와 같이 update를 해줍니다.

#관리자(root) 일 때
[root@localhost ~]# yum update

#일반 계정 일 때
[lucky@localhost ~]$ sudo yum update

"yum update"를 하면 아래와 같이 update 목록과 설치 진행 하겠냐고 물어 보는데요. "Y"를 입력하고 Enter를 눌러 주시면 됩니다.

2. mariaDB 설치

yum update가 완료 되면 다음으로 MariaDB를 설치를 시작 하면 되는데요. 아래와 같이 입력해 주세요.

#관리자(root) 일 때
[root@localhost ~]# yum -y install mariadb-server

#일반 계정 일 때
[lucky@localhost ~]$ sudo yum -y install mariadb-server

잠시 기다리면 아래와 같이 "Complete"가 나오면 설치가 완료!!

자 이제 설치가 다 되었으니 한번 잘 설치되었는지 확인 해볼까요? 아래 명령어를 입력 후 아래와 같은 결과가 나온다면 잘 설치가 됬다고 보시면 됩니다.

[root@localhost ~]# rpm -qa | grep -i mariadb

3. mariaDB 실행

mariaDB를 설치 했으시 이제 실행 해 봐요. 간단히 실행, 상태확인, 정지, 재시작 명령어를 정리 해봤습니다.

# mariaDB 실행
[root@localhost ~]# systemctl start mariadb

# mariaDB 상태 확인
[root@localhost ~]# systemctl status mariadb

# mariaDB 재시작
[root@localhost ~]# systemctl restart mariadb

# mariaDB 정지
[root@localhost ~]# systemctl stop mariadb

mariaDB를 시작하고, 잘 시작됬는지 확인하려면 "systemctl status mariadb"또는 "ps -ef | grep mysqld"를 통해 잘 실행 됬는지 확인 가능합니다.

4. mariaDB 계정 생성 및 권한 부여

mariaDB가 정상 적으로 잘 실행 됬으니 이제 계정 생성 및 권한 부여를 해보겠습니다. 계정 생성하기 위해서는 mariaDB의 root 계정으로 로그인 해야 되는데요. mariaDB를 처음 설치 했을때 root 계정의 패스워드가 없으니 먼저 root계정의 패스워드 부터 초기화 하도록 하겠습니다.

# mariaDB root 패스워드 변경하기
[root@localhost ~]# mysqladmin -uroot password

mariaDB의 root 계정의 패스워드를 입력해주고, root계정으로 mariaDB를 접속해을 해주세요.

# mariaDB root계정 접속
# mariaDB -u[계정명] -p [사용 할 스키마(DB명)]
[root@localhost ~]# mariaDB -uroot -p
Enter password: 패스워드 입력

초기화 시킨 패스워드를 사용하여, root계정으로 mariaDB에 접속 성공되면 위와같이 결과가 나오게 됩니다. mariaDB를 접속하기 위해서 "mariaDB -uroot -p"접속해도 되지만 "mysql -uroot -p"로 입력해도 마찬가지로 접속이 가능합니다. 간단히 이유를 설명하면 mariaDB는 MySQL로부터 뿌리가 시작 되었는데요. mariaDB와 MySQL은 기능상 약간의 차이점이 있을 수 있지만 대부분 명령어 등 대부분 호환성을 가지고 있습니다. 덕분에 mariaDB를 사용할때 mysql 명령어를 사용해도 대부분 실행 되고 있습니다.

자! 이제 계정을 생성하고 권한을 부여 해보도록 하겠습니다.

위와 같이 순서대로 진행 하면 계정 생성 및 권한을 부여 할 수 있습니다.

# 스키마 선택(DB 선택)
MariaDB [mysql]> use mysql;

# 계정 생성
# CREATE USER '[계정명]'@'[HOST주소(IP주소)]' IDENTIFIED BY '패스워드';
MariaDB [mysql]> CREATE USER 'lucky'@'localhost' IDENTIFIED BY 'qwer1234!';

# 권한 부여
# GRANT [권한] PRIVILEGES ON '[DB명]'.'[테이블명]' TO '[계정명]'@'[HOST주소(IP주소)]';
MariaDB [mysql]> GRANT ALL PRIVILEGES ON 'mysql'.* TO 'lucky'@'localhost';

# 추가 및 수정 사항 적용
MariaDB [mysql]> FLUSH PRIVILEGES;

"CREATE USER '[계정명]'@'[HOST주소(IP주소)]' IDENTIFIED BY '패스워드'"에서 "HOST주소(IP주소)"는 DB서버로 접속 하려는 사용자의 IP주소를 말합니다.

예를 들어 회사 컴퓨터(1.1.1.1)과 개인 컴퓨터(2.2.2.2)가 있다고 가정 합니다. CREATE USER 'lucky'@'1.1.1.1'로 계정을 생성 합니다. 그러면 회사 컴퓨터(1.1.1.1)에서는 lucky계정으로 DB에 접속이 되지만, 개인 컴퓨터(2.2.2.2)에서는 lucky계정을 사용하여 접근이 불가능 합니다. 즉, 회사 컴퓨터(1.1.1.1) 외 다른 컴퓨터에서는 DB서버 접속이 불가능 합니다.

이제 계정과 권한이 잘 생성 되었는지 확인 해보겠습니다. 아래 쿼리를 사용하여 계정 및 권한 정보를 확인 해 보겠습니다.

# 계정 정보 확인
SELECT host, user, select_priv, insert_priv, update_priv, delete_priv from user;

# 권한 정보 확인
SELECT host, db, user, select_priv, insert_priv, update_priv, delete_priv from db;

MariaDB 설치 및 계정 생성에 대해서 포스팅 해봤습니다. 긴글 읽어주셔서 갑사합니다!! ㅎㅎ