MySQL中binlog和relay log清理方式

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
简介: 1. MySQL server的binlog清理1.1 使用MySQL参数控制expire_logs_days设置二进制日志的过期天数,过了指定天数的日志将被自动删除,可动态修改如果设置了非0值,则在mysqld启动和日志刷新时,可能执行清理超过定义天数的binlog file全局变...

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"
AI 代码解读

其中第一种形式的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)
AI 代码解读

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
AI 代码解读

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
AI 代码解读

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
AI 代码解读
  • 参数描述:

    • --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
AI 代码解读
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.
AI 代码解读

3.2 脚本清除

  • 可以参考上面binlog清理脚本
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
powdba
+关注
目录
打赏
0
0
1
0
32438
分享
相关文章
MySQL日志分析:binlog、redolog、undolog三大日志的深度探讨。
数据库管理其实和写小说一样,需要规划,需要修订,也需要有能力回滚。理解这些日志的作用与优化,就像把握写作工具的使用与运用,为我们的数据库保驾护航。
100 23
MySQL Binlog 日志查看方法及查看内容解析
本文介绍了 MySQL 的 Binlog(二进制日志)功能及其使用方法。Binlog 记录了数据库的所有数据变更操作,如 INSERT、UPDATE 和 DELETE,对数据恢复、主从复制和审计至关重要。文章详细说明了如何开启 Binlog 功能、查看当前日志文件及内容,并解析了常见的事件类型,包括 Format_desc、Query、Table_map、Write_rows、Update_rows 和 Delete_rows 等,帮助用户掌握数据库变化历史,提升维护和排障能力。
简单聊聊MySQL的三大日志(Redo Log、Binlog和Undo Log)各有什么区别
在MySQL数据库管理中,理解Redo Log(重做日志)、Binlog(二进制日志)和Undo Log(回滚日志)至关重要。Redo Log确保数据持久性和崩溃恢复;Binlog用于主从复制和数据恢复,记录逻辑操作;Undo Log支持事务的原子性和隔离性,实现回滚与MVCC。三者协同工作,保障事务ACID特性。文章还详细解析了日志写入流程及可能的异常情况,帮助深入理解数据库日志机制。
343 0
数据库运维:mysql 数据库迁移方法-mysqldump
本文介绍了MySQL数据库迁移的方法与技巧,重点探讨了数据量大小对迁移方式的影响。对于10GB以下的小型数据库,推荐使用mysqldump进行逻辑导出和source导入;10GB以上可考虑mydumper与myloader工具;100GB以上则建议物理迁移。文中还提供了统计数据库及表空间大小的SQL语句,并讲解了如何使用mysqldump导出存储过程、函数和数据结构。通过结合实际应用场景选择合适的工具与方法,可实现高效的数据迁移。
101 1
大数据大厂之MySQL数据库课程设计:揭秘MySQL集群架构负载均衡核心算法:从理论到Java代码实战,让你的数据库性能飙升!
本文聚焦 MySQL 集群架构中的负载均衡算法,阐述其重要性。详细介绍轮询、加权轮询、最少连接、加权最少连接、随机、源地址哈希等常用算法,分析各自优缺点及适用场景。并提供 Java 语言代码实现示例,助力直观理解。文章结构清晰,语言通俗易懂,对理解和应用负载均衡算法具有实用价值和参考价值。
大数据大厂之MySQL数据库课程设计:揭秘MySQL集群架构负载均衡核心算法:从理论到Java代码实战,让你的数据库性能飙升!
【YashanDB知识库】原生mysql驱动配置连接崖山数据库
【YashanDB知识库】原生mysql驱动配置连接崖山数据库
【YashanDB知识库】原生mysql驱动配置连接崖山数据库
大数据新视界 --面向数据分析师的大数据大厂之 MySQL 基础秘籍:轻松创建数据库与表,踏入大数据殿堂
本文详细介绍了在 MySQL 中创建数据库和表的方法。包括安装 MySQL、用命令行和图形化工具创建数据库、选择数据库、创建表(含数据类型介绍与选择建议、案例分析、最佳实践与注意事项)以及查看数据库和表的内容。文章专业、严谨且具可操作性,对数据管理有实际帮助。
大数据新视界 --面向数据分析师的大数据大厂之 MySQL 基础秘籍:轻松创建数据库与表,踏入大数据殿堂
MySQL下载安装全攻略!小白也能轻松上手,从此数据库不再难搞!
这是一份详细的MySQL安装与配置教程,适合初学者快速上手。内容涵盖从下载到安装的每一步操作,包括选择版本、设置路径、配置端口及密码等。同时提供基础操作指南,如数据库管理、数据表增删改查、用户权限设置等。还介绍了备份恢复、图形化工具使用和性能优化技巧,帮助用户全面掌握MySQL的使用方法。附带常见问题解决方法,保姆级教学让你无忧入门!
MySQL下载安装全攻略!小白也能轻松上手,从此数据库不再难搞!
docker拉取MySQL后数据库连接失败解决方案
通过以上方法,可以解决Docker中拉取MySQL镜像后数据库连接失败的常见问题。关键步骤包括确保容器正确启动、配置正确的环境变量、合理设置网络和权限,以及检查主机防火墙设置等。通过逐步排查,可以快速定位并解决连接问题,确保MySQL服务的正常使用。
628 82
MySQL与Clickhouse数据库:探讨日期和时间的加法运算。
这一次的冒险就到这儿,期待你的再次加入,我们一起在数据库的世界中找寻下一个宝藏。
70 9

热门文章

最新文章

推荐镜像

更多
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等

登录插画

登录以查看您的控制台资源

管理云资源
状态一览
快捷访问