I have what I believe is a corrupted MySQL table. In the database I can see there is only a .frm
file, not a .mdi
or .mdy
for it. How can I recover data from this .frm
file?
I believe that I have to get data from the ibdata1
file?
There is no data in the .frm
file; that file only contains the table definition. However, if you're not running a MyISAM table, it's expected that there will be no .MYD
or .MYD
files, because those are only used for MyISAM tables. Instead, the data will be somewhere else -- for InnoDB tables, for instance, it'll be in your ibdata*
files (or perhaps in a per-table file if you've got file_per_table
turned on).
The .frm file doesn't really contain any data--it's just the table definition. You can read the specifics here.
Use the tool dbsake.
curl -s get.dbsake.net > dbsake
chmod u+x dbsake
./dbsake frmdump path/to/frm/file.frm
this will output something like:--
-- Table structure for table `mytable`
-- Created with MySQL Version 5.7.28
--
CREATE TABLE `mytable` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(1000) DEFAULT NULL,
`url` varchar(1000) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Option 2
You also could spin up a mysql sandbox instance by:
dbsake sandbox -D mysql-backup-dir/
~/sandboxes/sandbox_20200621_192450/sandbox.sh start
~/sandboxes/sandbox_20200621_192450/sandbox.sh mysql