MySQL单表ibd文件苏醒

发布时间:2018-12-18  栏目:MySQL  评论:0 Comments

 

本博客已经搬迁至:

Preface

http://cenalulu.github.io/

 

本篇博文已经搬迁,借使显示不健康请以这链接:

    ibdata1 file is a shared system
tablespace of innodb engine.Although we always set variable of
“innodb_file_per_table” to “on” which means put the data of table
into their individual ibd files.The ibdata file will still store undo
log of our innodb tables.As the ibdata1 file is inevitable to make the
MySQL database running properly.Today,I’m gonna demonstrate a way to
rescue the table in an instance whose ibdata file is destroied by a
certain way such as write several unmeaningful characters into
it.

http://cenalulu.github.io/mysql/innodb-single-tablespace-recovery/

 

 

前言:

随着innodb的普及,innobackup也化为了主流备份形式。物理备份对于新建slave,全库復苏的需要还是可以够从容应对。

可是当面临单表数据误删,或者单表误drop的景,假设采取物理全备举行复原为? 

下文将拓展详细分析。 

过来过程中得利用的工具,percona data recover tool
: https://launchpad.net/percona-innodb-recovery-tool

manbet手机客户端3.0,PS:以下有所方案唯有援助innodb-file-per-table = 1 的动静 

小心:
以下操作非文档推荐,切勿在并未测试的意况下直接以production环境下!!!

Porcedure

事态一模一样:误删部分数据,需要为此目前同一回于备份覆盖

来源同一台机器的ibd恢复生机覆盖,且备份后table没有受recreate过。

这种情状是极致简便的,备份时的ibd文件(后如老ibd)中之space id和index id
与 新ibd的space id 和index id一致。

犹与ibdata文件被的space id和index
id一致。因而,物理文件可以一直覆盖做復苏。

以下是事无巨细步骤

——————————————-准备阶段————————————————-

Step -2 : 物理备份

 innobackupex –defaults-file=/usr/local/mysql3321/my.cnf
–socket=/xfs/mysql3321/mysql.sock –user=root –password=password
/xfs/backup/

Step -1 : 截止数据库对外劳务

service mysqld restart (起于其它一个端口上)

依旧 停止所有业务连接而set global innodb_max_dirty_pages_pct  =0

——————————————操作阶段—————————————————-

Step 0 : apply log

innobackupex –apply-log –defaults-file=/usr/local/mysql3321/my.cnf
 /xfs/backup/2012-10-17_11-29-20/

Step 1 : 备客现在的ibd文件(可选)

cp -a testibd.ibd testibd.bak

Step 2 : 摒弃现在ibd文件

mysql> alter table testibd discard tablespace

Step 3 : 复制备份ibd文件

shell> cp /xfs/backup/2012-10-17_11-29-20/test/testibd.ibd
/xfs/mysql3321/test/ 

shell> chown mysql:mysql /xfs/mysql3321/test/testibd.ibd

Step 4 : 导入ibd文件

mysql> alter table testibd import tablespace

 

 

情状二:误删 table,表结构已经被drop了

这种情景有些复杂,然而恢复过程或者比好操作的。由于table被drop后的space
id会留空由此备份文件的space id不会见受占用。

大家仅需要重建表结构,然后把ibdata中该表的space
id还原,物理文件可以直接盖做恢复了。

Step 1 : 重建表

mysql> create table testibd (UserID int);

Step 2 : 关闭mysql服务(必须)

shell> service mysqld3321 stop

Step 3: 准备ibd文件  apply log

shell> innobackupex –apply-log
–defaults-file=/usr/local/mysql3321/my.cnf
 /xfs/backup/2012-10-17_11-29-20/

Step 4 : 备份现在之ibd文件(可选)

cp -a testibd.ibd testibd.bak

Step 5 : 复制备份ibd文件

shell> cp -a /xfs/backup/2012-10-17_11-29-20/test/testibd.ibd
/xfs/mysql3321/test/ 

shell> chown mysql:mysql /xfs/mysql3321/test/testibd.ibd

Step 6 : 使用percona recovery tool 修改ibdata 

shell> /root/install/percona-data-recovery-tool-for-innodb-0.5/ibdconnect
-o /xfs/mysql3321/ibdata1 -f /xfs/mysql3321/test/testibd.ibd -d test -t
testibd

manbet手机客户端3.0 1manbet手机客户端3.0 2输出结果

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=1 in SYS_TABLE for `test`.`testibd`
Check if space id 1 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: 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 12 18 25 33 37 41 49 53 53 
Db/table: test/testibd
Space id: 2 (0x2)
Next record at offset: 74
Space id 1 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: 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 12 18 25 33 37 41 49 53 53 
Db/table: test/testibd
Space id: 2 (0x2)
Updating test/testibd (table_id 17) with id 0x01000000
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=1 in SYS_INDEXES for TABLE_ID = 17
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: 22C
Record position: 22C
Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 16 22 29 44 48 52 56 
TABLE_ID: 17
SPACE: 2
Updating SPACE(0x00000001 , 0x01000000) for TABLE_ID: 17
sizeof(s)=4
Next record at offset: 74
SYS_INDEXES is updated successfully

 

Step 7 : 使用percona recovery tool 重新checksum ibdata

再也执行以下命令,直到程序没有出口了。

shell> /root/install/percona-data-recovery-tool-for-innodb-0.5/innochecksum
-f /xfs/mysql3321/ibdata1

manbet手机客户端3.0 3manbet手机客户端3.0 4出口结果

page 8 invalid (fails old style checksum)
page 8: old style: calculated = 0xF4AD74CB; recorded = 0xEECB309D
fixing old checksum of page 8
page 8 invalid (fails new style checksum)
page 8: new style: calculated = 0x6F0C29B4; recorded = 0x3D02308C
fixing new checksum of page 8
page 11 invalid (fails old style checksum)
page 11: old style: calculated = 0x3908087C; recorded = 0xF9E8D30C
fixing old checksum of page 11
page 11 invalid (fails new style checksum)
page 11: new style: calculated = 0xB26CFD77; recorded = 0xDB25D39D
fixing new checksum of page 11

 

Step 8 : 启动mysql服务

shell> service mysqld3321 start

 

参照文档:

http://www.chriscalender.com/?p=28

http://www.mysqlperformanceblog.com/2011/05/13/connecting-orphaned-ibd-files/

http://blogs.innodb.com/wp/2012/04/innodb-transportable-tablespaces/

 http://www.mysqlperformanceblog.com/2012/01/25/how-to-recover-a-single-innodb-table-from-a-full-backup/

 

 

Backup the instance using Xtrabackup
first.

 1 [root@zlm1 13:46:27 ~]
 2 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf --user=root --password=Passw0rd /data/backup
 3 180805 13:46:40 innobackupex: Starting the backup operation
 4 ... //Omitted.
 5 
 6 [root@zlm1 13:47:14 /data/backup]
 7 #ls -l
 8 total 4
 9 drwxr-x--- 7 root root 4096 Aug  5 13:47 2018-08-05_13-46-40
10 
11 [root@zlm1 13:47:17 /data/backup]

 

Check
the target table which we want to rescue in plan.

 1 zlm@192.168.56.100:3306 [sysbench]>show tables;
 2 +--------------------+
 3 | Tables_in_sysbench |
 4 +--------------------+
 5 | sbtest1            |
 6 | sbtest2            |
 7 | sbtest3            |
 8 | sbtest4            |
 9 +--------------------+
10 4 rows in set (0.00 sec)
11 
12 zlm@192.168.56.100:3306 [sysbench]>select count(*) from sbtest4;
13 +----------+
14 | count(*) |
15 +----------+
16 |    10000 |
17 +----------+
18 1 row in set (0.05 sec)
19 
20 zlm@192.168.56.100:3306 [sysbench]>delete from sbtest4 limit 1000;
21 Query OK, 1000 rows affected (0.17 sec)
22 
23 zlm@192.168.56.100:3306 [sysbench]>select count(*) from sbtest4;
24 +----------+
25 | count(*) |
26 +----------+
27 |     9000 |
28 +----------+
29 1 row in set (0.00 sec)

 

Destroy the
ibdata1 file with “dd” command.

 1 [root@zlm1 13:57:01 ~]
 2 #cd /data/mysql/mysql3306/data
 3 
 4 [root@zlm1 13:59:35 /data/mysql/mysql3306/data]
 5 #ls -l
 6 total 433892
 7 -rw-r----- 1 mysql mysql        56 Mar 18 15:10 auto.cnf
 8 -rw-r----- 1 mysql mysql     81490 Aug  5 13:50 error.log
 9 -rw-r----- 1 mysql mysql       882 Jul 31 16:36 ib_buffer_pool
10 -rw-r----- 1 mysql mysql 104857600 Aug  5 13:45 ibdata1
11 -rw-r----- 1 mysql mysql 104857600 Aug  5 13:45 ib_logfile0
12 -rw-r----- 1 mysql mysql 104857600 Aug  5 13:45 ib_logfile1
13 -rw-r----- 1 mysql mysql 104857600 Jul  1 14:32 ib_logfile2
14 -rw-r----- 1 mysql mysql  12582912 Aug  5 13:45 ibtmp1
15 -rw-r----- 1 mysql mysql      3924 Aug  5 13:53 innodb_status.3799
16 -rw-r----- 1 mysql mysql         0 Jul 14 02:52 innodb_status.3828
17 -rw-r----- 1 mysql mysql      4008 Jun  1 21:38 innodb_status.4131
18 drwxr-x--- 2 mysql mysql      4096 Jul  7 09:57 mrbs
19 drwxr-x--- 2 mysql mysql      4096 Mar 18 15:10 mysql
20 -rw-r----- 1 mysql mysql         5 Aug  5 13:45 mysql.pid
21 drwxr-x--- 2 mysql mysql      8192 Mar 18 15:10 performance_schema
22 -rw-r----- 1 mysql mysql       177 Jun  4 16:48 relay-bin.000001
23 -rw-r----- 1 mysql mysql        19 Jun  4 16:48 relay-bin.index
24 -rw-r----- 1 mysql mysql    526773 Aug  5 13:45 slow.log
25 drwxr-x--- 2 mysql mysql      8192 Mar 18 15:10 sys
26 drwxr-x--- 2 mysql mysql      4096 Jul 31 16:35 sysbench
27 -rw-r----- 1 mysql mysql  11610419 Jul 29 09:52 zlm1.log
28 
29 [root@zlm1 13:59:38 /data/mysql/mysql3306/data]
30 #dd if=/dev/zero of=./ibdata1 bs=1024 count=262144
31 262144+0 records in
32 262144+0 records out
33 268435456 bytes (268 MB) copied, 1.61997 s, 166 MB/s
34 
35 [root@zlm1 14:02:34 /data/mysql/mysql3306/data]
36 #ls -l|grep ibdata1
37 -rw-r----- 1 mysql mysql 268435456 Aug  5 14:06 ibdata1 //The ibdata1 turned out to be 256M and filled with zero.

 

Restart the
MySQL instance.

 1 zlm@192.168.56.100:3306 [sysbench]>exit
 2 Bye
 3 
 4 [root@zlm1 14:13:06 ~]
 5 #mysqladmin shutdown
 6 
 7 [root@zlm1 14:13:10 ~]
 8 #ps aux|grep mysqld
 9 root      4002  0.0  0.0 112640   960 pts/0    R+   14:13   0:00 grep --color=auto mysqld
10 
11 [root@zlm1 14:13:15 ~]
12 #./mysqld.sh
13 
14 [root@zlm1 14:13:53 ~]
15 #mysql
16 ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.56.100' (111)
17 
18 [root@zlm1 14:13:56 ~]
19 #mysql
20 ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.56.100' (111)
21 
22 [root@zlm1 14:13:58 ~]
23 #cd /data/mysql/mysql3306/data
24 
25 [root@zlm1 14:14:07 /data/mysql/mysql3306/data]
26 #tail error.log
27 ... /Omitted.
28 2018-08-05T12:13:53.242723Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
29 2018-08-05T12:13:53.242806Z 0 [Note] mysqld (mysqld 5.7.21-log) starting as process 4008 ...
30 2018-08-05T12:13:53.249168Z 0 [Note] InnoDB: PUNCH HOLE support available
31 2018-08-05T12:13:53.249207Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
32 2018-08-05T12:13:53.249213Z 0 [Note] InnoDB: Uses event mutexes
33 2018-08-05T12:13:53.249218Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
34 2018-08-05T12:13:53.249222Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
35 2018-08-05T12:13:53.249227Z 0 [Note] InnoDB: Using Linux native AIO
36 2018-08-05T12:13:53.249426Z 0 [Note] InnoDB: Number of pools: 1
37 2018-08-05T12:13:53.249507Z 0 [Note] InnoDB: Using CPU crc32 instructions
38 2018-08-05T12:13:53.251488Z 0 [Note] InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
39 2018-08-05T12:13:53.256630Z 0 [Note] InnoDB: Completed initialization of buffer pool
40 2018-08-05T12:13:53.257913Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
41 2018-08-05T12:13:53.280321Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
42 12:13:53 UTC - mysqld got signal 11 ;
43 This could be because you hit a bug. It is also possible that this binary
44 or one of the libraries it was linked against is corrupt, improperly built,
45 or misconfigured. This error can also be caused by malfunctioning hardware.
46 Attempting to collect some information that could help diagnose the problem.
47 As this is a crash and something is definitely wrong, the information
48 collection process might fail.
49 
50 key_buffer_size=8388608
51 read_buffer_size=2097152
52 max_used_connections=0
53 max_threads=100
54 thread_count=0
55 connection_count=0
56 It is possible that mysqld could use up to 
57 key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 227127 K  bytes of memory
58 Hope that's ok; if not, decrease some variables in the equation.
59 
60 Thread pointer: 0x0
61 Attempting backtrace. You can use the following information to find out
62 where mysqld died. If you see no messages after this, something went
63 terribly wrong...
64 stack_bottom = 0 thread_stack 0x30000
65 mysqld(my_print_stacktrace+0x35)[0xf4a495]
66 mysqld(handle_fatal_signal+0x4a4)[0x7ce2f4]
67 /lib64/libpthread.so.0(+0xf130)[0x7f6bbee76130]
68 mysqld(_Z26page_cur_search_with_matchPK11buf_block_tPK12dict_index_tPK8dtuple_t15page_cur_mode_tPmS9_P10page_cur_tP8rtr_info+0x148)[0x1074478]
69 mysqld(_Z27btr_cur_search_to_nth_levelP12dict_index_tmPK8dtuple_t15page_cur_mode_tmP9btr_cur_tmPKcmP5mtr_t+0x1598)[0x11806d8]
70 mysqld(_Z30btr_pcur_open_on_user_rec_funcP12dict_index_tPK8dtuple_t15page_cur_mode_tmP10btr_pcur_tPKcmP5mtr_t+0x212)[0x1184b62]
71 mysqld[0x11df28d]
72 mysqld(_Z19dict_load_sys_tableP12dict_table_t+0x69)[0x11e0609]
73 mysqld(_Z9dict_bootv+0xdfb)[0x11bf48b]
74 mysqld(_Z34innobase_start_or_create_for_mysqlv+0x3212)[0x11150a2]
75 mysqld[0x100023a]
76 mysqld(_Z24ha_initialize_handlertonP13st_plugin_int+0x51)[0x819721]
77 mysqld[0xd39226]
78 mysqld(_Z40plugin_register_builtin_and_init_core_sePiPPc+0x3e4)[0xd397a4]
79 mysqld[0x7c48f7]
80 mysqld(_Z11mysqld_mainiPPc+0x92f)[0x7c7e9f]
81 /lib64/libc.so.6(__libc_start_main+0xf5)[0x7f6bbd857af5]
82 mysqld[0x7be479]
83 The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
84 information that should help you find out what is causing the crash.
85 
86 [root@zlm1 14:14:36 /data/mysql/mysql3306/data]
87 
88 //Okay,becasue of the lack of normal ibdata1 file in datadir,the instance cannot be started again.
89 //Then,how can we resuce the data in target table 'sbtest4'?
90 //First of all,let's get the ibd and frm file of it from the crashed server.

 

Copy
ibd and frm file of table “sbtest4” to another newly initialized
instance on zlm2.

1 [root@zlm1 14:20:39 /data/mysql/mysql3306/data/sysbench]
2 #scp sbtest4.* zlm2:/data/backup
3 sbtest4.frm                                                                                                        100% 8632     8.4KB/s   00:00    
4 sbtest4.ibd                                                                                                        100%   10MB  10.0MB/s   00:01

 

Install
mysql-utilities package on zlm2.

 1 [root@zlm2 14:23:13 ~]
 2 #mysqlfrm --help
 3 -bash: mysqlfrm: command not found
 4 
 5 [root@zlm2 14:23:17 ~]
 6 #yum install mysql-utilities
 7 Loaded plugins: fastestmirror
 8 base                                                                                                                          | 3.6 kB  00:00:00     
 9 epel/x86_64/metalink                                                                                                          | 7.0 kB  00:00:00     
10 epel                                                                                                                          | 3.2 kB  00:00:00     
11 extras                                                                                                                        | 3.4 kB  00:00:00     
12 updates                                                                                                                       | 3.4 kB  00:00:00     
13 (1/5): epel/x86_64/group_gz                                                                                                   |  88 kB  00:00:00     
14 (2/5): epel/x86_64/updateinfo                                                                                                 | 932 kB  00:00:00     
15 (3/5): extras/7/x86_64/primary_db                                                                                             | 173 kB  00:00:00     
16 (4/5): epel/x86_64/primary                                                                                                    | 3.6 MB  00:00:01     
17 (5/5): updates/7/x86_64/primary_db                                                                                            | 4.3 MB  00:00:04     
18 Loading mirror speeds from cached hostfile
19  * base: mirrors.shu.edu.cn
20  * epel: mirrors.tongji.edu.cn
21  * extras: mirrors.163.com
22  * updates: mirrors.163.com
23 epel                                                                                                                                     12629/12629
24 Resolving Dependencies
25 --> Running transaction check
26 ---> Package mysql-utilities.noarch 0:1.3.6-1.el7 will be installed
27 --> Processing Dependency: mysql-connector-python for package: mysql-utilities-1.3.6-1.el7.noarch
28 --> Running transaction check
29 ---> Package mysql-connector-python.noarch 0:1.1.6-1.el7 will be installed
30 --> Finished Dependency Resolution
31 
32 ... //Omitted.
33 
34 Installed:
35   mysql-utilities.noarch 0:1.3.6-1.el7                                                                                                               
36 
37 Dependency Installed:
38   mysql-connector-python.noarch 0:1.1.6-1.el7                                                                                                        
39 
40 Complete!

 

Restore the
table structure by mysqlfrm.

 1 [root@zlm2 14:30:41 ~]
 2 #cd /data/backup/
 3 
 4 [root@zlm2 14:30:48 /data/backup]
 5 #ls -l|grep sbtest4
 6 -rw-r----- 1 root  root      8632 Aug  5 14:30 sbtest4.frm
 7 -rw-r----- 1 root  root  10485760 Aug  5 14:30 sbtest4.ibd
 8 
 9 [root@zlm2 14:31:25 /data/backup]
10 #mysqlfrm --basedir=/usr/local/mysql --port=8219 --diagnostic ./sbtest4.frm
11 # WARNING The --port option is not used in the --diagnostic mode.
12 # WARNING: Cannot generate character set or collation names without the --server option.
13 # CAUTION: The diagnostic mode is a best-effort parse of the .frm file. As such, it may not identify all of the components of the table correctly. This is especially true for damaged files. It will also not read the default values for the columns and the resulting statement may not be syntactically correct.
14 # Reading .frm file for ./sbtest4.frm:
15 # The .frm file is a TABLE.
16 # CREATE TABLE Statement:
17 
18 CREATE TABLE `sbtest4` (
19   `id` int(11) NOT NULL AUTO_INCREMENT, 
20   `k` int(11) NOT NULL, 
21   `c` char(360) NOT NULL, 
22   `pad` char(180) NOT NULL, 
23 PRIMARY KEY `PRIMARY` (`id`),
24 KEY `k_4` (`k`)
25 ) ENGINE=InnoDB;
26 
27 #...done.

 

Create a
same table structure in the new instance using the restored “create
table” statement.

 1 [root@zlm2 14:39:02 /data/backup]
 2 #mysql
 3 Welcome to the MySQL monitor.  Commands end with ; or \g.
 4 Your MySQL connection id is 7
 5 Server version: 5.7.21-log MySQL Community Server (GPL)
 6 
 7 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
 8 
 9 Oracle is a registered trademark of Oracle Corporation and/or its
10 affiliates. Other names may be trademarks of their respective
11 owners.
12 
13 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
14 
15 zlm@192.168.56.101:3306 [(none)]>show databases;
16 +--------------------+
17 | Database           |
18 +--------------------+
19 | information_schema |
20 | mrbs               |
21 | mysql              |
22 | performance_schema |
23 | sys                |
24 +--------------------+
25 5 rows in set (0.00 sec)
26 
27 zlm@192.168.56.101:3306 [(none)]>create database sysbench;
28 Query OK, 1 row affected (0.00 sec)
29 
30 zlm@192.168.56.101:3306 [(none)]>use sysbench
31 Database changed
32 zlm@192.168.56.101:3306 [sysbench]>show tables;
33 Empty set (0.00 sec)
34 
35 zlm@192.168.56.101:3306 [sysbench]>CREATE TABLE `sbtest4` (
36     ->   `id` int(11) NOT NULL AUTO_INCREMENT, 
37     ->   `k` int(11) NOT NULL, 
38     ->   `c` char(360) NOT NULL, 
39     ->   `pad` char(180) NOT NULL, 
40     -> PRIMARY KEY `PRIMARY` (`id`),
41     -> KEY `k_4` (`k`)
42     -> ) ENGINE=InnoDB;
43 ERROR 1074 (42000): Column length too big for column 'c' (max = 255); use BLOB or TEXT instead
44 zlm@192.168.56.101:3306 [sysbench]>CREATE TABLE `sbtest4` (
45     ->   `id` int(11) NOT NULL AUTO_INCREMENT, 
46     ->   `k` int(11) NOT NULL, 
47     ->   `c` char(120) NOT NULL, 
48     ->   `pad` char(60) NOT NULL, 
49     -> PRIMARY KEY `PRIMARY` (`id`),
50     -> KEY `k_4` (`k`)
51     -> ) ENGINE=InnoDB;
52 ERROR 1146 (42S02): Table 'sysbench.sbtest4' doesn't exist
53 zlm@192.168.56.101:3306 [sysbench]>CREATE TABLE `sbtest4_bak` (
54     ->   `id` int(11) NOT NULL AUTO_INCREMENT, 
55     ->   `k` int(11) NOT NULL, 
56     ->   `c` char(120) NOT NULL, 
57     ->   `pad` char(60) NOT NULL, 
58     -> PRIMARY KEY `PRIMARY` (`id`),
59     -> KEY `k_4` (`k`)
60     -> ) ENGINE=InnoDB;
61 Query OK, 0 rows affected (0.01 sec)
62 
63 
64 zlm@192.168.56.101:3306 [sysbench]>rename table sbtest4_bak to sbtest4;
65 Query OK, 0 rows affected (0.00 sec)
66 
67 zlm@192.168.56.101:3306 [sysbench]>show tables;
68 +--------------------+
69 | Tables_in_sysbench |
70 +--------------------+
71 | sbtest4            |
72 +--------------------+
73 1 row in set (0.00 sec)
74 
75 zlm@192.168.56.101:3306 [sysbench]>show create table sbtest4\G
76 *************************** 1. row ***************************
77        Table: sbtest4
78 Create Table: CREATE TABLE `sbtest4` (
79   `id` int(11) NOT NULL AUTO_INCREMENT,
80   `k` int(11) NOT NULL,
81   `c` char(120) NOT NULL,
82   `pad` char(60) NOT NULL,
83   PRIMARY KEY (`id`),
84   KEY `k_4` (`k`)
85 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
86 1 row in set (0.00 sec)
87 
88 zlm@192.168.56.101:3306 [sysbench]>select count(*) from sbtest4;
89 +----------+
90 | count(*) |
91 +----------+
92 |        0 |
93 +----------+
94 1 row in set (0.00 sec)

 

Discard the
tablesapce of new table “sbtest4”.

 1 zlm@192.168.56.101:3306 [sysbench]>alter table sbtest4 discard tablespace;
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 [root@zlm2 15:01:45 /data/mysql/mysql3306/data/sysbench]
 5 #ls -l
 6 total 16
 7 -rw-r----- 1 mysql mysql   61 Aug  5 14:39 db.opt
 8 -rw-r----- 1 mysql mysql 8632 Aug  5 14:54 sbtest4.frm
 9 
10 //After the discard operation,the new ibd file has gone.

 

Copy
the ibd file of table “sbtest4” to the sysbench directory in
datadir(notice the owner of file).

 1 [root@zlm2 15:05:42 /data/backup]
 2 #cp sbtest4.ibd /data/mysql/mysql3306/data/sysbench/
 3 
 4 [root@zlm2 15:05:54 /data/backup]
 5 #ll /data/mysql/mysql3306/data/sysbench/ |grep sbtest4
 6 -rw-r----- 1 mysql mysql     8632 Aug  5 14:54 sbtest4.frm
 7 -rw-r----- 1 root  root  10485760 Aug  5 15:05 sbtest4.ibd
 8 
 9 [root@zlm2 15:06:12 /data/backup]
10 #chown mysql.mysql /data/mysql/mysql3306/data/sysbench/sbtest4.ibd
11 
12 [root@zlm2 15:06:39 /data/backup]
13 #ll /data/mysql/mysql3306/data/sysbench/ |grep sbtest4
14 -rw-r----- 1 mysql mysql     8632 Aug  5 14:54 sbtest4.frm
15 -rw-r----- 1 mysql mysql 10485760 Aug  5 15:05 sbtest4.ibd

 

Import the
original tablespace of table “sbtest4”.

 1 zlm@192.168.56.101:3306 [sysbench]>alter table sbtest4 import tablespace;
 2 Query OK, 0 rows affected, 1 warning (1.77 sec)
 3 
 4 zlm@192.168.56.101:3306 [sysbench]>select count(*) from sbtest4;
 5 +----------+
 6 | count(*) |
 7 +----------+
 8 |     9000 |
 9 +----------+
10 1 row in set (0.01 sec)
11 
12 //Now the table has been rescued.
13 //Because of the destroying of ibdata1 in the original instance,it should be restored by Xtrabackup again.

 

Summary

  • This method is only used to
    resuce a single table without backup when MySQL instance cannot
    startup beause of the destoryed ibdata file.
  • mysqlfrm is a tool which can
    load table structure from .frm files.We need to install the
    mysql-utilities package first.
  • If we don’t have a properly
    full Xtrabackup and binlog,the .ibd file may lose the undo
    information on target table.In this situation,it’s an incompletely
    recovery.

 

留下评论

网站地图xml地图