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
4 Commentaires