Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer 1

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.

share|improve this answer
    
Thanks for the tips. This error pops up in the err log when I run mysqld_safe on the backup 2013-04-21 12:16:09 10077 [ERROR] InnoDB: read can't be opened in ./ib_logfile0 mode 2013-04-21 12:16:09 10077 [ERROR] Plugin 'InnoDB' init function returned error. 2013-04-21 12:16:09 10077 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 2013-04-21 12:16:09 10077 [ERROR] Unknown/unsupported storage engine: InnoDB 2013-04-21 12:16:09 10077 [ERROR] Aborting 2013-04-21 12:16:09 10077 [Note] Binlog end Any thoughts on what that means? –  Ken Ingram Apr 21 '13 at 19:09
    
Only the first message is relevant -- the others are all side-effects of it. Check the file permissions... everything in the data directory needs to be owned and writable by the appropriate system user account, typically 'mysql' is the correct user. –  Michael - sqlbot Apr 22 '13 at 1:47
    
i deleted ib_logfiles which i imagine was the rollback log and the db started up. Not sure what the ramifications were. But I was able to get some important older data. –  Ken Ingram Apr 22 '13 at 18:35
1  
If my answer was helpful, please consider accepting it. The ramifications of removing the ib_log* files depend on whether you had innodb_fast_shutdown = 0 when you did the graceful shutdown before the backup. If you didn't, the data in the tablespace (ibd) files may not represent the latest state of committed transactions when you shut down, but if it was, then you should be fine. Accessing the tables requires that the frm and ibd files both exist and be named as the server expects. –  Michael - sqlbot Apr 23 '13 at 2:10
    
Michael I believe your solution is helpful but I couldn't get anything working until I deledted the ib_log files. I don't know what name they are suppose to have and recovery is not happening on the original machine, so I doubt any transactions in the logs will complete successfully. Thanks for the input. It did help in other ways. –  Ken Ingram May 19 '13 at 21:46
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.