I realize that the “log block numbers mismatch” error is a relativly common error and that it has been reported many times before but I cannot seem to get around the problem with the suggested fixes. The backup sometimes succeeds, probably about 30% of the times it is run, which is obviously not good enough.
I have tried to increase the innodb_log_file_size so that the log should contain a few hours of data and I have made sure that write IOPS isn’t a problem by trying to backup to a ramdisk. The server should be plenty powerful with Intel DC3700 SSD RAID1 for data and 15k SAS RAID10 with BBU for logs (also 12 cores latest gen Xeon and 128GB RAM).
The dataset is small at about 32GB but is updated frequently and the disks writes about a TB per day in total.
From what I understand the log size that I have now of 2GB should be plenty, perhaps even excessive, and I see no reason log copying should be too slow with these disks.
Any ideas what I can try?
Output of xtrabackup below.
=== Backing up database ===
Thu Apr 17 02:00:01 CEST 2014
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.
Get the latest version of Percona XtraBackup, documentation, and help resources:
140417 02:00:02 innobackupex: Connecting to MySQL server with DSN ‘dbi:mysql:;mysql_read_default_file=/root/.my.cnf;mysql_read_default_group=xtrabackup’ (using password: NO).
140417 02:00:02 innobackupex: Connected to MySQL server
140417 02:00:02 innobackupex: Executing a version check against the server…
140417 02:00:06 innobackupex: Done.
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.15-63.0-log
innobackupex: Created backup directory /root
140417 02:00:06 innobackupex: Starting ibbackup with command: xtrabackup_56 --defaults-extra-file="/root/.my.cnf" --defaults-group=“mysqld” --backup --suspend-at-end --target-dir=/var/tmp/mem --tmpdir=/var/tmp/mem --tables_file=’/var/tmp/mem/backup/tables_to_backup.txt’ --stream=tar
innobackupex: Waiting for ibbackup (pid=26846) to suspend
innobackupex: Suspend file ‘/var/tmp/mem/xtrabackup_suspended_2’
xtrabackup_56 version 2.1.8 for MySQL server 5.6.15 Linux (x86_64) (revision id: 733)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql/
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir =
xtrabackup: innodb_data_file_path = /opt/mysql/ibdata1:10M:autoextend:max:10G
xtrabackup: innodb_log_group_home_dir = /opt/mysql/
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 2147483648
xtrabackup: using O_DIRECT
xtrabackup: error: log block numbers mismatch:
xtrabackup: error: expected log block no. 507603581, but got no. 515992181 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 2622.
=== Backing up /etc ===
Thu Apr 17 02:05:38 CEST 2014