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.