xtrabackup结合percona recover工具恢复被drop的表

mysql版本
mysql> select version();
+-------------------------------------------+
| version()                                 |
+-------------------------------------------+
| 5.6.22-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select database();
+------------+
| database() |
+------------+
| live_koo   |
+------------+
1 row in set (0.00 sec)

物理备份全库：
[root@test11g scripts]# innobackupex --user=root --password=root --socket=/tmp/mysql.sock --defaults-file=/tmp/my.cnf /tol/xtrabackup/full --no-timestamp

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p
150101 16:57:16  innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/tmp/my.cnf;mysql_read_default_group=xtrabackup;mysql_socket=/tmp/mysql.sock' as 'root'  (using password: YES).
150101 16:57:16  innobackupex: Connected to MySQL server
150101 16:57:16  innobackupex: Executing a version check against the server...
150101 16:57:16  innobackupex: Done.
IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

innobackupex: Using mysql server version 5.6.22-enterprise-commercial-advanced-log

innobackupex: Created backup directory /tol/xtrabackup/full

150101 16:57:16  innobackupex: Starting ibbackup with command: xtrabackup_56  --defaults-file="/tmp/my.cnf"  --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/tol/xtrabackup/full --tmpdir=/tmp
innobackupex: Waiting for ibbackup (pid=21328) to suspend
innobackupex: Suspend file '/tol/xtrabackup/full/xtrabackup_suspended_2'

xtrabackup_56 version 2.1.6 for MySQL server 5.6.11 Linux (x86_64) (revision id: 702)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /tol/mysql/data/
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 2097152000
>> log scanned up to (91560373)
InnoDB: Allocated tablespace 4, old maximum was 0
[01] Copying ./ibdata1 to /tol/xtrabackup/full/ibdata1
[01]        ...done
[01] Copying ./mysql/slave_master_info.ibd to /tol/xtrabackup/full/mysql/slave_master_info.ibd
[01]        ...done
[01] Copying ./mysql/slave_worker_info.ibd to /tol/xtrabackup/full/mysql/slave_worker_info.ibd
[01]        ...done
[01] Copying ./mysql/innodb_table_stats.ibd to /tol/xtrabackup/full/mysql/innodb_table_stats.ibd
[01]        ...done
[01] Copying ./mysql/slave_relay_log_info.ibd to /tol/xtrabackup/full/mysql/slave_relay_log_info.ibd
[01]        ...done
[01] Copying ./mysql/innodb_index_stats.ibd to /tol/xtrabackup/full/mysql/innodb_index_stats.ibd
[01]        ...done
[01] Copying ./live_koo/live_room_failing.ibd to /tol/xtrabackup/full/live_koo/live_room_failing.ibd
[01]        ...done
[01] Copying ./live_koo/live_room.ibd to /tol/xtrabackup/full/live_koo/live_room.ibd
[01]        ...done
[01] Copying ./live_koo/live_consumer_koo.ibd to /tol/xtrabackup/full/live_koo/live_consumer_koo.ibd
[01]        ...done
[01] Copying ./live_koo/live_users.ibd to /tol/xtrabackup/full/live_koo/live_users.ibd
[01]        ...done
[01] Copying ./live_koo/live_stats_detail.ibd to /tol/xtrabackup/full/live_koo/live_stats_detail.ibd
>> log scanned up to (91560373)
[01]        ...done
[01] Copying ./live_koo/live_stats_num.ibd to /tol/xtrabackup/full/live_koo/live_stats_num.ibd
[01]        ...done
[01] Copying ./live_koo/live_provider_soooner.ibd to /tol/xtrabackup/full/live_koo/live_provider_soooner.ibd
[01]        ...done
>> log scanned up to (91560373)
xtrabackup: Creating suspend file '/tol/xtrabackup/full/xtrabackup_suspended_2' with pid '21328'

150101 16:57:19  innobackupex: Continuing after ibbackup has suspended
150101 16:57:19  innobackupex: Starting to lock all tables...
150101 16:57:19  innobackupex: All tables locked and flushed to disk

150101 16:57:19  innobackupex: Starting to backup non-InnoDB tables and files
innobackupex: in subdirectories of '/tol/mysql/data/'
innobackupex: Backing up files '/tol/mysql/data//mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (74 files)
>> log scanned up to (91560373)
innobackupex: Backing up file '/tol/mysql/data//live_koo/live_room_failing.frm'
innobackupex: Backing up file '/tol/mysql/data//live_koo/live_users.frm'
innobackupex: Backing up file '/tol/mysql/data//live_koo/db.opt'
innobackupex: Backing up file '/tol/mysql/data//live_koo/live_stats_num.frm'
innobackupex: Backing up file '/tol/mysql/data//live_koo/live_consumer_koo.frm'
innobackupex: Backing up file '/tol/mysql/data//live_koo/live_provider_soooner.frm'
innobackupex: Backing up file '/tol/mysql/data//live_koo/live_room.frm'
innobackupex: Backing up file '/tol/mysql/data//live_koo/live_stats_detail.frm'
innobackupex: Backing up files '/tol/mysql/data//performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (53 files)
150101 16:57:20  innobackupex: Finished backing up non-InnoDB tables and files

150101 16:57:20  innobackupex: Waiting for log copying to finish

xtrabackup: The latest check point (for incremental): '91560373'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (91560373)

xtrabackup: Creating suspend file '/tol/xtrabackup/full/xtrabackup_log_copied' with pid '21328'
xtrabackup: Transaction log of lsn (91560373) to (91560373) was copied.
150101 16:57:21  innobackupex: All tables unlocked

innobackupex: Backup created in directory '/tol/xtrabackup/full'
innobackupex: MySQL binlog position: filename 'mysql-bin.000022', position 78332367
150101 16:57:21  innobackupex: Connection to database server closed
150101 16:57:21  innobackupex: completed OK!

测试用表：34258条数据
mysql> select count(*) from live_users;
+----------+
| count(*) |
+----------+
|    34258 |
+----------+
1 row in set (0.02 sec)

删除该表
mysql> drop table live_users;
Query OK, 0 rows affected (0.01 sec)

mysql> exit

apply log 
[root@test11g scripts]# innobackupex --apply-log --defaults-file=/tmp/my.cnf /tol/xtrabackup/full/

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p

IMPORTANT: Please check that the apply-log run completes successfully.
           At the end of a successful apply-log run innobackupex
           prints "completed OK!".

150101 16:59:35  innobackupex: Starting ibbackup with command: xtrabackup_56  --defaults-file="/tmp/my.cnf"  --defaults-group="mysqld" --prepare --target-dir=/tol/xtrabackup/full --tmpdir=/tmp

xtrabackup_56 version 2.1.6 for MySQL server 5.6.11 Linux (x86_64) (revision id: 702)
xtrabackup: cd to /tol/xtrabackup/full
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(91560373)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 2097152
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 2097152
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: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, size = 100.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Highest supported file format is Barracuda.
InnoDB: The log sequence numbers 1625987 and 1625987 in ibdata files do not match the log sequence number 91560373 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 78332237, file name mysql-bin.000022
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
2015-01-01 16:59:35 7f8de9187700  InnoDB: Warning: table 'mysql/innodb_index_stats'
InnoDB: in InnoDB data dictionary has unknown flags 50.
2015-01-01 16:59:35 7f8de9187700  InnoDB: Warning: table 'mysql/innodb_table_stats'
InnoDB: in InnoDB data dictionary has unknown flags 50.
InnoDB: 5.6.11 started; log sequence number 91560373

[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 78332237, file name mysql-bin.000022

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 91560383

150101 16:59:36  innobackupex: Restarting xtrabackup with command: xtrabackup_56  --defaults-file="/tmp/my.cnf"  --defaults-group="mysqld" --prepare --target-dir=/tol/xtrabackup/full --tmpdir=/tmp
for creating ib_logfile*

xtrabackup_56 version 2.1.6 for MySQL server 5.6.11 Linux (x86_64) (revision id: 702)
xtrabackup: cd to /tol/xtrabackup/full
xtrabackup: This target seems to be already prepared.
xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 2097152000
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 2097152000
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: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, size = 100.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Setting log file ./ib_logfile101 size to 2000 MB
InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000
InnoDB: Setting log file ./ib_logfile1 size to 2000 MB
InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=91560383
InnoDB: Highest supported file format is Barracuda.
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
2015-01-01 16:59:45 7f8fde5ef700  InnoDB: Warning: table 'mysql/innodb_index_stats'
InnoDB: in InnoDB data dictionary has unknown flags 50.
2015-01-01 16:59:45 7f8fde5ef700  InnoDB: Warning: table 'mysql/innodb_table_stats'
InnoDB: in InnoDB data dictionary has unknown flags 50.
InnoDB: 5.6.11 started; log sequence number 91560460

[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 78332237, file name mysql-bin.000022

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 91560470
150101 16:59:46  innobackupex: completed OK!

直接从备份中拷贝被drop表的相关文件来恢复是不行的，即使文件存在，也会报表不存在
[root@test11g live_koo]# cp /tol/xtrabackup/full/live_koo/live_users.* ./

mysql> use live_koo;                   
Database changed
mysql> select count(*) from live_users;
ERROR 1017 (HY000): Can't find file: './live_koo/live_users.frm' (errno: 13 - Permission denied)

[root@test11g live_koo]# chown mysql:mysql live_users.*
mysql> select count(*) from live_users;
ERROR 1146 (42S02): Table 'live_koo.live_users' doesn't exist

[root@test11g live_koo]# rm -rf live_users.*

恢复方法：先创建一个表结构相同的表：
mysql> CREATE TABLE `live_users` (
    ->   `id` int(11) NOT NULL,
    ->   `user_name` varchar(90) DEFAULT NULL,
    ->   `channel` varchar(50) DEFAULT NULL,
    ->   `sub_channel` varchar(50) DEFAULT NULL,
    ->   `email` varchar(250) DEFAULT NULL,
    ->   `mobile` varchar(20) DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

关闭mysql服务
[root@test11g scripts]# /etc/init.d/mysqld stop 
Shutting down MySQL..                                      [  OK  ]

从备份中拷贝ibd文件，并更改权限
root@test11g live_koo]# cp /tol/xtrabackup/full/live_koo/live_users.ibd ./
[root@test11g live_koo]# chown mysql:mysql live_users.ibd 

使用percona recovery tool 修改ibdata
[root@test11g percona-data-recovery-tool]# ./ibdconnect -o /tol/mysql/data/ibdata1 -f /tol/mysql/data/live_koo/live_users.ibd -d live_koo -t live_users
/tol/mysql/data/live_koo/live_users.ibd belongs to space #12
Initializing table definitions...
Processing table: SYS_TABLES
 - total fields: 10
 - nullable fields: 6
 - minimum header size: 5
 - minimum rec size: 21
 - maximum rec size: 555

Processing table: SYS_INDEXES
 - total fields: 9
 - nullable fields: 5
 - minimum header size: 5
 - minimum rec size: 29
 - maximum rec size: 165

Setting SPACE=12 in SYS_TABLE for `live_koo`.`live_users`
Check if space id 12 is already used
Page_id: 8, next page_id: 4294967295
Record position: 65
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 8 50 3 2 0 0 0 0 0 
Db/table: infimum
Space id: 1768842857 (0x696E6669)
Next record at offset: 16E
Record position: 16E
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 13 19 26 34 38 42 50 54 54 
Db/table: SYS_DATAFILES
Space id: 0 (0x0)
Next record at offset: 8D
Record position: 8D
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 11 17 24 32 36 40 48 52 52 
Db/table: SYS_FOREIGN
Space id: 0 (0x0)
Next record at offset: D5
Record position: D5
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 16 22 29 37 41 45 53 57 57 
Db/table: SYS_FOREIGN_COLS
Space id: 0 (0x0)
Next record at offset: 122
Record position: 122
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 15 21 28 36 40 44 52 56 56 
Db/table: SYS_TABLESPACES
Space id: 0 (0x0)
Next record at offset: 361
Record position: 361
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 26 32 39 47 51 55 63 67 67 
Db/table: live_koo/live_consumer_koo
Space id: 6 (0x6)
Next record at offset: 3B8
Record position: 3B8
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 30 36 43 51 55 59 67 71 71 
Db/table: live_koo/live_provider_soooner
Space id: 7 (0x7)
Next record at offset: 413
Record position: 413
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 18 24 31 39 43 47 55 59 59 
Db/table: live_koo/live_room
Space id: 8 (0x8)
Next record at offset: 462
Record position: 462
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 26 32 39 47 51 55 63 67 67 
Db/table: live_koo/live_room_failing
Space id: 9 (0x9)
Next record at offset: 4B9
Record position: 4B9
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 26 32 39 47 51 55 63 67 67 
Db/table: live_koo/live_stats_detail
Space id: 10 (0xA)
Next record at offset: 510
Record position: 510
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 23 29 36 44 48 52 60 64 64 
Db/table: live_koo/live_stats_num
Space id: 11 (0xB)
Next record at offset: 564
Record position: 564
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 19 25 32 40 44 48 56 60 60 
Db/table: live_koo/live_users
Space id: 13 (0xD)
Next record at offset: 20D
Record position: 20D
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 24 30 37 45 49 53 61 65 65 
Db/table: mysql/innodb_index_stats
Space id: 2 (0x2)
Next record at offset: 1B8
Record position: 1B8
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 24 30 37 45 49 53 61 65 65 
Db/table: mysql/innodb_table_stats
Space id: 1 (0x1)
Next record at offset: 2B9
Record position: 2B9
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 23 29 36 44 48 52 60 64 64 
Db/table: mysql/slave_master_info
Space id: 4 (0x4)
Next record at offset: 262
Record position: 262
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 26 32 39 47 51 55 63 67 67 
Db/table: mysql/slave_relay_log_info
Space id: 3 (0x3)
Next record at offset: 30D
Record position: 30D
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 23 29 36 44 48 52 60 64 64 
Db/table: mysql/slave_worker_info
Space id: 5 (0x5)
Next record at offset: 74
Space id 12 is not used in any of the records in SYS_TABLES
Page_id: 8, next page_id: 4294967295
Record position: 65
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 8 50 3 2 0 0 0 0 0 
Db/table: infimum
Space id: 1768842857 (0x696E6669)
Next record at offset: 16E
Record position: 16E
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 13 19 26 34 38 42 50 54 54 
Db/table: SYS_DATAFILES
Space id: 0 (0x0)
Next record at offset: 8D
Record position: 8D
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 11 17 24 32 36 40 48 52 52 
Db/table: SYS_FOREIGN
Space id: 0 (0x0)
Next record at offset: D5
Record position: D5
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 16 22 29 37 41 45 53 57 57 
Db/table: SYS_FOREIGN_COLS
Space id: 0 (0x0)
Next record at offset: 122
Record position: 122
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 15 21 28 36 40 44 52 56 56 
Db/table: SYS_TABLESPACES
Space id: 0 (0x0)
Next record at offset: 361
Record position: 361
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 26 32 39 47 51 55 63 67 67 
Db/table: live_koo/live_consumer_koo
Space id: 6 (0x6)
Next record at offset: 3B8
Record position: 3B8
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 30 36 43 51 55 59 67 71 71 
Db/table: live_koo/live_provider_soooner
Space id: 7 (0x7)
Next record at offset: 413
Record position: 413
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 18 24 31 39 43 47 55 59 59 
Db/table: live_koo/live_room
Space id: 8 (0x8)
Next record at offset: 462
Record position: 462
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 26 32 39 47 51 55 63 67 67 
Db/table: live_koo/live_room_failing
Space id: 9 (0x9)
Next record at offset: 4B9
Record position: 4B9
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 26 32 39 47 51 55 63 67 67 
Db/table: live_koo/live_stats_detail
Space id: 10 (0xA)
Next record at offset: 510
Record position: 510
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 23 29 36 44 48 52 60 64 64 
Db/table: live_koo/live_stats_num
Space id: 11 (0xB)
Next record at offset: 564
Record position: 564
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 19 25 32 40 44 48 56 60 60 
Db/table: live_koo/live_users
Space id: 13 (0xD)
Updating live_koo/live_users (table_id 28) with id 0x0C000000
SYS_TABLES is updated successfully
Initializing table definitions...
Processing table: SYS_TABLES
 - total fields: 10
 - nullable fields: 6
 - minimum header size: 5
 - minimum rec size: 21
 - maximum rec size: 555

Processing table: SYS_INDEXES
 - total fields: 9
 - nullable fields: 5
 - minimum header size: 5
 - minimum rec size: 29
 - maximum rec size: 165

Setting SPACE=12 in SYS_INDEXES for TABLE_ID = 28
Page_id: 11, next page_id: 4294967295
Record position: 65
Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 50 7 2 0 0 0 0 
TABLE_ID: 3798561113125514496
SPACE: 1768842857
Next record at offset: 8C
Record position: 8C
Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 16 22 29 35 39 43 47 
TABLE_ID: 11
SPACE: 0
Next record at offset: CE
Record position: CE
Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 16 22 29 36 40 44 48 
TABLE_ID: 11
SPACE: 0
Next record at offset: 111
Record position: 111
Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 16 22 29 36 40 44 48 
TABLE_ID: 11
SPACE: 0
Next record at offset: 154
Record position: 154
Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 16 22 29 35 39 43 47 
TABLE_ID: 12
SPACE: 0
Next record at offset: 196
Record position: 196
Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 16 22 29 50 54 58 62 
TABLE_ID: 13
SPACE: 0
Next record at offset: 1E7
Record position: 1E7
Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 16 22 29 48 52 56 60 
TABLE_ID: 14
SPACE: 0
Next record at offset: 236
Record position: 236
Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 16 22 29 36 40 44 48 
TABLE_ID: 15
SPACE: 1
Next record at offset: 279
Record position: 279
Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 16 22 29 36 40 44 48 
TABLE_ID: 16
SPACE: 2
Next record at offset: 2BC
Record position: 2BC
Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 16 22 29 36 40 44 48 
TABLE_ID: 17
SPACE: 3
Next record at offset: 2FF
Record position: 2FF
Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 16 22 29 36 40 44 48 
TABLE_ID: 18
SPACE: 4
Next record at offset: 342
Record position: 342
Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 16 22 29 36 40 44 48 
TABLE_ID: 19
SPACE: 5
Next record at offset: 385
Record position: 385
Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 16 22 29 36 40 44 48 
TABLE_ID: 20
SPACE: 6
Next record at offset: 3C8
Record position: 3C8
Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 16 22 29 54 58 62 66 
TABLE_ID: 20
SPACE: 6
Next record at offset: 41D
Record position: 41D
Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 16 22 29 36 40 44 48 
TABLE_ID: 21
SPACE: 7
Next record at offset: 460
Record position: 460
Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 16 22 29 40 44 48 52 
TABLE_ID: 21
SPACE: 7
Next record at offset: 4A7
Record position: 4A7
Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 16 22 29 36 40 44 48 
TABLE_ID: 22
SPACE: 8
Next record at offset: 4EA
Record position: 4EA
Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 16 22 29 44 48 52 56 
TABLE_ID: 22
SPACE: 8
Next record at offset: 535
Record position: 535
Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 16 22 29 36 40 44 48 
TABLE_ID: 23
SPACE: 9
Next record at offset: 578
Record position: 578
Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 16 22 29 40 44 48 52 
TABLE_ID: 23
SPACE: 9
Next record at offset: 5BF
Record position: 5BF
Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 16 22 29 36 40 44 48 
TABLE_ID: 24
SPACE: 10
Next record at offset: 602
Record position: 602
Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 16 22 29 40 44 48 52 
TABLE_ID: 24
SPACE: 10
Next record at offset: 649
Record position: 649
Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 16 22 29 36 40 44 48 
TABLE_ID: 25
SPACE: 11
Next record at offset: 68C
Record position: 68C
Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 16 22 29 40 44 48 52 
TABLE_ID: 25
SPACE: 11
Next record at offset: 716
Record position: 716
Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 16 22 29 36 40 44 48 
TABLE_ID: 28
SPACE: 13
Updating SPACE(0x0000000C , 0x0C000000) for TABLE_ID: 28
sizeof(s)=4
Next record at offset: 74
SYS_INDEXES is updated successfully

使用percona recovery tool 重新checksum ibdata
[root@test11g percona-data-recovery-tool]# ./innochecksum -f /tol/mysql/data/ibdata1 
page 8 invalid (fails new style checksum)
page 8: new style: calculated = 0x266CEF14; recorded = 0x8440A05
fixing new checksum of page 8
page 11 invalid (fails new style checksum)
page 11: new style: calculated = 0xF8159BA8; recorded = 0x1A02C2A9
fixing new checksum of page 11
[root@test11g percona-data-recovery-tool]#

[root@test11g scripts]# /etc/init.d/mysqld start
Starting MySQL..The server quit without updating PID file ([FAILED]ql/data/test11g.pid).
[root@test11g scripts]# /etc/init.d/mysqld start
Starting MySQL..                                           [  OK  ]

mysql> use live_koo;                               
Database changed
mysql> select count(*) from live_users;
+----------+
| count(*) |
+----------+
|    34258 |
+----------+
1 row in set (0.04 sec)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23249684/viewspace-1386969/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23249684/viewspace-1386969/