XtraBackup fails instantly

MySQL & MariaDB Percona XtraBackup
1

Hi there,

XtraBackup fails on me when backuping up our database. The error is the infamous

xtrabackup: error: log block numbers mismatch:
xtrabackup: error: expected log block no. 1012092717, but got no. 1020481321 from the log file.

So I googled and read the posts on this forum. At first I thought the logfiles were too small. Allthough 3 logfiles of 1GB each sounds pretty big, I checked the writing speed and it was around 300MB per minute. Checked with:

mysql -uUSER -pPASSWD -e "show engine innodb status\G select sleep(60); show engine innodb status\G" \
| grep sequence | awk '{ if(N==0) { N=$(NF); } else { printf("%.2f MB/m\n", ($(NF) - N)/1024.0/1024.0); } }'

With 300 MB/minute, I would need 18GB of logfiles to keep an hours worth of transactions. So allthough the manual states the logfiles should not exceed 4GB in total, I increased the sizes to 3x 5GB. There was no warning or error in the logs and the databases kept working OK.

However, this did not work for the backups. Most of the times XtraBackup fails within seconds, but I can’t believe the log has wrapped that fast. If only our I/O really was that fast…

Then I even increased to 2x 10GB to comply with the 18GB needed for an hour, but still to no avail.

Running XtraBackup directed to >/dev/null also fails within seconds, so I don’t suspect the I/O is too slow.

Sometimes it seems to really try to copy the logfiles and then it takes a couple of minutes before it crashes, but most of the times it’s within seconds.

I guess I did something wrong, could someone help me out?
Should I decrease logfile sizes back to small values even though the transaction log won’t hold much more than a couple of minutes?

2

Hi rob.dewit;

Since you seem to have potentially ruled out one of the common issues (log size and slow I/O), another possible issue might be the xtrabackup binary you are using. What version of MySQL are you backing up? If you are backing up MySQL 5.6, then the issue could be that you are not using the xtrabackup_56 binary. If that is not the case, then I would try using the latest version of Xtrabackup to see if that helps (if you are not doing so already).

[url]Percona XtraBackup

If that is not the issue, then unfortunately I do not have much else to offer at this point since it’s a fairly common error message with no clear solutions for every case.

-Scott

3

Back from holiday…

Thanks for your reply. I already use the newest binary, so I’m somewhat at a loss right now. If I find a solution I will post it back to the board.

Thanks again.

4

The probleem seems to be solved:

We found a way to dramitcally reduce the amount of updates while backing up, but still xtrabackup would crash. It was only when the logfiles were configured back to 3x 1GB that the backups would succeed. I’m not certain, but I think in the end, the large logfiles were part of the problem, allthough the large amounts of transactions must have been causing the original problem.