How to fix ERROR 1021: Disk full; waiting for someone to free some space in MySQL

df -h

Filesystem      Size  Used Avail Use% Mounted on
/dev/sda1       100G   99G  1.0G  99% /
/dev/sda2        50G   50G     0  100% /var/lib/mysql
tmpfs            16G   16G     0  100% /tmp

mysql -u root -p

SELECT table_name,
       ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)',
       ROUND(data_length / 1024 / 1024, 2) AS 'Data (MB)',
       ROUND(index_length / 1024 / 1024, 2) AS 'Index (MB)'
FROM information_schema.tables
WHERE table_schema != 'information_schema'
  AND table_schema != 'mysql'
  AND table_schema != 'performance_schema'
ORDER BY (data_length + index_length) DESC
LIMIT 20;

-- Delete records older than 30 days
DELETE FROM logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

-- Or clear specific archive tables
TRUNCATE TABLE archived_transactions;

-- After deleting, optimize the table to reclaim space
OPTIMIZE TABLE logs;

-- Show current binary logs
SHOW BINARY LOGS;

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

-- Or purge before a specific date
PURGE BINARY LOGS BEFORE '2024-01-15';

[mysqld]
binlog_expire_logs_seconds = 604800  # 7 days in seconds
# Or use the older directive (deprecated in MySQL 8.0.3)
expire_logs_days = 7

sudo systemctl restart mysql

# Show temp directory usage
du -sh /tmp

# Remove MySQL temporary files (if MySQL is stopped)
sudo rm -rf /tmp/#sql_*

# Or use tmpwatch to safely remove old files (older than 24 hours)
sudo tmpwatch 24 /tmp

# Create a new tmpdir with adequate space
sudo mkdir -p /mnt/bigssd/mysql_tmp
sudo chown mysql:mysql /mnt/bigssd/mysql_tmp
sudo chmod 750 /mnt/bigssd/mysql_tmp

[mysqld]
tmpdir = /mnt/bigssd/mysql_tmp

sudo systemctl restart mysql

# For AWS EBS, expand the volume in the console, then resize the filesystem
sudo resize2fs /dev/xvda1

# For Azure managed disks, expand in the portal, then resize
sudo parted /dev/sda
# Type: resizepart
# Number: 1
# End: 100%
sudo resize2fs /dev/sda1

# Stop MySQL
sudo systemctl stop mysql

# Move data directory
sudo mv /var/lib/mysql /mnt/large_disk/mysql

# Update MySQL config
# Edit /etc/mysql/my.cnf and change:
# datadir = /mnt/large_disk/mysql

# Fix permissions
sudo chown -R mysql:mysql /mnt/large_disk/mysql

# Restart MySQL
sudo systemctl start mysql

# Add to crontab (crontab -e)
0 */4 * * * df -h | grep -vE '^Filesystem|tmpfs|cdrom' | awk '{ print $5 " " $1 }' | while read output; do
  usage=$(echo $output | awk '{ print $1}' | cut -d'%' -f1)
  partition=$(echo $output | awk '{ print $2 }')
  if [ $usage -ge 80 ]; then
    echo "WARNING: $partition is $usage% full" | mail -s "Disk Alert" [email protected]
  fi
done