Perform Mysql Physical Backup using Percona Xtrabackup

November 18, 2021
()

Perform Mysql Physical Backup using Percona Xtrabackup

The Percona XtraBackup tools provide a method of performing a hot backup of your MySQL data while the system is running. Percona XtraBackup is a free, online, open source, complete database backups solution for all versions of Percona Server for MySQL and MySQL. Percona XtraBackup performs online non-blocking, tightly compressed, highly secure full backups on transactional systems so that applications remain fully available during planned maintenance windows. Percona Xtrabackup also supports incremental backup where it can copy the data that has changed since the last backup. You can have many incremental backups between each full backup. For every incremental backup, you need information on the last one you did so it knows where to start the new one.

In a recent post, we saw Steps to Install Percona XtraBackup Click here to read more. In this post, we will see How to perform Physical Backup using XtraBackup.

Below are the backup types we will perform using XtraBackup
1. Full Backup
2. Incremental Backup
3. Compressed Backup


1. Full Backup: To create a backup, run xtrabackup with the –backup option. You also need to specify the –target-dir option to store backup files. After the backup is finished, the target directory will contain all files from datadir. Please note we are using here root user but you can create your own backup user with the required privileges.

[root@test-machine01 ~]#
[root@test-machine01 ~]# su - mysql
Last login: Wed Nov 17 11:36:07 +03 2021 on pts/0
[mysql@test-machine01 ~]$
[mysql@test-machine01 ~]$ cd /u01
[mysql@test-machine01 u01]$
[mysql@test-machine01 u01]$ mkdir -p mysql_bkp/full_bkp
[mysql@test-machine01 u01]$ mkdir -p mysql_bkp/base_bkp
[mysql@test-machine01 u01]$ mkdir -p mysql_bkp/incr1_bkp
[mysql@test-machine01 u01]$ mkdir -p mysql_bkp/incr2_bkp
[mysql@test-machine01 u01]$ mkdir -p mysql_bkp/full_comp1_bkp
[mysql@test-machine01 u01]$ mkdir -p mysql_bkp/full_comp2_bkp
[mysql@test-machine01 u01]$ cd mysql_bkp
[mysql@test-machine01 mysql_bkp]$

[mysql@test-machine01 mysql_bkp]$ xtrabackup --backup --target-dir=/u01/mysql_bkp/full_bkp/ -uroot -p
xtrabackup: recognized client arguments: --backup=1 --target-dir=/u01/mysql_bkp/full_bkp/ --user=root --password
Enter password:
xtrabackup version 8.0.26-18 based on MySQL server 8.0.26 Linux (x86_64) (revision id: 4aecf82)
211117 11:48:27  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root'  (using password: YES).
Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup','root',...) failed: Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory at - line 1535.
211117 11:48:27 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
Using server version 8.0.22
211117 11:48:27 Executing LOCK INSTANCE FOR BACKUP...
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql/
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
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 = 2
xtrabackup:   innodb_log_file_size = 50331648
Number of pools: 1
xtrabackup: inititialize_service_handles suceeded
211117 11:48:27 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
xtrabackup: Redo Log Archiving is not set up.
211117 11:48:27 >> log scanned up to (1061140266)
xtrabackup: Generating a list of tablespaces
xtrabackup: Generating a list of tablespaces
Scanning './'
Directory '/var/lib/mysql/.cache' will not be scanned because it is a hidden directory.
Directory '/var/lib/mysql/.config' will not be scanned because it is a hidden directory.
Directory '/var/lib/mysql/.bash_history' will not be scanned because it is a hidden directory.
Directory '/var/lib/mysql/.bash_profile' will not be scanned because it is a hidden directory.
Scanning '/u01/mysql/'
Completed space ID check of 2 files.
Allocated tablespace ID 74 for grouprepl_test/t1, old maximum was 0
Using undo tablespace './undo_001'.
Using undo tablespace './undo_002'.
Opened 2 existing undo tablespaces.
211117 11:48:27 [01] Copying ./ibdata1 to /u01/mysql_bkp/full_bkp/ibdata1
211117 11:48:27 [01]        ...done
211117 11:48:27 [01] Copying ./sys/sys_config.ibd to /u01/mysql_bkp/full_bkp/sys/sys_config.ibd
211117 11:48:27 [01]        ...done
211117 11:48:27 [01] Copying ./grouprepl_test/t1.ibd to /u01/mysql_bkp/full_bkp/grouprepl_test/t1.ibd
211117 11:48:27 [01]        ...done
211117 11:48:27 [01] Copying ./mysql_innodb_cluster_metadata/clusters.ibd to /u01/mysql_bkp/full_bkp/mysql_innodb_cluster_metadata/clusters.ibd
211117 11:48:27 [01]        ...done

211117 11:48:28 [01]        ...done
211117 11:48:28 [01] Copying performance_schema/clone_progress_507.sdi to /u01/mysql_bkp/full_bkp/performance_schema/clone_progress_507.sdi
211117 11:48:28 [01]        ...done
211117 11:48:28 [00] Writing /u01/mysql_bkp/full_bkp/test/db.opt
211117 11:48:28 [00]        ...done
211117 11:48:28 Finished backing up non-InnoDB tables and files
211117 11:48:28 Executing FLUSH NO_WRITE_TO_BINLOG BINARY LOGS
211117 11:48:28 Selecting LSN and binary log position from p_s.log_status
211117 11:48:28 [00] Copying /var/lib/mysql/binlog.000019 to /u01/mysql_bkp/full_bkp/binlog.000019 up to position 156
211117 11:48:28 [00]        ...done
211117 11:48:28 [00] Writing /u01/mysql_bkp/full_bkp/binlog.index
211117 11:48:28 [00]        ...done
211117 11:48:28 [00] Writing /u01/mysql_bkp/full_bkp/xtrabackup_binlog_info
211117 11:48:28 [00]        ...done
211117 11:48:28 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1061140266'
xtrabackup: Stopping log copying thread at LSN 1061140266.
Starting to parse redo log at lsn = 1061140014
211117 11:48:28 Executing UNLOCK INSTANCE
211117 11:48:28 All tables unlocked
211117 11:48:28 [00] Copying ib_buffer_pool to /u01/mysql_bkp/full_bkp/ib_buffer_pool
211117 11:48:28 [00]        ...done
211117 11:48:28 Backup created in directory '/u01/mysql_bkp/full_bkp/'
MySQL binlog position: filename 'binlog.000019', position '156', GTID of the last change '13ecba9c-444a-11eb-a397-000c29f9d9e6:1,4cb6a75d-9ac5-11eb-98d9-000c293ab022:1-4871:1000012-1000187,81646864-9069-11eb-bb04-000c293ab022:1-98'
211117 11:48:28 [00] Writing /u01/mysql_bkp/full_bkp/backup-my.cnf
211117 11:48:28 [00]        ...done
211117 11:48:28 [00] Writing /u01/mysql_bkp/full_bkp/xtrabackup_info
211117 11:48:28 [00]        ...done
xtrabackup: Transaction log of lsn (1061140266) to (1061140276) was copied.
211117 11:48:30 completed OK!
[mysql@test-machine01 mysql_bkp]$

[mysql@test-machine01 mysql_bkp]$
[mysql@test-machine01 mysql_bkp]$ cd full_bkp
[mysql@test-machine01 full_bkp]$ ls -ltr
total 61508
-rw-r-----. 1 mysql mysql 12582912 Nov 17 11:48 ibdata1
drwxr-x---. 2 mysql mysql       28 Nov 17 11:48 sys
drwxr-x---. 2 mysql mysql       20 Nov 17 11:48 grouprepl_test
drwxr-x---. 2 mysql mysql     4096 Nov 17 11:48 mysql_innodb_cluster_metadata
drwxr-x---. 2 mysql mysql     4096 Nov 17 11:48 employees
drwxr-x---. 2 mysql mysql     4096 Nov 17 11:48 repl_test
drwxr-x---. 2 mysql mysql       93 Nov 17 11:48 repl_test1
-rw-r-----. 1 mysql mysql 25165824 Nov 17 11:48 mysql.ibd
-rw-r-----. 1 mysql mysql 12582912 Nov 17 11:48 undo_002
-rw-r-----. 1 mysql mysql 12582912 Nov 17 11:48 undo_001
drwxr-x---. 2 mysql mysql     4096 Nov 17 11:48 mysql
drwxr-x---. 2 mysql mysql     8192 Nov 17 11:48 performance_schema
drwxr-x---. 2 mysql mysql       20 Nov 17 11:48 test
-rw-r-----. 1 mysql mysql      156 Nov 17 11:48 binlog.000019
-rw-r-----. 1 mysql mysql       29 Nov 17 11:48 binlog.index
-rw-r-----. 1 mysql mysql      159 Nov 17 11:48 xtrabackup_binlog_info
-rw-r-----. 1 mysql mysql     2560 Nov 17 11:48 xtrabackup_logfile
-rw-r-----. 1 mysql mysql      108 Nov 17 11:48 xtrabackup_checkpoints
-rw-r-----. 1 mysql mysql     4275 Nov 17 11:48 ib_buffer_pool
-rw-r-----. 1 mysql mysql      475 Nov 17 11:48 backup-my.cnf
-rw-r-----. 1 mysql mysql      138 Nov 17 11:48 xtrabackup_tablespaces
-rw-r-----. 1 mysql mysql      643 Nov 17 11:48 xtrabackup_info
[mysql@test-machine01 full_bkp]$


2. Incremental Backup: To make an incremental backup, we need to take first a full backup. This full backup will become the base of the next incremental backup. The xtrabackup utility writes a file called xtrabackup_checkpoints into the backup’s target directory. This file contains a line showing the to_lsn, which is the database’s LSN at the end of the backup.


Full Backup : We will take full backup in target directory : /u01/mysql_bkp/base_bkp/

[mysql@test-machine01 incr2_bkp]$
[mysql@test-machine01 incr2_bkp]$ xtrabackup --backup --target-dir=/u01/mysql_bkp/base_bkp/ -u root -p
xtrabackup: recognized client arguments: --backup=1 --target-dir=/u01/mysql_bkp/base_bkp/ --user=root --password
Enter password:
xtrabackup version 8.0.26-18 based on MySQL server 8.0.26 Linux (x86_64) (revision id: 4aecf82)
211117 16:55:53  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root'  (using password: YES).
Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup','root',...) failed: Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory at - line 1535.
211117 16:55:53 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
Using server version 8.0.22
211117 16:55:54 Executing LOCK INSTANCE FOR BACKUP...
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql/
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
211117 16:56:07 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1061140326'
xtrabackup: Stopping log copying thread at LSN 1061140336.
211117 16:56:07 >> log scanned up to (1061140336)
Starting to parse redo log at lsn = 1061140014

211117 16:56:09 Executing UNLOCK INSTANCE
211117 16:56:09 All tables unlocked
211117 16:56:09 [00] Copying ib_buffer_pool to /u01/mysql_bkp/base_bkp/ib_buffer_pool
211117 16:56:09 [00]        ...done
211117 16:56:09 Backup created in directory '/u01/mysql_bkp/base_bkp/'
MySQL binlog position: filename 'binlog.000022', position '156', GTID of the last change '13ecba9c-444a-11eb-a397-000c29f9d9e6:1,4cb6a75d-9ac5-11eb-98d9-000c293ab022:1-4871:1000012-1000187,81646864-9069-11eb-bb04-000c293ab022:1-98'
211117 16:56:09 [00] Writing /u01/mysql_bkp/base_bkp/backup-my.cnf
211117 16:56:09 [00]        ...done
211117 16:56:09 [00] Writing /u01/mysql_bkp/base_bkp/xtrabackup_info
211117 16:56:09 [00]        ...done
xtrabackup: Transaction log of lsn (1061140326) to (1061140346) was copied.
211117 16:56:10 completed OK!
[mysql@test-machine01 incr2_bkp]$

[mysql@test-machine01 mysql_bkp]$
[mysql@test-machine01 mysql_bkp]$ cd base_bkp
[mysql@test-machine01 base_bkp]$ ls -ltr
total 61508
-rw-r-----. 1 mysql mysql 12582912 Nov 17 16:56 ibdata1
drwxr-x---. 2 mysql mysql       28 Nov 17 16:56 sys
drwxr-x---. 2 mysql mysql       20 Nov 17 16:56 grouprepl_test
drwxr-x---. 2 mysql mysql     4096 Nov 17 16:56 mysql_innodb_cluster_metadata
drwxr-x---. 2 mysql mysql     4096 Nov 17 16:56 employees
drwxr-x---. 2 mysql mysql     4096 Nov 17 16:56 repl_test
drwxr-x---. 2 mysql mysql       93 Nov 17 16:56 repl_test1
-rw-r-----. 1 mysql mysql 25165824 Nov 17 16:56 mysql.ibd
-rw-r-----. 1 mysql mysql 12582912 Nov 17 16:56 undo_002
-rw-r-----. 1 mysql mysql 12582912 Nov 17 16:56 undo_001
drwxr-x---. 2 mysql mysql     4096 Nov 17 16:56 mysql
drwxr-x---. 2 mysql mysql     8192 Nov 17 16:56 performance_schema
drwxr-x---. 2 mysql mysql       20 Nov 17 16:56 test
-rw-r-----. 1 mysql mysql      156 Nov 17 16:56 binlog.000022
-rw-r-----. 1 mysql mysql       29 Nov 17 16:56 binlog.index
-rw-r-----. 1 mysql mysql      159 Nov 17 16:56 xtrabackup_binlog_info
-rw-r-----. 1 mysql mysql     2560 Nov 17 16:56 xtrabackup_logfile
-rw-r-----. 1 mysql mysql      108 Nov 17 16:56 xtrabackup_checkpoints
-rw-r-----. 1 mysql mysql     4275 Nov 17 16:56 ib_buffer_pool
-rw-r-----. 1 mysql mysql      475 Nov 17 16:56 backup-my.cnf
-rw-r-----. 1 mysql mysql      138 Nov 17 16:56 xtrabackup_tablespaces
-rw-r-----. 1 mysql mysql      644 Nov 17 16:56 xtrabackup_info
[mysql@test-machine01 base_bkp]$ cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 1061140326
last_lsn = 1061140336
flushed_lsn = 1061140326
[mysql@test-machine01 base_bkp]$

First Incremental Backup : Now that you have a full backup, we can make an incremental backup based on it. Using the below command. Please note we are providing new empty directory /u01/mysql_bkp/incr1_bkp/ as –target-dir and –incremental-basedir as our last full backup directory location /u01/mysql_bkp/base_bkp/ and after incremental backup /u01/mysql_bkp/incr1_bkp/ directory should now contain delta files, such as ibdata1.delta . Also from_lsn of incremetnal backup is same as to_lsn of full backup i.e. 1061140326. Also difference is huge of Full DB backup & Incremental DB Backup size where FullDB backup size is 243M and Incremental Backup size is just 2.4M.

[mysql@test-machine01 incr2_bkp]$
[mysql@test-machine01 incr2_bkp]$ xtrabackup --backup --target-dir=/u01/mysql_bkp/incr1_bkp/ --incremental-basedir=/u01/mysql_bkp/base_bkp/ -u root -p
xtrabackup: recognized client arguments: --backup=1 --target-dir=/u01/mysql_bkp/incr1_bkp/ --incremental-basedir=/u01/mysql_bkp/base_bkp/ --user=root --password
Enter password:
xtrabackup version 8.0.26-18 based on MySQL server 8.0.26 Linux (x86_64) (revision id: 4aecf82)
211117 16:56:49  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root'  (using password: YES).
Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup','root',...) failed: Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory at - line 1535.
211117 16:56:49 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
Using server version 8.0.22
211117 16:56:49 Executing LOCK INSTANCE FOR BACKUP...
incremental backup from 1061140326 is enabled.
xtrabackup: uses posix_fadvise().
211117 16:56:53 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1061140346'
xtrabackup: Stopping log copying thread at LSN 1061140346.
Starting to parse redo log at lsn = 1061140014

211117 16:56:53 Executing UNLOCK INSTANCE
211117 16:56:53 All tables unlocked
211117 16:56:53 [00] Copying ib_buffer_pool to /u01/mysql_bkp/incr1_bkp/ib_buffer_pool
211117 16:56:53 [00]        ...done
211117 16:56:53 Backup created in directory '/u01/mysql_bkp/incr1_bkp/'
MySQL binlog position: filename 'binlog.000023', position '156', GTID of the last change '13ecba9c-444a-11eb-a397-000c29f9d9e6:1,4cb6a75d-9ac5-11eb-98d9-000c293ab022:1-4871:1000012-1000187,81646864-9069-11eb-bb04-000c293ab022:1-98'
211117 16:56:53 [00] Writing /u01/mysql_bkp/incr1_bkp/backup-my.cnf
211117 16:56:53 [00]        ...done
211117 16:56:53 [00] Writing /u01/mysql_bkp/incr1_bkp/xtrabackup_info
211117 16:56:53 [00]        ...done
xtrabackup: Transaction log of lsn (1061140346) to (1061140356) was copied.
211117 16:56:54 completed OK!
[mysql@test-machine01 incr2_bkp]$
[mysql@test-machine01 mysql_bkp]$ cd incr1_bkp
[mysql@test-machine01 incr1_bkp]$
[mysql@test-machine01 incr1_bkp]$ ls -ltr
total 168
-rw-r-----. 1 mysql mysql    64 Nov 17 16:56 ibdata1.meta
-rw-r-----. 1 mysql mysql 32768 Nov 17 16:56 ibdata1.delta
drwxr-x---. 2 mysql mysql    61 Nov 17 16:56 sys
drwxr-x---. 2 mysql mysql    45 Nov 17 16:56 grouprepl_test
drwxr-x---. 2 mysql mysql  4096 Nov 17 16:56 mysql_innodb_cluster_metadata
drwxr-x---. 2 mysql mysql  4096 Nov 17 16:56 employees
drwxr-x---. 2 mysql mysql  4096 Nov 17 16:56 repl_test
drwxr-x---. 2 mysql mysql  4096 Nov 17 16:56 repl_test1
-rw-r-----. 1 mysql mysql    73 Nov 17 16:56 mysql.ibd.meta
-rw-r-----. 1 mysql mysql 16384 Nov 17 16:56 mysql.ibd.delta
-rw-r-----. 1 mysql mysql    69 Nov 17 16:56 undo_002.meta
-rw-r-----. 1 mysql mysql 16384 Nov 17 16:56 undo_002.delta
-rw-r-----. 1 mysql mysql    69 Nov 17 16:56 undo_001.meta
-rw-r-----. 1 mysql mysql 16384 Nov 17 16:56 undo_001.delta
drwxr-x---. 2 mysql mysql  4096 Nov 17 16:56 mysql
drwxr-x---. 2 mysql mysql  8192 Nov 17 16:56 performance_schema
drwxr-x---. 2 mysql mysql    20 Nov 17 16:56 test
-rw-r-----. 1 mysql mysql   156 Nov 17 16:56 binlog.000023
-rw-r-----. 1 mysql mysql    29 Nov 17 16:56 binlog.index
-rw-r-----. 1 mysql mysql   159 Nov 17 16:56 xtrabackup_binlog_info
-rw-r-----. 1 mysql mysql  2560 Nov 17 16:56 xtrabackup_logfile
-rw-r-----. 1 mysql mysql   115 Nov 17 16:56 xtrabackup_checkpoints
-rw-r-----. 1 mysql mysql  4275 Nov 17 16:56 ib_buffer_pool
-rw-r-----. 1 mysql mysql   475 Nov 17 16:56 backup-my.cnf
-rw-r-----. 1 mysql mysql   701 Nov 17 16:56 xtrabackup_info
-rw-r-----. 1 mysql mysql   138 Nov 17 16:56 xtrabackup_tablespaces
[mysql@test-machine01 incr1_bkp]$ cat xtrabackup_checkpoints
backup_type = incremental
from_lsn = 1061140326
to_lsn = 1061140346
last_lsn = 1061140346
flushed_lsn = 1061140346
[mysql@test-machine01 incr1_bkp]$

[mysql@test-machine01 mysql_bkp]$ du -sh *
243M    base_bkp
243M    full_bkp
2.4M    incr1_bkp
[mysql@test-machine01 mysql_bkp]$


Second Incremental Backup : We are providing new empty directory /u01/mysql_bkp/incr2_bkp/ as –target-dir and –incremental-basedir as our last incremental backup directory location /u01/mysql_bkp/incr1_bkp/ and after incremental backup /u01/mysql_bkp/incr2_bkp/ directory should now contain delta files, such as ibdata1.delta . Also from_lsn of incremetnal backup is same as to_lsn of first incremental backup i.e. 1061140346.

[mysql@test-machine01 incr2_bkp]$
[mysql@test-machine01 incr2_bkp]$ xtrabackup --backup --target-dir=/u01/mysql_bkp/incr2_bkp/ --incremental-basedir=/u01/mysql_bkp/incr1_bkp/ -u root -p
xtrabackup: recognized client arguments: --backup=1 --target-dir=/u01/mysql_bkp/incr2_bkp/ --incremental-basedir=/u01/mysql_bkp/incr1_bkp/ --user=root --password
Enter password:
xtrabackup version 8.0.26-18 based on MySQL server 8.0.26 Linux (x86_64) (revision id: 4aecf82)
211117 16:57:20  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root'  (using password: YES).
Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup','root',...) failed: Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory at - line 1535.
211117 16:57:20 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
Using server version 8.0.22
211117 16:57:20 Executing LOCK INSTANCE FOR BACKUP...
incremental backup from 1061140346 is enabled.
211117 16:57:23 Executing UNLOCK INSTANCE
211117 16:57:23 All tables unlocked
211117 16:57:23 [00] Copying ib_buffer_pool to /u01/mysql_bkp/incr2_bkp/ib_buffer_pool
211117 16:57:23 [00]        ...done
211117 16:57:23 Backup created in directory '/u01/mysql_bkp/incr2_bkp/'
MySQL binlog position: filename 'binlog.000024', position '156', GTID of the last change '13ecba9c-444a-11eb-a397-000c29f9d9e6:1,4cb6a75d-9ac5-11eb-98d9-000c293ab022:1-4871:1000012-1000187,81646864-9069-11eb-bb04-000c293ab022:1-98'
211117 16:57:23 [00] Writing /u01/mysql_bkp/incr2_bkp/backup-my.cnf
211117 16:57:23 [00]        ...done
211117 16:57:23 [00] Writing /u01/mysql_bkp/incr2_bkp/xtrabackup_info
211117 16:57:23 [00]        ...done
xtrabackup: Transaction log of lsn (1061140366) to (1061140376) was copied.
211117 16:57:25 completed OK!
[mysql@test-machine01 incr2_bkp]$
[mysql@test-machine01 mysql_bkp]$
[mysql@test-machine01 mysql_bkp]$ cd incr2_bkp
[mysql@test-machine01 incr2_bkp]$ ls -ltr
total 168
-rw-r-----. 1 mysql mysql    64 Nov 17 16:57 ibdata1.meta
-rw-r-----. 1 mysql mysql 32768 Nov 17 16:57 ibdata1.delta
drwxr-x---. 2 mysql mysql    61 Nov 17 16:57 sys
drwxr-x---. 2 mysql mysql    45 Nov 17 16:57 grouprepl_test
drwxr-x---. 2 mysql mysql  4096 Nov 17 16:57 mysql_innodb_cluster_metadata
drwxr-x---. 2 mysql mysql  4096 Nov 17 16:57 employees
drwxr-x---. 2 mysql mysql  4096 Nov 17 16:57 repl_test
drwxr-x---. 2 mysql mysql  4096 Nov 17 16:57 repl_test1
-rw-r-----. 1 mysql mysql    73 Nov 17 16:57 mysql.ibd.meta
-rw-r-----. 1 mysql mysql 16384 Nov 17 16:57 mysql.ibd.delta
-rw-r-----. 1 mysql mysql    69 Nov 17 16:57 undo_002.meta
-rw-r-----. 1 mysql mysql 16384 Nov 17 16:57 undo_002.delta
-rw-r-----. 1 mysql mysql    69 Nov 17 16:57 undo_001.meta
-rw-r-----. 1 mysql mysql 16384 Nov 17 16:57 undo_001.delta
drwxr-x---. 2 mysql mysql  4096 Nov 17 16:57 mysql
drwxr-x---. 2 mysql mysql    20 Nov 17 16:57 test
drwxr-x---. 2 mysql mysql  8192 Nov 17 16:57 performance_schema
-rw-r-----. 1 mysql mysql   156 Nov 17 16:57 binlog.000024
-rw-r-----. 1 mysql mysql    29 Nov 17 16:57 binlog.index
-rw-r-----. 1 mysql mysql   159 Nov 17 16:57 xtrabackup_binlog_info
-rw-r-----. 1 mysql mysql  2560 Nov 17 16:57 xtrabackup_logfile
-rw-r-----. 1 mysql mysql   115 Nov 17 16:57 xtrabackup_checkpoints
-rw-r-----. 1 mysql mysql  4275 Nov 17 16:57 ib_buffer_pool
-rw-r-----. 1 mysql mysql   475 Nov 17 16:57 backup-my.cnf
-rw-r-----. 1 mysql mysql   702 Nov 17 16:57 xtrabackup_info
-rw-r-----. 1 mysql mysql   138 Nov 17 16:57 xtrabackup_tablespaces
[mysql@test-machine01 incr2_bkp]$ cat xtrabackup_checkpoints
backup_type = incremental
from_lsn = 1061140346
to_lsn = 1061140366
last_lsn = 1061140366
flushed_lsn = 1061140366
[mysql@test-machine01 incr2_bkp]$


3. Compressed Backup: In order to make a compressed backup we need to use the –compress option

Compressed Backup: We can see the difference Full backup without compression is 243M and with compression just 80M. So we are getting 3x compression rate.

[mysql@test-machine01 mysql_bkp]$
[mysql@test-machine01 mysql_bkp]$ xtrabackup --backup --compress --target-dir=/u01/mysql_bkp/full_comp1_bkp/ -u root -p
xtrabackup: recognized client arguments: --backup=1 --compress --target-dir=/u01/mysql_bkp/full_comp1_bkp/ --user=root --password
Enter password:
xtrabackup version 8.0.26-18 based on MySQL server 8.0.26 Linux (x86_64) (revision id: 4aecf82)
211117 11:37:13  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root'  (using password: YES).
Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup','root',...) failed: Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory at - line 1535.
211117 11:37:13 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
Using server version 8.0.22
211117 11:37:13 Executing LOCK INSTANCE FOR BACKUP...
211117 11:37:13 [01] Compressing ./ibdata1 to /u01/mysql_bkp/full_comp1_bkp/ibdata1.qp
211117 11:37:13 [01]        ...done
211117 11:37:13 [01] Compressing ./sys/sys_config.ibd to /u01/mysql_bkp/full_comp1_bkp/sys/sys_config.ibd.qp
211117 11:37:13 [01]        ...done
211117 11:37:13 [01] Compressing ./grouprepl_test/t1.ibd to /u01/mysql_bkp/full_comp1_bkp/grouprepl_test/t1.ibd.qp
211117 11:37:13 [01]        ...done
211117 11:37:13 [01] Compressing ./mysql_innodb_cluster_metadata/clusters.ibd to /u01/mysql_bkp/full_comp1_bkp/mysql_innodb_cluster_metadata/clusters.ibd.qp
211117 11:37:13 [01]        ...done
211117 11:37:13 [01] Compressing ./mysql_innodb_cluster_metadata/instances.ibd to /u01/mysql_bkp/full_comp1_bkp/mysql_innodb_cluster_metadata/instances.ibd.qp
211117 11:37:13 [01]        ...done
211117 11:37:18 [00] Compressing /u01/mysql_bkp/full_comp1_bkp/backup-my.cnf.qp
211117 11:37:18 [00]        ...done
211117 11:37:18 [00] Compressing /u01/mysql_bkp/full_comp1_bkp/xtrabackup_info.qp
211117 11:37:18 [00]        ...done
xtrabackup: Transaction log of lsn (1061140226) to (1061140236) was copied.
211117 11:37:19 completed OK!
[mysql@test-machine01 mysql_bkp]$


[mysql@test-machine01 mysql_bkp]$
[mysql@test-machine01 mysql_bkp]$ du -sh full_bkp
243M    full_bkp
[mysql@test-machine01 mysql_bkp]$ du -sh full_comp1_bkp
80M     full_comp1_bkp
[mysql@test-machine01 mysql_bkp]$


Parallel Compressed Backup: If you want to speed up the compression you can use parallel compression, which can be enabled with –compress-threads option.

[mysql@test-machine01 mysql_bkp]$
[mysql@test-machine01 mysql_bkp]$ xtrabackup --backup --compress --compress-threads=4 --target-dir=/u01/mysql_bkp/full_comp2_bkp/ -u root -p
xtrabackup: recognized client arguments: --backup=1 --compress --compress-threads=4 --target-dir=/u01/mysql_bkp/full_comp2_bkp/ --user=root --password
Enter password:
xtrabackup version 8.0.26-18 based on MySQL server 8.0.26 Linux (x86_64) (revision id: 4aecf82)
211117 11:42:22  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root'  (using password: YES).
Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup','root',...) failed: Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory at - line 1535.
211117 11:42:22 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
Using server version 8.0.22
211117 11:42:22 Executing LOCK INSTANCE FOR BACKUP...
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql/
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
211117 11:42:25 [01] Compressing performance_schema/clone_progress_507.sdi to /u01/mysql_bkp/full_comp2_bkp/performance_schema/clone_progress_507.sdi.qp
211117 11:42:25 [01]        ...done
211117 11:42:25 [00] Compressing /u01/mysql_bkp/full_comp2_bkp/test/db.opt.qp
211117 11:42:25 [00]        ...done
211117 11:42:25 Finished backing up non-InnoDB tables and files
211117 11:42:25 Executing FLUSH NO_WRITE_TO_BINLOG BINARY LOGS
211117 11:42:25 Selecting LSN and binary log position from p_s.log_status
211117 11:42:25 [00] Compressing /var/lib/mysql/binlog.000018 to /u01/mysql_bkp/full_comp2_bkp/binlog.000018.qp up to position 156
211117 11:42:25 [00]        ...done
211117 11:42:25 [00] Compressing /u01/mysql_bkp/full_comp2_bkp/binlog.index.qp
211117 11:42:25 [00]        ...done
211117 11:42:25 [00] Compressing /u01/mysql_bkp/full_comp2_bkp/xtrabackup_binlog_info.qp
211117 11:42:25 [00]        ...done
211117 11:42:25 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '1061140246'
xtrabackup: Stopping log copying thread at LSN 1061140246.
Starting to parse redo log at lsn = 1061140014

211117 11:42:25 Executing UNLOCK INSTANCE
211117 11:42:25 All tables unlocked
211117 11:42:25 [00] Compressing ib_buffer_pool to /u01/mysql_bkp/full_comp2_bkp/ib_buffer_pool.qp
211117 11:42:25 [00]        ...done
211117 11:42:25 Backup created in directory '/u01/mysql_bkp/full_comp2_bkp/'
MySQL binlog position: filename 'binlog.000018', position '156', GTID of the last change '13ecba9c-444a-11eb-a397-000c29f9d9e6:1,4cb6a75d-9ac5-11eb-98d9-000c293ab022:1-4871:1000012-1000187,81646864-9069-11eb-bb04-000c293ab022:1-98'
211117 11:42:25 [00] Compressing /u01/mysql_bkp/full_comp2_bkp/backup-my.cnf.qp
211117 11:42:25 [00]        ...done
211117 11:42:25 [00] Compressing /u01/mysql_bkp/full_comp2_bkp/xtrabackup_info.qp
211117 11:42:25 [00]        ...done
xtrabackup: Transaction log of lsn (1061140246) to (1061140256) was copied.
211117 11:42:26 completed OK!
[mysql@test-machine01 mysql_bkp]$
[mysql@test-machine01 mysql_bkp]$


Reference: Percona XtraBackup Operations Manual: https://learn.percona.com/download-percona-xtrabackup-8.0-manual

This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.


Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.


Related Articles

, ,


Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Jamsher Khan

🌐

Hello and welcome to DBsGuru,I’m Jamsher Khan working as Senior Oracle DBA based in KSA-Jeddah, I have working experience in Oracle DBA, SQL Server, MySql, PostgreSQL, Linux, Golden Gate, ODA.Thanks for the visits!Share Learn Grow!

Leave a Reply

Your email address will not be published. Required fields are marked *