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.
The answer with MySQL is always, "it depends". That being said where 5.5 is going to give you a huge improvement on boxes with 16 cores and higher. If you use the InnoDB storage engine, then there is a good chance that you will get better performance too. The best way to find out is to get a test box and run it through a performance test of your system.
As far as upgrades go, the safest way to upgrade tends to be a dump and reload. It eliminates most possible issues. I have successfully upgrade from 5.0 to 5.1 just using mysql_upgrade. I don't know of any particular issues with indexes getting hosed by an upgrade to 5.5. (Though I haven't upgraded any of our production boxes to 5.5 yet. Test boxes haven't had issues.) If you do go the upgrade route without a dump/restore, make sure to read the docs on the web site. 5.5 behaves differently than the upgrade from 5.0 to 5.1 did.
Best Answer
You cannot mysqldump the mysql schema and simply import to the higher version. Why ? In the DBA StackExchange, I answered this question a little over a year ago: Cannot GRANT privileges as root. In short, the table
mysql.userhas a different number of columns for each major release of MySQL:What you can do is dump
mysql.userusing one of two techniques:TECHNIQUE #1
Use one of the following Percona Tools
Echo the output of either program to a text file.
TECHNIQUE #2
You can emulate pt-show-grants as follows:
That is the cleanest and safest way to migrate grants from version to version. I have successfully upgraded MySQL 5.0 to MySQL 5.1/5.5 many times. You cannot go MySQL 5.0 to MySQL 5.6 because IMHO the 16-character PASSWORD() function hash is not available via old_password. If you have the plain text passwords for all users, simply edit MySQLUserGrants.sql replacing
IDENTIFIED BY PASSWORD '16-char hash'withIDENTIFIED BY 'plaintext password'. Then you can execute it in MySQL 5.6. When done, quickly delete MySQLUserGrants.sql.