달력

32024  이전 다음

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31

4장 데이터베이스 관리

전체적으로 볼 때 MySQL은 그다지 관리할 것이 많지 않은 편이다. 일단 설치하고 나면 관리자가 할 일이 별로 없다. 그렇다고 해도 MySQL 관리자는 다음과 같은 임무를 맡고 있다.

  • 설치
  • 설정 및 튜닝
  • 액세스 제어
  • 로깅
  • 백업 및 복구
  • 테이블 관리

위에 열거한 주제 중 몇 가지는 다른 자에서 다룬다(설치는 2장에서, 퍼포먼스 튜닝은 5장에서, 액세스 제어는 6장에서).

MySQL 서버 관리를 할 필요가 없는 사람이라도 이 장에 있는 내용을 알아두면 도움이 된다. 데이터베이스 관리에 대한 내용을 알고 있으면 데이터베이스 관리자에게 문의하기 전에 어떤 문제가 있는지 확인해 볼 수 있기 때문이다.

여러 가지 관리 작업을 처리하려면 MySQL의 관리자 액세스가 필요하다(액세스 권한과 MySQL 데이터베이스 관리자 설정에 관한 내용은 6장에서 자세히 다룬다). 작업에 따라 운영 체제의 관리자 권한(유닉스에서는 root, 윈도우 NT/2000/XP에서는 Administrator)이 필요할 수도 있다.

설정

우선 MySQL 서버 프로세스인 mysqld 및 mysql 명령행 유틸리티를 비롯한 여러 클라이언트 프로세스에 대한 설정을 해야 한다. MySQL 설정은 유닉스에서 흔히 사용하는 방법으로 이루어진다. 즉 명령행 옵션, 설정 파일 그리고 환경 변수를 이용하여 설정할 수 있다. 이러한 세 가지 방법을 통해 모든 설정 가능한 내역을 관리할 수 있다.

옵션을 정의하는 방법이 다양하기 때문에 위의 세 가지 옵션들이 서로 충돌할 때에는 다음과 같은 우선순위를 기준으로 삼는다.

  1. 명령행 옵션
  2. 설정 파일 옵션
  3. 환경 변수 옵션

예를 들어, 패스워드 옵션이 명령행, 설정 파일, 환경 변수에서 제각기 다르게 정의되어 있다면 MySQL 클라이언트 도구에서는 명령행에서 지정한 옵션을 사용한다.

MySQL 옵션을 다루는 방법 중에서는 설정 파일을 이용하는 방법이 가장 쉽고 많이 쓰인다. 설정 파일을 이용하면 모든 옵션을 파일 하나에 넣을 수 있기 때문에 명령을 실행시키거나 컴퓨터에 로그인할 때마다 옵션을 지정하지 않아도 된다.

파일 위치

유닉스 시스템에서는 MySQL 설정 파일을 다음과 같은 위치에서 순서대로 검색한다.

  1. /etc/my.cnf 파일. MySQL에서는 가장 먼저 전역 옵션 파일을 검색한다. 일반적으로 모든 사용자와 서버에서 사용하는 기본 옵션은 이 파일에 저장해 두는 편이 좋다.
  2. DATADIR/my.cnf 파일. DATADIR은 MySQL 서버 인스턴스의 데이터파일을 저장하는 디렉토리이다. 이 설정 파일에는 주어진 서버 인스턴스에만 적용되는 설정 인자를 저장한다.
  3. --default-extra-file=filename 명령행 옵션을 통해 지정한 위치. 이 명령행 옵션을 이용하면 MySQL 서버나 클라이언트 유틸리티에서 임의의 위치에 있는 설정 파일을 이용할 수 있다.
  4. $HOME/.my.cnf 파일. $HOME은 현재 사용자의 홈 디렉토리를 나타내는 유닉스 환경 변수이다. 이 설정 파일은 사용자의 홈 디렉토리에 저장되며 여기에는 사용자들이 개인적으로 사용하는 옵션을 저장해둘 수 있다. 클라이언트 옵션은 대부분 여기에 저장된다.

윈도우에는 전역 설정 파일이 추가로 있으며, 사용자별 개인 설정 파일은 없다.

  1. 윈도우 시스템 폴더(보통 C:\WINNT\System32)에 들어가는 My.ini 파일
  2. C:\my.cnf
  3. C:\mysql/data/my.cnf
  4. --default-extra-file=filename

만약 어떤 옵션이 여러 파일에서 서로 다르게 정의되었다면 가장 나중에 읽어들인 내용이 적용된다. 즉 C:\my.cnf 파일에 저장된 사용자 정의 옵션이 My.ini 파일에 있는 옵션보다 더 우선시된다. 이렇게 하면 데이터베이스 관리자는 클라이언트 도구의 기본값을 My.ini를 통해 설정하고 사용자는 필요에 따라 다른 옵션을 사용할 수 있다.

파일 내용

모든 설정 파일의 형식은 같다. 설정 파일의 예가 [예제 4-1]에 나와 있다. 설정 파일이 그다지 어렵진 않지만, 일단 이 파일을 꼼꼼하게 살펴보자:

 [예제 4-1] MySQL 설정 파일 샘플 # MySQL 설정파일 예제 # # 클라이언트 옵션 [client] password = my_password port     = 3306 socket   = /var/lib/mysql/mysql.sock # mysqld 서버 옵션 [mysqld] port   = 3306 socket = /var/lib/mysql/mysql.sock skip-locking set-variable   = max_allowed_packet=1M

#으로 시작하는 맨 처음 세 줄은 주석이다. MySQL 설정 파일 형식에서는 #와 ; 기호로 주석을 표시할 수 있다. 샵(#)이나 세미콜론(;)이 나타나면 주석 기호 뒤에 나오는 그 줄의 나머지 부분은 모두 무시된다.

다음 줄에는 한 섹션이 시작하는 것을 나타내는 다음과 같은 코드가 있다:

 [client]

삼바(samba)나 윈도우 INI 설정을 건드려본 경험이 있다면 위와 같은 형식이 그다지 낯설지 않을 것이다. 설정 파일은 몇 개의 섹션으로 나위고, 각 옵션은 그 섹션에만 적용된다. MySQL 설정 파일에는 client와 mysqld의 두 가지 섹션이 있다.

섹션을 표시하는 부분 다음 줄부터는 파일이 끝나기 전까지, 또는 다른 그룹이 시작되기 전까지 그 섹션에만 해당되는 옵션이 들어간다. client 섹션에는 클라이언트 도구에 필요한 세 가지 설정 내역이 들어있다. 첫 번째 옵션은 기본 패스워드를 지정하는 부분이다:

 password       = my_password

이 옵션은 --password=my_password 명령행 옵션과 같은 역할을 한다. 일반적으로 명령행 옵션에서 --option=value 형태로 지정하는 것은 MySQL 설정 파일에서 option=value 형태로 지정하는 것과 똑같다.

설정 파일에 패스워드를 넣어 두면 MySQL에 연결할 때마다 매번 패스워드를 입력할 필요가 없기 때문에 편리하다. 하지만 패스워드를 설정 파일이나 명령행에 직접 입력하는 것은 그다지 권장할 만한 방법이 아니다. 클라이언트 유틸리티에서 패스워드를 입력할 수 있는 프롬프트가 나오게 하는 것이 좋다. 설정 파일에 패스워드를 입력해 놓았을 때에는 다른 사람이 절대 그 파일을 열어볼 수 없도록 주의해야 한다.

패스워드 옵션 아래에는 있는 두 줄에서는 클라이언트 도구에서 서버에 연결할 때 사용할 포트 및 소켓 파일을 설정한다. 그 뒤에는 다음과 같은 행이 있어서 새로운 섹션이 시작된다:

 [mysqld]

이 섹션에서는 MySQL 서버 프로세스를 설정하는 옵션이 들어간다. 여기에서는 클라이언트 섹션에서 사용하는 것과 비슷한 형태 외에 두 가지 새로운 옵션 설정 방식이 등장한다. 다음과 같은 옵션에서는 값을 입력할 필요가 없다:

 skip-locking

위의 옵션은 MySQL 서버에서 시스템 잠금을 사용하지 않는다는 것을 의미하는 부울 옵션이다. 위의 옵션을 지정하지 않으면 시스템 잠금이 작동한다. 명령행에서는 --skip-locking 옵션을 사용하면 된다.

MySQL에서 옵션을 지정하는 또 다른 방법은 mysqld 변수를 직접 지정하는 방법이다:

 set-variable = max_allowed_packet=1M

명령행에서는 --set-variable max_allowed_packet=1M라고 적으면 된다. 변수는 서버와 클라이언트의 런타임 환경을 제어하는 설정값이다.

서버 시작 및 종료

MySQL 서버는 서버 프로세스 형태로 동작하므로 컴퓨터를 켤 때마다 자동으로 시작해야 한다. 또한 컴퓨터를 종료할 때 서버도 종료시켜야 한다. MySQL 서버를 자동으로 시작하고 종료시키는 방법은 운영 체제에 따라 다르다.

mysqld 명령으로 명령행에서 직접 서버를 시작하게 만들 수도 있다. 하지만 어떤 운영 체제에서든지 MySQL을 시작할 때에은 safe_mysqld를 사용하는 것이 좋다.

유닉스/리눅스

유닉스 및 리눅스와 같은 유닉스 계열 운영 체제(Mac OS X는 제외)에서 MySQL을 시작하고 끝내는 방법은 SVR4 시스템인지 아닌지에 따라 달라진다. SVR4 시스템에서는 mysql.server 스크립트를 사용하면 되고 다른 유닉스 시스템에서는 safe_mysqld를 사용하면 된다. 여기에서는 이러한 스크립트를 사용하는 대략적인 방법에 대해 설명하겠다(유닉스 시스템을 관리할 때 힘든 부분 중 하나가 바로 시스템별로 서비스를 시작하고 종료하는 방법이 조금씩 다르다는 점이다).

SVR4

SVR4 시스템에서 MySQL을 시작하고 종료할 때에는 mysql.server 스크립트를 사용한다. 이 스크립트는 보통 MySQL을 설치하고 나면 만들어지는 support.files라는 디렉토리(보통 /usr/local/mysql/support-files)에 저장된다. SVR4의 시작/종료 메커니즘은 시스템이 다른 런레벨(run level)에 들어갈 때 서비스를 시작하고, 종료하기 위한 스크립트(/etc 폴더 아래에 들어가는 몇 개의 폴더에 들어감)에 의해 운영된다.

리눅스 시스템에서 RPM 패키지로 설치했다면 mysql.server가 자동으로 설치된다. RPM 설치기에서 mysql.server 파일을 /etc/rc.d/init.d에 복사할 때 그 파일명을 mysql로 바꾼다. /etc/rc.d/init.d/mysql 파일이 있으면 MySQL이 자동으로 시작되고 종료될 것이다.

레드헷 리눅스 시스템에서 mysql.server를 설치하는 방법은 다음과 같다:

 $ cp mysql.server /etc/rc.d/init.d $ ln -s /etc/rc.d/init.d/mysql.server /etc/rc.d/rc3.d/S99mysql $ ln -s /etc/rc.d/init.d/mysql.server /etc/rc.d/rc0.d/S01mysql

첫 번째 줄에서는 mysql.server 스크립트를 초기화 스크립트가 들어가는 /etc/rc.d/init.d 디렉토리에 설치한다. 두 번째 명령에서는 시스템이 3번 런 레벨에 들어갈 때 리눅스에서 자동으로 스크립트를 실행시킬 수 있도록 링크를 만든다. 리눅스에서는 시스템이 3번 런 레벨로 들어가면 /etc/rc.d/rc3.d 디렉토리에 있는 스크립트를 실행시킨다. 3번 런 레벨은 보통 SVR4 시스템이 다중 사용자 모드로 들어간다는 것을 의미한다. 다중 사용자 모드의 런 레벨이 3번이 아니라면 그에 맞는 디렉토리에 링크를 설치해야 한다.

마지막 줄에서는 0번 런 레벨에 mysql.server 스크립트에 대한 링크를 만든다. 0번 런 레벨은 시스템을 종료시키는 런 레벨이다. 따라서 /etc/rc.d/rc0.d에 있는 스크립트는 시스템이 종료될 때 실행된다.

다른 유닉스 시스템

SVR4를 기반으로 하지 않는 유닉스 시스템에서는 safe_mysqld라는 스크립트로 MySQL을 시작한다. 이 스크립트는 MySQL 설치 디렉토리 밑에 있는 /bin 디렉토리(보통 /usr/local/mysql/bin)에서 찾을 수 있다.

safe_mysqld를 사용하고 싶다면 해당 유닉스 시스템이 시작할 때와 종료할 때 서비스를 시작하고 종료하는 방법을 알아야 한다. BSD 시스템 중에는 safe_mysqld를 호출할 수 있도록 /etc/rc.local이라는 파일을 수정해야 하는 것도 있다. 하지만 FreeBSD와 같이 최근에 나온 BSD 시스템에서는 rc.local 파일을 수정하면 안되는 경우도 있다. 예를 틀어, FreeBSD에서는 mysql.server와 같은 스크립트를 /usr/local/etc/rc.d 디렉토리에 저장하면 된다.

Mac OS X

Mac OS X에서는 유닉스 시스템에서 자동으로 서비스를 시작하는 새로운 방법을 도입하였다. 여기에서는 세 가지 서로 다른 시작 디렉토리를 사용한다.

  • /System/Library/StartupItems
  • /Library/StartupItems
  • $HOME/Library/StartupItems

./System/Library/StartupItems 디렉토리는 운영 체제 서비스를 위한 디레토리이고, $HOME/Library/StartupItems 디렉토리는 사용자 소유의 서비스를 위한 디렉토리이다. MySQL은 시스템이 부팅될 때 시작되어야 하는 일반 서비스 디렉토리인 /Library/StartupItems 디렉토리에서 시작해야 한다.

StartupItems 디렉토리에서는 각 서비스별로 디렉토리가 하나씩 있어야 하기 때문에 MySQL을 설치할 때에는 우선 /Library/StartupItems/MySQL 디렉토리를 만들어야 한다. 사실 디렉토리의 이름은 그다지 중요하지 않다. 어쨌든 그 디렉토리에는 다음과 같은 파일이 있어야 한다.

  • MySQL을 시작할 때 쓰는 유닉스 스크립트
  • StartupParameters.plist라는 이름의 시작 매개 변수 파일

유닉스 스크립트는 MySQL을 시작할 때 safe_mysqld 명령어를 호출하기 위한 간단한 스크립트이다. 이 파일명은 디렉토리의 이름과 같아야 한다(여기에는 MySQL). 스크립트는 다음과 같이 만들면 된다:

 #!/bin/sh ./etc/rc.common if ["${MYSQLSERVER:=-NO-}" = "-YES-"]; then        cd /usr/local/mysql        bin/mysqld_safe --user=mysql & fi

$MYSQLSERVER의 값을 확인하는 부분을 넣어 두면 MySQL을 사용하고 싶지 않을 때, StartupItems에서 MySQL 디렉토리를 지울 필요 없이 Mac OS X hostconfig 파일의 내용만 수정하면 된다. MySQL을 사용할 때에는 /etc/hostconfig 파일에 다음과 같은 내용을 추가하면 된다:

 MYSQLSERVER=-YES-

MySQL을 사용하지 않을 때에는 위에 있는 -YES-를 -NO-로 바꾸면 된다.

스크립트 설치를 끝내고 나면 StartupParameters.plist 파일을 만들어야 한다:

 {  Description = "MySQL Database Server";  Provides = ("MySQL");  Requires = ("Resolver");  OrderPreference = "None";  Message =  {   start = "Starting MySQL Server";   stop = "Stopping MySQL Server";  }; }

이 파일은 Mac OS X에 이 디렉토리에 있는 서비스에 대한 설명을 해 주고 다른 서비스에 대한 의존성을 알려주는 역할을 한다. 시스템을 재부팅하면 서비스 시작 메시지가 나올 때 "Starting MySQL Server"라는 메시지가 나오는 것을 볼 수 있다.

윈도우 NT/2000

윈도우 NT 시스템에서는 NT 서비스 형태로 설치된 모든 애플리케이션을 자동으로 시작하고 종료시킨다. 윈도우 시스템에서 MySQL을 서비스로 설치하는 방법은 2장에서 나와 있다.

로그

MySQL 서버에서는 다음과 같은 로그를 만들 수 있다.

  • 에러 로그
  • 질의 로그
  • 이진 로그
  • 느린 질의 로그

mysql.server 또는 safe_mysqld를 이용하여 MySQL을 시작하면 에러 로그가 만들어진다. 원한다면 위에 있는 모든 로그를 기록할 수도 있고, 로그를 전혀 기록하지 않도록 할 수도 있다. 따로 실정해주지 않으면 로그 파일은 데이터 디렉토리에 저장된다.

에러 로그

에러 로그에는 safe_mysqld 스크립트에서 디다이렉트된 출력이 기록된다. 유닉스에서는 hostname.err(hostname 자리에는 호스트명이 들어감)이라는 파일로 저장된다. 윈도우에서는 mysql.err이라는 파일로 저장된다. 이 파일에는 서버가 죽어서 재시작한 경우를 포함하여 매번 서버가 시작할 때와 종료할 때 내용이 기록된다. 테이블의 치명적인 에러 또는 경고 메시지도 이 로그에 기록된다(나중에 확인해야 하거나 고칠 때에는 이 로그를 참조하면 된다).

이진 로그

이진 로그에는 데이터를 갱신하는 모든 SQL 명령어가 기록된다. MySQL에서는 데이터베이스에 있는 데이터를 실제로 바꾼 선언문만을 로그에 남긴다. 예를 들어, 삭제 명령을 내렸는데 전혀 삭제된 행이 없다면 로그에 아무런 기록도 남지 않는다. 도한 어떤 열에 원래 들어있던 것과 같은 값을 다시 대입하면 그 내용도 로그에 기록되지 않는다. 업데이트 내역은 실행된 순서대로 기록된다.

마지막으로 백업한 이후에 일어난 모든 업데이트 내역을 보관할 때에는 이진 로그가 좋다. 예를 들어, 데이터베이스를 매일 한 번씩 백업하는데 데이터베이스가 깨져버렸다면 다음과 같은 방법으로 마지막으로 완료된 트랜잭션까지의 데이터베이스 내용을 그래도 살려낼 수 있다.

  1. 데이터베이스를 복구한다(복구 작업과 관련된 내용은 잠시 후에 나오는 "백업"과 "복구"절을 참고하기 바란다).
  2. 마지막으로 백업한 이후에 이진 로그에 저장된 트랜잭션을 적용한다.

이진 로그를 활성화시킬 때에는 --log-bin=file 옵션을 사용한다. 파일명을 지정해주지 않으면 hostname-bin이라는 파일에 로그가 저장된다. 상대 경로를 사용하면 데이터 디렉토리를 기준으로 한 상대 경로로 간주된다. 그리고 파일명에 숫사 인덱스를 붙이기 때문에 파일명은 filename.number와 같은 형태가 된다(예를 틀어, hostname-bin.2). 이 인덱스는 파일을 순환(rotate)시키기 위한 용도로 쓰이는데, 다음과 같은 경우에 파일이 순환된다.

  • 서버를 다시 시작했을 때
  • 서버를 갱신(refresh)했을 때
  • 로그 크기가 최대 크기에 다다랐을 때
  • 로그를 밀어낼 때(flush할 때)

또한 사용중인 모든 이진 로그 파일의 목록이 저장된 인덱스 파일도 생성된다. 따로 설정해주지 않으면 파일명은 hostname-bin.index가 된다. 이 인덱스의 이름과 위치는 --log-bin-index=file 옵션으로 바꿀 수 있다.

이진 로그의 내용을 볼 때에는 mysqlbinlog 유틸리티를 사용한다. 다음 예에서 이진 로그가 어떤 식으로 작동하는지 알아보자. 이 예에서는 odin이라는 호스트에서 MySQL을 시작했고 log-bin 옵션을 전체 설정 파일인 /etc/my.cnf 파일에서 설정했다고 가정하자. 이 경우에 데이터 디렉토리에 다음과 같은 파일이 만들어진다:

 $ cd /usr/local/mysql/data $ ls -l . . -rw-rw----   1  mysql    mysql     73 Aug   5  17:06 odin-bin.001 -rw-rw----   1  mysql    mysql     15 Aug   5  17:06 odin-bin.index . .

odin-bin.index 파일의 내용은 다음과 같다:

 $ cat odin-bin.index ./odin-bin.001 $

위와 같은 인덱스 내용으로부터 인덱스와 같은 디렉토리에 odin-bin.001이라는 이진 로그파일이 있다는 것을 알 수 있다. mysqlbinlog 유틸리티를 이용하면 로그를 읽을 수 있다:

 $ mysqlbinlog odin-bin.001 # at 4 #010805 17:06:00 server id 1    Start: binlog v 1, server 3.23.40-log created 010805 17:06:00 $

클라이언트에서 데이터베이스를 갱신할 때마다 이진 로그의 내용은 점점 불어난다. 예를 들어, mysql 명령 프롬프트에서 다음과 같은 명령을 내리는 경우를 생각해 보자:

 $ mysql mysql> USE test; mysql> INSERT INTO test (object_id, object_title) VALUES (1, 'test'); Query OK, 1 row affected (0.02 sec) mysql> QUIT; Bye $

위와 같이 하고 나면 이진 로그의 내용이 다음과 같이 바뀐다:

 $ mysqlbinlog odin-bin.001 # at 4 #010805 17:06:00 server id 1    Start: binlog v 1, server 3.23.40-log created 010805 17:06:00 # at 73 #010805 17:39:38 server id 1    Query   thread_id=2    exec_time=0 error_code=0 USE test; SET TIMESTAMP=997058378; INSERT INTO test (object_id, object_title) VALUES (1, 'test'); $

즉 SQL 구문으로 데이터베이스를 갱신하면 이진 로그도 갱신된다. 로그를 밀어내고(flush) 새로운 이진 로그를 시작할 때에는 다음과 같이 하면 된다:

 $ mysqladmin -u root -ppassword flush-logs

이렇게 하면 모든 새로운 갱신 내용이 odin-bin.002라는 파일에 저장된다. 또한 odin-bin.index 파일에도 odin-bin.002 파일이 추가되었음이 기록된다.

이진 로그에 있는 명령을 다시 실행시킬 때에는 mysqlbinlog의 결과를 파이프를 통해 mysql 클라이언트 도구로 보내면 된다:

 $ mysqlbinlog odin-bin.001 | mysql

MySQL에서는 이진 로그를 제어하기 위한 몇 가지 옵션을 제공한다. --binlog-do-db=dbname 옵션을 지정하면 특정 데이터베이스를 갱신했을 때에만 로그를 기록한다. 특정 데이터베이스에 대해서 이진 로그를 기록하지 않을 경우에는 --binlog-ignore-db=dbname 옵션을 사용하면 된다.

느린 질의 로그

느린 질의 로그에는 long_query_time이라는 시스템 변수에 주어진 시간보다 더 오랜 시간이 걸린 모든 명령이 기록된다. 이 로그는 문제가 있는 질의를 찾아서 데이터베이스나 애플리케이션에서 튜닝이 필요한 부분을 밝혀낼 때 많은 도움이 된다.

느린 질의 로그를 사용하고 싶다면 --log-slow-queries=filename 옵션을 사용하면 된다. 파일 이름을 지정하지 않으면 자동으로 hostname-slow.log라는 파일에 저장되고, 디렉토리 이름을 지정하지 않으면 데이터 디렉토리에 저장된다. long_query_time 변수를 설정하고 싶다면 --set-variable long_query_time=time 옵션을 사용하면 된다(이때 time 자리에 초 단위로 시간을 지정하면 된다).

로그 순환

로그를 사용할 때에는 로그 파일이 너무 커지지 않도록 적당히 관리해애 한다. 잘못하면 파일 시스템에서 처리할 수 없을 만큼 로그 파일이 커질 수도 있기 때문이다.

하지만 여기에서 다루는 내용은 에러 로그에는 적용되지 않는다. 에러 로그는 safe_mysqld 스크립트에서 만들기 때문에 MySQL 서버에선 제어할 수 없고, flush-logs 명령어로 밀어낼 수도 없기 때문이다. 게다가 safe_mysqld에서는 매번 새로 시작할 때 새로운 로그를 만들지 않고 같은 로그에 내용을 덧붙인다. 에러 로그를 제대로 제대로 관리하려면 MySQL 서버의 시작 및 종료 스크립트를 수정해야 한다.

레드헷 리눅스를 사용한다면 mysql-log-rotate를 이용하여 로그를 순환시킬 수 있다. 이 프로그램은 MySQL을 설치했을 때 생기는 support-files 디렉토리에 들어 있다. 이 프로그램은 logrotate 유틸리티를 이용하여 자동으로 에러 로그를 순환시켜준다. 레드헷 리눅스에서 mysql-log-rotate를 설치할 때에는 그 파일을 /etc/logrotate.d 디렉토리에 복사하기만 하면 된다. 이 스크립트를 편집하려면 다른 로그도 순환시킬 수 있다. 기본적으로 이 스크립트에서는 질의 로그만 순환시킨다. logrotate에 대한 자센한 내용은 man 페이지나 레드헷 문서에서 찾을 수 있다.

리눅스에서 MySQL을 RPM 패키지로 설치하면 mysql-log-rotata가 자동으로 설치된다. RPM 설치기에서는 mysql-log-rotata를 /etc/logrotate.d에 복사할 대 그 이름을 mysql로 바꾼다. /etc/logrotate.d/mysql 파일이 있다면 로그 순환용 프로그램이 설치되어 있다고 보면된다.

레드헷 기반이 아닌 시스템에서는 로그 순환용 스크립트를 직접 만들어야 되는데, 사용하는 로그의 종류와 로그 저장 위치에 따라 스크립트의 내용이 달라진다. 하지만 로그파일을 복사한 다음 mysqladmin flush-logs 명령으로 로그를 다시 초기화하는 작업을 처리한다는 점은 같다.

백업

관리자가 해야 할 일 중 가장 중요한 것이 바로 백업 계획을 잡는 것이다. 특히 시스템이 다운되었을 때 데이터를 최대한 원래대로 복구해내는 것이 중요하다. 그리고 실수로 테이블을 지우거나 데이터베이스를 손상시킨 경우에도 백업해 둔 데이터가 있다면 문제를 쉽게 해결할 수 있다.

하지만 각 사이트마다 차이점이 있기 때문에 백업 문제에 있어서 어떤 방법이 딱 맞다고 할 수는 없다. 자신이 어떤 식으로 설치했는지, 어떤 기능이 필요한지에 따라 방법이 달라진다. 이 절에서는 백업을 할 때 지켜야 하는 일반적인 규칙과 백업 방법 등에 대해 설명할 것이다. 이 절에 나와 있는 내용을 바탕으로 자신의 상황에 맞는 적당한 백업 방식을 만들면 한다.

일반적으로 백업을 할 때 다음과 같은 점을 고려해야 한다.

  • 가능하다면 데이터베이스와 다른 기기(다른 디스크나 데이프 드라이브 등)에 백업하는 것이 좋다. 디스크가 망거졌더라도 다른 장소에 백업해 두었다면 데이터는 살릴 수 있다. 이진 로그를 사용한다면 이진 로그도 백업하는 기기에 저장하도록 하자.
  • 백업을 하기 전에 디스크 공간이 충분히 남아 있는지 확인한다.
  • 될 수 있으면 백업과 함께 이진 로그를 활용하자. 그러면 데이터 손실을 최소로 줄일 수 있다. 이진 로그를 사용하지 않으면 마지막으로 백업할 시점까지의 데이터만 살릴 수 있다. 백업을 해 두었더라도 이진 로그가 없으면 별 도움이 안되는 경우도 있다.
  • 백업 아카이브의 개수를 적절하게 유지하는 것이 좋다.
  • 위급 상황이 일어나기 전에 백업을 테스트해보는 것이 좋다.

이제 백업할 때 쓸 수 있는 두 가지 MySQL 유틸리티에 대해 알아보자.

mysqldump

mysqldump는 데이터베이스를 덤프해주는 MySQL 유틸리티이다. 이 프로그램에서는 기본적으로는 그 데이터베이스를 다시 만들 때 필요한 모든 명령어(CREATE TABLE, INSERT 등)가 들어 있는 SQL스크립트를 만든다. 이렇게 하면 mysqlhotcopy로 직접 복사하는 경우에 비해 다른 하드웨어나 운영 체제에서 같은 데이터베이스를 새로 만들기에 좋은(즉 이식성이 좋은) ASCII 형식으로 출력된다는 점이다. 그리고 출력 결과가 SQL 스크립트이므로 원하는 테이블만 골라서 복구할 수도 있다.

mysqldump로 데이터베이스를 백업할 때에는 -opt 옵션을 사용하는 것이 좋다. 이 옵션을 사용하면 -quick, --add-drop-table, --add-locks, --extended-insert, --lock-tables 옵션이 모두 선택되며 데이터베이스를 가장 빠르게 덤프할 수 있다. --lock-tables 옵션은 데이터베이스를 사용할 수 없다.

실제 명령은 다음과 같은 식으로 입력하면 된다:

 $ mysqldump --opt test > /usr/backups/testdb

이렇게 하면 test 데이터베이스가 /usr/backups/testdb라는 파일로 덤프된다. 이진 로그를 사용한다면 --flush-logs 옵션을 사용하여 백업할 때 이진 로그를 새로 시작하도록 하는 것이 좋다:

 $ mysqldump --flush-logs --opt test > /usr/backups/testdb

mysqldump에서는 백업할 때 사용할 수 있는 몇 가지 다른 옵션도 제공한다. mysqldump에서 사용할 수 있는 모든 옵션의 목록을 보고 싶다면 mysqldump --help라는 명령을 사용하면 된다.

mysqlhotcopy

mysqlhotcopy는 LOCK TABLES, FLUSH TABLES, 유닉스 cp 명령어 등을 조합하여 데이터베이스를 빠르게 백업하는 펄 스크립트이다. 이 스크립트에서는 데이터베이스 파일을 그대로 다른 곳으로 복사한다. 파일을 복사하기 때문에 mysqldump에 비해 훨씬 더 빠르지만 원래 이식 가능한 형태로 만들어지는 MyISAM 테이블을 제외하면 다른 하드웨어 또는 운영 체제에서 사용하기가 힘들다. 또한 mysqldump는 외부에서도 실행시킬 수 있지만, mysqlhotcopy는 데이터베이스와 같은 호스트에서만 실행시킬 수 있다.

mysqlhotcopy를 실행시킬 때에는 다음과 같은 명령을 사용한다:

 $ mysqlhotcopy test /usr/backups

이렇게 하면 /usr/backups 디렉토리에 test 데이터베이스에 있는 모든 데이터 파일이 복사된다.

이진 로그를 사용한다면 --flushlog 옵션을 사용하면 백업을 마치고 나서 이진 로그를 새로 시작하는 것이 좋다.

복구

디스크에 하드웨어적인 문제가 있는 경우, 데이터 파일이 망가진 경우, 실수로 테이블을 지워버린 경우와 같이 데이터를 복구해야 하는 상황은 정말 다양한다. 이 절에서는 복구 절차에 관한 전반적인 내용을 다뤄보기로 한다.

일반적인 데이터베이스를 복구할 때에는 백업 파일과 이진 로그, 이렇게 두 가지가 필요하다. 복구 절차는 크게 다음과 같이 나눌 수 있다.

  • 마지막으로 백업한 내용으로부터 데이터베이스를 복구한다.
  • 이진 로그를 적용해서 시스템을 완전하게 예전 상태로 되돌린다.

이진 로그 기능을 사용하지 않았다면 마지막으로 백업한 부분까지만 복구할 수 있다.

mysqldump 복구

여기에서는 앞에서 덤프한 test라는 데이터베이스를 북구한다고 가정하겠다.

다음과 같은 명령을 실행하면 데이터베이스를 다시 볼러올 수 있다:

 $ cat test.dump | mysql

이 명령을 실행시키면 mysqldump에서 만들어낸 SQL 명령을 모수 실행시키게 되므로 마지막으로 데이터베이스를 백업할 시점의 상태 그대로 돌아갈 수 있다.

시스템을 마지막으로 백업할 상태로 돌로놓고 나면 이진 로그를 이용해서 마지막 백업 이후에 처리된 트랜잭션을 다시 실행시키면 된다. 로그에 여러 개의 데이터베이스에 대한 내용이 들어 있는데 그 중 하나만 복구하고 싶다면 --one-database 옵션을 사용해서 다른 데이터베이스에 적용되는 SQL 명령을 걸러낼 수 있다. 또한 이진 로그 중에서 마지막 백업 이후에 기록된 부분만 다시 실행시켜야 한다. 각 이진 로그 파일에 대해 다음과 같은 명령을 입력하자:

 $ mysqlbinlog host-bin.xxx | mysql --one-database=testdb

경우에 따라 mysqlbinlog 프로그램에서 출력된 결과를 조금 수정해야 할 수도 있따. 예를 들어, DROP TABLE 명령을 잘못 내려서 지워진 테이블을 복구한다면 mysqlbinlog에서 출력된 내용 중 DROP TABLE 선언문 부분을 지워야 한다. 그렇게 하지 않으면 열심히 복구한 테이블이 다시 삭제된다. 따라서 이런 경우에는 mysqlbinlog에서 출력된 내용을 텍스트 파일에 저장해서 편집하고, 그 텍스트 파일을 이용하여 데이터베이스를 복구해야 한다.

mysqlhotcopy 복구

mysqlhotcopy 백업 데이터로부터 복구할 때에는 서버가 멈춰 있는 상태에서 데이터베이스 파일을 백업 디렉토리에서 mysql 데이터 디렉토리로 복사하면 된다. 데이터베이스를 /var/backup/test에 백업했다고 가정하고, MySQL 데이터 디렉토리가 /usr/local/mysql/data 라면 다음과 같은 명령을 실행시켜서 마지막에 백업한 시점으로 데이터베이스를 되돌릴 수 있다:

 $ cp -R /var/backup/test /usr/lcoal/mysql/data

이제 mysql 서버를 다시 시작시키고 앞에서 설명한 것처럼 이진 로그를 적용시키면 시스템을 완전히 복구할 수 있다.

테이블 관리 및 복구

데이터 파일에 대한 쓰기 작업이 제대로 처리되지 않았을 때 데이터베이스 파일이 망가질 수도 있다. 전원이 나가거나 MySQL 서버를 정상적으로 종료시키지 않으면 이런 일이 일어날 수 있다.

MySQL에서는 myisamchk/ismchk와 mysqlcheck라는 두 가지 방법으로 테이블 에러를 알아내고 수리할 수 있다. 웬만하면 정기적으로 테이블에 문제가 있는지 검사하는 것이 좋다. 데이터베이스에 문제가 있다는 것을 일찍 발견할수록 테이블을 성공적으로 복구할 수 있는 가능성이 높아지게 마련이다.

mysqlcheck는 MySQL 3.23.38부터 새로 등장한 프로그램이다. myisamchk/isamchk와 mysqlcheck의 가장 큰 차이점은 mysqlcheck에서는 서버가 돌아가고 있을 때에도 테이블을 검사하거나 수리할 수 있다는 점이다.

myisamchk와 isamchk는 서로 상당히 유사하다. 이 둘의 기능은 같지만 myisamchk는 MyISAM 테이블에서 isamchk는 ISAM 테이블서 사용한다. mysqlcheck는 MyISAM 테이블에서만 사용할 수 있다.

테이블 검사

테이블에 어떤 문제가 있는 것 같다면 우선 테이블 검사용 유틸리티를 이용하여 테이블을 검사해야 한다. 데이터 파일의 확장자를 보면 테이블의 종류를 알 수 있다. 파일 확장자가 .MYI라면 MyISAM 테이블이고 .ISM이면 ISAM 테이블이다. 따라서 .MYI 파일에 대해서는 myismchk와 mysqlcheck를, .ISM 파일에 대해서는 isamchk를 사용하면 된다.

table1(ISAM 테이블)과 table2(MyISAM 테이블)라는 두 개의 테이블이 있는 test라는 테이터베이스가 있다고 가정하자. 우선 적절한 유틸리티로 테이블을 검사해야 한다. myisamchk와 isamchk를 사용할 때에는 파일을 읽는 도중에 서버에서 파일에 쓰기 작업을 하는 것을 방지하기 위해 MySQL 서버를 종료시켜야 한다:

 $ myisamchk table2.MYI Data records:      0    Deleted blocks:      0 - check file-size - check key delete-chain - check record delete-chain - check index reference $ isamchek table1.ISM Checking ISAM file: table1.ISM Data record:       0   Deleted blocks:       0 - check file-size - check delete-chain - check index reference $ mysqlcheck test table2 test.table2                                  OK

위와 같은 결과가 나오면 두 테이블 모두 에러가 없음을 알 수 있다.

간단한 에러를 찾아낼 때에는 위와 같은 기본적인 검사만으로도 충분하다. 에러는 없지만 테이블이 손상된 것 같다면 myisamchek/isamchk의 --extend-check 옵션이나 mysqlcheck의 --extend 옵션으로 확장 검사를 할 수 있다. 확장 검사를 하면 시간은 조금 오래 걸리지만 매우 자세히 검사할 수 있다. 확장 검사에서도 아무로 에러가 없다면 테이블에 문제가 없음을 알 수 있다.

테이블 복구

검사 결과 테이블에 에러가 있다면 그 에러를 고쳐야 한다.

myisamchk나 isamchk를 사용하는 경우에는 테이블을 복구할 때 MySQL 서버를 종료해야 한다. 그리고 만약에 대비해서 테이블을 복구하기 전에 데이터 파일을 백업해 두는 것이 좋다.

우선 --recover 옵션을 주고, myisamchk/isamchk를 실행해 보자:

 $ isamchk --recover table1.ISM - recovering ISAM-table 'table1.ISM' Data records: 3 $ myisamchk --recover table2.MYI - recovering (with keycache) MyISAM-table 'table2.MYI' Data records: 2

위와 같이 해도 문제가 해결되지 않는다면 느리긴 하지만 --recover로는 수정할 수 없는 에러도 고칠 수 있는 --safe-recover 옵션을 사용해 보자:

 $ isamchk --safe-recover table1.ISM - recovering ISAM-table 'table1.ISM' Data records: 3 $ myisamchk --safe-recover table2.MYI - recovering (with keycache) MyISAM-table 'table2.MYI' Data records: 2

mysqlcheck에서느 복구 옵션이 --repair밖에 없다:

 $mysqlcheck -repair test table2 test.table2                                  OK

이렇게 해도 안 된다면 백업 자료와 이진 로그를 이용해서 테이블을 복구하는 수밖에 없다. 자세한 내용은 "백업" 절과 "복구" 절에 나와 있다.

테이블 정기 검사

테이터베이스 파일에 대해서 정기적으로 테이블 검사를 해 주는 것이 좋다. isamchk/myisamchk/mysqlcheck 명령을 처리하는 스크립트를 만들어서 cron과 같은 스케쥴링 소프트웨어에서 정기적으로 실행하도록 만들면 된다.

또한 시스템이 부팅될 때 테이블을 검사할 수 있도록 시스템 부팅 절차를 조금 고치는 것도 괜찮은 생각이다. 이렇게 하면 특히 시스템이 다운된 후에 재부팅할 때 여려 모로 도움이 된다.


한빛미디어 "MySQL 시스템 관리와 프로그래밍"

Posted by tornado
|