XtraBackup is one of Percona’s primary backup offerings, and it is well-known among DBAs as one of the primary open-source utilities for performing hot backups. The tool is known to avoid locking databases during its backup procedures – of course, it has a couple of limitations unique to itself, but that’s not an obstacle for experienced database administrators across the globe: the tool is loved by pretty much everyone who uses it, and with Percona on its side, we all know that the tool will deliver awesomeness by day and night.
What is Percona XtraBackup?
Percona XtraBackup, as previously stated, is one of the key services produced by Percona for MySQL and Percona database managers. The program is an open-source backup solution that does not lock our databases while performing backups. Percona claims that their tool can automatically verify backups, give rapid dumping and restore times, and, most importantly, it is supported by their award-winning consulting services, allowing us to ensure that our data and backups are safe day and night.
How To Install Percona XtraBackup?
In ubuntu
step 1.
wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
step 2.
sudo dpkg -i percona-release_latest.generic_all.deb
step 3.
sudo percona-release setup ps80
step 4.
apt-cache search percona-xtrabackup
step 5.
sudo apt install -y percona-xtrabackup-80
then, we can check which version is installed in our local. In that case we use “xtrabackup –version” option in OS.
Reference link – https://docs.percona.com/percona-xtrabackup/2.4/installation/apt_repo.html
In Rhel
Step 1.
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
Step 2.
wget https://repo.percona.com/yum/percona-release-latest.noarch.rpm
Step 3.
rpm -ivH percona-release-latest.noarch.rpm
Step 4.
Testing the repository
yum list | grep percona
Step 5.
To be able to make compressed backups, install the qpress package.
yum install qpress
Step 6.
Step 7.
you can install Percona XtraBackup by running.
yum localinstall percona-xtrabackup-24-2.4.28-1.el9.x86_64.rpm
Step 8.
Uninstalling Percona XtraBackup
yum remove percona-xtrabackup
Reference link – https://docs.percona.com/percona-xtrabackup/2.4/installation/yum_repo.html
Options – Percona XtraBackup
Percona XtraBackup, like any other command-line tool, provides a few options. Some of them are listed below.
Percona XtraBackup Option | Meaning |
–backup | Takes a backup of the database.rly. |
–check-privileges | Checks if Percona XtraBackup has all of the required privileges to be operating properly. |
–apply-log-only | Prepares to take incremental backups by ignoring all stages except the redo stage. |
–copy-back | Restores a backup. This option is meant to be used in conjunction with other options (see examples below.) |
–databases=x | –tables=x | “x” specifies a database or a table inside of a database to be backed up. These options are similar to –databases-file or –tables-file options that backs up databases or tables from a file. |
–defaults-file | Makes XtraBackup read only the options specified in the file after this parameter (the file will most likely be my.cnf.) |
I’ll do a full backup of Percona first, followed by an incremental backup. I’ll also demonstrate how to use Percona settings like –apply-log-only and –prepare.
Before we dive into the process, let’s define some key terms:
Full Backup: A complete backup of your database, including all data and metadata.
Incremental Backup: A backup that only includes changes made since the last full or incremental backup.
Transaction Log: A record of all transactions (changes) made to the database.
InnoDB: A popular storage engine used by MySQL.
Prerequisites
Install Percona XtraBackup on your server.
Ensure you have sufficient disk space to store the backup.
Verify that your MySQL server is running and accessible.
XtraBackup is designed to take backups without downtime, and it can indeed take backups in parallel with ongoing MySQL writes.
How can perform incremental and full Percona Xtrabackup?
Step 1:
A binary was used to install my MySQL. I therefore updated the xtrabackup command to include the socket path. The socket path does not need to be added if MySQL was not installed using a binary.
The command below will perform a full backup of your database, and then store the backup in a directory named “backup”
xtrabackup --backup --target-dir=/tmp/backup/ --user=root --host=localhost --password --socket=/home/genexdbs/percona2/percona.sockoptions:
--backup: This option tells xtrabackup to perform a backup operation.
--target-dir=/tmp/backup/: This option specifies the directory where the backup files will be stored.
--user=root: This option specifies the MySQL user account to use for the backup operation.
--host=localhost: This option specifies the hostname or IP address of the MySQL server to connect to.
--password: This option tells xtrabackup to prompt for a password for the MySQL user account specified with --user. If you want to provide the password directly, you can use the --password=<your_password> syntax instead.
--socket=/home/genexdbs/percona2/percona.sock: This option specifies the socket file to use for connecting to the MySQL server.
when the complete xtrabackup or any backup was completed successfully. We successfully finished the last output line.
Data was stored in the backup directory, as shown in the screenshot below.
Step 2.
Increamental backup
An incremental backup is a type of backup that only saves the changes made since the last backup. This approach is useful for reducing storage space and backup time.
How Incremental Backup Works ?
Full Backup: A full backup is performed initially, which creates a complete copy of the data.
Incremental Backups: Subsequent backups only save the changes made since the last backup.
Restore: To restore data, the system combines the full backup with the incremental backups to recreate the original data.
xtrabackup --backup --target-dir=/tmp/backupincl1 --user=root --host=localhost --password --socket=/home/genexdbs/percona2/percona.sock --incremental-basedir=/tmp/backup
As you can see in the image below, the incremental backup was finished successfully.
Why we need to use prepare and apply-log-only option?
Backup verification: By applying the transaction log to the backup files without modifying them, you can verify the integrity of the backup without altering the original files.
Backup preparation for restoration: Before restoring a backup, you need to apply the transaction log to ensure that the backup is consistent and ready for restoration.
Avoiding data corruption: By using –apply-log-only, you avoid modifying the original backup files, which reduces the risk of data corruption.
Step 1.
xtrabackup with –prepare and –apply-log-only options
xtrabackup --prepare --apply-log-only --target-dir=/tmp/backup --user=root --host=localhost --password=new –socket=/home/genexdbs/percona2/percona.sock
The original backup was the one below. view the size of the directory
There was a minor increase in the size of the backup directory after using prepare and apply-log-only in the previous full backup. Please refer to the image below.
Step 2.
xtrabackup --prepare --apply-log-only --target-dir=/tmp/backup --user=root --host=localhost --password=new --socket=/home/genexdbs/percona2/percona.sock --incremental-dir=/tmp/backupincl1When you use –incremental-dir, XtraBackup applies the transaction log to the incremental backup files, rather than the full backup files. This allows you to prepare the incremental backup for restoration.
The previous command without –incremental-dir applies the transaction log to the full backup files. This is useful for preparing the full backup for restoration.
Here are the key differences between the two commands:
Backup type: The previous command prepares a full backup, while the command with –incremental-dir prepares an incremental backup.
Transaction log application: The previous command applies the transaction log to the full backup files, while the command with –incremental-dir applies the transaction log to the incremental backup files.
Restoration: The prepared full backup can be restored directly, while the prepared incremental backup needs to be applied to the full backup to create a consistent restoration point.
Why do we only need to use prepare and not the apply-log-only option?
Effect of removing –apply-log-only
Apply the transaction log to the backup files.
Modify the backup files to bring them to a consistent state.
Here are the key differences between the two commands:
Backup modification: The previous command with –apply-log-only does not modify the backup files, while the current command without –apply-log-only modifies the backup files to bring them to a consistent state.
Transaction log application: Both commands apply the transaction log to the backup files, but the current command takes it a step further by modifying the files to ensure consistency.
( fyi – i am already took 2 more incremental backup that’s why i directly used different file name here)
The new incremental backup file (/tmp/backupincl2) contains different changes than the previous one (/tmp/backup).
Do not use same incremental backup file which you taken before with –apply-log-only option
xtrabackup --prepare --target-dir=/tmp/backup --user=root --host=localhost --password=new --socket=/home/genexdbs/percona2/percona.sock –incremental-dir=/tmp/backupincl2
How to do data copy-back?
Make sure to stop the MySQL server before copying the backup files back.
Verify that the backup files are consistent and complete before copying them back.
Use the –force-non-empty-directories option if the target directory is not empty.
The following command can be used if we need to see which MySQL or OS processes are active.
ps -ef | grep processName
I’m using binary formation to install MySQL. Since no systemctl link was automatically created, I used the following command to end the process.
Kill -9 processid
Before deleted the data directory here some file is there.
Now i deleted the data directory using the following command
rm -rf /path/to/dir_name
After deleted the data directory or data files the folder was gone/empty. See the below image for your reference.
Below command is xtrabackup copy back command.
I have modified the original filename for convenience.
For example.
Oldname – backup
Newname – xtrabackup
xtrabackup --copy-back --target-dir=/tmp/xtrabackup --user=root --host=localhost --password=new --socket=/home/genexdbs/percona2/percona.sock --datadir=/home/genexdbs/percona2/data
after the copy back command was successfully completed. The datadir path contained the file/data.
When we copied the data to the data_dir, the ownership of the data directory changed to root, thus we need to modify it. MySQL’s default user and group name is mysql. ( fyi – because i am used default user. So, i am not created any user for this). if we forgot to changed the ownership in the data directory. we getting below error and server also not started.
Changed the ownership using folowing command.
Chown -R mysql.mysql data ( the name data is data directory folder)
Chmod -R 755 /home/genexdbs/percona2/data
FYI: After deleting the current data directory, we are unable to use the same password. We can use the backup location where the server password was accepted once the new data directory has been restored in the same path. Once we have logged into the server, we can change the password.
Now server also came to active status. See the below for your reference.
Conclusion
Database engineers will undoubtedly continue to choose Percona XtraBackup for many years to come because it prevents database locking during backup procedures, takes backups quickly and safely, and enables the quick restoration of all backup types. Percona XtraBackup is the flagship tool in Percona’s backup arsenal and is used by both junior and senior database administrators. We hope that this blog post has given you some insight into the world of Percona’s XtraBackup. We hope to see you in the future blog.
References
https://www.red-gate.com/simple-talk/blogs/backing-up-mysql-part-2-percona-xtrabackup/
https://docs.percona.com/percona-xtrabackup/2.4/manual.html