MySQL Disk Full: No Space Left on Device

# MySQL Disk Full: No Space Left on Device

Your MySQL server starts failing with errors like:

ERROR 1021 (HY000): Disk full (/tmp); waiting for someone to free some space...

Or:

ERROR 1114 (HY000): The table 'mytable' is full

Or in the error log:

[ERROR] /usr/sbin/mysqld: Disk is full writing './mydb/mytable.MYD' (Errcode: 28 - No space left on device)

A full disk is a critical situation that can corrupt your database. Here's how to recover and prevent future issues.

Understanding the Error

MySQL needs disk space for: - Data files (tables, indexes) - Binary logs - InnoDB log files - Temporary files - Undo logs

When any of these locations fills up, MySQL can't function properly.

Step 1: Assess the Situation

Check overall disk usage:

df -h

Output shows something like:

Filesystem      Size  Used Avail Use% Mounted on
/dev/sda1       100G   95G    5G  95% /
/dev/sdb1       500G  450G   50G  90% /var/lib/mysql

Check MySQL data directory size:

sudo du -sh /var/lib/mysql/*

Find large files:

sudo find /var/lib/mysql -type f -size +100M -exec ls -lh {} \;

Check MySQL's current size:

SELECT 
    table_schema AS database_name,
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables
GROUP BY table_schema
ORDER BY size_mb DESC;

Find largest tables:

SELECT 
    table_schema,
    table_name,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb,
    ROUND((data_free / 1024 / 1024), 2) AS free_mb
FROM information_schema.tables
ORDER BY (data_length + index_length) DESC
LIMIT 20;

Step 2: Immediate Free Space Options

Option A: Clean Binary Logs

Binary logs for replication can consume significant space:

-- Check binary log usage
SHOW BINARY LOGS;

# From command line
ls -lh /var/lib/mysql/mysql-bin.*

Purge old binary logs:

```sql -- Keep only last 7 days PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);

-- Keep only last 3 days PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 3 DAY);

-- Or purge to specific log file PURGE BINARY LOGS TO 'mysql-bin.000123'; ```

If not using replication, disable binary logs:

[mysqld]
# Comment out or remove
# log_bin = /var/log/mysql/mysql-bin.log

Option B: Clean Slow Query and General Logs

-- Check log settings
SHOW VARIABLES LIKE 'slow_query_log_file';
SHOW VARIABLES LIKE 'general_log_file';

# Truncate or rotate logs
sudo truncate -s 0 /var/log/mysql/mysql-slow.log
sudo truncate -s 0 /var/log/mysql/mysql.log

Option C: Clean Temporary Tables

-- Find large temporary tables
SELECT 
    table_schema,
    table_name,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables
WHERE table_name LIKE '#sql%'
ORDER BY (data_length + index_length) DESC;

# Remove orphaned temp files (be careful!)
sudo find /var/lib/mysql -name "#sql*" -type f -ls

Option D: Drop or Archive Old Data

```sql -- Drop old tables (if confirmed safe) DROP TABLE IF EXISTS old_logs_table;

-- Truncate tables (keeps structure, removes data) TRUNCATE TABLE audit_logs;

-- Archive old data to another location first CREATE TABLE archive_2025 AS SELECT * FROM logs WHERE created_at < '2026-01-01'; ```

Option E: Optimize Tables to Reclaim Space

InnoDB doesn't always release space when you delete rows:

```sql -- Reclaim space from specific table OPTIMIZE TABLE mydb.large_table;

-- For InnoDB, this rebuilds the table ALTER TABLE mydb.large_table ENGINE=InnoDB; ```

Step 3: Move MySQL Data Directory

If your disk is chronically small, move MySQL to a larger disk.

Stop MySQL:

sudo systemctl stop mysql

Check current location:

SHOW VARIABLES LIKE 'datadir';
-- Usually /var/lib/mysql

Copy data to new location:

```bash # Create new directory sudo mkdir -p /mnt/larger-disk/mysql

# Copy with permissions preserved sudo rsync -av /var/lib/mysql/ /mnt/larger-disk/mysql/

# Verify files copied sudo du -sh /mnt/larger-disk/mysql ```

Update MySQL configuration:

Edit /etc/mysql/mysql.conf.d/mysqld.cnf:

[mysqld]
datadir = /mnt/larger-disk/mysql

Update AppArmor (Ubuntu/Debian):

Edit /etc/apparmor.d/usr.sbin.mysqld:

# Change
/var/lib/mysql/ r,
/var/lib/mysql/** rwk,
# To
/mnt/larger-disk/mysql/ r,
/mnt/larger-disk/mysql/** rwk,

Reload AppArmor:

sudo apparmor_parser -r /etc/apparmor.d/usr.sbin.mysqld

Start MySQL:

sudo systemctl start mysql

Verify:

SHOW VARIABLES LIKE 'datadir';

Step 4: Move Individual Databases or Tables

For a less drastic approach, move specific large tables.

Create symlink for a table (MyISAM):

-- Check storage engine
SHOW TABLE STATUS LIKE 'large_table'\G

For InnoDB with file-per-table enabled:

```sql -- Ensure file-per-table is on SHOW VARIABLES LIKE 'innodb_file_per_table';

-- Move table to new location ALTER TABLE mydb.large_table DATA DIRECTORY = '/mnt/larger-disk/mysql-data' ENGINE=InnoDB; ```

Step 5: Prevent Future Issues

Configure Binary Log Retention

[mysqld]
# Auto-purge logs older than 7 days
expire_logs_days = 7
# Limit individual log size
max_binlog_size = 100M

Set Up Automatic Log Rotation

Create /etc/logrotate.d/mysql:

/var/log/mysql/mysql-slow.log /var/log/mysql/mysql.log {
    daily
    rotate 7
    compress
    missingok
    create 640 mysql adm
    postrotate
        /usr/bin/mysqladmin flush-logs
    endscript
}

Monitor Disk Space

Create a monitoring query:

-- Create a procedure to check disk usage
DELIMITER //
CREATE PROCEDURE check_disk_usage()
BEGIN
    SELECT 
        table_schema AS database_name,
        COUNT(*) AS tables,
        ROUND(SUM(data_length) / 1024 / 1024, 2) AS data_mb,
        ROUND(SUM(index_length) / 1024 / 1024, 2) AS index_mb,
        ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS total_mb
    FROM information_schema.tables
    GROUP BY table_schema
    ORDER BY total_mb DESC;
END //
DELIMITER ;

Set Alerts

Bash script for disk monitoring:

```bash #!/bin/bash THRESHOLD=90 USAGE=$(df /var/lib/mysql | awk 'NR==2 {print $5}' | tr -d '%')

if [ $USAGE -gt $THRESHOLD ]; then echo "MySQL disk usage at ${USAGE}% - action required" | mail -s "MySQL Disk Alert" admin@example.com fi ```

Step 6: Emergency Recovery

If MySQL won't start due to full disk:

Free minimal space from OS:

```bash # Clean package cache sudo apt clean # or sudo yum clean all

# Clean old logs sudo find /var/log -type f -name "*.gz" -delete sudo journalctl --vacuum-time=3d

# Find large files sudo du -ahx / | sort -rh | head -20 ```

Start MySQL with minimal space:

[mysqld]
# Temporarily disable binary logs
skip-log-bin
# Reduce InnoDB buffer pool
innodb_buffer_pool_size = 128M

Verification

After freeing space:

```bash # Check disk space df -h

# Check MySQL status sudo systemctl status mysql

# Verify MySQL can write mysql -u root -p -e "CREATE DATABASE test_space; DROP DATABASE test_space;"

# Check table sizes mysql -u root -p -e " SELECT table_schema, ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb FROM information_schema.tables GROUP BY table_schema ORDER BY size_mb DESC;" ```

Prevention Checklist

• [ ] Monitor disk usage with alerts at 80%
• [ ] Configure binary log expiration
• [ ] Set up log rotation
• [ ] Schedule regular OPTIMIZE TABLE for fragmented tables
• [ ] Archive or purge old data regularly
• [ ] Plan for data growth
• [ ] Consider separate disks for data and logs

Quick Reference

```sql -- Check database sizes SELECT table_schema, ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS mb FROM information_schema.tables GROUP BY table_schema;

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

-- Reclaim table space OPTIMIZE TABLE tablename;

-- Check binary logs SHOW BINARY LOGS; ```

Disk space issues are preventable with proper monitoring and maintenance. Set up alerts before you reach critical levels, and have a plan for handling data growth.