up vote 3 down vote favorite

I have a mysql installed inside a windows virtual machine. The hard drive was crashed and the system is not able to restore all again. But I can have access to entire c: ROOT. So I went to

c:/ProgramData/Mysql/Mysql Server 5.5/data/myDatabaseName

There I can see different files like: .frm, .opt, .trg and .trn. In a regular way, I could run a mysqldump to backup and restore all database from command line, but the system is not able to be restored. So I had to copy all these files and past inside inside the same directory inside same database in another physical instance.

The issue is some tables are not been recognized by this new instance. So I think copy and past is not enough to restore the database.

What am I missing?

Note: Database has innoDb and Myisam tables.

share|improve this question
up vote 3 down vote accepted

InnoDB makes it impossible to just cut and paste a database because each .ibd files has tablespace_id that are referenced in the data dictionary within c:/ProgramData/Mysql/Mysql Server 5.5/data/ibdata1.

Here is a Pictorial Representation of InnoDB (from Percona CTO Vadim Tkachenko)

sx

You cannot recover just a database. You must restore the entire data folder c:/ProgramData/Mysql/Mysql Server 5.5/data. Please see my old post restore table from .frm and .ibd file? on reattaching .ibd files to the data dictionary.

If the database folder has no .ibd files but only .frm files, then all the data are contained within c:/ProgramData/Mysql/Mysql Server 5.5/data/ibdata1.

You may have mangled the data dictionary. You have to do a complete restore of your datadir or mysqldump from an original database, shutdown mysqld, delete all of datadir, restart mysqld, and reload the mysqldump.

share|improve this answer
  • Hi Ronaldo. Thanks a lot. Your suggestion save my day. Well, what I did I copy all the data file from the restored machine, and pasted to a new machine that had some databases, lite Sakila and etc. I just renamed the data diretory to dataS, so the mysql server could not recognize the data directory and past the new data diretcory with the correct name (data) as mysql server was execting. It seems that worked. Even the password was the same of the old machine. I was not abble to access the mysql client using the password they had before the changes. Continuing in the next post .. – zwitterion Apr 24 '15 at 15:03
  • So, I can not see the databse like sakila, test, ect, (as expected) I can see all recovery databases.... Well. my question is... how sure can I be with this method? That means, only copy and past the data dirrectory, am I sure that I have recovered all data or there could be another place somewhere that mysql could store data or links or dictionary or relationship between tables? As I told I have innoDb and Myisam tables in all databses. – zwitterion Apr 24 '15 at 15:07

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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