Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
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

3 Answers 3

up vote 0 down vote accepted

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
share|improve this answer
    
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

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

share|improve this answer
    
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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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