SystemV's

PMM - Percona Monitoring and Management

PMM은 서버 클라이언트 형태로 MySQL(MariaDB), MongoDB를 모니터할수 있는 플랫폼으로 1.0.3 Beta 버전을 지나 얼마전 1.0.4 GA 정식 안정화버전으로 나온 뜨끈한 제품이다.

 • PMM Client : 모니터링하고자 하는 DB서버에 직접 설치한다. 서버정보, DB정보, 쿼리정보등을 수집해 PMM Server로 보내준다.

 • PMM Server : PMM Client에서 수집된 데이터를 저장하고 이를 이용해 웹기반의 대시보드와 그래프를 보여준다.

설치

모니터링 하고자 하는 서버가 1대라면 DB서버에 Server/Client를 모두 설치해도 무방하지만 DB서버가 다수라면 모니터링 전용 Server를 두고 각각의 DB서버에는 Client만 운영하도록 한다.

PMM Server 설치

PMM Server는 Docker 이미지로 배포된다. (https://hub.docker.com/r/percona/pmm-server)

본 문서에서 서버용 호스트를 CentOS7로 진행하지만 Docker를 사용하기 때문에 어떤 OS인지는 중요하지 않다.

CentOS7에서 yum 으로 Docker 설치가 가능하지만 최신버전을 권장하므로 스크립트를 활용하도록 한다.

이제 이미지를 받아온다.

받은 이미지를 확인한다.

PMM 데이터 컨테이너를 만든다.

PMM 서버 컨테이너를 만들고 실행한다.

만약 80포트를 쓸 수 없다면 -p 8080:80 해서 8080으로 바꿔쓰면 된다.

생성된 컨테이너를 확인한다.

웹이 잘뜨는지 확인해본다. 물론 Client 설치가 되지 않았므로 데이터는 없다.

- PMM 랜딩페이지 : http://서버아이피

- 쿼리분석 페이지 : http://서버아이피/qan

- 매트릭스 모니터 : http://서버아이피/graph

PMM Client 설치

Client는 모니터링 할 DB서버에 설치되고 각종 데이터를 수집해 수집된 데이터는 Server로 보내준다.

여러가지 설치방식을 제공하지만 손쉽게 rpm 설치로 진행한다. 

https://www.percona.com/downloads/pmm-client/LATEST/ 에서 최신버전을 구할 수 있으며 현재 최신버전은 1.0.4 이다.

RPM 설치

기본 설치 경로는 /usr/local/percona 

Server 등록하기

Client에서 Server로 데이터를 밀어주는 방식이라 Client에서 Server를 등록해줘야 한다.

모니터링 서비스 등록하기

모니터링 서비스 영역에는 총 4가지가 있다.

 • linux:metrics : 시스템 자체의 모니터링

 • mysql:metrics : MySQL 인스턴스의 모니터링

 • mysql:queries : 쿼리 모니터링

 • mongodb:metrics : MongoDB 인스턴스의 모니터링

 이 외에 셋트 영역도 있다.

 • mysql : linux:metrics + mysql:metrics + mysql:queries

 • mongodb : linux:metrics + mongodb:metrics

개별적으로 하나씩 등록해도 되지만 간단하게 mysql 이나 mongodb 같은 셋트영역을 사용하면 된다.

등록 확인하기

이제 모든 설치 작업이 끝났으므로 웹으로 확인하면 된다.

http://서버아이피/qan

http://서버아이피/graph

user : admin

password : admin  

Binary Log(binlog) 사용하기

binlog(쉽게 빈로그라 읽고 쓰자..)는 서버내에서 발생되는 모든 변경내역이 기록되는 파일이다.

PIT(시점복구)와 리플리케이션에 필수 요소이며 이 2가지 기능을 쓰지 않는다면 굳이 활성화 할 필요는 없다.

빈로그를 쓰는것 자체가 미세하지만 IO 부담을 가중시키기 때문이다.

빈로그 활성화 하기

빈로그를 활성화 할 수 있는 방법은 2가지다.

1. 서버 구동시에 --log-bin 옵션을 준다.

--log-bin=VALUE 과 같은 형식으로 사용할수 있는데 VALUE에는 파일경로/파일명을 쓸수 있다.

VALUE값을 생략하면 data_dir 디렉토리에 hostname-bin 이라는 이름으로 생성된다.

(라고 모든 MySQL 공식 도큐먼테이션에서 밝히지만 5.5버전 이후에서 테스트 했을시 mysqld-bin 으로 생성되더라.

hostname 변경시 발생될 수 있는 변수 상황을 피하기 위함이라 생각한다.) 

2. my.cnf 에 작성

my.cnf의 [mysqld] 섹션에 log_bin 옵션을 설정해준다.

log_bin=VALUE 형식으로 작성할수 있으며 VALUE에 대한 내용은 위에 구동시 내용과 동일하다. 

어떤방식으로 활성화를 하든 생성되는 빈로그에는 시퀀셜한 넘버링이 자동으로 붙게된다. (mysqld-bin.000001)

빈로그 활성화 여부는 show 로 확인 가능하다.

show 로 확인되는 log_bin 값과 my.cnf 에 작성하는 log_bin 은 전혀 다르므로 헷갈리면 안된다.

show 의 log_bin 은 현재 빈로그의 활성화 유무만을 보여주는 변수값일 뿐이고 

my.cnf 에 작성되는 log_bin 은 빈로그를 활성화 시키면서 경로까지 지정해주는 설정 옵션이다.

어떤 방식으로 활성화 해도 결과는 동일하지만 서버 구동시마다 잊지 않고 옵션을 넣어 주는게 힘들기 때문에 

my.cnf 설정법이 주로 쓰인다.

추가 설정 옵션

빈로그 사용시 기본적으로 같이 쓰는 옵션이 몇개 더 있으니 넘어가듯 살펴보자.

- log-bin=bin-log

data_dir에 bin-log.000001 같은 형식으로 빈로그를 남기고...

- binlog_format=row

빈로그 데이터 포맷을 row 형식으로 지정하고...

- binlog-do-db=test-db

test-db 라는 DB에 대한 내용을 남기는데...

- max_binlog_size=256M

빈로그 파일이 256M가 넘어가면 bin-log.000002 로 자동 로테이트 하면서 ...

- expire_logs_days=4  

4일 이상 오래된 빈로그는 삭제하도록 한다.

빈로그 포맷

MySQL 5.7.6 까지는 기본값이 statement 이고 그 이후 버전부터는 row 가 기본값이다. (예외적으로 클러스터는 mix)

빈로그 포맷은 동적변수이므로 DB가 가동중일때도 set 으로 변경할 수 있지만 리플리케이션일 경우에는

마스터/슬레이브를 내리고 변경하는게 좋다.

빈로그 포맷 형식에는 3가지가 있는데 상황에 맞게 적절하게 써야한다.

- statement : 

쿼리문으로 기록되기 때문에 용량을 적게 차지하고 버전특성을 타지 않는다. 

하지만 복구시 일관된 데이터에 대한 보장이 적으며(sysdate()와 now()의 다른 결과등..)

쿼리기반이기 때문에 복구(동기화)시 굉장히 느려질 수 있다.

- row :

statement 형식과 반대 개념으로 쿼리문이 아닌 변경된 데이터 기반으로 기록된다.

장단점 역시 statement 과 정반대로 용량이 커지고 복구시 일관성을 보장받을수 있으며 빠른 복구(동기화)가 가능하다.

- mixed :

statement 와 row 방식의 장점을 취합한 형태로 기본적으로 statement 방식을 취하나 필요에 따라(일관성 보장등..)

row 방식으로 기록한다.

데이터 일관성을 위해 mixed 나 row 방식을 선택하는게 백번 맘 편하다.

주의

isolation level이 read-committed 일 경우 현재 트랜젝션이 종료되지 않았더라도 다른 트랜젝션에서 동일한 데이터의

commit 이 일어나면 현재 트랜젝션에서도 변경된 값이 보이게 된다. 이런 환경에서 statement 방식을 사용하게 되면

트랜젝션 단위로 순서대로 로깅하기 때문에 복구나 슬레이브동기화시 원하는 바와 달리 다른 결과가 나타날수 있다.

그렇기 때문에 read-committed 에서는 row 방식이나 이런 상황에서 자동으로 row 형태로 변경해주는 mixed 방식을

사용해야 한다.

PIT(Point In Time)복구

보통 사용량이 낮은 새벽시간에 디비백업을 하는데 그 이후 장애가 발생했다면 백업과 장애발생 시점 사이의

데이터를 잃을수 밖에 없다. 그래서 백업된 데이터를 활용해 1차로 복구하고 빈로그를 통해 PIT 복구를 추가로

진행하게 된다.  

xtrabackup 을 이용해 백업된 데이터를 먼저 복구하고

백업된 데이터의 마지막 포지션이 빈로그와 겹치는 파일부터 포지션을 지정해 먼저 적용하고

그 이후 부터 현재까지 생성된 빈로그도 전부 적용시키면 PIT 복구까지 완료된다.

* start-position 대신 start-datetime 을 쓸수도 있으나 동일한 시간대에 실행된 원치않은 다른 쿼리나 이벤트가

재실행 될수도 있기 때문에 position을 쓰는걸 권장한다.

빈로그 원격저장

PIT복구등에 반드시 필요한 빈로그이지만 예전에는 로컬에만 저장되었기 때문에 디스크나 서버자체에 장애가 생기게 되면

복구하는데 어려움이 많았다. 하지만 MySQL5.6 이상, MariaDB 10.0 이상부터 빈로그를 원격에 저장할 수 있는 기능이

생겼다. 물론 이전에도 rsync나 scp, ftp 등을 이용해 원격에서도 백업할수 있었지만 지금 쓰고 있는 빈로그는 백업할

수가 없었다. 맥스사이즈를 넘어서거나 DB재가동 등으로 스위칭 된 빈로그만 백업이 가능했기때문에 스위칭되는 간격동안의

데이터는 유실될수밖에 없다는 단점이 있었다.

새로 추가된 원격저장은 지원버전 이상의 mysqlbinlog(예전부터 빈로그를 관리하던 커맨드에 옵션만 추가됨)를 사용해 

실행할 수 있다. 기존에 사용하던 실행파일을 그대로 쓰는건 좋은거 같다.

실행문을 보기 전에 기본적으로 필요한 옵션 몇가지만 정리해보자.

--read-from-remote-server 

빈로그를 원격에서 저장하기 위한 핵심옵션으로 이 옵션이 사용되면 당연히 --host(DB주소), --user(접속계정),

--password(패스워드), --port(접속포트) 등의 옵션도 함께 사용해야한다.

--raw 

이 옵션이 없으면 원격서버에서 빈로그를 텍스트 형태로 가져오게 된다. 일반적으로 실서버에 있는 빈로그 그대로를

백업하기 원하기 때문에 이 옵션을 반드시 쓰도록 한다.

--stop-never

빈로그가 존재하는 실서버의 디비가 내려가거나 커넥션이 끊어지지 않는한 용량이 커지건 파일이 바뀌건 죽지않고

계속 빈로그를 가져오게 된다.

--stop-never-slave-server-id

stop-never 옵션을 쓰게 되면 백업서버가 리플리케이션의 슬레이브처럼 동작하게 된다. 리플리케이션 내에서는

server_id가 중복될수 없기때문에 해당 옵션으로 server_id를 지정해주는 것이다. 물론 생략도 가능하며

생략하면 65535로 셋팅된다.

--result-file

가져온 빈로그 파일명에 Prefix를 붙여준다. 

--result-file=orig-binlog- 라면 orig-binlog-mysqld-bin.000001 처럼 저장된다.

원본과 동일하게 저장하고자 하면 잴끝에 "/" 를 포함해 디렉토리명까지만 지정해주면 된다.

--result-file=/bin_backup_dir/

--to-last-log

이 옵션을 쓰면 지정된 빈로그 뿐만 아니라 그 이후에 생성된 모든 빈로그를 가지고 온다. --stop-never 옵션을 쓰면

자동으로 활성화되는 옵션이므로 굳이 지정해 쓸 일은 없다.

위 옵션들을 참고해 실행문을 만들어 보면 아래와 같다.

스크립트

원격 빈로그백업의 단점이 원서버가 재시작되거나 다른이유로 커넥션이 한번 끊기면 자동 재접속을 하지 않는다는 것이다.

게다가 실행문 마지막에 백업할 빈로그 파일명을 하나 지정해줘야하는데 그때마다 파일명을 확인하는게 굉장히 번거롭다.

그래서 스크립트로 만들어 쓰는게 좋다.

---

innodb_flush_method, O_DIRECT에 대한 오해!

innodb_flush_method 설정은 InnoDB가 데이터 파일을 어떤 방식으로 읽고 쓰기를 할것인지

선택할수 있게 해준다.

그중 O_DIRECT 설정값을 흔히 사용하는데 이유를 물어보면 IO 성능향상을 위해서라고 한다.

웹상의 많은 글에도 IO 성능향상을 위해 O_DIRECT를 쓰라는 글이 많아 보이는데 물론 완전히 틀린말은 아니지만

좀 더 정확히 알고 쓰자라는 취지에서 글을 남겨본다.

O_DIRECT

거의 모든 UNIX&Linux 시스템에서는 모든 데이터 읽기/쓰기에 OS 캐시를 사용한다.

이러한 캐시기반에서는 어플리케이션이 데이터를 읽을때 가장 먼저 OS캐시에 원하는 데이터가 있는지 찾아보고

원하는 데이터가 캐시에 있다면 그대로 가져다 쓰고 만약 캐시에 원하는 데이터가 없다면 디스크에서 데이터를 찾아다가

캐시에 먼저 복사한 다음에 어플리케이션에 데이터를 돌려준다. 

O_DIRECT 옵션은 바로 이 OS캐시를 MySQL/MariaDB 에서 사용하지 않겠다라는 선언이다.

[ fdatasync ]

DB -> OS캐시 -> DataFile 

[ O_DIRECT ]

DB -> DataFile

OS캐시를 거치는 과정이 없어졌으므로 IO 과정이 단순화 되어 빨라질꺼라 착각할수 있지만 실제로는 전혀아니다.

캐시=메모리 라는 점을 다시 생각해보면 OS캐시는 느려터진 디스크와 DB간의 완충작용을 해주기 때문에 

O_DIRECT를 쓰게되면 버퍼풀에서 읽어오는 읽기성능은 차치하더라도 쓰기성능은 오히려 더 나빠질수 밖에 없다.  

그렇다면 O_DIRECT를 쓰는 진짜 이유는 무엇일까?

이유 

IO 성능 저하를 감안하면까지 O_DIRECT를 사용하는 가장 큰 이유는 더블버퍼링을 막아 메모리를 효율적으로 쓰기 위함이다.

(더블버퍼링을 더블라이트버퍼(Double Write Buffer)와 헷갈리면 안된다. 더플라이트버퍼는 데이터의 안전성을 위해

디스크상에 중복된 데이터를 이중으로 저장하는 것이고 여기서 말한 더블버퍼링은 메모리상에서의 중복 저장을 의미한다.)

InnoDB는 이미 버퍼풀(Buffer Pool)이라는 훨씬 고도화된 메모리 관리 영역을 가지고 있기 때문에 동일한 데이터를

버퍼풀과 OS캐시에 중복으로 저장해 메모리를 낭비할 필요가 없다.

그리고 두번째 이유는 OS캐시에 대한 무한한 불신(ㅋㅋㅋㅋ) 때문이 아닐까? 사실 OS캐시가 우리가 생각하는것 만큼

똑똑하게 움직여주진 않는다. OS캐시는 되도록 많은 데이터를 캐시하려고 하기 때문에 어느순간 버퍼풀의 데이터가 

스왑으로 빠져버리는 경우도 허다하기 때문이다. 물론 vm.swappiness 를 수정하거나 innodb_buffer_pool_populate 를 

설정할 수 도 있겠으나 해당서버에 DB이외에 다른 어플리케이션이 있을수도 있고 innodb_buffer_pool_populate 는 

아직 MariaDB에서만 지원되며 다른 예외사항이 발생할수 있기 때문에 O_DIRECT를 쓰는게 DB가 OS캐시를 사용할수 없게 

강제할수 있는 가장 쉽고 빠른 방법이 될 수도 있다.

조건

O_DIRECT를 썻다면 쓰기 성능이 나빠지게 되는데 이를 보완해줄수 있는게 RAID 컨트롤러의 캐시다.

OS와는 전혀 별개의 캐시를 IO에 사용하기 때문에 스와핑이나 기타 변수가 발생할 염려없이 쓰기 성능을 

보장해주므로 이런 캐시를 내장한 컨트롤러를 쓴다면 O_DIRECT를 검토하도록 하자. 물론 BBU까지 있다면 금상첨화다!

ETC

O_DIRECT를 쓰더라도 데이터파일에 대해서만 OS캐시를 안쓸뿐이다. 만약 Redo로그에서도 OS캐시를 쓰지 않겠다면

ALL_O_DIRECT를 사용하면 된다.

innodb_flush_log_at_trx_commit

DBMS를 사용하다 보면 읽기보다는 쓰기에 더 신경이 쓰일때가 있다. InnoDB는 쓰기작업에 있어서 몇가지

옵션을 제공하므로 정확히 알아두면 굉장히 유용하게 쓰일수 있다.

먼저 쓰기 작업에 대한 흐름을 살펴보자.

1. Commit 이 되면

2. InnoDB의 log buffer에 데이터를 쓴다(메모리영역)

3. 그런 다음 OS buffer 를 거쳐서(메모리영역)

4. redo 로그라 불리는 InooDB log file 에 쓰게 된다. (디스크영역) (이 부분을 flush라 표현한다.)

innodb_flush_log_at_trx_commit 옵션이 위 흐름을 어떻게 컨트롤 할지 선택할 수 있게 한다.

innodb_flush_log_at_trx_commit=1

기본값이다. 굳이 설정하지 않아도 해당 값으로 셋팅된다.

트랜잭션이 커밋되면 1~4 의 과정을 건건이 처리하게 되는데 ACID의 지속성을 보장받을수 있지만

IO 부하가 상당하다. 쓰기 속도보다 데이터의 중요도가 훨씬 더 크다면 기본값을 사용하는게 좋다.

innodb_flush_log_at_trx_commit=2

기본값과 다른점은 커밋됐을때 1~3 까지의 과정만 처리한다는 것이다. (한마디로 메모리 영역만)

그리고 실제 디스크에 쓰는 flush 는 약 1초에 한번씩 자동 수행된다.

트랜젝션의 양은 상관없이 flush 가 1초에 한번씩만 수행되기 때문에 매번 flush 되는 기본값보다 IO 성능이 

월등히 좋아지지만 단점은 데이터를 유실할 가능성이 있다는 점이다.

OS buffer 까지는 데이터가 넘어가기 때문에 DBMS가 크래시되는건 별 문제 없지만 1초마다 실행되는 flush가 

실행되고 있는 와중에 OS가 셧다운되버린다면 해당 트랜젝션은 커밋되었지만 유실될수 밖에 없다.

innodb_flush_log_at_trx_commit=0

이 옵션은 1~2 까지의 과정만 처리한다. 1초에 한번씩 3~4 과정을 자동으로 수행하게 되는데 쓰기 속도가

그만큼 더 빨라지지만 역시나 리스크는 더 커진다. 커밋해도 최종적으로 log buffer 에 쓰여지는 것 까지만 

보장하므로 flush 되는 과정에서 DBMS가 크래시 되면 해당 트랜잭션은 유실된다.

서버가 갑작스럽게 죽었을때 1~2초 정도의 데이터를 버릴수 있을 정도의 상황이라면 옵션2 가 가장 좋은 선택이다.

서버가 죽었을때는 당연하고 MySQL/MariaDB 가 갑자기 죽어도 1~2초 정도의 데이터를 버릴수 있다면 옵션0 이 가장 좋다.

커밋된 데이터는 무조건 살려야만 한다면 반드시 옵션1 을 선택해야 한다.

XtraBackup을 이용한 InnoDB 증분백업과 복구하기 ( 4 ) 원격백업

원격백업

XtraBackup을 이용한 InnoDB 증분백업과 복구하기 ( 3 ) 증분백업

Incremental Backup (증분백업)

증분백업이란 바로 이전에 백업했던(그게 풀백업이었던, 증분백업이었던지 간에..)시점부터 현재까지 변경된

내역만 백업하는 방식이다. 설명만으로만 본다면 증분백업은 정말 금방 끝날것같지만 그렇지는 않다.

증분백업도 풀백업과 동일하게 데이터파일을 모두 읽어야 하기 때문이다. 물론 직전 백업의 lsn보다 큰 번호에 

해당하는 것들만 백업하기때문에 풀백업보다는 확실히 시간이 줄고 용량도 적다.

백업 방식

서비스특성이나 관리자 성향에 따라 다 다르겠지만 보통 아래와 같은 방식을 쓴다.

1. 매일매일 풀백업을 한다.

 - 장점 : 속편하다. 설정이 편하다. 복구가 가장 빠르다.

 - 단점 : 새벽에 시작해서 아침에 끝날까? 용량을 너무 먹는다. 1T 데이터를 1주일 저장하면 7T다.

2. 풀백업을 한번하고 죽을때까지 증분백업만 한다.

 - 장점 : 용량을 가장 적게 먹는다. 처음 풀백업 외엔 백업시간이 가장 짧다.

 - 단점 : 복구시 오래걸린다. 

3. 1주를 단위로 사용량이 가장적은 요일 새벽에 풀백업을 하고 그외 요일은 증분백업을 한다.

 - 장점 : 적은 용량으로 1주일 데이터를 저장할수 있다. 풀백업외에는 백업부하가 적다.

 - 단점 : 설정이 복잡해지고 관리가 귀찮다.

SystemV는 일반적인 3번방식을 설명하고자 한다. 사실 가장 장점없고 단점없는 그저그런 방식이랄까...;

LSN 

Log Sequence Number 의 약자로 MySQL 로그파일내에 해당 로그 레코드의 번호값이다. 번호? 순서? 위치? 주소? 암튼..

보통 시점복구나 증분백업등의 관리용으로 사용된다. 1트랜잭션 = 1LSN 은 아니다. 

여러개의 LSN들이 하나의 트랜잭션에 포함되는 구성이 되겠다.

증분백업

증분백업을 위해서는 반드시 이전백업데이터가 있어야 하므로 무조건 풀백업이 한번은 있어야 한다. 

주단위 백업을 설명하기로 했으므로 일요일에 풀백업을하고 그외는 증분백업을 한다고 가정하자.

[일요일] 풀백업

풀백업 디렉토리에 xtrabackup_checkpoints 를 보면 풀백업에 대한 lsn 정보가 있다.

이 풀백업은 0번(from_lsn)부터 1951426944668번(to_lsn)까지 백업된 데이터다.

풀백업이므로 당연히 from_lsn 은 0 이고 to_lsn 은 다음 증분백업의 from_lsn 이 될 것이다.

증분백업은 풀백업 실행문에 --incremental 를 추가해서 이 실행문이 '증분백업이다' 라고 선언해주고

incremental-lsn 에 이전백업(여기서는 풀백업)의 to_lsn 의 값을 할당해주면 된다.

[월요일] 1차증분백업 (풀백업에 대한 증분)

2차 증분백업은 1차증분백업에 대한 증분백업을 해야하므로 1차증분백업 디렉토리에 있는 xtrabackup_checkpoints 의 to_lsn을 이용한다.

[화요일] 2차증분백업 (1차증분백업에 대한 증분)

3차 역시 2차증분백업의 to_lsn을 이용한다.

[수요일] 3차증분백업 (2차증분백업에 대한 증분)

.

..

...

....

이렇게 토요일까지 진행하고 나서 일요일에는 동일한 방식으로 풀백업부터 다시 시작하면 된다.

to_lsn 찾기

이게 번거로울 경우 직전백업의 디렉토리경로를 지정해주는 방법도 있다.

--incremental-lsn 옵션 대신에 --incremental-basedir 에 이전백업 디렉토리를 할당해 주면 된다.

증분백업 복구

증분백업의 복구도 풀백업과 동일하게 apply-log 로 로그반영 작업을 해주고 copy-back 으로 실제복구를 하게 된다.

다만 다른점이 있다면 apply-log 로 로그반영시에 --redo-only 옵션을 꼭 같이 써줘야 한다는거다. 

복구상황 가정

위 과정처럼 백업이 매일 새벽에 처리되고 있었으며 오늘이 만약 수요일 업무중인데 데이터가 망실됐다고 가정해보자.

가장 최근 백업데이터는 오늘 새벽 증분백업이다. 이번주 새벽에 풀백업이 있었으므로 일요일(풀백업), 월요일(증분), 화요일(증분), 수요일(증분)을 모두 합치면 오늘 새벽까지의 데이터를 모두 살릴수 있다.

apply-log 로 증분백업을 풀백업에 합쳐주고 copy-back으로 복구시켜보자.

apply-log 작업은 풀백업부터 시작해 오래된 데이터부터 진행한다.

1. 일요일 풀백업 로그반영 

이 다음에 증분백업 로그반영이 있으므로 반드시 --redo-only 를 써줘야 한다.

2. 월요일 증분 로그반영

3. 화요일 증분 로그반영

4. 수요일 증분 로그반영

증분백업의 마지막 로그반영이므로 redo-only는 필요없다.

5. 마지막으로 일요일 풀백업 로그반영 한번더

6. apply-log 작업으로 모든 증분데이터들이 풀백업에 병합되었으므로 풀백업으로 복구를 하면 된다.

copy-back 이 끝나고 data_dir 의 소유권과 퍼미션을 맞춰주면 모든 복구작업이 완료된다.

XtraBackup을 이용한 InnoDB 증분백업과 복구하기 ( 2 ) 풀백업

Full Backup

기본 풀백업은 아래처럼 간단하게 할 수 있다.

가장 밑줄에 있는 completed OK! 가 떨어지면 백업이 정상 종료된거다.

내부적인 실행순서는 아래와 같다.

1. ibdata1 (시스템 테이블스페이스) 카피

2. InnoDB  파일 카피 (.ibd)

3. FLUSH NO_WRITE_TO_BINLOG TABLES 실행

4. FLUSH TABLES WITH READ LOCK 실행

5. non-InnoDB  파일카피 (.MYI, .MYD, .FRM)

6. UNLOCK TABLES 실행

7. backup-my.cnf 생성

8. xtrabackup_info 생성

9. 종료

/Backup 디렉토리 안에 현재 날짜/시간으로 디렉토리가 만들어지고 그 안에 아래와 같이 백업이 된다.

backup-my.cnf : 백업&복구에 필요한 몇가지 옵션이 저장되어 있다. my.cnf자체를 백업한게 아니다.

xtrabackup_checkpoints : 아래와 같은 내용을 담고 있다. 백업타입과 lsn 정보등. 

backup_type = full-backuped

from_lsn = 0

to_lsn = 1667732673

last_lsn = 1667732673

compact = 0

recover_binlog_info = 0

xtrabackup_info : 백업 실행시의 각종 상태(백업시간,사용한 옵션,버전정보등등..)를 저장.

백업시 추가적으로 사용 할 수 있는 옵션들

--host : 원격 백업일때 디비서버 아이피를 적어주면 된다. 안쓰면 기본이 localhost

--port : 안쓰면 기본이 3306

--defaults-file : 안쓰면 기본이 /etc/my.cnf 

--backup : 안쓰면 기본이 --backup, --apply-log 나 --copy-back 등을 쓸수 있음.

--no-timestamp : 날짜/시간으로 된 디렉토리를 생성하지 않는다. 지정한 경로에 바로 백업파일을 생성한다.

그리고 성능 개선을 위해...

--parallel : .ibd 데이터 파일 복사시 병렬로 처리한다. --parallel=4

InnoDB가 아니거나 innodb_file_per_table 을 사용하지 않을때는 당연히 성능향상은 없다.

병렬처리 옵션을 줘도 MyISAM 이나 InnoDB의 .frm 파일은 병렬처리가 안된다.

--compress_threads : 압축백업시 압축수행하는 쓰레드 수 지정.

로그반영

이전글에서 설명했듯 백업만 완료된 데이터는 inconsistent 하기 때문에 이 데이터로는 복구를 할 수 없다.

그렇기 때문에 꼭 백업된 리두로그를 백업된 데이터파일에 반영해줘야한다.

로그반영은 아래처럼 --apply-log 옵션과 함께 백업된 디렉토리를 지정해주면 된다.

백업때와 마찬가지로 가장 밑줄에 있는 completed OK! 가 떨어지면 로그반영로이 정상 종료된거다.

이제 이 파일들은 완전히 consistent 한 데이터가 되었고 이 데이터들로 복구를 할 수 있게 되었다.

로그반영시 추가할수 있는 옵션

로그반영 작업을 좀 더 빠르게 하기위해 --use-memory 옵션을 사용할 수 있다.

지정하지 않으면 기본값이 100M 로 셋팅되는데 로그반영은 보통 복구 직전에 진행하기에 디비도 내려간 상태이므로

메모리 여유량이 충분할 것이다. 넉넉히 셋팅해주자. 

복구

로그반영까지 완료된 상태에서 복구는 아주 간단하다. 실행옵션 3가지중 --copy-back 을 써주면 된다. 

다시 한번 알아보는 실행옵션 3가지

복구시 주의사항

- 복구전에 datadir 디렉토리는 비어있어야 한다. --copy-back 은 기존파일을 덮어쓰지 못한다. 

  datadir 이 비어있지 않을때는 -force-non-empty-directories 옵션을 이용할 순 있지만 기존 파일을 덮어쓰는 무모한 도전은

  의미가 없다. mv 를 이용해 기존 datadir 이름을 바꾸고 mkdir 로 새로 생성해주자.

- partial backup을 임포팅하는 작업이 아니라면 반드시 디비는 내려간 상태여야 한다.

- 사실 로그반영이 완료된 데이터는 이미 consistent 하므로 --copy-back 을 이용하지 않고 백업디렉토리를 그대로 datadir 로 옮겨도 무방하다. 하지만 운영에 필요없는 파일들도 같이 들어가므로 특별한 이유가 아니라면 정석대로 --copy-back을 사용하도록 하자.

역시 위 작업들과 동일하게 innobackupex: completed OK! 가 떨어지면 정상종료 된것이다.

내부적인 복구 과정은 아래와 같다.

1. 데이터파일(.ibd)과 메타데이터(.frm) 카피

2. 시스템테이블 스페이스(iddata1) 카피

3. 언두테이블 스페이스 (따로 있지 않다면 생략)

4. 리두로그(ib_logfile0..) 카피

복구된 데이터의 퍼미션을 확인해보고 mysql 권한이 아니라면 반드시 수정하고 디비를 올려보자.

이상없이 데몬이 잘올라온것을 확인할 수 있다. 예상 못했던 문제가 있을수 있으므로 errorlog 를 꼭 확인하도록 한다.

풀백업정리

풀백업 

/data/backups에 오늘날짜 디렉토리에 백업파일 생성

/data/backups에 백업파일 생성

로그반영

메모리 4기가 할당해 /data/backups/2016-03-01_01-00-00 백업디렉토리에 대한 로그반영

복구

백업데이터 /data/backups/2016-03-01_01-00-00 를 datadir 로 복구. datadir 경로는 당연히 my.cnf 참조

XtraBackup을 이용한 InnoDB 증분백업과 복구하기 ( 1 ) 설치

XtraBackup

XtraBackup은 percona사에서 만든 무료 백업 솔루션이다. Percona server, MySQL, MariaDB 등을

백업할 수 있으며 흔히 쓰는 mysqldump 와 다르게 무려! 증분백업이 된다.

XtraBackup의 장점은 너무 많다.

pausing 없이 핫백업이 되고, 증분백업이 되고, 로컬이 아닌 다른서버로 압축전송도 가능하고...등등...

XtraBackup 원리

XtraBackup은 InnoDB엔진의 자동복구 기능과 비슷한 원리다. 백업이 실행되면 데이터 파일들을 카피 하기 시작한다. 

우리가 이미 알고 있는 cp 와 크게 다르지 않은데 차이점은 카피가 시작된 이후부터 발생되는 변경사항을 별도의 파일에 

기록한다는 것이다. 이 파일을 백업된 리두로그 라고 한다.

카피작업이 끝나고 백업된 리두로그를 카피한 백업데이터에 반영해 consistent 한 데이터로 만들어주면 백업이 완료된다. 

설치

https://www.percona.com/downloads/XtraBackup/

Percona 홈페이지에서 원하는 OS와 버전을 선택해 바로 다운 받을수 있다.

테스트 서버가 CentOS 6.4 x64라서 percona-xtrabackup-24-2.4.3-1.el6.x86_64.rpm 파일을 받았다.

xtrabackup 설치시 libev 라이브러리가 필요하므로 먼저 설치해준다.

libev는 기본 repo에는 없고 epel에서 찾을수 있다.

설치내역은 굉장히 단촐하다. 백업 및 복구에 있어 핵심파일은 /usr/bin/xtrabackup 이고

실제 작업시에는 innobackupex를 사용한다. 

inoobackupex는 C로 만들어진 xtrabackup를  내부적으로 호출하는 펄스크립트로

스트리밍이라던지 추가 기능들을 더 쉽게 사용할수 있게 해준다.

그런데 2.1 버전은 분명 perl 스크립트였는데 2.4 버전은 바이너리로 바뀌어 있더라...

암튼.. 그래서 이후로 모든 작업은 innobackupex 를 사용할 예정이고 xtrabackup 을 직접 실행하지 않을것이다.

InnoDB Buffer Pool 덤프 & 로드

InnoDB는 자주 사용하는 데이터를 버퍼풀에 모두 올려놓고 사용하는데 서버나 데몬이 

재가동될시에는 모든 캐시를 잃게 된다. 그래서 재가동 직후에는 디스크에서 모든 데이터를 

다시 읽어와야 되기 때문에 과부하가 걸리기 쉽다. 

물론 임의로 테이블과 인덱스를 풀스캔하면서 워밍업을 해줄순 있겠지만 사용량이나 빈도에 따라

잘~ 정리되어있던 예전 버퍼풀만 못할것이다. 

그래서 서버를 내리기 직전의 현재 버퍼풀을 덤프해 저장해두고 서버 재기동시 덤프를 복구해

재기동으로 인한 과부하를 막을수 있는 방법을 알아보도록 하자.

설정변수

Mariadb 10.1 기준으로 innodb_buffer_pool 을 검색해보면 총 10개가 나오는데

버퍼풀 덤프&로드 작업에 필요한것도 있고 아닌것도 있지만.... 일단 나왔으니 다 정리해보자.

- innodb_buffer_pool_dump_at_shutdown

이 옵션이 ON으로 설정되어 있으면 서버가 정상종료시 자동으로 버퍼풀을 덤프한다.

- innodb_buffer_pool_dump_now

이 옵션의 값은 항상 OFF 이고 ON 으로 바꾸게 되면 그 즉시 버퍼풀을 덤프한다. 덤프가 끝나면 자동으로 OFF로 바뀐다.

- innodb_buffer_pool_dump_pct

버퍼풀의 얼마만큼을 덤프할껀지에 대한 퍼센트 값이다. 버전에 따라 기본값이 다르다.

- innodb_buffer_pool_filename

자동이든 수동이든 덤프된 파일명이다. 경로는 기본적으로 data 디렉토리다.

- innodb_buffer_pool_instances

버퍼풀을 운영할 인스턴스를 몇개나 띄우건지 지정해준다.

- innodb_buffer_pool_load_abort

버퍼풀을 로드하고 있을때 이 값을 ON으로 바꿔주면 로드가 취소된다.

- innodb_buffer_pool_load_at_startup

이 옵션이 ON으로 설정되어 있으면 서버 구동시 자동으로 덤프된 버퍼풀을 로드한다.

- innodb_buffer_pool_load_now

이 옵션도 항상 OFF 이고 이 값을 ON으로 바꾸면 그 즉시 덤프된 버퍼풀을 로드한다.

- innodb_buffer_pool_populate

이 값이 ON이면 서버 구동시 지정된 버퍼풀 사이즈 만큼의 메모리를 모두 할당한다.

- innodb_buffer_pool_size

MySQL, MariaDB에서 가장 중요한 옵션. 인덱스와 데이터에 대한 캐시사이즈 값이다.

위 설정중에 innodb_buffer_pool_instances, innodb_buffer_pool_populate, innodb_buffer_pool_size

3가지는 덤프&로드와는 무관한 옵션들이다.

실행 조건

>= MySQL5.6, >= MariaDB10.0

버퍼풀 덤프하기

이건 실서버가 아니라 덤프파일이 9.8K 밖에 안되지만 실서버라 해도 용량이 그렇게 크지 않다.

버퍼풀 덤프는 메모리에 있는 모든 내용을 파일로 쓰는게 아니라 버퍼풀에 캐싱된 페이지들의 

id 와 no 값만을 저장하기 때문이다. 그래서 버퍼풀이 몇백기가라 할지라도 덤프파일은 몇백메가면 충분하다.

버퍼풀 로드하기

innodb_buffer_pool_load_status 상태값으로 로드 현황을 볼 수 있다.

예제로 사용한 덤프파일이 작아서 너무 금방끝나는 바람에 completed 화면만 볼 수 있는데

로드중일때는 Loaded 4444/9999 pages 와 같이 진행상태를 확인할수 있다.

MySQL PID 경로(파일명) 변경하기

DB : Maria 10.1

분명 예전엔 [mysqld] 섹션에 pid-file=경로 이렇게 쓴거 같은데... 이상하게 안되는거다.

버전이 올라가면서 바꼈나?... 싶어서 생각해보니 mysqld 가 아니라 왠지 mysqld_safe 일꺼 같은거다.

그래서 그쪽으로 설정을 바꿔 넣어보니 역시나 안된다.

에라이 모르겠다. 검색..ㄱㄱ 했지만 역시 내가 아는 것과 내용들은 비슷비슷...

결국 마지막으로 메뉴얼 ㄱㄱ.. 역시나 메뉴얼에 모든게 있다.

원문

요약하자면... 

pid-file 옵션은 PID값을 저장하고 있는 파일을 지정하는 옵션이고

별다른 설정을 안하면 DATA_DIR/HOST_NAME.pid 라는 경로 및 파일명을 갖는다.

mysql.server 스크립트를 이용해 서비스를 구동/정지 할 시에는 

my.cnf의 [mysqld] 섹션과 [mysqld_safe] 섹션에 pid-file 옵션을 모두 넣어줘라....인듯..? 

그래서 두군데 섹션에 모두 넣어주고 

확인했더니.. 잘바껴져있다.