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)重启数据库