mysql frm ibd 创建表_通过.frm表结构和.ibd文件恢复数据

最新推荐文章于 2024-04-15 00:49:07 发布

漫姐

最新推荐文章于 2024-04-15 00:49:07 发布

阅读量367

点赞数

文章标签： mysql frm ibd 创建表

本文链接：https://blog.csdn.net/weixin_35521120/article/details/113591358

版权

整个恢复过程其实可以总结为下面几步：

(1)：恢复表结构

(2)：复制出来创建表的sql语句

(3)：恢复表数据(在恢复表数据的时候，首先需要解除当前创建的表与默认生成的.ibd文件间的关系，接着将要恢复数据表的.ibd文件与当前创建的表联系起来即可)

1.通过官网下载mysql-utilities-1.6.5.tar.gz(https://downloads.mysql.com/archives/utilities/)

#tar xzvf mysql-utilities-1.6.5.tar.gz

#. cd mysql-utilities-1.6.5

#python setup.py install

[root@node01 soft]# cd /data/mysql/mysql3306/

[root@node01 mysql3306]# ll

total 24

drwxr-xr-x 7 mysql mysql 4096 Sep 22 17:05 data

drwxr-xr-x 2 mysql mysql 4096 Sep 22 17:05 logs

-rw-r--r-- 1 mysql mysql 3080 Sep 12 13:09 my3306.cnf

-rw-r--r-- 1 mysql mysql 9890 Jul 20 10:52 my3306.cnf.bak

drwxr-xr-x 2 mysql mysql 6 Sep 22 17:05 tmp

[root@node01 mysql3306]# cd data/

[root@node01 data]# mysqlf

mysqlfailover mysqlfrm

[root@node01 data]# mysqlfrm --diagnostic qq/

db.opt sbtest.frm sbtest.ibd

[root@node01 data]# mysqlfrm --diagnostic qq/sbtest.frm

# WARNING: Cannot generate character set or collation names without the --server option.

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

# Reading .frm file for qq/sbtest.frm:

# The .frm file is a TABLE.

# CREATE TABLE Statement:

CREATE TABLE `qq`.`sbtest` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`k` int(10) unsigned NOT NULL,

`c` char(360) NOT NULL,

`pad` char(180) NOT NULL,

PRIMARY KEY `PRIMARY` (`id`),

KEY `k` (`k`)

) ENGINE=InnoDB;

#...done.

mysqlfrm 是一个恢复性质的工具，用来读取.frm文件并从该文件中找到表定义数据生成CREATE语句。在大多数情况下，生成的CREATE语句用于在另一个服务器上创建表或进行诊断等。然而，有些功能是不保存在.frm文件中的，因此这些功能将被忽略的。如：

外键约束

自增长序列

mysqlfrm 有两种操作模式。默认的模式是再生个实例，使用--basedir选项或指定--server选项来连接到已经安装的实例。这种过程不会改变原始的.frm文件。该模式也需要指定--port选项来给再生的实例使用，该端口不能与现有的实例冲突。在读取.frm文件后，再生的实例将被关闭，所有的临时文件将被删除的。

另一个模式是诊断模式，需要指定 --diagnostic 选项。byte-by-byte读取.frm文件尽可能多的恢复信息。该模式有更多的局限性，不能校验字符集。

当使用默认模式无法读取文件或者该服务器上没有安装MySQL实例就使用诊断模式。

需要指定.frm文件的路径，也可以指定一个目录，该目录下的所有.frm文件将被读取。

可以指定数据库名称用来产生CREATE语句，格式如下：database_name:table_name.frm，如：oltp:t1.frm。可选的数据库名称也可以使用路径，如 /home/me/oltp:t1.frm，将使用oltp作为数据库名。如果省略了数据库名称和路径，最后的一个文件夹将作为数据库名称的。如 /home/me/data1/t1.frm，data1将作为数据库名。如果不想最后的文件夹作为数据库名，只需指定冒号，如 /home/me/data1/:t1.frm，在这种情况下，CREATE语句将忽略数据库。

选项

MySQL Utilities mysqlfrm version1.5.3

License type:GPLv2

Usage:mysqlfrm--server=[user[:]@host[:][:]|[:][:]][path\tbl1.frm|db:tbl.frm]

mysqlfrm-show CREATE TABLE from.frm files

Options:

--version show program's version number and exit

--license display program'slicense andexit

--help

--basedir=BASEDIR the base directory forthe server,默认模式

--diagnostic read the frm files byte-by-bytetoform the CREATE

statement.May require the--server or--basedir

options todecipher character set information

--new-storage-engine=NEW_ENGINE

change ENGINE clause tousethisengine.

--frmdir=FRMDIR save the new.frm files inthisdirectory.Used and

valid with--new-storage-engine only.

--port=PORT Port touseforthe spawned server.未使用的端口，默认模式

-s,--show-stats show file statistics andgeneral table information.

--server=SERVER connection information forthe server inthe form:

[:]@[:][:]or

[:][:](optional)-if

provided,the storage engine andcharacter set

information will be validated against thisserver.

--user=USER user account tolaunch spawned server.Required if

running asroot user.Used only inthe defaultmode.

--start-timeout=START_TIMEOUT

Number of seconds towait forspawned server tostart.

Default=10.

-v,--verbose control how much information isdisplayed.e.g.,-v=

verbose,-vv=more verbose,-vvv=debug

-q,--quiet turn off all messages forquiet execution.

--ssl-ca=SSL_CA The path toafile that containsalist of trusted SSL

CAs.

--ssl-cert=SSL_CERT The name of the SSL certificate file tousefor

establishingasecure connection.

--ssl-key=SSL_KEY The name of the SSL key file touseforestablishinga

secure connection.

注意

1. 某些引擎表在默认模式下不可读取的。如PARTITION, PERFORMANCE_SCHEMA，必需在诊断模式下可读。

2. 要在创建语句中改变存储引擎，可使用--new-storage-engine 选项。如果有指定该选项，同时必须指定--frmdir选项，该工具生成新的.frm文件，前缀为new_，并保存在--frmdir目录下。

3. 关掉所有信息除了CREATE 语句和警告或错误信息，使用--quiet选项。

4. 使用--show-stats 选项统计每个.frm文件信息。

5. 使用--user 选项指定再生的实例以哪个权限运行。

6. 如果再生的实例超过10秒启动，需调大--start-timeout 选项参数。

实例

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

# mysqlfrm --basedir=/usr ttlsa_com:data.frm --port=3333 --user=mysql -vvv

# Checking read access to .frm files

# Creating a temporary datadir = /data/mydata3306/ttlsa_com/5831a350-3c22-4869-a6d7-9c843fe51ac7

# Spawning server with --user=mysql.

# Starting the spawned server on port 3333 ...

# Cloning the MySQL server located at /usr.

# Configuring new instance...

# Locating mysql tools...

# Location of files:

# mysqld: /usr/sbin/mysqld

# mysqladmin: /usr/bin/mysqladmin

# mysql_system_tables.sql: /usr/share/mysql/mysql_system_tables.sql

# mysql_system_tables_data.sql: /usr/share/mysql/mysql_system_tables_data.sql

# mysql_test_data_timezone.sql: /usr/share/mysql/mysql_test_data_timezone.sql

# fill_help_tables.sql: /usr/share/mysql/fill_help_tables.sql

# Setting up empty database and mysql tables...

2015-01-2019:12:350[Warning]TIMESTAMP with implicit DEFAULTvalue isdeprecated.Please use--explicit_defaults_for_timestamp server option(see documentation formore details).

2015-01-2019:12:3518168[Note]Plugin'FEDERATED'isdisabled.

2015-01-2019:12:3518168[Note]InnoDB:Using atomics toref count buffer pool pages

2015-01-2019:12:3518168[Note]InnoDB:The InnoDB memory heap isdisabled

2015-01-2019:12:3518168[Note]InnoDB:Mutexes andrw_locks useGCC atomic builtins

2015-01-2019:12:3518168[Note]InnoDB:Memory barrier isnotused

2015-01-2019:12:3518168[Note]InnoDB:Compressed tables usezlib1.2.3

2015-01-2019:12:3518168[Note]InnoDB:Using Linux nativeAIO

2015-01-2019:12:3518168[Note]InnoDB:Using CPU crc32 instructions

2015-01-2019:12:3518168[Note]InnoDB:Initializing buffer pool,size=128.0M

2015-01-2019:12:3518168[Note]InnoDB:Completed initialization of buffer pool

2015-01-2019:12:3518168[Note]InnoDB:The first specified data file./ibdata1 did notexist:anewdatabase tobe created!

2015-01-2019:12:3518168[Note]InnoDB:Setting file./ibdata1 size to12MB

2015-01-2019:12:3518168[Note]InnoDB:Database physically writes the file full:wait...

2015-01-2019:12:3518168[Note]InnoDB:Setting log file./ib_logfile101 size to48MB

2015-01-2019:12:3618168[Note]InnoDB:Setting log file./ib_logfile1 size to48MB

2015-01-2019:12:3618168[Note]InnoDB:Renaming log file./ib_logfile101 to./ib_logfile0

2015-01-2019:12:3618168[Warning]InnoDB:Newlog files created,LSN=45781

2015-01-2019:12:3618168[Note]InnoDB:Doublewrite buffer notfound:creating new

2015-01-2019:12:3618168[Note]InnoDB:Doublewrite buffer created

2015-01-2019:12:3618168[Note]InnoDB:128rollback segment(s)are active.

2015-01-2019:12:3618168[Warning]InnoDB:Creating foreign key constraint system tables.

2015-01-2019:12:3618168[Note]InnoDB:Foreign key constraint system tables created

2015-01-2019:12:3618168[Note]InnoDB:Creating tablespace anddatafile system tables.

2015-01-2019:12:3618168[Note]InnoDB:Tablespace anddatafile system tables created.

2015-01-2019:12:3618168[Note]InnoDB:Waiting forpurge tostart

2015-01-2019:12:3618168[Note]InnoDB:5.6.22started;log sequence number0

2015-01-2019:12:3818168[Note]Binlog end

2015-01-2019:12:3818168[Note]InnoDB:FTS optimize thread exiting.

2015-01-2019:12:3818168[Note]InnoDB:Starting shutdown...

2015-01-2019:12:3918168[Note]InnoDB:Shutdown completed;log sequence number1625977

# Starting new instance of the server...

# Startup command for new server:

/usr/sbin/mysqld--no-defaults--datadir=/data/mydata3306/ttlsa_com/5831a350-3c22-4869-a6d7-9c843fe51ac7--tmpdir=/data/mydata3306/ttlsa_com/5831a350-3c22-4869-a6d7-9c843fe51ac7--pid-file=/data/mydata3306/ttlsa_com/5831a350-3c22-4869-a6d7-9c843fe51ac7/clone.pid--port=3333--server-id=101--basedir=/usr--socket=/data/mydata3306/ttlsa_com/5831a350-3c22-4869-a6d7-9c843fe51ac7/mysql.sock--user=mysql

# Testing connection to new instance...

2015-01-2019:12:400[Warning]TIMESTAMP with implicit DEFAULTvalue isdeprecated.Please use--explicit_defaults_for_timestamp server option(see documentation formore details).

2015-01-2019:12:4018193[Note]Plugin'FEDERATED'isdisabled.

2015-01-2019:12:4018193[Note]InnoDB:Using atomics toref count buffer pool pages

2015-01-2019:12:4018193[Note]InnoDB:The InnoDB memory heap isdisabled

2015-01-2019:12:4018193[Note]InnoDB:Mutexes andrw_locks useGCC atomic builtins

2015-01-2019:12:4018193[Note]InnoDB:Memory barrier isnotused

2015-01-2019:12:4018193[Note]InnoDB:Compressed tables usezlib1.2.3

2015-01-2019:12:4018193[Note]InnoDB:Using Linux nativeAIO

2015-01-2019:12:4018193[Note]InnoDB:Using CPU crc32 instructions

2015-01-2019:12:4018193[Note]InnoDB:Initializing buffer pool,size=128.0M

2015-01-2019:12:4018193[Note]InnoDB:Completed initialization of buffer pool

2015-01-2019:12:4018193[Note]InnoDB:Highest supported file format isBarracuda.

2015-01-2019:12:4018193[Note]InnoDB:128rollback segment(s)are active.

2015-01-2019:12:4018193[Note]InnoDB:Waiting forpurge tostart

2015-01-2019:12:4018193[Note]InnoDB:5.6.22started;log sequence number1625977

2015-01-2019:12:4018193[Warning]No existing UUID has been found,so we assume that thisisthe first time that thisserver has been started.GeneratinganewUUID:3f618744-a095-11e4-9f53-0050569f3bb8.

2015-01-2019:12:4018193[Note]Server hostname(bind-address):'*';port:3333

2015-01-2019:12:4018193[Note]IPv6 isavailable.

2015-01-2019:12:4018193[Note]-'::'resolves to'::';

2015-01-2019:12:4018193[Note]Server socket created on IP:'::'.

2015-01-2019:12:4018193[Note]Event Scheduler:Loaded0events

2015-01-2019:12:4018193[Note]/usr/sbin/mysqld:ready forconnections.

Version:'5.6.22'socket:'/data/mydata3306/ttlsa_com/5831a350-3c22-4869-a6d7-9c843fe51ac7/mysql.sock'port:3333MySQL Community Server(GPL)

# trying again...

# Success!

# Setting the root password...

Warning:Usingapassword on the command line interfacecan be insecure.

# Connection Information:

# -uroot -proot --socket=/data/mydata3306/ttlsa_com/5831a350-3c22-4869-a6d7-9c843fe51ac7/mysql.sock

#...done.

# Connecting to spawned server

done.

# Reading .frm files

# Reading the data.frm file.

# Changing engine for .frm file /data/mydata3306/ttlsa_com/5831a350-3c22-4869-a6d7-9c843fe51ac7/ttlsa_com_temp/data.frm:

# Skipping to header at : 2

# General Data from .frm file:

{'IO_SIZE':4096,

'MYSQL_VERSION_ID':50622,

'avg_row_length':0,

'charset_low':0,

'create_options':8,

'db_create_pack':2,

'default_charset':8,

'default_part_eng':0,

'extra_size':28,

'frm_file_ver':5,

'frm_version':9,

'key_block_size':0,

'key_info_length':53,

'key_length':706,

'legacy_db_type':'INNODB',

'length':12288,

'max_rows':0,

'min_rows':0,

'rec_length':39,

'row_type':0,

'table_charset':8,

'tmp_key_length':706}

# Engine string: InnoDB

# Server version in file: 5.6.22

# CREATE statement for data.frm:

CREATE TABLE`ttlsa_com`.`data`(

`id`int(11)NOTNULLAUTO_INCREMENT,

`value`char(30)NOTNULLDEFAULT'',

`count`int(11)DEFAULTNULL,

PRIMARY KEY(`value`),

KEY`id`(`id`)

)ENGINE=InnoDB DEFAULTCHARSET=latin1

# Shutting down spawned server

# Removing the temporary datadir

2015-01-2019:12:4018193[Note]/usr/sbin/mysqld:Normal shutdown

2015-01-2019:12:4018193[Note]Giving0client threadsachance todie gracefully

2015-01-2019:12:4018193[Note]Event Scheduler:Purging the queue.0events

2015-01-2019:12:4018193[Note]Shutting down slave threads

2015-01-2019:12:4018193[Note]Forcefully disconnecting0remaining clients

2015-01-2019:12:4018193[Note]Binlog end

2015-01-2019:12:4018193[Note]Shutting down plugin'partition'

2015-01-2019:12:4018193[Note]Shutting down plugin'BLACKHOLE'

2015-01-2019:12:4018193[Note]Shutting down plugin'ARCHIVE'

2015-01-2019:12:4018193[Note]Shutting down plugin'PERFORMANCE_SCHEMA'

2015-01-2019:12:4018193[Note]Shutting down plugin'INNODB_SYS_DATAFILES'

2015-01-2019:12:4018193[Note]Shutting down plugin'INNODB_SYS_TABLESPACES'

2015-01-2019:12:4018193[Note]Shutting down plugin'INNODB_SYS_FOREIGN_COLS'

2015-01-2019:12:4018193[Note]Shutting down plugin'INNODB_SYS_FOREIGN'

2015-01-2019:12:4018193[Note]Shutting down plugin'INNODB_SYS_FIELDS'

2015-01-2019:12:4018193[Note]Shutting down plugin'INNODB_SYS_COLUMNS'

2015-01-2019:12:4018193[Note]Shutting down plugin'INNODB_SYS_INDEXES'

2015-01-2019:12:4018193[Note]Shutting down plugin'INNODB_SYS_TABLESTATS'

2015-01-2019:12:4018193[Note]Shutting down plugin'INNODB_SYS_TABLES'

2015-01-2019:12:4018193[Note]Shutting down plugin'INNODB_FT_INDEX_TABLE'

2015-01-2019:12:4018193[Note]Shutting down plugin'INNODB_FT_INDEX_CACHE'

2015-01-2019:12:4018193[Note]Shutting down plugin'INNODB_FT_CONFIG'

2015-01-2019:12:4018193[Note]Shutting down plugin'INNODB_FT_BEING_DELETED'

2015-01-2019:12:4018193[Note]Shutting down plugin'INNODB_FT_DELETED'

2015-01-2019:12:4018193[Note]Shutting down plugin'INNODB_FT_DEFAULT_STOPWORD'

2015-01-2019:12:4018193[Note]Shutting down plugin'INNODB_METRICS'

2015-01-2019:12:4018193[Note]Shutting down plugin'INNODB_BUFFER_POOL_STATS'

2015-01-2019:12:4018193[Note]Shutting down plugin'INNODB_BUFFER_PAGE_LRU'

2015-01-2019:12:4018193[Note]Shutting down plugin'INNODB_BUFFER_PAGE'

2015-01-2019:12:4018193[Note]Shutting down plugin'INNODB_CMP_PER_INDEX_RESET'

2015-01-2019:12:4018193[Note]Shutting down plugin'INNODB_CMP_PER_INDEX'

2015-01-2019:12:4018193[Note]Shutting down plugin'INNODB_CMPMEM_RESET'

2015-01-2019:12:4018193[Note]Shutting down plugin'INNODB_CMPMEM'

2015-01-2019:12:4018193[Note]Shutting down plugin'INNODB_CMP_RESET'

2015-01-2019:12:4018193[Note]Shutting down plugin'INNODB_CMP'

2015-01-2019:12:4018193[Note]Shutting down plugin'INNODB_LOCK_WAITS'

2015-01-2019:12:4018193[Note]Shutting down plugin'INNODB_LOCKS'

2015-01-2019:12:4018193[Note]Shutting down plugin'INNODB_TRX'

2015-01-2019:12:4018193[Note]Shutting down plugin'InnoDB'

2015-01-2019:12:4018193[Note]InnoDB:FTS optimize thread exiting.

2015-01-2019:12:4018193[Note]InnoDB:Starting shutdown...

#...done.

2015-01-2019:12:4218193[Note]InnoDB:Shutdown completed;log sequence number1625987

2015-01-2019:12:4218193[Note]Shutting down plugin'CSV'

2015-01-2019:12:4218193[Note]Shutting down plugin'MEMORY'

2015-01-2019:12:4218193[Note]Shutting down plugin'MRG_MYISAM'

2015-01-2019:12:4218193[Note]Shutting down plugin'MyISAM'

2015-01-2019:12:4218193[Note]Shutting down plugin'sha256_password'

2015-01-2019:12:4218193[Note]Shutting down plugin'mysql_old_password'

2015-01-2019:12:4218193[Note]Shutting down plugin'mysql_native_password'

2015-01-2019:12:4218193[Note]Shutting down plugin'binlog'

2015-01-2019:12:4218193[Note]/usr/sbin/mysqld:Shutdown complete

看看执行过程反过头去看看上面所说是否正确。

# mysqlfrm --basedir=/usr --port=3333 --user=mysql /data/mydata3306/ttlsa_com/data.frm /data/mydata3306/mysql/bbs_ttlsa_com:user.frm

# Spawning server with --user=mysql.

# Starting the spawned server on port 3333 ... done.

# Reading .frm files

# Reading the user.frm file.

# CREATE statement for /data/mydata3306/mysql/user.frm:

CREATE TABLE`bbs_ttlsa_com`.`user`(

`Host`char(60)COLLATE utf8_bin NOTNULLDEFAULT'',

`User`char(16)COLLATE utf8_bin NOTNULLDEFAULT'',

`Password`char(41)CHARACTER SET latin1 COLLATE latin1_bin NOTNULLDEFAULT'',

`Select_priv`enum('N','Y')CHARACTER SET utf8 NOTNULLDEFAULT'N',

`Insert_priv`enum('N','Y')CHARACTER SET utf8 NOTNULLDEFAULT'N',

`Update_priv`enum('N','Y')CHARACTER SET utf8 NOTNULLDEFAULT'N',

`Delete_priv`enum('N','Y')CHARACTER SET utf8 NOTNULLDEFAULT'N',

`Create_priv`enum('N','Y')CHARACTER SET utf8 NOTNULLDEFAULT'N',

`Drop_priv`enum('N','Y')CHARACTER SET utf8 NOTNULLDEFAULT'N',

`Reload_priv`enum('N','Y')CHARACTER SET utf8 NOTNULLDEFAULT'N',

`Shutdown_priv`enum('N','Y')CHARACTER SET utf8 NOTNULLDEFAULT'N',

`Process_priv`enum('N','Y')CHARACTER SET utf8 NOTNULLDEFAULT'N',

`File_priv`enum('N','Y')CHARACTER SET utf8 NOTNULLDEFAULT'N',

`Grant_priv`enum('N','Y')CHARACTER SET utf8 NOTNULLDEFAULT'N',

`References_priv`enum('N','Y')CHARACTER SET utf8 NOTNULLDEFAULT'N',

`Index_priv`enum('N','Y')CHARACTER SET utf8 NOTNULLDEFAULT'N',

`Alter_priv`enum('N','Y')CHARACTER SET utf8 NOTNULLDEFAULT'N',

`Show_db_priv`enum('N','Y')CHARACTER SET utf8 NOTNULLDEFAULT'N',

`Super_priv`enum('N','Y')CHARACTER SET utf8 NOTNULLDEFAULT'N',

`Create_tmp_table_priv`enum('N','Y')CHARACTER SET utf8 NOTNULLDEFAULT'N',

`Lock_tables_priv`enum('N','Y')CHARACTER SET utf8 NOTNULLDEFAULT'N',

`Execute_priv`enum('N','Y')CHARACTER SET utf8 NOTNULLDEFAULT'N',

`Repl_slave_priv`enum('N','Y')CHARACTER SET utf8 NOTNULLDEFAULT'N',

`Repl_client_priv`enum('N','Y')CHARACTER SET utf8 NOTNULLDEFAULT'N',

`Create_view_priv`enum('N','Y')CHARACTER SET utf8 NOTNULLDEFAULT'N',

`Show_view_priv`enum('N','Y')CHARACTER SET utf8 NOTNULLDEFAULT'N',

`Create_routine_priv`enum('N','Y')CHARACTER SET utf8 NOTNULLDEFAULT'N',

`Alter_routine_priv`enum('N','Y')CHARACTER SET utf8 NOTNULLDEFAULT'N',

`Create_user_priv`enum('N','Y')CHARACTER SET utf8 NOTNULLDEFAULT'N',

`Event_priv`enum('N','Y')CHARACTER SET utf8 NOTNULLDEFAULT'N',

`Trigger_priv`enum('N','Y')CHARACTER SET utf8 NOTNULLDEFAULT'N',

`Create_tablespace_priv`enum('N','Y')CHARACTER SET utf8 NOTNULLDEFAULT'N',

`ssl_type`enum('','ANY','X509','SPECIFIED')CHARACTER SET utf8 NOTNULLDEFAULT'',

`ssl_cipher`blob NOTNULL,

`x509_issuer`blob NOTNULL,

`x509_subject`blob NOTNULL,

`max_questions`int(11)unsignedNOTNULLDEFAULT'0',

`max_updates`int(11)unsignedNOTNULLDEFAULT'0',

`max_connections`int(11)unsignedNOTNULLDEFAULT'0',

`max_user_connections`int(11)unsignedNOTNULLDEFAULT'0',

`plugin`char(64)COLLATE utf8_bin DEFAULT'',

`authentication_string`text COLLATE utf8_bin,

`password_expired`enum('N','Y')CHARACTER SET utf8 NOTNULLDEFAULT'N',

PRIMARY KEY(`Host`,`User`)

)ENGINE=MyISAM DEFAULTCHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'

# Reading the data.frm file.

# CREATE statement for /data/mydata3306/ttlsa_com/data.frm:

CREATE TABLE`ttlsa_com`.`data`(

`id`int(11)NOTNULLAUTO_INCREMENT,

`value`char(30)NOTNULLDEFAULT'',

`count`int(11)DEFAULTNULL,

PRIMARY KEY(`value`),

KEY`id`(`id`)

)ENGINE=InnoDB DEFAULTCHARSET=latin1

#...done.

/data/mydata3306/ttlsa_com/data.frm 数据库名为ttlsa_com;

/data/mydata3306/mysql/bbs_ttlsa_com:user.frm 数据库名为bbs_ttlsa_com。

以诊断模式读取目录中的所有的FRM文件：

# mysqlfrm --diagnostic /data/mydata3306/ttlsa_com

# WARNING: Cannot generate character set or collation names without the --server option.

# Reading .frm file for /data/mydata3306/ttlsa_com/bbs_categories.frm:

# The .frm file is a TABLE.

# CREATE TABLE Statement:

# Reading .frm file for /data/mydata3306/ttlsa_com/data.frm:

# The .frm file is a TABLE.

# CREATE TABLE Statement:

CREATE TABLE`ttlsa_com`.`data`(

`id`int(11)NOTNULLAUTO_INCREMENT,

`value`char(30)NOTNULL,

`count`int(11)DEFAULTNULL,

PRIMARY KEY`PRIMARY`(`value`),

KEY`id`(`id`)

)ENGINE=InnoDB;

#...done.

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

# mysqlfrm --diagnostic /data/mydata3306/ttlsa_com/data.frm -vvv

# WARNING: Cannot generate character set or collation names without the --server option.

# Reading .frm file for /data/mydata3306/ttlsa_com/data.frm:

# The .frm file is a TABLE.

# Skipping to header at : 2

# General Data from .frm file:

{'IO_SIZE':4096,

'MYSQL_VERSION_ID':50622,

'avg_row_length':0,

'charset_low':0,

'create_options':8,

'db_create_pack':2,

'default_charset':8,

'default_part_eng':0,

'extra_size':28,

'frm_file_ver':5,

'frm_version':9,

'key_block_size':0,

'key_info_length':53,

'key_length':706,

'legacy_db_type':'INNODB',

'length':12288,

'max_rows':0,

'min_rows':0,

'rec_length':39,

'row_type':0,

'table_charset':8,

'tmp_key_length':706}

# Skipping to key data at : 1000

# Reading key part 0.

# Index (key) Data from .frm file:

{'key_names':['PRIMARY','id'],

'keys':[{'algorithm':0,

'block_size':0,

'comment':'',

'flags':2,

'key_length':30,

'key_parts':[{'field_num':2,

'key_part_flag':0,

'key_type':0,

'length':30,

'offset':5}],

'num_parts':1},

{'algorithm':0,

'block_size':0,

'comment':'',

'flags':1,

'key_length':4,

'key_parts':[{'field_num':1,

'key_part_flag':0,

'key_type':6912,

'length':4,

'offset':1}],

'num_parts':1}],

'num_key_parts':(2,),

'num_keys':2}

# Skipping to default data at : 12c3

# Skipping to keys at : 12eb

# Engine string: InnoDB

# Partition string:

# Skipping to column data at : 2102

{'com_length':0,

'int_length':0,

'interval_count':39,

'interval_parts':17,

'n_length':0,

'null_fields':1,

'num_cols':3,

'pos':72,

'unknown':52}

# Fields per screen = 3

# Reading column metadata #0

# Reading column metadata #1

# Reading column metadata #2

# Column comment: 0

# Column Data from .frm file:

[{'bytes_in_col':11,

'charset':8,

'charset_low':0,

'comment':'',

'comment_length':0,

'default':None,

'enums':[],

'field_length':11,

'field_type':3,

'field_type_name':'int',

'flags':27,

'flags_extra':0,

'interval_nr':0,

'name':'id',

'recpos':2,

'unireg':0,

'unireg_type':15},

{'bytes_in_col':30,

'charset':8,

'charset_low':0,

'comment':'',

'comment_length':0,

'default':None,

'enums':[],

'field_length':30,

'field_type':254,

'field_type_name':'char',

'flags':0,

'flags_extra':0,

'interval_nr':0,

'name':'value',

'recpos':6,

'unireg':0,

'unireg_type':0},

{'bytes_in_col':11,

'charset':8,

'charset_low':0,

'comment':'',

'comment_length':0,

'default':None,

'enums':[],

'field_length':11,

'field_type':3,

'field_type_name':'int',

'flags':27,

'flags_extra':128,

'interval_nr':0,

'name':'count',

'recpos':36,

'unireg':0,

'unireg_type':0}]

# Number of columns: 3

[{'bytes_in_col':30,

'charset':8,

'charset_low':0,

'comment':'',

'comment_length':0,

'default':None,

'enums':[],

'field_length':30,

'field_type':254,

'field_type_name':'char',

'flags':0,

'flags_extra':0,

'interval_nr':0,

'name':'value',

'recpos':6,

'unireg':0,

'unireg_type':0},

{'bytes_in_col':11,

'charset':8,

'charset_low':0,

'comment':'',

'comment_length':0,

'default':None,

'enums':[],

'field_length':11,

'field_type':3,

'field_type_name':'int',

'flags':27,

'flags_extra':128,

'interval_nr':0,

'name':'count',

'recpos':36,

'unireg':0,

'unireg_type':0}]

# Skipping to table comments at : 202e

# Comment:

# CREATE TABLE Statement:

CREATE TABLE`ttlsa_com`.`data`(

`id`int(11)NOTNULLAUTO_INCREMENT,

`value`char(30)NOTNULL,

`count`int(11)DEFAULTNULL,

PRIMARY KEY`PRIMARY`(`value`),

KEY`id`(`id`)

)ENGINE=InnoDB;

#...done.

权限

权限取决于如何使用mysqlfrm。统一用root账号就好了。

如果要连接服务器，需要对mysql数据库读权限。

具体详细可以参考：http://www.ttlsa.com/mysql/mysql-utilities-mysqlfrm/

漫姐

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql frm ibd 创建表_通过.frm表结构和.ibd文件恢复数据

整个恢复过程其实可以总结为下面几步：(1)：恢复表结构(2)：复制出来创建表的sql语句(3)：恢复表数据(在恢复表数据的时候，首先需要解除当前创建的表与默认生成的.ibd文件间的关系，接着将要恢复数据表的.ibd文件与当前创建的表联系起来即可)1.通过官网下载mysql-utilities-1.6.5.tar.gz(https://downloads.mysql.com/archives/uti...
复制链接

扫一扫