#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    25
    Rep Power
    0

    Recover MySql Data from .MYD .frm .MYI files


    Hi Everyone,

    I need to recover mysql database from a folder which contains .MYD, .frm, and .MYI files.

    I have mysql installed on this Solaris machine. I have not done this earlier, so i am asking this question.

    Request all members to guide me with the steps.

    Thanks to everyone.

    Ajay.
  2. #2
  3. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,549
    Rep Power
    4542
    I restored a database from the server data files once, but it was many years ago and I don't know if the procedure is still valid.

    Basically, as root i copied the entire database directory in to /var/lib/mysql on the target machine. Next I verified ownership and permissions on the copied database. I was then able to run mysqlcheck on the restored database.

    PS- this was a MyISAM db.
    ======
    Doug G
    ======
    I've never been able to appreciate the sublime arrogance of folks who feel they were put on earth just to save other folks from themselves .." - Donald Hamilton
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    25
    Rep Power
    0
    Originally Posted by Doug G
    I restored a database from the server data files once, but it was many years ago and I don't know if the procedure is still valid.

    Basically, as root i copied the entire database directory in to /var/lib/mysql on the target machine. Next I verified ownership and permissions on the copied database. I was then able to run mysqlcheck on the restored database.

    PS- this was a MyISAM db.
    Hi Doug,

    First i am stopping the mysql database. then i will place backup folder, "emcdb" in the path /usr/bin/mysql
    and then i will again start the mysql database.

    then which command i should use to repair the database and table ??

    thanks Sir for helping me.

    please reply me.

    From Ajay.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    25
    Rep Power
    0
    Originally Posted by ajays
    Hi Doug,

    First i am stopping the mysql database. then i will place backup folder, "emcdb" in the path /usr/bin/mysql
    and then i will again start the mysql database.

    then which command i should use to repair the database and table ??

    thanks Sir for helping me.

    please reply me.

    From Ajay.

    Hi Forum Members,

    Does any one else has the process and steps to recover the database from .MDY .frm .MYI files.

    if i have the exact steps, it will save my time, though i have started working on it.

    Thanks Doug and Other forum Members.
  8. #5
  9. Lazy Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,384
    Rep Power
    9645
    Originally Posted by ajays
    Does any one else has the process and steps to recover the database from .MDY .frm .MYI files.
    I did this once a few years back and Doug's procedure sounded about right. Have you actually tried it yet?
  10. #6
  11. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,549
    Rep Power
    4542
    Yes, stopping the mysql server is a good idea. One note, in my case mysql kept the database files in /var/lib/mysql, not /usr/bin/mysql

    Assuming the database is recognized after copying/moving the db files to the proper location and restarting mysql server, the command line program mysqlcheck verifies myisam databases.
    ======
    Doug G
    ======
    I've never been able to appreciate the sublime arrogance of folks who feel they were put on earth just to save other folks from themselves .." - Donald Hamilton
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    25
    Rep Power
    0
    Originally Posted by Doug G
    Yes, stopping the mysql server is a good idea. One note, in my case mysql kept the database files in /var/lib/mysql, not /usr/bin/mysql

    Assuming the database is recognized after copying/moving the db files to the proper location and restarting mysql server, the command line program mysqlcheck verifies myisam databases.
    Hi Doug and Requinix,

    with this "mysqlcheck" command, i get this error.

    aunr-cad-01# mysqlcheck -u root --auto-repair --check --databases emcdb;

    emcdb.compliance_folders_data
    Error : Incorrect key file for table 'compliance_folders_data'; try to repair it
    error : Corrupt
    emcdb.compliance_folders_lookup
    Error : Incorrect key file for table 'compliance_folders_lookup'; try to repair it
    error : Corrupt

    what can be the solution ? I also tried to login to mysql and repair each tables or database. But every command gives same error.

    mysql> check table ''compliance_folders_data'';
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '''compliance_folders_data''' at line 1
    mysql>
    mysql> REPAIR TABLE compliance_folders_data;
    +-------------------------------+--------+----------+--------------------------------------------------------------------------+
    | Table | Op | Msg_type | Msg_text |
    +-------------------------------+--------+----------+--------------------------------------------------------------------------+
    | emcdb.compliance_folders_data | repair | Error | Incorrect key file for table 'compliance_folders_data'; try to repair it |
    | emcdb.compliance_folders_data | repair | error | Corrupt |
    +-------------------------------+--------+----------+--------------------------------------------------------------------------+
    2 rows in set (0.01 sec)


    Thanks for answering my query.
    Ajay
  14. #8
  15. Lazy Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,384
    Rep Power
    9645
    Are you copying files between the same versions of MySQL? Is it also roughly the same operating system?
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    25
    Rep Power
    0
    Originally Posted by requinix
    Are you copying files between the same versions of MySQL? Is it also roughly the same operating system?
    I am using this mysql version.

    "mysql Ver 14.14 Distrib 5.1.30, for sun-solaris2.10 (sparc) using readline 5.1"

    This mysql DB was on different machine, but that machine had corrupt disk. we had transferred the backup of that machine to this present Solaris

    "Oracle Solaris 10 1/13 s10s_u11wos_24a SPARC". both are solaris machine.

    How i can repair the database.

    Thanks
    From Ajay
  18. #10
  19. Lazy Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,384
    Rep Power
    9645
    If you copied files from a corrupt disk then it's quite possible one or more of those files are corrupted.

    So to be sure: you had MySQL 5.1 on the other machine too, right?

    Meanwhile if the key file is the only issue then you may be able to create the table before the import (ie, CREATE TABLE), shut down MySQL, copy the good files, start MySQL, and repair/analyze table.
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    25
    Rep Power
    0
    Originally Posted by requinix
    If you copied files from a corrupt disk then it's quite possible one or more of those files are corrupted.

    So to be sure: you had MySQL 5.1 on the other machine too, right?

    Meanwhile if the key file is the only issue then you may be able to create the table before the import (ie, CREATE TABLE), shut down MySQL, copy the good files, start MySQL, and repair/analyze table.
    Yes Requinix,

    how will i know, which are good files ?

    I am not sure, but mostly probably the earlier server also had Mysql 5.1.

    Or i need to do hit and trial method, check each file 1 by 1 by creating table.

    and do repair table name ?


    From
    Ajay
  22. #12
  23. Lazy Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,384
    Rep Power
    9645
    You can also try
    Code:
    REPAIR TABLE compliance_folders_data USE_FRM;
    Originally Posted by ajays
    how will i know, which are good files ?
    Copy everything and repair each table. See what files MySQL gives errors about.
  24. #13
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    25
    Rep Power
    0
    Originally Posted by requinix
    You can also try
    Code:
    REPAIR TABLE compliance_folders_data USE_FRM;

    Copy everything and repair each table. See what files MySQL gives errors about.

    Hi Requinix,

    I have placed all type of files in a folder called "emcdb". took the folder to /var/lib/mysql and restarted mysql.

    Logged in to the mysql from command prompt.

    show database; command shows that database emcdb, use emcdb; to get into the database.

    But REPAIR TABLE table_name USE_FRM; does not works.

    mysql> REPAIR TABLE compliance_folders_data USE_FRM;
    +-------------------------+--------+----------+-----------------------------------------+
    | Table | Op | Msg_type | Msg_text |
    +-------------------------+--------+----------+-----------------------------------------+
    | compliance_folders_data | repair | error | Failed repairing incompatible .frm file |
    +-------------------------+--------+----------+-----------------------------------------+
    1 row in set (0.00 sec)
  26. #14
  27. Wiser? Not exactly.
    Devshed God 2nd Plane (6000 - 6499 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    6,099
    Rep Power
    4103
    That error appears to be due to a version mismatch:
    Caution
    If you use USE_FRM for a table that was created by a different version of the MySQL server than the one you are currently running, REPAIR TABLE will not attempt to repair the table. In this case, the result set returned by REPAIR TABLE contains a line with a Msg_type value of error and a Msg_text value of Failed repairing incompatible .FRM file.
    You could try using the same version (if you know what it is), but I'd say things are not looking good for you. The files may be too corrupted to be repaired and recover the data.
    Recycle your old CD's



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  28. #15
  29. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    25
    Rep Power
    0
    Originally Posted by kicken
    That error appears to be due to a version mismatch:


    You could try using the same version (if you know what it is), but I'd say things are not looking good for you. The files may be too corrupted to be repaired and recover the data.
    Hi Kicken,

    I also think all the data is corrupt. also i will give you reputation and also bit coins .

    Though i think the recover journey for this mysql server did not finish properly.

IMN logo majestic logo threadwatch logo seochat tools logo