데이터베이스 백업 · 6 min read · Dec 10, 2025
MySQL 증분 백업 - InnoDB 및 MyIsam 데이터베이스의 시점 백업 및 복구
증분 백업을 수행하는 것은 대규모 생산 데이터베이스에 대한 중요한 요구 사항입니다. 안전한 증분 백업이 없으면 신뢰할 수 있는 생산 데이터베이스가 있다고 자신할 수 없습니다. 비상 상황에서 데이터베이스를 복구하기 위해 충분한 데이터가 있어야 하기 때문입니다. 인터넷에서 몇 가지 검색을 해본 결과, MyISAM과 InnoDB를 혼합 환경에서 완전한 증분 백업을 수행할 수 있는 도구를 찾을 수 없었습니다(아마도 제가 구글과 인터넷에서 전문가 검색자가 아니기 때문일 것입니다). 그래서 이 도구를 작성하기로 결정했지만 시간을 낭비하지 않고 다른 오픈 소스 솔루션의 이점을 활용하기 위해, 전체 백업의 단순성과 널리 사용되는 최고의 스크립트인 -automysqlbackup-에 이 기능을 추가하기로 했습니다.
메커니즘
우리는 automysqlbackup의 Post 및 Pre 기능을 사용하여 증분 백업을 수행합니다. 전체 백업을 시작하기 전에 mysql-backup-pre가 백업 프로세스 중에 전체 데이터베이스를 잠그기 위해 쿼리를 실행합니다. 백업이 실행되는 동안 변경을 피하기 위해 binlog를 동결해야 하기 때문입니다. 백업 중에 binlog 이름과 위치는 변경되지 않아야 합니다. 이진 로그 위치는 이후의 증분 백업 프로세스에서 매우 중요하며 다음 증분 백업을 시작하는 기준점으로 사용됩니다. 전체 백업이 완료된 후 mysql-backup-post가 데이터베이스 잠금을 제거합니다.
잠금 쿼리: FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)
잠금 쿼리 찾기: mysql -u[username] -p[pass] -e “show processlist” | grep “SELECT SLEEP(86400)” | awk ‘{print $1}’
요구 사항
- 패키지를 설치하고 mysql.conf를 업데이트할 수 있는 root 권한
- mysql-community-client 패키지
- automysqlbackup 및 mysql-incremental 설치
설치
배포판에 맞는 mysql-community-client 패키지를 설치합니다.
참고: MySQL 설치 후 ‘mysqlshow’ 명령이 있어야 합니다.
automysqlbackup 설치:
download the package from https://sourceforge.net/projects/automysqlbackup/tar -xzf [PathYouSavedTarFile] -C /tmp/cd /tmp/./install.shautomysqlbackup 설치 중에 automysqlbackup.conf 및 해당 바이너리의 경로에 대해 질문을 받게 되며, 기본값을 변경하지 않고 그대로 두어도 됩니다.
rm /etc/automysqlbackup/myserver.confmysql-incremental 설치: https://sourceforge.net/projects/mysqlincrementalbackup/에서 패키지를 다운로드합니다.
cd /tmp
wget http://downloads.sourceforge.net/project/mysqlincrementalbackup/mysql-incremental.tar.gz
tar xfz mysql-incremental.tar.gzcp mysql-incremental /etc/automysqlbackup/chmod 755 /etc/automysqlbackup/mysql-incrementalcp mysql-backup-post /etc/automysqlbackup/chmod 755 /etc/automysqlbackup/mysql-backup-postcp mysql-backup-pre /etc/automysqlbackup/chmod 755 /etc/automysqlbackup/mysql-backup-preautomysqlbackup.conf를 업데이트합니다:
아래 매개변수를 찾아 주석을 제거하고 변경합니다:
CONFIG_mysql_dump_username='Mysql 사용자 이름. 잠금을 얻을 수 있는 권한이 있어야 합니다.'
CONFIG_mysql_dump_password='비밀번호'
CONFIG_backup_dir='전체 및 증분 백업을 저장할 백업 디렉토리'
CONFIG_db_names=('databaseName1' 'databaseName2' )
CONFIG_db_month_names=('databaseName1' 'databaseName2' )
CONFIG_mysql_dump_master_data=2
CONFIG_prebackup="/etc/automysqlbackup/mysql-backup-pre"
CONFIG_postbackup="/etc/automysqlbackup/mysql-backup-post"my.cnf 업데이트:
MySQL 구성 파일을 편집합니다:
nano /etc/mysql/my.cnf1- BinLog 형식
STATEMENT 형식의 일부 제한으로 인해, ROW 기반 형식으로 설정하는 것이 좋습니다. 자세한 내용은 이 설명서의 ‘문제 해결’ 섹션을 참조하십시오. “ select @@binlog_format; “ 쿼리를 실행하여 이진 로그 형식의 유형을 확인할 수 있습니다. logbin 형식을 수정하려면 mysql.conf 또는 my.cnf에 binlog_format = ROW를 추가해야 합니다.
2- binlog_do_db
이진 로그에서 관련 변경 사항을 기록할 데이터베이스를 지정해야 합니다. 데이터베이스를 지정하지 않으면 모든 데이터베이스의 변경 사항이 이진 로그에 기록됩니다. 이 경우 STATEMENT 형식을 선택하면 증분 백업 및 binlog 파일에서 복원할 때 문제가 발생할 수 있습니다. 이 옵션에 데이터베이스를 추가할 수 있습니다:
binlog_do_db = DATABASENAME1
binlog_do_db = DATABASENAME23- expire_logs_days
이진 로그 파일을 더 오랫동안 보관하려면 이 매개변수를 더 높은 값으로 증가시킬 수 있습니다. 제 추천은 60일입니다. 따라서 “ expire_logs_days = 60 “으로 추가하거나 변경해야 합니다.
4- log-bin
이진 로그가 저장될 디렉토리입니다. 이전 MySQL 버전에서는 mysql-incremental이 올바른 경로를 찾지 못할 수 있습니다. 따라서 mysql-incremental을 실행한 후 이와 관련된 오류가 발생하면 mysql-incremental 스크립트를 업데이트하고 이진 로그 경로를 설정해야 합니다.
5- log_slave_updates
슬레이브 서버에서 mysql-incremental 백업을 설정하는 경우 이 옵션을 활성화해야 합니다. 일반적으로 슬레이브는 마스터 서버에서 수신한 대로 자신의 이진 로그에 업데이트를 기록하지 않습니다. 이 옵션은 슬레이브가 SQL 스레드에 의해 수행된 업데이트를 자신의 이진 로그에 기록하도록 지시합니다. http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html#option_mysqld_log-slave-updates
automysqlbackup 실행
지정한 데이터베이스에서 최소한 하나의 전체 백업을 수행하기 위해 automysqlbackup를 수동으로 실행합니다.
automysqlbackup명령을 성공적으로 실행한 후, /[BackupDirInAutomysqlbackup]/status/backup_info 파일에서 일일 백업에 대한 새로 추가된 정보를 확인하십시오. 오류 세부정보는 /var/log/Backup_Post_Pre_log에서 확인하십시오. 백업 파일은 /[BackupDirInAutomysqlbackup]/daily/[DatabaseName]/ 디렉토리에 저장됩니다.
mysql-incremental 실행
이제 최소한 하나의 시간별 백업을 수행하기 위해 mysql-incremental을 수동으로 실행합니다.
mysql-incremental오류가 발생할 경우 세부정보는 “ /var/log/Backup_Incremental_Log “ 파일에 기록됩니다. 증분 백업 파일은 /[BackupDirInAutomysqlbackup]/IncrementalBackup/ 디렉토리에 저장됩니다.
루트 크론탭 편집
mysql-incremental을 1시간 이상 예약할 수 있습니다. backup_status에서 전체 백업의 총 시간을 찾은 다음 해당 값을 기반으로 정확한 예약 시간을 설정합니다. 물론 mysql-incremental 백업에는 시작하기 전에 실행 중인 전체 백업을 찾는 메커니즘이 있으므로 증분 백업과 전체 백업 간의 충돌에 대한 우려는 없습니다.
crontab -e5 00 * * * root /usr/local/bin/automysqlbackup
25 * * * * root /etc/automysqlbackup/mysql-incremental데이터베이스 복원
특정 시간(시점 복구)까지 복원하려면 먼저 하루 전체 백업을 복원한 다음 관련 증분 백업 파일을 순차적으로 복원해야 합니다. 더 명확히 하기 위해, testDB 데이터베이스를 복구하는 단계는 다음과 같습니다. 샘플 시나리오에서는 2015-5-01 오전 2시까지 데이터를 복구하려고 합니다. /backup을 주요 백업 디렉토리로 설정하고 testDB를 대상 데이터베이스로 설정했습니다:
1- mysql -u root -p DatabaseName < /backup/daily/testDB/daily_DatabaseName_2015-05-16_00h05m_Saturday.sql.gz
2- mysql -u root -p DatabaseNAme < /backup/IncrementalBackup/2015-5-01_Incremental/testDB/testDB_IncrementalBackup_2015-5-01_00h25m.1
3- mysql -u root -p DatabaseNAme < /backup/IncrementalBackup/2015-5-01_Incremental/testDB/testDB_IncrementalBackup_2015-5-01_01h25m.2
4- mysql -u root -p DatabaseNAme < /backup/IncrementalBackup/2015-5-01_Incremental/testDB/testDB_IncrementalBackup_2015-5-01_02h25m.3중요 사항 및 문제 해결
MySQL은 이진 로그에 대해 다양한 형식을 지원합니다. 일부 MySQL 버전은 binlog 형식으로 ‘statement-based’를 사용하며, 이 유형의 binlog는 증분 백업 절차에서 사용할 때 주의해야 할 몇 가지 제한이 있습니다. MySQL이 statement-base 형식으로 설정되면 데이터베이스를 기준으로 올바르게 필터링할 수 없습니다. 데이터베이스를 변경하기 위해 ‘USE’ 또는 ‘\u’를 설정한 후 binlog-do-db에 포함되지 않은 다른 데이터베이스를 업데이트하면 해당 문이 binlog 파일에 기록됩니다. 이는 바람직하지 않은 상태이며 특정 데이터베이스를 기준으로 복원할 때 문제가 발생할 수 있습니다. 또한 binlog-do-db에 포함되지 않은 다른 데이터베이스로 변경하고 binlog-do-db에 포함된 데이터베이스를 업데이트하면 해당 문이 binlog 파일에 기록되지 않습니다. binlog-do-db에 데이터베이스를 추가하는 목적은 데이터베이스를 기준으로 필터링하는 것이지만, 예상대로 작동하지 않습니다. 쿼리를 실행하기 전에 USE 또는 \u가 실행되지 않으면 mysqlbinlog는 특정 데이터베이스와 관련된 ‘업데이트 쿼리’를 추출할 수 없습니다. 아래 시나리오로 이 문제를 더 설명하겠습니다:
데이터베이스:
- binlog
- person (테이블)
- binlog2
- person (테이블)
binlog-do-db=binlog2 (이 데이터베이스의 변경 사항만 binlog 파일에 기록되어야 함)
--------시나리오 1---------
\u binlog2
insert into person (data) values ('17') ---> binlog에 기록됨 *바람직한 상태*
insert into binlog.person (data) values ('25'); ---> binlog에 기록됨 (대상 데이터베이스는 'binlog' ) *바람직하지 않은 상태*
--------시나리오 2---------
\u binlog
insert into person (data) values ('17') ---> binlog에 기록되지 않음 *바람직한 상태*
insert into binlog2.person (data) values ('25'); ---> binlog에 기록되지 않음 (대상 데이터베이스는 'binlog2' ) *바람직하지 않은 상태* binlog2 데이터베이스가 변경되고 있으므로 이 변경 사항을 기록하고 싶지만 logbin 파일에 기록되지 않음
--------시나리오 3---------
데이터베이스에 연결할 때 USE 또는 '\u' 문이 없으면 모든 데이터베이스의 업데이트가 기록되지만 mysqlbinlog는 특정 데이터베이스를 기준으로 필터링할 수 없으므로 이는 증분 백업의 목적에 바람직하지 않은 상태입니다. 업데이트 쿼리를 실행하기 전에 USE 또는 \u를 사용하는 것이 매우 중요합니다. mysqlbinlog는 binlog 파일에서 USE 문을 기준으로 업데이트 쿼리를 찾기 때문입니다.언급된 문제에 대한 해결 방법
각 사용자가 업데이트할 수 있는 데이터베이스에만 접근할 수 있도록 데이터베이스에서 사용자를 정의하고 데이터베이스에 연결할 때 데이터베이스 이름을 지정해야 합니다. 물론 대부분의 애플리케이션은 자격 증명과 데이터베이스 이름이 설정된 구성 파일을 가지고 있으므로 이 경우 데이터베이스 간의 교차 접근이 없으며 “\USE 또는 \u”를 사용하는 것에 대한 우려가 없습니다.
행 기반 binlog 형식을 사용하는 경우 모든 언급된 문제가 사라집니다. 즉, 행 기반 형식은 binlog에 더 적합한 방법입니다. https://dev.mysql.com/doc/refman/5.1/en/replication-options-binary-log.html
로그 파일
모든 것을 로그 파일에 기록하려고 했으므로 로그에서 충분한 정보를 찾을 수 있습니다:
/var/log/Backup_Post_Pre_log
/var/log/Backup_Incremental_Log
/[지정된BackupDirInAutomysqlbackup.conf]/status/backup_info
“backup_info” 파일에는 백업에 대한 자세한 정보와 백업이 완료된 시간(시간은 Unix 시간 형식) 등이 포함되어 있습니다. 백업이 시작된 시점의 binlog 이름과 위치, 백업 유형, 마지막 전체 백업 이후의 백업 수 및 백업 지속 시간이 포함됩니다.
샘플 backup_info:
1431043501,mysql-bin.000026,120,Daily,2015-05-08,0,24
1431044701,mysql-bin.000026,120,Hourly,2015-05-08,1,1여기서 다양한 값에 대한 설명:
1th) 1431043501 : 백업이 완료된 시간을 나타냅니다. 백업이 수행된 서버에서 date --date @1431043501 명령을 실행하여 사람이 읽을 수 있는 형식으로 볼 수 있습니다.
2th) Mysql-bin.000026 : 이 파일까지 백업이 완료된 이진 로그 이름을 나타냅니다.
3th) 120 : 이 위치까지 이진 로그에서 백업이 완료된 위치를 나타냅니다.
4th) Daily/Hourly: 백업 유형을 나타냅니다. Daily는 automysqlbackup 스크립트에 의해 수행된 전체 백업을 의미하고 Hourly는 mysql-incremental 스크립트에 의해 수행됩니다.
5th) 2015-05-08: 백업이 수행된 날짜입니다. 이 날짜는 증분 백업을 위한 디렉토리 생성 및 시간별 백업 복원의 기준으로 사용됩니다. 복원 절차에서 먼저 전체 백업을 복원한 다음 순차적으로 다른 증분 백업을 복원합니다.
6th) 0 : 이전 전체 백업 이후의 백업 수를 나타냅니다. 0은 백업이 전체임을 의미하고 다른 숫자는 시간별 백업을 의미합니다. 이 숫자는 복원 절차에서 매우 중요합니다.
7th) 24: 백업 지속 시간을 초 단위로 나타냅니다.버그 보고
버그를 보고하거나 제안 및 리뷰를 제공하려면 https://sourceforge.net/projects/mysqlincrementalbackup 에서 가능합니다.
새 게시물을 받은 편지함에서 받기
스팸은 없습니다. 언제든지 구독 해지 가능합니다.