记一次MySQL磁盘空间满处理方式

Reason

1. Binlog files are so huge.
2. Undo logs are so huge.
3. Databases are huge.

Describe

Binlog

Binlog which is containing all the commands during every SQL query. These logs improve the performance when transactions rollback. The backward is obvious, causing tons of space wasted.

undo log

When running an InnoDB transaction, every DML command will log into undo log. If a transaction is interrupted, undo log plays a key role in the rollback procedure.

Solution

1. Purge Binlog

Way1：annote lines in config file my.cnf

#log-bin=mysql-bin  #annote this line
#binlog_format=mixed  #annote this line

Stop the service and purge the logs manually, and then restart the service.

Way2：purge specific binlog by time

show binary logs;   #get binlog detail
PURGE MASTER LOGS TO 'binlog.000002';   #delete all binlog before binlog.000002
PURGE MASTER LOGS BEFORE '2019-01-22 14:00:00';  #delete all binlog before 2019-01-22 14:00:00
PURGE MASTER LOGS BEFORE DATE_SUB(NOW(), INTERVAL 1 DAY);  #delete all binlog before 1 day

2. Undo logs

Shutting down the service and deleting the undo logs is fine.

3. Database is too heavy

Using optimize table command, but it's unsure whether just defraging the table or rebuild the whole table.

What if it's rebuilding the whole table, it will cost double size of the table to finish the process.

The Best Way：using mysqldump to save the database structure locally, and re-import the database structure after dropping the old one.