安全刪除mysql的binlog日誌

標籤：mysql

出於公司的業務原因，既不能保留固定天數的日誌，又不能讓磁碟空間站滿，所以只能手動刪除binlog

使用mysql內建的 PURGE命令

PURGE MASTER LOGS TO ‘mysql-bin.000002‘; 刪除002之前（不包括002）的binlog日誌，

[[email protected] data]# ls

DBmaster.test.err  ib_logfile0    localhost.pid     mysql-bin.000002  mysql-bin.000005  performance_schema  wine_test

DBmaster.test.pid  ib_logfile1    mysql             mysql-bin.000003  mysql-bin.000006  test

ibdata1            localhost.err  mysql-bin.000001  mysql-bin.000004  mysql-bin.index   wine_cn

mysql> PURGE MASTER LOGS TO ‘mysql-bin.000002‘;

[[email protected] data]# ls

DBmaster.test.err  ib_logfile0    localhost.pid     mysql-bin.000003  mysql-bin.000006    test

DBmaster.test.pid  ib_logfile1    mysql             mysql-bin.000004  mysql-bin.index     wine_cn

ibdata1            localhost.err  mysql-bin.000002  mysql-bin.000005  performance_schema  wine_test

PURGE MASTER LOGS TO ‘mysql-bin.000005‘;

[[email protected] data]# ls

DBmaster.test.err  ibdata1      ib_logfile1    localhost.pid  mysql-bin.000005  mysql-bin.index     test     wine_test

DBmaster.test.pid  ib_logfile0  localhost.err  mysql          mysql-bin.000006  performance_schema  wine_cn

[[email protected] data]# 

#!/bin/bash

# Date: 2014-06-11  

# Database delete  -ge 30day binlog feil

cd /DBdata/mysql

#logname=( `find  ./  -maxdepth 1  -type f -mtime +30 -name "mysql-bin.*" |sed  -n "1p;\$p"|awk -F ‘/‘ ‘{print $2}‘`)

logname=`find  ./  -maxdepth 1  -type f -mtime +30 -name "mysql-bin.*" |sed  -n ‘$p‘|awk -F ‘/‘ ‘{print $2}‘`

echo $logname

mysql -uroot -p123456 -e " PURGE MASTER LOGS TO ‘$logname‘;"

mysql> CREATE  USER  ‘binlog‘@‘localhost‘  IDENTIFIED  BY  ‘mypass123‘;

Query OK, 0 rows affected (0.05 sec)

mysql> GRANT  SUPER   ON *.*   TO  ‘binlog‘@‘localhost‘;

Query OK, 0 rows affected (0.00 sec)

本文出自 “小盒” 部落格，請務必保留此出處http://zhangxiaohe.blog.51cto.com/7821029/1545478

安全刪除mysql的binlog日誌