Restore MySQL database from .frm files

Tags:
MySQL
I'm in a huge bind. I'm using Windows Server 2003 / MySQL. I have to recover a MySQL database from an old server to a new installation. The server it was running on will no longer boot. So I cannot do a backup. All I have is the MySQL data directory from the old machine. I've stopped the MySQL services on the new box, copied over the data directory (just the subfolders for each db) and all of the databases work except for the one I need. I've also tried copying all of the files in the data folder, but then MySQL service will not start. The db folder has .frm files and one .opt file. It does not have .myd and .myi files like the other databases. Is there a way to restore just from these .frm files? HELP!
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

Repairing tables with mysqlcheck or another variant to learn helpful topics
about mysql databases

https://social.msdn.microsoft.com/Forums/en-US/fb94219c-fbf8-455d-b912-5544be11f186/how-to-fix-my-sql-database?forum=sqldataaccess

http://www.sqlservercentral.com/Forums/Topic1618894-2893-1.aspx

But in case your corruption can’t be overcome, then I would suggest you
more powerful instrument for such cases https://mysql.recoverytoolbox.com/
Recovery Toolbox for MySQL

 

If you, by any chance, forgot to create backup, you should do more work as
the following:

  1. Edit my.cnf (usually
    located in /etc/my.cnf) and add this line into [mysqld] section

innodb_force_recovery=4

  1. Restart mysql (e.g:
    /etc/init.d/mysqld restart)
  2. Your MySQL log should
    tell you which table is corrupt. You can dump the table using “SELECT …
    INTO OUTFILE …” from mysql console or using tool
    like mysqldump
    • Example using MySQL
      query (you also need to dump the schema later)


mysql> SELECT * FROM corrupted_table INTO OUTFILE ‘/path/to/file’

    • Example using mysqldump

root# mysqldump –opt -u uname -p dbname corrupted_tablename >
/path/to/out.sql

  1. Go back to my.cnf and
    comment the line you wrote from step #1
  2. Go intoMySQL console and
    drop the corrupted table

mysql> DROP TABLE corrupted_tablename;

  1. Import data dump, for sql
    dump you can use:

root# mysql -u uname -p < /path/to/out.sql

  1. Go back into mysql
    console, check and optimize all tables
  2. Done

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Elizine
    Just copy the .frm files to the to the databse folder but you also need to copy the ib_logfiles and ibdata file into your data folder and just restart the server and database is restored.
    1,145 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: