来自 首页 2019-09-25 15:53 的文章
当前位置: 澳门太阳娱乐手机登录 > 首页 > 正文

ibdata1文件损坏时恢复InnoDB单表测试,基于Xtraba

 

 

Preface

Preface

 

 

    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.

    We all know that Xtrabackup is a backup tool of percona for innodb or Xtradb.It's usually used to back up whole databases physically.But how to restore a single innodb table from a full Xtrabackup after dropping it by accident?We can also make use of the feature of transportable tablespace to accomplish it.

 

    I've demonstrated how to restore a table on a server to another using transportable tablespace in my yesterday's blog.Today,we'll use another tool called "mysqlfrm" to fetch the structure of table in .frm files.

Porcedure

 

 

Introduce

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]

    mysqlfrm is a tool designed to diagnose information in .frm file when in recovery scenario.mysqlfrm provides two modes of operatins.By default,it creates a new instance referencing the base directory using "--basedir" it also need a port specified by "--port" which ought to be diffrent from the one used in the original instance.The other mode is connecting to the already exist instance using "--server".The new instance will be shutdown and all temperary files will be deleted after it reads data in .frm files.Further more,there're two exclusions when using mysqlfrm,one is foreign key constraints,the other one is auto increment number sequences.

 

 

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

Example

 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)

 

 

Install mysqlfrm tool.

Destroy the ibdata1 file with "dd" command.

 1 [root@zlm1 10:03:25 ~]
 2 #yum install mysql-utilities
 3 
 4 Installed:
 5   mysql-utilities.noarch 0:1.3.6-1.el7                                                                                                               
 6 
 7 Dependency Installed:
 8   mysql-connector-python.noarch 0:1.1.6-1.el7                                                                                                        
 9 
10 Complete!
 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.

 

 

**Generate a Xtrabackup backup.**

Restart the MySQL instance.

 1 [root@zlm1 10:07:36 ~]
 2 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf --host=localhost --user=root --password=Passw0rd --sock=/tmp/mysql3306.sock /data/backup
 3 180706 10:09:22 innobackupex: Starting the backup operation
 4 
 5 IMPORTANT: Please check that the backup run completes successfully.
 6            At the end of a successful backup run innobackupex
 7            prints "completed OK!".
 8 
 9 //Omitts the intermedia output.
10 
11 180706 10:10:27 Finished backing up non-InnoDB tables and files
12 180706 10:10:27 [00] Writing xtrabackup_binlog_info
13 180706 10:10:27 [00]        ...done
14 180706 10:10:27 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
15 xtrabackup: The latest check point (for incremental): '1703733455'
16 xtrabackup: Stopping log copying thread.
17 .180706 10:10:27 >> log scanned up to (1703733464)
18 
19 180706 10:10:27 Executing UNLOCK TABLES
20 180706 10:10:27 All tables unlocked
21 180706 10:10:27 [00] Copying ib_buffer_pool to /data/backup/2018-07-06_10-09-22/ib_buffer_pool
22 180706 10:10:27 [00]        ...done
23 180706 10:10:27 Backup created in directory '/data/backup/2018-07-06_10-09-22'
24 MySQL binlog position: filename 'mysql-bin.000071', position '194', GTID of the last change '2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715494'
25 180706 10:10:27 [00] Writing backup-my.cnf
26 180706 10:10:27 [00]        ...done
27 180706 10:10:27 [00] Writing xtrabackup_info
28 180706 10:10:27 [00]        ...done
29 xtrabackup: Transaction log of lsn (1703733455) to (1703733464) was copied.
30 180706 10:10:27 completed OK!
31 
32 [root@zlm1 10:10:27 ~]
33 #cd /data/backup
34 
35 [root@zlm1 10:13:14 /data/backup]
36 #ls -l
37 total 4
38 drwxr-x--- 9 root root 4096 Jul  6 10:10 2018-07-06_10-09-22
39 
40 [root@zlm1 10:13:15 /data/backup]
41 #cd 2018-07-06_10-09-22/
42 
43 [root@zlm1 10:13:19 /data/backup/2018-07-06_10-09-22]
44 #ls -l
45 total 102468
46 drwxr-x--- 2 root root        51 Jul  6 10:10 aaron8219
47 -rw-r----- 1 root root       433 Jul  6 10:10 backup-my.cnf
48 drwxr-x--- 2 root root        19 Jul  6 10:10 -help
49 -rw-r----- 1 root root      9492 Jul  6 10:10 ib_buffer_pool
50 -rw-r----- 1 root root 104857600 Jul  6 10:09 ibdata1
51 drwxr-x--- 2 root root      4096 Jul  6 10:10 mysql
52 drwxr-x--- 2 root root      8192 Jul  6 10:10 performance_schema
53 drwxr-x--- 2 root root      8192 Jul  6 10:10 sys
54 drwxr-x--- 2 root root      4096 Jul  6 10:10 sysbench
55 -rw-r----- 1 root root        69 Jul  6 10:10 xtrabackup_binlog_info
56 -rw-r----- 1 root root       119 Jul  6 10:10 xtrabackup_checkpoints
57 -rw-r----- 1 root root       639 Jul  6 10:10 xtrabackup_info
58 -rw-r----- 1 root root      2560 Jul  6 10:10 xtrabackup_logfile
59 drwxr-x--- 2 root root      4096 Jul  6 10:10 zlm
 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.

 

 

***Prepare the backup.***

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

 1 [root@zlm1 10:17:32 /data/backup/2018-07-06_10-09-22]
 2 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf --host=localhost --user=root --password=Passw0rd --sock=/tmp/mysql3306.sock --apply-log /data/backup/2018-07-06_10-09-22/
 3 180706 10:18:21 innobackupex: Starting the apply-log operation
 4 
 5 IMPORTANT: Please check that the apply-log run completes successfully.
 6            At the end of a successful apply-log run innobackupex
 7            prints "completed OK!".
 8            
 9 //Omitts the intermedia output.
10 
11 InnoDB: Database was not shutdown normally!
12 InnoDB: Starting crash recovery.
13 InnoDB: xtrabackup: Last MySQL binlog file position 139807334, file name mysql-bin.000069
14 InnoDB: Removed temporary tablespace data file: "ibtmp1"
15 InnoDB: Creating shared tablespace for temporary tables
16 InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
17 InnoDB: File './ibtmp1' size is now 12 MB.
18 InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
19 InnoDB: 32 non-redo rollback segment(s) are active.
20 InnoDB: Waiting for purge to start
21 InnoDB: 5.7.13 started; log sequence number 1703733781
22 xtrabackup: starting shutdown with innodb_fast_shutdown = 1
23 InnoDB: page_cleaner: 1000ms intended loop took 10865ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
24 InnoDB: FTS optimize thread exiting.
25 InnoDB: Starting shutdown...
26 InnoDB: Shutdown completed; log sequence number 1703733800
27 180706 10:18:36 completed OK!
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

 

 

**Drop two tables to mimic misoperation.**

Install mysql-utilities package on zlm2.

 1 root@localhost:mysql3306.sock [(none)]>show tables from zlm;
 2 +----------------+
 3 | Tables_in_zlm  |
 4 +----------------+
 5 | customer       |
 6 | goods          |
 7 | semi_sync_test |
 8 | test_flashbk   |
 9 | test_myisam    |
10 +----------------+
11 5 rows in set (0.00 sec)
12 
13 root@localhost:mysql3306.sock [(none)]>show tables from sysbench;
14 +--------------------+
15 | Tables_in_sysbench |
16 +--------------------+
17 | sbtest1            |
18 | sbtest10           |
19 | sbtest2            |
20 | sbtest3            |
21 | sbtest4            |
22 | sbtest5            |
23 | sbtest6            |
24 | sbtest7            |
25 | sbtest8            |
26 | sbtest9            |
27 +--------------------+
28 10 rows in set (0.00 sec)
29 
30 root@localhost:mysql3306.sock [(none)]>drop table zlm.test_flashbk,sysbench.sbtest1;
31 Query OK, 0 rows affected (0.11 sec)
 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!

 

 

***Diagnose .frm file from Xtrabackup using mysqlfrm.***

Restore the table structure by mysqlfrm.

 1 [root@zlm1 10:35:56 /data/backup/2018-07-06_10-09-22]
 2 #mysqlfrm --basedir=/usr/local/mysql --port=8219 --diagnostic /data/backup/2018-07-06_10-09-22/sysbench/sbtest1.frm /data/backup/2018-07-06_10-09-22/zlm/zlm:test_flashbk.frmsbtest1.frm /data/backup/2018-07-06_10-09-22/zlm/zlm:tes
 3 # WARNING The --port option is not used in the --diagnostic mode.
 4 # WARNING: Cannot generate character set or collation names without the --server option.
 5 # 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.
 6 # Reading .frm file for /data/backup/2018-07-06_10-09-22/sysbench/sbtest1.frm:
 7 # The .frm file is a TABLE.
 8 # CREATE TABLE Statement:
 9 
10 CREATE TABLE `sysbench`.`sbtest1` (
11   `id` int(11) NOT NULL AUTO_INCREMENT, 
12   `k` int(11) NOT NULL, 
13   `c` char(360) NOT NULL, 
14   `pad` char(180) NOT NULL, 
15 PRIMARY KEY `PRIMARY` (`id`),
16 KEY `k_1` (`k`)
17 ) ENGINE=InnoDB;
18 
19 # Reading .frm file for /data/backup/2018-07-06_10-09-22/zlm/test_flashbk.frm:
20 # The .frm file is a TABLE.
21 # CREATE TABLE Statement:
22 
23 CREATE TABLE `zlm`.`test_flashbk` (
24   `id` bigint(20) NOT NULL AUTO_INCREMENT, 
25   `name` varchar(80) NOT NULL, 
26 PRIMARY KEY `PRIMARY` (`id`)
27 ) ENGINE=InnoDB;
28 
29 #...done.
30 
31 //If you want to get the information of character set,"--server" is indispensable.
32 //you can either use <dbname>:<tablename>.frm or just <tablename>.frm.
33 //"--port" can be omitted.
 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 vacant table using above create statement.**

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

 1 root@localhost:mysql3306.sock [(none)]>CREATE TABLE `sysbench`.`sbtest1` (
 2     ->   `id` int(11) NOT NULL AUTO_INCREMENT, 
 3     ->   `k` int(11) NOT NULL, 
 4     ->   `c` char(360) NOT NULL, 
 5     ->   `pad` char(180) NOT NULL, 
 6     -> PRIMARY KEY `PRIMARY` (`id`),
 7     -> KEY `k_1` (`k`)
 8     -> ) ENGINE=InnoDB;
 9 ERROR 1074 (42000): Column length too big for column 'c' (max = 255); use BLOB or TEXT instead
10 root@localhost:mysql3306.sock [(none)]>CREATE TABLE `zlm`.`test_flashbk` (
11     ->   `id` bigint(20) NOT NULL AUTO_INCREMENT, 
12     ->   `name` varchar(80) NOT NULL, 
13     -> PRIMARY KEY `PRIMARY` (`id`)
14     -> ) ENGINE=InnoDB;
15 Query OK, 0 rows affected (0.02 sec)
16 
17 //We get an error when creating table sysbench.sbtest1 beause of the overload value of char.
 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 sbtest4G
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)

 

 

**Check the structure of  sbtest2 table in sysbench.**

Discard the tablesapce of new table "sbtest4".

 1 root@localhost:mysql3306.sock [(none)]>show create table sysbench.sbtest2G
 2 *************************** 1. row ***************************
 3        Table: sbtest2
 4 Create Table: CREATE TABLE `sbtest2` (
 5   `id` int(11) NOT NULL AUTO_INCREMENT,
 6   `k` int(11) NOT NULL DEFAULT '0',
 7   `c` char(120) NOT NULL DEFAULT '', //In the counterpart table,the value is 120.
 8   `pad` char(60) NOT NULL DEFAULT '', //In the counterpart table,the value is 60.
 9   PRIMARY KEY (`id`),
10   KEY `k_2` (`k`)
11 ) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8
12 1 row in set (0.00 sec)
 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.

 

 

***Change the create statement reference to the value in sbtest2.***

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

 1 when creating table sysbench.sbtest1 beause of the overload value of char.
 2 
 3 
 4 Check the structure of  sbtest2 table in sysbench.
 5  1 root@localhost:mysql3306.sock [(none)]>show create table sysbench.sbtest2G
 6  2 *************************** 1. row ***************************
 7  3        Table: sbtest2
 8  4 Create Table: CREATE TABLE `sbtest2` (
 9  5   `id` int(11) NOT NULL AUTO_INCREMENT,
10  6   `k` int(11) NO
 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

 

 

**Add a write lock on these two tables.**

Import the original tablespace of table "sbtest4".

 1 root@localhost:mysql3306.sock [(none)]>lock tables sysbench.sbtest1 write;
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 root@localhost:mysql3306.sock [(none)]>lock tables zlm.test_flashbk write;
 5 Query OK, 0 rows affected (0.00 sec)
 6 
 7 root@localhost:mysql3306.sock [(none)]>alter table sysbench.sbtest1 discard tablespace;
 8 ERROR 1100 (HY000): Table 'sbtest1' was not locked with LOCK TABLES //when locks another table,the lock on previous table will be released.
 9 root@localhost:mysql3306.sock [(none)]>lock tables sysbench.sbtest1 write; //This time,lock one and discard one in order.
10 Query OK, 0 rows affected (0.00 sec)
11 
12 root@localhost:mysql3306.sock [(none)]>alter table sysbench.sbtest1 discard tablespace;
13 Query OK, 0 rows affected (0.00 sec)
14 
15 root@localhost:mysql3306.sock [(none)]>lock tables zlm.test_flashbk write;
16 Query OK, 0 rows affected (0.00 sec)
17 
18 root@localhost:mysql3306.sock [(none)]>alter table zlm.test_flashbk discard tablespace;
19 Query OK, 0 rows affected (0.00 sec)
 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.

 

 

**Copy .ibd files from Xtrabackup and change privilege.**

Summary

 1 [root@zlm1 11:06:18 /data/backup/2018-07-06_10-09-22]
 2 #cp sysbench/sbtest1.ibd /data/mysql/mysql3306/data/sysbench
 3 
 4 [root@zlm1 11:07:50 /data/backup/2018-07-06_10-09-22]
 5 #cp zlm/test_flashbk.ibd /data/mysql/mysql3306/data/zlm
 6 
 7 [root@zlm1 11:08:05 /data/backup/2018-07-06_10-09-22]
 8 #chown -R mysql.mysql /data/mysql/mysql3306/data
 9 
10 [root@zlm1 11:11:25 /data/backup/2018-07-06_10-09-22]
11 #ls -l /data/mysql/mysql3306/data/sysbench | grep sbtest1.ibd
12 -rw-r----- 1 mysql mysql 33554432 Jul  6 11:07 sbtest1.ibd
13 
14 [root@zlm1 11:12:39 /data/backup/2018-07-06_10-09-22]
15 #ls -l /data/mysql/mysql3306/data/zlm | grep test_flashbk.ibd
16 -rw-r----- 1 mysql mysql  12582912 Jul  6 11:08 test_flashbk.ibd
  • 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.

 

 

**Import tablespaces and check data of tables.**

 1 root@localhost:mysql3306.sock [(none)]>alter table sysbench.sbtest1 import tablespace;
 2 Query OK, 0 rows affected, 1 warning (1.73 sec)
 3 
 4 root@localhost:mysql3306.sock [(none)]>show warnings;
 5 +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+
 6 | Level   | Code | Message                                                                                                                                          |
 7 +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+
 8 | Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './sysbench/sbtest1.cfg', will attempt to import without schema verification |
 9 +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+
10 1 row in set (0.00 sec)
11 
12 root@localhost:mysql3306.sock [(none)]>alter table zlm.test_flashbk import tablespace;
13 Query OK, 0 rows affected, 1 warning (1.01 sec)
14 
15 root@localhost:mysql3306.sock [(none)]>show warnings;
16 +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+
17 | Level   | Code | Message                                                                                                                                          |
18 +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+
19 | Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './zlm/test_flashbk.cfg', will attempt to import without schema verification |
20 +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+
21 1 row in set (0.00 sec)
22 
23 root@localhost:mysql3306.sock [(none)]>select count(*) from sysbench.sbtest1;
24 +----------+
25 | count(*) |
26 +----------+
27 |   100000 |
28 +----------+
29 1 row in set (0.25 sec)
30 
31 root@localhost:mysql3306.sock [(none)]>select count(*) from zlm.test_flashbk;
32 +----------+
33 | count(*) |
34 +----------+
35 |   100000 |
36 +----------+
37 1 row in set (0.10 sec)
38 
39 //The warnings show that tthe message about missing of .cfg file what rally doesn't matter.
40 //The .cfg file is usually create by executing "flush table ... for export;"
41 //We can benifit in crash recover scenario with the support of ignoring the missing of .cfg in transportable tablespace feature.

 

*Summary*

  • *mysqlfrm is a tool of mysql-utilities which is specialized in analyzing .frm files in order to gain the missing structure of tables.*
  • mysqlfrm provides two modes of operation:1. connecting to server with "--server"(defaut mode);2. create a new instance with "--basedir".
  • With the help of parameter "--diagnostic",we can even get information from a .frm file without installing a MySQL server on the host.
  • We cannot get character set and collation information on tables if we forget to use "--server" option.
  • Of course,in order to use transportable tablespace properly,the parameter "innodb_file_per_table=1" is necessary.
  • In my case above,the structure of table about char datatype changed accidently which I'm still baffled with.
  • Also,we can restore these table to any other server like transportable tablespace does.

 

本文由澳门太阳娱乐手机登录发布于首页,转载请注明出处:ibdata1文件损坏时恢复InnoDB单表测试,基于Xtraba

关键词: