MySQL 서버는 기동하면서 설정 파일의 내용을 읽어 메모리나 작동 방식을 초기화하고, 접속된 사용자를 제어하기 위해 이러한 값을 별도로 저장해 둔다. MySQL 서버에서는 이렇게 저장된 값을 시스템 변수(System Variables)라고 한다.
각 시스템 변수는 MySQL 서버에서 아래 명령어로 확인할 수 있다.
$ mysql > SHOW GLOBAL VARIABLES;
시스템 변수(설정) 값이 어떻게 MySQL 서버와 클라이언트에 영향을 미치는지 판단하려면 각 변수가 글로벌 변수인지 세션 변수인지 구분할 수 있어야 한다.
▶ 시스템 변수가 가지는 5가지 속성
- Cmd-Line : MySQL 서버의 명령행 인자로 설정될 수 있는지 여부를 나타낸다. 즉, 이 값이 "Yes"이면 명령행 인자로 이 시스템 변수의 값을 변경하는 것이 가능하다는 의미이다.
- Option File : MySQL의 설정 파일인 my.cnf(또는 my.ini)로 제어할 수 있는지 여부를 나타낸다. 옵션 파일이나 설정 파일 또는 킨피규레이션 파일 등은 전부 my.cnf(or my.ini)파일을 지칭하는 것으로 같은 의미로 사용된다.
- System Var : 시스템 변수인지 아닌지를 나타낸다. MySQL 서버의 설정 파일을 작성할 때 각 변수명에 사용된 하이픈('-')이나 언더스코어('_')의 구분에 주의해야 한다. 이는 MySQL 서버가 예전부터 수많은 사람들의 손을 거쳐오면서 생긴 일관성 없는 변수의 명명 규칙 때문이다. 어떤 변수는 하이픈으로 구분되고 어떤 시스템 변수는 언더스코어로 구분되는 등 상당히 애매모호한 부분이 있는데, 뒤늦게 이런 부분을 언더스코어로 통일해가는 중이다. 현재 MySQL 8.0에서는 모든 시스템 변수들이 '_'를 구분자로 사용하도록 변경된 것으로 보인다. 그리고 명령행 옵션으로만 사용 가능한 설정들은 '_'가 아니라 '-'을 구분자로 사용한다.
- Var Scope : 시스템 변수의 적용 범위를 나타낸다. 이 시스템 변수가 영향을 미치는 곳이 MySQL 서버 전체(Global)를 대상으로 하는지, 아니면 MySQL 서버와 클라이언트 간의 커넥션(Session)만인지 구분한다. 그리고 어떤 변수는 세션과 글로벌 범위에 모두 적용(Both)되기도 한다.
- Dynamic : 시스템 변수가 동적인지 정적인지 구분하는 변수이다.
▶ 글로벌 변수와 세션 변수
MySQL의 시스템 변수는 적용 범위에 따라 글로벌 변수와 세션 변수로 나뉘는데, 일반적으로 세션별로 적용되는 시스템 변수의 경우 글로벌 변수 뿐만 아니라 세션 변수에도 동시에 존재한다. 이러한 경우 MySQL 매뉴얼의 'Var Scope'에는 'Both'라고 표시된다.
- 글로벌 범위의 시스템 변수는 하나의 MySQL 서버 인스턴스에서 전체적으로 영향을 미치는 시스템 변수를 의미하며, 주로 MySQL 서버 자체에 관련된 설정일 때가 많다. MySQL 서버에서 단 하나만 존재하는 InnoDB 버퍼 풀 크기(innodb_buffer_pool_size) 또는 MyISAM의 키 캐시 크기(key_buffer_size) 등이 가장 대표적인 글로벌 영역의 시스템 변수이다.
- 세션 범위의 시스템 변수는 MySQL 클라이언트가 MySQL 서버에 접속할 때 기본으로 부여하는 옵션의 기본값을 제어하는 데 사용된다. 다른 DBMS에서도 거의 비슷하겠지만 MySQL에서도 각 클라이언트가 처음에 접속하면 기본적으로 부여하는 기본값을 가지고 있다. 별도로 그 값을 변경하지 않은 경우에는 그대로 값이 유지되지만, 클라이언트의 필요에 따라 개별 커넥션 단위로 다른 값으로 변경할 수 있는 것이 세션 변수다.
- 세션 범위의 시스템 변수 가운데 MySQL 서버의 설정 파일(my.cnf or my.ini)에 명시해 초기하할 수 있는 변수는 대부분의 범위가 'Both'라고 명시돼 있다. 이렇게 'Both'로 명시된 시스템 변수는 MySQL 서버가 기억만 하고 있다가 실제 클라이언트와의 커넥션이 생성되는 순간에 해당 커넥션의 기본값으로 사용되는 값이다. 그리고 순수하게 범위가 세션(Session)이라고 명시된 시스템 변수는 MySQL 서버의 설정 파일에 초깃값을 명시할 수 없으며, 커넥션이 만들어지는 순간부터 해당 커넥션에서만 유효한 설정 변수를 의미
▶ 정적 변수와 동적 변수
MySQL 서버의 시스템 변수는 MySQL 서버가 기동 중인 상태에서 변경 가능한지에 따라 동적 변수와 정적 변수로 구분된다. MySQL 서버의 시스템 변수는 디스크에 저장돼 있는 설정 파일을 변경하는 경우와 이미 기동 중인 MySQL 서버의 메모리에 있는 MySQL 서버의 시스템 변수를 변경하는 경우로 구분할 수 있다. 디스크에 저장된 설정 파일의 내용은 변경하더라도 MySQL 서버가 재시작하기 전에는 적용되지 않는다.
SET 명령을 통해 변경되는 시스템 변숫값이 MySQL의 설정 파일인 my.cnf 파일에 반영되는 것은 아니기 때문에 현재 기동 중인 MySQL의 인스턴스에서만 유효하다. MySQL 서버가 재시작하면 다시 설정 파일의 내용으로 초기화되기 때문에 설정을 영구히 적용하려면 my.cnf 파일도 반드시 변경해야 한다.
MySQL 8.0 버전부터는 SET PERSIST 명령을 이용하면 실행 중인 MySQL 서버의 시스템 변수를 변경함과 동시에 자동으로 설정 파일로도 기록된다. SHOW나 SET 명령에서 GLOBAL 키워드를 사용하면 글로벌 시스템 변수의 목록과 내용을 읽고 변경할 수 있으며, GLOBAL 키워드를 빼면 자동으로 세션 변수를 조회하고 변경한다.
일반적으로 글로벌 시스템 변수는 MySQL 서버의 기동 중에는 변경할 수 없는 것이 많지만 실시간으로 변경할 수 있는 것도 있다. my.cnf 설정 파일을 변경할 때 MySQL 서버를 재시작하는 경우가 많은데, 사실 변경하고자 하는 값이 동적 변수라면 SET 명령으로 간단히 변숫값을 변경할 수 있으며, 굳이 MySQL 서버를 재시작하지 않아도 된다.
이처럼 동적으로 시스템 변숫값을 변경하는 경우 SET 명령으로 시스템 변수를 변경하면 my.cnf 설정 파일에는 변경 내용이 기록되지 않는다. 설정 파일까지 내용을 변경하고자 한다면 SET PERSIST 명령을 사용해야 한다. SET PERSIST 명령을 사용하는 경우 변경된 시스템 변수는 my.cnf 파일이 아닌 별도의 파일에 기록된다.
▶ SET PERSIST
SET PERSIST 명령으로 시스템 변수를 변경하면 MySQL 서버는 변경된 값을 즉시 적용함과 동시에 별도의 설정 파일(mysqld-auto.cnf)에 변경 내용을 추가로 기록해둔다. 그리고 MySQL 서버가 다시 시작될 때 기본 설정 파일(my.cnf) 뿐만 아니라 자동 생성된 mysqld-auto.cnf 파일을 같이 참조해서 시스템 변수를 적용한다.
즉, SET PERSIST 명령을 사용하면 MySQL 서버 설정 파일(my.cnf)에 변경 내용을 수동으로 기록하지 않아도 자동으로 영구 변경이 되는 것이다.