【MYSQL】binlog安全清理的两种方法

通常在交付MYSQL数据库前会将日志目录与数据文件分开，为其单独设立一个文件系统，这样便于掌握日志与数据的空间使用情况。如果不是业务突然增长，binlog会按照默认设置的过期时间自动被清理，但是有时候业务量增长是很突然的，比如上线了一个活动等，所以设置binlog自动清理是每个MYSQL管理员必须要做的一件事情。

两种binlog清理方法的选择

按MYSQL8.0官方手册的说法，purge binary log 和 expire_logs_senconds 都可以安全清理binlog文件，那么到底该选择哪一种呢？
1、选择参数expire_logs_senconds。对于大公司、大企业来说，交付的数据库数量较多，数据库通常有统一的部署规范，这种情形就可使用本参数来设置所有的数据库binlog自清理周期，例如本公司大基线要求是7天。
2、选择命令purge binary log。这种方式比较适合临时清理一下的场景，比如自清理脚本。例如某应用binlog文件突增，触发自清理条件就会清理，但不会修改过期参数expire_logs_senconds，当业务量下来是binlog又会保留的久一点。

值得注意的是，官方手册中有一句话 “为了手动清理binlog日志文件，请用 purge binary log 命令”

清理方法1（purge binary log）

清理时，PURGE BINARY LOGS和PURGE MASTER LOGS这两个命令任选其一即可，PURGE命令会根据mysql-bin.index的内容来确定被清理的binlog日志文件。

The PURGE BINARY LOGS statement deletes all the binary log files listed in the log index file prior to the specified log file name or date. BINARY and MASTER are synonyms. Deleted log files also are removed from the list recorded in the index file, so that the given log file becomes the first in the list.

Examples:

PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2019-04-02 22:46:26';

如果从库正在复制主库binlog的情况下，你执行PURGE命令，这时不会删除正在被占用的binlog日志文件；但时如果主从断掉的情况下，你执行PURGE 命令，就无法自动恢复主从同步。

PURGE BINARY LOGS is safe to run while replicas are replicating. You need not stop them. If you have an active replica that currently is reading one of the log files you are trying to delete, this statement does not delete the log file that is in use or any log files later than that one, but it deletes any earlier log files. A warning message is issued in this situation. However, if a replica is not connected and you happen to purge one of the log files it has yet to read, the replica cannot replicate after it reconnects.

清理方法2（expire_logs_senconds）

当启动数据库服务或者刷新binlog时，MYSQL会根据参数值binlog_expire_logs_seconds（默认30天）自动清理binlog文件。

Binary log files are automatically removed after the server’s binary log expiration period. Removal of the files can take place at startup and when the binary log is flushed. The default binary log expiration period is 30 days. You can specify an alternative expiration period using the binlog_expire_logs_seconds system variable. If you are using replication, you should specify an expiration period that is no lower than the maximum amount of time your replicas might lag behind the source.

自清理脚本（shell）

在这里附上我为本公司编写的binlog自清理脚本，脚本以crontab的形式部署在交付出去的MYSQL服务器上，包括主备机、单机。脚本中的清理命令虽然只有寥寥几行，但是附加的文件系统使用率、空间大小、数据库版本、同步延迟时间等检验条件以及邮件报警却丰富了脚本的多样性。

#!/bin/ksh
#本脚本必须使用系统用户执行
#功能：检查所有mysql8.0版本的单机或主库的binlog占用文件系统大小。若达到清理条件则开始执行清理脚本，清理前备份binlog文件，清理完邮件报警给数据库管理员。
#注意：部署时使用sh 脚本名，否则=~符号会报错。
export LC_ALL=C

manageruser=xxxxxx
managerpass=xxxxxx
useage_limited=90
idle_size_limited=10

mysql_host="xxx.xxx.xxx.xxx"
mysql_user=xxxxxx
mysql_pass=xxxxxx

datetime=`date +"%Y%m%d_%H%M%S"`
yesterday=`date '+%Y-%m-%d %H:%M:%S' -d '1 days ago'`
ipaddr=`ifconfig -a|grep inet|grep -v 127.0.0.1|grep -v 0.0.0.0|grep -v inet6|awk '{print $2}'|tr -d "addr:"`

function getFileSystem(){
	F_PATH=/data/mysql_8.0_3306/log/bin_log
	FS_RESULT="/"
	for fs in `df |grep "/"|awk '{print $6}'`
	do
		if [[ $F_PATH/ =~ ${fs}/ ]]; then
			 FS_RESULT=$fs
		fi
	done
	echo $FS_RESULT
}

function get_binlog_useage(){
	useage=`df -h|grep $1|awk '{print $5}'|awk -FG '{print $1}'|awk '{print int($0)}'|sed -n '1p'`
	echo $useage
}

function get_binlog_idle_size(){
	idle=`df -h|grep $1|awk '{print $4}'|awk -FG '{print $1}'|awk '{print int($0)}'|sed -n '1p'`
	echo $idle
}

function get_behind_seconds(){
	standbyip=$(get_standby_ip $ipaddr)
	#echo "${standbyip}"
	if [ ! -n "${standbyip}" ]; then
        echo "单机或者备库[$ipaddr]，可直接清理..."
		#echo 0
	else
		SECONDS_BEHIND_MASTER=$(mysql --connect-timeout=5 -h${standbyip} -u${manageruser} -p${managerpass} -e "SHOW SLAVE STATUSG"| grep "Seconds_Behind_Master" | awk -F": " {' print $2 '})
		echo $SECONDS_BEHIND_MASTER
	fi
}

function get_standby_ip(){
	standby_ip=$(mysql --connect-timeout=5 -h$mysql_host -u$mysql_user -p$mysql_pass  -e "select distinct standby from iomp.ZJFH_MYSQL_LISTS where standby is not null and ip='$1';")
	echo $standby_ip|awk {'print $2'}
}

function backup_binlog_before_clean(){
	mkdir -p /data/mysql_8.0_3306/temp
	mount xxx.xxx.xxx.xxx:/appm/aixinst/mysql_install/Mysqlbinlog /data/mysql_8.0_3306/temp
	iptime="${ipaddr}_${datetime}"
	localdir=/data/mysql_8.0_3306/temp/${iptime}
	mkdir -p $localdir
	chown -R mysql:mysql $localdir
	cd $(find /data -name "bin_log")
	cp mysql-bin.* $localdir
	umount /data/mysql_8.0_3306/temp
	echo "mysqlbinlog backup dir: \\xxx.xxx.xxx.xxxappmaixinstmysql_installMysqlbinlog\${iptime}"
	echo
}

function clean_binlog(){
	echo '清理前binlog大小：'
	mysql -u$manageruser -p$managerpass -e "show binary logs;"
	echo
	mysql -u$manageruser -p$managerpass -e "purge binary logs before '${yesterday}';"
	echo '清理后binlog大小：'
	mysql -u$manageruser -p$managerpass -e "show binary logs;"
	
}

function send_mails()
{
	#test url
	#url="http://xxx.xxx.xxx.xxx:8080/iomp/probe/send"
	#production url
	url=http://xxx.xxx.xxx.xxx:8080/xxx/probe/send
	appcode="00000021"
	eagleappno="00000021"
	probetype="01"
	probedes="mysql_binlog_file_has_been_auto_clean[${ipaddr}]."   
	probetime=`date +"%Y%m%d%H%M%S"`    
	subappcode="1"
	echo "probetime:${datetime}, probedes: ${probedes}."
	command="curl $url -X POST -d 'appcode=$appcode&eagleappno=$eagleappno&probetype=$probetype&probetime=$probetime&subappcode=$subappcode' --data-urlencode probedes=$probedes"
    echo $command 
    eval $command
}

#清理条件？binlog使用率大于90或 binlog剩余空间小于10G
fileSystem=$(getFileSystem)
useage=$(get_binlog_useage ${fileSystem})
idle_size=$(get_binlog_idle_size ${fileSystem})
if [ $useage -gt $useage_limited ] || [ $idle_size -lt $idle_size_limited ]; then
	echo "binlog文件系统使用率或剩余大小满足清理条件，开始清理..."
else
	echo "binlog文件系统使用率[$useage]或剩余大小[$idle_size]充足，无需清理。"
	exit 1
fi

#清理内容？binlog时期在1天前的都备份后删除掉，修改binlog_expire_logs_seconds=86400可实现。
. /home/mysql/.profile
version=$(mysql -u$manageruser -p$managerpass  -e "select version();")
echo $version |grep "8.0"
if [ $?==0 ]; then
	echo "mysql版本为8.0，程序继续..."
else
	echo "mysql版本为5.7或5.5, 脚本暂不支持, 程序已退出..."
	exit 1
fi

delaytime=$(get_behind_seconds)
echo "Seconds_Behind_Master:${delaytime}"
if [ $delaytime -gt 86400 ]; then
	echo "主从延迟[$delaytime]超过86400秒(一天)，不在本程序处理范围内，程序已停止，请手动处理..."
	exit 1
else
	echo "binlog文件系统使用率或剩余大小满足清理条件，开始清理..."
fi

alertcontent="本次共清理x个mysql-bin文件，空出x空间"
#备份、清理、邮件通知
backup_binlog_before_clean
clean_binlog
send_mails