Running mysqldump on a debian system that has gone into read-only mode

Question

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?

I get: **mount: can't find /tmp in /etc/fstab or /etc/mtab** — Nada_Surf, Oct 09 '13 at 12:21

score 1 · Accepted Answer · answered Oct 09 '13 at 21:59

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

answered Oct 09 '13 at 21:59

Brian Showalter

1,029
9
13

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

score 0 · Answer 2 · answered Oct 09 '13 at 12:33

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

answered Oct 09 '13 at 12:33

sCiphre

464
3
6

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

score 0 · Answer 3 · answered Oct 09 '13 at 21:50

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.

Running mysqldump on a debian system that has gone into read-only mode

3 Answers3