Xtrabackup Backing up large databases (full backup and incremental backup)

Xtrabackup

Xtrabackup is a free database hot backup software Percona open source, which can back up non-blocking database of InnoDB database and XTRADB storage engine (also need to add a table lock for MyISAM backup);

The mysqldump backup method is a logical backup, the biggest drawback is the slow backup and recovery, if the database is larger than 50g,mysqldump backup is not suitable.

Xtrabackup Advantages

1) Fast backup speed, reliable physical backup

2) The backup process does not interrupt the executing transaction (no lock table required)

3) Ability to save disk space and traffic based on functions such as compression

4) Automatic backup check

5) Fast restore speed

6) can be circulated to transfer the backup to another machine

Xtrabackup principle

Xtrabackup after the installation is complete, there are 4 executables, 2 of which are more important backup tools are Innobackupex, xtrabackup

1) Xtrabackup is specifically used to back up the InnoDB table, cannot back up non-innodb tables, and MySQL server does not interact;

2) Innobackupex is a script that backs up non-InnoDB tables, calls Xtrabackup commands to back up the InnoDB table, and interacts with MySQL server, such as lock table, get location point, and so on. To put it simply, a layer of encapsulation is done on the basis of xtrabackup;

3) Xbcrypt encryption and decryption Backup tool

4) Xbstream circulated package Transfer tool, similar to tar

650) this.width=650; "Src=" Http://www.lichengbing.cn/ueditor/php/upload/image/20160722/1469162312164139.png " Title= "1469162312164139.png" alt= "Xtrabackup.png"/>

1) A redo log file is maintained inside the InnoDB engine table, and we can also call it a transaction log file. Transaction log stores record modifications for each INNODB table data

2) Xtrabackup remembers log sequence number (LSN) log sequence numbers at startup, which is the location of the current redo record, and copies all data files

3) The copy process takes some time, so during this period if the data file LSN is changed, it runs a background process that monitors the transaction log and keeps a record of the changes to every data file in the transaction log

Xtrabackup Installation

Official website Download Xtrabackup

# #https://www.percona.com/downloads/xtrabackup/percona-xtrabackup-2.3.5/binary/redhat/6/x86_64/ percona-xtrabackup-2.3.5-1.el6.x86_64.rpm

Search download several dependent RPM packages

# #http://rpmfind.net/linux/rpm/perl-dbi-1.609-4.el6.x86_64.rpmperl-dbd-mysql-4.013-3.el6.x86_64. rpmlibev-4.04-2.el6.x86_64.rpm

Check RPM package Installation after installation is complete

[[Email protected] tools]# rpm -ivh percona-xtrabackup-2.3.5-1.el6.x86_64.rpmwarning:  percona-xtrabackup-2.3.5-1.el6.x86_64.rpm: header v4 dsa/sha1 signature, key  ID cd2efd2a: NOKEYPreparing...                 ########################################### [100%]   1 :p ercona-xtrabackup     ########################################### [100%][[ Email protected] tools]# rpm -qa|grep xtrabackuppercona-xtrabackup-2.3.5-1.el6.x86_64 [[email protected] tools]# rpm -ql percona-xtrabackup-2.3.5-1.el6.x86_64/usr/bin/ innobackupex  #innobackupex脚本工具/USR/BIN/XBCLOUD/USR/BIN/XBCLOUD_OSENV/USR/BIN/XBCRYPT/USR/BIN/XBSTREAM/USR /bin/xtrabackup  #最主要的备份工具/usr/share/doc/percona-xtrabackup-2.3.5/usr/share/doc/percona-xtrabackup-2.3.5/ 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

Make a full-scale backup of the database first

[Email protected] data]# Innobackupex--defaults-file=/data/3306/my.cnf--user=root--password=li123456/backup/full /... 60710 08:24:47 [xx] Writing backup-my.cnf160710 08:24:47 [xx] ... done160710 08:24:47 [xx] Writing xtrabackup_info16 0710 08:24:47 [xx] ... donextrabackup:transaction log of LSN (1672995) to (1672995) was copied.160710 08:24:47 Comp Leted ok!

At this point, you can see that/backup/full already has a data file backed up

[[email protected] full]# cd /backup/full/2016-07-10_08-24-43/[[email protected]  2016-07-10_08-24-43]# ll Total dosage  131108-rw-r----- 1 root root        387 7 Month   10 08:24 backup-my.cnf-rw-r----- 1 root  root 134217728 7 Month   10 08:24 ibdata1drwx------ 2 root root       4096 7 Month   10 08:24 lichengbingdrwx------ 2  root root      4096 7 Month   10 08:24  LILONGZIDRWX------ 2 root root      4096 7 Month   10  08:24 MYSQLDRWX------ 2 root root      4096 7 Month   10 08:24 performance_schema-rw-r----- 1 root root         21 7 Month   10 08:24 xtrabackup_binlog_info-rw-r----- 1 root root        113 7 Moon   10 08:24 xtrabackup_checkpoints  #记录LSN号文件-rw-r----- 1  root root       503 7 Month   10 08:24  Xtrabackup_info-rw-r----- 1 root root      2560 7 Month    10 08:24 xtrabackup_logfile[[email protected] 2016-07-10_08-24-43]# cat  xtrabackup_checkpoints backup_type = full-backuped  #备份类型为全备from_lsn  = 0 # The starting LSN number (since it is fully prepared, starting with the LSN number starting at 0) to_lsn = 1672995  #截止LSN号last_lsn  = 1672995compact =  0recover_binlog_info = 0

now that it's complete, we'll simulate destroying the data and then recover the data .

First stop the database

[Email protected] 3306]# mysqladmin-uroot-pli123456-s/data/3306/mysql.sock shutdown[[email protected] 3306]# Mv/data /3306/data//data/3306/data_bak/[[email protected] 3306]# mkdir-p/data/3306/data/#必须创建一个新的空data文件夹用来恢复, Otherwise the recovery will be error original data directory/data/3306/data is not empty!

At this point we are opening the database, we find that the database file is corrupted and cannot be started

[[email protected] 3306]# mysqld_safe --defaults-file=/data/3306/my.cnf &160710  10:05:14 mysqld_safe Logging to  '/data/3306/mysql_oldboy3306.err ' .160710  10:05:14 mysqld_safe starting mysqld daemon with databases from /data/ 3306/data160710 10:05:15 mysqld_safe mysqld from pid file /data/3306/ Mysqld.pid ended# pid process file failed to start [1]+  done                     mysqld_safe --defaults-file=/data /3306/my.cnf[[email protected] 3306]# tail mysql_oldboy3306.err 160710 10:05:15  innodb: 5.5.32 started; log sequence number 1595668160710 10:05:15  [Note] Recovering after a crash using /data/3306/mysql-bin160710  10:05:15 [note] starting&Nbsp;crash recovery ... 160710 10:05:15 [note] crash recovery finished.160710 10:05:15 [note]  server hostname  (bind-address):  ' 0.0.0.0 ';  port: 3306160710 10:05:15 [note]    -  ' 0.0.0.0 '  resolves to  ' 0.0.0.0 ';160710 10:05:15 [note]  server socket created on ip:  ' 0.0.0.0 '. 160710 10:05:15 [error] fatal  error: can ' t open and lock privilege tables: table  ' mysql.host '  DOESN ' t exist  #提示相关数据库表未找到160710  10:05:15 mysqld_safe mysqld from pid  file /data/3306/mysqld.pid ended

Prepare for full-scale recovery

[Email protected] 3306]# Innobackupex--defaults-file=/data/3306/my.cnf--user=root--password=li123456--apply-log/ backup/full/2016-07-10_08-24-43/... Innodb:waiting for purge to startinnodb:5.6.24 started; Log sequence number 1673228xtrabackup:starting shutdown with innodb_fast_shutdown = 1innodb:fts optimize thread exiting. Innodb:starting shutdown ... Innodb:shutdown completed; Log sequence number 1673238160710 08:42:01 completed ok!

#这里的apply-log parameter means that the database Redo log state is restored before the database data is actually restored

The next step is to restore the data file

[[email protected] 3306]# innobackupex -- defaults-file=/data/3306/my.cnf --copy-back /backup/full/2016-07-10_08-24-43/... 160710 08:43:41 [01]        ...done160710 08:43:41  [01] copying ./mysql/user.frm to /data/3306/data/mysql/user.frm160710 08:43:41  [01]        ...done160710 08:43:41 [01] copying  ./mysql/tables_priv. Myi to /data/3306/data/mysql/tables_priv. myi160710 08:43:41 [01]        ...done160710 08:43:41  [01] copying ./mysql/proxies_priv. Myi to /data/3306/data/mysql/proxies_priv. myi160710 08:43:41 [01]        ...done160710 08:43:41  completed ok! 

Change data file group again, start database successfully

[Email protected] 3306]# chown-r mysql.mysql data[[email protected] data]# mysqld_safe--defaults-file=/data/3306/ MY.CNF &[1] 90409[[email protected] data]# mysql-uroot-pli123456-s/data/3306/mysql.sockmysql> show databases;+ --------------------+| Database |+--------------------+| Information_schema | | lichengbing | #数据表恢复成功 | Lilongzi | | MySQL | | Performance_schema |+--------------------+5 rows in Set (0.00 sec)

And then simulate the incremental backup.

Insert a few rows of data in a new

[Email protected] opt]# mysql-uroot-pli123456-s/data/3306/mysql.sock lichengbing </opt/test2.sqlmysql> Select * FROM lichengbing.test;+----+-------+| ID |  Name |+----+-------+| 1 |  One | | 2 |  A | | 3 |  Three | | 4 | Four |  #此时4, 5, 6 equals database delta Files | 5 |  Five | | 6 | Six |+----+-------+6 rows in Set (0.00 sec)

Incremental backup

[[email protected] opt]# innobackupex -- defaults-file=/data/3306/my.cnf --user=root --password=li123456 --incremental /backup/add/  --incremental-basedir=/backup/full/2016-07-10_08-24-43/... 160710 08:55:15 [00] writing xtrabackup_info160710 08:55:15 [00]         ...donextrabackup: Transaction log of lsn  ( 1673693)  to  (1673693)  was copied.160710 08:55:15 completed ok! [[email protected] add]# cd /backup/add/2016-07-10_08-55-12/[[email protected]2  2016-07-10_08-55-12]# cat xtrabackup_checkpoints backup_type = incrementalfrom_lsn  = 1672995  #增量备份的起始LSN号是靠读取全备xtrabackup_checkpoints文件得到的to_lsn  = 1673693 # Incremental end LSN Number last_lsn = 1673693compact = 0recover_binlog_info = 0 

Incremental recovery

There are some differences between incremental recovery and full-scale recovery, and it is important to note that:

1) You need to "replay" the transactions that have been committed on each backup (including full and incremental backups), and after replay, all the backup data will be merged into a full backup;

2) "Roll Back" uncommitted transactions based on all backups

Close the database

[Email protected] ~]# mysqladmin-uroot-pli123456-s/data/3306/mysql.sock shutdown

Roll forward to perform a full backup

[Email protected] ~]# Innobackupex--defaults-file=/data/3306/my.cnf--user=root--pli123456--apply-log--redo-only/ backup/full/2016-07-10_08-24-43/

##--redo-only when doing an incremental restore, the full and incremental backup data files must be rolled forward in the Redo log file before the committed transaction is resumed! This parameter merges both full-and incremental-backed data files, but does not include data files for the last incremental backup

Consolidate and add all

[Email protected] ~]# Innobackupex--defaults-files=/data/3306/my.cnf--user=root--pli123456--apply-log/backup/ full/2016-07-10_08-24-43/--incremental-dir=/backup/add/2016-07-10_08-55-12/##/backup/full/2016-07-10_08-24-43/ Full directory ##--incremental-dir=/backup/add/2016-07-10_08-55-12/incremental directory ##--redo-only only one time, so it is not necessary to add this parameter, if there is n Bether, sequentially restore the In addition to the last parameter to add--redo-only each time

Destroy the database and restore the full standby and the backup back to

[[EMAIL PROTECTED] 3306]# RM -FR DATA_BAK[[EMAIL PROTECTED] 3306]# MV  data data_bak[[email protected] 3306]# innobackupex --defaults-file=/data/3306/ my.cnf --copy-back /backup/full/2016-07-10_08-24-43/... 160710 09:54:54 [01]        ...done160710 09:54:54  [01] copying ./mysql/user.frm to /data/3306/data/mysql/user.frm160710 09:54:54  [01]        ...done160710 09:54:54 [01] copying  ./mysql/tables_priv. Myi to /data/3306/data/mysql/tables_priv. Myi160710 09:54:54 [01]        ...done160710 09:54:55  [01] copying ./mysql/proxies_priv. Myi to /data/3306/data/mysql/proxies_priv. Myi160710 09:54:55 [01]        ...done160710 09:54:55 completed ok! [[Email protected] 3306]# chown -r mysql.mysql data

Restore success, Login to view

[Email protected] data]# mysqld_safe--defaults-file=/data/3306/my.cnf &[[email protected] data]# mysql-uroot- Pli123456-s/data/3306/mysql.sockmysql> SELECT * from lichengbing.test;+----+-------+| ID |  Name |+----+-------+| 1 |  One | | 2 |  A | | 3 |  Three | | 4 |  Four | | 5 |  Five | | 6 | Six |+----+-------+6 rows in Set (0.00 sec)

Some other backup examples

Specify database backup

[Email protected] each]# Innobackupex--defaults-file=/data/3306/my.cnf--user=root--password=li123456--databases= "Lichengbing"/backup/each/

Specify table Backup

[Email protected] each]# Innobackupex--defaults-file=/data/3306/my.cnf--user=root--password=li123456--databases= "Lichengbing test"/backup/each/

Backup in compressed format

[Email protected] full]# Innobackupex--defaults-file=/data/3306/my.cnf--user=root--password=li123456--stream=tar /backup/full/|gzip>/backup/full/back_ ' Date +%f '. tar.gz

Xtrabackup Backing up large databases (full backup and incremental backup)