MySQL – How to Recover a Broken or Damaged Database from Transaction File

MySQLrecoveryrestore

A greeting fellow, I would like to know if in MySQL (I understand that all engines must have), you can recover from damage to the database using some kind of file "journal" transactions, if so, what is that file? What is the correct procedure to recover it ?. I know that I can recover it from the * .frm files present in /var/lib/mysql, but I would also like to know this other option. And if someone dominates the subject, I will appreciate your comments.

Best Answer

The MySQL binary log is what you are looking for. Aside from being used for replication, because it stores a range of writes executed on the server, it can be used for point in time recovery.

You will need some kind of regular starting point in case of recovery (such as a full backup), and then you can use the binary logs and mysqlbinlog to roll forward to an arbitrary point in time (e.g. just before an accidental table drop). So they won't substitute backups but can complement them nicely.

Some people was able even to use them to roll back actions under specific circumstances and configuration.

Related Solutions

MySQL Table Structure – How to Recover from FRM Files

If you only see the .frm files, then there is a strong likelihood that the storage engine in use was InnoDB and innodb_file_per_table must have been off by default.

If you transferred everything from datadir on the crashed server onto another disk on another machine, you may be able to startup mysql with that folder as is.

For example, suppose ServerA is your crashed server and ServerB is where you want it placed.

Install MySQL 5.5.8 on ServerB
net stop mysql on ServerB to make sure mysql is down on ServerB
md C:\MySQLData on ServerB
Drop everything from the data folder on ServerA into C:\MySQLData on ServerB
Make C:\MySQLData the new datadir

Add this to my.ini on ServerB

[mysql]
datadir=C:/MySQLData

C:\> del C:\MySQLData\ib_logfile* on ServerB
net start mysql on ServerB

Please try this and tell us what happened

UPDATE 2012-02-03 17:06 EDT

Since you were able to recover everything NOW DO THIS:

mysqldump -u... -p... -A -d --routines --triggers > C:\MySQLSchema.sql

This will give all table structures in the MySQL Instance.

Mysql – Error When Restoring Backed Up Tables

I think I got it.

Here is the table as it appears in Linux from the Chat Session

-rw-rw----. 1 mysql mysql 9478 Jul 30 07:18 /var/lib/mysql/bsgtran/t2010_08.frm 
-rw-rw----. 1 mysql mysql 1882892084 Jul 30 07:21 /var/lib/mysql/bsgtran/t2010_08.myd 
-rw-rw----. 1 mysql mysql 74588160 Jul 30 07:22 /var/lib/mysql/bsgtran/t2010_08.myi

Since the database is on a Linux OS, filenames are case-sensitive.

Here is the solution

mv /var/lib/mysql/bsgtran/t2010_08.myd /var/lib/mysql/bsgtran/t2010_08.MYD
mv /var/lib/mysql/bsgtran/t2010_08.myi /var/lib/mysql/bsgtran/t2010_08.MYI

File extensions must be uppercase for MyISAM tables in Linux

Give it a Try !!!

Best Answer

Related Solutions

MySQL Table Structure – How to Recover from FRM Files

UPDATE 2012-02-03 17:06 EDT

Mysql – Error When Restoring Backed Up Tables

Related Question