mariadb 从.frm和.ibd文件恢复表？

我有一些ibd和frm文件。现在我想把它们导入mysql数据库。但它不工作。我尝试与mysqlfrm，但它显示

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.

我在windows 10上安装了mysqlfrm。如果有人知道如何解决这个问题，请告诉我。那会有很大的帮助。
谢谢

由于你共享了users.ibd和user.frm，我拍了一张照片，在Windows 11机器上恢复了表。实际上，在“Windows上的Ubuntu”上，所以它将在原生Ubuntu上工作。
首先，获取Undrop for InnoDB并编译它。

$ 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

将users.zip放到当前目录-undrop-for-innodb中。

unzip users.zip

现在让我们从users. frm恢复一个表模式。我喜欢dbsake工具。它比mysqlfrm好得多（对不起Oracle，这是真的）。

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

下面是您的架构：

$ 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;

现在，让我们从users.ibd中获取记录，第一步是解析ibd文件并对其中的InnoDB页面进行排序。

./stream_parser -f users.ibd

stream_parser创建pages-users.ibd/FIL_PAGE_INDEX/0000000000000367.page，这是一个包含users表的PRIMARY索引的InnoDB页的文件，基本上，这就是表记录所在的位置。
第二步是从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

命令将记录保存在文件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"

为了将此文件加载到MySQL示例中，c_parser还生成了一个辅助LOAD语句，我将其保存在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

load命令应类似于

mysql my_database < load.sql

如果你重复这些步骤，你会得到同样的结果。如果有什么不工作-下降我一行在LinkedIn我会发送给你的dumps/default/users文件。
祝你好运。

mariadb 从.frm和.ibd文件恢复表？

1条答案

相关问题

热门标签

最新问答