Well I tried a bunch of stuff and researched a bunch of commands, switches, and structures (not surprisingly, the MySQL docs were most helpful, if extensive—needle/haystack). Eventually some of my tricks worked (it turns out that others had thought of the same tricks, though I did not see the two major parts—recovering the table structure, and recovering the data—in one place, so I’m posting them together here).
What I had to do was to recreate the IBDATA1 file. Unfortunately while running the daemon detects the databases (the directories), it does not pick up the tables Innodb inside (the IBD/FRM files). So what I did was to:
- Empty out the data directory (or move the original and create an empty one)
- Run the daemon, let it create a new, empty IBDATA1 file
- Import the system tables using the SQL scripts in
…\MySQL\share
- Create a dummy database and table of the same name
- Copy over the original FRM file
- Use either
DESCRIBE
or better, SHOW TABLE CREATE
to extract the table structure
- Next, I used
DISCARD TABLESPACE
on the table
- Copied over the original IBD file
- Then I used
IMPORT TABLESPACE
- Finally, I re-ran the daemon with
innodb-force-recovery=6
- And I ran
mysqldump
to extract the structures and data
Of course it was not always smooth. Some tables were fine, but some required dropping the table and database after the SHOW TABLE CREATE
, and using that to re-create the table before trying to import the data. Others did not even work that far, and I had to manually get the comments and names of the columns from the FRM file using a hex-editor (though figuring out what the data types and attributes, keys, etc. were was a crap-shoot). Also, there were plenty of—too many—daemon and client restarts.
(I’m still looking for a tool that will directly parse FRM/IBD files (or at least display table structure from an FRM file), but it looks like nobody has bothered to “reverse-engineer” them even though it’s open-source and the file formats are publicly available. It seems that everyone is complacent using the official MySQL tools—thus creating a great opportunity for data-recovery firms and proprietary/commercial tools.)
The key was to always work with the absolute minimum (eg only the MYSQL directory—ie system tables). Unfortunately while it meant that things would be simplified and easier to work with, it also meant recovering one table at a time—which wasn’t a big deal for me, but for some people it could be.
Anyway, out of the many MySQL recovery pages on the Internet that I saw during the past couple of days, a small handful were pretty useful, and I’ll add them once I scour my history to dig them up.
Hope this can help others in a similar situation.
First off, going from 32bit to 64bit should not have any correlation to your filesystem, so copying your DB files directly should not be a concern. Dumping a large DB could be slow, so copying the raw files is probably the best option. Are you using MyISAM, or are all your tables in InnoDB? If you are using InnoDB, you could try using xtrabackup from Percona to do a "live" backup of your database without downtime:
https://launchpad.net/percona-xtrabackup
If you're using MyISAM and downtime is a concern, what you can do is perform an rsync
on the data files directly while the server is running. Run it multiple times in a row until the "changed" tables are few and rsync is quick to finish. Then, you can perform a quick shutdown of MySQL, run the rsync
one last time to grab the files in a consistent state and start MySQL again. You can then copy them to your new server, start MySQL and go from there.
Hope this helps!
Best Answer
The monolithic
ibdata1
contains some system-wide data. It is possible you could suffer data loss by trying to delete it manually without getting rid of all your existing databases first.(There are ways to recover from such a situation, but why put yourself in that position? Follow the instructions you've been given and dump ALL your databases, rebuild your MySQL installation, and reload them. It's the safe choice.)
You're going to have to afford it. It's just one outage - schedule it and make it happen.
Our resident MySQL expert confirms that this is the only (safe) way to shrink
ibdata1
and I defer to his expertise in these matters.(I would also suggest reading his other posts, linked from that answer, before you schedule the outage window. You may as well clean up as many potential problems as you can since you're going to have to take an outage window...)