Recently, when I moved, I did not have time to pick up my databases from the server before it ended. But I managed to pick up the var directory, which, as you know, also stores MySQL databases in the /var/lib/mysql directory.
Actually, in this article I will talk about my experience in restoring databases from these files. Who needs the result right away? Scroll to the end.
I had no experience in transferring databases from files. As you know, MyISAM files can be safely transferred from folder to folder and everything will work.
I decided to try the same trick with .frm and .ibd files. Moved, try to open… I get an error in the form:
Table doesn't exist in engine
Of course, I did not forget to put the rights:
But, as it is not difficult to guess, it was unsuccessful.
Next, I tried to pull this off:
ALTER TABLE tablename DISCARD TABLESPACE; ALTER TABLE tablename IMPORT TABLESPACE;
Which, of course, did not help me again and I ran into this error.
After reading a little about how it all works, I came to the conclusion that to run these tables you need
sacrifice transfer the ibdata1 file from the / var / lib / mysql folder as well.
Without thinking twice, I backed up and deleted my / var / lib / mysql directory on the local computer and dropped the one from the host.
Also, of course, I did not forget to change the rights to the folder
I tried to connect, and, lo and behold, all the tables in place, open and in general everything is fine with them!
PS This is not a 100% option, however, I have a ride like this. Try it, maybe it will work for you too. ) All questions in a comment or in the VKontakte group , I will help than I can.
Do not forget to backup!