Backing Up MySQL Part 2: Percona XtraBackup

There’s no doubt about it – if you’ve ever heard of Percona, you’ve heard of XtraBackup as well. XtraBackup is one of the primary Percona’s offerings in the backup space: the tool is famous amongst DBAs as one of the primary open-source utilities to take 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 at its side, we all know that the tool is going to deliver awesomeness by day and by night.

What is Percona XtraBackup?

As already stated above, Percona XtraBackup is one of the primary offerings for MySQL & Percona database administrators developed by Percona. The tool is an open-source backup utility that does not lock our databases during the backup processes it performs. Percona says that their tool can provide automatic verification of backups that have been taken, offer fast dumping and restore times, and above all, it’s supported by their award-winning consulting services helping us make sure that our data and its backups are in safe hands by day and by night.

How To Use Percona XtraBackup?

In order to start exploring the features offered by Percona’s XtraBackup, please install the tool before proceeding any further – the tool can be installed from a repo, from a tarball, from packages, or via source code – the old-fashioned way of installing the tool from a repository is a favourite option for many. To install the tool from a repo, head over to Percona’s documentation, and once you’re done installing, you can backup your data by issuing the command below (the command below will perform a full backup of your database, and then store the backup in a directory named “backups”):
xtrabackup --backup --target-dir=/backups

Bear in mind that in this case, contrary to mysqldump, the “backups” directory won’t consist of only one file – Percona’s XtraBackup would backup the following:

• The my.cnf file that consists of the most vital information for MySQL to function correctly (XtraBackup will take a backup of the file and name it backup-my.cnf)
• The file vital for InnoDB to function correctly – ibdata1. Since ibdata1 holds data, indexes, Multiversion Concurrency Control (MVCC) data, and double write & insert buffers that are necessary for InnoDB to work in the way it does, without it InnoDB’s infrastructure would plummet to ashes.
• All databases inside of your MySQL infrastructure including the test and performance_schema databases.

Incremental backups can also be made in a very similar fashion: first, take a full backup using the command provided above, then issue a very similar statement, just add a --incremental-basedir statement at the end of it like so:
xtrabackup --backup --target-dir=/backups –incremental-basedir=/incbackups

In order to take a compressed backup, add the --compress option, and for partial backups, use one or more of the following options:

• Use the --databases or --databases-file options to back up a database or a list of databases from a file:
  xtrabackup --databases=”db1 db2 test_db demo_db”
xtrabackup --databases-file=databases.txt*
  * The file databases.txt would need to contain databases and tables in the format of database.table.
• Use the --tables or --tables-file options to back up a table or a list of tables in the same fashion you would back up databases.

Woohoo – you’ve now learned how to take backups using Percona XtraBackup! That’s not everything, though – when using Percona XtraBackup you would also find yourself needing to prepare your backups for them to be successfully restored: we will tell you how to do that in the next section.

Preparing XtraBackups

As you could have noticed, Percona XtraBackup doesn’t take backups in a simple backup.sql form you might be used to when using mysqldump or other database backup tools – instead, Percona XtraBackup often takes a backup of the files associated with the database with itself too (we have covered those in the previous chapter.)

That’s why all of the backups taken using Percona XtraBackup need to be prepared for recovery before they can be successfully recovered – here’s how to do that for each flavour of backups:

• To prepare a full or an encrypted backup to be restored, run the following command:
  xtrabackup --prepare --target-dir=backups/
• To prepare an incremental backup to be recovered, you would need to ensure that the rollback phase will be skipped by specifying an --apply-log-only option. Percona themselves state that if the rollback phase isn’t prevented, the incremental backups would be worth nothing and you would have to start over, so keep that in mind. To prepare an incremental backup, run the same command as with full backups, just with the --apply-log-only option, nothing complex here:
  xtrabackup –apply-log-only –prepare –target-dir=backups/
• To prepare a partial backup, specify the --export tag (don’t worry about warnings in this case – they are most likely issued because InnoDB “sees” tables, but their files do not exist in the data directory):
  xtrabackup --prepare --export --target-dir=backups/

And.. you’re done! Well, kind of. Now you also need to restore the backups, right?

Restoring XtraBackups

Now that you’ve made and prepared your backups, there will obviously be a point in time where you would need to restore what you’ve got in store. Again, such a process is a little different for each backup type, but don’t fret – we’re here to help. Here’s how to come around this issue:

• To restore full, compressed, incremental, or encrypted backups, add a --copy-back option to restore your backup to the data directory:
  xtrabackup --copy-back --target-dir=/backups
• To restore partial backups, all you have to do is restore all of the tables in the partial backup (copy them back to the server of your choice.)

That’s it – it’s that simple! Of course, we can run into a couple of issues during these steps as well, so it’s always beneficial to keep an eye out for the documentation.

Options Offered by Percona XtraBackup

As with everything command-line related, Percona XtraBackup has a couple of options associated with itself. Some of them are as follows (all of the options can be found over at the Percona’s documentation):

Percona XtraBackup Option	Meaning
--backup	Takes a backup of the database.
--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 above.)
--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.)

Summary

Percona XtraBackup is the flagship tool in Percona’s backup arsenal – the tool is widely used by junior and senior database administrators alike and as it avoids the locking of databases during its backup procedures, takes backups in a quick and safe fashion, and allows all kinds of backups to be restored quickly, there’s no doubt that Percona XtraBackup will be the option of choice for database engineers for many years to come. We hope that this blog post has provided you with some of the insight into the Percona’s XtraBackup world and that you will refer to the Percona’s manual for more information, and we will see you in the next blog!

The post Backing Up MySQL Part 2: Percona XtraBackup appeared first on Simple Talk.

from Simple Talk https://ift.tt/6bdQxzR
via