[MYSQL] Cold backup, Hot backup, Logical backup, Physical Bakcup

Backup 비교

핫 백업 (Hot Backup/Open Backup)	DB 서버를 온라인 상태로 유지한 채 데이터 백업	- 데이터베이스 서버를 중지하지 않고 백업 가능 - 트랜잭션, 스냅샷, 로그 등을 이용하여 실시간 백업 - Archive Log 모드에서만 백업을 수행할 수 있음 - 디스크 용량이 추가로 요구될 수 있음 - 핫 백업 도구를 사용할 때의 비용이 발생할 수 있음
콜드 백업 (Cold Backup/Close Backup)	DB 서버를 중지한 후 데이터 백업	- 쉬운 백업이 가능함 - 데이터 백업을 요청한 시기 이후의 데이터는 백업할 수 없음 (따라서 일부 데이터 손실이 발생할 수 있음)
논리 백업 (Logical Backup)	각 오브젝트를 SQL문 등으로 저장	- 데이터의 쉬운 검토 가능 - 데이터 백원 및 복원 시의 안정성을 증가시킴 - 백업과 복원 속도가 느리고 작업 시 시스템 자원을 많이 차지하게 됨 - 다른 PC(서버)간의 데이터 이전이 용이함
물리 백업 (Physical Backup)	파일 자체를 그대로 백업	- 빠른 속도의 백업과 복원 - 디스크 용량을 크게 차지함 - 문제 발생에 대한 파악과 검토가 어려움 - 다른 PC(서버)간의 데이터 이전이 어려울 수 있음

 

 

Cold backup 

1. mysql datafile 위치 확인

mysql> show variables like 'datadir'; +---------------+--------------+ | Variable_name | Value        | +---------------+--------------+ | datadir       | /data/mysql/ | +---------------+--------------+ 1 row in set (0.06 sec) -. my.cnf location 확인 mysql --help | grep my.cnf                       order of preference, my.cnf, $MYSQL_TCP_PORT, /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

2. mysql 기동중지

 

3. datafile copy

cp /data/mysql /data/backup/mysql

 

Hot backup

 

1. mysqlhotcopy를 사용하는 방법

-. mysql 백업 방법중 속도가 빠르며 DB 디렉토리를 다른위치에 Copy 한다.

-. Myl및 Archive 테이블만 Hotbackup을 지원한다. (InnoDB 지원하지 않는다.)

-. mysqlhotcopy는 5.6버전에서 지원되었지만 5.7 버전에서는 제거 되었다.

mysqlhotcopy [db_name./table_name/] [디렉토리경로]

 

 

2. xtraback을 사용하는 방법

-. mysql 서버 중단하지 않고 InnoDB를 Hot Backup 할 수 있다. 

-. InnoDB Hot Backup을 지원

-.xtrabackup 2.4 이하 버전은 EOS 됨

-. 2.4는 mysql 5.7 이하 버전에 대해 제공하고 있으며, 8.0은 MYSQL8.0을 지원합니다.

-. Innobackupex 기능은 8.0에서부터는 제거 되었다.

# tar 다운로드 wget https://downloads.percona.com/downloads/Percona-XtraBackup-8.0/Percona-XtraBackup-8.0.35-30/binary/tarball/percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17.tar.gz or # Percona yum repository 설치 yum -y install https://repo.percona.com/yum/percona-release-latest.noarch.rpm   # Enable the repository: percona-release enable-only tools   # xtrabackup 설치 yum -y install percona-xtrabackup-80   # 압축 백업을 사용하기 위해 qpress 설치 yum -y install qpress

 

2.1 xtrabackup 백업용 DB 계정 생성 및 최소 권한 설정
※ xtrabackkup을 실행시키는 OS계정은 mysql DATA_DIR에 대한 Read, Write, Execute 권한이 필요하다.

CREATE USER 'xtrabackup'@'%' IDENTIFIED BY 'xtrabackup'; GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'xtrabackup'@'%'; GRANT SELECT ON performance_schema.log_status TO 'xtrabackup'@'%'; GRANT SELECT ON performance_schema.keyring_component_status TO 'xtrabackup'@'%'; FLUSH PRIVILEGES ;

 

 2.2 xtrabackup을 사용한 Full backup

xtrabackup --user=xtrabackup --password=xtrabackup --no-version-check --backup --socket=/data/mysql/mysql.sock --target-dir=/data/mysql_hotbackup_test1

 

2.3 위에서 version_check 에러 발생하는 경우

xtrabackup --backup --user=root --password -pxtrabk --socket=/data/mysql/mysql.sock --target-dir=/data/mysql_hotbackup_test1 221018 10:24:33  version_check Connecting to MySQL server with DSN ' . . cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: 동적 오브젝트 파일을 열 수 없습니다: 그런 파일이나 디렉터리가 없습니다 . . Failed to connect to MySQL server: Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/local/xtrabackup/lib/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory.

version_check 에러 제거를 위해 --no-version-check 옵션 또는 계정 인증 플러그인 변경      (caching_sha2_password -> mysql_native_password)     alter user 'xtrabackup'@'%' identified with mysql_native_password by 'xtrabackup';   mysql> select user, host, plugin from mysql.user     -> where user = 'xtrabackup'; +------------+------+-----------------------+ | user       | host | plugin                | +------------+------+-----------------------+ | xtrabackup | %    | mysql_native_password |

 

2.4 full backup을 이용한 복구

2.4.1 mysql 중지 systemctl stop mysql   2.4.2 백업파일 무결성, 일관성 검증실행 xtrabackup --prepare --target-dir=/data/mysql_hotbackup_test1/   2.4.3기존 data direcotry 이름 변경 mv /data/mysql /data/mysql_old   2.4.4  xtrabackup 복구 명령어 xtrabackup --copy-back --target-dir=/data/mysql_hotbackup_test1/ --datadir=/data/mysql 2.4.5 mysql start systemctl start mysql

 

2.5 xtrabackup을 사용한 증분백업

2.5.1 증분백업 xtrabackup --user=xtrabackup --password=xtrabackup --no-version-check --backup --socket=/data/mysql/mysql.sock --target-dir=/data/mysql_hotbackup_test1_inc1 \ --incremental-basedir=/data/mysql_hotbackup_test1   xtrabackup --user=xtrabackup --password=xtrabackup --no-version-check --backup --socket=/data/mysql/mysql.sock --target-dir=/data/mysql_hotbackup_test1_inc2 \ --incremental-basedir=/data/mysql_hotbackup_test1_inc1   2.5.2 복구 준비 백업 시 커밋 되지 않은 트랜잭션이 진행 중일 수 있으며, 다음 증분 백업에서 커밋 될 가능성이 있으므로 백업 복원을 준비할 때 커밋 되지 않은 트랜잭션의 롤백 단계를 방지하기 위해 --apply-log-only 옵션 사용   xtrabackup --prepare --apply-log-only --target-dir=/data/mysql_hotbackup_test1   xtrabackup --prepare --apply-log-only --target-dir=/data/mysql_hotbackup_test1 --incremental-basedir=/data/mysql_hotbackup_test1_inc1   -- 마지막 증분백업을 적용할떄는 --apply-log-only 옵션은 적용하지 않는다!!! xtrabackup --prepare  --target-dir=/data/mysql_hotbackup_test1 --incremental-basedir=/data/mysql_hotbackup_test1_inc2   2.5.3 복구 xtrabackup --copy-back --target-dir=/data/mysql_hotbackup_test1/ --datadir=/data/mysql

 

3. bin log를 이용한 완전 복구 및 시점 복구
-. mysql dump, xtraback 백업 모두 백업시점까지 복구를 하는 불완전 복구이다.

 

-. bin log를 이용하여 백업 시점 후, 장애 발생 시점까지 복구를 할 수 있다. 

-. bin log란? 

-. 서버 내에서 발생되는 모든 변경 내역이 기록되는 파일이다.  -. DML, DDL의 모든 명령문이 기록되며 데이터는 'event' 형태로 저장되어 있다.  -. 각 명령문이 데이터를 업데이트하는 소비시간 정보를 가지고 있다. -. select or show와 같이 수정하지 않는 명령문은 기록되지 않는다. -. 시점복구(Point In Time)와 리플리케이션의 필수 요소이다.

3.1 bin log ON/OFF 상태 확인(mysql 8.0.22 기준으로 default는 on상태)

mysql> show variables like 'log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin       | ON    | +---------------+-------+ 1 row in set (1.31 sec) 3.1.1 bin log 관련 파라메터 -log-bin=bin-log DATA_DIR에 이름을 bin-log.00001와 같은 형식으로 남길 수 있습니다. -binlog_format=row 데이터 포맷을 row형식으로 지정할 수 있습니다. mysql 5.7.6 까지는 기본값이 statement이고 그 이후 버전은 row가 기본 값이다.  -binlog-do-db=test-db test-db에 대해 내용을 남긴다는 의미입니다. -max_binlog_size=256MB 빈로그 파일의 최대 크기입니다. 256MB가 넘어가면 2번으로 추가로 생성됩니다. -expire_logs_days=4 4일 이상 오래된 빈로그는 삭제할 수 있습니다.

 

3.2 bin log를 이용한 복구 

3.1.1 xtrabackup을 통해 받은 백업본을 통해 해당 백업시점까지 복구 mysqlbinlog /data/mysql_old/binlog.000021 > tmpfile.sql  -. sql로 추출하여 해당 sql 구문, 시간 확인 3.2.2 장애시점 전까지 생성된 bin log 모두 적용 mysqlbinlog binlog.000021 binlog.000022 binlog.000023 binlog.000024 binlog.000025 binlog.000026 binlog.000027 | mysql -u root -p or mysqlbinlog binlog.0000* | mysql -u root -p 3.2.3 시간기반 복구 mysqlbinlog --stop-datetime="2022-10-22 23:48:00" \          binlog.0000* | mysql -u root -p

 

 

 

- 추가로 bin log 로그 읽는 방법 학습

 

참고 사이트

https://it-sunny-333.tistory.com/m/73 https://velog.io/@wwcck/Percona-XtraBackup-for-MySQL https://myinfrabox.tistory.com/m/219 https://iamwhat.tistory.com/m/44

 

 

Logical Backup

https://origina1.tistory.com/48