Introduction
Some servers set up either have very large tables and require larger temp space or there isn't enough memory and causes swap usage to use more temp space. This can be alleviated at times by moving the MySQL tmpdir from /tmp into /home.
Procedure
If the cPanel server is using MariaDB as the MySQL server then the following should be done first.
Create file /etc/systemd/system/mariadb.service.d/override.conf and add the following contents.
[Service]
ProtectHome=false
Then run the following
systemctl daemon-reload
/scripts/restartsrv_mysql
If you are using MySQL RPMs and not MariaDB then start here. If you're using MariaDB then continue with the following changes after disabling ProtectHome.
These are the steps needed to move the MySQL server tmpdir to /home:
mkdir /home/mysqltmp
chown mysql:mysql /home/mysqltmp
Then put the following in /etc/my.cnf under [mysqld]
tmpdir=/home/mysqltmp
and restart MySQL.
/scripts/restartsrv_mysql
When you run
mysqladmin variables|grep tmpdir
you should see this
vps root # mysqladmin variables|grep tmpdir
| slave_load_tmpdir | /home/mysqltmp |
| tmpdir | /home/mysqltmp |
vps root #