Unable to restore incremental data

For Full backup :

  1. xtrabackup --backup --target-dir=/root/bkp/dbbkp/20472021/base/ -u root -ptesting

output is :
xtrabackup: Transaction log of lsn (39916443) to (39916463) was copied.
210724 01:38:19 completed OK!

Prepare the backup:
2) xtrabackup --prepare --target-dir=/root/bkp/dbbkp/24072021/base/
output is :
210724 03:18:54 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS…
xtrabackup: The latest check point (for incremental): ‘39971479’
xtrabackup: Stopping log copying thread at LSN 39971489.
210724 03:18:54 >> log scanned up to (39971489)
Starting to parse redo log at lsn = 39971439

210724 03:18:55 Executing UNLOCK INSTANCE
210724 03:18:55 All tables unlocked
210724 03:18:55 [00] Copying ib_buffer_pool to /root/bkp/dbbkp/24072021/base/ib_buffer_pool
210724 03:18:55 [00] …done
210724 03:18:55 Backup created in directory ‘/root/bkp/dbbkp/24072021/base/’
MySQL binlog position: filename ‘mysql-bin.000048’, position ‘236’, GTID of the last change ‘05d5db67-7a77-11eb-872e-000c2935c0fa:6,26201052-7a76-11eb-b772-000c293d6e27:1-93,4996f581-e547-11eb-bc13-000c293d6e27:1-32,4ef94f4b-7a83-11eb-bcff-005056ac5709:9-10:16-21,eced1025-ec66-11eb-9ad4-000c293d6e27:1’
210724 03:18:55 [00] Writing /root/bkp/dbbkp/24072021/base/backup-my.cnf
210724 03:18:55 [00] …done
210724 03:18:55 [00] Writing /root/bkp/dbbkp/24072021/base/xtrabackup_info
210724 03:18:55 [00] …done
xtrabackup: Transaction log of lsn (39971479) to (39971499) was copied.
210724 03:18:56 completed OK!
Output of xtrabackup_checkpoints:
cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 39971479
last_lsn = 39971489
flushed_lsn = 39971479

Now i have created database and delete another database tables
3) Now i will take the incremental backup
xtrabackup --backup --target-dir=/root/bkp/dbbkp/24072021/inc1/ --incremental-basedir=/root/bkp/dbbkp/24072021/base/ -u root -ptesting

xtrabackup: The latest check point (for incremental): ‘39996191’
xtrabackup: Stopping log copying thread at LSN 39997222.
Starting to parse redo log at lsn = 39995974
210724 03:28:58 >> log scanned up to (39997222)

210724 03:28:59 Executing UNLOCK INSTANCE
210724 03:28:59 All tables unlocked
210724 03:28:59 [00] Copying ib_buffer_pool to /root/bkp/dbbkp/24072021/inc1/ib_buffer_pool
210724 03:28:59 [00] …done
210724 03:28:59 Backup created in directory ‘/root/bkp/dbbkp/24072021/inc1/’
MySQL binlog position: filename ‘mysql-bin.000049’, position ‘276’, GTID of the last change ‘05d5db67-7a77-11eb-872e-000c2935c0fa:6,26201052-7a76-11eb-b772-000c293d6e27:1-93,4996f581-e547-11eb-bc13-000c293d6e27:1-32,4ef94f4b-7a83-11eb-bcff-005056ac5709:9-10:16-21,6333c4c3-ec68-11eb-8365-000c293d6e27:1-5,eced1025-ec66-11eb-9ad4-000c293d6e27:1’
210724 03:28:59 [00] Writing /root/bkp/dbbkp/24072021/inc1/backup-my.cnf
210724 03:28:59 [00] …done
210724 03:28:59 [00] Writing /root/bkp/dbbkp/24072021/inc1/xtrabackup_info
210724 03:28:59 [00] …done
xtrabackup: Transaction log of lsn (39996191) to (39997232) was copied.
210724 03:28:59 completed OK!

Prepare the incremental backup to base
4) xtrabackup --prepare --apply-log-only --target-dir=/root/bkp/dbbkp/24072021/base/ --incremental-basedir=/root/bkp/dbbkp/24072021/inc1/
Output is :
xtrabackup --prepare --apply-log-only --target-dir=/root/bkp/dbbkp/24072021/base --incremental-basedir=/root/bkp/dbbkp/24072021/inc1
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksums=1 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2 --server-id=0 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0 --innodb_undo_log_encrypt=0
xtrabackup: recognized client arguments: --prepare=1 --apply-log-only=1 --target-dir=/root/bkp/dbbkp/24072021/base --incremental-basedir=/root/bkp/dbbkp/24072021/inc1
xtrabackup version 8.0.25-17 based on MySQL server 8.0.25 Linux (x86_64) (revision id: d27028b)
xtrabackup: cd to /root/bkp/dbbkp/24072021/base/
xtrabackup: This target seems to be not prepared yet.
Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(39971479)
xtrabackup: using the following InnoDB configuration for recovery:
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 = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: inititialize_service_handles suceeded
xtrabackup: using the following InnoDB configuration for recovery:
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 = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
PUNCH HOLE support available
Uses event mutexes
GCC builtin __atomic_thread_fence() is used for memory barrier
Compressed tables use zlib 1.2.11
Number of pools: 1
Using CPU crc32 instructions
Directories to scan ‘./’
Scanning ‘./’
Completed space ID check of 12 files.
Initializing buffer pool, total size = 128.000000M, instances = 1, chunk size =128.000000M
Completed initialization of buffer pool
page_cleaner coordinator priority: -20
page_cleaner worker priority: -20
page_cleaner worker priority: -20
page_cleaner worker priority: -20
The log sequence number 39944204 in the system tablespace does not match the log sequence number 39971479 in the ib_logfiles!
Database was not shutdown normally!
Starting crash recovery.
Starting to parse redo log at lsn = 39971439, whereas checkpoint_lsn = 39971479 and start_lsn = 39971328
Doing recovery: scanned up to log sequence number 39971489
Log background threads are being started…
Applying a batch of 1 redo log records …
100%
Apply batch completed!
Using undo tablespace ‘./undo_001’.
Using undo tablespace ‘./undo_002’.
Opened 2 existing undo tablespaces.
GTID recovery trx_no: 32019
Creating shared tablespace for temporary tables
Setting file ‘./ibtmp1’ size to 12 MB. Physically writing the file full; Please wait …
File ‘./ibtmp1’ size is now 12 MB.
Scanning temp tablespace dir:’./#innodb_temp/’
Created 128 and tracked 128 new rollback segment(s) in the temporary tablespace. 128 are now active.
8.0.25 started; log sequence number 39971489
Allocated tablespace ID 17 for example/student, old maximum was 0
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
FTS optimize thread exiting.
Starting shutdown…
Log background threads are being closed…
Shutdown completed; log sequence number 39971489
Number of pools: 1
210724 03:30:18 completed OK!

  1. Now i have to restore the base into /var/lib/mysql using the command rsync. I saw full backup data but unable to see the incremental data (create database and delete tables in another database).

How will do it restore the incremental on top of base

Hello @gopalakrishna99,
Your step 2 is wrong. You cannot prepare the full backup before taking the incremental. You need to check the manual and understand --apply-log-only process. Our documentation has examples on how to properly do incremental backup and restore.

1 Like