Use of the Xtrabackup tool for backup and recovery of MySQL database

I. Why a backup is required

In a real-world production environment, it is a mistake to delete the operation, the hardware damage will result in the data is not available, this time we need to backup to achieve disaster recovery. Hardware-level redundancy is also required, such as RAID, and it is not a substitute for backup operations, since data is not deleted after it has been mistakenly erased, so backup is essential.

Two. Classification of backups 1. Physical and logical backups

Physical Backup: It is a direct copy of MySQL data files stored on disk. This kind of backup uses a large amount of space, especially the table space used by InnoDB to store data, because the tablespace allocation size grows at a certain scale, so there is no space to use. However, recovery takes less time.
logical Backup: export the data as an SQL statement. It takes more time to export data and recover data, but it takes up little space.

2. Hot spares, Win Bei, cold

Hot spare : No locks are seen on the online environment and do not affect any database operations on the line.
Win Bei: to acquire a lock between backups and to apply a lock. Some actions are affected during the lock.
Cold: Stop the server before backing up and then back up.

Three. Xtrabackup

Xtrabackup is an open source hot standby tool under Percona that backs up MySQL data without locking the database. It is able to back up the Innodb,xtradb,myisam table and the MySQL version supports 5.1,5.5,5.5,5.7.
Xtrabackup has the following functions:

能够对InnoDB实现热备，无需暂停数据库能够对MySQL进行增量备份对MySQL备份能够实现流式压缩并传输给其他服务器MySQL服务运行时能够在MySQL 服务器之间进行表的迁移能够很容易创建一个MySQL从服务器备份MySQL时不会增加服务器负担

Xtrabackup can implement hot spares with tables created by the InnoDB engine, Win Bei the MyISAM engine.

Four. Xtrabackup how to implement Backup

Xtrabackup backup data is to back up the InnoDB data, then back up MyISAM data, and finally record the backup information.

1.XtraBackup How to implement backup for InnoDB

Before explaining the principle, we need to introduce two important features of the InnoDB engine, one transactional feature, and one is the table space used to store the data.

1) Business

In the InnoDB engine, once the user's operation involves modifying the MySQL data, he does not write directly to the disk, it generates a transaction, and the transaction is recorded in the transaction log, and the log is the Round-robin method used, the transaction log space size must be, It is said that the following transaction log will overwrite the previous. The data is persisted to disk only after the user commits the transaction.

2) Table Space

InnoDB storage data using the table space, in this space, InnoDB self-organizing the data, all the data on the page, each page size fixed by default is 16KB, you can make it by innodb_page_size a major small. In addition to storing database data in a page, the table's metadata is stored to describe this page. One of the meta-data is the log sequence number, which is the basis for incremental backups.

3) Principle of Backup

Xtrabackup logs the log sequence number (LSN) at the beginning, and then begins copying the data files on the disk. In the meantime, if the data changes, then the data is in a different position. The data changes, we don't have to copy the changed data, we can record the changed transaction log during this period, because the transaction log also records the changes in the data. So during the copy of the data, Xtrabackup initiates a background process to monitor the transaction log and copies the transaction log written during this time. This process is an ongoing process because the transaction log overwrites the previous one.

4) Incremental Backup implementation principle

The implementation of the incremental backup mentioned earlier relies on the LSN, which is the property that a page has. So how do you use him for incremental backups when you're backing up? First, we make a full backup of the data, with a full backup with four pages and a page number of 1,2,3,4. If the page data for LSN 1 is changed after a full backup, the number of his LSN is at the maximum LSN number plus 1, which means that his number will change to 5 instead of 2. Similarly, if the LSN is 3, his LSN number will change to 6. For incremental backups we only need to back up the page with the LSN number 5,6. As shown in the following:

Incremental backups are implemented under physical backups because the MyISAM engine stores data without an LSN.

2.XtraBackup Backup MyISAM Table

Xtrabackup is after the InnoDB table is backed up, the lock TABLES for backup copy MyISAM table and. frm file are run. This lock is added after the InnoDB data copy is a backup lock which is more lightweight than the flush TABLES with READ lock. And the InnoDB DML operation is not affected during the lock-up, which is why the Xtrabackup backup InnoDB is hot standby, and the backup MyISAM is Win Bei.
Copying MyISAM does not affect the operation of INNODB data, which means that innodb data is changing. This causes MyISAM and InnoDB data to be inconsistent at some point in time. In order for the data to be consistent, transaction logs and binary logs are required after the copy is completed. And this process he's going to apply the lock BINLOG for backup lock, and the copy is done before the binary log and table are unlocked.

3. Backup-Created file interpretation

• BACKUP-MY.CNF: It is not the original my.cnf, but rather the xtrabackup of the InnoDB engine that was acquired during the backup. When it is pre-restored, it reads the contents of this file or reads it from the file indicated by Xtrabackup--defaults-file.
• Xtrabackup_checkpoints: Describes the type of backup (full or incremental), its state (such as prepared), and his LSN range. See the example below
  Full backup

  backup_type = full-backupedfrom_lsn = 0to_lsn = 15188961605last_lsn = 15188961605

  Incremental backup

  backup_type = incrementalfrom_lsn = 15188961605to_lsn = 15189350111last_lsn = 15189350111

• Xtrabackup_binlog_info: The location of the server binaries at the moment of backup, obtained through show MASTER status
• The binary location where the Xtrabackup_binlog_pos_innodb:innodb table is currently located. Related to InnoDB transactions
• Xtrabackup_logfile: Transaction log copied during backup for pre-restore

Five. Experiments

This experiment will demonstrate backup and restore operations, using the Innobackup command.
Operating system for CentOS 7.2
MySQL version is 5.5

1. Installing Percona-xtrabackup

~]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/7/x86_64/ Percona-xtrabackup-2.4.4-rdf58cf2-el7-x86_64-bundle.tar
Expand Tarball which contains three rpm,
PERCONA-XTRABACKUP-24: Includes the latest Percona xtrabackup GA binaries and related files
Percona-xtrabackup-24-debuginfo: For debugging binaries in percona-xtrabackup-24
Percona-xtrabackup-test-24:percona Xtrabackup Test Kit
~]# Tar XF Percona-xtrabackup-2.4.4-rdf58cf2-el7-x86_64-bundle.tar
~]# Yum install-y percona-xtrabackup-24-debuginfo-2.4.4-1.el7.x86_64.rpm percona-xtrabackup-24-2.4.4-1.el7.x86_64. RPM percona-xtrabackup-test-24-2.4.4-1.el7.x86_64.rpm

2. Make a full backup of the data and restore 1) Create a full backup

~]# Innobackupex--defaults-file=/etc/my.cnf--user=root--host=127.0.0.1--password=123456/backups/full
If the full backup succeeds, the final output message will appear completed ok!

• Parameter interpretation

  --defaults-file：读取mysql默认的参数，这个要是命令行的第一个参数，不能是符号链接文件。--user --host --password 登录主机，用户名和密码/backups/full：备份目录，如果没有会自动创建

• generate file

    [[email protected] ~]# ls-l/backups/full/total 0drwxr-x---. 7 root Root 227 Mar 14:20 2018-03-16_14-20-35[[email protected] ~]# ls-l/backups/full/2018-03-16_14-20-35/total 18460 -rw-r-----. 1 root root 417 Mar 14:20 backup-my.cnfdrwxr-x---. 2 root root 272 Mar 14:20 hellodb-rw-r-----. 1 root root 18874368 Mar 14:20 ibdata1drwxr-x---. 2 root root 4096 Mar 14:20 mysqldrwxr-x---. 2 root root 4096 Mar 14:20 performance_schemadrwxr-x---. 2 root root 14:20 ptdrwxr-x---. 2 root root Mar 14:20 test-rw-r-----. 1 root 14:20 xtrabackup_binlog_info-rw-r-----. 1 root root 113 Mar 14:20 xtrabackup_checkpoints-rw-r-----. 1 root root 522 Mar 14:20 xtrabackup_info-rw-r-----. 1 root 2560 Mar 14:20 xtrabackup_logfile  

  Innobackupex creates a directory with the current time in the directory where the data is specified, and all the generated backup files will be this time directory Under

• __ View xtrabackup_checkpoints File __

  backup_type = full-backupedfrom_lsn = 0to_lsn = 1843549last_lsn = 1843549compact = 0recover_binlog_info = 0

  This is a full backup that can be seen through Backup_type, with LSN from 0 to 1843549.

2) Full backup restore

This backup cannot be used for recovery because the data file is in an inconsistent state when the data in the backup may contain transactions that have not yet been committed or have been committed but have not been synchronized to the data file. Therefore, we are now going to keep the data file in a consistent state by rolling back uncommitted transactions and synchronizing the committed transactions to the data file.

• Pre-restore
  ~]# Innobackupex--apply-log--redo-only/backups/full/2018-03-16_14-20-35/
  If the pre-restore succeeds, the final output message will appear completed ok!
  Parameter description

  --apply-log：应用在备份期间产生的事务日志。--redo-only：表明应用日志的方式，将提交的日志重新提交，没有提交的事务不要回滚，因为他很有可能在下个备份中提交了。

• Restores
  When restoring, the MySQL server needs to be shut down and the data directory needs to be empty.
  ~]# Systemctl Stop mariadb
  ~]# mv/var/lib/mysql/tmp
  ~]# Mkdir/var/lib/mysql
  ~]# Innobackupex--defaults-file=/etc/my.cnf--copy-back/backups/full/2018-03-16_14-20-35/
  ~]# Chown-r Mysql.mysql/var/lib/mysql
  ~]# systemctl Start mariadb

2. Full backup + incremental backup and restore

• Backup
  Full backup
  ~]# innobackupex--defaults-file=/etc/my.cnf--user=root--host=127.0.0.1-- Password=123456/backups/full
  First Incremental backup
  ~]# Innobackupex--defaults-file=/etc/my.cnf--user=root--host= 127.0.0.1--password=123456--incremental/backups/incr-1--incremental-basedir=/backups/full/2018-03-16_14-20-35/
  Secondary Incremental backup
  ~]# innobackupex--defaults-file=/etc/my.cnf--user=root--host=127.0.0.1--password=123456/backups/ Incr-2/backups/incr-2--incremental-basedir=/backups/incr-1/2018-03-16_15-20-32/
  parameter Description

  The

   --incremental indicates that this backup is an incremental backup. --incremental-basedir indicates who is the basis for an incremental backup. /backups/incr-1 indicates the directory to which the backup is placed  

• Pre-restore
  Full backup pre-restore
  ~]# Innobackupex--apply-log--redo-only/backups/full/2018-03-16_14-20-35/
  First incremental backup pre-restore
  ~]# Innobackupex--apply-log--redo-only/backups/incr-1/2018-03-16_15-20-32/--incremental-dir=/backups/full/ 2018-03-16_14-20-35/
  Second incremental backup and restore
  ~]# Innobackupex--apply-log--redo-only/backups/incr-1/2018-03-16_16-01-44/--incremental-dir=/backups/full/ 2018-03-16_14-20-35/

  --incremental-dir：指明以哪个备份作为还原处理。

• Restores
  Reference Backup Restore

Six. Backup scripts

#!/bin/bash# Description:innobackupex backup mysql# date:2018-01-23# version:0.1.0# author:lirou# This backup is scheduled to be one cycle a week, Monday is a full backup, with Monday-based incremental backups from Tuesday to Sunday. #以一周还原的时候只需要周一的完全备份和周日的增量备份即可. Backup_dir_parent=/var/lib/mysql/backupsbackup_dir=full_backup_dir=increment_backup_dir=backup_times=backup_ max_times=5# Login Server parameter mysql_host=127.0.0.1mysql_user=rootmysql_password=123456#mysql service profile defaults_file=/etc/ my.cnf# record Backup is output information backup_log=/var/log/innobackup/innobackup.log# record each backup is successful backup_status=/var/log/innobackup/ Innobackup.status! [-D $backup _dir_parent] && MKDIR-PV $backup _dir_parent! [-D $backup _log] && MKDIR-PV $backup _logweek=$ (date "+%u") if [$week-eq 1]; Then backup_times=$ (ls-l $backup _dir_parent | grep-i ' ^d.*backup.* ' | wc-l) if [$backup _times-ge $backup _max _times];then rm-rf ${backup_dir_parent}/$ (ls-lt $backup _dir_parent |tail-l) fi backup_dir=${backup_dir_pa rent}/backup-$ (date "+%y-%m-%d") Full_backup_dir=${backup_dir}/full Innobackupex--defaults-file=${defaults_file}--host=${mysql_host}--user=${mysql_user}--password=${mysql_password} ${full_ Backup_dir} &> $backup _logelse backup_dir=${backup_dir_parent}/$ (ls-lt $backup _dir_parent | grep-i ' ^d.*backu P.* ' | head-1 | Grep-o ' backup.* ') Increment_backup_dir=${backup_dir}/incr-${week} Innobackupex--defaults-file=${defaults_file}-- Host=${mysql_host}--user=${mysql_user}--password=${mysql_password}--incremental=${backup_dir}/full ${increment_     Backup_dir} &> $backup _log Fiecho "$ (date ' +%y-%m-%d '): $?" >> $backup _status

This backup script makes sure that a full backup of Monday is performed when the scheduled task is made, because the increments from Tuesday to Sunday are based on him.

Reference

Https://www.percona.com/doc/percona-xtrabackup/2.4/how_xtrabackup_works.html
Https://www.percona.com/doc/percona-xtrabackup/LATEST/innobackupex/innobackupex_option_reference.html
Https://dev.mysql.com/doc/refman/5.7/en/innodb-file-space.html
Https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_page
Https://www.percona.com/doc/percona-xtrabackup/2.4/xtrabackup-files.html#xtrabackup-files

Use of the Xtrabackup tool for backup and recovery of MySQL database