How to fix EE_DISK_FULL (20): Disk is full writing file in MySQL

-- From another terminal/connection, view active processes
SHOW PROCESSLIST;

-- Kill connections stuck on writes (replace process_id)
KILL QUERY process_id;     -- Terminates only the query
KILL CONNECTION process_id; -- Terminates the whole connection

# Check partition usage
df -h

# If MySQL data is on separate partition, check it specifically
# Linux/Unix
du -sh /var/lib/mysql/*

# Check MySQL data directory path
mysql -u root -p -e "SELECT @@datadir;"

# Find large files in data directory
find /var/lib/mysql -type f -size +1G -exec ls -lh {} \;

-- Check binary logs
SHOW BINARY LOGS;

-- On replication master: find logs already applied by all replicas
SHOW SLAVE HOSTS;  -- View connected replicas

-- Purge logs safely (keep the one currently in use)
-- Example: keep logs from mysql-bin.000010 onwards
PURGE BINARY LOGS TO 'mysql-bin.000010';

-- Or purge by date (delete logs older than specific date)
PURGE BINARY LOGS BEFORE '2025-01-01';

-- On replica, check which master log is applied
SHOW SLAVE STATUS \G
-- Look for Master_Log_File and Read_Master_Log_Pos

sudo systemctl restart mysql

# Check temp directory
mysql -u root -p -e "SELECT @@tmpdir;"

# Remove MySQL temp files (be careful not to delete OS temp files)
cd /tmp  # or wherever tmpdir points
ls -lah | grep -E '#sql|tmp|temp'

# Safe removal of crashed/abandoned temp tables
rm -f /tmp/#sql*

# Check InnoDB temp tablespace
# MySQL 8.0+ uses ibtmp1 file - can be large if crashed
ls -lh /var/lib/mysql/ibtmp1

# If ibtmp1 is very large, you can remove it after MySQL restart
# MySQL will recreate it

# Find all large files in data directory
find /var/lib/mysql -type f -size +500M

# Archive or delete old backups
ls -lah /backups/

# LVM expansion (if using LVM)
# First, extend the volume
sudo lvextend -L +100G /dev/mapper/vg0-lv_data

# Resize the filesystem
sudo resize2fs /dev/mapper/vg0-lv_data

# Verify
df -h

# Stop MySQL
sudo systemctl stop mysql

# Create new directory on larger partition
sudo mkdir -p /large_disk/mysql_data
sudo chown mysql:mysql /large_disk/mysql_data
sudo chmod 700 /large_disk/mysql_data

# Copy data
sudo cp -av /var/lib/mysql/* /large_disk/mysql_data/

# Update MySQL config
sudo nano /etc/mysql/mysql.cnf
# Change: datadir = /large_disk/mysql_data

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

# Start MySQL
sudo systemctl start mysql

-- Check current setting
SHOW VARIABLES LIKE 'expire_logs_days';
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';

-- Set expiration (MySQL 8.0.1+, more precise)
SET GLOBAL binlog_expire_logs_seconds = 604800; -- 7 days

-- For older MySQL versions use:
SET GLOBAL expire_logs_days = 7;

-- Make it permanent in /etc/mysql/mysql.cnf
[mysqld]
binlog_expire_logs_seconds = 604800  # 7 days
# OR (older versions)
expire_logs_days = 7

sudo systemctl restart mysql

-- Check if disk-full message stops appearing
-- Watch error log in real-time (Linux)
tail -f /var/log/mysql/error.log

-- Verify stuck transactions resume
SHOW PROCESSLIST;  -- Blocked queries should complete

-- Check InnoDB status
SHOW ENGINE INNODB STATUS \G
-- Should show normal activity, not disk-full messages

-- Once recovered, test a simple write
INSERT INTO test_table VALUES (1);