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
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· C++代码改造为UTF-8编码问题的总结
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库