0

My old server was crashed but I was able to recover the databases from MySQL through testdrive.

I'm trying to dump a database using mysqldump but it gives error for certain tables are crashed and should repaired.

I tried myisamchk --safe-recover /var/lib/mysql/database/table.MYI
And also myisamchk -r -q /var/lib/mysql/database/table.MYI

The tables fix themselves but unfortunately they delete that data from MYD files.

Is there a way to fix the tables without losing data from MYD or is it possible to convert the MYD into human readable format like a txt file?

4
  • I think you have .frm and .myd and it might have deleted the .myi Commented May 28, 2015 at 18:11
  • @MySQLRockstar I have the 3 files and none where deleted. It's reseting the MYI to 1024B and emptying the MYD file becoming 0.
    – Zaher
    Commented May 28, 2015 at 18:13
  • Have you done a mysqladmin flush-tables before you ran myisamchk ? Commented May 28, 2015 at 18:16
  • @MySQLRockstar yes I used that and same result ending
    – Zaher
    Commented May 28, 2015 at 18:21

2 Answers 2

2

First of all, if your data is worth anything, don't store it in MyISAM.

I don't think you can reconstruct a table from corrupted MYD file. Records in MyISAM have different format that depends on values stored in each record. There are about 20 block types! To make it more fun the records in MyISAM can be fragmented. So, if you absolutely need the data from MyISAM table the only way I'm aware of is a C program with wildcard masks.

You can partially recover the table if you select from it ranges of the primary key. Corrupt PK ranges will be lost, but at least if the range is good you can get it back.

Here's a script that I wrote a while ago to read as many records is possible from a corrupted table. Try it out, I hope it'll help:

#!/bin/bash

# Adjust your values here
host=localhost
user=root
db=test
table=test
PK=id
newtable=newtest2
# end of user defined values

read -p "Password: " -s password
echo ""
if [ "xxx$password" = "xxx" ]
then
        pass_cmd=""
else
        pass_cmd="-p$password"
fi
mysql -h $host -u $user $pass_cmd -e "show databases" $db
if [ $? -eq 0 ]
then
        echo "Credentials are ok!"
else
        echo "Can't run query SHOW DATABASES"
        echo "Check what's wrong"
        exit
fi
minPK=`mysql -sN -h $host -u $user $pass_cmd -e "SELECT MIN($PK) FROM $table" $db`
maxPK=`mysql -sN -h $host -u $user $pass_cmd -e "SELECT MAX($PK) FROM $table" $db`
a=$minPK
b=$maxPK
let chunk=$b-$a

function insert_range {
q="INSERT IGNORE INTO \`$newtable\` SELECT * FROM \`$table\` WHERE \`$PK\` >= $1 and \`$PK\` <= $2"
mysql -h $host -u $user $pass_cmd -e "$q" $db 2>/dev/null
}
successful_tries=0
echo "Primary key ($PK) range: $minPK .. $maxPK"
while true
do
        insert_range $a $b
        if [ $? -eq 0 ]
        then
                echo "Good, primary key range: $a .. $b"
                let a=$b+1
                let b=$b+$chunk
                let successful_tries=$successful_tries+1
        else
                echo "Bad luck, primary key range: $a .. $b"
                let chunk=$chunk/2
                let b=$a+$chunk
                mysqladmin -h $host -u $user $pass_cmd ping > /dev/null 2>&1
                while [ $? -ne 0 ]
                do
                        mysqladmin -h $host -u $user $pass_cmd ping > /dev/null 2>&1
                done
                successful_tries=0
        fi
        if [ $a -gt $maxPK ]
        then
                exit
        fi
        if [ $chunk -eq 0 ]
        then
                let a=$a+1
                let b=$a+1
                let chunk=1
        fi
        # If things are going well, increase pace
        if [ $successful_tries -gt 5 ]
        then
                let chunk=$chunk*2
        fi
done
3
  • Thanks for the information and script I didn't choose MyISAM from the beginning it was the software itself I think. I copied your script and saved it as mysql.sh then I ran sh mysql.sh it asked for password I entered root password I get
    – Zaher
    Commented May 28, 2015 at 19:27
  • Credentials are ok! ERROR 1146 (42S02) at line 1: Table 'test.test' doesn't exist ERROR 1146 (42S02) at line 1: Table 'test.test' doesn't exist mysql.sh: line 33: let: chunk=-: syntax error: operand expected (error token is "-") Primary key (id) range: .. Bad luck, primary key range: .. mysql.sh: line 52: let: chunk=/2: syntax error: operand expected (error token is "/2") mysql.sh: line 53: let: b=+: syntax error: operand expected (error token is "+")
    – Zaher
    Commented May 28, 2015 at 19:27
  • As a comment in the script suggests you have to define your values for variables host, user, db, table, PK, and newtable
    – akuzminsky
    Commented May 28, 2015 at 20:07
0

Have you tried CHECK TABLE and REPAIR TABLE

REPAIR TABLE is the equivalent of myisamchk --recover (-r). If you want to rebuild the MYI file then the correct use of REPAIR TABLE is:

REPAIR TABLE tablename USE_FRM;

It is best to make a backup of a table before performing a table repair operation; under some circumstances the operation might cause data loss

3
  • I tried repair table using your command and still it's emptying the MYD file it was 85,396B now it's 0. I have backup of all files.
    – Zaher
    Commented May 28, 2015 at 18:17
  • So if MYD is zero, means you have no data, Have you checked in mysql prompt if your tables has data or its truncated. Also do you have only MyISAM or a mix of Innodb and MyISAM ? Commented May 28, 2015 at 18:29
  • The MYD wasn't zero when I use myisamchk it becomes 0. No I didn't check MySQL prompt but it has to have data. I think I have mix of both engines
    – Zaher
    Commented May 28, 2015 at 18:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.