Christophe Casalegno

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


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

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *