MySQL 백업 도구 사용법 (mysqldump, xtrabackup, innobackupex)

9.1 mysqldump

---

필요한 권한

• SELECT
• SHOW VIEW
• TRIGGER
• LOCK TABLES
• FILE
• CREATE
• ALTER DATABASE

사용법과 옵션

mysqldump 도구는 MySQL 클라이언트 프로그램으로, MySQL이 설치된 디렉터리에서 mysql-mysqladmin과 같은 클라이언트 프로그램과 같은 디렉토리에 들어있음.

전체 데이터 베이스 백업하기

mysqldump --all-databases [기타 옵션] -u[유저명] -p[패스워드] > /data/backup.sql

• 백업한 결과를 특정 파일로 저장하고 싶다면 리다이렉션(”>”)을 이용해 백업 결과 파일을 명시적으로 지정
• mysqldump가 백업을 수행할 땐 데이터를 순차적으로 읽어서 출력하므로 디렉터리의 유휴 공간이 없으면 수행중인 백업이 중간에 멈춘다. 따라서 백업을 수행하기 전에 디렉터리의 여유 공간이 충분한지 미리 확인 진행
• --all-databases : 백업 대상 데이터베이스 전체를 백업

특정 데이터베이스만 백업하기

mysqldump --databases testdb [기타 옵션] -u[유저명] -p[패스워드] > /data/testdb_backup.sql

• --databases 옵션을 주고 그 뒤에 백업한 데이터베이스 이름 명시
• 하나 이상의 데이터베이스를 한 번에 백업하기 위해서는 --databases 옵션 뒤에 testdb1 testdb2 testdb3 와 같이 빈칸으로 구분해서 데이터베이스명 나열

특정 테이블만 백업하기

mysqldump --tables testdb table1 table 2[기타 옵션] -u[유저명] -p[패스워드] > /data/testdb_backup.sql

• --tables 옵션은 --databases 하위 옵션
• 한 번의 명령으로는 하나의 데이터베이스에 속한 테이블만 백업할 수 있다.

mysqldump 옵션

A. 접속에 사용되는 옵션

--user=[유저명], -u [유저명] 백업 대상 MySQL 서버에 접속하기 위한 MySQL 서버 계정의 이름

--password=[패스워드], -p [패스워드]	백업 대상 MySQL 서버에 접속하기 위한 MySQL 서버 계정의 암호
--host=[호스트], -h [호스트이름]	백업 대상 MySQL 서버의 호스트 이름. 기본 값은 loaclhost
--port=[포트번호], -P [포트번호]	접속에 사용할 TCP/IP 포트 번호. (3306)
--socket=[소켓 파일], -S [소켓 파일]	리눅스나 유닉스 운영체제에서 백업 대상 MySQL 서버에 접속할 때 사용하는 소켓 파일
ex) --socket=/tmp/mysql.sock
--compress, -C	데이터 압축 백업 명령을 실행하는 클라이언트와 백업 대상 MySQL 서버가 모두 압축 기능을 지원한다면 클라이언트와 서버 간에 전송되는 모든 정보를 압축
--login-path=[로그인 경로 파일 이름]	MySQL 5.6.6 버전부터 추가된 옵션으로, 백업 대상 MySQL 서버에 접속할 때, user, password 옵션이 아닌 ㄴ별도의 로그인 경로 파일로 접속할 때 사용

B. DDL 옵션

--add-drop-database DROP DATABASE 구문 추가하여 create database 전에 기존 database 삭제

--add-drop-table	DROP TABLE 구문 추가하여 table 생성전 기존 table 삭제
--add-drop-trigger	CREATE TRIGGER 구문 이전에 DROP TRIGGER 구문을 추가하여 트리거를 복원하기 전에 대상 트리거를 명시적으로 삭제
-n, --no-create-db	CREATE DATABASE 구문 제외
-t, --no-create-info	CREATE TABLE 구문 제외
--replace	백업 파일에 작성되는 INSERT 구문을 REPLACE 구문으로 기록하고자 할 때 사용하는 옵션

C. 디버그 옵션

--allow-keywords 칼럼명을 예약어로 생성하는 것을 허용하는 옵션

mysqldump의 장점과 단점

장점

• 백업 결과 파일을 조회하면 백업된 테이블의 스키마 구조와 데이터를 SQL 구문으로 직접 확인할 수 있으며, 필요하다면 파일을 직접 수정해 복원에 사용 가능
  • 백업 결과 파일을 직접 수정할 때는 문자열 인코딩이 일치해야함
• 사용자가 원하는 형식과 데이터만 백업 가능
• 대용량이지만 장애가 발생해도 데이터 자체는 복원할 필요가 없는 단순 로그 성격의 데이터만 저장된 데이터베이스라면 스키마 구조만 배겅ㅂ해 백업 결과 파일의 크기를 줄이면 유휴 공간 확보 가능

단점

• 크기가 큰 데이터베이스를 백업하고 복원하는데 걸리는 시간이 길다
  • 옵션을 이용해 어느정도 개선 가능
  • 복원할 때 SQL 문장을 실행하면서 발생하는 디스크 부하를 비롯해 인덱스 생성으로 인한 데이터 정렬 작업 등에서 발생하는 부하는 성능 관련 옵션으로 해결 불가.
• SQL구문을 실행해 데이터를 새로 적재하는 논리적인 백업 복원 방식은 시간이 오래걸리기 때문에, 백업 대상 데이터의 크기가 크다면 실제 데이터 파일을 백업하는 물리적인 백업 방식 권고
  • MySQL 엔터프라이즈 백업 프로그램, Percona의 Xtrabackup

9.2 Percona Xtrabackup

---

Percona의 Xtrabackup은 내부적으로 innobackupex 스크립트 또는 xtrabackup 바이너리를 사용해 백업 수행 가능

innobackupex 는 내무벅으로 xtrabackup을 호출해 백업 실행.

• xtrabackup 실행파일만 이용하여 백업 : InnoDB 또는 XtraDB 스토리지 엔진의 데이터 파일만 백업
• innobackupex 스크립트 이용하여 백업 : InnoDB 또는 XtraDB 스토리지 엔진의 데이터 파일을 백업한 후 테이블 스키마 정의 파일인 .frm 과 기타 엔진 테이블의 파일을 백업하는 과정 진행

권한

• RELOAD, LOCK TABLES
• REPLICATION CLIENT

옵션

공통 옵션

-user=USER 현재 사용자가 아닌 경우 로그인 할 사용자 (즉, 서버에 연결할 때 사용되는 MySQL 사용자 이름)를 지정하는 문자열 인수를 허용합니다. 변경 없이 mysql 하위 프로세스로 전달된다.

-password=PASSWORD	이 옵션은 데이터베이스에 연결할 때 사용할 암호를 지정하는 문자열 인수를 승인합니다. 변경 없이 mysql 하위 프로세스로 전달된다.
-port=PORT	이 옵션은 TCP/IP로 데이터베이스 서버에 연결할 때 사용할 포트를 지정하는 문자열 인수를 승인합니다. 그것은 mysql 하위 프로세스로 전달된다. 변경없이 mysql 하위 프로세스로 전달된다.
-defaults-file=[MY.CNF]	이 옵션은 디폴트 MySQL 옵션을 읽을 파일을 지정하는 문자열 인수를 허용합니다. 명령줄에서 첫 번째 옵션으로 지정해야 합니다.
-export	이 옵션은 xtrabackup --export 옵션에 직접 전달됩니다. 개별 테이블을 다른 서버로 가져 오기 위해 내보낼 수 있습니다.
-tables-file=FILE	이 옵션은 한 줄에 하나씩 database.table 형식의 이름 목록이있는 파일을 지정하는 문자열 인수를 허용합니다. 이 옵션은 xtrabackup의 --tables-file 옵션에 직접 전달됩니다.
--stream	TAR파일과 같이 백업된 파일을 스트림 형태로 묶는 옵션
-compress	이 옵션은 InnoDB 데이터 파일의 백업 복사본을 압축하도록 xtrabackup에 지시합니다. xtrabackup 하위 프로세스로 직접 전달됩니다.
-compress-threads = #	이 옵션은 병렬 압축에 사용될 worker threads의 수를 지정합니다. xtrabackup 하위 프로세스로 직접 전달됩니다.
-parallel=NUMBER-OF-THREADS	이 옵션은 xtrabackup 하위 프로세스가 파일을 동시에 백업해야 하는 스레드 수를 지정하는 정수 인수를 채택합니다. 이 옵션은 파일 수준에서 작동합니다. 즉, .ibd 파일이 여러 개 있으면 병렬로 복사됩니다. 테이블이 단일 tablespace 파일에 함께 저장된 경우에는 아무 효과가 없습니다.
-incremental	이 옵션을 사용하면 xtrabackup이 전체 백업이 아닌 증분 백업을 생성합니다. xtrabackup 하위 프로세스로 전달됩니다. 이 옵션을 지정하면 --incremental-lsn 또는 --incremental-basedir 중 하나를 지정할 수도 있습니다. 두 옵션을 모두 지정하지 않으면 기본적으로 --incremental-basedir 옵션이 xtrabackup에 전달되고 백업 기본 디렉토리의 첫 번째 타임 스탬프 백업 디렉토리로 설정됩니다.
-incremental-basedir = DIRECTORY	이 옵션은 증분 백업의 기본 데이터 세트인 전체 백업을 포함하는 디렉토리를 지정하는 문자열 인수를 허용합니다. --incremental 옵션과 함께 사용됩니다.
-incremental-dir = DIRECTORY	이 옵션은 증분 백업이 전체 백업과 결합되어 새로운 전체 백업을 만들 디렉토리를 지정하는 문자열 인수를 허용합니다. --incremental 옵션과 함께 사용됩니다.
-compact	모든 secondary index 페이지가 생략 된 압축 백업을 생성하십시오. 이 옵션은 xtrabackup에 직접 전달됩니다.

<aside> 💡 ⭐ --compress 옵션을 사용할 때 주의사항 xtrabackup과 innobackupex 를 실행할 때, compress 옵션을 사용하면 파일을 압축해 확장자가 .qp인 파일로 백업된다. .qp파일은 qpress 를 이용해 압축을 푼 뒤에 복구 단계를 수행한다. innobackupex 를 사용한다면 --decompress 옵션을 지정해 압축을 풀지만 내부적으로는 qpress를 사용하게 되므로 반드시 qpress가 설치되어 있어야한다.

</aside>

<aside> 💡 c.f) xtrabackup을 이용해 증분백업을 수행할 때 반드시 전체 백업 파일이 필요하다. 증분 백업은 전체 백업 파일을 기반으로 백업 완료 이후의 시점부터 데이터 파일에 변경된 블록을 증분으로 백업한다.

1. 일요일에 전체 백업을 수행했고, 이 전채 백업은 LSN이 0부터 100까지에 대한 백업 파일
2. 다음날인 월요일에는 일요일의 전체 백업 파일을 기반으로 백업이 완료된 LSN=100시점부터
3. 데이터 파일에 변경된 블록이 있는지, 즉 데이터 파일의 LSN이 100보다 큰 값을 가지는 블록이 존재한다면 LSN=100시점부터 백업

증분 백업은 마지막 백업 시점을 기반으로 데이터 파일에서 변경된 블록의 부분만을 백업함으로써, 매일 전체 백업을 수행하는 것보다 백업 파일의 용량을 축소할 수 있다. 그러나 증분 백업은 백업할 때마다 내부적으로 전체 데이터 파일을 스캔해 마지막 백업 시점의 LSN보다 큰 값을 가지는 데이터 블록을 찾는 작업을 수행하므로 백업 파일의 용량이 줄어드는 것일 뿐 전체 백업과 비교해 백업 수행 속도가 월등히 빠르진 않다.

</aside>

xtrabackup 옵션

--target_dir 백업과 복구에 사용하는 옵션으로, 백업할 때는 백업 결과 파일이 저장되는 경로를 지정하며 복구할 때는 복구를 수행할 파일 경로를 지정한다.

--backup	Xtrabackup으로 백업을 수행하기 위해 기본적으로 사용되는 옵션
--datadir	백업이 필요한 대상 데이터베이스의 데이터 파일 경로
--prepare	백업 파일을 복구할 때 사용되는 주 옵션

1. 백업 시점에 복사한 데이터 파일에 백업 수행 중에 변경된 데이터를 적용해 달라진 시점을 맞출 때 사용
2. 복구 과정을 수행할 때 InnoDB 로그 파일을 생성하는 용도로 사용 | | --apply-log-only | 증분 백업 파일을 복구할 때 사용하는 옵션. 백업 수행 중에 변경된 데이터에 대해 커밋되지 않은 트랜잭션의 롤백은 제외하고 커밋된 트랜잭션에 대한 롤 포워드만 진행 |

innobackupex 옵션

| --include | 백업하려는 대상 객체는 지정하는 옵션이며, 백업 대상은 정규 표현식으로 지정

--include=”^testdb ^testlogdb”

--databases=LIST	이 옵션은 innobackupex가 백업해야 하는 데이터베이스 목록을 지정합니다. 이 옵션은 백업 할 데이터베이스 목록이 들어 있는 파일에 대한 문자열 인수 또는 경로를 허용합니다. 목록의 형식은 "databasename1 [.table_name1] databasename2 [.table_name2]입니다. . . " 이 옵션을 지정하지 않으면 MyISAM 및 InnoDB 테이블을 포함하는 모든 데이터베이스가 백업됩니다. -databases에 모든 InnoDB 데이터베이스와 테이블이 포함되어 있는지 확인하여 모든 innodb.frm 파일도 백업하도록 하십시오. 목록이 매우 긴 경우 파일에 이 목록을 지정할 수 있으며 목록 대신 파일의 전체 경로를 지정할 수 있습니다. (-tables-file 옵션을 참조하십시오.)
--slave-info	레플리케이션으로 구성된 슬레이브에서 백업을 수행할 때 유용한 옵션
백업 완료 시 생성되는 xtrabackup_slave_info 파일에 CHANGE MASTER TO 명령 구문으로 기록되므로, 새로운 슬레이브를 구성할 때 활용 가능
-apply-log	Xtrabackup의 --prepare 옵션을 사용하는 단계

1. 백업할 때 생성된 xtrabackup_logfile을 이용해 백업 데이터에 트랜잭션 로그 적용
2. logfile을 재생성. logfile의 크기나 개수는 백업할 때 생성된 backup-my.cnf 파일에 명시되어있는 iblogfile 형식을 바탕으로 생성

--apply-log 단계가 끝나면 데이터를 사용할 수 있는 단계가 됨 | | --copy-back | 복구된 파일을 복구 영역의 데이터 경로로 일괄적으로 옮기는 기능. --defaults-file 옵션에 복구될 데이터베이스의 my.cnf 위치를 지정하면 옵션 파일의 datadir, ib_logfile 의 위치 정보로 복구 데이터를 옮긴다. --copy-back 옵션이 동작하려면 두 가지 조건 필요

1. 복구될 MySQL 서버의 데이터 파일 경로(datadir 시스템 변수)의 위치가 비어있어야 한다.
2. 복구될 MySQL 서버는 shutdown 상태여야한다. --defaults-file 옵션을 설정하지 않으면 기본적으로 /etc/my.cnf 의 위치를 읽어 동작 | | -decompress | 이전에 -compress 옵션으로 작성된 백업에서 확장자가 .qp 인 모든 파일의 압축을 풉니다. innobackupex --parallel 옵션을 사용하면 여러 파일을 동시에 암호해독 또는 압축해제 할 수 있습니다. 압축을 풀기 위해 경로 내에 qpress 유틸리티를 설치하고 액세스 할 수 있어야 합니다. Percona XtraBackup은 압축 파일을 자동으로 제거하지 않습니다. 백업 디렉토리를 지우려면 * .qp 파일을 수동으로 제거해야 합니다. |

<aside> 💡 backup.lst 파일을 작성해 databases 옵션 값으로 지정

$ vi backup.lst testdb testlogdb

backup.lst 파일 생성한 뒤, --database 옵션에 파일명을 지정해서 사용 --database=backup.lst

</aside>

Xtrabackup의 동작 원리

Xtrabackup은 InnoDB의 crash-recovery 기능을 기반으로 동작.

백업이 시작된 후에, 데이터가 계속해서 변경되는 중이라면 crash-recovery를 수행하여 사용 가능한 일관된 상태의 데이터 파일로 백업 가능

• crash-recovery 동작은 InnoDB의 트랜잭션 로그인 리두로그(ib_logfile)로 인해 가능하다.
• 백업이 수행되는 중에 리두 로그에 기록도니 변경사항을 데이터 파일과 함께 백업

백업 단계

1. 백업 시작 시점 LSN 기록. 데이터 파일 복구할 때 데이터 파일 일관성을 맞추기 위해 사용
2. 데이터 파일 복사 시작. 백그라운드 프로세스가 추가로 가동되며, 리두 로그를 감시해 데이터가 변경할 때마다 xtrabackup_logfile 에 변경 내용 백업

여기까지는 xtrabackup 의 백업 작업 innobackupex 로 백업하면 다음의 세가지 작업이 추가로 진행됨. 용도는 InnoDB, XtraDB 스토리지 엔진을 제외한 MyISAM 및 기타 스토리지 엔진과 .frm 파일을 백업하는 것 3. 데이터가 변경되는 것을 박기 위해 FLUSH TABLES WITH READ LOCK 명령 실행. 이 후에 유입되는 DML, DDL 쿼리 모두 대기 4. MyISAM 스토리지 엔진 테이블을 모두 메모리에서 디스크로 플러시해서 데이터 동기화 후, MyISAM 스토리지 엔진과 .frm 파일 모두 복사 5. UNLOCK TABLE 명령을 수행해 LOCK 해제 6. MyISAM, InnoDB, XtraDB 스토리지 엔진 테이블은 동일한 시점에 백업 종료. xtrabackup_checkpoints 파일에 백업의 시작 시점과 마지막 시점의 LSN 기록됨

<aside> 💡 FLUSH TABLES WITH READ LOCK 명령을 실행하는 이유 Innobackupex는 InnoDB, XtraDB 스토리지 엔진과 그 밖의 엔진을 사용하는 테이블 데이터의 백업 시점을 동일하게 보자할 수 있어야 하기 때문.

</aside>

복구 단계

prepare 단계라고도 함.

백업이 시작되고 종료되는 동안 백업 대상 데이터베이스에 발생한 변경사항을 적용해 백업된 데이터를 백업 시점으로 일관되게 맞춰주는 작업

1. 백업 도중에 변경된 데이터를 백업한 리두 로그인 xtrabackup_logfile 파일을 백업된 데이터 파일에 적용. 커밋된 트랜잭션이 변경한 데이터 블록은 데이터 파일에 적용하며 이 작업을 롤 포워드(roll forward)라 한다. 커밋되지 않은 트랜잭션이 변경한 데이터 블록은 원래의 파일 상태로 돌리는 롤백(rollback)수행
2. 복구 단계를 수행할 때는 xtrabackup, innobackupex 에서 사용하는 옵션이 다름.

-1) xtrabackup을 사용한다면 prepare 옵션을 두 번 사용해 복구를 수행함. 첫번째 단계 : xtrabackup_logfile을 백업된 데이터 파일에 적용해 일관성 맞추기 두번째 단계 : prepare 단계까지 완료해 생성된 데이터 파일과 리두로그 복구 대상 데이터베이스의 my.cnf 에 설정한 데이터 파일 경로와 리두 로그 경로에 위치시킨 뒤 데이터베이스를 시작하면 백업이 완료된 시점으로 복구 완료

```bash
$ xtrabackup --target-dir=/backup/xtrabackup --prepare
$ xtrabackup --target-dir=/backup/xtrabackup --prepare
```

-2) --apply-log 옵션을 주고 innobackupex 를 한 번 실행하는 것은 xtrabackup에 prepare 옵션을 사용해 두 번 실행하는 것과 동일하게 동작 

```bash
$ innobackupex /backup/xtrabackup --apply-log
```

추가 기능을 위한 도구

원격 백업을 가능하게 하는 xbstream이 있다.

• Xtrabackup으로 백업을 수행한 결과로 생성된 여러 개의 백업 파일을 하나의 파일로 묶어서 관리하기 위함.
• innobackupex 에 --stream 옵션으로 사용 가능
• 여러 개의 백업 파일을 하나의 스트림 파일로 생성. 스트림 파일 해제할 때는 xbstream 사용