mysqldump failed to create backup with error “mysqldump: Error: Can’t create/write to file /tmp/”‏

System:

  • Debian 5 (Lenny)
  • MySQL 5.1.51
  • login as root

Problem:
mysqldump failed to create database backup even I’m logging-in as root. Error example:

[root@nq ~] mysqldump -u mv -p mv>mv.sql
Password:
mysqldump: Error: 'Can't create/write to file '/tmp/#sql_3c4_2.MYI' (Errcode: 13)' when trying to dump tablespaces
mysqldump: Couldn't execute 'show fields from `logs`': Can't create/write to file '/tmp/#sql_3c4_0.MYI' (Errcode: 13) (1)

It looks like mysqldump trying to write to /tmp even if  I’m tell it to save to my home (/root).
Apparently this problem also happen inside mysql console when running ‘describe’ command, example output inside mysql console:

mysql> describe logs;
ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_3c4_0.MYI' (Errcode: 13)
mysql>

Cause:
Apparently, mysqldump and ‘describe’ command need to write temporary file to /tmp
all mysql commands is ran by mysql server user, not as user currently logging-in (even root), so, mysql server user (in Debian: mysql) need to have permission to write to /tmp

Solution:

chmod 777 /tmp

Try it! (No need to restart mysql)

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>