MySQL 慢日志文件按天生成(切割)

1.必要性

在生成环境中,MySQL实例一般都会开启慢日志的,我们可以基于慢日志进行性能分析,但是文件小会逐渐增加,从几十兆到几十G,如此大的文件给我们分析带来了调整。如果我们部署了日志平台,例如通过filebeat +logstash实时读取,当慢日志文件过大也会导致Server性能降低(例如,读取时内存消耗明显)。所以,防止mysql慢查询日志文件过大,进行按天切割很有必要。

2.编辑执行文件chop_slow_log.sh

复制代码
#!/bin/bash

# The version is defined V.001
# Version   ModifyTime                ModifyBy              Desc
# Ver001    2018-03-02 12:00         Carson.Xu             Create the Scripts File
# Desc: This file is used by cron to cut the slow log and remove the history slow log.

#### 部署前应检查slow 所在的路径 (默认为/data/mysql/data/slow.log,否则进行调整)

####time=`date -d yesterday +"%Y-%m-%d"`
time=$(date "+%Y-%m-%d")
rmbaktime=$(date -d '-7 days' "+%Y-%m-%d")

##账号(执行 flush-logs 命令 ,需要有reload权限,允许使用flush语句)
user="用户名"
##应安全要求,账号密码不能同时出现在一个文件中,所以单独存放
passwd=$(cat /data/dbsave/mysql_upwd)

####刷新慢查询日志文件
/usr/local/mysql/bin/mysqladmin -u$user -p$passwd flush-logs slow


####重命名旧慢查询日志
mv /data/mysql/data/slow.log /data/mysql/data/slow-$time.log


####生成新慢查询日志文件
/usr/local/mysql/bin/mysqladmin -u$user -p$passwd flush-logs slow


####删除历史slow.log
rm -rf /data/mysql/data/slow-$rmbaktime.log
复制代码

 注意 . 文件设置切割后的慢日志保留7天。

3.设置定时任务(crontab)

46 14 * * * /data/scripts/chop_slow_log.sh >> /data/scripts/chop_slow_log.log 2>&1

4.相关知识补充

4.1 设置成crontab 报错

单独调试执行chop_slow_log.sh正常,但是设置成定时任务时,就报错。报错信息如下:

/data/scripts/chop_slow_log.sh: line 20: mysqladmin: command not found
/data/scripts/chop_slow_log.sh: line 28: mysqladmin: command not found

解决方案是:mysqladmin必须是完成的路径,软链接不可以。

即将文件中的mysqladmin调整为/usr/local/mysql/bin/mysqladmin即可。

4.2 MySQL权限

主要权限列表如下list.

权  限作用范围作  用
all 服务器 所有权限
select 表、列 选择行
insert 表、列 插入行
update 表、列 更新行
delete 删除行
create 数据库、表、索引 创建
drop 数据库、表、视图 删除
reload 服务器 允许使用flush语句
shutdown 服务器 关闭服务
process 服务器 查看线程信息
file 服务器 文件操作
grant option 数据库、表、存储过程 授权
references 数据库、表 外键约束的父表
index 创建/删除索引
alter 修改表结构
show databases 服务器 查看数据库名称
super 服务器 超级权限
create temporary tables 创建临时表
lock tables 数据库 锁表
execute 存储过程 执行
replication client 服务器 允许查看主/从/二进制日志状态
replication slave 服务器 主从复制
create view 视图 创建视图
show view 视图 查看视图
create routine 存储过程 创建存储过程
alter routine 存储过程 修改/删除存储过程
create user 服务器 创建用户
event 数据库 创建/更改/删除/查看事件
trigger 触发器
create tablespace 服务器 创建/更改/删除表空间/日志文件
proxy 服务器 代理成为其它用户
usage 服务器 没有权限

5.优化方案

5.1 优化背景(why?)

随着公司的业务发展,从库承担的业务也越来越多,相应的慢查询的log也日积月累,越来越大,从节点也需要部署 切割 slow log的任务。

我们发现,部署后,在GTID模式下,主从切换后,主从关系搭不起来了(使用master_auto_position=1,不是文件+位点)。

同样,MHA在线切换后(masterha_master_switch --master_state=alive --conf=/data/mysql_mha/XXXX.cnf --new_master_host=172.XXX.XXX.XXX --new_master_port=3306 --orig_master_is_new_slave),也是新主从报同样的错误(同样主从使用master_auto_position=1,不是文件+位点;使用文件+位点 搭建主从的MHA在线切换没问题,就是说切换后,新主从是好的)。

说明:MHA切换正常,不受影响,切换后新出从报错。

show slave status 显示的错误如下:

....
Slave_IO_Running: No
Slave_SQL_Running: Yes
....
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'
.....

经过验证分析,错误原因是:

执行如下命令,会产生一个代表事务的GTID,当然,这个GTID也会记录在bin log 中。

/usr/local/mysql/bin/mysqladmin -u$user -p$passwd flush-logs slow

即,执行这条命令,实实在在的是一个事务,有GTID,记录在binlog中了,只是 bin log 记录了一个空事务。

通过 show master status 命令查看,我们会看到 Executed_Gtid_Set 值,seqeunce_id有+1,也说明了是一个事务。

如果 flush-logs slow 命令,是在主节点上执行,我们通过 show slave status 命令查看。Executed_Gtid_Set 也发生了变动,这个GTID同步到从节点上了。也就是说,有重放bin log,但是是个空事务(如果不是空事务,那么从节点上也会自动截断 slow log ,但实际不是这样)。

反过来讲,如果 flush-logs slow  在从节点上执行,那么,从节点,就会多了一条 事务,多了一条 GTID(虽然 binlog 对应的GTID是空事务),如果是 每天 执行 一次,那么每天就多一个GTID,从实例不会同步到主实例,主从实例两边事务不一致。而在生产环境中,实例 又会 设置 expire_logs_days (例如 7天),那么expire_logs_days(比如7天)前的 binlog 就会被PURGE掉。这些PURGE掉的binlog就包含了 flush-logs slow 产生的GTID。主从FailOver,重建主从,master_auto_position=1,就会报错,提示包含需要的GTID的binlog已经被PURGE;

5.2 怎么优化(what?)

思路简单,使用不产生 GTID的 flush logs命令,或者在什么条件下 flush logs 不产生GTID。

复制代码
#!/bin/bash

# The version is defined V.001
# Version   ModifyTime                ModifyBy              Desc
# Ver001    2018-03-02 12:00         Carson.Xu             Create the Scripts File
# Desc: This file is used by cron to cut the slow log and remove the history slow log.

#### 部署前应检查slow 所在的路径 (默认为/data/mysql/data/slow.log,否则进行调整)

####time=`date -d yesterday +"%Y-%m-%d"`
time=$(date "+%Y-%m-%d")
rmbaktime=$(date -d '-7 days' "+%Y-%m-%d")

##账号(执行 flush-logs 命令 ,需要有reload权限,允许使用flush语句)
user="用户名"
##应安全要求,账号密码不能同时出现在一个文件中,所以单独存放
passwd=$(cat /data/dbsave/mysql_upwd)

####刷新慢查询日志文件
/usr/local/mysql/bin/mysql -u$user -p$passwd -e"set sql_log_bin=0;flush slow logs;"


####重命名旧慢查询日志
mv /data/mysql/data/slow.log /data/mysql/data/slow-$time.log


####生成新慢查询日志文件
/usr/local/mysql/bin/mysql -u$user -p$passwd -e"set sql_log_bin=0;flush slow logs;"


####删除历史slow.log
rm -rf /data/mysql/data/slow-$rmbaktime.log
复制代码

 

posted @   东山絮柳仔  阅读(2079)  评论(0编辑  收藏  举报
编辑推荐:
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· C++代码改造为UTF-8编码问题的总结
阅读排行:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
点击右上角即可分享
微信分享提示