Mysql : how to purge binary logs when you have no space left on device ?
When you need to purge binary logs on a MySQL server, you just need to connect to your server and use the PURGE BINARY LOGS command like this :
mysql> PURGE BINARY LOGS BEFORE 'yyyy-mm-dd hh:mm:ss'; For example :
mysql> PURGE BINARY LOGS BEFORE '2017-03-02 22:46:26'; Will purge all the binary logs before the date. Other method is to purge log file on « name » attribute of the log file. Example :
mysql> PURGE BINARY LOGS TO 'ns30XXXX-bin.009836';But sometime you can have bigger problem : no space left on device and you can’t free space with other method. In this case, the only solution is to drop manually the binary logs files. Assume your binary logs can be find in your /var/lib/mysql you can do this to fix your problem :
First step : stop mysql (syntax can depend of your linux distribution version). Example :
service mysql stop Second step : count and delete 50% of the binary logs to free space and remove them from the Mysql binary logs index
cd /var/lib/mysql && a=`ls |grep -v relay |grep bin.index` && b=`wc -l <$a` ; c=`echo $(($b/2))` |xargs -l rm ; echo $c | head -n $b $a |cut -d "/" -f2 && sed 1,$c\d $a -i Third step : just start MySQL
service mysql start Fourth step : verify there is no problem
mysql> SHOW BINARY LOGS;If you see file with a value of « 0 » there is a problem : you need to stop mysql and remove the file from the index manually. If all is ok you can now use the « PURGE BINARY LOGS BEFORE » mysql command to clean other logs if you need.
Enjoy !
Christophe Casalegno
​Vous pouvez me suivre sur : Twitter | Facebook | Linkedin | Telegram | Youtube
Hi, Same as Tyler Teh up there.
Thank you very much, you saved my day.Good news!
Hi, I encountered the exactly same situation like what your page title said.
I google for solution urgently and saw the light from here.
Thank you very much, you saved my day.Due to my lack of understanding and security concerns, I didn’t run your command at second step.
Your article gave me the idea how to do it manually:
I sudo remove few oldest binary logs, then sudo vim binlog.index to remove those deleted logs from the list.
Thank you.Dear Tyler,
Thank you for taking the time to write such a positive comment. I’m delighted to hear that the article was helpful to you and provided a solution to your issue.
There’s always more than one way to solve a problem: kudos to you for figuring it out!
I understand the security concerns related to running commands on a production server, and it’s always wise to be cautious.
Your method of manually editing the files and index is a good workaround when you’re not comfortable running scripts or complex command. This post was old, and I’ll probably update it.
For example, you can gain instant space on your server with : tune2fs -m 0 (or 1, 2, 3, 4) /dev/yourdisk (generally it is 5% by default) if you are on a extxx filesystem.
If you have any further questions or run into more challenges, please don’t hesitate to reach out. Have a nice day!
4 Commentaires