MYSQL增量恢复

第一步：　利用全备的备份进行检查，全备时的日志和数据备份进行检查。

 innobackupex --apply-log --redo-only /apps/dbdat/backdata/xtrabackup/full
[apps@GD6-EBS-001 xtrabackup]$ innobackupex --apply-log --redo-only /apps/dbdat/backdata/xtrabackup/full

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona Inc 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!".

 

140115 18:46:44  innobackupex: Starting ibbackup with command: xtrabackup_55  --defaults-file="/apps/dbdat/backdata/xtrabackup/full/backup-my.cnf"  --defaults-group="mysqld" --prepare --target-dir=/apps/dbdat/backdata/xtrabackup/full --apply-log-only

xtrabackup_55 version 2.0.2 for Percona Server 5.5.16 Linux (x86_64) (revision id: undefined)
xtrabackup: cd to /apps/dbdat/backdata/xtrabackup/full
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=215793664, start_lsn=(1132910720933)
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:1000M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 215793664
140115 18:46:44 InnoDB: Using Linux native AIO
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:1000M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 215793664
140115 18:46:44 InnoDB: Using Linux native AIO
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
140115 18:46:44 InnoDB: The InnoDB memory heap is disabled
140115 18:46:44 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140115 18:46:44 InnoDB: Compressed tables use zlib 1.2.3
140115 18:46:44 InnoDB: Using Linux native AIO
140115 18:46:44 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead
140115 18:46:44 InnoDB: Initializing buffer pool, size = 100.0M
140115 18:46:44 InnoDB: Completed initialization of buffer pool
140115 18:46:44 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 1132910720933
140115 18:46:44  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 1132915963392 (2 %)
InnoDB: Doing recovery: scanned up to log sequence number 1132921206272 (5 %)
InnoDB: Doing recovery: scanned up to log sequence number 1132926449152 (8 %)
InnoDB: Doing recovery: scanned up to log sequence number 1132931692032 (10 %)
InnoDB: Doing recovery: scanned up to log sequence number 1132936934912 (13 %)
140115 18:46:48  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: Doing recovery: scanned up to log sequence number 1132942177792 (16 %)
InnoDB: Doing recovery: scanned up to log sequence number 1132947420672 (19 %)
InnoDB: Doing recovery: scanned up to log sequence number 1132952663552 (21 %)
InnoDB: Doing recovery: scanned up to log sequence number 1132957906432 (24 %)
InnoDB: Doing recovery: scanned up to log sequence number 1132963149312 (27 %)
140115 18:47:24  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: Doing recovery: scanned up to log sequence number 1132968392192 (30 %)
InnoDB: Doing recovery: scanned up to log sequence number 1132973635072 (32 %)
InnoDB: Doing recovery: scanned up to log sequence number 1132978877952 (35 %)
140115 18:48:01  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: Doing recovery: scanned up to log sequence number 1132984120832 (38 %)
InnoDB: Doing recovery: scanned up to log sequence number 1132989363712 (40 %)
InnoDB: Doing recovery: scanned up to log sequence number 1132994606592 (43 %)
InnoDB: Doing recovery: scanned up to log sequence number 1132999849472 (46 %)
140115 18:48:05  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: Doing recovery: scanned up to log sequence number 1133005092352 (49 %)
InnoDB: Doing recovery: scanned up to log sequence number 1133010335232 (51 %)
InnoDB: Doing recovery: scanned up to log sequence number 1133015578112 (54 %)
InnoDB: Doing recovery: scanned up to log sequence number 1133020820992 (57 %)
140115 18:48:08  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: Doing recovery: scanned up to log sequence number 1133026063872 (60 %)
InnoDB: Doing recovery: scanned up to log sequence number 1133031306752 (62 %)
InnoDB: Doing recovery: scanned up to log sequence number 1133036549632 (65 %)
InnoDB: Doing recovery: scanned up to log sequence number 1133041792512 (68 %)
140115 18:48:10  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: Doing recovery: scanned up to log sequence number 1133047035392 (71 %)
InnoDB: Doing recovery: scanned up to log sequence number 1133052278272 (73 %)
InnoDB: Doing recovery: scanned up to log sequence number 1133057521152 (76 %)
InnoDB: Doing recovery: scanned up to log sequence number 1133062764032 (79 %)
140115 18:48:13  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: Doing recovery: scanned up to log sequence number 1133068006912 (81 %)
InnoDB: Doing recovery: scanned up to log sequence number 1133073249792 (84 %)
InnoDB: Doing recovery: scanned up to log sequence number 1133078492672 (87 %)
InnoDB: Doing recovery: scanned up to log sequence number 1133083735552 (90 %)
140115 18:48:15  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: Doing recovery: scanned up to log sequence number 1133088978432 (92 %)
InnoDB: Doing recovery: scanned up to log sequence number 1133094221312 (95 %)
InnoDB: Doing recovery: scanned up to log sequence number 1133099464192 (98 %)
InnoDB: Doing recovery: scanned up to log sequence number 1133102528980 (99 %)
140115 18:48:17  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 1058549672, file name /apps/dbdat/mariadb5_data3306/log/mysql-bin.000009

[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 1058549672, file name /apps/dbdat/mariadb5_data3306/log/mysql-bin.000009

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
140115 18:48:29  InnoDB: Starting shutdown...
140115 18:48:30  InnoDB: Shutdown completed; log sequence number 1133102528980
140115 18:48:30  innobackupex: completed OK!


 innobackupex --apply-log --redo-only --incremental  /apps/dbdat/backdata/xtrabackup/full  --incremental-dir=/apps/dbdat/backdata/xtrabackup/rec0
在最后出现这个提示：
140115 18:49:15  innobackupex: No updated xtrabackup_slave_info found in incremental dir, removing stale xtrabackup_slave_info file from target dir.140115 18:50:32  innobackupex: completed OK!


 innobackupex --apply-log --redo-only --incremental  /apps/dbdat/backdata/xtrabackup/full  --incremental-dir=/apps/dbdat/backdata/xtrabackup/rec1
140115 18:51:20  innobackupex: No updated xtrabackup_slave_info found in incremental dir, removing stale xtrabackup_slave_info file from target dir.140115 18:52:42  innobackupex: completed OK!


 innobackupex --apply-log --redo-only --incremental  /apps/dbdat/backdata/xtrabackup/full  --incremental-dir=/apps/dbdat/backdata/xtrabackup/rec2

140115 18:53:00  innobackupex: No updated xtrabackup_slave_info found in incremental dir, removing stale xtrabackup_slave_info file from target dir.140115 18:54:11  innobackupex: completed OK!


 innobackupex --apply-log --redo-only --incremental  /apps/dbdat/backdata/xtrabackup/full  --incremental-dir=/apps/dbdat/backdata/xtrabackup/rec3

140115 18:54:30  innobackupex: No updated xtrabackup_slave_info found in incremental dir, removing stale xtrabackup_slave_info file from target dir.140115 18:55:46  innobackupex: completed OK!


 innobackupex --apply-log --redo-only --incremental  /apps/dbdat/backdata/xtrabackup/full  --incremental-dir=/apps/dbdat/backdata/xtrabackup/rec4

140115 19:15:29  innobackupex: No updated xtrabackup_slave_info found in incremental dir, removing stale xtrabackup_slave_info file from target dir.140115 19:16:52  innobackupex: completed OK!


 innobackupex --apply-log --redo-only --incremental  /apps/dbdat/backdata/xtrabackup/full  --incremental-dir=/apps/dbdat/backdata/xtrabackup/rec5
140115 19:17:08  innobackupex: No updated xtrabackup_slave_info found in incremental dir, removing stale xtrabackup_slave_info file from target dir.140115 19:18:39  innobackupex: completed OK!

停掉MYSQL进行全恢复：

[apps@GD6-EBS-001 xtrabackup]$ /apps/sh/mariadb5.sh stop
Enter password: 
140115 19:19:22 mysqld_safe mysqld from pid file /apps/dbdat/mariadb5_data3306/mysql.pid ended

[apps@GD6-EBS-001 xtrabackup]$ ps -elf | grep mysql
0 S apps     14352  5568  0  80   0 - 25823 pipe_w 19:19 pts/1    00:00:00 grep mysql

把现有的数据文件目录给移走：

[apps@GD6-EBS-001 dbdat]$ mv mariadb5_data3306 mariadb5_data3306bak
[apps@GD6-EBS-001 dbdat]$ mkdir mariadb5_data3306
[apps@GD6-EBS-001 dbdat]$ cd

进行完全恢复：

[apps@GD6-EBS-001 ~]$ innobackupex --copy-back /apps/dbdat/backdata/xtrabackup/full

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona Inc 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!".

Warning: xtrabackup: ignoring option '--innodb_adaptive_hash_index' due to invalid value 'ON'
innobackupex: Starting to copy files in '/apps/dbdat/backdata/xtrabackup/full'
innobackupex: back to original data directory '/apps/dbdat/mariadb5_data3306'
innobackupex: Copying '/apps/dbdat/backdata/xtrabackup/full/ib_lru_dump' to '/apps/dbdat/mariadb5_data3306/ib_lru_dump'
innobackupex: Copying '/apps/dbdat/backdata/xtrabackup/full/xtrabackup_binlog_pos_innodb' to '/apps/dbdat/mariadb5_data3306/xtrabackup_binlog_pos_innodb'
innobackupex: Creating directory '/apps/dbdat/mariadb5_data3306/test'

中间全是COPY文件，所以没有贴出来。

innobackupex: Copying '/apps/dbdat/backdata/xtrabackup/full/mysql/time_zone_name.MYI' to '/apps/dbdat/mariadb5_data3306/mysql/time_zone_name.MYI'

innobackupex: Starting to copy InnoDB system tablespace
innobackupex: in '/apps/dbdat/backdata/xtrabackup/full'
innobackupex: back to original InnoDB data directory '/apps/dbdat/mariadb5_data3306'
innobackupex: Copying file '/apps/dbdat/backdata/xtrabackup/full/ibdata1'

innobackupex: Starting to copy InnoDB log files
innobackupex: in '/apps/dbdat/backdata/xtrabackup/full'
innobackupex: back to original InnoDB log directory '/apps/dbdat/mariadb5_data3306'
innobackupex: Finished copying back files.

140115 20:05:06  innobackupex: completed OK!

到最后显示OK。

启动实例：

[apps@GD6-EBS-001 ~]$ /apps/sh/mariadb5.sh start
[apps@GD6-EBS-001 ~]$ 140115 20:07:35 mysqld_safe Logging to '/apps/logs/mysql/error3306.log'.
140115 20:07:35 mysqld_safe Starting mysqld daemon with databases from /apps/dbdat/mariadb5_data3306

[apps@GD6-EBS-001 ~]$ ps -elf | grep mysql
0 S apps     17191     1  2  80   0 - 26570 wait   20:07 pts/1    00:00:00 /bin/sh /apps/svr/mariadb5/bin/mysqld_safe --defaults-file=/apps/conf/mysql/mariadb3306.cnf
0 R apps     19101 17191 84  80   0 - 2941667 -    20:07 pts/1    00:00:05 /apps/svr/mariadb5/bin/mysqld --defaults-file=/apps/conf/mysql/mariadb3306.cnf --basedir=/apps/svr/mariadb5 --datadir=/apps/dbdat/mariadb5_data3306 --plugin-dir=/apps/svr/mariadb5/lib/plugin --log-error=/apps/logs/mysql/error3306.log --open-files-limit=8192 --pid-file=/apps/dbdat/mariadb5_data3306/mysql.pid --socket=/tmp/mysql3306.sock --port=3306
0 S apps     19114  5568  0  80   0 - 25824 pipe_w 20:07 pts/1    00:00:00 grep mysql
[apps@GD6-EBS-001 ~]$ mysql -udba -p -S /tmp/mysql3306.sock 
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.31-MariaDB-log MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

dba@localhost : (none) 20:07:58> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ggs_admin          |
| log                |
| mysql              |
| performance_schema |
| test               |
| tms                |