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