How to recover MySQL database from myd myi frm files

Question

How to restore one of my MySQL databases from  myd   myi   frm files

User · Answer

You can copy the files into an appropriately named subdirectory directory of the data folder as long as it is the EXACT same version of mySQL and you have retained all of the associated files in that directory  If you don t have all the files  I m pretty sure you re going to have issues

User · Answer

If these are MyISAM tables  then plopping the  FRM   MYD  and  MYI files into a database directory  e g    var lib mysql dbname  will make that table available  It doesn t have to be the same database as they came from  the same server  the same MySQL version  or the same architecture  You may also need to change ownership for the folder  e g   chown -R mysql mysql  var lib mysql dbname   Note that permissions  GRANT  etc   are part of the mysql database  So they won t be restored along with the tables  you may need to run the appropriate GRANT statements to create users  give access  etc   Restoring the mysql database is possible  but you need to be careful with MySQL versions and any needed runs of the mysql upgrade utility    Actually  you probably just need the  FRM  table structure  and  MYD  table data   but you ll have to repair table to rebuild the  MYI  indexes    The only constraint is that if you re downgrading  you d best check the release notes  and probably run repair table   Newer MySQL versions add features  of course    Although it should be obvious  if you mix and match tables  the integrity of relationships between those tables is your problem  MySQL won t care  but your application and your users may  Also  this method does not work at all for InnoDB tables  Only MyISAM  but considering the files you have  you have MyISAM

User · Answer

The above description wasn t sufficient to get things working for me  probably dense or lazy  so I created this script once I found the answer to help me in the future  Hope it helps others  vim fixperms sh      bin sh for D in  find   -type d  do         echo  D          chown -R mysql mysql  D          chmod -R 660  D          chown mysql mysql  D          chmod 700  D  done echo Dont forget to restart mysql   etc init d mysqld restart

User · Answer

I found a solution for converting the files to a  sql file  you can then import the  sql file to a server and recover the database   without needing to access the  var directory  therefore you do not need to be a server admin to do this either   It does require XAMPP or MAMP installed on your computer    After you have installed XAMPP  navigate to the install directory  Usually C  XAMPP   and the the sub-directory mysql data  The full path should be C  XAMPP mysql data Inside you will see folders of any other databases you have created  Copy  amp  Paste the folder full of  myd   myi and  frm files into there  The path to that folder should be   C  XAMPP mysql data foldername  mydfiles Then visit localhost phpmyadmin in a browser  Select the database you have just pasted into the mysql data folder  and click on Export in the navigation bar  Chooses the export it as a  sql file  It will then pop up asking where the save the file   And that is it  You  should  now have a  sql file containing the database that was originally  myd   myi and  frm files  You can then import it to another server through phpMyAdmin by creating a new database and pressing  Import  in the navigation bar  then following the steps to import it

User · Answer

I think  myi you can repair from inside mysql   If you see these type of error messages from MySQL  Database failed to execute query  query  1016  Can t open file   sometable MYI    errno  145  Error Msg  1034  Incorrect key file for table   sometable   Try to repair it  thenb you probably have a crashed or corrupt table   You can check and repair the table from a mysql prompt like this   check table sometable   ------------------ ------- ---------- ----------------------------    Table   Op   Msg type   Msg text     ------------------ ------- ---------- ----------------------------     yourdb sometable   check   warning   Table is marked as crashed      yourdb sometable   check   status   OK     ------------------ ------- ---------- ----------------------------    repair table sometable   ------------------ -------- ---------- ----------     Table   Op   Msg type   Msg text     ------------------ -------- ---------- ----------     yourdb sometable   repair   status   OK     ------------------ -------- ---------- ----------    and now your table should be fine   check table sometable   ------------------ ------- ---------- ----------     Table   Op   Msg type   Msg text    ------------------ ------- ---------- ----------     yourdb sometable   check   status   OK    ------------------ ------- ---------- ----------

User · Answer

Simple  Create a dummy database  say abc   Copy all these  myd   myi   frm files to mysql data abc wherein mysql data  is the place where  myd   myi   frm for all databases are stored   Then go to phpMyadmin  go to db abc and you find your database

User · Answer

For those that have Windows XP and have MySQL server 5 5 installed - the location for the database is C  Documents and Settings All Users Application Data MySQL MySQL Server 5 5 data  unless you changed the location within the MySql Workbench installation GUI

User · Answer

I just discovered to solution for this  I am using MySQL 5 1 or 5 6 on Windows 7    Copy the  frm file and ibdata1 from the old file which was located on  C  Program Data MySQL MSQLServer5 1 Data  Stop the SQL server instance in the current SQL instance Go to the datafolder located at  C  Program Data MySQL MSQLServer5 1 Data  Paste the ibdata1 and the folder of your database which contains the  frm file from the file you want to recover  Start the MySQL instance    No need to locate the  MYI and  MYD file for this recovery

User · Answer

http   forums devshed com mysql-help-4 mysql-installation-problems-197509 html  It says to rename the ib   files  I have done it and it gave me back the db

User · Answer

Note that if you want to rebuild the MYI file then the correct use of REPAIR TABLE is   REPAIR TABLE sometable USE FRM   Otherwise you will probably just get another error

User · Answer

One thing to note   The  FRM file has your table structure in it  and is specific to your MySQL version   The  MYD file is NOT specific to version  at least not minor versions   The  MYI file is specific  but can be left out and regenerated with REPAIR TABLE like the other answers say   The point of this answer is to let you know that if you have a schema dump of your tables  then you can use that to generate the table structure  then replace those  MYD files with your backups  delete the MYI files  and repair them all  This way you can restore your backups to another MySQL version  or move your database altogether without using mysqldump  I ve found this super helpful when moving large databases

[mysql] How to recover MySQL database from .myd, .myi, .frm files

Examples related to mysql

Examples related to recovery