安全删除mysql的binlog日志

出于公司的业务原因,既不能保留固定天数的日志,又不能让磁盘空间站满,所以只能手动删除binlog

使用mysql自带的 PURGE命令

PURGE MASTER LOGS TO ‘mysql-bin.000002‘; 删除002之前(不包括002)的binlog日志,


[root@DBmaster 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‘;


[root@DBmaster 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‘;


[root@DBmaster 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

[root@DBmaster 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日志

上一篇:sql列转横并拼接字符串


下一篇:sql优化