2022-08-03 @이영훈
1. MySQL 서버 설치
리눅스의 RPM이나 운영체제 별 인스톨러를 이용하여 설치하기를 권장한다.
버전과 에디션(엔터프라이즈와 커뮤니티) 선택
MySQL 서버의 버전을 선택할 때는 다른 제약 사항 (기존 솔루션이 특정 버전만 지원하는 경우)이 없다면 가능한 한 최신 버전을 설치하는 것이 좋다.
기존 버전에서 새로운 메이저 버전(MySQL 5.1, 5.5, 5.6, 5.7, 8.0)으로 업그레이드하는 경우라면 최소 패치 버전이 15~20번 이상 릴리즈된 버전을 선택하는 것이 안정적인 서비스에 도움이 될 것이다.
즉, MySQL 8.0 버전이라면 MySQL 8.0.15 ~ 8.0.20 사이의 버전부터 시작하는 것을 권장한다.
오픈 코어 모델(Open Core Model). MySQL 서버의 상용화 전략은 핵심 내용은 엔터프라이즈 에디션과 커뮤니티 에디션 모두 동일하며, 특정 부가 기능들만 상용 버전인 엔터프라이즈 에디션에 포함되는 방식이다.
다음은 엔터프라이즈 에디션에서만 지원하는 부가 기능과 서비스
•
Thread Pool
•
Enterprise Audit
•
Enterprise TDE (Master Key 관리)
•
Enterprise Authentication
•
Enterprise Firewall
•
Enterprise Monitor
•
Enterprise Backup
•
MySQL 기술 지원
Percona에서 출시하는 Percona Server 백업 및 모니터링 도구, Percona Server에서 지원하는 플러그인 (Thread Pool과 Audit 플러그인 등)을 활용하면 MySQL 커뮤니티 에디션의 부족한 부분을 메꿀 수 있었기 때문에 MySQL 엔터프라이즈 에디션의 필요성은 그다지 크지 않았다.
MySQL 설치
•
리눅스 서버의 yum 인스톨러 설치
•
macOS용 DMG 패키지 설치
MySQL 서버가 설치된 디렉터리는 /usr/local/mysql이며, 하위의 각 디렉터리 정보는 다음과 같다. 최소한 다음 디렉터리들은 절대 삭제하면 안된다.
•
bin: MySQL 서버와 클라이언트 프로그램, 유틸리티를 위한 디렉터리
•
data: 로그 파일과 데이터 파일들이 저장되는 디렉터리
•
include: C/C++ 헤더 파일들이 저장된 디렉터리
•
lib: 라이브러리 파일들이 저장된 디렉터리
•
share: 다양한 지원 파일들이 저장돼 있으며, 에러 메시지나 ㅅ갬플 설정 파일(my.cnf)이 있는 디렉터리
•
윈도우 MSI 인스톨러 설치
2. MySQL 서버의 시작과 종료
여기에서는 거의 대부분의 서비스 환경에서 사용되는 리눅스 운영체제에서 MySQL 서버의 설정 파일을 비롯해 MySQL 서버를 시작, 종료하는 방법을 살펴보겠다.
설정 파일 및 데이터 파일 준비
리눅스 서버에서 yum 인스톨러나 RPM을 이용해 MySQL 서버를 설치하면
MySQL 서버에 필요한 프로그래들과 디렉터리들은 일부 준비되지만, 트랜잭션 로그 파일과 시스템 테이블이 준비되지 않기 때문에 아직 MySQL 서버를 시작할 수 없다.
MySQL 서버가 설치되면 /etc/my.cnf 설정 파일이 준비되는데, 이 설정 파일에는 MySQL 서버를 실행하는 데 꼭 필요한 3~4개의 아주 기본적인 설정만 기록돼 있다.
다음과 같이 MySQL 서버를 실행하는 데 필요한 초기 데이터 파일 (시스템 테이블이 저장되는 데이터 파일)과 트랜잭션 로그 (redo 로그) 파일을 생성하자.
linux> mysqld --defaults-file=/etc/my.cnf --initialize-insecure
Bash
복사
•
initialize-insecure 옵션: 필요한 초기 데이터 파일과 로그 파일들을 생성하고, 비밀번호가 없는 관리자 계정인 root 유저를 생성한다.
•
initialize 옵션: 생성된 관리자 계정의 비밀번호를 에러 로그 파일로 기록한다.
시작과 종료
/usr/bin/systemd/system/mysqld.service 파일이 생성되고, systemctl 유틸리티를 이용해 MySQL을 시작하거나 종료하는 것이 가능하다.
# start
linux> systemctl start mysqld
# stop
linux> systemctl stop mysqld
Bash
복사
MySQL 서버를 셧다운하려면 MySQL 서버에 로그인한 상태에서 SHUTDOWN 명령을 실행하면 된다.
원격으로 MySQL 서버를 셧다운하려면 SHUTDOWN 권한(Privileges)를 가지고 있어야 한다.
mysql> SHUTDOWN;
SQL
복사
MySQL 서버에서는 실제 트랜잭션이 정상적으로 커밋돼도 데이터 파일에 변경된 내용이 기록되지 않고 로그 파일(redo 로그)에만 기록돼 있을 수 있다.심지어 MySQL 서버가 종료되고 다시 시작된 이후에도 계속 이 상태로 남아있을 수도 있다.
사용량이 많은 MySQL 서버에서는 이런 현상이 더 일반적인데, 이는 결코 비정상적인 상황이 아니다.
클린 셧다운(Clean shutdown). MySQL 서버가 종료될 때 모든 커밋된 내용을 데이터 파일에 기록하고 종료하게 할 수도 있다. 클린 셧다운으로 종료되면 다시 MySQL 서버가 시작될 때 별도의 트랜잭션 복구 과정을 진행하지 않기 때문에 빠르게 시작할 수 있다.
mysql> SET GLOBAL innodb_fast_shutdown=0;
linux> systemctl stop mysqld.service
# 또는 원격으로 MySQL 서버 종료 시
mysql> SET GLOBAL innodb_fast_shutdown=0;
mysql> SHUTDOWN;
SQL
복사
서버 연결 테스트
linux> mysql -uroot -p --host=localhost --socket=/tmp/mysql.sock
linux> mysql -uroot -p --host=127.0.0.1 --port=3306
linux> mysql -uroot -p
Bash
복사
첫 번째는 소켓 파일을 이용해 접속하는 예제
•
--host=localhost 옵션을 사용하면 MySQL 클라이언트 프로그램은 항상 소켓 파일을 통해 MySQL 서버에 접속
•
이는 ‘Unix domain socket’을 이용하는 방식으로 IPC (Inter Process Communication)의 일종이다.
두 번째는 TCP/IP를 통해 127.0.0.1 (로컬 호스트)로 접속하는 예제
•
포트를 명시하는 것이 일반적
•
원격 호스트에 있는 MySQL 서버에 접속할 때는 반드시 두 번째 방법을 사용해야 한다.
•
--host=127.0.0.1 옵션을 사용하면 자기 서버를 가리키는 루프백(loopback) IP이기는 하지만 TCP/IP 통신 방식을 사용
세 번째는 별도로 호스트 주소와 포트를 명시하지 않는 예제
•
기본값으로 호스트는 localhost
•
소켓 파일 이용한 통신
•
소켓 파일의 위치는 MySQL 서버의 설정 파일에서 읽어서 사용. MySQL 서버가 시작될 때 만들어지는 유닉스 소켓 파일은 MySQL 서버를 재시작하지 않으면 다시 만들어 낼 수 없기 때문에 실수로 삭제하지 않도록 주의한다
3. MySQL 서버 업그레이드
MySQL 서버를 업그레이드하는 두 가지 방법
1.
인플레이스 업그레이드 (In-Place Upgrade)
•
MySQL 서버의 데이터 파일을 그대로 두고 업그레이드 하는 방법
•
여러 가지 제약 사항이 있지만 업그레이드 시간을 크게 단축할 수 있다
2.
논리적 업그레이드(Logical Upgrade)
•
mysqldump 도구 등을 이용해 MySQL 서버의 데이터를 SQL 문장이나 텍스트 파일로 덤프한 후, 새로 업그레이드된 버전의 MySQL 서버에서 덤프된 데이터를 적재하는 방법
•
버전 간 제약 사항이 거의 없지만 업그레이드 시간이 매우 많이 소요될 수 있다
인플레이스 업그레이드 제약 사항
동일 메이저 버전에서 마이너(패치) 버전 간 업그레이드는 대부분 데이터 파일의 변경 없이 진행되며, 많은 경우 여러 버전을 건너뛰어서 업그레이드 하는 것도 허용된다.
ex) MySQL 8.0.16 → MySQL 8.0.21 업그레이드 시 MySQL 서버 프로그램만 재설치하면 된다.
메이저 버전 간 업그레이드 대부분 크고 작은 데이터 파일의 변경이 필요하기 때문에 반드시 직전 버전에서만 업그레이드가 허용된다.
MySQL 8.0 서버 프로그램은 직전 메이저 버전인 MySQL 5.7 버전에서 사용하던 데이터 파일과 로그 포맷만 인식하도록 구현되기 때문이다.
ex) MySQL 5.5 → MySQL 5.6 ⭕️
ex) MySQL 5.5 → MySQL 5.7
ex) MySQL 5.5 → MySQL 8.0
두 단계 이상을 한 번에 업그레이드해야 한다면 mysqldump 프로그램으로 MySQL 서버에서 데이터를 백업받은 후 새로 구축된 MySQL 8.0 서버에 데이터를 적재하는 ‘논리적 업그레이드'가 더 나은 방법일 수도 있다.
항상 메이저 버전 업그레이드를 할 때는 MySQL 서버의 메뉴얼을 정독한 후 진행할 것을 권장한다.
MySQL 8.0 업그레이드 시 고려 사항
MySQL 5.7 버전과 8.0 버전의 기본적인 부분의 차이점과 MySQL 8.0에서는 사용할 수 없는 기능들이 몇 가지 있다.
1.
사용자 인증 방식 변경
•
MySQL 8.0 버전부터는 Caching SHA-2 Authentication 인증 방식이 기본 인증 방식으로 바뀌었다.
•
MySQL 5.7에 존재했던 사용자 계정은 여전히 Native Authentication 방식을 사용한다.
2.
MySQL 8.0과의 호환성 체크
•
MySQL 5.7 버전에서 손상된 FRM 파일이나 호환되지 않은 데이터 타입 또는 함수가 있는지 mysqlcheck 유틸리티를 이용해 확인해 볼 것을 권장한다.
•
FRM(Format File): 테이블 구조가 저장되어 있는 파일
3.
외래키 이름의 길이
•
MySQL 8.0에서는 외래키(Foreign Key)의 이름이 64글자로 제한된다.
4.
인덱스 힌트
•
MySQL 5.x에서 사용되던 인덱스 힌트가 있다면 MySQL 8.0에서 먼저 성능 테스트를 수행하자
•
MySQL 8.x에서는 오히려 성능 저하를 유발할 수도 있다
5.
GROUP BY에 사용된 정렬 옵션
•
MySQL 5.x에서 GROUP BY 절의 칼럼 뒤에 ‘ASC’나 ‘DESC’를 사용하고 있다면 먼저 제거하거나 다른 방식으로 변경하자.
6.
파티션을 위한 공용 테이블스페이스
•
MySQL 8.x에서는 파티션의 각 테이블스페이스를 공용 테이블스페이스를 저장할 수 없다.
MySQL 8.0 업그레이드
MySQL 8.0부터는 시스템 테이블의 정보와 데이터 딕셔너리 정보의 포맷이 완전히 바뀌었다.
MySQL 5.7에서 MySQL 8.0으로의 업그레이드는 크게 두 가지 단계로 나뉘어 처리된다.
1.
데이터 딕셔너리 업그레이드
•
MySQL 5.7 버전까지는 데이터 딕셔너리 정보가 FRM 확장자를 가진 파일로 별도로 보관됐었는데,
•
MySQL 8.0 버전부터는 데이터 딕셔너리 정보가 트랜잭션이 지원되는 InnoDB 테이블로 저장되도록 개선됐다.
2.
서버 업그레이드
•
MySQL 서버의 시스템 데이터베이스(performance_schema와 information_schema, 그리고 mysql 데이터베이스)의 테이블 구조를 MySQL 8.0 버전에 맞게 변경한다.
MySQL 8.0.16부터는 MySQL 서버 프로그램(mysqld)이 ‘데이터 딕셔너리 업그레이드'와 ‘서버 업그레이드'를 순서대로 진행한다.
4. 서버 설정
일반적으로 MySQL 서버는 단 하나의 설정 파일을 사용한다.
•
리눅스, 유닉스: my.cnf
•
윈도우: my.ini
•
MySQL 서버는 지정된 여러 개의 디렉터리를 순차적으로 탐색하면서 처음 발견된 my.cnf 파일을 사용하게 된다
또한 직접 MySQL을 컴파일해서 설치한 경우에는 이 디렉터리가 다르게 설정될 수도 있다.
# 클라이언트 프로그램으로 확인하는 방법 추천
shell> mysql --help
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf ✅
# MySQL 서버 프로그램으로 확인하다가 MySQL 서버가 실행 중인데 다시 시작을 한다거나 건드릴 수 있기 때문에
shell> mysqld --verbose --help
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf ✅
Bash
복사
MySQL 서버용 설정 파일은 주로 1번이나 2번을 사용한다.
설정 파일의 구성
MySQL 설정 파일은 하나의 my.cnf 파일에 여러 개의 설정 그룹을 담을 수 있으며, 대체로 실행 프로그램 이름을 그룹명으로 사용한다.
예를 들어, mysqldump 프로그램은 [mysqldump] 설정 그룹, mysqld 프로그램은 [mysqld] 영역을 참조한다. 그리고 mysqld_safe 프로그램은 [mysqld_safe]와 [mysqld] 섹션을 참조한다.
[mysqld_safe]
malloc-lib = /opt/lib/libtcmalloc_minial.so
[mysqld]
socket = /usr/local/mysql/tmp/mysql.sock
port = 3306
[mysql]
default-charset-set = utf8mb4 // utf8mb4를 사용하기 때문에 이모지를 볼 수 있다
socket = /usr/local/mysql/tmp/mysql.sock
port = 3304
[mysqldump]
default-char-set = utf8mb4
socket = /usr/local/mysql/tmp/mysql.sock
port = 3305
Plain Text
복사
MySQL 시스템 변수의 특징
시스템 변수 (System Variables): MySQL 서버는 시작하면서 설정 파일의 내용을 읽어 메모리나 작동 방식을 초기화하고, 접속된 사용자를 제어하기 위해 이러한 값을 별도로 저장해둔 값
# 세션 변수 확인
mysql> SHOW SESSION VARIABLES;
# 글로벌 변수 확인
mysql> SHOW GLOBAL VARIABLES;
# 세션(기본값) 변수 확인
mysql> SHOW VARIABLES;
SQL
복사
시스템 변수(설정) 값이 어떻게 MySQL 서버와 클라이언트에 영향을 미치는 지 판단하려면 각 변수가 글로벌 변수인지 세션 변수인지 구분할 수 있어야 한다.
다음 공식문서에서 시스템 변수들을 확인할 수 있습니다.
1.
Cmd-Line
a.
MySQL 서버의 명령행 인자로 설정될 수 있는지 여부를 나타낸다.
b.
Yes 이면 명령행 인자로 이 시스템 변수의 값을 변경하는 것이 가능하다
2.
Option file
•
MySQL의 설정 파일인 my.cnf로 제어할 수 있는지 여부를 나타낸다.
3.
System Var
•
시스템 변수인지 아닌지를 나타낸다.
•
시스템 변수를 언더스코어로 통일해가는 중이다. (하이픈(-)과 언더스코어(_)를 혼용해서 사용했다)
•
MySQL 8.0에서는 모든 시스템 변수들이 ‘_’를 구분자로 사용하도록 변경된 것으로 보인다
•
명령행 옵션으로만 사용 가능한 설정들은 ‘-’을 구분자로 사용한다
4.
Var Scope
•
시스템 변수의 적용 범위를 나타낸다
•
시스템 변수가 영향을 미치는 곳이 MySQL 서버 전체 (Global)인지,
•
MySQL 서버와 클라이언트 간의 컨낵션 (Session)인지
•
세션과 글로벌 범위 모두 적용(Both)인지
5.
Dynamic
•
시스템 변수가 동적인지 정적인지 구분하는 변수
글로벌 변수와 세션 변수
적용 범위에 따라 글로벌 변수와 세션 변수로 나뉜다.
글로벌 범위의 시스템 변수 (Global Variable)
•
하나의 MySQL 서버 인스턴스에서 전체적으로 영향을 미치는 시스템 변수
•
주로 MySQL 서버 자체에 관련된 설정
•
ex) MySQL 서버에 단 하나만 존재하는 InnoDB 버퍼 풀 크기(innodb_buffer_pool_size)
•
ex) MYISAM의 키 크기 (key_buffer_size)
세션 범위의 시스템 변수 (Session Variable)
•
MySQL 클라이언트가 MySQL 서버에 접속할 때 기본적으로 부여하는 옵션의 기본값(글로벌 변수)을 제어하는 데 사용
•
개별 컨넥션 단위로 다른 값으로 변경할 수 있는 것이 세션 변수
•
기본값은 글로벌 시스템 변수이며, 각 클라이언트가 가지는 값이 세션 변수이다.
•
ex) autocommit을 글로벌 변수에서 ON으로 설정해 두면 해당 서버에 접속하는 모든 컨넥션은 기본으로 자동 커밋 모드로 시작되지만 각 커넥션에서 autocommit 변수의 값을 OFF로 변경해 자동 커밋 모드를 비활성화할 수도 있다.
•
세션 변수는 커넥션별로 설정값을 서로 다르게 지정할 수 있다.
•
한번 연결된 커넥션의 세션 변수는 서버에서 강제로 변경할 수 없다.
Both Variable
•
세션 범위의 시스템 변수 가운데 MySQL 서버의 설정 파일(my.cnf)에 명시해 초기화할 수 있는 변수는 대부분 범위가 ‘Both’
•
Both로 명시된 시스템 변수는 MySQL 서버가 기억만 하고 있다가 실제로 클라이언트와 커넥션이 생성되는 순간에 해당 커넥션의 기본값으로 사용
•
순수하게 Session 범위의 시스템 변수는 MySQL 서버의 설정 파일에 초기값을 명시할 수 없으며, 커넥션이 만들어지는 순간부터 해당 커넥션에서만 유효한 설정 변수를 의미
정적 변수와 동적 변수
MySQL 서버가 기동 중인 상태에서 변경 가능한지에 따라 ‘동적 변수’와 ‘정적 변수’로 구분
1.
MySQL 서버의 시스템 변수는 디스크에 저장돼 있는 설정 파일(my.cnf)을 변경하는 경우
•
MySQL 서버가 재시작되기 전에는 적용되지 않는다
2.
이미 기동 중인 MySQL 서버의 메모리에 있는 시스템 변수를 변경하는 경우
•
SET 명령을 이용해 값을 변경 가능
mysql> SHOW GLOBAL VARIABLES LIKE '%max_connections%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| max_connections | 151 |
| mysqlx_max_connections | 100 |
+------------------------+-------+
mysql> SET GLOBAL max_connections=500;
mysql> SHOW GLOBAL VARIABLES LIKE '%max_connections%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| max_connections | 500 |
| mysqlx_max_connections | 100 |
+------------------------+-------+
SQL
복사
•
SET 명령을 통해 변경되는 시스템 변수값은 MySQL 설정 파일(my.cnf)에 반영되는 것은 아니기 때문에 현재 기동중인 MySQL 인스턴스에만 유효하다.
•
MySQL 서버가 재시작되면 다시 설정 파일의 내용이 초기화 되기 때문에 영구적으로 반영하기 위해서는 my.cnf 파일도 변경해야 한다.
•
MySQL 8.0부터는 SET PERSIST 명령을 이용하면 실행 중인 MySQL 서버의 시스템 변수를 변경함과 동시에 자동으로 설정 파일로도 기록된다.
•
SET PERSIST 명령을 사용하는 경우 변경된 시스템 변수는 my.cnf 파일이 아닌 별도의 파일에 기록된다.
변경하려는 값이 동적 변수라면 SET 명령으로 변수값을 변경할 수 있으며 굳이 MySQL 서버를 재시작하지 않아도 된다. (정적 변수는 실행중에 변경이 불가능하다)
재시작 후에도 설정을 변경하려면 SET PERSIST를 이용해야 한다.
시스템 변수 범위가 ‘Both’인 경우 글로벌 시스템 변수의 값을 변경해도 이미 존재하는 커넥션의 세션 변수값은 변경되지 않고 그대로 유지된다.
SET 명령은 2*1024*1024 수식 사용 ⭕️, MB나 GB 표기법
SET PERSIST
SET PERSIST 명령으로 시스템 변수를 변경하면 MySQL 서버는 변경된 값을 즉시 적용함과 동시에 별도의 설정 파일 (mysqld-auto.cnf)에 변경 내용을 추가로 기록해 둔다.
그리고 MySQL 서버가 다시 시작될 때 기본 설정 파일(my.cnf)와 함께 자동 생성된 mysqld-auto.cnf 파일을 같이 참조해서 시스템 변수를 적용한다.
SET PERSIST 명령은 세션 변수에는 적용되지 않으며, SET PERSIST 명령으로 시스템 변수를 변경하면 MySQL 서버는 자동으로 GLOBAL 시스템 변수의 변경으로 인식하고 변경한다.
SET_PERSIST_ONLY
•
현재 실행 중인 MySQL 서버에는 변경 내용을 적용하지 않고 다음 재시작을 위해 mysqld-auto.cnf 파일에만 변경 내용을 기록
•
정적인 변수 값을 영구적으로 변경하고자 할 때도 사용 (정적인 변수는 SET, SET PERSIST로 변경이 불가능)
RESET PERSIST
•
mysqld-auto.cnf 파일의 내용을 삭제
# 특정 시스템 변수만 삭제
mysql> RESET PERSIST max_connections;
mysql> RESET PERSIST IF EXISTS max_connections;
# mysqld-auto.cnf 파일의 모든 시스템 변수 삭제
mysql> RESET PERSIST;
SQL
복사
my.cnf 파일
MySQL 8.0 서버의 시스템 변수는 대략 570개 수준이다.