When you need to purge binary logs on a , you just need to connect to your and use the PURGE BINARY LOGS 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 on “name” attribute of the log . 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 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 | -v relay | bin.index` && b=`wc -l <$a` ; c=`echo $(($b/2))` |xargs -l rm ; echo $c | head -n $b $a |cut -d "/" -f2 && 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.

Print Friendly, PDF & Email



Bài viết liên quan