📦 Moving MySQL tmpdir to /home

By /

Introduction

In certain server environments, MySQL may encounter problems due to limited space in /tmp or high memory usage that forces the server to rely on swap. This is especially true for operations involving large temporary tables (e.g., ALTER TABLE, GROUP BY, ORDER BY). A practical solution is to move MySQL’s temporary directory (tmpdir) to a larger partition such as /home.

🛠️ Procedure

Step 1: Adjusting Settings for MariaDB (If Applicable)

If you’re using MariaDB (common on many cPanel servers), you must allow MariaDB access to /home.

  1. Create a systemd override directory and file: mkdir -p /etc/systemd/system/mariadb.service.d nano /etc/systemd/system/mariadb.service.d/override.conf
  2. Add the following configuration: [Service] ProtectHome=false
  3. Reload systemd and restart MariaDB: systemctl daemon-reexec /scripts/restartsrv_mysql

Step 2: Moving MySQL tmpdir to /home

Whether you’re using MySQL or MariaDB, continue with these steps:

  1. Create a new temporary directory: mkdir /home/mysqltmp chown mysql:mysql /home/mysqltmp chmod 750 /home/mysqltmp
  2. Modify the MySQL configuration:
    Open /etc/my.cnf in your preferred editor and add the following line under the [mysqld] section: [mysqld] tmpdir=/home/mysqltmp
  3. Restart MySQL to apply the changes: /scripts/restartsrv_mysql

Step 3: Verify the Configuration

To confirm the new temporary directory is in use:

mysqladmin variables | grep tmpdir

Expected output:

| slave_load_tmpdir                       | /home/mysqltmp |
| tmpdir                                  | /home/mysqltmp |

✅ Conclusion

Moving MySQL’s tmpdir to /home can significantly enhance performance and stability on systems where /tmp is constrained or when working with large datasets. Just be sure to:

  • Set correct permissions.
  • Avoid using user-writable locations (e.g., /home/user/tmp) to prevent security risks.
  • Monitor disk usage regularly.

This is a safe and effective optimization when implemented properly on cPanel servers running AlmaLinux 9 / CloudLinux 9 with LiteSpeed and MariaDB/MySQL.

Scroll to Top