MariaDB 증분 백업
증분 백업을 하기 위해서는 맨 처음 full 백업을 하고, 이 이후에는 변경되는 사항들만 모아놓은 바이너리 로그 파일만 백업하면 된다.
증분 백업은 바이너리 로그 포맷에 ROW를 지정할 수 있는 5.1.5 이후 버전부터 실질적으로 가능하다. 안전하지 않은 SQL문은 바이너리 로그에서 제대로 복구할 수 없기 때문이다.
다음은 복제를 하지 않는 서버에서의 증분 백업 방법이다.
1. 로그 파일 생성
따라서 MySQL 서버가 먼저 바이너리 로그 파일을 만들도록 해야 한다. 디폴트로는 생성하지 않기 때문이다.
/etc/my.cnf 파일에 다음 부분을 추가한다.
# incremental 백업을 하거나 복제를 하려면 바이너리 로그를 저장해야 한다. # 로그 파일 기본이름은 명시적으로 지정해주는 것을 권장한다. 생략하면 호스트 이름을 # 사용하는데, 호스트 이름이 바뀔 경우에는 따라서 변경되기 때문이다. log-bin=mysqld-bin # ROW 포맷이 가장 안전 binlog_format=row # InnoDB 트랜잭션 복제를 하기 위해서 마스터에서 # innodb_flush_log_at_trx_commit=1과 sync_binlog=1을 지정. # innodb_flush_log_at_trx_commit의 디폴트 값이 1이므로 그냥 생략해도 무방 sync_binlog=1 # 바이너리 로그 포맷을 ROW로 지정했기 때문에 함수를 만들 때 실수해도 # 복제하는 데 문제가 없다. 따라서 NON DETERMINISTIC 함수도 만들 수 있게끔 # log_bin_trust_function_creators를 1로 지정한다. 디폴트는 0. log_bin_trust_function_creators=1
실제 만들어지는 파일명은 로그 파일 기본이름 뒤에 확장자로 일련번호가 붙는다.
로그 파일 기본이름은 명시적으로 지정해주는 것을 권장한다. 생략하면 호스트 이름을 사용하는데, 호스트 이름이 바뀔 경우에는 따라서 변경되기 때문이다.
로그 파일 기본이름에 확장자를 지정하면 확장자는 무시한다.
2. full 백업
incremental 백업을 위한 full 백업을 하려면 백업을 시작하기 전에 먼저 메모리에만 있고 아직 로그 파일에 저장하지 않은 변동 사항은 모두 기존 로그 파일에 저장하고 새로운 로그파일을 생성한다.(—flush-logs) 이 때 로그 저장 시점과 백업 시점을 동일하게 하려면 반드시 —lock-all-tables 이나 —master-data=2 옵션과 같이 사용해야 한다.
그리고 기존 로그 파일은 지워버린다.(—delete-master-logs) full 백업을 하면 백업 이전에 저장했던 바이너리 로그는 필요가 없기 때문이다.
이렇게 하면 남아있는 바이너리 로그 파일에는 백업 이후에 변동된 사항만 남아있게 된다.
바이너리 로그를 사용한다면 바이너리 로그 좌표(파일명과 위치)를 알 수 있기 때문에 꼭 슬레이브를 셋업할 목적이 아니라도 -lock-all-tables 대신 —master-data=2 옵션을 쓰는 것이 좋다.
mysqldump --master-data=2 --flush-logs --delete-master-logs --all-databases --events > fullbackup.sql
- —master-data[=value]
- 바이너리 로그가 활성화되어 있어야 한다.
- 마스터에서 백업받은 것을 가지고 슬레이브를 셋업하기 위해서 사용한다. 바이너리 로그 좌표(파일명과 위치)를 가리키는 CHANGE MASTER TO 문을 백업 파일에 기록한다.
- 이 옵션을 사용하기 위해선 RELOAD 권한이 필요하다.
- value 값의 디폴트는 1이다. 따라서 1일 때는 값을 생략할 수 있다.
- value가 2이라면 CHANGE MASTER TO 문이 주석으로 삽입된다. 주석으로 삽입하기 때문에 실제 영향은 없다.
- —single-transaction을 명시적으로 적어주지 않는다면 —lock-all-tables 옵션이 자동으로 지정된다.
- —lock-tables 옵션을 자동으로 끈다.
- —lock-all-tables, -x
- 백업을 하는 동안 모든 데이터베이스를 읽기만 가능하도록 잠근다(READ LOCK).
- 이 옵션을 설정하면 —single-transaction과 —lock-tables 옵션은 자동으로 꺼진다.
- 데이터베이스의 일관성은 보장되지만 백업이 진행되는 동안 쓰기가 불가능해지므로 대용량 데이터베이스인 경우엔 신중하게 해야 한다.
- full 백업 이후의 변경 사항을 복구(point-in-time 복구, roll-forward)하기 위해서라도 —lock-all-tables 명령 대신 —master-data=2 옵션을 주는 것이 좋다.
- —lock-tables, -l
- 백업을 하는 동안 특정 데이터베이스를 잠근다(READ LOCAL lock).
- READ LOCAL LOCK이므로 진행 중인 insert는 허용한다.
- 여러 데이터베이스를 백업할 때는 주의해야 한다. —lock-tables은 각 데이터베이스를 각각 잠근다. 그래서 데이터베이스간에 논리적인 일관성이 보장되지 않는다.
- 백업이 진행되는 동안 특정 데이터베이스에 쓰기가 불가능해지므로 대용량 데이터베이스인 경우엔 신중하게 해야 한다.
- InnoDB나 BDB 같은 트랜잭션을 지원하는 테이블에는 —single-transaction이 더 나은 옵션이다. 테이블을 전혀 잠글 필요가 없기 때문이다.
- —flush-logs, -F
- 백업을 시작하기 전에 로그를 파일에 저장한다(flush).
- 이 옵션을 사용하기 위해선 RELOAD 권한이 필요하다.
- —all-databases 옵션과 같이 사용하면 각 데이터베이스가 백업될 때마다 각각 그 로그를 저장(flush)한다는 점에 주의한다.
- 그러나 —lock-all-tables, —master-data 옵션이 사용되면 모든 데이터베이스를 잠근 순간에 한꺼번에 로그를 저장(flush)한다.
- 따라서 백업과 로그 저장을 정확히 같은 시각에 하려면 —flush-logs를 —lock-all-tables, —master-data 옵션과 같이 사용해야 한다.
- —delete-master-logs
- —events
- 명시적으로 적어줘야 mysql.events 테이블을 백업한다. —all-databases 옵션을 줘도 mysql.events 테이블 스키마만 백업하고 데이터는 다음과 같은 경고를 내면서 백업하지 않는다. Warning: Skipping the data of table mysql.event. Specify the —events option explicitly.
- EVENT 권한이 필요하다.
- —quick
- 디폴트 값이므로 따로 지정해주지 않아도 된다. 큰 테이블을 백업할 때 유용하다. —skip-quick 옵션을 사용하면 테이블 데이터를 모두 메모리에 불러온 다음에 백업 파일에 쓰기 시작한다.
mysql 데이터베이스 외에는 모두 InnoDB나 BDB 같은 트랜잭션을 지원하는 테이블이고 백업하는 동안 mysql 데이터베이스를 변경하지 않을 거라면 full 백업은 다음과 같다.
mysqldump --master-data=2 --single-transaction --flush-logs --delete-master-logs --all-databases --events > fullbackup.sql
- —single-transaction
- 쓰기를 막지 않고도 일관성있게 데이터베이스를 백업할 수 있다.
- InnoDB 같은 트랜잭션을 지원하는 테이블에만 유용하다. MyISAM 같은 트랜잭션을 지원하지 않는 테이블에서는 논리적인 일관성이 보장되지 않는다.
- —lock-tables과 같이 쓸 수 없다.
- MySQL Cluster 테이블에는 사용할 수 없다. (확인 필요)
- 백업하는 중에 ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE 명령을 사용하면 문제가 생길 수 있다.
3. incremental 백업
다음 명령을 내리면 /etc/my.cnf 파일에 지정한 로그 파일 기본이름 뒤에 새로운 일련번호가 붙은 파일이 저장된다.
/usr/bin/mysqladmin flush-logs
이 파일을 백업하면 된다.
4. 증분 백업 스크립트
매번 이 작업을 할 수 없으니 백업 스크립트를 만들어서 매일 작업하는 것이 좋다.
- 이 예에서는 백업 파일을 로컬 하드디스크가 아닌 다른 서버의 하드디스크에 저장하는 것으로 가정했다. (/backup 이라는 디렉토리에 마운트해서)
- 복제는 사용하지 않는 것으로 가정했다. 복제를 사용한다면 바이너리 로그를 단순히 옮기는 것이 아니라 복사한 후 purge birary logs 명령으로 지워야 한다.
- gzip 대신 zstd를 사용하는 것으로 가정했다.
#!/bin/bash # 백업 파일을 저장할 곳 마운트 /bin/mount ... /backup # 마운트가 성공했다면 if [ $? ]; then echo "== mysql DB 백업 ==" # dump가 입력 매개변수라면 if [ "$1" == "dump" ]; then echo "Full Backup 시작 `date`" # 이전 full 백업과 incremental 백업 삭제 \rm -f /backup/mysql*.zst /usr/bin/mysqldump --master-data=2 --flush-logs --delete-master-logs --all-databases --events > /backup/mysql`date +\%Y\%m\%d`.sql # Full 백업에 시간이 많이 걸리는 경우에는 --single-transaction 옵션을 추가한다. # /usr/bin/mysqldump --master-data=2 --single-transaction --flush-logs --delete-master-logs --all-databases --events > /backup/mysql`date +\%Y\%m\%d`.sql RETVAL=$? /usr/bin/zstd -q --rm /backup/mysql`date +\%Y\%m\%d`.sql echo "Full Backup 완료" else echo "바이너리 로그 저장" /usr/bin/mysqladmin flush-logs RETVAL=$? newestlog=`ls -d /var/lib/mysql/mysqld-bin.?????? | sed 's/^.*\.//' | sort -g | tail -n 1` for file in `ls /var/lib/mysql/mysqld-bin.??????` do if [ "/var/lib/mysql/mysqld-bin.$newestlog" != "$file" ]; then /usr/bin/zstd -q --rm "$file" fi done \mv /var/lib/mysql/*.zst /backup fi exit $RETVAL # 마운트 해제 /bin/umount /backup else echo "could not mount" exit 1 fi exit $RETVAL
5. 증분 백업 복구
(MyISAM 테이블이 손상되었다면 백업 복구보다는 REPAIR TABLE 이나 myisamchk -r 을 먼저 시도하는 것이 좋다.)
5.1. full 백업 복구
full 백업받은 것의 압축을 풀고 복구한다.
gzip -d full-backup.sql.gz mysql < full-backup.sql
- full 백업받았던 당시에 있던 데이터베이스와 동일한 이름의 기존 데이터베이스는 모두 삭제가 되니 주의해야 한다.
- 만약 mysql을 처음 설치해서 복구하는 것이라면 test 데이터베이스는 수동으로 삭제해줘야 할 것이다. full 백업 당시에 이미 test 데이터베이스는 삭제한 상태이므로 복구 과정에서 test 데이터베이스는 삭제되지 않는다.
5.2. 증분 백업 복구
증분 백업받은 것의 압축을 풀고 복구한다.
gzip -d mysqld-bin.[0-9]*.gz mysqlbinlog mysqld-bin.[0-9]* | mysql
mysqlbinlog는 바이너리 로그 파일을 sql 문으로 변환하는 역할을 한다.
full 백업할 때 -master-data=2 옵션을 줘서 바이너리 로그 좌표(파일명과 위치)를 알 수 있다면 그 이후부터의 바이너리 로그만 복원하면 된다.
mysqlbinlog --start-position=1 "mysql bin로그 경로" > binlog.sql
문제가 되는 시점 이전까지만 복구하려고 하면 —stop-datetime 옵션을 사용하면 된다. 정확한 시점을 모르거나 다시 한번 확인을 하기 위해서는 mysqlbinlog의 결과물을 텍스트 파일에 저장한 후 문제가 되는 시점부터의 데이터를 지우는 편이 낫다.
mysqlbinlog mysqld-bin.[0-9]* > /tmp/satements.sql ... /tmp/satements.sql 수정 후 ... mysql -u root -p -e "source /tmp/statements.sql"
복원 목적이 아니라 어떤 SQL문이 실행되었는지 보는 목적이라면 —short-form 옵션을 사용한다. (바이너리 로그 포맷이 STATEMENT일 때)
mysqlbinlog --database=ppalbang --stop-datetime="2017-06-13 14:00:00" --short-form mysqld-bin.000002
바이너리 로그 포맷이 STATEMENT가 아니라 ROW일 때는 —base64-output=DECODE-ROWS —verbose 옵션을 사용한다. 실행한 SQL문을 볼 수는 없지만 어떤 데이터가 변했는지 알아볼 수 있다.
mysqlbinlog --database=ppalbang --stop-datetime="2017-06-13 14:00:00" --base64-output=DECODE-ROWS --verbose mysqld-bin.000002
6. 바이너리 로그 삭제
바이너리 로그 파일을 백업받지 않고 삭제했을 때는 반드시 full 백업을 해야 한다. 증분 백업 데이터가 지워졌기 때문이다.
7. 복제를 하는 경우의 증분 백업
복제를 하는 경우 슬레이브 서버에서는 증분 백업을 할 수 없다. (복제 쓰레드 외에는 따로 슬레이브 서버에서 업데이트를 하지 않기 때문에) 바이너리 로그 파일에는 데이터베이스 변경 사항이 저장되지 않고, 릴레이 로그 파일에는 데이터베이스 변경 사항이 저장되어 있지만 자동으로 지워지기 때문이다.
따라서 복제를 하면서 증분 백업을 하기 위해서는 마스터 서버에서 바이너리 로그를 백업받는 수밖에 없다. 슬레이브 서버가 변경 사항을 다 받아가지 않았을 수 있기 때문에 바이너리 로그 파일이 지워지지 않도록 주의해야 한다.
바이너리 로그를 백업하지않고 삭제했을 때를 참조해서 full 백업을 하고 바이너리 로그는 백업 후 purge binary logs 명령으로 지워야 한다. purge binary logs 명령은 슬레이브 서버가 로그 파일을 읽는 중에는 로그 파일을 지우지 않고 에러를 내기 때문에 안전하다. 다만, 슬레이브 서버가 연결되지 않은 상태에서 이 명령을 내리면 복제가 깨진다.
