> CommCell Console > Backup and Restore Agents > Databases > MySQL > MySQL Backup using Percona XtraBackup

Running a Backup Operation using XtraBackup Utility

Using XtraBackup utility, you can run the following MySQL backup operations:

Backing up databases using XtraBackup is similar to a regular MySQL backup operation.

Note

  • By default, XtraBackup command outputs are not displayed in the Commvault logs. To display XtraBackup command outputs, create the additional setting ndebugXtraBackup, and set its value as 1. For information about creating an additional setting, see Adding or Modifying Additional Settings from the CommCell Console.

  • Backup operation using Xtrabackup utility uses four parallel streams by default. To change the number of streams, create the additional setting nXtraBackupParallelThreads, and set its value as the number of streams that you want to run the backup operation. For information about creating an additional setting, see Adding or Modifying Additional Settings from the CommCell Console.

  • To add additional parameters to XtraBackup backup command during a backup operation, create the additional setting sAdvancedXtraBackupOptions, and pass the required parameters as its value. For information about creating an additional setting, see Adding or Modifying Additional Settings from the CommCell Console.

  • You can back up a database using Xtrabackup utility if all the tables within the database are InnoDB storage engine. When a database is backed up using the XtraBackup utility, we use the mysqldump command with --no-data, --routines, and --triggers parameters for that database to backup the schema information, routines, and triggers. These parameters are needed while restoring the databases.

  • You cannot back up a database using Xtrabackup utility if the database contains at least one table with non-InnoDB storage engine. You can either use the Xtrabackup utility to run a full instance backup or use the MySQL dump utility to back up such databases.

XtraBackup Command Examples:

Full backup using XtraBackup:

xtrabackup --user= --defaults-file= --backup --datadir= --socket=/var/lib/mysql/mysql.sock –extra-lsndir=  --stream=xbstream
Metadata generated
binlog_pos = filename 'log.001385', position '1020569'
innodb_from_lsn = 0
innodb_to_lsn = 134480565956
partial = N
incremental = N

Incremental backup using XtraBackup:

xtrabackup --user= --defaults-file= --backup --datadir=/u01/data/mysql --socket=/var/lib/mysql/mysql.sock --incremental-lsn=134482319345 --stream=xbstream
Metadata generated
binlog_pos = filename 'log.001387', position '1022854'
innodb_from_lsn = 134482319345
innodb_to_lsn = 134484086515
partial = N
incremental = Y

Restore using XtraBackup:

  1. Stop the MySQL server and empty data directory of the destination instance.
  2. Restore the full backup to data dir and run XtraBackup prepare command.
xtrabackup --prepare --apply-log-only --target-dir=
  1. After the XtraBackup prepare is completed for the full restore, we need to restore the incremental backups to different directories. Ex: /tmp/inc1, /tmp/inc2 etc.
  2. Once the incremental backups are restored, they will also need to be prepared and applied.
xtrabackup --prepare --apply-log-only --target-dir=/u01/data/mysql --incremental-dir=/tmp/inc1
  1. For the final incremental backup you will omit the --apply-log-only switch to ensure that the MySQL Server is brought online. 
xtrabackup --prepare --target-dir=/u01/data/mysql --incremental-dir=/tmp/inc2
×

Loading...