Innobackupex fully prepared database

For the hot standby of MySQL database, Xtrabackup is the perfect addition to MySQL enterprise backup. The tool provides hot spares based on the InnoDB storage engine, supports full-volume, incremental, partial, point-in-time recovery, and redo slave using xtrabackup backups. The Xtrabackup Toolkit contains a INNOBACKUPEX command-line tool that supports both the InnoDB engine and the MyISAM engine. This paper mainly describes the backup principle of trabackup and gives the related demonstration.

1. Installation and Deployment Xtrabackup
:Http://www.percona.com/software/percona-xtrabackup
# The version of this installation is 2.2.5, download and unzip the rpm file, install directly

# TAR-XVF Percona-xtrabackup-2.2.5-r5027-el5-x86_64-bundle.tar
# RPM-UVH percona-xtrabackup-2.2.5-5027.el5.x86_64.rpm
# RPM-UVH percona-xtrabackup-debuginfo-2.2.5-5027.el5.x86_64.rpm
# RPM-UVH percona-xtrabackup-test-2.2.5-5027.el5.x86_64.rpm
[Email protected] ~]# ls/usr/bin/xtraba*/usr/bin/inno*
/usr/bin/innobackupex/usr/bin/innochecksum/usr/bin/xtrabackup

Xtrabackup has two main tools: Xtrabackup, Innobackupex
A, xtrabackup can only back up InnoDB and xtradb two kinds of data tables, but not the MyISAM data table
B, Innobackupex is a Perl script that encapsulates the xtrabackup. Supports the simultaneous backup of InnoDB and MyISAM engine tables.
Note: The xtrabackup and Innobackupex described in this article can be seen as the same concept.

2. Xtrabackup Working principle
The InnoDB engine is much like Oracle, using the Redo,undo mechanism, so it is important to consider the issue of writing files to the log buffers online in a timely manner during hot provisioning. If log buffer is not written in time, it will be overwritten by the circular write feature of the log. Xtrabackup remembers the log sequence number (LSN) at startup, and then copies the InnoDB data one page at a time. At the same time, monitoring log buffer in the logging situation, once the log buffer changes, that is, the data is inconsistent, the process is immediately captured and copy the changed page to Xtrabckup log, until all the InnoDB data files are copied, stop monitoring log Buffer and log replication.
Xtrabackup the rollback of committed transactions, uncommitted or failed transactions during recovery to ensure data consistency. Therefore, the table is not locked for the InnoDB table during backup. Because xtrabackup its built-in InnoDB library to open the file is RW, so the user running xtrabackup must have read and write permissions to the InnoDB data file.

3, extrabackup the backup steps

4, full demo

#当前环境 [email protected][(none)]> show variables like ' version '; +---------------+------------+| variable_name | Value |+---------------+------------+| Version | 5.6.12-log |+---------------+------------+[email protected][(none)]> show variables like '%default_storage% '; +------------------------+--------+| variable_name | Value |+------------------------+--------+| Default_storage_engine | InnoDB |+------------------------+--------+ #全备数据库 [[email protected] ~]$ Innobackupex--user=robin-password= XXX--port=3606--socket=/tmp/mysql3606.sock >--defaults-file=/data/inst3606/data3606/my3606.cnf/data/bak/ Hotbakinnodb Backup Utility v1.5.1-xtrabackup; Copyright 2003, Innobase Oy .......                                  141105 15:41:59 innobackupex:connecting to MySQL server with DSN ' dbi:mysql:; ..... innobackupex:using MySQL server version 5.6.12-loginnobackupex:created backup Directory/data/bak/hotbak the. /2014-11-05_15-42-02 #在指定备份目录下创建一个基于时间戳的文件夹141105 15:42:02 innobackupex:starting ibbackup with Command:xtrabackup--defaults-fil E= "/data/inst3606/data3606/my3606.cnf"--defaults-group= "mysqld"--backup--suspend-at-end--target-dir=/data/bak/ hotbak/2014-11-05_15-42-02--tmpdir=/tmp--extra-lsndir= '/tmp ' innobackupex:waiting for Ibbackup (pid=27441) to Suspendinnobackupex:suspend file '/data/bak/hotbak/2014-11-05_15-42-02/xtrabackup_suspended_2 ' #  Below is a configuration file to get configuration information about InnoDB Xtrabackup version 2.2.5 based on MySQL server 5.6.21 Linux (x86_64) (Revision ID:) xtrabackup:uses Posix_fadvise (). XTRABACKUP:CD to/data/inst3606/data3606xtrabackup:open Files limit requested 0, set to 1024x768 # Aut    Hor:Leshamixtrabackup:using the following InnoDB configuration: # Blog:http://blog.csdn.net/leshamixtrabackup: Innodb_data_home_dir =./xtrabackup:innodb_data_file_path = Ibdata1:10m:autoextendxtrabackup:innodb_log_group_hom E_dir =./xtrabackup:innodb_log_files_in_group = 2xtrabackup:inNodb_log_file_size = 50331648>> Log scanned up to (380655683) #扫描innodb日志lsn并复制inndodb系统表空间 [] Copying./ibdata1 To/data/bak/hotbak/2014-11-05_15-42-02/ibdata1>> Log scanned up to (380655683) >> log scanned up to (38065568 3) [done[01] Copying./MYSQL/SLAVE_RELAY_LOG_INFO.IBD To/data/bak/hotbak/2014-11-05_15-42-02/mysql/slave_rel Ay_log_info.ibd #类似部分省略, all of the IBD files based on the InnoDB engine ... [Copying]/tempdb/tb_user.ibd to/data/bak/hotbak/2014-11-05_15-42-02/tempdb/tb_user.ibd[01] ...done>> Lo G scanned up to (380655683) xtrabackup:creating suspend file '/data/bak/hotbak/2014-11-05_15-42-02/xtrabackup_ Suspended_2 ' with PID ' 27441 ' 141105 15:42:30 innobackupex:continuing after ibbackup have suspended141105 15:42:30 Innoba  Ckupex:executing FLUSH TABLES with READ LOCK ... #缓冲写出到数据文件并锁表141105 15:42:30 innobackupex:all TABLES locked and flushed To disk141105 15:42:30 innobackupex:starting to backup Non-innodb tables and files #复制非innodb表及相关文件Innobackupex:in subdirectories of '/data/inst3606/data3606 ' innobackupex:backing up files '/data/inst3606/data3606/ mysql/*. {Frm,isl,myd,myi,mad,mai,mrg,trg,trn,arm,arz,csm,csv,opt,par} ' () >> log scanned up to (380655683) innobackupex:backing up files '/data/inst3606/data3606/salary/*. {Frm,isl,myd,myi,mad,mai,mrg,trg,trn,arm,arz,csm,csv,opt,par} ' (innobackupex:backing files) up files '/data/ inst3606/data3606/performance_schema/*. {Frm,isl,myd,myi,mad,mai,mrg,trg,trn,arm,arz,csm,csv,opt,par} ' (innobackupex:backing files) up file '/data/ Inst3606/data3606/tempdb/tb_user.frm ' innobackupex:backing up file '/data/inst3606/data3606/tempdb/db.opt ' 141105 15:42:31 innobackupex:finished backing up non-innodb tables and files141105 15:42:31 innobackupex:executing FLUSH ENGI NE LOGS ... #innodb logfile write out to disk 141105 15:42:31 innobackupex:waiting for log copying to finishxtrabackup:the latest C Heck point (for incremental): ' 380655683 ' #获取最新的lsnxtrabackup: stopping log copying Thread: >> Log scanned up to (380655683) xtrabackup:creating suspend file '/data/bak/hotbak/2014-11-05_15-42-02/ Xtrabackup_log_copied ' with PID ' 27441 ' Xtrabackup:transaction log of LSN (380655683) to (380655683) was copied.141105 15: 42:32 innobackupex:all Tables Unlocked #unlock tableinnobackupex:backup created in directory '/data/bak/ Hotbak/2014-11-05_15-42-02 ' #列出binlog的位置innobackupex: MySQL binlog position:filename ' inst3606bin.000001 ', position 352, GTID of the last change "141105 15:42:32 innobackupex:connection to database server closed141105 15:42:32 INNOBAC Kupex:completed ok! 

5, backup information

In addition to the information that you can see from the input log for backup, you can obtain information from the backup location that was generated during the backup # View the files produced during the backup [[email protected] ~]$ ls/data/bak/hotbak/2014-11-05 _15-42-02/backup-my.cnf MySQL salary xtrabackup_binlog_info xtrabackup_infoibdata1 Performance_sch EMA tempdb xtrabackup_checkpoints xtrabackup_logfile# View the overall information about the backup [[email protected] ~]$ more/data/bak/hotbak/ 2014-11-05_15-42-02/xtrabackup_infouuid = 4d49753a-64bf-11e4-8850-8c89a5d108aename = Tool_name = Innobackupextool_ Command =--user=robin-password=xxx--port=3606--socket=/tmp/mysql3606.sock--defaults-file=/data/inst3606/ Data3606/my3606.cnf/data/bak/hotbaktool_version = 1.5.1-xtrabackupibbackup_version = Xtrabackup version 2.2.5 based on MySQL Server 5.6.21 Linux (x86_64) (Revision ID:) server_version = 5.6.12-logstart_time = 2014-11-05 15:41:59end_time = 20 14-11-05 15:42:32lock_time = 2binlog_pos = FileName ' inst3606bin.000001 ', position 352, GTID of the last change ' Innodb_f ROM_LSN = 0INNODB_TO_LSN = 380655683partial = Nincremental = Nformat = Filecompact = ncompressed = nencrypted = n# View information about Backup Binlog [[email protected] ~]$ MORE/DATA/BAK/HOTBAK/2 014-11-05_15-42-02/xtrabackup_binlog_infoinst3606bin.000001 information about 352# checkpoints [[email protected] ~]$ More/data/bak /hotbak/2014-11-05_15-42-02/xtrabackup_checkpointsbackup_type = FULL-BACKUPEDFROM_LSN = 0TO_LSN = 380655683LAST_LSN = 380655683compact = 0 

6, Innobackupexe backup generated related files
Backup-my.cnf
MySQL files +
Xtrabackup_binlog_info
Xtrabackup_binlog_pos_innodb
Xtrabackup_slave_info (When–slave-info is used)
Xtrabackup_checkpoints
Only After–apply-log
Xtrabackup_logfile
Xtrabackup_binary

Innobackupex fully prepared database