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)

Related Posts via Taxonomies

• Debian 6 : preparing the server for higher score in Page Speed and YSlow (4)
• Controlling your visitors/users bandwidth usage and speed using mod_cband (4)
• Create SOCKS5 server (4)
• disable IPv6 on kernel level in Debian 6 (and it’s derivatives) (4)
• Installing more updated version of ffmpeg on Debian 5 (Lenny)‏ (4)