percona_xtrabackup
原理
percona xtrabackup备份过程主要分为以下几点：
1、xtrabackup在启动时会记住LSN(log sequence number)，然后复制所有的数据文件
2、xtrabackup会运行一个后台进程，用于监控事务日志(redo.log)，并从事务日志中复制最新的修改
3、准备过程(prepare)，xtrabackup使用之前复制的事务日志，对各个数据文件执行灾难恢复
4、执行flush tables with read lock来阻止新的写入进来，并把MyISAM表数据刷写到硬盘上，之后复制MyISAM数据文件，最后释放锁。
其中1-3步骤是xtrabackup来实现，第4步由innobackupex来实现。
备份MyISAM和Innodb表最终会处于一致，在prepare过程结束后，Innodb表数据已经前滚到整个备份数据结束的点，而不是回滚到xtrabackuo刚开始的点。
通过复制数据文件、日志文件和提交日志到数据文件（前滚）实现各种复合备份方式。
innobackupex备份对MyISAM表之前要对全库进行加READ LOCK，阻塞写操作，对Innodb表备份不会阻塞读写。
备份演练
1、创建备份目录 mkdir -p /data1/backup 2、拷贝全量数据 innobackupex --defaults-file=/data1/mysql6011/my6011.cnf --user=root --password='xxx' --tmpdir=/data1/tmp/ --socket=/tmp/mysql6011.sock --no-timestamp --slave-info /data1/backup/mysql6011 > innobackupex.log 2>&1 注意点： a、设置tmp为目录较大的位置 b、设置no-timestamp，否则以时间作为前缀 c、设置备份的目录,/data1/backup先提前创建好，然后再以mysql6011作为备份的目录名称 d、配置文件中character_set_client=utf8,导致错误 3、拷贝配置文件到另一台机器上
4、回放redolog(apply-log)
如果给主库搭建从库：
http://www.percona.com/doc/percona-xtrabackup/2.1/howtos/setting_up_replication.html
binlog点的位置为xtrabackup_binlog_info文件中
apply-log产生的日志：
[root@eos177 data1]# innobackupex -defaults-file=/data1/mysql6011/my6011.cnf --apply-log /data1/mysql6011/ InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy and Percona Inc 2009-2011. All Rights Reserved. This software is published under the GNU GENERAL PUBLIC LICENSE Version 2, June 1991. IMPORTANT: Please check that the apply-log run completes successfully. At the end of a successful apply-log run innobackupex prints "completed OK!". 140220 10:37:43 innobackupex: Starting ibbackup with command: xtrabackup_55 --defaults-file="/data1/mysql6011/my6011.cnf" --prepare --target-dir=/data1/mysql6011 xtrabackup_55 version 1.6.3 for Percona Server 5.5.9 Linux (x86_64) (revision id: 292) xtrabackup: cd to /data1/mysql6011 xtrabackup: This target seems to be not prepared yet. xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(37043716524) xtrabackup: Temporary instance for recovery is set as followings. xtrabackup: innodb_data_home_dir = ./ xtrabackup: innodb_data_file_path = ibdata1:100M:autoextend xtrabackup: innodb_log_group_home_dir = ./ xtrabackup: innodb_log_files_in_group = 1 xtrabackup: innodb_log_file_size = 2097152 140220 10:37:43 InnoDB: Using Linux native AIO xtrabackup: Starting InnoDB instance for recovery. xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter) 140220 10:37:43 InnoDB: The InnoDB memory heap is disabled 140220 10:37:43 InnoDB: Mutexes and rw_locks use GCC atomic builtins 140220 10:37:43 InnoDB: Compressed tables use zlib 1.2.3 140220 10:37:43 InnoDB: Using Linux native AIO 140220 10:37:43 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead 140220 10:37:43 InnoDB: Initializing buffer pool, size = 100.0M 140220 10:37:43 InnoDB: Completed initialization of buffer pool 140220 10:37:43 InnoDB: highest supported file format is Barracuda. InnoDB: Log scan progressed past the checkpoint lsn 37043716524 140220 10:37:43 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Doing recovery: scanned up to log sequence number 37043837591 (6 %) 140220 10:37:43 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Last MySQL binlog file position 0 218163482, file name ./mysql-bin.000038 140220 10:37:44 InnoDB: Waiting for the background threads to start 140220 10:37:45 Percona XtraDB (http://www.percona.com) 1.1.5-20.0 started; log sequence number 37043837591 [notice (again)] If you use binary log and don't use any hack of group commit, the binary log position seems to be: InnoDB: Last MySQL binlog file position 0 218163482, file name ./mysql-bin.000038 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 140220 10:37:45 InnoDB: Starting shutdown... 140220 10:37:49 InnoDB: Shutdown completed; log sequence number 37043847066 140220 10:37:49 innobackupex: Restarting xtrabackup with command: xtrabackup_55 --defaults-file="/data1/mysql6011/my6011.cnf" --prepare --target-dir=/data1/mysql6011 for creating ib_logfile* xtrabackup_55 version 1.6.3 for Percona Server 5.5.9 Linux (x86_64) (revision id: 292) xtrabackup: cd to /data1/mysql6011 xtrabackup: This target seems to be already prepared. xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'. xtrabackup: Temporary instance for recovery is set as followings. xtrabackup: innodb_data_home_dir = ./ xtrabackup: innodb_data_file_path = ibdata1:100M:autoextend xtrabackup: innodb_log_group_home_dir = ./ xtrabackup: innodb_log_files_in_group = 3 xtrabackup: innodb_log_file_size = 1363148800 140220 10:37:49 InnoDB: Using Linux native AIO xtrabackup: Starting InnoDB instance for recovery. xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter) 140220 10:37:49 InnoDB: The InnoDB memory heap is disabled 140220 10:37:49 InnoDB: Mutexes and rw_locks use GCC atomic builtins 140220 10:37:49 InnoDB: Compressed tables use zlib 1.2.3 140220 10:37:49 InnoDB: Using Linux native AIO 140220 10:37:49 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead 140220 10:37:49 InnoDB: Initializing buffer pool, size = 100.0M 140220 10:37:49 InnoDB: Completed initialization of buffer pool 140220 10:37:49 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 1300 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 140220 10:37:52 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 1300 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 140220 10:37:54 InnoDB: Log file ./ib_logfile2 did not exist: new to be created InnoDB: Setting log file ./ib_logfile2 size to 1300 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 140220 10:37:57 InnoDB: highest supported file format is Barracuda. InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 140220 10:37:57 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Last MySQL binlog file position 0 218163482, file name ./mysql-bin.000038 140220 10:37:57 InnoDB: Waiting for the background threads to start 140220 10:37:58 Percona XtraDB (http://www.percona.com) 1.1.5-20.0 started; log sequence number 37043847180 [notice (again)] If you use binary log and don't use any hack of group commit, the binary log position seems to be: InnoDB: Last MySQL binlog file position 0 218163482, file name ./mysql-bin.000038 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 140220 10:37:58 InnoDB: Starting shutdown... 140220 10:38:02 InnoDB: Shutdown completed; log sequence number 37043853783 140220 10:38:02 innobackupex: completed OK!
copy-back
innobackupex: Error: Original data directory '/data1/mysql6011' does not exist! at /etc/dbCluster/innobackupex line 361.
[root@eos177 data1]# innobackupex -defaults-file=/data1/backup/mysql6011/my6011.cnf /data1/backup/mysql6011 --copy-back InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy and Percona Inc 2009-2011. All Rights Reserved. This software is published under the GNU GENERAL PUBLIC LICENSE Version 2, June 1991. IMPORTANT: Please check that the copy-back run completes successfully. At the end of a successful copy-back run innobackupex prints "completed OK!". innobackupex: Starting to copy MyISAM tables, indexes, innobackupex: .MRG, .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV, .opt, and .frm files innobackupex: in '/data1/backup/mysql6011' innobackupex: back to original data directory '/data1/mysql6011' innobackupex: Copying directory '/data1/backup/mysql6011/performance_schema' innobackupex: Copying file '/data1/backup/mysql6011/xtrabackup_checkpoints' innobackupex: Copying file '/data1/backup/mysql6011/xtrabackup_slave_info' innobackupex: Copying directory '/data1/backup/mysql6011/mysql' innobackupex: Copying file '/data1/backup/mysql6011/xtrabackup_binary' innobackupex: Copying file '/data1/backup/mysql6011/xtrabackup_binlog_info' innobackupex: Copying directory '/data1/backup/mysql6011/zjmdmm' innobackupex: Copying file '/data1/backup/mysql6011/my6011.cnf' innobackupex: Copying directory '/data1/backup/mysql6011/elink' innobackupex: Copying file '/data1/backup/mysql6011/xtrabackup_binlog_pos_innodb' innobackupex: Copying directory '/data1/backup/mysql6011/test' innobackupex: Starting to copy InnoDB tables and indexes innobackupex: in '/data1/backup/mysql6011' innobackupex: back to original InnoDB data directory '/data1/mysql6011' innobackupex: Copying file '/data1/backup/mysql6011/ibdata1' innobackupex: Starting to copy InnoDB log files innobackupex: in '/data1/backup/mysql6011' innobackupex: back to original InnoDB log directory '/data1/mysql6011' innobackupex: Copying file '/data1/backup/mysql6011/ib_logfile0' innobackupex: Copying file '/data1/backup/mysql6011/ib_logfile2' innobackupex: Copying file '/data1/backup/mysql6011/ib_logfile1' innobackupex: Finished copying back files. 140220 10:41:29 innobackupex: completed OK!
