MySQL中binlog和relay log清理方式

1. MySQL server的binlog清理

1.1 使用MySQL参数控制

• expire_logs_days

  • 设置二进制日志的过期天数，过了指定天数的日志将被自动删除，可动态修改
  • 如果设置了非0值，则在mysqld启动和日志刷新时，可能执行清理超过定义天数的binlog file
  • 全局变量，动态变量，默认值为0（代表不会自动清理binlog），整型值，取值范围为0~99

自动清理的具体实现是：当binlog文件达到 max_binlog_size自动切换或者手动切换（flush）或者MySQL启动（startup）时，会遍历index文件，找到第一个“最后修改时间”在N天内的binlog文件，然后将该binlog文件之前的所有binlog文件删除掉。

1.2 手动purge清理

通常手动清理binlog是使用MySQL提供的purge命令。purge命令的定义如下：

purge {binary | master} logs to "binlog-file-name"
purge {binary | master} logs before "datetime-expr"

其中第一种形式的purge命令的作用是将binlog-file-name之前的（不包括自己本身）所有binlog文件清理掉，而第二种形式的purge命令的作用是将最后修改时间早于datetime-rxpr的binlog文件清理掉（不包括自己）。

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000006 |   1215598 |
| mysql-bin.000007 |  20380128 |
| mysql-bin.000008 |     57424 |
| mysql-bin.000009 |   1861624 |
+------------------+-----------+
4 rows in set (0.03 sec)

mysql> PURGE BINARY LOGS TO 'mysql-bin.000007';
Query OK, 0 rows affected (0.06 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000007 |  20380128 |
| mysql-bin.000008 |     57424 |
| mysql-bin.000009 |   1870354 |
+------------------+-----------+
3 rows in set (0.02 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-04-11 12:32:31 |
+---------------------+
1 row in set (0.00 sec)

mysql> PURGE BINARY LOGS BEFORE '2019-04-11 12:32:31';
Query OK, 0 rows affected, 1 warning (0.11 sec)
Warning (Code 1868): file /var/lib/mysql/archive/mysql-bin.000009 was not purged because it is the active log file.

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000009 |   1888784 |
+------------------+-----------+
1 row in set (0.00 sec)

1.3 脚本清理

按binlog保留个数持续清理
#!/bin/bash

# binlog index文件路径和名称
binlog_index_name=/var/lib/mysql/archive/mysql-bin.index

# 需要保留的binlog个数
reserve_num=10

# 执行检查间隔，循环检查，发现在该参数指定的时间间隔内有超过reserve_num参数指定的binlog个数，即执行删除，否则等待下一次检查
check_interv=60

# 单个文件删除间隔，在reserve_num参数指定的间隔内发现binlog数量超过reserve_num参数指定的数量就会执行删除，在有多个文件需要删除时，按照rm_interv参数指定的时间间隔逐个删除文件
rm_interv=0

# 执行日志
log_file=/tmp/`basename $0`.log

rm -f $log_file

# 执行清理binlog函数
exec_purge_binlog() {
    total_num=`sed -n '$=' $binlog_index_name`
    let purge_num=${total_num}-${reserve_num}
    if [ "$purge_num" -gt "0" ];then
        purge_files=(`sed -n "1,${purge_num}p" $binlog_index_name`)
        start_file=$(basename $(sed -n '1p' $binlog_index_name))
        end_file=$(basename $(sed -n "${purge_num}p" $binlog_index_name))
        echo
        echo '=================================================================================================================================='
        echo "发现有需要清理的binlog：binlog文件总个数：${total_num}，需要保留文件个数：${reserve_num}，需要清理的文件个数：${purge_num}，将执行清理的文件名称范围：${start_file}-${end_file}"
        echo "---------------------------`date`----------------------------------正在执行binlog的清理"
        
        i=0
        for purge_file in "${purge_files[@]}"
        do
            echo
            echo "正在清理binlog文件：${purge_file} ----------------------`date`-----------------"
            \rm -f "$purge_file"
            sleep "$rm_interv"
            let i++
            echo "完成清理binlog文件：${purge_file} 时间：`date`，标文件名：${end_file}，目标文件数：${purge_num}，已清理文件个数：${i}"
        done
        
        sed -i "1,${purge_num}d" $binlog_index_name
        echo "本次清理binlog执行完成------------------`date`--------------------"
    else
        echo
        echo '=================================================================================================================================='
        echo "本次未检测到需要清理的binlog，等待下次执行检查中......"
    fi
}


# 执行函数调用
if [ ! -f "$binlog_index_name" ];then
    echo "警告，指定的binlog index文件不存在，脚本退出！！" |tee -a $log_file
    exit 1
else
    while :
    do
        exec_purge_binlog |tee -a $log_file
        sleep "$check_interv"
    done
fi

2. MySQL binlog server的binlog脚本清理

#!/bin/bash

# binlog server文件路径和名称
binlog_dir=/opt/backup/binlog/

# 需要保留的binlog个数
reserve_num=10

# 执行检查间隔，循环检查，发现在该参数指定的时间间隔内有超过reserve_num参数指定的binlog个数，即执行删除，否则等待下一次检查
check_interv=60

# 单个文件删除间隔，在reserve_num参数指定的间隔内发现binlog数量超过reserve_num参数指定的数量就会执行删除，在有多个文件需要删除时，按照rm_interv参数指定的时间间隔逐个删除文件
rm_interv=0

# 执行日志
log_file=/tmp/`basename $0`.log

rm -f $log_file

# 执行清理binlog函数
exec_purge_binlog() {
    total_num=`ls $binlog_dir | wc -l`
    let purge_num=${total_num}-${reserve_num}
    if [ "$purge_num" -gt "0" ];then
        purge_files=(`ls $binlog_dir | sort -k2 | head -$purge_num`)
        start_file=(`head -1 $purge_files 2>/dev/null`)
        end_file=(`tail -1 $purge_files 2>/dev/null`)
        echo
        echo '=================================================================================================================================='
        echo "发现有需要清理的binlog：binlog文件总个数：${total_num}，需要保留文件个数：${reserve_num}，需要清理的文件个数：${purge_num}，将执行清理的文件名称范围：${start_file}-${end_file}"
        echo "---------------------------`date`----------------------------------正在执行binlog的清理"

        i=0
        for purge_file in "${purge_files[@]}"
        do
            echo
            echo "正在清理binlog文件：${purge_file} ----------------------`date`-----------------"
            \rm -f "$binlog_dir$purge_file"
            sleep "$rm_interv"
            let i++
            echo "完成清理binlog文件：${purge_file} 时间：`date`，标文件名：${end_file}，目标文件数：${purge_num}，已清理文件个数：${i}"
        done

        echo "本次清理binlog执行完成------------------`date`--------------------"
    else
        echo
        echo '=================================================================================================================================='
        echo "本次未检测到需要清理的binlog，等待下次执行检查中......"
    fi
}


# 执行函数调用
if [ ! -d "$binlog_dir" ];then
    echo "警告，指定的binlog文件不存在，脚本退出！！" |tee -a $log_file
    exit 1
else
    while :
    do
        exec_purge_binlog |tee -a $log_file
        sleep "$check_interv"
    done
fi

3. MySQL server的relay log清理

3.1. MHA中工具purge_relay_logs

• 自行手动安装mha-node软件

3.1.1 purge_relay_logs功能

为relay日志创建硬链接(最小化批量删除大文件导致的性能问题)
SET GLOBAL relay_log_purge=1; FLUSH LOGS; SET GLOBAL relay_log_purge=0;
删除relay log（rm –f /path/to/archive_dir/*）

3.1.2 purge_relay_logs用法和参数

• 用法：

purge_relay_logs --user=root --password=rootpass --host=127.0.0.1

• 参数描述：

  • --user：mysql用户名，缺省为root
  • --password：mysql密码
  • --port：端口号
  • --host：主机名，缺省为127.0.0.1
  • --workdir：指定创建relay log的硬链接的位置，默认是/var/tmp，成功执行脚本后，硬链接的中继日志文件被删除。由于系统不同分区创建硬链接文件会失败，故需要执行硬链接具体位置，建议指定为relay log相同的分区
  • --disable_relay_log_purge：默认情况下，参数relay_log_purge=1，脚本不做任何处理，自动退出；设定该参数，脚本会将relay_log_purge设置为0，当清理relay log之后，最后将参数设置为OFF(0)

3.1.3 定期清理relay log

pureg_relay_logs脚本在不阻塞SQL线程的情况下自动清理relay log。对于不断产生的relay log直接将该脚本部署到crontab以实现按天或按小时定期清理。

$ crontab -l  
# purge relay logs at 5am  
0 5 * * * /usr/bin/purge_relay_logs --user=xxx --host=xxx --password=xxx --disable_relay_log_purge >> /var/log/masterha/purge_relay_logs.log 2>&1

3.1.4 手动清理示例

# purge_relay_logs --version
purge_relay_logs version 0.58.
# purge_relay_logs --user=xxx --host=xxx --password=xxx --disable_relay_log_purge
2019-08-07 10:28:18: purge_relay_logs script started.
 relay_log_purge is enabled. Disabling..
 Opening /var/lib/mysql/data/relaylog/mysql-relay-bin.000005 ..
 Opening /var/lib/mysql/data/relaylog/mysql-relay-bin.000006 ..
 Executing SET GLOBAL relay_log_purge=1; FLUSH LOGS; sleeping a few seconds so that SQL thread can delete older relay log files (if it keeps up); SET GLOBAL relay_log_purge=0; .. ok.
2019-08-07 10:28:21: All relay log purging operations succeeded.

3.2 脚本清除

• 可以参考上面binlog清理脚本