Real MySQL 8.0 Vol.1 - 시스템 변수

MySQL 시스템 변수의 특징

 

MySQL 서버는 기동 하면서 설정 파일의 내용을 읽어 메모리나 작동 방식을 초기화하고, 접속된 사용자(Session)를 제어하기 위해 이러한 값을 별도로 저장해 둡니다.

 

시스템 변수는 적용 범위에 따라 글로벌 변수와 세션 변수로 나뉩니다. 변수의 'Var Scope'는 Global, Session, Both(글로벌, 세션 동시)가 있습니다.

 

글로벌 변수

- 하나의 MySQL 서버 인스턴스에서 전체적으로 영향을 미치는 시스템 변수를 의미

- 대체로 MySQL 서버 자체에 관련된 설정

- 대표적으로 InnoDB 버퍼 풀 크기(innodb_buffer_pool_size) 또는 MyISAM의 키 캐시 크기(key_buffer_size)

 

세션 변수

- MySQL 클라이언트가 MySQL 서버에 접속할 때 기본으로 부여하는 옵션의 기본값을 제어하는 데 사용

세션 범위의 시스템 변수는 (Global, Both가 아닌) MySQL 서버의 설정 파일에 초깃값을 명시할 수 없으며, 커넥션이 만들어지는 순간부터 해당 커넥션에만 유요한 설정 변수를 의미합니다.

 

MySQL 서버의 시스템 변수는 또한, MySQL 서버가 기동 중인 상태에서 변경 가능한지에 따라 동적 변수와 정적 변수로 구분됩니다. MySQL 서버의 시스템 변수를 변경할 수 있는 방법은 크게 2가지로 다음과 같습니다.

- 설정 파일(my.cnf 또는 my.ini)을 변경하는 경우

- 가동 중인 MySQL 서버의 메모리에 있는 MySQL 서버의 시스템 변수를 변경하는 경우

 

SHOW 명령어를 통해 MySQL 서버에 적용된 변수의 값을 확인할 수 있으며, SET 명령을 이용해 값을 변경할 수 있습니다.

# 변수의 값을 확인
SHOW GLOBAL VARIABLES LIKE '변수명';

# 시스템 변수 변경
SET GLOBAL max_connections=150;

# 좀 더 확장되게 확인 [Querydsl의 contains]
SHOW GLOBAL VARIABLES LIKE '%이건포함됐다%';

 

다만, SET 명령을 통해 변경한 시스템 변수는 MySQL의 설정 파일인 my.cnf (또는 my.ini) 파일에 반영되는 것은 아니기 때문에 현재 기동 중인 MySQL 인스턴스에서만 유효합니다. [MySQL 서버를 재가동하면 설정 파일의 내용으로 초기화가 되기 때문에] 그래서 이 시스템 변수를 영구적으로 적용하려면 설정 파일인 my.cnf 파일도 반드시 변경해줘야 합니다.

 

동적 변수면서 시스템 변수의 범위가 'Both'인 경우(글로벌이자 세션) SET 명령을 통해 변수를 변경했을 때, 글로벌 시스템 변수의 값은 변경이 되어도 이미 존재하는 커넥션의 세션 변숫값은 그대로 유지됩니다.

 

MySQL 8.0 버턴부터는 SET PERSIST 명령을 이용하면 실행 중인 MySQL 서버의 시스템 변수를 변경함과 동시에 자동으로 설정 파일로도 기록이 됩니다.

SHOW, SET 명령에서 GLOBAL 키워드를 사용하면 글로벌 변수에 대한 작업을 하는 것이며
GLOBAL 키워드를 빼면 자동으로 세션 변수에 대한 작업을 하는 것입니다.

 

SET PERSIST

글로벌 변수 vs. 세션 변수
동적 변수 vs. 정적 변수
의 개념을 좀 분리해서 가져가야 이해하기 편한 거 같습니다.

 

SET, SET, SET을 사용하면서 서버의 시스템 변수를 변경하면, my.cnf의 설정 파일도 같이 변경을 해줘야 합니다. 그렇지 않으면 언젠가 MySQL 서버를 재시작했을 때 SET 이전의 시스템 변숫값으로 초기화가 되기 때문입니다. 실제로 이로 인해 장애가 발생하는 케이스가 꽤 빈번하게 일어난다고 해주셨습니다.

 

이러한 문제점을 보완하기 위해 MySQL 8.0 버전에서 SET PERSIST 명령을 도입했습니다.

 

SET PERSIST 명령을 사용하여 시스템 변수를 변경하게 되면 MySQL 서버는 변경된 값을 즉시 적용함과 동시에 my.cnf 파일이 아닌 별도의 설정 파일(mysqld-auto.cnf)에 변경 내용을 기록합니다. 향후에 MySQL 서버를 재시작할 때 별도의 설정 파일(mysqld-auto.cnf)을 my.cnf 설정 파일과 함께 참조하여 시스템 변수를 적용합니다.

 

만약 현재 실행 중인 MySQL 서버에는 변경 내용을 적용하지 않고 향후에 재시작을 위해 mysqld-auto.cnf 파일에만 변경 내용을 기록하고 싶으시면 SET PERSIST_ONLY 명령을 사용하면 됩니다.

 

또한, SET PERSIST_ONLY 명령은 정적인 변수의 값을 영구적으로 변경하기 위해서 사용되기도 합니다.

SET PERSIST 명령은 동적인 변수의 값을 변경함과 동시에 mysqld-auto.cnf를 통해 영구 반영을 하는 것이 목적인데 정적인 변수에 SET PERSIST 명령을 사용하게 되면 정상적으로 작동하지 않습니다. 그러나, SET PERSIST_ONLY 명령을 사용하게 되면 변경된 변수의 값이 바로 적용되지 않더라도 mysqld-auto.cnf 파일에 정적인 변수의 값이 변경되어 기록될 것이며 향후에 서버를 재시작할 때 mysqld-auto.cnf를 참조하여 정적 변수가 변경되도록 합니다.

 

mysqld-auto.cnf 파일에는

1. 변경된 시스템 변수의 이름

2. 설정값

3. 언제(When) 변경했으며

4. 누가(Who) 변경했는지 등의 정보도 함께 기록됩니다.

 

이미 SET PERSIST, SET PERSIST_ONLY로 시스템 변숫값을 변경했는데, 그 내용을 삭제해야 할 때도 있을겁니다. 그때는 mysqld-auto.cnf의 파일을 직접 변경할 수도 있는데, 이럴 경우에는 내용상 오류를 만들 수 있는 여지가 있기 때문에 MySQL 서버가 정상적으로 재가동되지 않을 수 있습니다. 그렇기 때문에 mysqld-auto.cnf 파일의 내용을 삭제할 때의 권장하는 방법은 RESET PERSIST 명령을 사용하는 것이 여러모로 안전합니다.

# 특정 시스템 변수만 삭제
RESET PERSIST max_connections;
RESET PERSIST IF EXISTS max_connections;

# mysqld-auto.cnf 파일의 모든 시스템 변수 삭제
RESET PERSIST;