MySQL 서버 및 시스템 변수 설정

MySQL 서버 연결 테스트

MySQL 서버에 연결 테스트하는 방법은 크게 3가지가 있다.

1. mysql -uroot -p --host=localhost --socket=/tmp/mysql.sock
2. mysql -uroot -p --host=127.0.0.1 --port=3306
3. mysql -uroot -p

위 명령어들은 기본적으로 로컬에 설정된 서버에 연결하는 명령어이다.

원격 서버로 연결하기 위해서는 2번 명령어의 host 옵션의 값을 원격 서버의 IP 주소로 설정한다.

1번 명령어는 소켓 파일을 통해 접속하는 명령어이다. --host=localhost 를 사용하는 경우에는 소켓을 사용하게 되는데, 이 때 이 소켓은 Unix Domain Socket으로 웹 소켓이 아닌 IPC의 일종이다.

2번 명령어는 비록 127.0.0.1 이 루프백 IP(localhost)이긴 하지만 1번 명령어와 다르게 TCP/IP 통신 방식으로 DB 서버에 접속한다.

3번 명령어는 기본적으로 1번 명령어와 똑같이 옵션들이 세팅되게 되는데, 소켓 파일의 위치는 MySQL 서버의 설정 파일에서 읽어서 사용한다.

 

MySQL Server 접속

MySQL 서버 업그레이드

MySQL 서버를 업그레이드하는 방법에는 크게 두 가지가 있다.

1. 데이터 파일은 그대로 두고 업그레이드
2. mysqldump 도구 등을 이용해 데이터를 덤프한 후 업그레이드 된 서버에서 다시 적재

1번 방법을 인플레이스 업그레이드라고 하며, 2번 방법을 논리적 업그레이드라고 한다.

마이너 버전을 업그레이드하는 경우에는 인플레이스 업그레이드로 진행하면 된다. 여러 버전을 건너뛰어 업그레이드할 수도 있다.

예를 들어, 8.0.11 버전에서 8.0.15 버전으로 업그레이드하는 경우에는 데이터 파일에 대한 고려없이 바로 서버 업그레이드를 진행하면 된다.

메이저 버전을 인플레이스 업그레이드하는 경우에는 직전 버전에서 업그레이드를 진행해야한다.

예를 들어 5.5 버전에서 8.0 버전으로 업그레이드를 한다고 가정하면, 5.5 → 5.6 → 5.7 → 8.0 으로 업그레이드를 진행해야한다.

이러한 경우에는 5.5 → 8.0으로 논리적 업그레이드를 하는 것이 더 효율적일 수도 있다.

 

고려사항

MySQL 8.0 버전부터 많은 내용이 바뀌어 업그레이드 작업 시 고려해야하는 사항들이 있다.

사용자 인증방식 변경	MySQL 8.0 버전부터 인증방식이 caching sha-2 authentication 으로 바뀌었다. 기존의 native authentication을 사용하고 싶다면 --default-authentication-plugin=mysql_native_authentication 파라미터를 활성화해야한다.
호환성 체크	MySQL 8.0 이전 버전에서 손상된 파일이나 호환되지 않는 타입, 함수가 있는지 mysqlcheck 유틸리티로 확인하는 것이 권장된다.
외래 키 이름 길이	MySQL 8.0 부터 외래 키 이름이 64자로 제한되므로 확인이 필요하다.
인덱스 힌트	MySQL 8.0 이전 버전에서 사용되던 힌트가 오히려 성능 저하를 유발할 수 있다.
Group by 정렬 옵션	MySQL 8.0 이전 버전에서 Group by 절에 ASC/DESC를 사용하는 경우 수정해줘야 한다.
파티션 공용 스페이스	MySQL 8.0 버전부터 파티션의 테이블 스페이스를 공용 스페이스에 저장할 수 없다. ALTER TABLE … 명령어로 파티션의 스페이스를 개별 스페이스로 수정해주어야 한다.

 

서버 설정

일반적으로 MySQL은 my.cnf 라는 설정 파일을 사용하게 되는데, 이 파일의 경로는 아래와 같이 확인할 수 있다.

my.cnf 확인

mysqld --verbose --help 명령어로 확인할 수 있으며 굉장히 많은 라인의 결과가 출력되므로 | grep my.cnf 명령어를 추가하여 실행해도 좋다.

위 명령어의 결과로 설정 파일의 경로는

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

의 우선순위 순서로 찾게된다.

즉, 여러 개의 설정 파일이 있는 경우 우선순위가 높은 파일을 기준으로 설정값을 세팅한다.

 

파일 경로

나의 경우에는 위의 3번 경우에 해당하는 경로에 설정 파일이 세팅되어 있는 것으로 보인다.

아마 homebrew를 통해 설치한 경우에는 해당 경로에 자동으로 파일이 생성되는 것 같다.

 

파일 구성

이렇게 생성된 설정 파일은 여러 개의 설정 그룹을 담을 수 있으며, 이 때의 그룹명은 실행 프로그램 이름으로 한다.

예를 들어, 위의 나의 설정파일의 설정 그룹은 [mysqld] 로 명시되어 있으며, 이는 mysqld 프로그램을 실행할 때 사용되는 설정 값들이다.

 

MySQL 시스템 변수

MySQL 서버는 기동하는 시점에 설정파일의 내용을 읽어 이러한 값들을 별도로 저장해둔다.

이렇게 저장된 값을 시스템 변수라고 한다.

각 시스템 변수는 show global variables 명령어로 확인할 수 있다.

MySQL 시스템 변수

이와 같은 변수들에 대해서 어떻게 영향을 미치는지 판단하기 위해서는 각 변수가 글로벌 변수인지 세션 변수인지 구분할 수 있어야 한다.

이를 구분하기 위해서는 각 시스템 변수에 대한 설명이 정리되어 있는 아래의 사이트를 참고하자.

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

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

 

MySQL 공식문서의 시스템 변수

여기서 각 시스템 변수가 가지는 5가지 속성의 의미는 아래와 같다.

1. Cmd-Line: 서버의 명령행으로 설정가능한지의 여부
2. Option File: my.cnf로 설정가능한지의 여부
3. System Var: 시스템 변수인지의 여부
4. Var Scope: 시스템 변수의 적용 범위 [Global: 전역, Session: 커넥션, Both: 둘 다]
5. Dynamic: 동적/정적 변수 여부

 

글로벌 변수와 세션 변수

Var Scope 속성에 따라 각 변수들은 글로벌 변수와 세션 변수로 구분된다.

글로벌 변수는 하나의 MySQL 인스턴스에 전체적으로 영향을 미치는 하나의 시스템 변수를 의미한다.

예를 들어 innodb_buffer_pool_size 변수는 InnoDB 버퍼 풀 크기를 세팅하는 글로벌 변수이다.

 

세션 변수는 클라이언트가 MySQL 서버에 접속할 때 기본적으로 부여되는 기본값으로 사용된다.

그렇기 때문에 각 커넥션에 따라 클라이언트에서 설정을 다르게 주면 세션 변수를 사용하지 않고 설정된 값을 사용하게 된다.

예를 들어 autocommit 변수는 쿼리 단위로 자동 커밋을 수행할지 여부를 설정하는 세션 변수이고, 클라이언트가 커넥션을 맺은 이후 변경할 수 있다.

Var Scope가 Both로 설정된 변수가 위와 같이 기본값으로 동작하는 세션 변수이며, Session으로 설정된 세션 변수는 초기값 세팅은 불가능하고 커넥션이 만들어지는 순간부터 값을 설정할 수 있는 세션 변수이다.

 

정적 변수와 동적 변수

시스템 변수를 바꾸는 방법은 my.cnf 파일을 수정하는 방법과, SET 명령어를 통해 서버 메모리의 변수를 수정하는 방법이 있다.

이 중 후자의 방법으로 수정이 가능한 시스템 변수를 동적 변수라고 한다.

max_connection 시스템 변수의 속성

max_connections 시스템 변수의 설정 값

 

동적 변수인 max_connections를 SET 명령어로 수정해보자.

max_connections 시스템 변수의 설정 값 변경

GLOBAL 키워드를 사용해 전역 변수의 값을 SET 했다. 해당 키워드를 빼면 자동으로 세션 변수의 값을 SET 한다.

이와 같이 MySQL 서버 메모리에 설정되어 있는 시스템 변수의 값을 바꿀 수 있다.

 

하지만 이와 같은 경우에는 설정 파일에 변경값이 반영되는 것이 아니기 때문에, 서버가 재기동하는 경우에는 원래 값으로 초기화된다.

설정 파일의 값을 같이 바꾸고 싶은 경우에는 SET PERSIST 키워드를 사용하면된다.

SET PERSIST 키워드를 사용한 시스템 변수 수정

 

mysqld-auto.cnf에 변경된 내용

이처럼 mysqld-auto.cnf 파일이 생성되며 변경 내용을 기록해두고 서버 재기동 시 똑같이 반영하게 된다.

 

시스템 변수 메타데이터 조회 뷰

그리고 반영된 변수의 메타 데이터는 performance_schema.variables_info 뷰와 performance_schema.persisted_variables 테이블을 통해 참조가능하다.

 

다음 글: https://gojs.tistory.com/37

MySQL 엔진 아키텍쳐