Fix MySQL 'Error writing file' (Errcode: 28) – No Space Left on Device

The Error

Error writing file '/tmp/MYxxxxxx' (Errcode: 28 - No space left on device)

This fires when a query needs to write temp files — for sorting, GROUP BY, ORDER BY, or large JOINs — but the target partition has no room left. MySQL names these temp files MYxxxxxx and writes them to tmpdir, which defaults to /tmp on Linux. Once that partition hits 100%, every query that touches disk dies immediately with Errcode 28. No retries, no graceful degradation — just an instant failure.

Step 1 – Confirm the partition is actually full

df -h

Check for the partition at /tmp or / showing 100%. Don't stop there — also check inodes:

df -i

A partition can exhaust inodes long before it runs out of bytes. If IUse% reads 100%, the fix is the same: delete files. Available bytes don't matter when there are no inodes left to create new ones.

Step 2 – Find and clear the junk

Clear old MySQL temp files left by crashed queries

# Stop MySQL first — don't remove active temp files
sudo systemctl stop mysql

# Remove stale temp files
sudo rm -f /tmp/MY*

# Restart
sudo systemctl start mysql

Find the biggest files on the full partition

sudo du -sh /* 2>/dev/null | sort -rh | head -20

On a busy server, this often surfaces MySQL binary logs sitting at 20–50 GB, application logs that were never rotated, or old backup dumps someone forgot to clean up.

Purge old binary logs (if binlog is enabled)

-- See exactly how much space binlogs are using
SHOW BINARY LOGS;

-- Purge logs older than 3 days
PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY;

Or from the shell:

sudo find /var/lib/mysql -name 'mysql-bin.*' -mtime +3 -delete

Truncate bloated log files

sudo truncate -s 0 /var/log/mysql/error.log
sudo journalctl --vacuum-size=200M

Step 3 – Move tmpdir to a larger partition (permanent fix)

Small /tmp partitions — often just 1–5 GB on default Linux installs — fill fast when MySQL processes large result sets. Point tmpdir somewhere with real headroom.

Create a dedicated temp directory

sudo mkdir -p /var/lib/mysql-tmp
sudo chown mysql:mysql /var/lib/mysql-tmp
sudo chmod 750 /var/lib/mysql-tmp

Update my.cnf / my.ini

[mysqld]
tmpdir = /var/lib/mysql-tmp

On Ubuntu/Debian the config lives at /etc/mysql/mysql.conf.d/mysqld.cnf. On CentOS/RHEL it's /etc/my.cnf.

Restart MySQL

sudo systemctl restart mysql

Step 4 – Reduce how much temp space queries need

No extra disk space available right now? You can shrink MySQL's appetite for temp files by pushing more work into RAM.

Increase in-memory sort buffers

[mysqld]
tmp_table_size      = 256M
max_heap_table_size = 256M
sort_buffer_size    = 4M

These are session-level values, so you can tune them for a single heavy query without restarting MySQL:

SET SESSION tmp_table_size = 268435456;
SET SESSION sort_buffer_size = 4194304;
-- then run your heavy query

Track down the offending query

SHOW PROCESSLIST;

Look for queries with State: Copying to tmp table or Creating sort index — those are the disk-spill candidates. Run EXPLAIN on them. A missing index on the sort column often eliminates temp file usage entirely.

Verify the fix

# 1. Confirm tmpdir is set correctly
SHOW VARIABLES LIKE 'tmpdir';

# 2. Check free space on the new tmpdir partition
df -h /var/lib/mysql-tmp

# 3. Re-run the failing query — it should complete without error

Want to watch temp files appear in real time while the query runs? This one-liner does it:

watch -n1 'ls -lh /var/lib/mysql-tmp/'

Prevent it from happening again

Alert at 80%, not 100% — by the time the partition is full, queries are already failing. Set a disk alert on the tmpdir partition at 80% so you have time to react.
Auto-expire binary logs — add binlog_expire_logs_seconds = 259200 (3 days) to my.cnf. Without this, binlogs accumulate indefinitely and can easily consume 20+ GB on a write-heavy server.
Rotate MySQL error logs — configure logrotate for /var/log/mysql/error.log. A busy server can generate hundreds of MB in logs per day; without rotation it adds up fast.
Fix the query, not just the disk — a query dumping gigabytes to /tmp usually has a missing index. Run EXPLAIN, find the full table scan, and add the right index. That kills the symptom at the source.