How do I add databases from a crashed MySQL 5.1 server to a new MySQL 5.6 server

Question

I was running MySQL 5.1 on a personal web server that recently died. The system drive is non-operational, but I have my second hard disk where I had installed all of my programs. This drive has my MySQL 5.1 installation directory, and in the data subdirectory appears to have all of my databases. Databases are both MyISAM and InnoDB, mostly the latter.

I just purchased a new PC to use as a server and installed MySQL 5.6 on it. I would like to move these databases onto this new server. I had hoped it would be as easy as stopping the service, copying the subfolders within [installDirectory]/data, and restarting the service. However, when I tested that with a small InnoDB database MySQL started okay and I could log into it and see the database, but all tables reported no rows and gave errors when trying to access them. MyISAM databases seem to copy over just fine.

How can I get these old databases onto the new server? I do have sql dump backups, but they're a month old (again, this was a personal web server) so I'd rather not go back to them if I can help it. All of the resources I can find in a web search detail moving files from a functional 5.1 server to a functional 5.6 server.

Answer 1

I have resolved this issue using the instructions posted here: http://dev.mysql.com/doc/refman/5.6/en/innodb-backup.html

I was unaware that by default all data in InnoDB tables across all databases on the server are stored in one single file at the root of the data directory: ibdata1. That file, and I believe the log files, must also be copied along with the folders containing the FRM files (which apparently just store the table structure). The side effect of this, of course, is that any InnoDB tables already defined on your new server will be rendered unusable.

• Vérace: Don't forget to mark your own answer as correct (no points though :-( ) so that others with the same issue may find a solution.
• Nicholas: @Vérace I will. I wanted to wait a short time, though, in case someone posted a more thorough answer with more insight than I could provide.
• ESG: It might sound obvious, but you can always copy the existing data to a new, temporary instance so you can just pick & choose whatever database and/or table you need
• da_didi: You can use innodb_file_per_table and next time remember to backup your databases regularly!