How to set temporary directory for MySQL?

Source : http://maisonbisson.com/blog/post/11657/mysql-error-28-temp-tables-and-running-out-of-disk-space/

Error 28 is about disk space, usually the disk space for temp tables. The first thing to do is figure out what filesystem(s) the tables are on. SHOW VARIABLES LIKE “%dir%” will return a number of results, but the ones that matter are tmpdir and datadir.
SHOW VARIABLES LIKE “%dir%”;

basedir /
character_sets_dir /usr/share/mysql/charsets/
datadir /var/lib/mysql/
innodb_data_home_dir
innodb_log_arch_dir
innodb_log_group_home_dir ./
innodb_max_dirty_pages_pct 90
slave_load_tmpdir /tmp/
tmpdir /tmp/


As it turns out, df shows plenty of space available on all the filesystems:

# df -H

Filesystem Size Used Avail Use% Mounted on
/dev/sda3 195G 11G 175G 6% /
/dev/sda1 98M 16M 78M 17% /boot
none 1.1G 0 1.1G 0% /dev/shm
/usr/tmpDSK 508M 12M 471M 3% /tmp
/tmp 508M 12M 471M 3% /var/tmp

How to set temporary directory?

# export TMPDIR = /var/tmp/Yogesh

# /etc/init.d/mysqld restart

mysql restart is a must, because temporary directory does not gets picked up dynamically, but it is picked up during startup of mysql.