While setting up a replicated MySQL database I looked at the manual about tmpdir and was somewhat confused about the two variables tmpdir and slave_load_tmpdir. I know the slave needs some data to be persistent between restarts, but I am not sure if only setting slave_load_tmpdir is enough or if both should be set to a persistent directory:
About tmpdir, the manual says:
The directory used for temporary files and temporary tables. [...]
If the MySQL server is acting as a replication slave, you should not set tmpdir to point to a directory on a memory-based file system or to a directory that is cleared when the server host restarts. A replication slave needs some of its temporary files to survive a machine restart so that it can replicate temporary tables or LOAD DATA INFILE operations. If files in the temporary file directory are lost when the server restarts, replication fails. You can set the slave's temporary directory using the slave_load_tmpdir variable. In that case, the slave will not use the general tmpdir value and you can set tmpdir to a nonpermanent location.
About slave_load_tmpdir, the manual says:
The name of the directory where the slave creates temporary files. This option is by default equal to the value of the tmpdir system variable. When the slave SQL thread replicates a LOAD DATA INFILE statement, it extracts the file to be loaded from the relay log into temporary files, and then loads these into the table. [...]
The directory specified by this option should be located in a disk-based file system (not a memory-based file system) because the temporary files used to replicate LOAD DATA INFILE must survive machine restarts. The directory also should not be one that is cleared by the operating system during the system startup process.
So a slave needs a persistent temporary directory to "replicate temporary tables or LOAD DATA INFILE operations", but slave_load_tmpdir says nothing about temporary tables, only LOAD DATA INFILE (also reflected in its name). On the other hand, the manual states that if slave_load_tmpdir is persistent, tmpdir can be in a nonpermanent location. Does anyone have any insight into this?
