-1

There is a problem on bad sectors on a file which is an important, mysql ibdata1 that contain all data of my databases.

So, I run

badblocks -sv /dev/mapper/storage-monitoring3
Checking for bad blocks (read-only test): 45766008done, 6:44 elapsed.
(0/0/0 errors)
45766009done, 6:46 elapsed. (1/0/0 errors)
45766010done, 6:47 elapsed. (2/0/0 errors)
45766011done, 6:49 elapsed. (3/0/0 errors)
done
Pass completed, 4 bad blocks found. (4/0/0 errors)

After that, I run

root@ubuntu:~# fsck /dev/mapper/storage-monitoring3
fsck from util-linux 2.20.1
e2fsck 1.42.9 (4-Feb-2014)
/dev/mapper/storage-monitoring3: clean, 14046/4276224 files, 2541252/17089792 blocks

But that is clean, and also the problem is not solved.

How to repair that bad sector?

shgnInc
  • 1,591
  • 1
  • 17
  • 31

1 Answers1

0

The best answer is that you should have a backup of your database. With that, you should restore your database on another disk volume. Use the binary logs to do point-in-time recovery.

If you don't have a backup, you might be able to make one, or at least a backup of most of the data.

Restart the MySQL Service with innodb_force_recovery=6 (see https://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html)

Try to do a read-only dump of all data. Try to save it to another disk, not the one with your bad sectors.

mysqldump --single-transaction mydatabase | gzip -c > /tmp/dump.sql.gz

Hope that the pages for your tables don't hit the bad sectors. If they do, then try to dump individual tables. Once you reach the table(s) that hit the bad sectors, you can dump partial tables using mysqldump --where ... (see documentation for mysqldump).

Once you get the database recovered, replace the bad disks in your server. Don't try to reuse disks that have gone bad. It's not worth the risk. Just recycle them.

Good luck.


Re your comment:

I've dealt with this situation once. It's a long shot, but it worked for me once.

What I did was copy leave the mysqld shut down, and try to copy the ibdata1 file using the shell:

dd bs=16k if=ibdata1 of=ibdata1.part1

STOP. If you don't know what the dd command does, go read up on it. http://man7.org/linux/man-pages/man1/dd.1.html

InnoDB pages are 16k by default, so that's the block size to use. This command will reach the pages that are stored on bad sectors, and you'll get I/O errors. Then you try to skip past the bad parts and continue.

dd bs=16k if=ibdata1 of=ibdata1.part3 skip=327

If that hits another bad page, skip another page.

dd bs=16k if=ibdata1 of=ibdata1.part3 skip=328

Keep incrementing the skip count until you stop getting I/O errors. This will mean you got past the bad pages. Calculate how many pages you had to skip. Notice I numbered this "part3" because you'll need to fill in the gap with a part2 of dead data.

Eventually you'll have two files, ibdata1.part1 and ibdata.part2. On the occasion I used this technique, I only needed two parts, because all the bad blocks were localized into one consecutive sequence of three database pages. If you have multiple bad spots, you might need multiple "parts" to this file. Keep track of the number of bad pages between each part.

Then you need to fill in the gap(s) before reconstructing a new file.

dd bs=16k if=/dev/null of=ibdata1.part2 count=4

The count of 3 obviously depends on your results from skipping bad pages. The count of pages might not correspond to the number of bad blocks you found with badblocks, multiple bad blocks might occur within the same InnoDB page.

Once you're all done with this, assemble them:

mv ibdata1 ibdata1.bad
cat ibdata1.part* > ibdata1
chown mysql:mysql ibdata1

Now start up MySQL Service with innodb_force_recovery=6. Try to dump the database as I described earlier.

This might work if the bad pages didn't contain crucial data. For example, if the bad pages were storing secondary indexes, which won't need to be touched during mysqldump. But this is entirely a matter of luck.

If this doesn't work, then your database is not recoverable as far as I know. Sorry to hear about your loss. You might try to engage a data recovery service like https://recovery.twindb.com/

You'll know from this experience that you need to make regular backups.

Bill Karwin
  • 462,430
  • 80
  • 609
  • 762
  • Thanks a lot. Because of the bad sector on `ibdata1` file the `mysql` process can't start, So non of your solution didn't run. – shgnInc Apr 20 '17 at 07:50
  • Thanks again, Just a simple question. How to calculate the `skip` in `dd` command. there was to many `dd: error reading ‘ibdata1’: Input/output error`. So I repeat the steps as you said. At last, ` No space left on device`... – shgnInc Apr 27 '17 at 07:31
  • You can't calculate the skip values, because you don't know how many sectors are bad. You just keep trying higher values until you find a skip value where you don't get an error reading the file. You shouldn't need multiple copies of any given part of the ibdata1. If you did that, then you misunderstood my instructions. But if your ibdata1 is larger than the free space on your disk, then you'll have to use a different disk. – Bill Karwin Apr 27 '17 at 07:47
  • 1
    No offense intended, but I am starting to think that you're in over your head and Stack Overflow is not the right place to teach you everything you need to know. You need to hire some professional data recovery service that also knows MySQL. – Bill Karwin Apr 27 '17 at 07:48