xtrabackup 백업 본으로 복구 할 때 LSN이 유실되었던 이슈가 발생해서 원인과 해결방법 공유드리겠습니다.

Xtrabackup 복구 후 MySQL 기동 실패

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.

=> apply log 를 통해 ib_logfile 을 생성 후 db recovery 과정을 진행하는데 아래 구간 LSN 유실 에러 발생

  • LSN 11324230185692 ~ 11324236422964
  • LSN 11324372211076 ~ 11324230185692

xtrabackup은 LSN 을 어떻게 얻어올까

xtrabackup log copy thread

Thread 8 (Thread 0x7f2a53aac700 (LWP 459)):
#0  0x00007f2a591beda2 in pthread_cond_timedwait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0
#1  0x000000000147af65 in os_event::timed_wait (this=this@entry=0x44fa228, abstime=abstime@entry=0x7f2a53aabbe0) at /usr/src/debug/percona-xtrabackup-8.0.9/storage/innobase/os/os0event.cc:322
#2  0x000000000147bc01 in os_event::wait_time_low (this=0x44fa228, time_in_usec=<optimized out>, reset_sig_count=1) at /usr/src/debug/percona-xtrabackup-8.0.9/storage/innobase/os/os0event.cc:495
#3  0x0000000000cb4fce in Redo_Log_Data_Manager::copy_func (this=0x7fff3edebfe0) at /usr/src/debug/percona-xtrabackup-8.0.9/storage/innobase/xtrabackup/src/redo_log.cc:1073
#4  0x0000000000cb509b in operator() (__closure=<optimized out>) at /usr/src/debug/percona-xtrabackup-8.0.9/storage/innobase/xtrabackup/src/redo_log.cc:867
#5  __invoke_impl<void, Redo_Log_Data_Manager::init()::<lambda()>&> (__f=...) at /opt/rh/devtoolset-7/root/usr/include/c++/7/bits/invoke.h:60
#6  __invoke<Redo_Log_Data_Manager::init()::<lambda()>&> (__fn=<synthetic pointer>) at /opt/rh/devtoolset-7/root/usr/include/c++/7/bits/invoke.h:95
#7  __call<void> (__args=<optimized out>, this=<synthetic pointer>) at /opt/rh/devtoolset-7/root/usr/include/c++/7/functional:467
#8  operator()<> (this=<synthetic pointer>) at /opt/rh/devtoolset-7/root/usr/include/c++/7/functional:551
#9  operator()<Redo_Log_Data_Manager::init()::<lambda()> > (f=<unknown type in /usr/lib/debug/usr/bin/xtrabackup.debug, CU 0x9a9ff2, DIE 0x9c6cbe>, this=0x44fa330)
    at /usr/src/debug/percona-xtrabackup-8.0.9/storage/innobase/include/os0thread-create.h:101
#10 __invoke_impl<void, Runnable, Redo_Log_Data_Manager::init()::<lambda()> > (__f=<optimized out>) at /opt/rh/devtoolset-7/root/usr/include/c++/7/bits/invoke.h:60
#11 __invoke<Runnable, Redo_Log_Data_Manager::init()::<lambda()> > (__fn=<optimized out>) at /opt/rh/devtoolset-7/root/usr/include/c++/7/bits/invoke.h:95
#12 _M_invoke<0, 1> (this=0x44fa328) at /opt/rh/devtoolset-7/root/usr/include/c++/7/thread:234
#13 operator() (this=0x44fa328) at /opt/rh/devtoolset-7/root/usr/include/c++/7/thread:243
#14 std::thread::_State_impl<std::thread::_Invoker<std::tuple<Runnable, Redo_Log_Data_Manager::init()::<lambda()> > > >::_M_run(void) (this=0x44fa320)
    at /opt/rh/devtoolset-7/root/usr/include/c++/7/thread:186
#15 0x000000000242c97f in execute_native_thread_routine ()
#16 0x00007f2a591bae65 in start_thread () from /lib64/libpthread.so.0
#17 0x00007f2a5710e88d in clone () from /lib64/libc.so.6
  • db backup 중 발생한 데이터 변경분에 대해 ib_logfile 을 crawling 하는 log-copy thread

해결방법

    /* sleep interval beetween log copy iterations in log copying thread
    in milliseconds (default is 1 second)
    ulint xtrabackup_log_copy_interval = 1000;