【Mysql】Mysql從frm檔案裡恢復表結構

小亮520cl發表於2016-05-04
MySql
磁碟損壞，只剩下frm以及ibd檔案了，如何從frm恢復表結構呢？
使用mysqlfrm 去官網http://dev.mysql.com/downloads/utilities/  下載mysql-connector-python 以及mysql-utilities工具

mysqlfrm 是一個恢復性質的工具，用來讀取.frm檔案並從該檔案中找到表定義資料生成CREATE語句。在大多數情況下，生成的CREATE語句用於在另一個伺服器上建立表或進行診斷等。然而，有些功能是不儲存在.frm檔案中的，因此這些功能將被忽略的。如：

  • 外來鍵約束
  • 自增長序列

mysqlfrm 有兩種操作模式。

  1. 預設的模式是再生個例項，使用--basedir選項或指定--server選項來連線到已經安裝的例項。這種過程不會改變原始的.frm檔案。該模式也需要指定--port選項來給再生的例項使用，該埠不能與現有的例項衝突。在讀取.frm檔案後，再生的例項將被關閉，所有的臨時檔案將被刪除的。
  2. 另一個模式是診斷模式，需要指定 --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語句將忽略資料庫。

 

注意

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 選項引數。



例子：
  1. [root@sh242 ~]# mysqlfrm --basedir=/home/data/mysql  /home/data/mydata/3307/test/DB2:b_orders.frm --port=3307  --user=root
    # Spawning server with --user=root.
    # Starting the spawned server on port 3307 ... done.
    # Reading .frm files
    #
    # Reading the b_orders.frm file.
    #
    # CREATE statement for /home/data/mydata/3307/test/b_orders.frm:
    #


    CREATE TABLE `DB2`.`b_orders` (
      `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      `c_id` bigint(20) unsigned DEFAULT '0' COMMENT 'C端訂單id',
      `username` char(32) NOT NULL COMMENT '姓名',
      `mobile` char(32) NOT NULL COMMENT '手機號碼',
      `iden_card` varchar(20) DEFAULT NULL COMMENT '身份證號',
      `birth` char(6) DEFAULT NULL COMMENT '格式：198005',
      `zone_id` int(10) unsigned DEFAULT NULL COMMENT '城市ID',
      `gsd_zone_id` int(10) unsigned NOT NULL COMMENT '歸屬地',
      `money` decimal(10,2) unsigned DEFAULT NULL COMMENT '申請額度',
      `month` smallint(5) unsigned DEFAULT NULL COMMENT '貸款期限',
      `use_company` tinyint(2) DEFAULT NULL COMMENT '貸款用途/企業型別(1-12/1-9)',
      `marriage` tinyint(1) DEFAULT NULL COMMENT '婚姻狀況(1未婚,2已婚)',
      `credit_card` tinyint(1) DEFAULT NULL COMMENT '信用卡(1無,2有)',
      `house_type` tinyint(2) DEFAULT NULL COMMENT '房產(1-6)',
      `car_type` tinyint(1) DEFAULT NULL COMMENT '車產(1無車產,2有車產無抵押,3有車產已抵押)',
      `profession` tinyint(1) DEFAULT NULL COMMENT '職業身份(1-5)',
      `work_license` int(10) unsigned DEFAULT NULL COMMENT '工作時間/營業執照(1-4/1-5))',
      `salary_bank_private` int(10) DEFAULT NULL COMMENT '工資發放形式/銀行對私流水(1-4/1-10)',
      `salary_bank_public` int(10) DEFAULT NULL COMMENT '工資收入/銀行對公流水(1-11/1-10)',
      `is_fund` tinyint(1) DEFAULT NULL COMMENT '公積金(1無2有)',
      `is_security` tinyint(1) DEFAULT NULL COMMENT '社保(1無2有)',
      `type` tinyint(3) unsigned DEFAULT '0' COMMENT '1個人2企業',
      `industry` tinyint(3) DEFAULT NULL COMMENT '公司行業(1-18)',
      `has_loan` tinyint(1) DEFAULT NULL COMMENT '有無貸款成功記錄(1無2有)',
      `has_debt` tinyint(1) DEFAULT NULL COMMENT '負債情況(1無，2有)',
      `issue` tinyint(3) unsigned DEFAULT '0' COMMENT '貸款人問題',
      `iscomment` varchar(100) DEFAULT '0' COMMENT '評價內容',
      `com_type` tinyint(1) DEFAULT '0' COMMENT '是否已未點評推送',
      `xdy_id` bigint(20) unsigned DEFAULT '0' COMMENT '購買信貸員ID',
      `c_time` int(11) DEFAULT '0' COMMENT '購買時間',
      `u_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
      `status` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '處理狀態：0,未處理；1，電聯；2，備註  ',
      `del_status` tinyint(3) NOT NULL DEFAULT '1' COMMENT '資料狀態 1為正常 2為物理刪除',
      `letter` char(1) DEFAULT NULL COMMENT '姓名首字母',
      `b_c_type` tinyint(3) unsigned DEFAULT '1' COMMENT '1 c 2 b',
      PRIMARY KEY (`id`),
      UNIQUE KEY `c_id` (`c_id`) USING BTREE,
      UNIQUE KEY `mobile_xdy_type` (`mobile`,`xdy_id`,`b_c_type`),
      KEY `xdy_id` (`xdy_id`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='搶單表'


    #...done.

更詳細 -vvv
[root@sh242 ~]# mysqlfrm --basedir=/home/data/mysql  /home/data/mydata/3307/test/DB2:b_orders.frm --port=3307  --user=root -vvv
# Checking read access to .frm files 
# Creating a temporary datadir = /tmp/tmpZZNej7
# Spawning server with --user=root.
# Starting the spawned server on port 3307 ...
# Cloning the MySQL server located at /home/data/mysql.
# Configuring new instance...
# Locating mysql tools...
# Location of files:
#                       mysqld: /home/data/mysql/bin/mysqld
#                   mysqladmin: /home/data/mysql/bin/mysqladmin
#      mysql_system_tables.sql: /home/data/mysql/share/mysql_system_tables.sql
# mysql_system_tables_data.sql: /home/data/mysql/share/mysql_system_tables_data.sql
# mysql_test_data_timezone.sql: /home/data/mysql/share/mysql_test_data_timezone.sql
#         fill_help_tables.sql: /home/data/mysql/share/fill_help_tables.sql
# Setting up empty database and mysql tables...
2016-05-05 10:52:18 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-05-05 10:52:18 0 [Note] /home/data/mysql/bin/mysqld (mysqld 5.6.24) starting as process 20121 ...
2016-05-05 10:52:18 20121 [Note] Plugin 'FEDERATED' is disabled.
2016-05-05 10:52:18 20121 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-05-05 10:52:18 20121 [Note] InnoDB: The InnoDB memory heap is disabled
2016-05-05 10:52:18 20121 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-05-05 10:52:18 20121 [Note] InnoDB: Memory barrier is not used
2016-05-05 10:52:18 20121 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-05-05 10:52:18 20121 [Note] InnoDB: Using Linux native AIO
2016-05-05 10:52:18 20121 [Note] InnoDB: Using CPU crc32 instructions
2016-05-05 10:52:18 20121 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2016-05-05 10:52:18 20121 [Note] InnoDB: Completed initialization of buffer pool
2016-05-05 10:52:18 20121 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
2016-05-05 10:52:18 20121 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB
2016-05-05 10:52:18 20121 [Note] InnoDB: Database physically writes the file full: wait...
2016-05-05 10:52:18 20121 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
2016-05-05 10:52:18 20121 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
2016-05-05 10:52:19 20121 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2016-05-05 10:52:19 20121 [Warning] InnoDB: New log files created, LSN=45781
2016-05-05 10:52:19 20121 [Note] InnoDB: Doublewrite buffer not found: creating new
2016-05-05 10:52:19 20121 [Note] InnoDB: Doublewrite buffer created
2016-05-05 10:52:19 20121 [Note] InnoDB: 128 rollback segment(s) are active.
2016-05-05 10:52:19 20121 [Warning] InnoDB: Creating foreign key constraint system tables.
2016-05-05 10:52:19 20121 [Note] InnoDB: Foreign key constraint system tables created
2016-05-05 10:52:19 20121 [Note] InnoDB: Creating tablespace and datafile system tables.
2016-05-05 10:52:19 20121 [Note] InnoDB: Tablespace and datafile system tables created.
2016-05-05 10:52:19 20121 [Note] InnoDB: Waiting for purge to start
2016-05-05 10:52:19 20121 [Note] InnoDB: 5.6.24 started; log sequence number 0
2016-05-05 10:52:19 20121 [Note] Binlog end
2016-05-05 10:52:19 20121 [Note] InnoDB: FTS optimize thread exiting.
2016-05-05 10:52:19 20121 [Note] InnoDB: Starting shutdown...
2016-05-05 10:52:21 20121 [Note] InnoDB: Shutdown completed; log sequence number 1625977
# Starting new instance of the server...
# Startup command for new server:
/home/data/mysql/bin/mysqld --no-defaults --basedir=/home/data/mysql --datadir=/tmp/tmpZZNej7 --pid-file=/tmp/tmpZZNej7/clone.pid --port=3307 --server-id=101 --socket=/tmp/tmpZZNej7/mysql.sock --tmpdir=/tmp/tmpZZNej7 --user=root
# Testing connection to new instance...
# trying again...
2016-05-05 10:52:22 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-05-05 10:52:22 0 [Note] /home/data/mysql/bin/mysqld (mysqld 5.6.24) starting as process 20143 ...
2016-05-05 10:52:22 20143 [Note] Plugin 'FEDERATED' is disabled.
2016-05-05 10:52:22 20143 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-05-05 10:52:22 20143 [Note] InnoDB: The InnoDB memory heap is disabled
2016-05-05 10:52:22 20143 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-05-05 10:52:22 20143 [Note] InnoDB: Memory barrier is not used
2016-05-05 10:52:22 20143 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-05-05 10:52:22 20143 [Note] InnoDB: Using Linux native AIO
2016-05-05 10:52:22 20143 [Note] InnoDB: Using CPU crc32 instructions
2016-05-05 10:52:22 20143 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2016-05-05 10:52:22 20143 [Note] InnoDB: Completed initialization of buffer pool
2016-05-05 10:52:22 20143 [Note] InnoDB: Highest supported file format is Barracuda.
2016-05-05 10:52:22 20143 [Note] InnoDB: 128 rollback segment(s) are active.
2016-05-05 10:52:22 20143 [Note] InnoDB: Waiting for purge to start
2016-05-05 10:52:22 20143 [Note] InnoDB: 5.6.24 started; log sequence number 1625977
2016-05-05 10:52:23 20143 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 644e3d5d-126c-11e6-8afe-00163e5587cf.
2016-05-05 10:52:23 20143 [Note] Server hostname (bind-address): '*'; port: 3307
2016-05-05 10:52:23 20143 [Note] IPv6 is not available.
2016-05-05 10:52:23 20143 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
2016-05-05 10:52:23 20143 [Note] Server socket created on IP: '0.0.0.0'.
2016-05-05 10:52:23 20143 [Note] Event Scheduler: Loaded 0 events
2016-05-05 10:52:23 20143 [Note] /home/data/mysql/bin/mysqld: ready for connections.
Version: '5.6.24'  socket: '/tmp/tmpZZNej7/mysql.sock'  port: 3307  Source distribution
# trying again...
# Success!
# Setting the root password...
Warning: Using a password on the command line interface can be insecure.
# Connection Information:
#  -uroot -proot --socket=/tmp/tmpZZNej7/mysql.sock
#...done.
# Connecting to spawned server
done.
# Reading .frm files
#
# Reading the b_orders.frm file.
# Changing engine for .frm file /tmp/tmpZZNej7/DB2_temp/b_orders.frm:
# Skipping to header at : 2
# General Data from .frm file:
{'IO_SIZE': 4096,
 'MYSQL_VERSION_ID': 50624,
 'avg_row_length': 0,
 'charset_low': 0,
 'create_options': 9,
 'db_create_pack': 2,
 'default_charset': 33,
 'default_part_eng': 0,
 'extra_size': 60,
 'frm_file_ver': 5,
 'frm_version': 10,
 'key_block_size': 0,
 'key_info_length': 130,
 'key_length': 1396,
 'legacy_db_type': 'INNODB',
 'length': 12288,
 'max_rows': 0,
 'min_rows': 0,
 'rec_length': 656,
 'row_type': 0,
 'table_charset': 33,
 'tmp_key_length': 1396}
# Engine string: InnoDB
# Server version in file: 5.6.24
#
# CREATE statement for /home/data/mydata/3307/test/b_orders.frm:
#


CREATE TABLE `DB2`.`b_orders` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `c_id` bigint(20) unsigned DEFAULT '0' COMMENT 'C端訂單id',
  `username` char(32) NOT NULL COMMENT '姓名',
  `mobile` char(32) NOT NULL COMMENT '手機號碼',
  `iden_card` varchar(20) DEFAULT NULL COMMENT '身份證號',
  `birth` char(6) DEFAULT NULL COMMENT '格式：198005',
  `zone_id` int(10) unsigned DEFAULT NULL COMMENT '城市ID',
  `gsd_zone_id` int(10) unsigned NOT NULL COMMENT '歸屬地',
  `money` decimal(10,2) unsigned DEFAULT NULL COMMENT '申請額度',
  `month` smallint(5) unsigned DEFAULT NULL COMMENT '貸款期限',
  `use_company` tinyint(2) DEFAULT NULL COMMENT '貸款用途/企業型別(1-12/1-9)',
  `marriage` tinyint(1) DEFAULT NULL COMMENT '婚姻狀況(1未婚,2已婚)',
  `credit_card` tinyint(1) DEFAULT NULL COMMENT '信用卡(1無,2有)',
  `house_type` tinyint(2) DEFAULT NULL COMMENT '房產(1-6)',
  `car_type` tinyint(1) DEFAULT NULL COMMENT '車產(1無車產,2有車產無抵押,3有車產已抵押)',
  `profession` tinyint(1) DEFAULT NULL COMMENT '職業身份(1-5)',
  `work_license` int(10) unsigned DEFAULT NULL COMMENT '工作時間/營業執照(1-4/1-5))',
  `salary_bank_private` int(10) DEFAULT NULL COMMENT '工資發放形式/銀行對私流水(1-4/1-10)',
  `salary_bank_public` int(10) DEFAULT NULL COMMENT '工資收入/銀行對公流水(1-11/1-10)',
  `is_fund` tinyint(1) DEFAULT NULL COMMENT '公積金(1無2有)',
  `is_security` tinyint(1) DEFAULT NULL COMMENT '社保(1無2有)',
  `type` tinyint(3) unsigned DEFAULT '0' COMMENT '1個人2企業',
  `industry` tinyint(3) DEFAULT NULL COMMENT '公司行業(1-18)',
  `has_loan` tinyint(1) DEFAULT NULL COMMENT '有無貸款成功記錄(1無2有)',
  `has_debt` tinyint(1) DEFAULT NULL COMMENT '負債情況(1無，2有)',
  `issue` tinyint(3) unsigned DEFAULT '0' COMMENT '貸款人問題',
  `iscomment` varchar(100) DEFAULT '0' COMMENT '評價內容',
  `com_type` tinyint(1) DEFAULT '0' COMMENT '是否已未點評推送',
  `xdy_id` bigint(20) unsigned DEFAULT '0' COMMENT '購買信貸員ID',
  `c_time` int(11) DEFAULT '0' COMMENT '購買時間',
  `u_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
  `status` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '處理狀態：0,未處理；1，電聯；2，備註  ',
  `del_status` tinyint(3) NOT NULL DEFAULT '1' COMMENT '資料狀態 1為正常 2為物理刪除',
  `letter` char(1) DEFAULT NULL COMMENT '姓名首字母',
  `b_c_type` tinyint(3) unsigned DEFAULT '1' COMMENT '1 c 2 b',
  PRIMARY KEY (`id`),
  UNIQUE KEY `c_id` (`c_id`) USING BTREE,
  UNIQUE KEY `mobile_xdy_type` (`mobile`,`xdy_id`,`b_c_type`),
  KEY `xdy_id` (`xdy_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='搶單表'


# Shutting down spawned server
# Removing the temporary datadir
2016-05-05 10:52:24 20143 [Note] /home/data/mysql/bin/mysqld: Normal shutdown


#...done.


診斷模式：
[root@sh242 ~]# mysqlfrm --diagnostic /home/data/mydata/3307/test/b_orders.frm  【-vvv】
# 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 /home/data/mydata/3307/test/b_orders.frm:
# The .frm file is a TABLE.
# CREATE TABLE Statement:


CREATE TABLE `test`.`b_orders` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 
  `c_id` bigint(20) unsigned DEFAULT NULL comment 'C端訂單id', 
  `username` char(96) NOT NULL comment '姓名', 
  `mobile` char(96) NOT NULL comment '手機號碼', 
  `iden_card` varchar(60) DEFAULT NULL comment '身份證號', 
  `birth` char(18) DEFAULT NULL comment '格式：198005', 
  `zone_id` int(10) unsigned DEFAULT NULL comment '城市ID', 
  `gsd_zone_id` int(10) unsigned NOT NULL comment '歸屬地', 
  `money` decimal(10,2) unsigned DEFAULT NULL comment '申請額度', 
  `month` smallint(5) unsigned DEFAULT NULL comment '貸款期限', 
  `use_company` tinyint(2) DEFAULT NULL comment '貸款用途/企業型別(1-12/1-9)', 
  `marriage` tinyint(1) DEFAULT NULL comment '婚姻狀況(1未婚,2已婚)', 
  `credit_card` tinyint(1) DEFAULT NULL comment '信用卡(1無,2有)', 
  `house_type` tinyint(2) DEFAULT NULL comment '房產(1-6)', 
  `car_type` tinyint(1) DEFAULT NULL comment '車產(1無車產,2有車產無抵押,3有車產已抵押)', 
  `profession` tinyint(1) DEFAULT NULL comment '職業身份(1-5)', 
  `work_license` int(10) unsigned DEFAULT NULL comment '工作時間/營業執照(1-4/1-5))', 
  `salary_bank_private` int(10) DEFAULT NULL comment '工資發放形式/銀行對私流水(1-4/1-10)', 
  `salary_bank_public` int(10) DEFAULT NULL comment '工資收入/銀行對公流水(1-11/1-10)', 
  `is_fund` tinyint(1) DEFAULT NULL comment '公積金(1無2有)', 
  `is_security` tinyint(1) DEFAULT NULL comment '社保(1無2有)', 
  `type` tinyint(3) unsigned DEFAULT NULL comment '1個人2企業', 
  `industry` tinyint(3) DEFAULT NULL comment '公司行業(1-18)', 
  `has_loan` tinyint(1) DEFAULT NULL comment '有無貸款成功記錄(1無2有)', 
  `has_debt` tinyint(1) DEFAULT NULL comment '負債情況(1無，2有)', 
  `issue` tinyint(3) unsigned DEFAULT NULL comment '貸款人問題', 
  `iscomment` varchar(300) DEFAULT NULL comment '評價內容', 
  `com_type` tinyint(1) DEFAULT NULL comment '是否已未點評推送', 
  `xdy_id` bigint(20) unsigned DEFAULT NULL comment '購買信貸員ID', 
  `c_time` int(11) DEFAULT NULL comment '購買時間', 
  `u_time` timestamp DEFAULT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP comment '更新時間', 
  `status` tinyint(3) unsigned NOT NULL comment '處理狀態：0,未處理；1，電聯；2，備註  ', 
  `del_status` tinyint(3) NOT NULL comment '資料狀態 1為正常 2為物理刪除', 
  `letter` char(3) DEFAULT NULL comment '姓名首字母', 
  `b_c_type` tinyint(3) unsigned DEFAULT NULL comment '1 c 2 b', 
PRIMARY KEY `PRIMARY` (`id`),
UNIQUE KEY `c_id` (`c_id`) USING BTREE,
UNIQUE KEY `mobile_xdy_type` (`mobile`,`xdy_id`,`b_c_type`),
KEY `xdy_id` (`xdy_id`) USING BTREE
) ENGINE=InnoDB COMMENT '搶單表';


#...done.

參考文件：


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29096438/viewspace-2093664/，如需轉載，請註明出處，否則將追究法律責任。

相關文章