xml地图|网站地图|网站标签 [设为首页] [加入收藏]
当前位置: www8029com > 澳门新葡8522最新网站 > 正文

澳门新葡8522最新网站据说Xtrabackup恢复单个innod

时间:2019-10-02 04:22来源:澳门新葡8522最新网站
    Preface Preface * *       I've got a db design job aboutmeeting room booking system last week.There're many suitable tools whichcan be used to handle this kind of job such as powerdesigner,ERwin,HeidiSQL,dbschema,etc.Today,I'm gon

 

 

Preface

Preface

* *

 

    I've got a db design job about meeting room booking system last week.There're many suitable tools which can be used to handle this kind of job such as power designer,ERwin,HeidiSQL,dbschema,etc.Today,I'm gonna demonstrate the last one —— dbschema.This is the official website:**https://www.dbschema.com

    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.

Introduce

 

 

Introduce

    dbschema is a rather simply used tool even you're a novice in designing db system.The dbschema which is downloaded on official website only free for 15 days,then you have to pay for license for later useage but there's no limit in function at all.It provide two modes in designing layout of your system.One is offline mode and the other one is connecting to db servers.You can easily synchronize tables of database with your designed tables as soon as possible by refreshing them from time to time.It also supports almost all popular rdbms such as oracle,db2,MySQL,postgreSQL.There're many key features which you can found in the homepage of official website.I'm not going to describe each one of them.

 

 

    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.

Procedure

 

 

Example

*    The meeting room booking system(I'll call it "mrbs" .) I  contains four tables:employee,department,conference_room,room_reservation.The detail of tables shows below.
*

 

 

Install mysqlfrm tool.

employee table

 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 id             自增id                int(11)
2 user_id        工号                  int(11)
3 user_name      用户名称              varchar(20)
4 user_phone     用户手机号             bigint
5 user_email     用户邮箱              varchar(50)
6 user_dept_id   用户所在部门id         int(11)
7 user_status    在职、离职等           tinyint(4)
8 create_time    用户创建时间           datetime
9 update_time    用户信息修改时间       datetime

 

 

**Generate a Xtrabackup backup.**

**department table**

 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 id              自增id                 int(11)
2 dept_id         部门id                 int(11)
3 dept_name       部门名称               varchar(30)
4 parent_id       父级id                 tinyint(4)
5 tlevel          层级id                 tinyint(4)
6 create_time     部门创建时间            datetime    
7 update_time     部门信息修改时间        datetime

 

 

**澳门新葡8522最新网站,*Prepare the backup.***

**conference_room table**

 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 id                 自增id                int(11)
2 room_id            会议室id              int(11)
3 room_building_id   楼号                  int(11)
4 room_num           房间号                int(11)
5 room_max_num       最大容纳人数           int(11)
6 room_status        会议室状态            tinyint(4)
7 create_time        会议室创建时间         datetime
8 update_time        会议室信息修改时间     datetime

 

 

**Drop two tables to mimic misoperation.**

**room_reservation table**

 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 会议室预定表(room_reservation)
 2 id                  自增id               int(11)
 3 book_id             预定工单id            int(11)
 4 book_room_id        预定会议室id          int(11)
 5 book_start_time     预定开始时间          datetime
 6 book_stop_time      预定结束时间          datetime
 7 book_user_id        预定人id              int(11)
 8 book_usage          预定用途              varchar(200)
 9 book_status         预定工单状态           tinyint(4)
10 create_time         预定工单创建时间       datetime
11 update_time         预定工单修改时间       datetime

 

 

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

**Configure the database connection.**

 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.

澳门新葡8522最新网站 1

 

 

**Create vacant table using above create statement.**

Use mouse to create target tables in dbschema.

 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.

 澳门新葡8522最新网站 2

 

 

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

Check the primary key & unique key(even other keys but I'm not creating them ye).

 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)

 澳门新葡8522最新网站 3

 

 

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

Check the foreign key.

 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

 澳门新葡8522最新网站 4

 

 

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

After you click ok button,the table will be created in "mrbs" database.

 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)

澳门新葡8522最新网站 5

 

 

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

Check the tables in "mrbys".

 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
 1 root@localhost:mysql3306.sock [mrbs]>show tables;
 2  ------------------ 
 3 | Tables_in_mrbs   |
 4  ------------------ 
 5 | conference_room  |
 6 | department       |
 7 | employee         |
 8 | room_reservation |
 9  ------------------ 
10 4 rows in set (0.01 sec)
11 
12 root@localhost:mysql3306.sock [mrbs]>show create table employeeG
13 *************************** 1. row ***************************
14        Table: employee
15 Create Table: CREATE TABLE `employee` (
16   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
17   `user_id` int(10) unsigned NOT NULL COMMENT '工号',
18   `user_name` varchar(20) NOT NULL COMMENT '用户名称',
19   `user_phone` bigint(20) unsigned NOT NULL COMMENT '用户手机号',
20   `user_email` varchar(50) DEFAULT NULL COMMENT '用户邮箱',
21   `user_dept_id` int(10) unsigned NOT NULL COMMENT '用户所在部门id',
22   `user_status` tinyint(3) unsigned NOT NULL COMMENT '是否在职',
23   `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '用户创建时间',
24   `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '用户信息修改时间',
25   PRIMARY KEY (`id`),
26   UNIQUE KEY `uk_user_id` (`user_id`)
27 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表'
28 1 row in set (0.00 sec)
29 
30 root@localhost:mysql3306.sock [mrbs]>show create table departmentG
31 *************************** 1. row ***************************
32        Table: department
33 Create Table: CREATE TABLE `department` (
34   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
35   `dept_id` int(10) unsigned NOT NULL COMMENT '部门id',
36   `dept_name` varchar(30) NOT NULL COMMENT '部门名称',
37   `parent_id` tinyint(3) unsigned NOT NULL,
38   `tlevel` tinyint(3) unsigned NOT NULL COMMENT '层级',
39   `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '部门创建时间',
40   `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '部门信息修改时间',
41   PRIMARY KEY (`id`),
42   UNIQUE KEY `uk_dept_id` (`dept_id`)
43 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='部门表'
44 1 row in set (0.00 sec)
45 
46 root@localhost:mysql3306.sock [mrbs]>show create table conference_roomG
47 *************************** 1. row ***************************
48        Table: conference_room
49 Create Table: CREATE TABLE `conference_room` (
50   `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
51   `room_id` int(10) unsigned NOT NULL COMMENT '会议室id',
52   `room_building_id` int(10) unsigned NOT NULL COMMENT '楼号',
53   `room_num` int(10) unsigned NOT NULL COMMENT '房间号',
54   `room_max_num` int(10) unsigned NOT NULL COMMENT '最大容纳人数',
55   `room_status` tinyint(3) unsigned NOT NULL COMMENT '会议室状态',
56   `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '会议室创建时间',
57   `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '会议室信息修改时间',
58   PRIMARY KEY (`id`),
59   UNIQUE KEY `uk_room_id` (`room_id`)
60 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='会议室表'
61 1 row in set (0.00 sec)
62 
63 root@localhost:mysql3306.sock [mrbs]>show create table room_reservationG
64 *************************** 1. row ***************************
65        Table: room_reservation
66 Create Table: CREATE TABLE `room_reservation` (
67   `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
68   `book_id` int(10) unsigned NOT NULL COMMENT '预定工单id',
69   `book_room_id` int(10) unsigned NOT NULL COMMENT '预定会议室id',
70   `book_start_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '预定开始时间',
71   `book_stop_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '预定结束时间',
72   `book_user_id` int(10) unsigned NOT NULL COMMENT '预定人id',
73   `book_usage` varchar(200) NOT NULL COMMENT '预定用途',
74   `book_status` tinyint(3) unsigned NOT NULL COMMENT '预定工单状态',
75   `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '预定工单创建时间',
76   `update_time` date DEFAULT NULL COMMENT '预定工单修改时间',
77   PRIMARY KEY (`id`),
78   UNIQUE KEY `uk_book_id` (`book_id`)
79 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='会议室预定表'
80 1 row in set (0.00 sec)

 

 

**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.

 

编辑:澳门新葡8522最新网站 本文来源:澳门新葡8522最新网站据说Xtrabackup恢复单个innod

关键词: www8029com