MySQL线上维护三脚本
2023-04-18 12:43:08 时间
1. 获取process和lock的现场信息(get_processlist.sh)
#!/bin/bash
source /home/mysql/.bashrc
DT=`date '+%Y%m%d_%H%M%S'`
mysql -uroot -p123456 -S /data/3306/mysqldata/mysql.sock -e "select t1.* from information_schema.processlist t1,(select count(1) c from information_schema.processlist where info is not null) t2 where t2.c>=50;" > /home/mysql/processlist_3306.txt
if test -s /home/mysql/processlist_3306.txt; then
mv /home/mysql/processlist_3306.txt /home/mysql/processlist_3306/processlist_3306_${DT}.txt
mysql -uroot -p123456 -S /data/3306/mysqldata/mysql.sock -e "SELECT trx_id, trx_state, trx_started, trx_requested_lock_id, trx_wait_started, trx_weight, trx_mysql_thread_id, trx_query, trx_operation_state, trx_tables_in_use, trx_tables_locked, trx_lock_structs, trx_lock_memory_bytes, trx_rows_locked, trx_rows_modified, trx_concurrency_tickets, trx_isolation_level, trx_unique_checks, trx_foreign_key_checks, trx_last_foreign_key_error, trx_adaptive_hash_latched, trx_adaptive_hash_timeout, trx_is_read_only, trx_autocommit_non_locking FROM information_schema.INNODB_TRX; SELECT requesting_trx_id, requested_lock_id, blocking_trx_id, blocking_lock_id FROM information_schema.INNODB_LOCK_WAITS;SELECT lock_id, lock_trx_id, lock_mode, lock_type, lock_table, lock_index, lock_space, lock_page, lock_rec, lock_data FROM information_schema.INNODB_LOCKS;" > /home/mysql/processlist_3306/innodb_locks_3306_${DT}.txt
else
rm /home/mysql/processlist_3306.txt
fi
find /home/mysql/processlist_3306/* -type f -mtime +10 -exec rm {} ;
2. 杀掉所有sleep线程(kill_3306.sh)
#!/bin/bash
source ~/.bashrc
rm -rf /tmp/kill.sql
mysql -uroot -p123456 -S /data/3306/mysqldata/mysql.sock -e "select * into outfile '/tmp/kill.sql' from (select concat('kill ',id,';') from information_schema.processlist where command in ('sleep') ) t; "
mysql -uroot -p123456 -S /data/3306/mysqldata/mysql.sock -f < /tmp/kill.sql
3. 定期清除慢日志(purge_slow_log.sh)
#!/bin/bash
source ~/.bashrc
mysql -uroot -p123456 -S /data/3306/mysqldata/mysql.sock <<!
use mysql;
set sql_log_bin=0;
truncate table test.slow_log_bak;
insert into test.slow_log_bak select * from slow_log where start_time >= current_date()-7;
set global slow_query_log = 'off';
alter table slow_log rename slow_log_drop;
create table slow_log like slow_log_drop;
set global slow_query_log = 'on';
drop table slow_log_drop;
!
4. 定期执行
* * * * * /home/mysql/dbbat/get_processlist.sh
0 6 * * 0 /home/mysql/dbbat/purge_slow_log.sh
相关文章
- 直接在代码里面对list集合进行分页
- .NET Framework 4.5新特性详解
- 大数据的简要介绍
- 大数据的由来
- 高斯混合模型的自然梯度变量推理
- timing-wheel 仿Kafka实现的时间轮算法
- 使用Navicat软件连接自建数据库(Linux系统)
- 那一天,我被Redis主从架构支配的恐惧
- Redis 深入了解键的过期时间
- C#使用委托调用实现用户端等待闪屏
- 基于流计算 Oceanus 和 Elasticsearch Service 构建百亿级实时监控系统
- GRAND | 转录调控网络预测数据库
- JFreeChart API中文文档
- 临床相关突变查询数据库
- TIGER | 人类胰岛基因变化查询数据库
- 视频边缘计算网关EasyNVR在视频整体监控解决方案中的应用分析
- Apache Arrow - 大数据在数据湖后的下一个风向标
- 常见的电商数据指标体系
- AKShare-艺人数据-艺人流量价值
- MySQL中多表联合查询与子查询的这些区别,你可能不知道!