Mysql – Is it possible to import a single thesql database directory

If you only see the .frm files, then there is a strong likelihood that the storage engine in use was InnoDB and innodb_file_per_table must have been off by default.

If you transferred everything from datadir on the crashed server onto another disk on another machine, you may be able to startup mysql with that folder as is.

For example, suppose ServerA is your crashed server and ServerB is where you want it placed.

• Install MySQL 5.5.8 on ServerB
• net stop mysql on ServerB to make sure mysql is down on ServerB
• md C:\MySQLData on ServerB
• Drop everything from the data folder on ServerA into C:\MySQLData on ServerB
• Make C:\MySQLData the new datadir

Add this to my.ini on ServerB

[mysql]
datadir=C:/MySQLData

• C:\> del C:\MySQLData\ib_logfile* on ServerB
• net start mysql on ServerB

Please try this and tell us what happened

UPDATE 2012-02-03 17:06 EDT

Since you were able to recover everything NOW DO THIS:

mysqldump -u... -p... -A -d --routines --triggers > C:\MySQLSchema.sql

This will give all table structures in the MySQL Instance.

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.