For Full backup :
- 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!
- 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