웹사이트 검색

MySQL 증분 백업 - InnoDB 및 MyIsam 데이터베이스의 특정 시점 백업 및 복구


이 페이지에서

  1. 메커니즘
  2. 요구 사항\n
  3. 설치
    1. my.cnf 업데이트:
    2. automysqlbackup 실행\n
    3. mysql 증분 실행\n
    4. 루트 crontab 편집

    1. 언급된 문제에 대한 해결 방법\n
    2. 로그 파일

    증분 백업을 수행하는 것은 대규모 프로덕션 데이터베이스의 중요한 요구 사항입니다. 안전한 증분 백업 없이는 신뢰할 수 있는 프로덕션 데이터베이스가 있다고 스스로에게 말할 수 없습니다. 긴급 상황에서 데이터베이스를 복구하려면 충분한 데이터가 있어야 하기 때문입니다. 인터넷에서 몇 가지 검색을 한 후 애플리케이션이 두 데이터베이스 엔진을 동시에 사용하는 혼합 환경에서 MyISAM 및 InnodB에 대한 완전한 증분 백업을 수행할 수 있는 도구를 찾을 수 없었습니다(아마도 Google 및 인터넷에서 전문 검색자가 아닐 수 있음). 그래서 이것을 작성하기로 결정했지만 시간 낭비를 피하고 다른 오픈 소스 솔루션의 이점을 얻기 위해 이 기능을 간단하고 널리 사용되는 전체 백업에 가장 적합한 스크립트인 -automysqlbackup- 스크립트에 추가하는 것을 선호했습니다.

    기구

    automysqlbackup의 사후 및 사전 기능을 사용하여 증분 백업을 수행합니다. 전체 백업을 시작하기 전에 mysql-backup-pre는 백업이 실행되는 동안 변경을 방지하기 위해 binlog를 동결해야 하기 때문에 백업 프로세스 중에 전체 데이터베이스를 잠그는 쿼리를 실행합니다. binlog 이름과 위치는 백업 중에 변경되지 않을 수 있습니다. 바이너리 로그 위치는 후속 증분 백업 프로세스에서 매우 중요하며 다음 증분 백업을 시작하기 위한 시작점으로 사용됩니다. 전체 백업을 완료한 후 mysql-backup-post는 데이터베이스 잠금을 제거합니다.

    잠금 쿼리: 읽기 잠금이 있는 FLUSH TABLES; 수면 선택(86400)

    잠금 쿼리 찾기:mysql -u[username] -p[pass] -e \show processlist\ | grep \슬립 선택(86400)\ | awk {인쇄 $1}

    요구 사항

    • 패키지 설치 및 mysql.conf 업데이트를 위한 루트 권한\n
    • 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.sh

    automysqlbackup을 설치하는 동안 automysqlbackup.conf 및 해당 바이너리의 경로에 대해 묻는 메시지가 표시되며 변경하지 않고 기본값을 그대로 둘 수 있습니다.

    rm /etc/automysqlbackup/myserver.conf

    mysql-incremental 설치: https://sourceforge.net/projects/mysqlincrementalbackup/에서 패키지 다운로드

    cd /tmp
    wget http://downloads.sourceforge.net/project/mysqlincrementalbackup/mysql-incremental.tar.gz
    tar xfz mysql-incremental.tar.gz
    cp mysql-incremental /etc/automysqlbackup/
    chmod 755 /etc/automysqlbackup/mysql-incremental
    cp mysql-backup-post /etc/automysqlbackup/
    chmod 755 /etc/automysqlbackup/mysql-backup-post
    cp mysql-backup-pre /etc/automysqlbackup/
    chmod 755 /etc/automysqlbackup/mysql-backup-pre

    automysqlbackup.conf를 업데이트합니다.

    아래 매개변수를 찾아 주석을 해제하고 변경합니다.

            CONFIG_mysql_dump_username='Mysql user name. It must has privileges to get Lock'
    	CONFIG_mysql_dump_password='Password'
    	CONFIG_backup_dir='The backup directory you want to store full and incremental backup'
    	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.cnf

    1- BinLog 형식

    STATEMENT 형식의 일부 제한으로 인해 ROW 기반 형식을 설정하는 것이 좋습니다. 자세한 내용은 이 하우투의 문제 해결 섹션을 참조하십시오. 바이너리 로그 형식의 종류는 "select @@binlog_format;" 쿼리를 실행하여 확인할 수 있습니다. 로그인 형식을 수정하려면 binlog_format=ROW를 mysql.conf 또는 my.cnf에 추가해야 합니다.

    2- binlog_do_db

    관련 변경 사항을 바이너리 로그에 포함시키려는 데이터베이스를 지정해야 합니다. 데이터베이스를 지정하지 않으면 데이터베이스의 모든 변경 사항이 이진 로그에 기록됩니다. 이 경우 STATEMENT 형식을 선택한 경우 증분 백업 및 binlog 파일에서 복원할 때 문제가 발생할 수 있습니다. 이 옵션에 데이터베이스를 추가할 수 있습니다.

    binlog_do_db = DATABASENAME1
    binlog_do_db = DATABASENAME2

    3- 만료_로그_일

    더 오랜 시간 동안 바이너리 로그 파일을 유지하려면 이 매개변수를 더 높은 값으로 늘릴 수 있습니다. 제 추천은 60일입니다. 따라서 "expire_logs_days = 60"으로 추가하거나 변경해야 합니다.

    4- 로그인

    바이너리 로그가 저장될 디렉터리입니다. 이전 MySQL 버전에서는 mysql-incremenetal이 올바른 경로를 찾지 못할 수 있습니다. 따라서 mysql-incremental 실행 후 이에 대한 오류가 발생하면 mysql-incremental 스크립트를 업데이트하고 바이너리 로그 경로를 설정해야 합니다.

    5- log_slave_updates

    슬레이브 서버에서 mysql 증분 백업을 설정하는 경우 이 옵션을 활성화해야 합니다. 일반적으로 슬레이브는 마스터 서버에서 받은 업데이트를 자체 바이너리 로그에 기록하지 않습니다. 이 옵션은 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 증분 실행

    지금 수동으로 mysql-incremental을 실행하여 최소 1시간마다 백업하세요.

    mysql-incremental

    오류가 발생한 경우 세부정보는 "/var/log/Backup_Incremental_Log" 파일에 기록됩니다. 증분 백업 파일은 /[BackupDirInAutomysqlbackup]/IncrementalBackup/ 디렉터리에 저장됩니다.

    루트 crontab 편집

    mysql-incremental을 한 시간 이상 예약할 수 있습니다. backup_status에서 전체 백업의 총 시간을 찾은 다음 해당 값을 기반으로 정확한 스케줄 시간을 설정할 수 있습니다. 물론 mysql 증분 백업에는 시작하기 전에 실행 중인 전체 백업을 찾는 메커니즘이 있으므로 증분 백업과 전체 백업 간의 충돌에 대한 우려가 없습니다.

    crontab -e
    5 00 * * * root /usr/local/bin/automysqlbackup
    25 *  * * * root  /etc/automysqlbackup/mysql-incremental

    데이터베이스 복원

    특정 시점까지 복구(특정 시점 복구)하기 위해서는 먼저 일일 전체 백업을 한 번 복구한 후 관련 증분 백업 파일을 순차적으로 복구해야 합니다. 더 명확히 하기 위해 testDB 데이터베이스를 복구하는 단계는 다음과 같습니다. 샘플 시나리오에서 우리는 2015년 5월 1일 오전 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 형식으로 사용하는데 이러한 유형의 binlog에는 몇 가지 제한 사항이 있으므로 증분 백업 절차에서 사용하려는 경우 세심한 주의를 기울여야 합니다. mysql이 문 기반 형식으로 설정되면 데이터베이스를 기반으로 올바르게 필터링할 수 없습니다. 데이터베이스를 변경하기 위해 USE 또는 \\u를 설정한 다음 binlog-do-db에 포함되지 않은 다른 데이터베이스를 업데이트하면 바람직하지 않은 상태라는 명령문이 binlog 파일에 기록됩니다! 특정 데이터베이스를 기반으로 복원할 때 일부 문제가 노출되며 또한 binlog-do-db에 포함되지 않은 다른 데이터베이스로 변경하고 binlog-do-db에 포함된 데이터베이스를 업데이트하는 경우 문이 기록되지 않습니다. binlog 파일. binlog-do-db에 데이터베이스를 추가하는 목적은 데이터베이스를 기반으로 필터링하는 것이지만 예상대로 작동하지 않습니다. 쿼리를 실행하기 전에 USE 또는 \\u를 실행하지 않으면 mysqlbinlog는 하나의 데이터베이스와 관련된 업데이트 쿼리를 추출할 수 없습니다. 아래 시나리오를 통해 이 문제에 대해 자세히 설명하겠습니다.

    databases: 
     - binlog
         - person (table) 
      - binlog2
         - person (table)
    
     binlog-do-db=binlog2 (it is supposed only change of this database are logged to binlog file)
    --------Scenario 1---------
    \u binlog2
    insert into person (data) values ('17') ---> loged in binlog  *desired state*
    insert into binlog.person (data) values ('25'); ---> logged in binlog (target database is 'binlog' ) *undesired state*
    --------Scenario 2---------
    \u binlog
    insert into person (data) values ('17') ---> is not logged in binlog  *desired state*
    insert into binlog2.person (data) values ('25'); ---> is not logged in binlog (target database is 'binlog2' ) *undesired state* because the binlog2 database
    is begin changed, so we want to have this change,but it will not logged in logbin file
    --------Scenario 3---------
    if you just connect to database without any USE or \u statement, all of updates on any databases will be logged, but mysqlbinlog can not able to filter
    based on specific database, so that is not desirable state for our purpose in incremental backup. Using USE or \u before executing update queries, is very
    important. Because mysqlbinlog finds update queries based on USE statement in binlog file.

    언급된 문제를 해결하십시오.

    1) 각 사용자는 업데이트할 하나의 데이터베이스(응용 사용자)에만 액세스할 수 있도록 데이터베이스에 사용자를 정의하고 데이터베이스에 연결할 때 데이터베이스 이름을 지정해야 합니다. 물론 대부분의 응용 프로그램에는 자격 증명과 데이터베이스 이름이 설정되어 있는 구성 파일이 있으므로 이 경우 데이터베이스에 대한 교차 액세스가 없으며 "\\USE 또는 \\ 유\.

    2) 행 기반 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
    /[SpecifiedBackupDirInAutomysqlbackup.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 : indicates the time when the backup has been finished. You can run date --date @1431043501 command on the server the backup has been done to view it in human readable format.
     2th) Mysql-bin.000026 : indicates the binary log name that backup up to this file has been done.
     3th) 120 : indicates the position of binlog  that backup up to this position in binary log has been done.
     4th) Daily/Hourly: indicates type of backup. Daily does mean the full backup by automysqlbackup script and Hourly is done by mysql-incremental script.
     5th) 2015-05-08: The date that backup has been done. This date will be used in creating directory for incremental backup and also as a base for restore hourly backups. In restoring procedure, first a full backup is restored and then sequentially other incremental backup are restored.
     6th) 0 : indicates number of backups from previous full backup. 0 does mean the backup is full and others mean hourly. This number is very important in restoring procedure.
     7th) 24: The backup duration in second.

    버그 신고

    https://sourceforge.net/projects/mysqlincrementalbackup에서 버그를 보고하거나 제안 및 리뷰를 제공할 수 있습니다.