一、问题提出
之前写过一篇名为“快速安全删除MySQL大表”的博客,讲解如何在不影响线上数据库服务的前提下删除大表。实际上清理MySQL binlog也会遇到同样的问题。例如,我们每个binlog文件的大小是1G。最初的做法是,每天凌晨2:30执行下面的操作清理10天前binlog:
mysql -uroot -p123456 -s /data/3306/mysqldata/mysql.sock -e "purge master logs before date_sub( now( ), interval 10 day);"
开始数据量不是很大,数据库负载也不高,而且物理上我们将datadir与binglog分布在两个磁盘,mount点分别是/data与/data1,由独立的磁盘控制器所控制,分散I/O。这种做法没有出现问题。随着业务量增长出现了两点变化:一是数据库负载增加;二是由于磁盘空间紧张,原存储binglog的磁盘上也存储了MySQL数据。这种情况下,即便是在业务低峰期,每次执行清理任务时也会卡库。
二、解决方案
解决这个问题的总体思路与删除大表类似,先在binlog文件上建立硬链接,以快速执行purge master logs操作。然后使用truncate操作系统命令逐步缩减binlog文件,直到最后binlog文件变得很小时再将其删除。事实证明这种方案行之有效,能够不影响数据库服务同时清理binlog。下面是相关脚本文件及其说明。
purge_binlog.sh主要负责执行purge master logs操作,文件内容如下:
#!/bin/bash
export PATH=.:$PATH:/home/mysql/mysql-5.6.14/bin;
source ~/.bashrc
# 创建binlog文件硬链接
cd /data1/3306/
ls -l mysqlbinlog.* | grep -v index |awk '{print $9}' | awk -F '.' '{print $1"."$2}' | sort | uniq -c | awk '$1==1{print $2}' | awk '{print "ln "$0" "$0".h"}' | bash
ls -ltr mysqlbinlog.* | grep -v .h | grep -v .index | awk '{print $9}' > before_purge.txt
mysql -uroot -p123456 -s /data/3306/mysqldata/mysql.sock -e "purge master logs before date_sub( now( ), interval 10 day);" > /home/mysql/dbbat/purge_binlog.log 2>&1
ls -ltr mysqlbinlog.* | grep -v .h | grep -v .index | awk '{print $9}' > after_purge.txt
diff before_purge.txt after_purge.txt | awk 'NR == 1 {next} {print $2}' | awk '{print "./rmbinlogfile.sh " $0}' > rmpurgefile.sh
chmod 755 rmpurgefile.sh
./rmpurgefile.sh
该脚本按顺序执行下面的步骤:
1. 设置环境
包括设置mysql可执行文件路径和其它资源。
2. 创建binlog文件硬链接
只对具有唯一前缀的binlog文件创建硬链接,避免重复创建时报错。其实即使出现重复创建硬链接也不会影响脚本正常执行,但报错总会让人不爽,所以这里做了一层判断。例如当前binlog文件如下:
-rw-rw---- 1 mysql mysql 1073741878 Aug 21 10:23 mysqlbinlog.026765
-rw-rw---- 1 mysql mysql 1073741878 Aug 21 10:23 mysqlbinlog.026765.h
-rw-rw---- 1 mysql mysql 1073742017 Aug 21 10:44 mysqlbinlog.026766
-rw-rw---- 1 mysql mysql 1073741878 Aug 21 10:44 mysqlbinlog.026766.h
-rw-rw---- 1 mysql mysql 1073742308 Aug 21 11:03 mysqlbinlog.026767
-rw-rw---- 1 mysql mysql 1073742288 Aug 21 11:20 mysqlbinlog.026768
-rw-rw---- 1 mysql mysql 376007370 Aug 21 11:27 mysqlbinlog.026769
-rw-rw---- 1 mysql mysql 5376 Aug 21 11:20 mysqlbinlog.index
只会对mysqlbinlog.026767、mysqlbinlog.026768、mysqlbinlog.026769三个文件创建硬链接。
3. 执行purge master logs操作,并生成删除文件的脚本
我们是按时间条件清除的binlog,MySQL并没有向用户返回具体删除了哪些文件,而这些文件才是真正需要truncate并从磁盘删除的。为了获取需要实际删除文件的列表,在purge master logs前后各取一次binlog文件列表,并分别存储在文件before_purge.txt和after_purge.txt中,before_purge.txt里有但after_purge.txt里没有的文件就是需要删除的文件。还是以前面的binlog列表为例,假设删除10:45之前的binlog,执行完purge master logs后,文件列表变为:
-rw-rw---- 1 mysql mysql 1073741878 Aug 21 10:23 mysqlbinlog.026765.h
-rw-rw---- 1 mysql mysql 1073741878 Aug 21 10:44 mysqlbinlog.026766.h
-rw-rw---- 1 mysql mysql 1073742308 Aug 21 11:03 mysqlbinlog.026767
-rw-rw---- 1 mysql mysql 1073742308 Aug 21 11:03 mysqlbinlog.026767.h
-rw-rw---- 1 mysql mysql 1073742288 Aug 21 11:20 mysqlbinlog.026768
-rw-rw---- 1 mysql mysql 1073742288 Aug 21 11:20 mysqlbinlog.026768.h
-rw-rw---- 1 mysql mysql 376007370 Aug 21 11:27 mysqlbinlog.026769
-rw-rw---- 1 mysql mysql 376007370 Aug 21 11:27 mysqlbinlog.026769.h
-rw-rw---- 1 mysql mysql 5376 Aug 21 11:20 mysqlbinlog.index
生成的rmpurgefile.sh文件内容如下:
./rmbinlogfile.sh mysqlbinlog.026765
./rmbinlogfile.sh mysqlbinlog.026766
4. 缩减并删除磁盘文件
该操作由rmbinlogfile.sh完成,文件内容如下:
#!/bin/bash
# binlog文件大小,单位M
filesize=`ls -l $1.h | awk '{print int($5/1024/1024)}'`
if (( $filesize < 20 ))
then
# 小于20直接删除
rm $1.h
else
# 大于等于20,每次截断20M
for i in `seq $filesize -20 0`
do
sleep 2
# echo $i
truncate -s ${i}M $1.h
done
# 删除小于20M的文件
rm $1.h
fi
每次缩减20M,并停两秒,最后当文件小于20M后将其删除。完全删除1个1G的binlog文件,大约需要102.4秒。当然,我们的目标是要最小化对线上的影响,只要不保证服务正常,这个后台的缩减和删除操作可以慢慢执行。