Print
카테고리: [ MariaDB ]
조회수: 6354

관리하는 Maria DB 중 xtrabackup으로 풀백업 및 apply log 수행 시 아래와 같은 에러가 발생하여 해결한 내용 공유 드립니다.

InnoDB: Page [page id: space=1545, page number=62678] log sequence number 11324438142428 is in the future! Current system log sequence number 11324230185692.
InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.

 

-.1 현상

2TB 가 넘는 사이즈의 DB에서 Xtrabackup으로 full backup 수행 시
apply-log 과정 중간에 아래와 같은 error 발생
 
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: InnoDB: Page [page id: space=695, page number=81921] log sequence number 11324236422964 is in the future! Current system log sequence number 11324230185692.
InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
InnoDB: Page [page id: space=1435, page number=322180] log sequence number 11324372211076 is in the future! Current system log sequence number 11324230185692.
InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.
 
그 후 transaction log 가 corrupt 됐다는 메시지를 출력하며 백업이 실패함
 
InnoDB: about forcing recovery.
xtrabackup: error: The transaction log file is corrupted.
xtrabackup: error: The log was not applied to the intended LSN!
xtrabackup: Log applied to lsn 11324230185683
xtrabackup: The intended lsn is 11328872170611

-2. 원인

아래와 같이 apply log 과정이 전부 실패하는 것이 아니기 때문에 백업 중간에 LSN, 즉 트랜잭션이 왜 유실되었는지 원인 파악필요함

 

InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: Doing recovery: scanned up to log sequence number 11323983387648 (5%)
InnoDB: Doing recovery: scanned up to log sequence number 11323988630528 (5%)
InnoDB: Doing recovery: scanned up to log sequence number 11323993873408 (5%)
InnoDB: Doing recovery: scanned up to log sequence number 11323999116288 (5%)
InnoDB: Doing recovery: scanned up to log sequence number 11324004359168 (5%)
InnoDB: Doing recovery: scanned up to log sequence number 11324009602048 (5%)
InnoDB: Doing recovery: scanned up to log sequence number 11324014844928 (5%)
InnoDB: Doing recovery: scanned up to log sequence number 11324020087808 (5%)
InnoDB: Doing recovery: scanned up to log sequence number 11324025330688 (5%)
InnoDB: Starting an apply batch of log records to the database...

InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
 
 
아래 xtrabackup manual 의 설명과 같이 xtrabackup은 백업 수행 시 
백업 중 발생한 DML과 데이터 변경분을 기록하기 위해 innodb 로그파일인 ib_logfile을 copy 하는데 
ib_logfile 이 DB에서 발생한 트랜잭션의 크기보다 많이 작거나 등의 이유로 xtrabackup 이 copy 하기 전에 덮어씌워지면 해당 에러가 발생할 수 있음
 
       The tool changes its working directory to the data directory and performs two primary tasks to complete the backup:

       · It  starts a log-copying thread in the background. This thread watches the InnoDB log files, and when they change, it copies
         the changed blocks to a file called xtrabackup_logfile in the backup target directory. This is necessary because the  backup
         might  take  a  long  time,  and the recovery process needs all of the log file entries from the beginning to the end of the
         backup.

 

       NOTE:
           Log copying thread checks the transactional log every second to see if there were any new log records written that need to
           be copied, but there is a chance that the log copying thread might not be able to keep up with the amount of  writes  that
           go to the transactional logs, and will hit an error when the log records are overwritten before they could be read.
 

-3. 해결방법

my.cnf 에서 innodb_log_file_size 값을 변경 후 재기동

 

#innodb-log-file-size                     = 1024M
innodb-log-file-size                     = 2048M
 
기존 1GB 에서 2GB로 log file의 사이즈를 늘림.
innodb_log_file의 사이즈는 innodb_buffer_pool_size / innodb_log_files_in_group  이 적정 값으로 알려져있으며
백업 이슈가 되었던 DB는  innodb_buffer_pool 28GB , 트랜잭션 빈도와 크기에 비해 innodb-log-file-size 가 너무 작아 
해당 에러가 발생하였던 것으로 보임