mysql使用innobackupex备份失败记录

缺乏、安全感 2022-12-20 03:16 128阅读 0赞

### 备份失败日志 ###

<table> 
 <tbody> 
 <tr> 
 <td> <pre>
&nbsp;</pre> Mon May 14 01:00:02 CST 2018 InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved. This software is published under the GNU GENERAL PUBLIC LICENSE Version 2, June 1991. 180514 01:00:05 innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/opt/mysql/my.cnf;mysql_read_default_group=xtrabackup;mysql_ socket=/opt/mysql/run/mysql.sock' (using password: NO). 180514 01:00:05 innobackupex: Connected to MySQL server IMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackupex prints "completed OK!". innobackupex: Using mysql server version 5.6.21-70.0-log innobackupex: Created backup directory /root 180514 01:00:05 innobackupex: Starting ibbackup with command: xtrabackup_56 --defaults-file="/opt/mysql/my.cnf" --defaults-group="mysqld" --backup --suspend-at- end --target-dir=/data/mysql/tmp --tmpdir=/data/mysql/tmp --throttle=10 --stream=tar innobackupex: Waiting for ibbackup (pid=23547) to suspend innobackupex: Suspend file '/data/mysql/tmp/xtrabackup_suspended_2' xtrabackup_56 version 2.1.5 for Percona Server 5.1.70 unknown-linux-gnu (x86_64) (revision id: undefined) xtrabackup: uses posix_fadvise(). xtrabackup: cd to /data/mysql/data xtrabackup: using the following InnoDB configuration: xtrabackup: innodb_data_home_dir = ./ xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend xtrabackup: innodb_log_group_home_dir = ./ xtrabackup: innodb_log_files_in_group = 2 xtrabackup: innodb_log_file_size = 268435456 xtrabackup: using O_DIRECT xtrabackup: error: log block numbers mismatch: xtrabackup: error: expected log block no. 1013117100, but got no. 1014165668 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. innobackupex: Error: The xtrabackup child process has died at /usr/bin/innobackupex line 2579. </td> 
 </tr> 
 </tbody> 
</table>

### 原因分析 ###

<table> 
 <tbody> 
 <tr> 
 <td> <pre>
&nbsp;</pre> 1、redo log的文件大小（innodb_log_file_size*innodb_log_files_in_group）设置过小； 2、XtraBackup拷贝redo log的速度有些慢（可能受其他的系统任务的比较频繁的磁盘IO影响）； 3、如果备份文件是存储在远程文件系统上，则还有可能是受网络带宽较小或拥堵导致； </td> 
 </tr> 
 </tbody> 
</table>

### 解决办法 ###

<table> 
 <tbody> 
 <tr> 
 <td> <pre>
&nbsp;</pre> 由于修改innodb_log_file_size的话需要重启mysqld服务，因此先调整定时备份任务的开始时间，错开IO高峰期。经测备份可以成功。 后面找停机窗口再调整redo参数。 </td> 
 </tr> 
 </tbody> 
</table>