When examining the MySQL data directory (/var/lib/mysql), we commonly encounter several large files:

ibdata1        # InnoDB system tablespace file
mysql-bin.000* # Binary log files

The ibdata1 file contains crucial InnoDB metadata and may store tables if configured that way. The mysql-bin.000* files are binary logs that record all data modification statements.

If you're not using MySQL replication (master/slave), binary logs serve two primary purposes:

• Point-in-time recovery
• Audit trail of database changes

To safely remove binary logs without breaking replication (since you're not using it), use these SQL commands:

-- View current binary logs
SHOW BINARY LOGS;

-- For MySQL versions before 8.0
RESET MASTER;

-- For MySQL 8.0+
PURGE BINARY LOGS BEFORE NOW();

The ibdata1 file requires special consideration because it contains:

• Data dictionary
• Change buffer
• Doublewrite buffer
• Undo logs

To shrink ibdata1, you would need to:

1. Dump all databases
2. Remove all databases and the ibdata1 file
3. Restore the dump

Example commands for this process:

# Backup all databases
mysqldump --all-databases --single-transaction > full_backup.sql

# Stop MySQL
systemctl stop mysql

# Remove data files (CAUTION)
rm -f /var/lib/mysql/ibdata1 /var/lib/mysql/ib_logfile*

# Restart MySQL (it will recreate ibdata1)
systemctl start mysql

# Restore data
mysql < full_backup.sql

To prevent future binary log accumulation, add these to my.cnf:

[mysqld]
skip-log-bin
innodb_file_per_table=1

Key configuration notes:

• skip-log-bin completely disables binary logging
• innodb_file_per_table stores each InnoDB table in its own file

For ongoing management, consider setting up:

# In my.cnf
expire_logs_days = 7
max_binlog_size = 100M

Or create a cron job:

0 3 * * * mysql -e "PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY)"

---

In MySQL's data directory (/var/lib/mysql), we typically find two types of files consuming significant disk space:

ibdata1 - InnoDB system tablespace file
mysql-bin.00000* - Binary log files

Binary logs (mysql-bin.00000*) are primarily used for replication and point-in-time recovery. If you're not using either feature, they're safe to remove. First verify if binary logging is enabled:

mysql> SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+

Never delete binary logs manually through the filesystem. Instead use these MySQL commands:

-- To remove all binary logs except the current one
mysql> RESET MASTER;

-- Alternative: Remove logs up to specific file
mysql> PURGE BINARY LOGS TO 'mysql-bin.000010';

Add these lines to your my.cnf/my.ini under [mysqld] section:

skip-log-bin
log_bin = OFF

Then restart MySQL:

sudo systemctl restart mysql

The ibdata1 file is more complex. It contains:

• InnoDB data dictionary
• Doublewrite buffer
• Insert buffer
• Rollback segments
• Undo space

To safely shrink ibdata1, you'll need to:

1. Dump all databases
2. Stop MySQL
3. Remove ibdata1 and ib_logfiles
4. Configure innodb_file_per_table
5. Restart MySQL
6. Import the data

For ongoing maintenance, set these variables in my.cnf:

expire_logs_days = 7
max_binlog_size = 100M

For InnoDB tablespace management:

innodb_file_per_table = ON
innodb_autoextend_increment = 64

Here's a bash script to automate cleanup when disk space is low:

#!/bin/bash

# Check disk space
SPACE=$(df /var/lib/mysql | awk '{print $5}' | tail -1 | cut -d'%' -f1)

if [ $SPACE -gt 90 ]; then
    mysql -e "PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 3 DAY);"
    mysql -e "FLUSH LOGS;"
    mysql -e "OPTIMIZE TABLE important_table1, important_table2;"
fi

• Always backup before performing any cleanup operations
• Monitor disk space regularly
• Consider setting up alerts for disk space thresholds
• Test procedures in staging before production