1.mysql使用xtrabackup备份失败。

报错日志如下;

xtrabackup: error: log block numbers mismatch:
xtrabackup: error: expected log block no. 46921381, but got no. 47535769 from the log file.
xtrabackup: error: it looks like InnoDB log has wrapped around before xtrabackup could process all records due to either log copying being too slow, or  log files being too small.
xtrabackup: Error: xtrabackup_copy_logfile() failed.

2.原因 

mysql 使用xtrabackup备份时,默认每秒钟刷新一次日志,即每秒从重做日志中读取最新的日志。
但是真实的情况是,当有的表比较大时,拷贝时间较长,等表拷贝结束后才会刷日志。
这时就会出现,表还没有拷贝完,日志已经被覆盖了。此时xtrabackup无法读取redo中需要的内容,
备份就会失败。

3.从库日志分析 

231219 17:45:30 >> log scanned up to (6620831089700)
231219 17:45:30 [02] Copying ./ct_activiti/act_id_info.ibd to /mysqlback/mysqlbackup20231214/xtrfullbackup-20231219/ct_activiti/act_id_info.ibd
231219 17:45:30 [02]        ...done
231219 17:45:31 [02] Copying ./ct_activiti/act_procdef_info.ibd to /mysqlback/mysqlbackup20231214/xtrfullbackup-20231219/ct_activiti/act_procdef_info.ibd
231219 17:45:31 [02]        ...done
231219 17:45:31 [02] Copying ./ct_activiti/act_re_deployment.ibd to /mysqlback/mysqlbackup20231214/xtrfullbackup-20231219/ct_activiti/act_re_deployment.ibd
231219 17:45:31 [02]        ...done
231219 17:45:33 [02] Copying ./ct_activiti/act_re_model.ibd to /mysqlback/mysqlbackup20231214/xtrfullbackup-20231219/ct_activiti/act_re_model.ibd
231219 17:45:33 >> log scanned up to (6620832151414)
231219 17:45:33 [02]        ...done
231219 17:45:33 [01]        ...done
231219 17:45:33 [02] Copying ./ct_bus/tb_gateway_routes_define.ibd to /mysqlback/mysqlbackup20231214/xtrfullbackup-20231219/ct_bus/tb_gateway_routes_define.ibd
231219 17:45:33 [01] Copying ./ct_bus/tb_service_access.ibd to /mysqlback/mysqlbackup20231214/xtrfullbackup-20231219/ct_bus/tb_service_access.ibd
231219 17:45:33 [02]        ...done
231219 17:45:33 [01]        ...done
231219 17:45:33 [02] Copying ./ct_bus/tb_gateway_access_config.ibd to /mysqlback/mysqlbackup20231214/xtrfullbackup-20231219/ct_bus/tb_gateway_access_config.ibd
231219 17:45:33 [02]        ...done
231219 17:45:33 [01] Copying ./ct_bus/tb_gateway_log.ibd to /mysqlback/mysqlbackup20231214/xtrfullbackup-20231219/ct_bus/tb_gateway_log.ibd
231219 17:45:33 [02] Copying ./ct_bus/tb_gateway_policy.ibd to /mysqlback/mysqlbackup20231214/xtrfullbackup-20231219/ct_bus/tb_gateway_policy.ibd
231219 17:45:33 [02]        ...done
231219 17:45:33 [02] Copying ./ct_bus/tb_gateway_properties.ibd to /mysqlback/mysqlbackup20231214/xtrfullbackup-20231219/ct_bus/tb_gateway_properties.ibd
231219 17:45:33 [02]        ...done
231219 17:45:33 [02] Copying ./ct_exms/car_fee_check.ibd to /mysqlback/mysqlbackup20231214/xtrfullbackup-20231219/ct_exms/car_fee_check.ibd
231219 17:45:33 [02]        ...done
231219 17:45:39 [02] Copying ./ct_exms/code_mapping.ibd to /mysqlback/mysqlbackup20231214/xtrfullbackup-20231219/ct_exms/code_mapping.ibd
231219 17:45:39 [02]        ...done
231219 17:45:58 [02]        ...done
231219 17:45:58 >> log scanned up to (6620840807988)

--由此可见,并不是每秒刷新日志,而是而是有间隔的先拷贝表,然后拷贝数据。
231219 17:45:30 >> log scanned up to (6620831089700)
231219 17:45:33 >> log scanned up to (6620832151414)
231219 17:45:58 >> log scanned up to (6620840807988)

3.解决方法 

修改如下三个参数:
innodb_log_files_in_group=3 
innodb_log_file_size=1024M 
innodb_log_buffer_size=512M  

(1)关闭数据库  
(2)备份:ib_logfile* 重做日志文件都备份。并修改参数。
(3)重启数据库 

Logo

开放原子开发者工作坊旨在鼓励更多人参与开源活动,与志同道合的开发者们相互交流开发经验、分享开发心得、获取前沿技术趋势。工作坊有多种形式的开发者活动,如meetup、训练营等,主打技术交流,干货满满,真诚地邀请各位开发者共同参与!

更多推荐