1. MySQL 모든 시스템 변수 확인
MySQL 서버의 실행 방식 및 제어 설정을 위한 별도의 값을 시스템 변수(System Variables)라 부른다.
mysql> SHOW GLOBAL VARIABLES;
2. MySQL 서버 관련
2.1 MySQL 서버 종료
서버에서 직접 종료
linux> systemctl stop mysqldMySQL에서 원격으로 종료
mysql> SHUTDOWN;
- 원격 종료에는 SHUTDOWN 권한(Privileges)이 있어야 한다.
- 위 두 명령어의 실행 결과는 동일하다.
2.2 MySQL 서버 클린 종료
MySQL 서버 종료 시 모든 커밋 내용을 데이터 파일에 적용하고 종료할 수 있게 해주려면 innodb_fast_shutdown을 0으로 설정한 뒤 종료해야 한다. 이를 클린 셧다운(Clean shutdown)이라 한다.
클린 셧다운을 할 때는 종료 시간이 오래 걸리지만, 반대로 MySQL을 재기동할 때 별도의 트랜잭션 복구 과정이 없어서 빠르게 시작할 수 있다.
클린 셧다운은 백업, 업그레이드, 마이그레이션, 서버 이전 등 데이터의 안정성을 요구할 때 주로 사용된다.
mysql> SET GLOBAL innodb_fast_shutdown=0;linux> systemctl stop mysqld
3. MySQL 권한 관련
3.1 전체 사용자에게 스토어드 함수 생성 권한 부여
mysql> SET GLOBAL log_bin_trust_function_creators = 1;
- 각 사용자에게 권한을 부여하려면
GRANT문을 사용해야 한다.
4. MySQL 계정 관련
4.1 비밀번호 내역 확인 명령어
비밀번호 재사용 및 이전 사용 기록을 확인하는 명령어다.
mysql> select * from mysql.password_history;
4.2 사용자 비밀번호 생성 규칙 확인
비밀번호 생성 규칙(비밀번호 길이, 대/소문자 규정 등)을 확인 할 수 있다.
mysql> show variables like 'validate_password%';
4.3 사용자 비밀번호 생성 규칙 변경
비밀번호 생성 규칙을 설정할 수 있다.
# ex) 비밀번호 규칙 수준 설정 ## 비밀번호 규칙 LOW로 변경 mysql> set global validate_password.policy=LOW;
5. MySQL 아키텍처 관련
5.1 MySQL 스토리지 엔진 확인
mysql> SHOW ENGINES;
- mysql 8.0 이후부터 대부분 InnoDB를 많이 사용한다.
5.2 MySQL 플러그인 확인
mysql> SHOW PLUGINS;
5.3 테이블 외래 키 관계 설정
InnoDB에서 외래 키 관계 조건 비활성화
외래 키 관계를 비활성화하는 것은 일시적으로 사용하는 게 좋다. 만약 부모 테이블의 레코드를 삭제하면 반드시 자식 테이블의 관련 레코드도 삭제해야 한다.
즉, 데이터의 일관성을 맞춰야 한다.
foreign_key_checks는 보통 SESSION 단위로 설정한다. GLOBAL 단위로 설정할 경우 데이터 일관성에 큰 문제가 발생할 수 있기 때문이다.# 외래 키 관계 비활성화 mysql> SET foreign_key_checks=OFF; ... 작업 ... # 외래 키 관계 활성화 mysql> SET foreign_key_checks=ON;
5.4 MVCC (격리 수준 설정) ⭐️
MySQL InnoDB에서 트렌젝션 격리 수준은transaction_isolation 시스템 변수를 통해 설정한다.
격리 수준은 아래와 같다.
READ-UNCOMMITTED(수준 낮음) > READ-COMMITTED > REPEATABLE-READ(기본 값) > SERIALIZABLE(수준 높음)
# 격리 수준 확인 mysql> SHOW VARIABLES LIKE 'transaction_isolation'; # 격리 수준 설정 mysql> SET GLOBAL transaction_isolation = <격리수준>;※ 참고
READ-UNCOMMITTED: 99.9% 사용 X (데이터의 일관성이 깨짐)READ-COMMITTED: Oracle, MSSQL, PGSQL 등 대부분의 DB에서 사용REPEATABLE-READ: MySQL의 기본값SERIALIZABLE: 동시성 문제를 완벽하게 커버 하지만, 순차적으로 트랜잭션을 처리하기 때문에 동시성 속도 문제가 있다.특별한 경우가 아니라면 그냥 기본 격리 수준인
REPEATABLE-READ을 사용하는 게 제일 좋다. (미국 석박사 엔지니어분들이 연구/개발/결정 했다. 그냥 믿고 쓰자!!)만약 동시성 트래픽 처리(특히 쓰기 작업)가 많을 때는
READ-COMMITTED를 고려해볼 만하다. 동시성 트래픽이 많은 환경에서는 약 10~30%의 속도를 높일 수 있다.
5.5 InnoDB 버퍼 풀 사이즈 설정 ⭐️
MySQL InnoDB엔진의 버퍼 풀 사이즈를 설정하는 시스템 변수는 innodb_buffer_pool_size이다.
# 버퍼 풀 사이즈 확인 mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; # 또 다른 확인 방법 SELECT (@@innodb_buffer_pool_size/1024/1024) AS InnoDB_버퍼풀_크기_MB; # 설정 mysql> SET innodb_buffer_pool_size = <설정값>;
7. MySQL 시간 관련
7.1 LOG 타임스탬프 시간 설정
MySQL 서버는 기본적으로 UTC(협정 세계 시간) 시간의 타임스탬프를 사용한다.
이를 MySQL 서버 PC(OS 기준)의 시간으로 변경하기 위해서는 log_timestamps 설정값을 SYSTEM으로 설정해야 한다. (기본값 : UTC)
mysql> set global log_timestamps = 'SYSTEM';
7.2 쿼리 결과 시간 설정
쿼리 결과의 시간(NOW(), CURRENT_TIMESTAMP())은 별도의 시스템 변수로 설정해야 한다. (기본값 : SYSTEM)
# 한국 시간을 기준으로 설정 mysql> SET time_zone = '+09:00';
8. MySQL 인덱스 관련 ⭐️
8.1 쿼리 요청 확인
아래는 스토리지 엔진에 보낸 “핸들러 요청” 카운터를 확인하는 명령어다.
이 중 인덱스 사용과 관련된 변수는 다음과 같다.
- Handler_read_first : 인덱스의 첫 번째 레코드를 읽은 횟수
- Handler_read_key : 인덱스 키로 레코드를 찾은 횟수(해당 값이 높을수록 인덱스를 잘 활용한다는 뜻)
- Handler_read_next : 인덱스 순회로 다음 엔트리를 읽은 횟수(해당 값이 높을수록 인덱스 레인지 스캔이 많음을 의미)
- Handler_read_rnd_next : 데이터 파일을 순회하면서 다음 행 읽는 횟수(풀 테이블 스캔일 때 해당 값이 상승)
mysql> show status like 'Handler_%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Handler_commit | 1 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_external_lock | 2 | | Handler_mrr_init | 0 | | Handler_prepare | 0 | | Handler_read_first | 1 | | Handler_read_key | 1 | | Handler_read_last | 0 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 8 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 0 | +----------------------------+-------+ 18 rows in set (0.00 sec)
9. MySQL 성능 관련
9.1 버퍼 풀 히트율 확인 ⭐️
MySQL 버퍼 풀의 히트율은 약 95% 이상을 목표로 하는 것이 좋다.
- 버퍼 풀 히트율 : 디스크가 아닌 메모리에서 데이터를 읽히는지 확인하는 수치
WITH tb_buffer_pool_reads as ( # 디스크 I/O 발생 건수
SELECT VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
), tb_buffer_pool_read_requests as ( # 디스크 I/O + 메모리 I/O 발생 건수 (전체 읽기 요청 수)
SELECT VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
)
SELECT
CONCAT(ROUND((1 - (A.VARIABLE_VALUE / B.VARIABLE_VALUE)) * 100, 2), '%') AS 버퍼풀_히트율
FROM tb_buffer_pool_reads A, tb_buffer_pool_read_requests B10. MySQL 접속 관련
10.1 최대 커넥션 가능 설정
# 최대 커넥션 가능한 값 확인 mysql> SHOW VARIABLES LIKE 'max_connections'; # 최대 커넥션 가능한 값 설정 mysql> SET max_connections = <설정값>;
10.2 현재 접속한 클라이언트 수 확인
# 현재 접속한 클라이언트 수 확인 mysql> SHOW STATUS LIKE 'Theeads_connected';
10.3 MySQL 서버 구동 시간
# MySQL 서버 구동 시간(초 단위) mysql> SHOW STATUS LIKE 'Uptime';
11. MySQL 로그 관련
11.1 슬로우 쿼리
11.1.1 슬로우 쿼리 ON/OFF 설정
# 슬로우 쿼리 저장 여부 확인 mysql> SHOW VARIABLES LIKE 'slow_query_log'; # 슬로우 쿼리 저장 허용(ON) mysql> SET slow_query_log = ON; # 슬로우 쿼리 저장 허용(OFF) mysql> SET slow_query_log = OFF;
11.1.2 슬로우 쿼리 기준 설정
슬로우 쿼리는 시간(초)을 기준으로 설정한다.
즉, n초 이상 소요된 쿼리문을 슬로우 쿼리문으로 지정할 수있다.
# 슬로우 쿼리 기준(초 시간) 확인 mysql> SHOW VARIABLES LIKE 'long_query_time'; # 5초 이상 쿼리문을 슬로우 쿼리로 지정 mysql> SET long_query_time = 5;
11.1.3 슬로우 쿼리 파일 위치
# 슬로우 쿼리 로그 파일 위치 확인 mysql> SHOW VARIABLES LIKE 'slow_query_log_file';
99. MySQL 기타 설정 관련
99.1 SQL 업데이트(UPDATE, DELETE) 안전 모드
시스템 변수 SQL_SAFE_UPDATES을 활성화(1, ON)하면, update와 delete 쿼리문에서 조건(where) 누락 또는 키값이 아닌 다른 컬럼으로 조건 작성 시 해당 쿼리문 실행을 막는다.
# 활성화 mysql> SET SQL_SAFE_UPDATES=ON; mysql> SET SQL_SAFE_UPDATES=1; # 비활성화 mysql> SET SQL_SAFE_UPDATES=OFF; mysql> SET SQL_SAFE_UPDATES=0;
