Changes between Initial Version and Version 1 of MariaDB 증분 백업

Timestamp:

Nov 11, 2025, 6:48:48 PM (4 weeks ago)

Author:

yongwoo

Comment:

—

MariaDB 증분 백업

@@ +1 @@
+[[PageOutline]]
+= MariaDB 증분 백업 =
+증분 백업을 하기 위해서는 맨 처음 full 백업을 하고, 이 이후에는 변경되는 사항들만 모아놓은 ["MariaDB 바이너리 로그" 바이너리 로그] 파일만 백업하면 된다.
+
+증분 백업은 ["MariaDB 바이너리 로그" 바이너리 로그] 포맷에 ROW를 지정할 수 있는 5.1.5 이후 버전부터 실질적으로 가능하다. 안전하지 않은 SQL문은 바이너리 로그에서 제대로 복구할 수 없기 때문이다.
+
+다음은 복제를 하지 않는 서버에서의 증분 백업 방법이다.
+
+== 1. 로그 파일 생성 ==
+
+따라서 MySQL 서버가 먼저 ["MariaDB 바이너리 로그" 바이너리 로그] 파일을 만들도록 해야 한다. 디폴트로는 생성하지 않기 때문이다.
+
+/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 백업을 하면 백업 이전에 저장했던 ["MariaDB 바이너리 로그" 바이너리 로그]는 필요가 없기 때문이다.
+
+이렇게 하면 남아있는 바이너리 로그 파일에는 백업 이후에 변동된 사항만 남아있게 된다. 
+
+바이너리 로그를 사용한다면 ["MariaDB 바이너리 로그" 바이너리 로그] 좌표(파일명과 위치)를 알 수 있기 때문에 꼭 슬레이브를 셋업할 목적이 아니라도 -lock-all-tables 대신 --master-data=2 옵션을 쓰는 것이 좋다.
+
+{{{
+mysqldump --master-data=2 --flush-logs --delete-master-logs --all-databases --events > fullbackup.sql
+}}}
+ * --master-data[=value]
+  - ["MariaDB 바이너리 로그" 바이너리 로그]가 활성화되어 있어야 한다.
+  - 마스터에서 백업받은 것을 가지고 슬레이브를 셋업하기 위해서 사용한다. ["MariaDB 바이너리 로그" 바이너리 로그] 좌표(파일명과 위치)를 가리키는 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
+  - ["MariaDB 바이너리 로그" 바이너리 로그]가 활성화되어 있어야 한다.
+  -  백업 후에 PURGE BINARY LOGS 명령을 내려 ["MariaDB 바이너리 로그" 바이너리 로그]를 지운다.
+  - --master-data 옵션을 지정하면 자동으로 활성화된다고 나오지만, 실제로는 그러지 않았다.(5.0.77, 10.3.27에서 확인)
+  - 슬레이브 서버가 연결되지 않은 상태에서 이 옵션을 지정하면 슬레이브 서버가 미처 ["MariaDB 바이너리 로그" 바이너리 로그] 내용을 동기화하기 전에 지워버리기 때문에 복제가 깨진다. 슬레이브 서버가 연결된 상태라면 슬레이브 서버가 로그 파일을 읽는 중에는 지우지 않고 에러를 내기 때문에 안전하다.
+ * --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는 ["MariaDB 바이너리 로그" 바이너리 로그] 파일을 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 옵션을 사용한다. (["MariaDB 바이너리 로그" 바이너리 로그] 포맷이 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. 바이너리 로그 삭제 ==
+["MariaDB 바이너리 로그" 바이너리 로그] 파일을 백업받지 않고 삭제했을 때는 반드시 full 백업을 해야 한다. 증분 백업 데이터가 지워졌기 때문이다.
+
+== 7. 복제를 하는 경우의 증분 백업 ==
+복제를 하는 경우 슬레이브 서버에서는 증분 백업을 할 수 없다. (복제 쓰레드 외에는 따로 슬레이브 서버에서 업데이트를 하지 않기 때문에) ["MariaDB 바이너리 로그" 바이너리 로그] 파일에는 데이터베이스 변경 사항이 저장되지 않고, 릴레이 로그 파일에는 데이터베이스 변경 사항이 저장되어 있지만 자동으로 지워지기 때문이다.
+
+따라서 복제를 하면서 증분 백업을 하기 위해서는 마스터 서버에서 ["MariaDB 바이너리 로그" 바이너리 로그]를 백업받는 수밖에 없다. 슬레이브 서버가 변경 사항을 다 받아가지 않았을 수 있기 때문에 ["MariaDB 바이너리 로그" 바이너리 로그] 파일이 지워지지 않도록 주의해야 한다.
+
+["MariaDB 복제#a2.3.바이너리로그를백업하지않고삭제했을때" 바이너리 로그를 백업하지않고 삭제했을 때]를 참조해서 full 백업을 하고 바이너리 로그는 백업 후 purge binary logs 명령으로 지워야 한다. purge binary logs 명령은 슬레이브 서버가 로그 파일을 읽는 중에는 로그 파일을 지우지 않고 에러를 내기 때문에 안전하다. 다만, 슬레이브 서버가 연결되지 않은 상태에서 이 명령을 내리면 복제가 깨진다. 
+
+----
+[WikiStart 처음으로], ["MariaDB mysqldump" mysqldump], ["MariaDB mysqlhotcopy" mysqlhotcopy], ["MariaDB mariabackup" mariabackup]