InnoDB files for the Backups are updated with proper Log Sequence NumbersĪs for XtraBackup's Incremental Backup, the Full Backup must be prepared as well.transactions written during the backup are extracted from the double-write buffer.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. According to the Documentation on XtraBackup's full backup, you are supposed to prepare the Full Backup for restoration. I learned about 1.5 years ago that Percona XtraBackup was similar to MySQL Enteprise Backup.
UPDATE 11:39 EDTĭISCLAIMER : Not Familiar with MySQL Enterprise Backup It is still advisable to do increment backups during offpeak times.Įven 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. Once you take this backup, you now have transaction logs big enough to endure a long-running incremental backup.
Please see How to safely change MySQL innodb variable 'innodb_log_file_size'? Take a fresh Full Backup STEP05) service mysql start (Transaction Logs get recreated sized 2047M) STEP04) Move the logs away cd /var/lib/mysql This will shorten mysql startup time because InnoDB Crash Recovery is not needed (or at least drastically minimized). This will cause all transactions to be completely committed to disk. STEP01) Run this in the mysql client: SET GLOBAL innodb_fast_shutdown = 0 In MySQL, the largest log allowed is 2047M. The fact that an incremental fails shows you are writing more than 512M of transaction info during an incremental backup cycle. You barely have about 512M with ib_logfile0 and ib_logfile1. 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 need to do two things: Resize the InnoDB transaction logsįrom the messages above, the logs look like 25600K. I'm afraid your chance to do the incremental has passed.
Innodb_log_group_home_dir = /common/mysql/testb/full/latest/datadir Innodb_data_home_dir = /common/mysql/testb/full/latest/datadir Innodb_log_group_home_dir = /common/mysql/xxx/data/ĭatadir = /common/mysql/testb/full/latest/datadir Innodb_data_file_path = ibdata1:50M:autoextend Innodb_data_home_dir = /common/mysql/xxx/data/ This means that the database server has overwrittenĪ part of the circular InnoDB log files before ibbackupġ) re-run the backup when the database has less load, orĢ) re-configure the database with bigger InnoDB log files.Ĭonfig:. I get this error: mysqlbackup: ERROR: Log scan was only able to reach 1667072,
I use MySQL Enterprise Backup to make incremental backups, but when I apply a restore (copy-back), I can't remake a backup.