데이터베이스 관리를 해야겠다고 마음먹은 이유
팀 개발 문화 발전시키기 (1) 을 하고 나서 결국에는 어플리케이션 단계에서도 인증과 접근 제어를 해야 할 필요성을 느꼈다. 특히나 데이터베이스는 개발 서버에서 가장 자주 사용하고 중요한 정보들이 담겨 있는 만큼 최우선적으로 사용자 관리를 시작해야겠다고 생각했다.
개선한 방향
MySQL Workbench 사용해보기
그래서 MySQL Workbench를 설치하여 사용해보기로 했다. 지금까지는 shell에서 쿼리를 직접 수행하는 방식으로 작업해왔다. 그러나 MySQL 서버 전체의 운영 현황을 cli로 확인하는 것은 무리라고 생각하여 대시보드 형식으로 한 눈에 모든 정보를 확인할 수 있도록 전용 툴을 이용하고자 한 것이다.
macos에서 brew를 이용하여 설치한 방법은 다음과 같다.
$ brew install --cask mysqlworkbench설치하고 실행한 뒤 root 계정을 이용하여 새로운 connection을 생성하였다. 알고 있었던 문제점들을 포함하여 문제점들을 몇 가지 파악하였다.
-
사용하지 않는 database schema가 너무 많았다. 실사용은 2개만 하고 있는데, 사용하지 않는 것은 약 50개 정도 되었다. 입사하기 전부터 사용되어 왔고, 프로젝트가 끝났을 때 정리하는 절차가 없었기 때문이다.
-
root 계정을 제외하고 사용자를 단 하나(!)만 사용하고 있었다. shell에 접속할 때, application에서 사용할 때 등 모든 경우에서 말이다.
-
database schema들에 대해 comment가 없어서 각각이 무슨 목적으로 만들어진 것인지 알 수 없었다.
MySQL 백업
우선 사용하지 않는 스키마들을 정리하는 것부터 시작하였다. 그러나 위에서 말했듯이 어떤 목적으로 만들어진 것인지도 모르는 스키마들도 있기 때문에 쉽게 마음대로 완전 삭제할 수는 없었다. 따라서 스키마를 삭제하기 전 백업해둔 후, 삭제하는 것으로 작업을 정했다.
백업은 mysqldump를 이용하여 다음과 같이 진행하였다.
$ mysqldump -d root -p SCHEMA_NAME > SCHEMA_NAME_221101.sql그러나 스키마가 수십 개나 되는 상황이었기 때문에 반복적으로 실행하기에는 비효율적이었다. 다음 stackoverflow 답변을 참고하여 bash script로 만들어 백업을 수행하였다.
https://stackoverflow.com/questions/10867520/mysqldump-with-db-in-a-separate-file
backup_db.sh
#!/bin/bash
BACKUP_DIR=/var/backups/mysql
BACKUP_FILENAME=$1_$(date '+%y%m%d').sql
BACKUP_FILEPATH=$BACKUP_DIR/$BACKUP_FILENAME
sudo mysqldump -u root -p $1 > $BACKUP_FILEPATH
sudo chown root:root $BACKUP_FILEPATHbackup_dbs.sh
#!/bin/bash
dbnames=$(mysql -u root -p -N -e 'show databases')
skiplist=("information_schema" "mysql" "performance_schema" "sys")
for dbname in $dbnames
do
if [[ " ${skiplist[*]} " =~ " $dbname " ]]; then
continue
fi
sudo ./backup_db.sh $dbname
done;실제로 위 스크립트를 실행할 때는 mysql 및 mysqldump 의 옵션으로 -ppassword 를 입력하여 매번 비밀번호를 입력하지 않도록 했다.
백업을 완료한 후 비슷한 방법으로 drop database SCHEMA_NAME 명령을 반복 실행하여 사용하지 않는 스키마 전부를 삭제하였다.
MySQL 계정 생성
MySQL에서 계정을 생성하고 관리할 때 기본적으로는 다음 명령어들을 이용한다.
mysql> create user 'root'@'%' identified by 'password';
mysql> grant all privileges on *.* to 'root'@'%';
mysql> flush privileges;그러나 각각을 살펴보면 섬세하게 조정할 수 있는 부분들이 여럿 있다.
host
host는 @ 뒤에 '%'와 같이 지정한 부분이다. %는 어느 ip에서나 접속 가능함을 의미한다. 실제 사원들이 사용하는 계정의 경우 어디서나 접근해야 할 수도 있기 때문에 %로 지정하였다. 그러나 특정 서비스에서 클라이언트를 통해 접근하려는 경우 해당 프로그램이 실행되는 위치의 ip 주소를 지정해두면 보안상 많은 이점을 얻을 것이다. 앞으로는 새로운 프로토타입 개발을 시작할 때마다 그 서비스가 실행되는 ip에서만 접근 가능한 계정을 만들어 관리하려고 한다.
grant all privileges
MySQL을 사용하면서 무의식적으로 all privileges 권한을 부여하곤 한다. 그러나 어떤 권한들이 있고, all privileges 가 어떤 권한들을 포함하는지 다시 한 번 확인하는 것이 좋을 것 같아 조사하였다.
MySQL 서버에서 제공하는 권한 목록을 확인할 수 있는 MySQL shell 명령어와 그 출력 결과는 다음과 같다.
mysql> show privileges;
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Privilege | Context | Comment |
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Alter | Tables | To alter the table |
| Alter routine | Functions,Procedures | To alter or drop stored functions/procedures |
| Create | Databases,Tables,Indexes | To create new databases and tables |
| Create routine | Databases | To use CREATE FUNCTION/PROCEDURE |
| Create temporary tables | Databases | To use CREATE TEMPORARY TABLE |
| Create view | Tables | To create new views |
| Create user | Server Admin | To create new users |
| Delete | Tables | To delete existing rows |
| Drop | Databases,Tables | To drop databases, tables, and views |
| Event | Server Admin | To create, alter, drop and execute events |
| Execute | Functions,Procedures | To execute stored routines |
| File | File access on server | To read and write files on the server |
| Grant option | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess |
| Index | Tables | To create or drop indexes |
| Insert | Tables | To insert data into tables |
| Lock tables | Databases | To use LOCK TABLES (together with SELECT privilege) |
| Process | Server Admin | To view the plain text of currently executing queries |
| Proxy | Server Admin | To make proxy user possible |
| References | Databases,Tables | To have references on tables |
| Reload | Server Admin | To reload or refresh tables, logs and privileges |
| Replication client | Server Admin | To ask where the slave or master servers are |
| Replication slave | Server Admin | To read binary log events from the master |
| Select | Tables | To retrieve rows from table |
| Show databases | Server Admin | To see all databases with SHOW DATABASES |
| Show view | Tables | To see views with SHOW CREATE VIEW |
| Shutdown | Server Admin | To shut down the server |
| Super | Server Admin | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. |
| Trigger | Tables | To use triggers |
| Create tablespace | Server Admin | To create/alter/drop tablespaces |
| Update | Tables | To update existing rows |
| Usage | Server Admin | No privileges - allow connect only |
+-------------------------+---------------------------------------+-------------------------------------------------------+
31 rows in set (0.00 sec)여기서는 all privileges 를 통해 부여되는 권한은 출력되지 않아 all 권한은 MySQL 5.7 공식 문서의 Table 11.8을 보고 확인할 수 있었다.
즉, 31개의 권한 중 29개의 권한이나 부여되는 막강한 권한이라고 할 수 있다. 서비스 클라이언트 계정에는 불필요한 권한도 많이 포함되어 있기 때문에 섬세히 조정할 필요가 있을 것이다. 그러나 팀원들에게 줄 계정은 다양한 테스트와 자유로운 사용이 가능해야 하므로 일단 all privileges 를 적용하였다.
on *.*
all privileges 를 무의식적으로 하는 것과 마찬가지로 *.* 도 무의식적으로 지정할 때가 많았다. 앞의 * 이 의미하는 것은 데이터베이스 스키마, 뒤의 * 이 의미하는 것은 데이터베이스 테이블이다. 특정 스키마의 특정 테이블에 대해 서로 다른 권한을 부여할 수 있는 것이다.
서비스 클라이언트 계정은 해당 서비스가 이용하는 스키마 외에 다른 스키마에 접근할 필요가 없기 때문에 앞의 * 을 해당 서비스의 스키마로 지정해 두면 좋을 것이다. 또한, 인증 서버의 클라이언트는 user 테이블만, 거래 서버의 클라이언트는 trade 테이블만 접근할 수 있게 하는 등 여러 규칙을 적용할 수 있는 가능성은 많아 보인다.
어떤 스키마 또는 테이블에 어떤 권한이 부여되어 있는지 확인하기 위해 간단히 사용할 수 있는 MySQL shell 명령어와 그 출력 결과는 다음과 같다.
mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)show grants for 'user'@'%' 와 같이 입력하여 특정 유저에 부여된 권한도 확인할 수 있었다.
MySQL ROLE
MySQL 8.0부터는 ROLE 이라는 개념이 추가되었다. 공식 문서 링크
일종의 가상 사용자로써 특정 권한들을 보유하고 있을 수 있고 특정 사용자에게 그 규칙을 전수해줄 수 있다. 사원들에 대해 모두 동일한 규칙을 부여하고 있는 상황이기 때문에 ROLE 을 사용하는 것도 좋은 방법이겠지만, 개발 서버는 아직 MySQL 5.7 서비스를 구동하고 있어서 아쉽게도 적용하지 못했다.
스키마 사용 목적 comment
MySQL은 테이블, 컬럼 단위로는 comment를 작성할 수 있지만, 스키마 단위로는 comment를 작성할 수 없다.
따라서 최선의 방법은 이름을 명확하게 지어 어떤 서비스에서 사용했는지 알아볼 수 있게 하는 것이다.
그러나 그것도 해당 서비스를 개발했던 사람이 팀에 남아있을 때의 이야기이고, 지금처럼 그 서비스를 알지 못하는 사람들이 스키마를 관리하여야 할 때는 문제가 된다.
따라서 개발 서버 전체의 현황판을 만들어 MySQL을 비롯한 각 스택들의 운영을 기록해야겠다는 motivation으로 이어졌다. 현재 구현중이다.
회고
데이터베이스는 stateful한 스택으로 다른 어떤 프로그램들보다도 운영에 대해 철저해야 하는 것 같다. 백업을 실행한 것 자체만이 아니라 그 과정을 스크립트로 만들어 쉽게 자주 실행할 수 있게 된 것이 큰 자산으로 남았다. 또한, 사용자 계정을 만들어 관리함으로써 문제가 생겼을 때 원인을 더 쉽게 판단할 수 있도록 한 것이 프로그램을 우리의 통제 아래로 들어가게 한 것 같아 만족스럽다.
