解析XtraBackup备份MySQL的原理和过程

XtraBackup是percona公司提供的开源工具，以热备Innodb表著称而被广泛采用。

XtraBackup对Innodb的备份之所以是热备，无需锁表，是基于Innodb自身的崩溃恢复机制，它首先复制所有的Innodb数据文件，这样复制出来的文件肯定是不一致的，然后对每个文件进行崩溃恢复处理，最终达到一致。就和MySQL在启动Innodb的时候一样，会通过比较数据文件头和redo log文件头信息来检查数据是否是一致的，如果不一致就尝试通过前滚（把redo log中所有提交的事务写入数据文件）和回滚（从数据文件中撤销所有redo log中未提交的事务引起的修改）来使数据达到最终一致。

This works because InnoDB maintains a redo log, also called the transaction log. This contains a record of every change to InnoDB's data. When InnoDB starts, it inspects the data files and the transaction log, and performs two steps. It applies committed transaction log entries to the data files, and it performs an undo operation on any transactions that modified data but did not commit.

XtraBackup works by remembering the log sequence number (LSN) when it starts, and then copying away the data files. It takes some time to do this, so if the files are changing, then they reflect the state of the database at different points in time. At the same time, XtraBackup runs a background process that watches the transaction log files, and copies changes from it. XtraBackup needs to do this continually because the transaction logs are written in a round-robin fashion, and can be reused after a while. XtraBackup needs the transaction log records for every change to the data files since it began execution.

The above is the backup process. Next is the prepare process. During this step, XtraBackup performs crash recovery against the copied data files, using the copied transaction log file. After this is done, the database is ready to restore and use.

The above process is implemented in the xtrabackup compiled binary program. The innobackupex program adds more convenience and functionality by also permitting you to back up MyISAM tables and .frm files. It starts xtrabackup, waits until it finishes copying files, and then issues FLUSH TABLES WITH READ LOCK to prevent further changes to MySQL's data and flush all MyISAM tables to disk. It holds this lock, copies the MyISAM files, and then releases the lock.

The backed-up MyISAM and InnoDB tables will eventually be consistent with each other, because after the prepare (recovery) process, InnoDB's data is rolled forward to the point at which the backup completed, not rolled back to the point at which it started. This point in time matches where the FLUSH TABLES WITH READ LOCK was taken, so the MyISAM data and the prepared InnoDB data are in sync.

The xtrabackup and innobackupex tools both offer many features not mentioned in the preceding explanation. Each tool's functionality is explained in more detail on its manual page. In brief, though, the tools permit you to do operations such as streaming and incremental backups with various combinations of copying the data files, copying the log files, and applying the logs to the data.

XtraBackup在启动的时候会记录一个LSN（log sequence number），然后就把所有的Innodb数据文件复制出来，这样复制出来的数据文件是不一致的，但是XtraBackup会在后台运行一个进程把所有对redo log file的修改记录下来，只要有了这个数据，就能进行崩溃恢复。只所以要额外记录下来，是因为MySQL自身的redo log file是可重用的。

以上的操作是由xtrabackup二进制程序（比如xtrabackup_55）完成的，如果使用innobackupex 脚本，刚才的步骤完成以后，innobackupex就会去备份MyISAM表和.frm文件，这时要保证数据的一致性就会先锁表了，通过FLUSH TABLES WITH READ LOCK命令锁表然后把文件复制出来，再释放掉这个锁。

在恢复数据的时候，要经过prepare（recovery）和restore两个步骤。在prepare结束以后，Innodb的表恢复到了复制Innodb文件结束的时间点，这个时间点也就是锁表复制MyISAM表的起点，所以最终数据是一致的。一般我们在恢复的时候执行两次prepare，是因为第二次prepare会帮助我们生成redo log文件，从而加快MySQL数据库启动的速度。

我们再来看一下实际备份的日志来理解这个过程：

......

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 suspend

innobackupex: Suspend file '/home/mysql/3306/data/xtrabackup_suspended'

xtrabackup: suspend-at-end is enabled.

xtrabackup: uses posix_fadvise().

xtrabackup: cd to /home/mysql/3306/data

xtrabackup: Target instance is assumed as followings.

xtrabackup:   innodb_data_home_dir = /home/mysql/3306/data

xtrabackup:   innodb_data_file_path = ibdata1:512M:autoextend

xtrabackup:   innodb_log_group_home_dir = /home/mysql/3306/redolog

xtrabackup:   innodb_log_files_in_group = 3

xtrabackup:   innodb_log_file_size = 134217728

110701  3:29:13 InnoDB: Using Linux native AIO

110701  3:29:13  InnoDB: Warning: allocated tablespace 268, old maximum was 0

xtrabackup: Stream mode.

>> log scanned up to (2371741708)

110701 03:29:15  innobackupex: Continuing after ibbackup has suspended

innobackupex: Starting to backup InnoDB tables and indexes

innobackupex: from original InnoDB data directory '/home/mysql/3306/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' (16 files)

......

110701 03:29:35  innobackupex: Connected to database with mysql child process (pid=22630)

>> log scanned up to (2371742526)

110701 03:29:39  innobackupex: Starting to lock all tables...

>> log scanned up to (2371742526)

>> log scanned up to (2371742526)

110701 03:29:51  innobackupex: All tables locked and flushed to disk

110701 03:29:51  innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI,

innobackupex: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV and .opt files in

innobackupex: 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}' (17 files)

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 files

innobackupex: Resuming ibbackup

xtrabackup: 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:55  innobackupex: All tables unlocked

110701 03:29:55  innobackupex: Connection to database server closed

innobackupex: Backup created in directory '/home/mysql/backup/data/3306'

innobackupex: MySQL binlog position: filename 'bin.000014', position 309836330mysql,information_schema,performance_schema

innobackupex: MySQL slave binlog position: master host '', filename '', position

innobackupex: You must use -i (--ignore-zeros) option for extraction of the tar stream.

110701 03:29:55  innobackupex: completed OK!

 

BY:

http://www.banping.com/2011/07/01/xtrabackup-process-backgroud/comment-page-1/#comment-13731