Analyze the principle and process of xtrabackup backup MySQL

Original: http://ourlinux.blog.51cto.com/274624/844859

Xtrabackup is an open source tool provided by Percona company, which is widely used in hot-standby innodb tables.

Xtrabackup to InnoDB Backup is hot standby, no lock table, is based on the InnoDB own crash recovery mechanism, it first copy all the InnoDB data files, so

The copied files are definitely inconsistent, and then the crash recovery process is done for each file, eventually achieving consistency. Just like when MySQL starts InnoDB, it compares data

File header and Redo log file header information to check whether the data is consistent, if inconsistent, try to roll forward (write all committed transactions in the redo log to the data file) and rollback (from the number

according file The uncommitted transactions in all redo logs) to make the data final and consistent.

Xtrabackup logs an LSN (log sequence number) when it is started, and then copies all the InnoDB data files so that the copied text

The pieces are inconsistent, but Xtrabackup will run a process in the background to record all changes to the redo log file, and as long as this data is available, a crash recovery can occur. Only the

For extra documentation, it is because MySQL's own redo log file is reusable.

The above operation is done by the Xtrabackup binary program (such as xtrabackup_55), if you use the Innobackupex script, the steps just completed, Innobackupex

will be to back up the MyISAM and. frm files, this time to ensure that the consistency of the data will be locked table, through the flush TABLES with READ lock command Lock table and then the file

And then release the lock.

When recovering data, go through prepare (recovery) and restore two steps. After the end of prepare, the InnoDB table is restored to the end of the copy InnoDB file.

Point in time, this time point is the lock table copy the starting point of the MyISAM table, so the final data is consistent. In general, we perform two prepare at the time of recovery because the second prepare

will help us generate the redo log file to speed up the MySQL database startup.

Let's take a look at the actual backup log to understand the process:

......110701 03:29:13 innobackupex:starting ibbackup with command:xtrabackup_55--defaults-file= "/HOME/MYSQL/3306/MY.CNF"- -backup--suspend-at-end--log-stream--target-dir=./innobackupex:waiting for Ibbackup (PID=22334) to suspendinnobackupex:suspend file '/home/mysql/320W/data/xtrabackup_suspended ' xtrabackup:suspend-at-end is enabled.xtrabackup:uses posix_fadvise (). XTRABACKUP:CD to/home/mysql/3306/dataxtrabackup:target instance is assumed as Followings.xtrabackup:innodb_data_home_dir=/home/mysql/3306/dataxtrabackup:innodb_data_file_path=Ibdata1:512m:autoextendxtrabackup:innodb_log_group_home_dir=/home/mysql/3306/redologxtrabackup:innodb_log_files_in_group= 3xtrabackup:innodb_log_file_size= 134217728110701 3:29:13innodb:using Linux Native AIO110701 3:29:13 InnoDB:Warning:allocated tablespace 268, old maximum was 0xtrabackup:stream mode.>> Log scanned up to (2371741708)110701 03:29:15innobackupex:continuing after ibbackup have suspendedinnobackupex:starting to backup InnoDB tables and Indexesinnob Ackupex:from original InnoDB Data directory '/home/mysql/320W/data ' innobackupex:backing up as tar stream ' ibdata1 ' >> log scanned up to (2371741708) >> Log scanned up to (2371742105) >> Log scanned up to (2371742105) innobackupex:backing up file '/home/mysql/3306/data/test/t.ibd ' >> log scanned up to (2371742115) innobackupex:backing up files '/home/mysql/3306/data/banping/*.ibd ' (16files) ...110701 03:29:35 innobackupex:connected to database with MySQL child process (pid=22630) >> Log scanned up to (2371742526)110701 03:29:39innobackupex:starting to lock all tables...>> log scanned up to (2371742526) >> Log scanned up to (2371742526)110701 03:29:51innobackupex:all tables locked and flushed to disk110701 03:29:51 innobackupex:starting to backup. frm,. MRG,. MYD,. MYI,Innobackupex:. TRG, . TRN,. Arm. ARZ,. Csm . CSV and. Opt files Ininnobackupex:subdirectories of '/home/mysql/3306/data ' innobackupex:backing up files '/home/mysql/3306/data/banping/*. {Frm,myd,myi,mrg,trg,trn,arm,arz,csm,csv,opt,par} ' (17files) innobackupex:backing up file '/home/mysql/3306/data/test/t.frm ' ...110701 03:29:53 innobackupex:finished backing up. frm,. MRG,. MYD,. MYI,. TRG,. TRN,. Arm. ARZ,. Csv . CSM and. Opt filesinnobackupex:resuming ibbackupxtrabackup:the Latest Check point (for incremental): '2371742526' >> log scanned up to (2371742526) xtrabackup:transaction Log of LSN (2371741708) to (2371742526) was copied.110701 03:29:55Innobackupex:all Tables Unlocked110701 03:29:55innobackupex:connection to database server closedinnobackupex:backup created in directory '/home/mysql/backup/data /3306' innobackupex:mysql binlog position:filename ' bin.000014 ', Position 309836330 Mysql,information_schema,performance_schemainnobackupex:mysql slave binlog position:master Host ", filename ",positioninnobackupex:you must use-i (--ignore-zeros) option for extraction of the tar stream.110701 03:29:55 innobackupex:completed ok!

Parsing the principle and process of xtrabackup backup MySQL (RPM)