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?

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

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
  • 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 '13 at 8:45
  • No work for me. Got the same error. – bronze man Mar 28 '20 at 8:23

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).

  • 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 '13 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 '13 at 15:01

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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