Xtrabackup description and installation
Xtrabackup description and installation
Full backup and recovery of Xtrabackup
Full and incremental backup and recovery of Xtrabackup
IV. Partial Completeness, Increment and Recovery of Xtrabackup
Xtrabackup description and installation
1. Introduction and description of Xtrabackup
Percona XtraBackup is the only open source free MySQL hot standby software in the world that can perform non-blocking.
Backup of InnoDB and XtraDB databases. With Percona XtraBackup, you can achieve the following benefits:
Quick and reliable backup
Uninterrupted transaction processing during backup
Save disk space and network bandwidth
Automatic backup verification
Because of faster recovery time, longer uptime
Percona XtraBackup provides hot backups of MySQL for all versions of Percona Server, MySQL and MariDB. It executes
Streaming transport, compression and incremental MySQL backup.
Percona XtraBackup is suitable for MySQL, MariaDB and Percona Server. It supports completely non-blocking backup
InnoDB, XtraDB and HailDB storage engines. In addition, it can simply backup the following storage engines
Pause writing at the end of backup: MyISAM, Merge and Archive, including partition tables, triggers and
Database options.
2. Xtrabackup Download and Installation
2.1. Download address
https://www.percona.com/downloads/XtraBackup/LATEST/
2.3. Installation
yum install percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm
Additional need to install some dependency perl packages
2.4. View installation results
# rpm -ql percona-xtrabackup-24-2.4.7-1.el7.x86_64 /usr/bin/innobackupex /usr/bin/xbcloud /usr/bin/xbcloud_osenv /usr/bin/xbcrypt /usr/bin/xbstream /usr/bin/xtrabackup /usr/share/doc/percona-xtrabackup-24-2.4.7 /usr/share/doc/percona-xtrabackup-24-2.4.7/COPYING /usr/share/man/man1/innobackupex.1.gz /usr/share/man/man1/xbcrypt.1.gz /usr/share/man/man1/xbstream.1.gz /usr/share/man/man1/xtrabackup.1.gz
Full backup of Xtrabackup
1.Xtrabackup ready
1.1. Begin to be ready
Create a backup directory
# mkdir /data/backups
Data view before preparation:
MariaDB [ckldb]> select * from jone; +------+--------+ | id | name | +------+--------+ | 1 | wukaka | | 2 | side | | 3 | ckl | | 4 | kk | | 5 | zld | | 6 | ned | | 7 | stark | +------+--------+ 7 rows in set (0.00 sec)
1.2. Start backup
# innobackupex --user=root /data/backups/ 170430 23:04:14 innobackupex: Starting the backup operation IMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackupex prints "completed OK!". 170430 23:04:14 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/tmp/mysql.sock' as 'root' (using password: NO). 170430 23:04:14 version_check Connected to MySQL server 170430 23:04:14 version_check Executing a version check against the server... 170430 23:04:14 version_check Done. 170430 23:04:14 Connecting to MySQL server host: localhost, user: root, password: not set, port: 3306, socket: /tmp/mysql.sock Using server version 10.1.22-MariaDB innobackupex version 2.4.7 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 6f7a799) xtrabackup: uses posix_fadvise(). xtrabackup: cd to /data/mysql/3306/data xtrabackup: open files limit requested 0, set to 1024 xtrabackup: using the following InnoDB configuration: xtrabackup: innodb_data_home_dir = /data/mysql/3306/data xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend xtrabackup: innodb_log_group_home_dir = /data/mysql/3306/data xtrabackup: innodb_log_files_in_group = 2 xtrabackup: innodb_log_file_size = 67108864 ........ 170430 23:04:24 Executing UNLOCK TABLES 170430 23:04:24 All tables unlocked 170430 23:04:24 Backup created in directory '/data/backups/2017-04-30_23-04-14/' MySQL binlog position: filename 'mysql-bin.000009', position '745', GTID of the last change '0-1-62' 170430 23:04:24 [00] Writing backup-my.cnf 170430 23:04:24 [00] ...done 170430 23:04:24 [00] Writing xtrabackup_info 170430 23:04:24 [00] ...done xtrabackup: Transaction log of lsn (1634473) to (1634473) was copied. 170430 23:04:25 completed OK!
1.3. View backup files
# ll /data/backups/2017-04-30_23-04-14/ total 75800 -rw-r-----. 1 root root 418 Apr 30 23:04 backup-my.cnf drwxr-x---. 2 root root 52 Apr 30 23:04 ckldb -rw-r-----. 1 root root 77594624 Apr 30 23:04 ibdata1 drwxr-x---. 2 root root 4096 Apr 30 23:04 mysql drwxr-x---. 2 root root 20 Apr 30 23:04 performance_schema drwxr-x---. 2 root root 20 Apr 30 23:04 test -rw-r-----. 1 root root 28 Apr 30 23:04 xtrabackup_binlog_info -rw-r-----. 1 root root 113 Apr 30 23:04 xtrabackup_checkpoints -rw-r-----. 1 root root 494 Apr 30 23:04 xtrabackup_info -rw-r-----. 1 root root 2560 Apr 30 23:04 xtrabackup_logfile # cd /data/backups/2017-04-30_23-04-14/ # cat xtrabackup_binlog_info mysql-bin.000009 745 0-1-62 #Binary Log File Log POS Point Timestamp LSN，Log serial number, Innodb The log serial number is a 64-bit integer # cat xtrabackup_checkpoints backup_type = full-backuped #Backup type is complete from_lsn = 0 #Starting position of LSN to_lsn = 1634473 #LSN End Position last_lsn = 1634473 #Latest LSN Location compact = 0 recover_binlog_info = 0
1.4. Simulated deletion of files
# rm -rf /data/mysql/3306/data
2. Prepare for recovery
After creating a backup, the data is not ready to restore. It may revoke uncommitted transactions or replay transactions in the log. Doing these wait operations will keep the data files consistent, which is the purpose of the preparatory phase. Once completed, the data can be used. To prepare a backup using innobackupex, you must use -- apply-log and the path of the backup directory as parameters:
2.1. Stop the database
# service mysqld stop
2.2. Resume preparation
# innobackupex --apply-log /data/backups/2017-04-30_23-04-14/ 170430 23:18:35 innobackupex: Starting the apply-log operation IMPORTANT: Please check that the apply-log run completes successfully. At the end of a successful apply-log run innobackupex prints "completed OK!". ....... InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 1634856 170430 23:18:46 completed OK!
2.3. Start to recover
# innobackupex --copy-back /data/backups/2017-04-30_23-04-14/ 170430 23:20:31 innobackupex: Starting the copy-back operation IMPORTANT: Please check that the copy-back run completes successfully. At the end of a successful copy-back run innobackupex prints "completed OK!". ...... 170430 23:20:42 [01] Copying ./ibtmp1 to /data/mysql/3306/data/ibtmp1 170430 23:20:42 [01] ...done 170430 23:20:43 completed OK!
2.4. View restored files
# ll total 219148 drwxr-x---. 2 root root 52 Apr 30 23:20 ckldb -rw-r-----. 1 root root 77594624 Apr 30 23:20 ibdata1 -rw-r-----. 1 root root 67108864 Apr 30 23:20 ib_logfile0 -rw-r-----. 1 root root 67108864 Apr 30 23:20 ib_logfile1 -rw-r-----. 1 root root 12582912 Apr 30 23:20 ibtmp1 drwxr-x---. 2 root root 4096 Apr 30 23:20 mysql drwxr-x---. 2 root root 20 Apr 30 23:20 performance_schema drwxr-x---. 2 root root 20 Apr 30 23:20 test -rw-r-----. 1 root root 23 Apr 30 23:20 xtrabackup_binlog_pos_innodb -rw-r-----. 1 root root 494 Apr 30 23:20 xtrabackup_info
Add permissions to start the database:
# chown -R mysql.mysql data/ # service mysqld start
View the recovery results:
MariaDB [(none)]> use ckldb Database changed MariaDB [ckldb]> show tables; +-----------------+ | Tables_in_ckldb | +-----------------+ | jone | +-----------------+ 1 row in set (0.00 sec) MariaDB [ckldb]> select * from jone; +------+--------+ | id | name | +------+--------+ | 1 | wukaka | | 2 | side | | 3 | ckl | | 4 | kk | | 5 | zld | | 6 | ned | | 7 | stark | +------+--------+ 7 rows in set (0.01 sec)
Full and incremental backup and recovery of Xtrabackup
1. Full-fledged implementation, the above has been backed up, this time do not need
2. Incremental backup
2.1. Add the first data
MariaDB [ckldb]> insert into jone values(8,'lanester'),(9,'gonku'); Query OK, 2 rows affected (0.39 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [ckldb]> select * from jone; +------+----------+ | id | name | +------+----------+ | 1 | wukaka | | 2 | side | | 3 | ckl | | 4 | kk | | 5 | zld | | 6 | ned | | 7 | stark | | 8 | lanester | | 9 | gonku | +------+----------+ 9 rows in set (0.00 sec)
2.2. Perform the first incremental backup
# innobackupex --incremental /data/backups/ --incremental-basedir=/data/backups/2017-04-30_23-04-14/ 170430 23:37:49 innobackupex: Starting the backup operation IMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackupex prints "completed OK!". ....... xtrabackup: Transaction log of lsn (1636718) to (1636718) was copied. 170430 23:37:56 completed OK!
View the backup results:
# ls /data/backups/ 2017-04-30_23-04-14 2017-04-30_23-37-49 # cd /data/backups/2017-04-30_23-37-49 backup_type = incremental from_lsn = 1634473 to_lsn = 1636718 last_lsn = 1636718 compact = 0 recover_binlog_info = 0
2.3. Add the second data
MariaDB [ckldb]> insert into jone values(10,'dragon'),(11,'lanli'); Query OK, 2 rows affected (0.61 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [ckldb]> select * from jone; +------+----------+ | id | name | +------+----------+ | 1 | wukaka | | 2 | side | | 3 | ckl | | 4 | kk | | 5 | zld | | 6 | ned | | 7 | stark | | 8 | lanester | | 9 | gonku | | 10 | dragon | | 11 | lanli | +------+----------+ 11 rows in set (0.00 sec)
2.4. Perform a second incremental backup
# innobackupex --incremental /data/backups/ --incremental-basedir=/data/backups/2017-04-30_23-37-49/ 170430 23:41:46 innobackupex: Starting the backup operation IMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackupex prints "completed OK!". ...... xtrabackup: Transaction log of lsn (1638894) to (1638894) was copied. 170430 23:41:54 completed OK!
View the backup results:
# du -sh /data/backups/* 224M /data/backups/2017-04-30_23-04-14 1.3M /data/backups/2017-04-30_23-37-49 1.1M /data/backups/2017-04-30_23-41-46 # cd /data/backups/2017-04-30_23-41-46/ # cat xtrabackup_checkpoints backup_type = incremental from_lsn = 1636718 to_lsn = 1638894 last_lsn = 1638894 compact = 0 recover_binlog_info = 0
3. Simulated deletion of database
# rm -rf /data/mysql/3306/data/
4. Incremental Backup Recovery
Preparing incremental backups with innobackupex is a bit different
Complete. This may be a stage requiring more attention:
First, transactions that must be submitted on each backup. This combines cardinal backup and increment.
Then, uncommitted transactions must be rolled back before ready-to-use backups can be made.
If you replay committed transactions and roll back uncommitted transactions on a basic backup, you will not be able to do so
Add increments. If you do this incrementally, you cannot add data from that time
And the remaining increments.
4.1. Readiness for full recovery
# innobackupex --apply-log --redo-only /data/backups/2017-04-30_23-04-14/
4.1. First Incremental Backup Recovery Preparedness
# innobackupex --apply-log --redo-only /data/backups/2017-04-30_23-04-14/ --incremental-dir=/data/backups/2017-04-30_23-37-49
4.2. Second Incremental Backup Recovery Preparedness
# innobackupex --apply-log --redo-only /data/backups/2017-04-30_23-04-14/ --incremental-dir=/data/backups/2017-04-30_23-41-46/
5. Start to recover
# innobackupex --copy-back /data/backups/2017-04-30_23-04-14/
6. View recovery data and files
# chown -R mysql.mysql /data/mysql/3306/data/ # service mysqld start
View data
MariaDB [(none)]> use ckldb Database changed MariaDB [ckldb]> select * from jone; +------+----------+ | id | name | +------+----------+ | 1 | wukaka | | 2 | side | | 3 | ckl | | 4 | kk | | 5 | zld | | 6 | ned | | 7 | stark | | 8 | lanester | | 9 | gonku | | 10 | dragon | | 11 | lanli | +------+----------+ 11 rows in set (0.00 sec)
Both incremental backups have been restored
IV. Partial Completeness, Increment and Recovery of Xtrabackup
Percona XtraBackup provides partial backup, which means that you can only backup specific tables or
Database. The tables you backed up must be in a separate table space because they are created or changed after you
The innodb_file_per_table option is enabled on the server.
There is only one warning about partial backups: do not copy backups of backups. Restore partial backup
It should be done by importing tables instead of using the traditional - copy-back option. Although there are some
Scenarios where recovery can be done by copying files, which may lead to database inconsistencies
Case studies are not recommended.
There are three ways to create partial backups to specify which part of the entire data to backup: conventional
Expressions (- include), enumeration of tables in files (- tables - files) or provision of database lists (- databases).
The regular expression provided to this with the -- include option will match the fully qualified
Table name, including database name, in databasename.tablename format.
1. Single repository available
# innobackupex --include='ckldb' /data/backups/ 170501 00:07:31 innobackupex: Starting the backup operation IMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackupex prints "completed OK!". ....... 170501 00:07:35 Backup created in directory '/data/backups/2017-05-01_00-07-31/' MySQL binlog position: filename 'mysql-bin.000001', position '313', GTID of the last change '' 170501 00:07:35 [00] Writing backup-my.cnf 170501 00:07:35 [00] ...done 170501 00:07:35 [00] Writing xtrabackup_info 170501 00:07:35 [00] ...done xtrabackup: Transaction log of lsn (1634875) to (1634875) was copied. 170501 00:07:35 completed OK!
View the backup file:
# cd /data/backups/2017-05-01_00-07-31/ [root@localhost 2017-05-01_00-07-31]# ls backup-my.cnf ckldb ibdata1 xtrabackup_binlog_info xtrabackup_checkpoints xtrabackup_info xtrabackup_logfile
2. Incremental backup of a single library
#innobackupex --include='ckldb' --incremental /data/backups/ --incremental-basedir=2017-05-01_00-07-31
3. Increase data, second incremental backup
3.1. Increase data
MariaDB [ckldb]> insert into jone values(12,'firegod'),(13,'sevenGod'); Query OK, 2 rows affected (0.38 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [ckldb]> select * from jone; +------+----------+ | id | name | +------+----------+ | 1 | wukaka | | 2 | side | | 3 | ckl | | 4 | kk | | 5 | zld | | 6 | ned | | 7 | stark | | 8 | lanester | | 9 | gonku | | 10 | dragon | | 11 | lanli | | 12 | firegod | | 13 | sevenGod | +------+----------+ 13 rows in set (0.00 sec)
3.2. Start the second incremental backup:
# innobackupex --include='ckldb' --incremental /data/backups/ --incremental-basedir=/data/backups/2017-05-01_00-10-00/
4. Simulated deletion of database
MariaDB [(none)]> drop database ckldb; Query OK, 1 row affected (0.19 sec) MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec)
5. Prepare for recovery
5.1. Prepare for recovery
# innobackupex --apply-log --export /data/backups/2017-05-01_00-07-31/
5.2. Prepare the first incremental backup
# innobackupex --apply-log --redo-only /data/backups/2017-05-01_00-07-31/ --incremental-dir=/data/backups/2017-05-01_00-10-00/
5.3. Prepare a second incremental backup
# innobackupex --apply-log --redo-only /data/backups/2017-05-01_00-07-31/ --incremental-dir=/data/backups/2017-05-01_00-13-35/
6. Start to recover
# \cp -rf /data/backups/2017-05-01_00-07-31/* /data/mysql/3306/data/
Permission addition:
# chown -R mysql.mysql /data/mysql/3306/data/ # service mysqld start
View data:
MariaDB [(none)]> use ckldb Database changed MariaDB [ckldb]> select * from jone; +------+----------+ | id | name | +------+----------+ | 1 | wukaka | | 2 | side | | 3 | ckl | | 4 | kk | | 5 | zld | | 6 | ned | | 7 | stark | | 8 | lanester | | 9 | gonku | | 10 | dragon | | 11 | lanli | | 12 | firegod | | 13 | sevenGod | +------+----------+ 13 rows in set (0.06 sec)