mysql卡库,快速安全清理MySQL binlog

一、问题提出

之前写过一篇名为“快速安全删除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秒。当然，我们的目标是要最小化对线上的影响，只要不保证服务正常，这个后台的缩减和删除操作可以慢慢执行。