xtrabackup之Innobackupex全备恢复
一、当前环境
[mysql@hadoop1 ~]$ mysql --defaults-file=/mysql/mysqldata/3306/my.cnf -uroot -p123456 -P3306 mysql> show variables like 'version'; +---------------+------------+ | Variable_name | Value | +---------------+------------+ | version | 5.6.29-log | +---------------+------------+ 1 row in set (0.00 sec) mysql> #完全备份集 [root@hadoop1 2016-05-27_00-23-05]# ls -l total 153824 -rw-rw-r-- 1 mysql mysql 290 May 27 00:23 backup-my.cnf -rw-rw---- 1 mysql mysql 104857600 May 27 00:23 ibdata01 drwx------ 2 mysql mysql 4096 May 27 00:23 mysql drwxrwxr-x 2 mysql mysql 4096 May 27 00:23 performance_schema drwx------ 2 mysql mysql 4096 May 27 00:23 pls_innodb drwxrwxr-x 2 mysql mysql 4096 May 27 00:23 pls_myisam drwxrwxr-x 2 mysql mysql 4096 May 27 00:23 test -rw-rw---- 1 mysql mysql 10485760 May 27 00:23 undo001 -rw-rw---- 1 mysql mysql 10485760 May 27 00:23 undo002 -rw-rw---- 1 mysql mysql 10485760 May 27 00:23 undo003 -rw-rw---- 1 mysql mysql 10485760 May 27 00:23 undo004 -rw-rw---- 1 mysql mysql 10485760 May 27 00:23 undo005 -rw-rw-r-- 1 mysql mysql 13 May 27 00:23 xtrabackup_binary -rw-rw-r-- 1 mysql mysql 24 May 27 00:23 xtrabackup_binlog_info -rw-rw---- 1 mysql mysql 77 May 27 00:23 xtrabackup_checkpoints -rw-rw---- 1 mysql mysql 2560 May 27 00:23 xtrabackup_logfile [root@hadoop1 2016-05-27_00-23-05]#
二、关闭原有实例
[mysql@hadoop1 ~]$ mysqladmin --defaults-file=/mysql/mysqldata/3306/my.cnf -uroot -p123456 -S /mysql/mysqldata/3306/mysql.sock shutdown [mysql@hadoop1 ~]$ netstat -an|grep 3306 #将原有数据库重命名 [mysql@DBMysql mysql]$ mv 3306 3306_bk [mysql@DBMysql mysql]$ mkdir 3306 #将备份集备份 [root@hadoop1 3306_full]# ls 2016-05-27_00-23-05 [root@hadoop1 3306_full]# cp -rf 2016-05-27_00-23-05 2016-05-27_00-23-05_02
三、准备全备文件，当前并不做实际的恢复
#创建临时文件夹，否则会报错 [mysql@hadoop1 3306]$ mkdir tmp [mysql@hadoop1 3306]$ innobackupex --defaults-file=/mysql/mysqldata/my.cnf --apply-log /mysql/mysqldata/backup/3306_full/2016-05-27_00-23-05 InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy and Percona Ireland Ltd 2009-2012. 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!". 160527 01:14:19 innobackupex: Starting ibbackup with command: xtrabackup_56 --defaults-file="/mysql/mysqldata/my.cnf" --defaults-group="mysqld" --prepare --target-dir=/mysql/mysqldata/backup/3306_full/2016-05-27_00-23-05 --tmpdir=/mysql/mysqldata/3306/tmp xtrabackup_56 version 2.0.7 for MySQL server 5.6.10 Linux (x86_64) (revision id: 552) xtrabackup: cd to /mysql/mysqldata/backup/3306_full/2016-05-27_00-23-05 xtrabackup: This target seems to be not prepared yet. xtrabackup: xtrabackup_logfile detected: size=2359296, start_lsn=(1527120) xtrabackup: Temporary instance for recovery is set as followings. xtrabackup: innodb_data_home_dir = ./ xtrabackup: innodb_data_file_path = ibdata01:100M:autoextend xtrabackup: innodb_log_group_home_dir = ./ xtrabackup: innodb_log_files_in_group = 1 xtrabackup: innodb_log_file_size = 2359296 xtrabackup: Temporary instance for recovery is set as followings. xtrabackup: innodb_data_home_dir = ./ xtrabackup: innodb_data_file_path = ibdata01:100M:autoextend xtrabackup: innodb_log_group_home_dir = ./ xtrabackup: innodb_log_files_in_group = 1 xtrabackup: innodb_log_file_size = 2359296 xtrabackup: Starting InnoDB instance for recovery. xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter) InnoDB: The InnoDB memory heap is disabled InnoDB: Mutexes and rw_locks use GCC atomic builtins InnoDB: Compressed tables use zlib 1.2.3 InnoDB: CPU does not support crc32 instructions InnoDB: Initializing buffer pool, size = 100.0M InnoDB: Completed initialization of buffer pool InnoDB: Opened 5 undo tablespaces InnoDB: Highest supported file format is Barracuda. InnoDB: The log sequence numbers 1512687 and 1512687 in ibdata files do not match the log sequence number 1527120 in the ib_logfiles! InnoDB: Database was not shutdown normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages InnoDB: from the doublewrite buffer... InnoDB: Last MySQL binlog file position 0 1714, file name mysql-bin.000003 InnoDB: 128 rollback segment(s) are active. InnoDB: Waiting for purge to start 2016-05-27 01:14:20 2b7849f40940 InnoDB: Warning: table 'mysql/innodb_index_stats' InnoDB: in InnoDB data dictionary has unknown flags 50. 2016-05-27 01:14:20 2b7849f40940 InnoDB: Warning: table 'mysql/innodb_table_stats' InnoDB: in InnoDB data dictionary has unknown flags 50. InnoDB: 1.2.10 started; log sequence number 1527120 [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 1714, file name mysql-bin.000003 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 InnoDB: FTS optimize thread exiting. InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 1527144 160527 01:14:22 innobackupex: Restarting xtrabackup with command: xtrabackup_56 --defaults-file="/mysql/mysqldata/my.cnf" --defaults-group="mysqld" --prepare --target-dir=/mysql/mysqldata/backup/3306_full/2016-05-27_00-23-05 --tmpdir=/mysql/mysqldata/3306/tmp for creating ib_logfile* xtrabackup_56 version 2.0.7 for MySQL server 5.6.10 Linux (x86_64) (revision id: 552) xtrabackup: cd to /mysql/mysqldata/backup/3306_full/2016-05-27_00-23-05 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 = ibdata01:100M:autoextend xtrabackup: innodb_log_group_home_dir = ./ xtrabackup: innodb_log_files_in_group = 3 xtrabackup: innodb_log_file_size = 134217728 xtrabackup: Temporary instance for recovery is set as followings. xtrabackup: innodb_data_home_dir = ./ xtrabackup: innodb_data_file_path = ibdata01:100M:autoextend xtrabackup: innodb_log_group_home_dir = ./ xtrabackup: innodb_log_files_in_group = 3 xtrabackup: innodb_log_file_size = 134217728 xtrabackup: Starting InnoDB instance for recovery. xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter) InnoDB: The InnoDB memory heap is disabled InnoDB: Mutexes and rw_locks use GCC atomic builtins InnoDB: Compressed tables use zlib 1.2.3 InnoDB: CPU does not support crc32 instructions InnoDB: Initializing buffer pool, size = 100.0M InnoDB: Completed initialization of buffer pool InnoDB: Setting log file ./ib_logfile101 size to 128 MB InnoDB: Progress in MB: 100 InnoDB: Setting log file ./ib_logfile1 size to 128 MB InnoDB: Progress in MB: 100 InnoDB: Setting log file ./ib_logfile2 size to 128 MB InnoDB: Progress in MB: 100 InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0 InnoDB: New log files created, LSN=1527144 InnoDB: Opened 5 undo tablespaces InnoDB: Highest supported file format is Barracuda. InnoDB: 128 rollback segment(s) are active. InnoDB: Waiting for purge to start 2016-05-27 01:14:24 2ac2c62dd940 InnoDB: Warning: table 'mysql/innodb_index_stats' InnoDB: in InnoDB data dictionary has unknown flags 50. 2016-05-27 01:14:24 2ac2c62dd940 InnoDB: Warning: table 'mysql/innodb_table_stats' InnoDB: in InnoDB data dictionary has unknown flags 50. InnoDB: 1.2.10 started; log sequence number 1527308 [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 1714, file name mysql-bin.000003 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 InnoDB: FTS optimize thread exiting. InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 1527318 160527 01:14:26 innobackupex: completed OK! [mysql@hadoop1 3306]$
四、查看备份集的变化
[root@hadoop1 2016-05-27_00-23-05]# pwd /mysql/mysqldata/backup/3306_full/2016-05-27_00-23-05 [root@hadoop1 2016-05-27_00-23-05]# ls -l total 549744 -rw-rw-r-- 1 mysql mysql 290 May 27 00:23 backup-my.cnf -rw-rw---- 1 mysql mysql 104857600 May 27 01:14 ibdata01 -rw-rw-r-- 1 mysql mysql 134217728 May 27 01:14 ib_logfile0 -rw-rw-r-- 1 mysql mysql 134217728 May 27 01:14 ib_logfile1 -rw-rw-r-- 1 mysql mysql 134217728 May 27 01:14 ib_logfile2 drwx------ 2 mysql mysql 4096 May 27 00:23 mysql drwxrwxr-x 2 mysql mysql 4096 May 27 00:23 performance_schema drwx------ 2 mysql mysql 4096 May 27 00:23 pls_innodb drwxrwxr-x 2 mysql mysql 4096 May 27 00:23 pls_myisam drwxrwxr-x 2 mysql mysql 4096 May 27 00:23 test -rw-rw---- 1 mysql mysql 10485760 May 27 01:14 undo001 -rw-rw---- 1 mysql mysql 10485760 May 27 01:14 undo002 -rw-rw---- 1 mysql mysql 10485760 May 27 01:14 undo003 -rw-rw---- 1 mysql mysql 10485760 May 27 01:14 undo004 -rw-rw---- 1 mysql mysql 10485760 May 27 01:14 undo005 -rw-rw-r-- 1 mysql mysql 13 May 27 00:23 xtrabackup_binary -rw-rw-r-- 1 mysql mysql 24 May 27 00:23 xtrabackup_binlog_info -rw-rw-r-- 1 mysql mysql 22 May 27 01:14 xtrabackup_binlog_pos_innodb -rw-rw---- 1 mysql mysql 77 May 27 01:14 xtrabackup_checkpoints -rw-rw---- 1 mysql mysql 2359296 May 27 01:14 xtrabackup_logfile [root@hadoop1 2016-05-27_00-23-05]#
注意文件时间的变化，说明准备全备文件的操作只是对备份集本身做了相关处理，所以在准备恢复前最好对备份集再次备份
五、将已经恢复好的数据文件复制回原始位置
#手动创建相关目录 否则会报错 [mysql@hadoop1 3306]$ mkdir data [mysql@hadoop1 3306]$ mkdir binlog [mysql@hadoop1 3306]$ mkdir logs [mysql@hadoop1 3306]$ mkdir redo [mysql@hadoop1 3306]$ mkdir undo [mysql@hadoop1 3306]$ innobackupex --defaults-file=/mysql/mysqldata/my.cnf --copy-back /mysql/mysqldata/backup/3306_full/2016-05-27_00-23-05 InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy and Percona Ireland Ltd 2009-2012. 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 files in '/mysql/mysqldata/backup/3306_full/2016-05-27_00-23-05' innobackupex: back to original data directory '/mysql/mysqldata/3306/data' innobackupex: Copying '/mysql/mysqldata/backup/3306_full/2016-05-27_00-23-05/xtrabackup_binlog_pos_innodb' to '/mysql/mysqldata/3306/data/xtrabackup_binlog_pos_innodb' innobackupex: Creating directory '/mysql/mysqldata/3306/data/test' innobackupex: Creating directory '/mysql/mysqldata/3306/data/mysql' .......拷贝相关文件 innobackupex: Creating directory '/mysql/mysqldata/3306/data/performance_schema' .......拷贝相关文件 innobackupex: Creating directory '/mysql/mysqldata/3306/data/pls_innodb' .......拷贝相关文件 nnobackupex: Creating directory '/mysql/mysqldata/3306/data/pls_myisam' .......拷贝相关文件 innobackupex: Starting to copy InnoDB system tablespace innobackupex: in '/mysql/mysqldata/backup/3306_full/2016-05-27_00-23-05' innobackupex: back to original InnoDB data directory '/mysql/mysqldata/3306/data' innobackupex: Copying '/mysql/mysqldata/backup/3306_full/2016-05-27_00-23-05/ibdata01' to '/mysql/mysqldata/3306/data/ibdata01' innobackupex: Starting to copy InnoDB undo tablespaces innobackupex: in '/mysql/mysqldata/backup/3306_full/2016-05-27_00-23-05' innobackupex: back to '/mysql/mysqldata/3306/data' innobackupex: Copying '/mysql/mysqldata/backup/3306_full/2016-05-27_00-23-05/undo005' to '/mysql/mysqldata/3306/data' .......拷贝相关文件 innobackupex: Starting to copy InnoDB log files innobackupex: in '/mysql/mysqldata/backup/3306_full/2016-05-27_00-23-05' innobackupex: back to original InnoDB log directory '/mysql/mysqldata/3306/redo' innobackupex: Copying '/mysql/mysqldata/backup/3306_full/2016-05-27_00-23-05/ib_logfile1' to '/mysql/mysqldata/3306/redo' .......拷贝相关文件 innobackupex: Finished copying back files. 160527 01:27:10 innobackupex: completed OK! [mysql@hadoop1 3306]$
疑问：undo文件还原的位置为data目录，并不是my.cnf中指定的（innodb_undo_directory= /mysql/mysqldata/3306/undo）目录。需手动移到该目录
六、测试恢复结果
[mysql@hadoop1 logs]$ mysqld_safe --defaults-file=/mysql/mysqldata/3306/my.cnf & #启动日志 [mysql@hadoop1 logs]$ more mysql-error.log 160527 01:38:38 mysqld_safe Starting mysqld daemon with databases from /mysql/mysqldata/3306/data 2016-05-27 01:38:48 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.6.29-log) starting as process 14386 ... 2016-05-27 01:38:49 14386 [Note] InnoDB: Using atomics to ref count buffer pool pages 2016-05-27 01:38:49 14386 [Note] InnoDB: The InnoDB memory heap is disabled 2016-05-27 01:38:49 14386 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2016-05-27 01:38:49 14386 [Note] InnoDB: Memory barrier is not used 2016-05-27 01:38:49 14386 [Note] InnoDB: Compressed tables use zlib 1.2.3 2016-05-27 01:38:49 14386 [Note] InnoDB: Not using CPU crc32 instructions 2016-05-27 01:38:49 14386 [Note] InnoDB: Initializing buffer pool, size = 256.0M 2016-05-27 01:38:51 14386 [Note] InnoDB: Completed initialization of buffer pool 2016-05-27 01:38:52 14386 [Note] InnoDB: Opened 5 undo tablespaces 2016-05-27 01:38:52 14386 [Note] InnoDB: Highest supported file format is Barracuda. 2016-05-27 01:38:53 14386 [Note] InnoDB: 128 rollback segment(s) are active. 2016-05-27 01:38:53 14386 [Note] InnoDB: Waiting for purge to start 2016-05-27 01:38:53 14386 [Note] InnoDB: 5.6.29 started; log sequence number 1527318 2016-05-27 01:38:53 14386 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 4c2b3a8e-23cd-11e6-bc50-080027c83f2b. 2016-05-27 01:38:53 14386 [Note] Server hostname (bind-address): '*'; port: 3306 2016-05-27 01:38:53 14386 [Note] IPv6 is not available. 2016-05-27 01:38:53 14386 [Note] - '0.0.0.0' resolves to '0.0.0.0'; 2016-05-27 01:38:53 14386 [Note] Server socket created on IP: '0.0.0.0'. 2016-05-27 01:38:53 14386 [Note] Event Scheduler: Loaded 0 events 2016-05-27 01:38:53 14386 [Note] /usr/local/mysql/bin/mysqld: ready for connections. Version: '5.6.29-log' socket: '/mysql/mysqldata/3306/mysql.sock' port: 3306 For mysqltest [mysql@hadoop1 logs]$
七、小结
a、Xtrabackup恢复的目的要是保证事务(数据)的一致性，Xtrabackup log会记录这些事务备份期间的状态
b、恢复过程分为2个阶段，一个是Prepare阶段，一个是copy back阶段，恢复前关闭原有实例
c、Prepare阶段会根据从innodb logfile捕获出来的信息(记录在xtrabackup log)来进行相应的前滚或回滚
d、Prepare阶段会在成功前滚或回滚后创建新的innodb logfile(空)
e、copy back阶段则是将成功恢复的全部文件复制回原来或指定的数据目录(目录应为空目录)
f、copy back前需要关闭原有实例，如果恢复到不同的实例则不需要
g、copy back完成后应做相应的权限修改
h、启动恢复后的实例并进行相关验证
