If you want to change the directory that the MySQL server uses to create and store temporary files (usually the default directory is /tmp), you must set the tmpdir option, either by by putting a line like 

tmpdir = /new/tmp/dir

in the MySQL config file (located in Ubuntu at /etc/mysql/my.cnf) under the [mysqld] section, or by starting the MySQL server with one of the --tmpdir=/new/tmp/dir or -t /new/tmp/dir command line options.

Note that the MySQL server (running with the mysql user and group) must have read and write permissions to the new tmpdir, otherwise you will get a /usr/sbin/mysqld: Can't create/write to file '/new/tmp/dir/ibpgdyOX' (Errcode: 13) error. There are two ways to give the mysql user permissions:

  1. Change the owner user and group of the folder to mysql: # chown mysql:mysql /new/tmp/dir. This option is useful when only the MySQL server will use the folder.

  2. Make the folder world readable and writable: # chmod 1777 /new/tmp/dir. Note that this command also sets the sticky bit on the directory. This is a security measure, and it means that files and folders in this directory can be unlinked or removed only by their owners. This option is useful when multiple processes will use the path as a temporary directory.

If after giving correct permissions on the new tmpdir to the mysql user, you still get the (Errcode: 13) error, and the new tmpdir is not a standard temporary directory (such as /tmp or /var/tmp), this may be caused by AppArmor (which is enabled by default since Ubuntu 7.10) not giving the MySQL server access rights to the tmpdir.

To fix this, you must modify the MySQL security profile located at /etc/apparmor.d/usr.sbin.mysqld. Add the lines: 

/new/tmp/dir rw,
owner /new/tmp/dir/** rwkl,

The first line gives read(r) and write(w) access to the folder, the second line gives read(r), write(w), lock(k) and link(l) access to all the files and folders inside the folder owned by the mysql user.
To apply the changes, restart AppArmor with # service apparmor restart

Note that the tmpdir option accepts multiple paths (separated by ":" on Unix/Linux and ";" on Windows, documentation). If you specify multiple paths, you must apply the procedures above for each component.

Spread the word