[MySQL] mysqldump로 백업하기

1. 백업의 종류

백업의 종류에는 논리적 백업과 물리적 백업이 있다.

	논리적 백업	물리적 백업
장점	사람이 읽을 수 있다 특정 DB만 백업 가능 다른 버전으로 이동 가능	속도 빠름 대용량에 적합
단점	속도 느림 파일 용량 큼	같은 MySQL 버전이 필요 OS 영향이 있음

 

 

이외에도 백업 방식에 따라 여러 백업 명칭이 있다.

• Full Backup : 전체 DB를 백업
• Partial Backup : 특정 DB 또는 특정 테이블만 백업
• Hot Backup : 서비스를 중단하지 않고 백업
• Cold Backup : 서비스를 종료한 뒤 백업

 

 

2. mysqldump로 백업 파일 생성

mysqldump는 논리적 백업이다. 따라서 mysqldump로 백업을 한다는 것은 백업 파일(.sql)을 생성하는 것이다.

 

복구 후 비교를 위해 현재 데이터의 개수를 확인

 

 

mkdir ~/backup

mysqldump \
-u root -p \
--single-transaction \
--routines \
--triggers \
employees \
> ~/backup/employees_full.sql

위 명령으로 /backup/employees_full.sql 백업 파일이 생성된다.

 

* <참고> 아래와 같은 주요 옵션들이 있다.

• --single-transaction : 테이블에 락을 걸지 않고 백업 수행. 백업 시점 스냅샷 기준으로 데이터 덤프. 따라서 백업 중에 서비스가 정상 동작(INSERT, UPDATE 등)할 수 있다. → 무중단 백업
• --routines : 데이터베이스 내에 생성된 저장 프로시저와 사용자 정의 함수를 백업에 포함 (옵션 없으면 데이터와 테이블 구조만 백업)
• --triggers : 테이블에 설정된 트리거를 백업에 포함 (대부분의 mysql 버전에서 기본값)
• --master-data=2 (8.0.26 이상 --source-data=2) : 백업이 완료된 시점의 Binlog 파일명과 Position 값을 덤프 파일 내에 주석으로 기록 → PITR 시작점을 알기 위해 사용, Replication 환경을 구축할 때 사용
• --databases (또는 -B) : 백업할 때 CREATE DATABASE, USE를 포함하여 생성.
• --no-data (또는 -d) : INSERT문은 빼고 CREATE TABLE만 백업 → 내용 빼고 구조만 원할 때 사용
• --ignore-table=DB명.테이블명 : 특정 테이블을 백업에서 제외 → 대용량 테이블 때문에 백업이 느려지는 것을 방지할 때 사용
• --hex-blob : Binary 데이터 컬럼(BLOB, BINARY 등)을 16진수 포맷으로 덤프 → 이미지 파일이나 암호화된 토큰 등이 백업/복구 과정에서 문자가 깨지는 것을 방지
• --set-gtid-purged=OFF : GTID(Global Transaction Identifier) 정보를 덤프 파일에 포함하지 않음. → GTID가 활성화된 서버에서 백업을 받아 다른 로컬 PC나 AWS RDS 같은 곳에 복원할 때, 권한 문제나 GTID 충돌로 나는 에러를 방지하기 위해 관용적으로 아주 많이 붙이는 옵션

 

head -30 ~/backup/employees_full.sql

tail -30 ~/backup/employees_full.sql

위 명령으로 생성된 파일의 내용을 일부 확인할 수 있다.

 

백업 파일 생성 후 확인

 

Binary Log 위치 확인

PITR을 위해 먼저 현재 Binary Log의 위치를 확인한다.

# 8.4버전 이후
SHOW BINARY LOG STATUS;

# 이전
SHOW MASTER STATUS;

Binary Log에서 현재 위치는 binlog.000007 로그 파일의 Position 158이다.

 

데이터 추가

직원을 2명 추가했다. (백업 이후에 데이터가 달라진 상황을 의미한다.)

emp_no가 999998, 999999인 직원을 추가했다.

 

 

3. 백업 파일로 복구하기

복구를 진행하기 위해 장애가 발생한 상황을 가정하여 employees 데이터베이스를 삭제한다.

DROP DATABASE employees;

employees 데이터베이스 삭제

 

 

3.1. 백업 시점으로 복구

이제 만들어둔 백업 파일을 실행하여 복구를 진행하면 된다.

mysql -u root -p < ~/backup/employees_full.sql

No database selected 라며 에러가 발생했다.

 

백업 파일에 데이터베이스 생성(CREATE DATABASE)과 선택(USE) 구문이 없기 때문에 발생하는 에러이다.

근본적인 원인은 백업 파일을 생성할 때 --databases employees 옵션을 빠트렸기 때문이다.

 

이번 실습에서는 직접 emplyees 데이터베이스를 생성하고 다시 복구를 진행하여 해결했다.

CREATE DATABASE employees;
USE employees;

 

이제 다시 복구를 진행하고 복구된 데이터를 확인한다.

employees 테이블의 행은 백업 당시와 같은 300024개이고, 백업 이후에 추가했던 999999 직원은 없다.

 

emp_no = 999999인 직원은 백업 파일 생성 이후에 추가되었으므로 복구되지 않았다.

하지만 백업 시점의 데이터는 전부 복구되었다.

 

3.2. 원하는 시점으로 복구 (PITR, Point-in-Time Recovery)

Binary Log를 활용하여 특정 시점으로 복구를 진행할 수 있다.

이렇게 하면 백업 이후에 추가했던 emp_no 999998과 999999 직원 데이터를 복구할 수 있다.

 

백업 이후 확인했던 Binary Log의 Position 158부터 복구를 하면 되는데, 이때 employees 데이터베이스를 삭제했던 DROP 전까지만 실행해야 한다.

따라서 Binary Log에서 해당 DROP 명령의 위치를 찾아야 한다.

 

우선 Binary Log의 위치를 확인한다.

SHOW VARIABLES LIKE 'log_bin_basename';

로그 파일의 위치를 확인했다.

 

 

Binary Log에서 해당 DROP 명령의 위치를 확인

mysqlbinlog -vv /var/lib/mysql/binlog.000007 | grep -A 20 -B 10 "DROP DATABASE"

at 2167

 

이제 158부터 2167 직전까지 실행하여 복구하면 된다.

sudo mysqlbinlog --start-position=158 --stop-position=2167 /var/lib/mysql/binlog.000007 | mysql -u root -p

999998과 999999 직원 데이터가 복구되었다.

 

 

4. 백업 자동화

작성중...