brief introduction

Xtrabackup is a data backup tool for InnoDB. It supports online hot backup (backup does not affect data reading and writing). It is a good substitute for InnoDB Hotbackup, a commercial backup tool. It can backup the databases of InnoDB and XtraDB storage engines non-blocking (for MyISAM backups, table locks are also required)

Characteristic

1) The backup process is fast and reliable.

2) The backup process will not interrupt the ongoing transactions;

3) It can save disk space and traffic based on compression function.

4) Automatically implement backup inspection;

5) The reduction speed is fast.

install

Installation under ubuntu

sudo apt-get install percona-xtrabackup

Full backup

backups

innobackupex --user=DBUSER --password=DBUSERPASS  /path/to/BACKUP-DIR/

--user: You need to create a user with the least privileges
mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'xtrauser'@'localhost' IDENTIFIED BY 'xtrauser' ;
mysql> FLUSH PRIVILEGES ;

/path/to/BACKUP_DIR

When using innobakupex to backup some metadata containing xtrabackup, it will call xtrabackup to backup all InnoDB tables, copy all files related to table structure definition (.frm), MyISAM, MERGE, CSV and ARCHIVE tables, and backup files related to triggers and database configuration information. These files are saved to a directory commanded by time.

innobackupex --user=xtrauser --password=xtrauser --defaults-file=/etc/my.cnf /data/backup

While backing up, innobackupex also creates the following files in the backup directory:

1.xtrabackup_checkpoints: backup type (such as full or incremental), backup status (such as whether it is prepared) and LSN(Log Serial Number log serial number) range information;

 # cat xtrabackup_checkpoints     
  backup_type = full-backuped
  from_lsn = 0
  to_lsn = 1752057
  last_lsn = 1752057
  compact = 0
  recover_binlog_info = 0

In mysql, blocks of data stored will have sequential IDs, and if a piece of data is modified, a new ID will be given. According to these IDs, the old and new Chengdu can be marked. xtrabackup uses these IDs for backup and incremental backup

Each InnoDB page (usually 16 K in size) contains a log sequence number, LSN. LSN is the system version number of the whole database system. The LSN associated with each page can show how the page has changed recently.

2. xtrabackup_binlog_info: The binary log file currently being used by the MySQL server and the location of the binary log event until the moment of backup.

reduction
Assuming an error occurs in the database, the data directory is deleted

//Delete directories (according to your environment)
sudo rm -rf /data/mysql/var
sudo rm -rf /data/mysql/data
//Create directories (according to your environment)
sudo mkdir /data/mysql/var
sudo mkdir /data/mysql/data
//Modify the owner of the directory (according to his environment)
sudo chown -R mysql:mysql /data/mysql/var
sudo chown -R mysql:mysql /data/mysql/data
//Restore data
innobackupex --defaults-file=/etc/my.cnf --copy-back=/data/backup/2017-02-22_16-36-35

Incremental backup

backups
Incremental backup based on full backup.

Make a full backup first

innobackupex --user=xtrauser --password=xtrauser --defaults-file=/etc/my.cnf /data/backup

Make some changes in the middle...

First incremental backup

innobackupex --user=xtrauser --password=xtrauser --defaults-file=/etc/my.cnf --incremental /data/increment --incremental-basedir=/data/backup/2017-02-22_17-46-35

Make some changes in the middle...

Second incremental backup

innobackupex --user=xtrauser --password=xtrauser --defaults-file=/etc/my.cnf --incremental /data/increment --incremental-basedir=/data/increment/2017-02-22_17-48-01

Get ready
Prepare incremental backups are different from complete backups, especially:

1) Submitted transactions need to be "replayed" on each backup (including full and incremental backups). After playback, all backup data will be merged into a full backup.

2) Roll back uncommitted transactions based on all backups. Thus, the operation becomes: cannot roll back, because it is possible that the first backup did not commit and has been successfully submitted in the increment.

innobackupex --apply-log --redo-only /data/backup/2017-02-22_17-46-35

innobackupex --apply-log --redo-only /data/backup/2017-02-22_17-46-35 --incremental-dir=/data/increment/2017-02-22_17-48-01

innobackupex --apply-log --redo-only /data/backup/2017-02-22_17-46-35 --incremental-dir=/data/increment/2017-02-22_17-49-11

recovery

It is consistent with the full backup recovery process.