2

An old Debian system of mine has recently had the primary hard drive go into read-only mode due to some disk problem. I have a MySQL database on the server that I would like to backup before trying to repair the harddrive.

Luckily I have another drive mounted which is not in read-only mode, so I should be able to run a mysqldump to dump the database to it. However, I get the below error running mysqldump:

mysqldump: Couldn't execute 'show fields from `xxx`': Can't create/write to file '/tmp/#sql_b96_0.MYI' (Errcode: 30) (1)

This is obviously due to the fact that the primary disk is in read-only mode.

Is there any way around this?

Improve this question
2
  • 1
    Try mount -o remount,rw /tmp
    – Nathan C
    Oct 9, 2013 at 11:28
  • I get: mount: can't find /tmp in /etc/fstab or /etc/mtab
    – Nada_Surf
    Oct 9, 2013 at 12:21

3 Answers 3

Reset to default
1

Use the TMPDIR environment variable to tell mysqldump where to put its temporary files, and point it to a writable location.

export TMPDIR=/mnt/writable_drive/tmp ; mysqldump dbname >/mnt/writable_drive/backup.sql
Improve this answer
2
  • Thanks, this worked! I had to make sure the new location was writable by the 'mysql' user. (chown -R mysql:mysql TMPDIR)
    – Nada_Surf
    Oct 10, 2013 at 8:45
  • No work for me. Got the same error.
    – bronze man
    Mar 28, 2020 at 8:23
0

mysqldump is trying to create some temp files on your dead disk.

Try to move /tmp to the new hard disk:

mkdir /mnt/newhdd/tmp
mount --bind /mnt/newhdd/tmp /tmp

This should allow your system to run normally until you complete the dump.

Other good options would be to: dump from a remote machine, or stop mysqld and just copy the mysql files over to a new system (which is good to do even if mysqldump works, ESPECIALLY if you have table problems).

Improve this answer
2
  • Thanks, I tried the mkdir and mount commands. They ran fine, but the "can't read/write" error still occurs. I cannot dump from a remote machine because the MySQL server may only be accessed locally and I cannot copy the Mysql files relaibly because the tables are all InnoDB
    – Nada_Surf
    Oct 9, 2013 at 14:20
  • You did replace /mnt/newhdd with the correct path to the new hard disk, right? - Also, check if user mysql has write access to the new /tmp
    – sCiphre
    Oct 9, 2013 at 15:01
0

If you are unable to simply mount a rw filesystem on /tmp and get mysql happy, then you have some other options:

1) use the remote mysql commands; i.e. login remotely and save remotely

2) stream all output from mysqldump to a filesystem that is rw

3) tar up the entire mysql db files, once mysqld is stopped, and re-use them on a healthy system.

4) buy a new drive, attach it, boot up on a LiveCD or bootable flash, mount old and new, use dd to recreate the old on the new.

....maybe 10 other ways too.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .