Changing the tmp folder of mysql

if you dont have apparmor or selinux issues, but still get errorcode 13's:

mysql must be able to access the full path. I.e. all folders must be mysql accessible, not just the one you intend in pointing to.

example, you try using this in your mysql configuration: tmp = /some/folder/on/disk

# will work, as user root:
mkdir -p /some/folder/on/disk
chown -R mysql:mysql /some

# will not work, also as user root:
mkdir -p /some/folder/on/disk
chown -R mysql:mysql /some/folder/on/disk

This maybe helpful for MySql with AppArmor

stop mysql :

sudo /etc/init.d/mysql stop

Create directory called /somewhere/tmp

Edit Config:

sudo vim /etc/mysql/my.cnf # or perhaps sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

change

tmpdir = /somewhere/tmp/

Then

sudo vim /etc/apparmor.d/usr.sbin.mysqld

Add

# Allow data dir access
  /somewhere/ r,
  /somewhere/** rwk,

sudo chown -R root:root /somewhere

sudo chmod -R 1777 /somewhere

Restart

 sudo /etc/init.d/apparmor reload

 sudo /etc/init.d/mysql restart

You can also set the TMPDIR environment variable.

In some situations (Docker in my case) it's more convenient to set an environment variable than to update a config file.

Works for 5.7 on centos 8

mkdir /tmp/1 /tmp/1

semanage fcontext -a -t mysqld_db_t "/tmp/1(/.*)?"
                   
restorecon -Rv /tmp/1

semanage fcontext -a -t mysqld_db_t "/tmp/2(/.*)?"

restorecon -Rv /tmp/2

to my.cnf tmpdir=/tmp/1:/tmp/2

sudo service mysql restart

If you are a MariaDB user, all this above apply, by don't forget to unlock the "home" protection by doing this.

touch /etc/systemd/system/mariadb.service.d/override.conf
nano /etc/systemd/system/mariadb.service.d/override.conf

Inside override.conf put this content and save.

[Service]
ProtectHome=false

Then run the following commands :

systemctl daemon-reload
/scripts/restartsrv_mysql

After restarting mysql, the variables can be checked by :

mysqladmin variables|grep tmp