[realmySQL][4] 시스템 변수

MySQL 워크밴치를 오픈하고 아래 명령어를 통해 시스템변수들의 목록을 볼 수 있습니다.

 

SHOW GLOBAL VARIABLES;

 

MySQL 서버는 기동하면서 설정파일의 내용을 읽어 메모리나 작동방식을 초기화 합니다. MySQL 서버에서는 이렇게 저장된 값을 시스템변수라고 합니다. 

 

https://dev.mysql.com/doc/refman/8.0/en/server-system-variable-reference.html

MySQL :: MySQL 8.0 Reference Manual :: 5.1.5 Server System Variable Reference

위 링크는 MySQL 서버에서 시스템 변수의 레퍼런스입니다. 5가지 속성의 의미에 대해서 알아보겠습니다.

 

 

Cmd-Line : 명령어로 변경가능한지 나타냅니다. Yes이면 명령행 인자로 이 시스템변수의 값을 변경하는 것이 가능합니다.

Option file: my.cnf 파일로 설정가능한지 나타냅니다.

System var : 시스템 변수인지 아닌지 나타냅니다.

Var Scope : 시스템변수의 적용 범위를 나타냅니다. MySQL 서버 전체대상이면 Global , 서버와 클라이언트의 커넥션 즉, Session , 세션과 글로벌 모두 적용되면 Both.

Dynamic : 시스템 변수가 동적인지 정적인지 구분합니다.

 

- 지금은 어떤 의미인지는 깊게 몰라도 될거같습니다. 앞으로 진행하면서 자연스럽게 체득할 겁니다.

글로벌 변수와 세션 변수

MySQL에서 사용하는 시스템변수는 그 적용범위에 따라 글로벌 변수와 세션 변수로 나뉩니다. 일반적으로 세션별로 적용되는 시스템변수의 경우 글로벌 변수 뿐만 아니라 세션변수에도 동시에 존재하여 Both로 표기 됩니다.

글로벌 변수

- MySQL 서버 인스턴스엥서 전체적으로 영향을 미치는 시스템 변수입니다.

- 주로 MySQL 서버 자체와 관련된 설정일 때가 많습니다.

- MySQL 서버에서 단 하나만 존재하는 InnoDB 버퍼 풀 크기(innodb_buffer_pool_size) 또는 MyISAM의 키 캐시크기(key_buffer_size) 등이 가장 대표적인 글로벌 변수입니다.

 

세션 변수

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

- MySQL 클라이언트의 필요에 따라 개별 커넥션 별로 다른 값으로 변경가능 한 것이 세션변수 입니다.

- 기본값은 글로벌 변수 값이고 각 클라이언트가 가지는 값이 세션 변수값 입니다.

- 예를 들어 autocommit 을 ON으로 둔 클라이언트는 기본으로 자동커밋모드이지만 OFF로 비활성화 할 수 도 있습니다.

- 이미 연결된 커넥션에서 서버는 강제로 세션변수를 변경할 수 없습니다.

 

정적 변수와 동적 변수

MySQL서버의 시스템 변수는 MySQL 서버가 기동 중인 상태에서 변경가능한지에 따라 동적변수와 정적변수로 구분됩니다.

변경하는 경우도 구분할 수 있습니다.

 

1) 디스크에 저장된 서버의 설정 파일(my.cnf)를 변경하는 경우

2) 이미 기동중인 서버의 메모리에 있는 시스템변수를 변경하는 경우

 

1)의 경우에는 재시작을 하지 않으면 적용되지 않습니다. 

 

예시를 통해 알아보겠습니다.

show global variables like 'max_connections%';

 

현재 최대 연결수는 151 입니다. 

 

 SET GLOBAL max_connections=500;
 
 show global variables like 'max_connections%';

위 명령어를 통해서 시스템 변수의 값들을 변경합니다.

 

 

SET 명령어를 통해 변경되는 시스템변수값은 파일에 반영되지 않기 때문에 재시작하면 초기화됩니다.

 

---

 

여담으로  저는 현재 debian 10 에서 실습중이며 혹시 mysql-server 가 설치되어 있지 않다면 아래 경로를 통해 설치하시기 바랍니다.

https://www.digitalocean.com/community/tutorials/how-to-install-the-latest-mysql-on-debian-10

How To Install the Latest MySQL on Debian 10 | DigitalOcean

---

 

위 mysql 은 도커내부에서 정지가 안됩니다.. 정지하더라도 다시 시작하더군요. 

그래서 컨테이너 자체를 중지시키고 다시 접속해보면

 

 

시스템 변수의 값이 151로 다시 초기화 되었습니다.

 

정리하면 SHOW 나 SET 명령어에서 GLOBAL 키워드를 사용하면 글로벌 시스템 변수의 목록과 내용을 변경할 수 있고 GLOBAL을 제거하면 자동으로 세션변수를 조회하고 변경합니다.

 

SET PERSIST

위 명령어는 세션 변수에는 적용되지 않습니다. 즉, 글로벌 변수로 자동으로 인식하고 변경합니다. 만약 현재 실행중인 서버는 변경내용을 저장하지 않고 다음 재시작을 위해 mysqld-auto.cnf 파일에만 변경내용을 기록하려면 SET PERSIST_ONLY 를 사용합니다.

 

그래서 주로 SET PERSIST_ONLY는 정적변수변경에 사용합니다. ( 예를 들어 innodb_doublewrite )

mysqld-auto.cnf 파일은 /var/lib/mysql 에 위치해 있습니다.

실제 내용을 보면 변경 내역이 저장되어있습니다.

컨테이너 종료후 다시 접속해보면 아래와 같이 변경 내역이 저장되었습니다.

 

 

만약 정적 변수를 SET PERSIST 를 이용해 변경하려고 한다면 실행중에 변경하게 되므로 명령이 실행되지 않습니다.

예를 들어 아래와 같은 현상입니다.

 

그래서 SET PERSIST_ONLY 를 통해서 정적변수를 다음 재시작을 위해 변경합니다.

 

또한 변경 내역들을 테이블을 통해서 보려면 아래의 쿼리를 입력하면 됩니다.

SELECT a.variable_name, b.variable_value,a.set_time, a.set_user, a.set_host
FROM performance_schema.variables_info a
INNER JOIN performance_schema.persisted_variables b
ON a.variable_name = b.variable_name

그러면 이때 까지 변경된 내역들이 등장하게 됩니다.

 

 

아래 명령어는 모든 시스템 변수변경내역 즉, mysqld-auto.cnf 의 변수를 삭제합니다.

RESET PERSIST;