menu LittleJake's Blog
Jake Liu
Never Settle
creative commons by-nc-sa

© 2024 LittleJake's Blog.




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



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.


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.

Buy me a beer
Jake Liu
Never Settle

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

Author: Jake Liu


Creative Commons License

This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0) For any re-post you must give appropriate credit.

文章遵循CC许可 署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0) 转载请注明出处

Tag:MySQL, BinLog, undo, innodb


Add a new comment.