MySQL 서버 설정과 사용자 및 권한

MySQL 서버 설정

일반적으로 MySQL 서버는 단 하나의 설정 파일을 사용하는데, 리눅스를 포함한 유닉스 계열에서는 my.cnf라는 이름을 사용하고, 윈도우 계열에서는 my.ini라는 이름을 사용한다. MySQL 8.0 서버의 시스템 변수는 대략 570개 수준이며, 사용하는 플러그인이나 컴포넌트에 따라 시스템 변수의 개수는 더 늘어날 수도 있다. 모든 시스템 변수를 공부해야 하는 것은 아니지만 MySQL 서버를 제대로 사용하려면 시스템 변수에 대한 이해가 상당히 많이 필요하다.

 

설정 파일

MySQL 서버는 시작될 때만 이 설정 파일을 참조하는데, 이 설정 파일의 경로가 고정되어 있는 것은 아니다. MySQL 서버는 지정된 여러 개의 디렉터리를 순차적으로 탐색하면서 처음 발견된 my.cnf 파일을 사용하게 된다.

위 캡쳐한 사진을 통해 알 수 있드시 mysql --help 명령어를 통해 살펴보면 MySQL 서버가 어떤 my.cnf 파일을 먼저 읽는지 다음과 같은 우선순위를 가진다.

1. /etc/my.cnf 파일
2. /etc/mysql/my.cnf 파일
3. /opt/homebrew/etc/my.cnf 파일
4. ~/ .my.cnf 파일

 

시스템 변수 특징

MySQL 서버는 기동하면서 설정 파일의 내용을 읽어 메모리나 작동 방식을 초기화하고, 접속된 사용자를 제어하기 위해 이러한 값을 별도로 저장하는데 이를 시스템 변수(System Variables)라고 한다. 각 시스템 변수는 show variables; show global variables; 명령으로 실행하면 위 결과를 얻을 수 있다.

시스템 변수(설정) 값이 어떻게 MySQL 서버와 클라이언트에 영향을 미치는지 판단하려면 각 변수가 글로벌 변수인지 세션 변수인지 구분할 필요가 있다. 그리고 이를 위해서는 우선 글로벌 변수와 세션 변수가 무엇이고 서로 어떤 관계가 있는지 명확하게 이해해야 한다. 

https://dev.mysql.com/doc/refman/8.0/en/server-system-variable-reference.html 페이지를 보면 MySQL 서버에서 제공하는 모든 시스템 변수의 목록과 간단한 설명을 참고할 수 있다.

 

설명 페이지에 있는 각 변수 항목은 다음 표와 같은 형식으로 되어 있으며 시스템 변수가 가지는 5가지 속성의 의미는 다음과 같다.

• Cmd-Line: MySQL 서버의 명령행 인자로 설정될 수 있는지 여부를 나타낸다. 즉, 이 값이 'Yes'이면 명령행 인자로 이 시스템 변수의 값을 변경하는 것이 가능하다는 의미다.
• Option File: MySQL의 설정 파일인 my.cnf(또는 my.ini)로 제어할 수 있는지 여부를 나타낸다. 옵션 파일이나 설정 파일 또는 컨피규레이션 파일 등은 전부 my.cnf(또는 my.ini) 파일을 지칭하는 것으로 같은 의미로 사용된다.
• System Var: 시스템 변수인지 아닌지를 나타낸다. MySQL 서버의 설정 파일을 작성할 때 각 변수명에 사용된 하이픈('-')이나 언더스코어('_')의 구분에 주의해야 한다. 이는 MySQL 서버가 예전부터 수많은 사람들의 손을 거쳐오면서 생긴 일관성 없는 변수의 명명 규칙 때문이다. 어떤 변수는 하이픈으로 구분되고 어떤 시스템 변수는 언더스코어로 구분되는 등 상당히 애매모호한 부분이 있는데, 뒤늦게 이런 부분을 언더스코어로 통일해가는 중이다. 현재 MySQL 8.0에서는 모든 시스템 변수들이 '_'를 구분자로 사용하도록 변경된 것으로 보인다. 그리고 명령행 옵션으로만 사용 가능한 설정들은 '_'가 아니라 '-'을 구분자로 사용한다.
• Var Scope: 시스템 변수의 적용 범위를 나타낸다. 이 시스템 변수가 영향을 미치는 곳이 MySQL 서버 전체(Global, 글로벌 또는 전역)를 대상으로 하는지, 아니면 MySQL 서버와 클라이언트 간의 커넥션(Session, 세션 또는 커넥션)만인지 구분한다. 그리고 어떤 변수는 세션과 글로벌 범위에 모두 적용(Both)되기도 한다.
• Dynamic: 시스템 변수가 동적인지 정적인지 구분하는 변수이다.

 

글로별 변수와 세션 변수

MySQL의 시스템 변수는 적용 범위에 따라 글로벌 변수와 세션 변수로 나뉘는데, 일반적으로 세션별로 적용되는 시스템 변수의 경우 글로벌 변수뿐만 아니라 세션 변수에도 동시에 존재한다.

글로벌 범위의 시스템 변수는 하나의 MySQL 서버 인스턴스에서 전체적으로 영향을 미치는 시스템 변수를 의미하며, 주로 MySQL 서버 자체에 관련된 설정일 때가 많다. MySQL 서버에서 단 하나만 존재하는 InnoDB 버퍼 풀 크기(innodb_buffer_pool_size) 또는 MyISAM의 키 캐시 크기(key_buffer_size) 등이 가장 대표적인 글로벌의 시스템 변수이다.

세션 범위의 시스템 변수는 MySQL 클라이언트가 MySQL 서버에 접속할 떄 기본으로 부여하는 옵션의 기본값을 제어하는 데 사용된다. 다른 DBMS에서도 거의 비슷하겠지만 MySQL에서도 각 클라이언트가 처음에 접속하면 기본적으로 부여하는 기본값을 가지고 있다. 별도로 그 값을 변경하지 않은 경우에는 그대로 값이 유지되지만, 클라이언트의 필요에 따라 개별 커넥션 단위로 다른 값으로 변경할 수 있는 것이 세션 변수이다. 여기서 기본값은 글로벌 세스템 변수이며, 각 클라이언트가 가지는 값이 세션 시스템 변수이다. 각 클라이언트에서 쿼리 단위로 자동 커밋을 수행할지 여부를 결정하는 autocommit 변수가 대표적인 예라고 볼 수 있다. autocommit을 ON으로 설정해 두면 해당 서버에 접속하는 모든 커넥션은 기본으로 자동 커밋 모드로 시작되지만 각 커넥션에서 autocommit 변수의 값을 OFF로 변경해 자동 커밋 모드를 비활성화할 수도 있다. 이러한 세션 변수는 커넥션별로 설정값을 서로 다르게 지정할 수 있으며, 한 번 연결된 커넥션의 세션 변수는 서버에서 강제로 변경할 수 없다. 순수하게 범위가 세션(Session)이라고 명시된 시스템 변수는 MySQL 서버의 설정 파일에 초깃값을 명시할 수 없으며, 커넥션이 만들어지는 순간부터 해당 커넥션에서만 유효한 설정 변수를 의미한다.

 

정적 변수와 동적 변수

MySQL 서버의 시스템 변수는 MySQL 서버가 기동 중인 상태에서 변경 가능한지에 따라 동적 변수와 정적 변수로 구분된다. 이는 MySQL 서버의 시스템 변수 중 디스크에 저장돼 있는 설정 파일(my.cnf 또는 my.ini)을 변경하는 경우와 이미 기동 중인 MySQL 서버의 메모리에 있는 MySQL 서버의 시스템 변수를 변경하는 경우로 구분할 수 있다.

 

디스크에 저장된 설정 파일의 내용은 변경하더라도 MySQL 서버가 재시작하기 전에는 적용되지 않지만 SHOW 명령으로 MySQL 서버에 적용된 변숫값을 확인하거나 SET 명령을 이용해 값을 바꿀 수도 있다. 또한, SET 명령을 통해 변경되는 시스템 변숫값이 MySQL의 설정 파일인 my.cnf(또는 my.ini) 파일에 반영되는 것은 아니기 때문에 현재 기동 중인 MySQL의 인스턴스에서만 유효하다. 만약에 설정을 영구히 적용하려면 my.cnf 파일도 반드시 변경해야 한다.

 

시스템 변수의 변위가 'Both'인 경우(글로벌이면서 세션 변수인)에는 글로벌 시스템 변수의 값을 변경해도 이미 존재하는 커넥션의 세션 변수값은 변경되지 않고 그대로 유지된다. 동적으로 변경 가능한 join_buffer_size라는 Both 타입 변수로 확인하면 글로벌 변수값은 524288으로 변경됐지만, 현재 커넥션의 세션 변수는 예전의 값인 262144를 그대로 유지하고 있음을 확인할 수 있다.

 

당연히 커넥션을 종료하고 다시 열면 세션 변수의 값도 바뀌는 것을 확인할 수 있다. MySQL의 시스템 변수 가운데 동적인 변수만 이렇게 SET 명령을 이용해 변경하는 것이 가능하다. SET 명령으로 새로운 값을 설정할 때는 설정 파일에서처럼 MB나 GB와 같은 단위 표기법을 사용할 수 없지만 2*1024*1024와 같은 수식은 사용할 수 있다.

 

SET PERSIST

위에서 SET 명령어를 통해서 시스템 변수의 값을 변경하는 것은 현재 기동중인 MySQL 서버 인스턴스에만 유효하다고 했다. 따라서 시간이 지나 MySQL 서버를 재시작하여 원래의 시스템 변수의 값으로 돌아가 장애가 발생할 수도 있게 된다. 이를 위해 MySQL 8.0 버전에서는 SET PERSIST 명령을 도입하여 해결하였다.

SET PERSIST 명령으로 시스템 변수를 변경하면 MySQL 서버는 변경된 값을 즉시 적용함과 동시에 별도의 설정 파일(mysqld-auto.cnf)에 변경 내용을 추가로 기록해 둔다. 그리고 MySQL 서버가 재시작할 때 기본 설정 파일(my.cnf)뿐만 아니라 자동 생성된 mysqld-auto.cnf 파일을 같이 참조해서 시스템 변수를 적용한다. SET PERSIST 명령은 세션 변수에는 적용되지 않으며, SET PERSIST 명령으로 시스템 변수를 변경하면 MySQL 서버는 자동으로 GLOBAL 시스템 변수의 변경으로 인식하고 변경한다.

현재 실행 중인 MySQL 서버에는 변경 내용을 적용하지 않고 mysqld-auto.cnf 파일에만 변경 내용을 기록해두고자 한다면 SET PERSIST_ONLY 명령을 사용하면 된다. 또한 정적 변수는 실행 중에는 변경이 불가하기 때문에 SET PERSIST_ONLY 명령을 통해 다음 재시작을 위해 mysqld-auto.cnf 파일에만 기록해두는 용도로도 사용할 수 있다.

 

 

사용자 및 권한

MySQL에서 사용자 계정은 단순히 사용자의 아이디뿐 아니라 해당 사용자가 어느 IP에서 접속하고 있는지도 확인한다. 또한 MySQL 8.0 버전부터는 권한을 묶어서 관리하는 역할(Role, 롤)의 개념이 도입됐기 때문에 각 사용자의 권한으로 미리 준비된 권한 세트(Role)를 부여하는 것도 가능하다. 데이터베이스 서버의 보안은 갈수록 중요해지고 있으므로 반드시 계정의 식별 방식과 권한, 역할에 대한 기본적인 내용은 꼭 숙지해야 할 필요가 있다.

 

계정 생성

MySQL 8.0 버전부터는 계정의 생성은 CREATE USER 명령으로, 권한 부여는 GRANT 명령으로 구분해서 실행한다. 계정을 생성할 때는 다음과 같은 다양한 옵션을 설정할 수 있다.

• 계정의 인증 방식과 비밀번호
• 비밀번호 관련 옵션(비밀번호 유효 기간, 비밀번호 이력 개수, 비밀번호 재사용 불가 기간)
• 기본 역할(Role)
• SSL 옵션
• 계정 잠금 여부

일반적으로 많이 사용되는 옵션을 가진 CREATE USER 명령은 다음과 같다.

CREATE USER 'user'@'%'
    IDENTIFIED WITH 'mysql_native_password' BY 'password'
    REQUIRE NONE
    PASSWORD EXPIRE INTERVAL 30 DAY
    ACCOUNT UNLOCK
    PASSWORD HISTORY DEFAULT
    PASSWORD REUSE INTERVAL DEFAULT
    PASSWORD REQUIRE CURRENT DEFAULT;

• IDENTIFIED WITH: 사용자의 인증 방식과 비밀번호를 설정한다. IDENTIFIED WITH 뒤에는 반드시 인증 방식(이증 플러그인의 이름)을 명시해야 하는데, MySQL 서버의 기본 인증 방식을 사용하고자 한다면 IDENTIFIED BY 'password' 형식으로 명시해야 한다. MySQL 서버에는 다양한 인증 방식을 플러그인 형태로 제공하며, Native Pluggablee Authentication, Caching SHA-2 Pluggable Authentication(기본 인증 방식), PAM Pluggable Authentication, LDAP Pluggable Authentication 4가지 방식이 가장 대표적이다.
• REQUIRE: MySQL 서버에 접속할 때 암호화된 SSL/TLS 채널을 사용할지 여부를 설정한다. 만약 별도로 설정하지 않으면 비암호화 채널로 연결하게 된다. 하지만 REQUIRE 옵션을 SSL로 설정하지 않더라도 Caching SHA-2 Authentication 인증 방식을 사용하면 암호화된 채널만으로 MySQL 서버에 접속할 수 있게 된다.
• PASSWORD EXPIRE: 비밀번호의 유효 기간을 설정하는 옵션이다. 별도로 명시하지 않으면 default_password_lifetime 시스템 변수에 저장된 기간으로 유효 기간이 설정된다.
  • PASSWORD EXPIRE: 계정 생성과 동시에 비밀번호의 만료 처리
  • PASSWORD EXPIRE NEVER: 계정 비밀번호의 만료 기간 없음
  • PASSWORD EXPIRE DEFAULT: default_password_lifetime 시스템 변수에 저장된 기간으로 비밀번호의 유효 기간을 설정
  • PASSWORD EXPIRE INTERVAL n DAY: 비밀번호의 유효 기간을 오늘부터 n일자로 설정
• PASSWORD HISTORY: 한 번 사용했던 비밀번호를 재사용하지 못하게 설정하는 옵션이다.
  • PASSWORD HISTROY DEFAULT: password_history 시스템 변수에 저장된 개수만큼 비밀번호의 이력을 저장하며, 저장된 이력에 남아있는 비밀번호는 재사용할 수 없다.
  • PASSWORD HISTORY n: 비밀번호의 이력을 최근 n개까지만 저장하며, 저장된 이력에 남아있는 비밀번호는 재사용할 수 없다.
• PASSWORD REUSE INTERVAL: 한 번 사용했던 비밀번호의 재사용 금지 기간을 설정하는 옵션이다. 별도로 명시하지 않으면 password_reuse_interval 시스템 변수에 저장된 기간으로 설정된다.
  • PASSWORD REUSE INTERVAL DEFAULT: password_reuse_interval 변수에 저장된 기간으로 설정
  • PASSWORD REUSE INTERVAL n DAY: n일자 이후에 비밀번호를 재사용할 수 있게 설정
  • PASSWORD REQUIRE: 비밀번호가 만료되어 새로운 비밀번호로 변경할 때 현재 비밀번호(변경하기 전 만료된 비밀번호)를 필요로 할지 말지를 결정하는 옵션이다. 별도로 명시하지 않으면 password_require_current 시스템 변수의 값으로 설정된다.
    • PASSWORD REQUIRE CURRENT: 비밀번호를 변경할 때 현재 비밀번호를 먼저 입력하도록 설정
    • PASSWORD REQUIRE OPTIONAL: 비밀번호를 변경할 때 현재 비밀번호를 입력하지 않아도 되도록 설정
    • PASSWORD REQUIRE DEFAULT: password_require_current 시스템 변수의 값으로 설정
• ACCOUNT LOCK / UNLOCK: 계정 생성 시 또는 ALTER USER 명령어를 사용해 계정 정보를 변경할 떄 계정을 사용하지 못하게 잠글지 여부를 결정한다.
  • ACCOUNT LOCK: 계정을 사용하지 못하게 잠금
  • ACCOUNT UNLOCK: 잠긴 계정을 다시 사용 가능 상태로 잠금 해제

 

권한(Privilege)

데이터베이스나 테이블 이외의 객체에 적용되는 권한을 글로벌 권한이라고 하며, 데이터베이스나 테이블을 제어하는 데 필요한 권한을 객체 권한이라고 한다. 객체 권한은 GRANT 명령으로 권한을 부여할 때 반드시 특정 객체를 명시해야 하며, 반대로 글로벌 권한은 GRANT 명령에서 특정 객체를 명시하지 말아야 한다.

예외적으로 ALL(또는 ALL PRIVILEGES)은 글로벌과 객체 권한 두 가지 용도로 사용될 수 있는데, 특정 객체에 ALL 권한이 부여되면 해당 객체에 적용될 수 있는 모든 객체 권한을 부여하며, 글로벌로 ALL이 사용되면 글로벌 수준에서 가능한 모든 권한을 부여하게 된다.

MySQL 5.7 버전까지는 정적 권한으로 MySQL 8.0 버전부터는 동적 권한이 추가되었다. 정적 권한은 MySQL 서버의 소스코드에 고정적으로 명시돼 있는 권한을 의미하며, 동적 권한은 MySQL 서버의 컴포넌트나 플러그인이 설치되면 그때 등록되는 권한, 즉 서버가 시작되면서 동적으로 생성하는 권한을 말한다.

 

글로벌 권한 부여

GRANT SUPER ON *.* TO 'user'@'localhost';

글로벌 권한은 특정 DB나 테이블에 부여될 수 없기 때문에 글로벌 권한을 부여할 때 GRANT 명령의 ON 절에는 항상 *.*를 사용하게 된다. *.*은 모든 DB의 모든 오브젝트(테이블과 스토어드 프로시저나 함수 등)를 포함해서 MySQL 서버 전체를 의미한다. CREATE USER나 CREATE ROLE과 같은 글로벌 권한은 DB 단위나 오브젝트 단위로 부여할 수 있는 권한이 아니므로 항상 *.*로만 대상을 사용할 수 있다.

 

객체 권한 중 DB 권한 부여

GRANT EVENT ON *.* TO 'user'@'localhost';
GRANT EVENT ON employees.* TO 'user'@'localhost';

EVENT 권한은 권한 범위가 DB이므로, 특정 DB에 대해서만 권한을 부여하거나 서버에 존재하는 모든 DB에 대해 권한을 부여할 수 있기 때문에 ON 절에 *.*이나 employees.* 모두 사용할 수 있다. 여기서 DB라 함은 DB 내부에 존재하는 테이블뿐만 아니라 스토어드 프로그램들도 모두 포함한다.

 

객체 권한 중 테이블 권한 부여

GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'user'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON employees.* TO 'user'@'locahost';
GRANT SELECT, INSERT, UPDATE, DELETE ON employees.department TO 'user'@'localhost';

테이블 권한은 모든 DB에 대한 권한 부여, 특정 DB의 오브젝트에 대한 권한 부여, 그리고 특정 DB의 특정 테이블에 대한 권한을 부여하는 것도 가능하다. 여러 가지 레벨이나 범위로 권한을 설정하는 것이 가능하지만 테이블이나 컬럼 단위의 권한은 잘 사용하지 않는다. 만약 컬럼 단위의 권한이 하나라도 설정되면 나머지 모든 테이블의 모든 컬럼에 대해서도 권한 체크를 하기 때문에 컬럼 하나에 대해서만 권한을 설정하더라도 전체적인 성능에 영향을 미칠 수 있다.

 

역할(Role)

CREATE ROLE role_emp_read, role_emp_write;

GRANT SELECT ON employees.* TO role_emp_read;
GRANT INSERT, UPDATE, DELETE ON employees.* TO role_emp_write;

MySQL 8.0 버전부터 권한을 묶어서 역할(Role)을 사용할 수 있게 되었다. 실제 MySQL 서버 내부적으로 역할(Role)은 계정과 똑같은 모습을 하고 있고, 위처럼 CREATE ROLE 명령어를 통해 role_emp_read와 role_emp_write이라는 이름의 역할을 정의한다. 그리고 역할의 이름대로 role_emp_read 역할에는 employees DB의 모든 객체에 대해 읽기(SELECT) 권한만 부여했으며, 'role_emp_write' 역할에는 employees DB의 모든 객체에 대해 데이터 변경(INSERT, UPDATE, DELETE) 권한을 부여했다.

 

 

 

출처

Real MySQL 8.0 1권 : 개발자와 DBA를 위한 MySQL 실전 가이드