Xtrabackup for MySQL Backup

1. Introduction

Xtrabackup is the MySQL database backup tool for Percona. An open source tool for hot provisioning of InnoDB and XTRADB.

1> its backup process is fast and reliable

2> can save disk and traffic based on compression function

3> The backup process does not disrupt the transaction being performed

4> restore speed, automatically implement backup inspection

2, the implementation of backup

1> Full Backup

Innobackupex--user=dbuser--password=dbuserpassword/path/to/backup_dir

When you use a Innobakupex backup, it calls Xtrabackup back up all InnoDB tables, copies all related files (. frm) about the table structure definition, and related files for the MyISAM, MERGE, CSV, and archive tables. Files related to triggers and database configuration information are also backed up. These files are saved to a directory in the time command.

At the same time as the backup, Innobackupex also creates the following files in the backup directory:

• (1) xtrabackup_checkpoints--backup type [such as full or incremental], backup status [if it is already prepared status] and lsn[log serial number] range information; each InnoDB page (usually 16k size) Will contain a log sequence number----that is LSN. LSN is the system version number of the entire database system, and each page-related LSN can indicate how the page has changed recently.

• (2) Xtrabackup_binlog_info--mysql the binary log file currently in use by the server and the location of the binary log event up to the moment the backup was made.

• (3) xtrabackup_binlog_pos_innodb--binary log files and the current position for binary log files for InnoDB or xtradb tables.

• (4) Xtrabackup executable files used in xtrabackup_binary--backup;

• (5) backup-my.cnf--configuration option information used for backup command;

  
  

When using Innobackupex for backup, you can also use the--NO-TIMESTAMP option to prevent commands from automatically creating a time-named directory, so that the Innobackupex command creates a Backup-dir directory to store the backup data.

If you want to back up with a user with minimal permissions, you can create such a user based on the following command:

mysql> CREATE USER ' bkuser ' @ ' localhost ' identified by ' secret ';

Mysql> REVOKE all privileges, GRANT OPTION from ' Bkuser ';

Mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT on *. * to ' bkuser ' @ ' localhost ';

mysql> FLUSH privileges;

2> Preparation (prepare) a full backup

In general, data cannot be used for recovery operations after the backup is complete, because the data that is backed up may contain transactions that have not yet been committed or that have been committed but have not been synchronized to the data file. Therefore, the data file still handles the inconsistent state at this time. The primary role of Prep is to keep the data file in a consistent state by rolling back uncommitted transactions and synchronizing committed transactions to data files.

The--apply-log option of the Innobakupex command can be used to implement the above functions. -----not finished

Xtrabackup for MySQL Backup