Restore table from .frm and .ibd file?

Question

I've some ibd and frm files. Now I want to import those into mysql database. But its not working. I tried with mysqlfrm but it showing

C:\xampp\mysql\data\example>mysqlfrm --diagnostic -vvv users.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 users.frm:
# The .frm file is a TABLE.
# Skipping to header at : 2
# General Data from .frm file:
{'IO_SIZE': 86,
 'MYSQL_VERSION_ID': 100334,
 'avg_row_length': 0,
 'charset_low': 0,
 'create_options': 9,
 'db_create_pack': 2,
 'default_charset': 224,
 'default_part_eng': 0,
 'extra_size': 16,
 'frm_file_ver': 5,
 'frm_version': 10,
 'key_block_size': 0,
 'key_info_length': 33,
 'key_length': 505,
 'legacy_db_type': 'INNODB',
 'length': 12831,
 'max_rows': 0,
 'min_rows': 0,
 'rec_length': 11314,
 'row_type': 0,
 'table_charset': 224,
 'tmp_key_length': 505}
# Skipping to key data at : 56
# Reading key part 0.
# Index (key) Data from .frm file:
{'key_names': ['PRIMARY'],
 'keys': [{'algorithm': 0,
           'block_size': 0,
           'comment': '',
           'flags': 0,
           'key_length': 8,
           'key_parts': [{'field_num': 1,
                          'key_part_flag': 0,
                          'key_type': 16896,
                          'length': 8,
                          'offset': 2}],
           'num_parts': 1}],
 'num_key_parts': (1,),
 'num_keys': 1}
# Skipping to default data at : 250
# Skipping to keys at : 2e83
# Engine string: InnoDB
# Partition string:
# Skipping to column data at : 2fb4
{'com_length': 64512,
 'int_length': 0,
 'interval_count': 0,
 'interval_parts': 57352,
 'n_length': 3840,
 'null_fields': 0,
 'num_cols': 20,
 'pos': 3,
 'unknown': 16896}
# Fields per screen = 0
EXCEPTION: unpack requires a string argument of length 1
ERROR: Cannot read column data.

I installed mysqlfrm on windows 10. If anyone know how to solve this, Please let me know. that be a great help.
Thank you

Answer 1

Since you shared users.ibd and user.frm I took a shot and recovered the table on a Windows 11 machine. Actually, on "Ubuntu on Windows", so it will work on native Ubuntu.

First, get Undrop for InnoDB and compile it.

$ git clone https://github.com/twindb/undrop-for-innodb.git
$ cd undrop-for-innodb/
$ sudo apt-get update
$ sudo apt-get install gcc flex bison
$ make

Drop users.zip in the current directory - undrop-for-innodb. Unzip it.

unzip users.zip

Now let's recover a table schema from users.frm. I like dbsake tool. It's much better than mysqlfrm (sorry Oracle, that's true).

$ curl -s http://get.dbsake.net > dbsake
$ chmod u+x dbsake
$ ./dbsake frmdump users.frm > users.sql

Here's your schema:

$ cat users.sql
--
-- Table structure for table `users`
-- Created with MySQL Version 10.3.34
--

CREATE TABLE `users` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `token` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `balance` double NOT NULL,
  `admin` tinyint(1) NOT NULL,
  `ref_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `referral` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `notification` tinyint(1) NOT NULL,
  `referral_total` double NOT NULL,
  `life_balance` double DEFAULT NULL,
  `refer_by` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `notice_type` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `notice_message` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `coupon` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `total_spent` double DEFAULT NULL,
  `total_order` double DEFAULT NULL,
  `total_refund` double DEFAULT NULL,
  `expire_ref` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '90',
  `ref_created` timestamp DEFAULT NULL,
  `created_at` timestamp DEFAULT NULL,
  `updated_at` timestamp DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Now, let's fetch records from users.ibd. First step would be to parse the ibd file and sort InnoDB pages in it.

./stream_parser -f users.ibd

The stream_parser creates pages-users.ibd/FIL_PAGE_INDEX/0000000000000367.page. This is a file with InnoDB pages of the PRIMARY index of the users table. Basically, this is where the table records are.

Second step is to fetch records from 0000000000000367.page.

$ mkdir -p dumps/default
$ ./c_parser -6f pages-users.ibd/FIL_PAGE_INDEX/0000000000000367.page -t users.sql 2> load.sql > dumps/default/users

The command saves records in a file dumps/default/users

$ head dumps/default/users
-- Page id: 3, Format: COMPACT, Records list: Valid, Expected records: (3 3)
-- Page id: 3, Found records: 0, Lost records: YES, Leaf page: NO
-- Page id: 4, Format: COMPACT, Records list: Valid, Expected records: (27 27)
000000000000    80000000000000  users   1       "support"       "admin@saveneur.com"    "8c6976e5b5410415bde908bd4dee15dfb167a9c873fc4bb8a81f6f2ab448a918"      49939.250000    1       "admin"    "f7315c0bbe5793a0182599b22932d8a77848ab9a0b06e30c37f8be74d058f1c4"      "none"  0       0.000000        56503.250000    NULL    NULL    NULL    ""      8148.250000     1315.00000NULL     "90"    NULL    NULL    "2023-01-10 14:40:02"
000000000000    80000000000000  users   61      "deliveredswiftly@gmail.com"    "deliveredswiftly@gmail.com"    "3c469e9d6c5875d37a43f353d4f88e61fcf812c66eee3457465a40b0da4153e0"      6.500000   0       "65d90fc6d307590b14e9e1800d4e8eab"      "8aaf6d0ff6e5b015c56fa47060b11d877cc6f7e09d6499f161203ccbe4f3056f"      "none"  0       0.000000        110.000000      "none"  """"       "Delivered1"    8455.500000     1088.000000     NULL    "90"    NULL    "2022-09-12 06:03:08"   "2023-01-10 03:34:07"
000000000000    80000000000000  users   62      "Amander11"     "butterphlys11@hotmail.com"     "3c469e9d6c5875d37a43f353d4f88e61fcf812c66eee3457465a40b0da4153e0"      0.000000        0 "3f9e3767ef3b10a0de4c256d7ef9805d"       "1b9588d1fe5cd0ffc3a126c0e53fca995c44bc2884de5c55ad323a9369087951"      "none"  0       0.000000        NULL    "none"  NULL    NULL    NULL    NULL       NULL    NULL    "90"    NULL    "2022-09-21 04:47:31"   "2022-09-21 04:47:31"
000000000000    80000000000000  users   63      "Mrbargainbeast"        "mrbargainbeast@gmail.com"      "3c469e9d6c5875d37a43f353d4f88e61fcf812c66eee3457465a40b0da4153e0"      0.000000  "2b38c2df6a49b97f706ec9148ce48d86"       "75530007195799a0d4569a68ff833792039b526cdeb8eb99b05b1c1284e74d6f"      "none"  0       0.000000        NULL    "none"  NULL    NULL    NULL    NULL       NULL    NULL    "90"    NULL    "2022-09-21 04:48:38"   "2022-09-21 04:48:38"
000000000000    80000000000000  users   64      "partsofus"     "info@partsofus.com"    "3c469e9d6c5875d37a43f353d4f88e61fcf812c66eee3457465a40b0da4153e0"      0.000000        0       "05ae14d7ae387b93370d142d82220f1b" "36512164c97c2f83df9ccbb395b8446727992fd1482ef24ae369c85d6b890670"      "none"  0       0.000000        NULL    "none"  NULL    NULL    NULL    NULL    NULL       NULL    "90"    NULL    "2022-09-21 06:33:55"   "2022-09-21 06:33:55"
000000000000    80000000000000  users   65      "Finditforu"    "finditforu@gmail.com"  "3c469e9d6c5875d37a43f353d4f88e61fcf812c66eee3457465a40b0da4153e0"      0.000000        0       "2122c699d5e3d2fa6690771845bd7904" "6e05e801665a5bc864dc9b59154f67a3fcb92283947592a243361afd4a2427c0"      "none"  0       0.000000        NULL    "none"  NULL    NULL    NULL    NULL    NULL       NULL    "90"    NULL    "2022-09-21 06:52:00"   "2022-09-21 06:52:00"
000000000000    80000000000000  users   66      "VibrantReturns"        "Vibrantreturns@protonmail.com" "3c469e9d6c5875d37a43f353d4f88e61fcf812c66eee3457465a40b0da4153e0"      0.000000  "0c2bcf2ee48c024117f6a057105ead45"       "be1d510e75e07e39d0874d2a971bb61bc1d218288b699289db6b3a7299fdb1c4"      "none"  0       0.000000        NULL    "none"  NULL    NULL    NULL    NULL       NULL    NULL    "90"    NULL    "2022-09-21 17:19:56"   "2022-09-21 17:19:56"

To load this file into a MySQL instance c_parser also generates a helper LOAD statement which I saved in load.sql

$ cat load.sql
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/home/aleks/undrop-for-innodb/dumps/default/users' REPLACE INTO TABLE `users` CHARACTER SET UTF8 FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'users\t' (`id`, `username`, `email`, `token`, `balance`, `admin`, `ref_id`, `password`, `referral`, `notification`, `referral_total`, `life_balance`, `refer_by`, `notice_type`, `notice_message`, `coupon`, `total_spent`, `total_order`, `total_refund`, `expire_ref`, `ref_created`, `created_at`, `updated_at`);
-- STATUS {"records_expected": 86, "records_dumped": 86, "records_lost": false} STATUS END

The load command should be something like

mysql my_database < load.sql

If you repeat the steps you should get the same result. If something doesn't work - drop me a line in LinkedIn I will send you the dumps/default/users file.

Good luck.