Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I use MySQL Enterprise Backup to make incremental backups, but when I apply a restore (copy-back), I can't remake a backup.

I get this error:

mysqlbackup: ERROR: Log scan was only able to reach 1667072,
           but a checkpoint was at 1667505.
           This means that the database server has overwritten
           a part of the circular InnoDB log files before ibbackup
           was able to read it.
           To solve this problem either:
           1) re-run the backup when the database has less load, or
           2) re-configure the database with bigger InnoDB log files.

Config:

--------------------------------------------------------------------
                       Server Repository Options:
-------------------------------------------------------------------- 
datadir = /common/mysql/xxx/data/
innodb_data_home_dir = /common/mysql/xxx/data/
innodb_data_file_path = ibdata1:50M:autoextend
innodb_log_group_home_dir = /common/mysql/xxx/data/
innodb_log_files_in_group = 2
innodb_log_file_size = 26214400

--------------------------------------------------------------------
                       Backup Config Options:
--------------------------------------------------------------------
datadir = /common/mysql/testb/full/latest/datadir  
innodb_data_home_dir = /common/mysql/testb/full/latest/datadir  
innodb_data_file_path = ibdata1:50M:autoextend  
innodb_log_group_home_dir = /common/mysql/testb/full/latest/datadir  
innodb_log_files_in_group = 2
innodb_log_file_size = 26214400
share|improve this question

migrated from stackoverflow.com Dec 10 '12 at 19:15

This question came from our site for professional and enthusiast programmers.

2 Answers 2

I'm afraid your chance to do the incremental has passed.

You need to do two things:

Resize the InnoDB transaction logs

From the messages above, the logs look like 25600K.

You need much bigger logs because writes to the logs are going so fast that incremental backups will lose it place while backing up as you just experienced. You barely have about 512M with ib_logfile0 and ib_logfile1. The fact that an incremental fails shows you are writing more than 512M of transaction info during an incremental backup cycle.

In MySQL, the largest log allowed is 2047M.

Please do the following

STEP01) Run this in the mysql client:

SET GLOBAL innodb_fast_shutdown = 0;

This will cause all transactions to be completely committed to disk. This will shorten mysql startup time because InnoDB Crash Recovery is not needed (or at least drastically minimized).

STEP02) service mysql stop

STEP03) Edit my.cnf with this

[mysqld]
innodb_log_file_size = 2047M
innodb_fast_shutdown = 0

STEP04) Move the logs away

cd /var/lib/mysql
mv ib_logilfe0 ib_logilfe0.bak
mv ib_logilfe0 ib_logilfe0.bak

STEP05) service mysql start (Transaction Logs get recreated sized 2047M)

Please see How to safely change MySQL innodb variable 'innodb_log_file_size'?

Take a fresh Full Backup

Once you take this backup, you now have transaction logs big enough to endure a long-running incremental backup. It is still advisable to do increment backups during offpeak times.

Even with the transaction logs resized to 2047M, the only way an incremental backup's restoration could fail is if more than 4094M of transaction info is written to the log files during the incremental backup cycle.

UPDATE 2012-12-17 11:39 EDT

DISCLAIMER : Not Familiar with MySQL Enterprise Backup

I learned about 1.5 years ago that Percona XtraBackup was similar to MySQL Enteprise Backup. According to the Documentation on XtraBackup's full backup, you are supposed to prepare the Full Backup for restoration. This step is necessary because MySQL may have been writing transactions that are probably present in the double-write buffer that have not been accounted for in any other parts of the InnoDB infrastructure. That way, the following must be done:

  • transactions written during the backup are extracted from the double-write buffer
  • those transaction are executed
  • InnoDB files for the Backups are updated with proper Log Sequence Numbers

As for XtraBackup's Incremental Backup, the Full Backup must be prepared as well.

If the claim that Percona XtraBackup was similar to MySQL Enteprise Backup is indeed true, then there must be a set of preparatory steps that needs to be done once or twice to the Full Backup. Please read in the MySQL Enterprise Backup Documentation and find any reference possible to preparing Full Backups.

UPDATE 2012-12-17 12:55 EDT

After reading the MySQL Documentation on MySQL Enterprise Backup, I noticed something that may help. You stated that you do this:

mysqlbackup --backup-dir=/mnt/testb/full/latest --user=dba backup
mysqlbackup --backup-dir=backupDir/latest apply-log

I think you have a typo:

mysqlbackup --backup-dir=/mnt/testb/full/latest --user=dba backup
mysqlbackup --backup-dir=backupDir/latest --apply-log

I do not see apply-log in the Docs, but I do see these in the Docs

  • --apply-log
  • --backup-and-apply-log

Please try one of these options and see if it corrects matters

  • mysqlbackup --backup-dir=/mnt/testb/full/latest --user=dba backup-and-apply-log
  • mysqlbackup --backup-dir=backupDir/latest --apply-log
share|improve this answer
    
Thanks ! I've the same problem after a restore backup... It's impossible to backup again. I've tried with 2047M and less. Do you have a exemple of config with good performances ? –  Fridus Dec 11 '12 at 10:28

I still get the same problem. After a restore, I can't redo a new backup

Config:

--------------------------------------------------------------------
                       Server Repository Options:
--------------------------------------------------------------------   
datadir = /common/mysql/xxx/data/   
innodb_data_home_dir = /common/mysql/xxx/data/   
innodb_data_file_path = ibdata1:50M:autoextend   
innodb_log_group_home_dir = /common/mysql/xxx/data/   
innodb_log_files_in_group = 2   
innodb_log_file_size = 1073741824

--------------------------------------------------------------------
                       Backup Config Options:
--------------------------------------------------------------------   
datadir = /mnt/testb/full/latest/datadir   
innodb_data_home_dir = /mnt/testb/full/latest/datadir   
innodb_data_file_path = ibdata1:50M:autoextend   
innodb_log_group_home_dir = /mnt/testb/full/latest/datadir   
innodb_log_files_in_group = 2   
innodb_log_file_size = 1073741824

mysqlbackup: INFO: Unique generated backup id for this is 13553227045920706

Config file (innodb):

innodb_data_home_dir = /common/mysql/xxx/data/
innodb_data_file_path = ibdata1:50M:autoextend
innodb_log_group_home_dir = /common/mysql/xxx/data/
innodb_log_files_in_group        =  2
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 1024M
innodb_log_buffer_size = 32M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_fast_shutdown = 0

My Error:

121212 15:31:48 mysqlbackup: INFO: Completing the copy of all non-innodb files.
 mysqlbackup: ERROR: Log scan was only able to reach 1851404,
          but a checkpoint was at 1851898.
          This means that the database server has overwritten
          a part of the circular InnoDB log files before ibbackup
          was able to read it.
          To solve this problem either:
          1) re-run the backup when the database has less load, or
          2) re-configure the database with bigger InnoDB log files.

Maybe I'm doing wrong procedure...

Backup command :

mysqlbackup --backup-dir=/mnt/testb/full/latest --user=dba backup
mysqlbackup --backup-dir=backupDir/latest apply-log

Restore command :

mysqladmin --defaults-file=PathConfig --user=root --password=rootPassword shutdown && \
mysqlbackup --defaults-file=pathConfig --backup-dir=backupDir --user=dba copy-back
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.