MySQL 소개 (1장)

MySQL은 오픈 소스 관계형 데이터베이스 관리 시스템(RDBMS)으로, 세계적으로 가장 많이 사용되는 데이터베이스 중 하나입니다. MySQL은 다중 사용자, 다중 스레드를 지원하며, 다양한 운영 체제에서 사용할 수 있습니다. 또한 MySQL은 다양한 프로그래밍 언어를 지원하며, 다양한 클라이언트 프로그램과 연동할 수 있습니다.

MySQL은 엔터프라이즈 에디션과 MySQL 커뮤니티 에디션으로 두 가지가 있습니다.

별도의 라이선스를 구매하지 않아도 무료로 사용할 수 있는 MySQL 커뮤니티 에디션은 오픈 소스로 제공되며, 소스 코드를 수정하여 자신의 프로그램에 적용할 수 있습니다. MySQL 커뮤니티 에디션은 MySQL 데이터베이스 서버, MySQL 클라이언트 프로그램, MySQL 워크벤치, MySQL 샘플 데이터베이스 등을 포함하고 있습니다.

MySQL 5.5 버전 이하에서는 두 에디션의 차이가 얼마나 자주 패치 버전이 릴리스되느냐 정도였습니다.

2011년 2월 MySQL 5.5 GA 버전부터는 엔터프라이즈 에디션의 소스코드가 공개되지 않고, 커뮤니티 에디션의 소스코드만 공개되었습니다.

Why MySQL?

DBMS를 비교할 때 MySQL은 다음과 같은 이유로 선택되는 경우가 많습니다:

DBMS 선택할 때 고려사항

  1. 안정성
  2. 성능과 기능
  3. 커뮤니티나 인지도

JetBrains 2024 설문조사

MySQL 서버 설치 (2장)

MySQL 서버는 다음과 같이 다양한 형태로 설치할 수 있다.

  1. Tar, Zip
  2. RPM(msi, dmg)
  3. 소스코드 빌드

버전과 에디션 선택

MySQL 서버의 버전을 선택할 때는 다른 제약사항이 없다.

따라서 가능한 최신 버전을 설치하는 것이 좋다.

막 출시된 메이저 버전은 안정성이 떨어질 수 있으므로, 최신 버전 중 최소 패치 버전이 15~20 이상 릴리즈된 버전을 설치하는 것이 안정적이다.

단, 새로 설치하는 환경이라면 5.7버전 보다는 8.0 이상 버전을 설치하는 것이 좋다.

RDS End of Life

엔터프라이즈 에디션에서만 지원되는 기능

  • Thread Pool
  • Enterprise Audit
  • Enterprise TDE
  • Enterprise Firewall
  • Enterprise Monitor
  • Enterprise Backup
  • MySQL 기술 지원

시작과 종료

시작

$ sudo systemctl start mysqld

종료

$ sudo systemctl stop mysqld
  • MySQL 서버를 종료하면, MySQL 서버가 사용하던 모든 리소스가 해제된다.
    • 서버가 종료될 때 모든 트랜잭션 커밋을 반영하고 종료 -> 클린 셧다운
    • 트랜잭션 복구 과정을 안거쳐도 됨.
    • 참고
  • mysqld_safe를 사용하면, MySQL 서버가 비정상적으로 종료되었을 때 자동으로 재시작한다.
SET GLOBAL innodb_fast_shutdown = 0;

재시작

$ sudo systemctl restart mysqld

상태 확인

$ sudo systemctl status mysqld

서버 연결

로컬 연결

$ mysql -u root -p --host=127.0.0.1 --port=3306

host를 지정하지 않으면 localhost로 연결된다.
이 경우에는 소켓파일을 사용하게 되며, 소켓 파일의 위치는 MySQL 설정 파일에 의해 결정된다.

데이터베이스 목록 확인

SHOW DATABASES;

MySQL 서버 설정

일반적으로 MySQL 서버는 하나의 설정 파일을 사용한다.

  • 리눅스
    • /etc/my.cnf
  • 윈도우
    • C:\ProgramData\MySQL\MySQL Server 8.0\my.ini
  • macOS
    • /usr/local/mysql/my.cnf

설정 파일 위치 확인

$ mysql --help

시스템 변수

    • MySQL 서버는 기동하면서 설정 파일의 내용을 읽어 메모리나 작동 방식을 초기화
    • 설정 파일에는 MySQL 서버의 동작을 제어하는 여러 가지 시스템 변수들이 정의되어 있다.
    •  

MySQL 서버에서는 위와 같이 저장된 값을 시스템 변수라고 한다. (system variable)

시스템 변수 확인

SHOW VARIABLES;

시스템 변수 변경

SET GLOBAL 변수명 = 값;

시스템 변수가 가지는 속성의 의미

  • Cmd-line
    • 서버의 명령행 인자로 설정될 수 있는지 여부 (YES/NO)
  • Option file
    • my.cnf 파일로 제어할 수 있는지 여부
  • System Var
    • 시스템 변수인지 여부
    • 네이밍은 하이픈 (-)로 구분되는 곳도 있고 언더스코어(_)로 구분되는 곳도 있다.
    • 가능한 언더스코어로 구분하는 것이 좋다.
    • MySQL 8.0부터는 모두 언더스코어로 구분한다.
      • 단, 명령행 옵션으로만 사용가능한 설정들은 하이픈으로 구분되어 있다.
  • Var Scope
    • GLOBAL: 전역 변수
    • SESSION: 세션 변수
    • BOTH: GLOBAL과 SESSION 모두 적용
  • Dynamic
    • 동적으로 변경 가능한지 여부

글로벌 변수와 세션 변수

  • 글로벌 범위의 시스템 변수는 하나의 MySQL 서버 인스턴스에서 전체적으로 영향을 미치는 시스템 변수
    • 주로 MySQL 서버의 전체적인 동작을 제어하는 데 사용
    • ex) innodb_buffer_pool_size, key_buffer_size
  • 세션 범위의 시스템 변수는 클라이언트의 필요에 따라 개별 커넥션 단위로 다른 값으로 변경할 수 있는 변수
    • ex) sql_mode, autocommit
  • 글로벌 변수와 세션 변수는 동일한 이름을 가질 수 있으나, 서로 다른 값을 가질 수 있다.
    • MySQL 서버가 기억하고 있다가 클라이언트와 커넥션이 생성되는 순간 해당 커넥션의 기본값으로 사용

정적 변수와 동적 변수

  • 정적 변수는 MySQL 서버가 기동될 때 설정 파일에 정의된 값으로 초기화되며, 이후 변경되지 않는 변수
  • 동적 변수는 MySQL 서버가 기동된 이후에도 변경할 수 있는 변수
    • SET GLOBAL 명령을 사용하여 변경 가능
    • SET PERSIST 명령을 사용하여 설정 파일에 저장할 수 있음
    • SET PERSIST_ONLY 명령을 사용하여 설정 파일에 저장하지 않고 현재 세션에만 적용할 수 있음
  • MySQL 8.0부터는 SET PERSIST 명령을 사용하여 설정 파일에 저장할 수 있음

SET PERSIST 명령

SET PERSIST 변수명 = 값;

ex

SET PERSIST max_connections = 1000;

SET PERSIST_ONLY 명령

SET PERSIST_ONLY 변수명 = 값;

ex

SET PERSIST_ONLY max_connections = 1000;
  • SET PERSIST
    • 현재 세션과 MySQL 서버 재시작 후에도 적용
    • 단, 세션 변수에는 적용되지 않으며, 시스템 명령으로 시스템 변수를 변경하면 MySQL 서버는 자동으로 GLOBAL 시스템 변수의 변경으로 인식하고 변경
  • SET PERSIST_ONLY
    • 현재 실행중인 서버에는 적용하지 않고 설정 파일에만 적용
    • 정적인 변수의 값을 영구적으로 변경하고자 할 때 사용 가능
    • 정적인 변수는 실행 중인 서버에는 변경할 수 없다 ex) innodb_doublewrite

RDS에서는 파라미터 그룹을 통해 변경할 수 있다!

참고

사용자 권한 (3장)

MySQL에서 사용자 계정을 생성하는 방법이나 각 계정 권한 부여에는 다른 DBMS와 차이가 있다.
대표적으로 단순 아이디 뿐 아니라 어느 IP에 접속하고 있는지도 체크한다.

또한 8.0 버전부터는 권한을 묶어서 관리하는 Role 개념이 추가되었다.

사용자 식별

MySQL에서 계정을 언급할 때는 다음과 같이 아이디와 호스트를 함께 명시해야한다.

'아이디'@'호스트'

만약 모든 외부 컴퓨터에서 접속 가능하게끔 하고 싶다면 호스트 부분을 '%'로 대체하면 된다.

'아이디'@'%'

만약 서로 동일한 아이디가 있을 경우 호스트로 구분한다.

'아이디'@'192.168.0.10'
'아이디'@'%'

위와 같이 설정되어있다면 192.168.0.10 PC에서는 어떤 계정을 선택할까?

MySQL 에서는 범위가 가장 작은 것을 먼저 선택한다!
-> 첫 번쨰 계정 선택

시스템 계정과 일반 계정

MySQL에서는 시스템 계정과 일반 계정을 구분한다.

8.0 버전부터 계정은 SYSTEM_USER 권한을 가지고 있는지 여부에 따라 시스템 계정과 일반 계정으로 구분된다.

시스템 계정은 다음과 같은 특징을 가진다.

  • 계정 관리(생성, 삭제, 권한 부여)를 할 수 있는 권한을 가지고 있다.
  • 다른 세션또는 세션에서 실행 중인 쿼리를 강제 종료
  • 스토어드 프로그램 생성 시 DEFINER를 타 사용자로 설정

계정 생성

5.7버전까지는 GRANT 명령어를 사용하여 계정을 생성하고 권한을 부여했다.
하지만 8.0 버전부터는 CREATE USER 명령어를 사용하여 계정을 생성하고 GRANT 명령으로 권한을 부여한다.

계정을 생성할 때 다양한 옵션을 설정할 수 있다.

  • 계정 인증 방식과 비밀번호
  • 비밀번호 관련 옵션
    • 비밀번호 만료 기간
    • 비밀번호 재사용 제한
    • 비밀번호 이력 개수
  • 기본 역할
  • SSL 옵션
  • 계정 잠금 여부
CREATE USER '아이디'@'호스트' 
    IDENTIFIED WITH 'mysql_native_password' BY '비밀번호'
    REQUIRE NONE
    PASSWORD EXPIRE INTERVAL 180 DAY
    ACCOUNT UNLOCK
    PASSWORD HISTORY 3
    PASSWORD REUSE INTERVAL DEFAULT
    PASSWORD REQUIRE CURRENT DEFAULT;

IDENTIFIED WITH

  • 사용자 인증 방식과 비밀번호 설정
  • IDENTIFIED WITH 뒤에는 반드시 인증 방식 명시
    • Native Pluggable Authentication
      • 5.7 버전까지 기본 방식 (SHA-1)
    • Caching SHA-2 Pluggable Authentication
      • 5.6 버전부터 추가된 방식 (SHA-256)
    • PAM Pluggable Authentication
      • 유닉스 패스워드 또는 LDAP와 같은 인증 시스템과 연동
      • 엔터프라이즈 에디션에서만 사용 가능
    • LDAP Pluggable Authentication
      • LDAP 서버와 연동
      • 엔터프라이즈 에디션에서만 사용 가능

REQUIRE

SSL/TLS 연결을 요구하는지 여부를 설정한다.

PASSWORD EXPIRE

비밀번호 만료 기간을 설정한다.

  • PASSWORD EXPIRE
    • 계정 생성과 동시에 비밀번호 만료 처리
  • PASSWORD EXPIRE INTERVAL n DAY
    • n일 후 비밀번호 만료 처리
  • PASSWORD EXPIRE NEVER
    • 비밀번호 만료 처리하지 않음
  • PASSWORD EXPIRE DEFAULT
    • 기본 설정 사용
    • default_password_lifetime 시스템 변수로 설정 가능
    • default value is 0

PASSWORD HISTORY

비밀번호 재사용 제한을 설정한다.

  • PASSWORD HISTORY n
    • n개의 이전 비밀번호를 재사용할 수 없음
  • PASSWORD HISTORY DEFAULT
    • 기본 설정 사용
    • password_history 시스템 변수에 저장된 개수만큼 이력을 저장하며 이력에 남아있는 비밀번호는 사용 불가

PASSWORD REUSE INTERVAL

비밀번호 재사용 제한 기간을 설정한다.

  • PASSWORD REUSE INTERVAL n DAY
    • n일 후에 재사용 가능
  • PASSWORD REUSE INTERVAL DEFAULT
    • 기본 설정 사용
    • password_reuse_interval 시스템 변수에 저장된 기간만큼 재사용 불가
    • default value is 0

PASSWORD REQUIRE

비밀번호가 만료되어 새로운 것으로 변경할 때 현재 비밀번호를 필요로 할지 말지 설정한다.

  • PASSWORD REQUIRE CURRENT
    • 현재 비밀번호를 입력해야 새로운 비밀번호로 변경 가능
  • PASSWORD REQUIRE OPTIONAL
    • 현재 비밀번호를 입력하지 않아도 새로운 비밀번호로 변경 가능
  • PASSWORD REQUIRE DEFAULT
    • 기본 설정 사용
    • password_require_current 시스템 변수에 저장된 설정을 따름
    • default value is OFF

권한

MySQL에서는 다양한 권한을 부여할 수 있다.

글로벌 권한과 객체 권한으로 분류할 수 있다. docs

글로벌 권한

  • 데이터베이스나 테이블 이외의 객체에 적용되는 권한
  • GRANT 명령에서 특정 객체를 명시하면 안 된다.
    • 예외로 ALL(ALL PRIVILEGES)은 글로벌과 객체 권한 두 가지 용도로 사용 가능
  • 특정 객체에 ALL 권한이 부여되면, 해당 객체에 적용될 수 있는 모든 객체 권한 부여
  • 글로벌로 ALL이 사용되면 글로벌 수준에서 가능한 모든 권한 부여

객체 권한

  • 객체 권한이란 데이터베이스나 테이블을 제어하는데 필요한 권한
  • GRANT 명령으로 권한을 부여할 때 반드시 객체를 명시해야 한다.
GRANT privilege_list ON db.table TO 'user'@'host';

글로벌 권한

GRANT SUPER ON *.* TO 'user'@'localhost';

글로벌 권한은 특정 DB나 테이블에 부여될 수 없기에 ON 절에 .을 사용한다.
CREATE USER, CREATE ROLE과 같은 글로벌 권한은 모든 DB와 테이블에 적용되기 때문에 .을 사용한다.

DB 권한

GRANT EVENT ON *.* TO 'user'@'localhost';
GRANT EVENT ON employees.* TO 'user'@'localhost';

DB 권한은 특정 DB에 대해서만 권한 부여 혹은 서버에 존재하는 모든 DB에 부여할 수 있기에 . 또는 employees.*를 모두 사용할 수 있다.

테이블 권한

GRANT SELECT ON *.* TO 'user'@'localhost';
GRANT SELECT ON employees.* TO 'user'@'localhost';
GRANT SELECT ON employees.employees TO 'user'@'localhost';

역할

8.0 버전부터 권한을 묶어서 관리하는 Role 개념이 추가되었다.

CREATE ROLE 'role_emp_read', 'role_emp_write';
GRANT SELECT ON employees.* TO 'role_emp_read';
GRANT INSERT, UPDATE, DELETE ON employees.* TO 'role_emp_write';

예제로 계정 생성 이후 권한을 부여하는 과정을 보자.

CREATE USER reader@'127.0.0.1' IDENTIFIED BY 'password';
CREATE USER writer@'127.0.0.1' IDENTIFIED BY 'password';

reader, writer 계정에 역할 부여

GRANT role_emp_read TO reader@'127.0.0.1';
GRANT role_emp_write TO writer@'127.0.0.1';

역할을 활성화 시키려면 SET ROLE 명령어를 사용한다.

SET ROLE 'role_emp_read';
SET ROLE 'role_emp_write';

로그아웃 이후 다시 로그인하면 역할이 해제된다.

SET GLOBAL activate_all_roles_on_login = ON;

역할 생성 시 호스트를 명시할 수 있다.

CREATE ROLE role_emp_local_read@localhost;
CREATE USER reader@localhost IDENTIFIED BY 'password';
GRANT SELECT ON employees.* TO role_emp_local_read@localhost;
GRANT role_emp_local_read@localhost TO reader@localhost;

해당 예제는 role_emp_local_read 역할을 localhost에서만 사용할 수 있도록 설정했다.
역할과 계정의 호스트 부분이 서로 달라서 호환되지 않는데 어떻게 될까?

예제와 같이 역할과 계정을 생성하면 사용자 계정은 employees DB의 테이블을 읽을 수 있다.
즉, 호스트 부분은 아무런 영향이 없다. 만약 역할을 다른 계정에 부여하지 않고 직접 로그인 하는 용도로 사용한다면 호스트 부분이 중요해진다.

티스토리툴바