一、当前环境
[mysql@hadoop1 ~]$ mysql --defaults-file=/mysql/mysqldata//my.cnf -uroot -p123456 -P3306
mysql> show variables like 'version';
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| version | 5.6.-log |
+---------------+------------+
row in set (0.00 sec) mysql> #完全备份集
[root@hadoop1 --27_00--]# ls -l
total
-rw-rw-r-- mysql mysql May : backup-my.cnf
-rw-rw---- mysql mysql May : ibdata01
drwx------ mysql mysql May : mysql
drwxrwxr-x mysql mysql May : performance_schema
drwx------ mysql mysql May : pls_innodb
drwxrwxr-x mysql mysql May : pls_myisam
drwxrwxr-x mysql mysql May : test
-rw-rw---- mysql mysql May : undo001
-rw-rw---- mysql mysql May : undo002
-rw-rw---- mysql mysql May : undo003
-rw-rw---- mysql mysql May : undo004
-rw-rw---- mysql mysql May : undo005
-rw-rw-r-- mysql mysql May : xtrabackup_binary
-rw-rw-r-- mysql mysql May : xtrabackup_binlog_info
-rw-rw---- mysql mysql May : xtrabackup_checkpoints
-rw-rw---- mysql mysql May : xtrabackup_logfile
[root@hadoop1 --27_00--]#
二、关闭原有实例
[mysql@hadoop1 ~]$ mysqladmin --defaults-file=/mysql/mysqldata//my.cnf -uroot -p123456 -S /mysql/mysqldata//mysql.sock shutdown
[mysql@hadoop1 ~]$ netstat -an|grep
#将原有数据库重命名
[mysql@DBMysql mysql]$ mv 3306_bk
[mysql@DBMysql mysql]$ mkdir #将备份集备份
[root@hadoop1 3306_full]# ls
--27_00--
[root@hadoop1 3306_full]# cp -rf --27_00-- --27_00--05_02
三、准备全备文件，当前并不做实际的恢复
#创建临时文件夹，否则会报错
[mysql@hadoop1 ]$ mkdir tmp
[mysql@hadoop1 ]$ innobackupex --defaults-file=/mysql/mysqldata/my.cnf --apply-log /mysql/mysqldata/backup/3306_full/--27_00-- InnoDB Backup Utility v1.5.1-xtrabackup; Copyright , Innobase Oy
and Percona Ireland Ltd -. All Rights Reserved. This software is published under
the GNU GENERAL PUBLIC LICENSE Version , June . IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints "completed OK!". :: innobackupex: Starting ibbackup with command: xtrabackup_56 --defaults-file="/mysql/mysqldata/my.cnf" --defaults-group="mysqld" --prepare --target-dir=/mysql/mysqldata/backup/3306_full/--27_00-- --tmpdir=/mysql/mysqldata//tmp xtrabackup_56 version 2.0. for MySQL server 5.6. Linux (x86_64) (revision id: )
xtrabackup: cd to /mysql/mysqldata/backup/3306_full/--27_00--
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=, start_lsn=()
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 =
xtrabackup: innodb_log_file_size =
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 =
xtrabackup: innodb_log_file_size =
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 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.
InnoDB: CPU does not support crc32 instructions
InnoDB: Initializing buffer pool, size = 100.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Opened undo tablespaces
InnoDB: Highest supported file format is Barracuda.
InnoDB: The log sequence numbers and in ibdata files do not match the log sequence number 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 , file name mysql-bin.
InnoDB: rollback segment(s) are active.
InnoDB: Waiting for purge to start
-- :: 2b7849f40940 InnoDB: Warning: table 'mysql/innodb_index_stats'
InnoDB: in InnoDB data dictionary has unknown flags .
-- :: 2b7849f40940 InnoDB: Warning: table 'mysql/innodb_table_stats'
InnoDB: in InnoDB data dictionary has unknown flags .
InnoDB: 1.2. started; log sequence number [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 , file name mysql-bin. xtrabackup: starting shutdown with innodb_fast_shutdown =
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number :: innobackupex: Restarting xtrabackup with command: xtrabackup_56 --defaults-file="/mysql/mysqldata/my.cnf" --defaults-group="mysqld" --prepare --target-dir=/mysql/mysqldata/backup/3306_full/--27_00-- --tmpdir=/mysql/mysqldata//tmp
for creating ib_logfile* xtrabackup_56 version 2.0. for MySQL server 5.6. Linux (x86_64) (revision id: )
xtrabackup: cd to /mysql/mysqldata/backup/3306_full/--27_00--
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 =
xtrabackup: innodb_log_file_size =
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 =
xtrabackup: innodb_log_file_size =
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 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.
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 MB
InnoDB: Progress in MB:
InnoDB: Setting log file ./ib_logfile1 size to MB
InnoDB: Progress in MB:
InnoDB: Setting log file ./ib_logfile2 size to MB
InnoDB: Progress in MB:
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=
InnoDB: Opened undo tablespaces
InnoDB: Highest supported file format is Barracuda.
InnoDB: rollback segment(s) are active.
InnoDB: Waiting for purge to start
-- :: 2ac2c62dd940 InnoDB: Warning: table 'mysql/innodb_index_stats'
InnoDB: in InnoDB data dictionary has unknown flags .
-- :: 2ac2c62dd940 InnoDB: Warning: table 'mysql/innodb_table_stats'
InnoDB: in InnoDB data dictionary has unknown flags .
InnoDB: 1.2. started; log sequence number [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 , file name mysql-bin. xtrabackup: starting shutdown with innodb_fast_shutdown =
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number
:: innobackupex: completed OK!
[mysql@hadoop1 ]$
四、查看备份集的变化
[root@hadoop1 --27_00--]# pwd
/mysql/mysqldata/backup/3306_full/--27_00--
[root@hadoop1 --27_00--]# ls -l
total
-rw-rw-r-- mysql mysql May : backup-my.cnf
-rw-rw---- mysql mysql May : ibdata01
-rw-rw-r-- mysql mysql May : ib_logfile0
-rw-rw-r-- mysql mysql May : ib_logfile1
-rw-rw-r-- mysql mysql May : ib_logfile2
drwx------ mysql mysql May : mysql
drwxrwxr-x mysql mysql May : performance_schema
drwx------ mysql mysql May : pls_innodb
drwxrwxr-x mysql mysql May : pls_myisam
drwxrwxr-x mysql mysql May : test
-rw-rw---- mysql mysql May : undo001
-rw-rw---- mysql mysql May : undo002
-rw-rw---- mysql mysql May : undo003
-rw-rw---- mysql mysql May : undo004
-rw-rw---- mysql mysql May : undo005
-rw-rw-r-- mysql mysql May : xtrabackup_binary
-rw-rw-r-- mysql mysql May : xtrabackup_binlog_info
-rw-rw-r-- mysql mysql May : xtrabackup_binlog_pos_innodb
-rw-rw---- mysql mysql May : xtrabackup_checkpoints
-rw-rw---- mysql mysql May : xtrabackup_logfile
[root@hadoop1 --27_00--]#
注意文件时间的变化，说明准备全备文件的操作只是对备份集本身做了相关处理，所以在准备恢复前最好对备份集再次备份
五、将已经恢复好的数据文件复制回原始位置
#手动创建相关目录 否则会报错
[mysql@hadoop1 ]$ mkdir data
[mysql@hadoop1 ]$ mkdir binlog
[mysql@hadoop1 ]$ mkdir logs
[mysql@hadoop1 ]$ mkdir redo
[mysql@hadoop1 ]$ mkdir undo [mysql@hadoop1 ]$ innobackupex --defaults-file=/mysql/mysqldata/my.cnf --copy-back /mysql/mysqldata/backup/3306_full/--27_00--
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright , Innobase Oy
and Percona Ireland Ltd -. All Rights Reserved. This software is published under
the GNU GENERAL PUBLIC LICENSE Version , June . 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. :: innobackupex: completed OK!
[mysql@hadoop1 ]$
疑问：undo文件还原的位置为data目录，并不是my.cnf中指定的（innodb_undo_directory= /mysql/mysqldata/3306/undo）目录。需手动移到该目录
六、测试恢复结果
[mysql@hadoop1 logs]$ mysqld_safe --defaults-file=/mysql/mysqldata//my.cnf & #启动日志
[mysql@hadoop1 logs]$ more mysql-error.log
:: mysqld_safe Starting mysqld daemon with databases from /mysql/mysqldata//data
-- :: [Note] /usr/local/mysql/bin/mysqld (mysqld 5.6.-log) starting as process ...
-- :: [Note] InnoDB: Using atomics to ref count buffer pool pages
-- :: [Note] InnoDB: The InnoDB memory heap is disabled
-- :: [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
-- :: [Note] InnoDB: Memory barrier is not used
-- :: [Note] InnoDB: Compressed tables use zlib 1.2.
-- :: [Note] InnoDB: Not using CPU crc32 instructions
-- :: [Note] InnoDB: Initializing buffer pool, size = 256.0M
-- :: [Note] InnoDB: Completed initialization of buffer pool
-- :: [Note] InnoDB: Opened undo tablespaces
-- :: [Note] InnoDB: Highest supported file format is Barracuda.
-- :: [Note] InnoDB: rollback segment(s) are active.
-- :: [Note] InnoDB: Waiting for purge to start
-- :: [Note] InnoDB: 5.6. started; log sequence number
-- :: [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.
-- :: [Note] Server hostname (bind-address): '*'; port:
-- :: [Note] IPv6 is not available.
-- :: [Note] - '0.0.0.0' resolves to '0.0.0.0';
-- :: [Note] Server socket created on IP: '0.0.0.0'.
-- :: [Note] Event Scheduler: Loaded events
-- :: [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.6.29-log' socket: '/mysql/mysqldata/3306/mysql.sock' port: 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、启动恢复后的实例并进行相关验证