Mysql – way to recover this database (MySQL)

MySQLmysql-5.1

I did a "cold" (db offline, all filesystem files) backup of a MySQL db.
I wrote the backup to an ISO image. Unfortunately Joliet filenames are not default.
I didn't realize that until attempting to restore the backup and notice that the
entire set of files are in DOS 8.3 format. Many of the MySQL filesystem files are not 8.3.

Anyone have tips or information on whether this is a lost cause?

If it's not a lost cause for restoring, are there some tips directions that can be offered?

Much appreciated.
========= 4/22/13
I have been able to recover some of the tables. Others respond with 1146 "Table Doesn't exist". Not sure why, as they are tables containing data, but renaming the frm does not appear to work.

As well, most of the time, the table name isn't in the frm file, just the column name data.

Best Answer

It might not be a lost cause, if you can figure out which files are which, but you'll most likely have to do that manually. Presumably your 8.3 format preserved the extensions of the files, which should make things somewhat easier.

I would start with a new installation of the same version of MySQL, verify that it works, and then stop it.

References to the "data" directory below would be whatever SHOW VARIABLES LIKE 'datadir' returns. Often, this is either /usr/local/mysql/data or /var/lib/mysql.

Identify which files in the backup were ibdata1, ib_logfile0, and ib_logfile1 in the "data" directory, and rename them to the proper names and copy them into place over the files from your new install.

Each schema should be a directory inside "data" in your backup. Copy all of those directories into "data" from your new install (including, especially the "mysql" directory inside "data"), then rename them to the original names of the schemas.

Then, inside each of those directories, you'll find the table files, views, and triggers, which hopefully have their original extensions... rename those to their original names, also, where .frm is a table or a view, .myi, .myd, .ibd would be tables, .trn would be triggers. Each of these needs its original filename, which would also be the names of the original table/view/trigger.

You can use the strings(1) unix command line utility to get some idea of what's in the files, to help you identify them. The frm files, for example, will have table or view definitions, which you could use to identify which table you're dealing with, if the filenames are ambiguous. Example:

sqlbot@dev:/usr/local/mysql/data/sakila# strings actor.frm
PRIMARY
idx_actor_last_name
InnoDB
)
        actor_id
first_name
last_name
last_update
actor_id
first_name
last_name
last_update

The same thing on the .ibd file will show the row data (assuming the tables weren't stored compressed on disk). It isn't pretty or formatted, but it's readable.

You don't have to go through all of this work, though, to test whether this is going to be a viable route for you. After you've fixed the ibdata1 and ib_log* files and the data/mysql files (compare with the names of the files in the reinstalled data/mysql directory) and fixed up the names of a couple of tables to test, you should be able to start the server and try to access only those tables that you've fixed the names on so far, and see if you can access the data.

If you don't try to access the tables you haven't yet fixed, the server should come up. if not, the hostname.err log should contain hints as to what you might have overlooked.

Then shut it down again to do more renaming.

If this process proves successful, I would then suggest doing a mysqldump of all the databases and restore it elsewhere, since this resurrected instance wouldn't be something I'd want to put in production.

Related Question