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 

 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 

 service mysql start 

mysql> SHOW BINARY LOGS;

1. 

   Tyler Teh · septembre 8, 2023 at 4:07 am · Répondre

   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.
   • 

     Christophe Casalegno · septembre 8, 2023 at 11:04 am · Répondre

     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!