xxl-job 都用来干什么?
xxl-job 是我每天打开频率最高的中间件没有之一.
那ta都用来干啥啊.给业务项目提供了哪些服务?
标题
1. Canal 同步.
2. ES备份.
3. Mysql迁移.
4. Mysql脚本同步.
5. 数据同步至Redis.
6. 业务调度.
7. Mysql自增键使用率监控.
8. Mysql慢查询监控.
9. 分区警告.
10.RabbitMQ监控.
11.Redis集群监控.
12.ES服务器监控.
13.Mysql并发超标预警.
14.ES日志服务清理历史日志
... ...
详情
1. Canal 同步.
这个不在这里展开叙述, 写到 canal中间件实操里面
2. ES备份.
字面意思,直接上脚本
#!/bin/bash
request_ip="xx.xx.xx.xx:9200"
request_pass="elastic:xxx-xxx"
location="/u02/xxxx"
index="kyyfk_prepaid_account_trade_detail"
index_list="kyyfk_prepaid_account_trade_detail"
pre_date=$(date -d "$(date) -1440 minute" +"%Y%m%d")
snapshot_all_url='http://'$request_ip'/_cat/repositories?'
snapshot_url='http://'$request_ip'/_snapshot/'$index
index_url='http://'$request_ip'/_snapshot/'$index'/'$index'_'$pre_date'?wait_for_completion=true'
echo "snapshot_url:$snapshot_url"
echo "index_url:$index_url"
#clear###################################################
echo -e "-------------${index}：开始备份时间："$(date -d "$(date)" +"%Y-%m-%d %H:%M:%S")"-------------"
snapshot_sql='{
"type": "fs",
"settings": {
"location": "'$location'/'$index'",
"compress": true,
"max_snapshot_bytes_per_sec": "50mb",
"max_restore_bytes_per_sec": "50mb"
}
}'
echo "snapshot_sql:$snapshot_sql"
index_sql='{
"indices": "'$index_list'",
"ignore_unavailable": "true",
"include_global_state": false
}'
echo "index_sql:$index_sql"
snapshots=$(curl -XGET $snapshot_all_url -H "Content-Type: application/json" -u $request_pass)
echo -e "仓库列表：$snapshots"
snapshot_result=$(echo $snapshots | grep "${index}")
if [[ "$snapshot_result" != "" ]]
then
echo -e "${index}：仓库已存在"
else
curl -XPUT $snapshot_url -H "Content-Type: application/json" -u $request_pass -d "$snapshot_sql"
fi
curl -XPUT $index_url -H "Content-Type: application/json" -u $request_pass -d "$index_sql"
echo -e "-------------${index}：结束备份时间："$(date -d "$(date)" +"%Y-%m-%d %H:%M:%S")"-------------"
3. Mysql迁移.
SOURCE_PASS=
SOURCE_HOST=
SOURCE_PORT=
SOURCE_DATABASE=
SOURCE_TABLE=
TARGET_USER=
TARGET_PASS=
TARGET_HOST=
TARGET_PORT=
TARGET_DATABASE=
TARGET_TABLE=
TARGET_USER_NEXT=
TARGET_PASS_NEXT=
TARGET_HOST_NEXT
TARGET_PORT_NEXT=
TARGET_DATABASE_NEXT=
TARGET_TABLE_NEXT=
TARGET_NEXT_DATE="2022-12-01"
## 1:只同步数据 0:同步数据同时清理数据
TARGET_IMPORT_FLAG="0"
PARTITION_TIELD=buz_date
PARTITION_DATE_LIMIT=60
## 毫无人性的手机通知
at_mobiles=['176xxxxxxxx']
## 一下脚本是公共部分. 可以提出来.写到sh里面. 放在公共盘中.直接引用使用
source /NAS/COMMON/backup/common_backup.sh
## common_backup 内容如下:
if [ ! -d $date_file_path ];then
mkdir -p $date_file_path
fi
file_name="$SOURCE_TABLE.log"
if [ ! -f "${date_file_path}/${file_name}" ]; then
echo "" > "${date_file_path}/${file_name}"
fi
if [ "$1" != "" ]; then
start_time="$1"
else
while read line
do
start_time="$line"
done < "${date_file_path}/${file_name}"
fi
now_time=$(date -d "$(date) -60 day" +"%Y-%m-%d")
echo -e "$start_time;$now_time"
time1=`date -d "$start_time" +%s`
time2=`date -d "$now_time" +%s`
if [ $time1 -gt $time2 ]; then
echo -e "清理时间未到：$start_time;$now_time"
echo $start_time > "${date_file_path}/${file_name}"
exit 0
fi
SOURCE_SQL="select count(1) from $SOURCE_DATABASE.$SOURCE_TABLE t where t.buz_date>='$start_time 00:00:00' and t.buz_date<='$start_time 23:59:59'"
TARGET_SQL="select count(1) from $TARGET_DATABASE.$TARGET_TABLE t where t.buz_date>='$start_time 00:00:00' and t.buz_date<='$start_time 23:59:59'"
echo "SOURCE_SQL:$SOURCE_SQL"
echo "TARGET_SQL:$TARGET_SQL"
source_result=$(mysql -u$SOURCE_USER -p$SOURCE_PASS -h$SOURCE_HOST -P$SOURCE_PORT -e "$SOURCE_SQL")
source_result_count=(${source_result//count(1) / })
target_result=$(mysql -u$TARGET_USER -p$TARGET_PASS -h$TARGET_HOST -P$TARGET_PORT -e "$TARGET_SQL")
target_result_count=(${target_result//count(1) / })
echo -e "源结果："${source_result}
echo -e "目标结果："${target_result}
echo -e "源数据量："${source_result_count[1]}
echo -e "目标数据量："${target_result_count[1]}
pre_time=$(date +"%Y-%m-%d" -d "1970-01-01 UTC $(($(date -d "$start_time" +"%s"))) seconds")
next_time=$(date +"%Y-%m-%d" -d "1970-01-01 UTC $(($(date -d "$start_time" +"%s") +86400)) seconds")
echo "下次清理时间:"$next_time
partition_name="p"$(date -d "$pre_time" +"%Y%m%d")
echo "清理分区名称:$partition_name"
if [ "${source_result_count[1]}" != "${target_result_count[1]}" ]; then
echo "分区数据不一致:$partition_name"
send_msg="历史数据清理差异"$SOURCE_TABLE"——$partition_name——"$start_time"：源："${source_result_count[1]}"，目标："${target_result_count[1]}",时间：$start_time 00:00:00;$start_time 23:59:59"
curl "https://oapi.dingtalk.com/robot/send?access_token=${access_token}" \-H 'Content-Type: application/json' \-d "{'msgtype': 'text','text': {'content': '$send_msg'},'at':{'atMobiles':${at_mobiles}}}"
else
cur_time=$(($(date +%s%N)/1000000))
clear_sql="alter table $SOURCE_DATABASE.$SOURCE_TABLE drop partition $partition_name"
echo -e "开始清理分区"$clear_sql"\n"
mysql -u$SOURCE_USER -p$SOURCE_PASS -h$SOURCE_HOST -P$SOURCE_PORT -e "$clear_sql" ;
echo -e "结束清理分区，执行$(($(($(date +%s%N)/1000000))-$cur_time))毫秒\n"
echo $next_time > "${date_file_path}/${file_name}"
fi
exit 0
4. Mysql脚本同步.
此方法在不追求实时性的场景中,是比较主流的同步方式, 同步数据较为可靠.
我们生产的mysql数据库 1000w数据大约3分钟可以跑完
#!/bin/bash
SOURCE_USER=
SOURCE_PASS=
SOURCE_HOST=
SOURCE_PORT=
SOURCE_DATABASE=
SOURCE_TABLE=
TARGET_USER=
TARGET_PASS=
TARGET_HOST=
TARGET_PORT=
TARGET_DATABASE=
TARGET_TABLE=
PARTITION_TIELD=
export_dir="/NAS/COMMON/$SOURCE_HOST/$SOURCE_DATABASE"
export_file="/$export_dir/$SOURCE_TABLE.csv"
if [ ! -d $export_dir ];then
mkdir -p $export_dir
fi
query_sql="SELECT * FROM $SOURCE_DATABASE.$SOURCE_TABLE t WHERE t.$PARTITION_TIELD>='$1' AND t.$PARTITION_TIELD< DATE_ADD('$1',INTERVAL 1 day)";
echo -e "$query_sql"
mysqluldr user=$SOURCE_USER/$SOURCE_PASS@$SOURCE_HOST:$SOURCE_PORT charset=UTF8 text="CSV" head=No safe=Yes file="$export_file" NULL="\\N" query="$query_sql"
if [ $? -ne 0 ]; then
echo " error for wait <$(date)>"
continue
fi
mysql -u$TARGET_USER -p$TARGET_PASS -h$TARGET_HOST -P$TARGET_PORT --local-infile --show-warnings -v -e "LOAD DATA LOCAL INFILE '$export_file' REPLACE INTO TABLE $TARGET_DATABASE.$TARGET_TABLE CHARACTER SET UTF8 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n';";
exit 0
5. 数据同步至Redis
分rang读取mysql中的数据, nas盘中存储的是rang的范围.
此脚本不支持mysql 中的数据修改场景.
#!NAS盘info
shell_dir=/NAS/NAS_KY_SCHOOL/kyyfk/basic_org
data_dir=/NAS/NAS_KY_SCHOOL/kyyfk/data
#!数据库信息
source_db_name=
CLIENT_IP=
CLIENT_PORT=
CLIENT_USER=
CLIENT_PWD=
#! 目录文件处理
if [ ! -d "$shell_dir" ]
then
#echo "目录不存在"
mkdir -p "$shell_dir"
fi
if [ ! -d "$data_dir" ]
then
#echo "目录不存在"
mkdir -p "$data_dir"
fi
log_file=$shell_dir/basic_org_del.log
if [ ! -f "$log_file" ]
then
touch $log_file
fi
rec_file=$shell_dir/basic_org_del.txt
if [ ! -f "$rec_file" ]
then
touch $rec_file
fi
if [ "$1" != "" ]; then
time_start="$1"
else
while read line
do
time_start=$(date -d "$line" +"%Y-%m-%d %H:%M:%S")
#time_start='2018-01-01 00:00:00'
done < "$rec_file"
fi
#!设置查询区间段
time_end=$(date +"%Y-%m-%d %H:%M:%S")
time_now=$(date +"%Y%m%d%H%M%S")
echo "time_start:"$time_start"time_end:"$time_end
org_file=$data_dir/org_$time_now.csv
user_file=$data_dir/user_$time_now.csv
if [ ! -f "$org_file" ]
then
touch $org_file
fi
if [ ! -f "$user_file" ]
then
touch $user_file
fi
sql="SELECT a.bm, a.mc,a.lb,a.bill_center_code FROM ${source_db_name}.gs a LEFT JOIN ${source_db_name}.gs t ON a.first_center_code = t.bm WHERE a.modify_time BETWEEN '$time_start' AND '$time_end' "
echo $sql
sql1="SELECT b.gh_oa, b.name,b.gs FROM ${source_db_name}.ry b WHERE b.modify_time BETWEEN '$time_start' AND '$time_end' AND b.user_type = 1"
echo $sql1
mysqluldr user=$CLIENT_USER/$CLIENT_PWD@$CLIENT_IP:$CLIENT_PORT charset=UTF8 text="CSV" head=No safe=Yes file="$org_file" query="$sql"
mysqluldr user=$CLIENT_USER/$CLIENT_PWD@$CLIENT_IP:$CLIENT_PORT charset=UTF8 text="CSV" head=No safe=Yes file="$user_file" query="$sql1"
if [ $? -eq 0 ]; then
echo $time_end > "$rec_file"
fi
#REDIS_IP=xx.xxx.xx.xx
#REDIS_PORT=9600
#redis_cli=/u02/redis/bin/redis-cli
#redis 集群
REDIS_IP=xx.xx.xx.xx
REDIS_PORT=19001
exec_cmd="redis.cluster"
redis_cli=/u02/redis3.2/bin/redis-cli
pass_word=xxxxx
sed -i 's/\"//g' $org_file
while read line
do
OLD_IFS="$IFS"
IFS=","
arr=($line)
bm="${arr[0]}"
mc="${arr[1]}"
lb="${arr[2]}"
bill_center_code="${arr[3]}"
echo "bm:"$bm"mc:"$mc"lb:"$lb"bill_center_code:"$bill_center_code
if [ -n "$bm" ] && [ "$bm"!=" " ]; then
echo " set $bm $mc "
echo " set $bm $mc " | $redis_cli -h $REDIS_IP -c -p $REDIS_PORT -a $pass_word
echo " set $bm""LB"" $lb "
echo " set $bm""LB"" $lb " | $redis_cli -h $REDIS_IP -c -p $REDIS_PORT -a $pass_word
fi
if [ -n "$bill_center_code" ] && [ "$bill_center_code"!=" " ]; then
echo " set $bm""BILL"" $bill_center_code "
echo " set $bm""BILL"" $bill_center_code " | $redis_cli -h $REDIS_IP -c -p $REDIS_PORT -a $pass_word
fi
done < "$org_file"
sed -i 's/\"//g' $user_file
while read line
do
OLD_IFS="$IFS"
IFS=","
arr=($line)
gh_oa="${arr[0]}"
name="${arr[1]}"
gs="${arr[2]}"
echo "gh_oa:"$gh_oa"name:"$name"gs:"$gs
if [ -n "$gh_oa" ] && [ "$gh_oa"!=" " ]; then
echo " set $gh_oa $name "
echo " set $gh_oa $name " | $redis_cli -h $REDIS_IP -c -p $REDIS_PORT -a $pass_word
echo " set $gh_oa""LB"" 78 "
echo " set $gh_oa""LB"" 78 " | $redis_cli -h $REDIS_IP -c -p $REDIS_PORT -a $pass_word
echo " set $gh_oa""BILL"" $gs "
echo " set $gh_oa""BILL"" $gs " | $redis_cli -h $REDIS_IP -c -p $REDIS_PORT -a $pass_word
fi
done < "$user_file"
exit 0
6. 业务调度.
业务代码中.原task任务迁移到这里.方便集中管理和灵活配置
7. Mysql自增键使用率监控.
这个脚本主要看 exec_sql , 这个sql 是查询库中所有自增字段（auto_increment） 的信息。包括最大值 。下一个值等。
exec_sql 去掉where条件后，查出的结果例如 ： 【[库:xx_xxn] [表:xxy_xx_sxxn] [字段:OP_ID] [类型:int] [使用率:0.00%]】
#!/bin/bash
cur_time=$(($(date +%s%N)/1000000))
send_msg_pre="警告！"
send_msg=""
notie_limit=80
access_token="钉钉的token,钉钉发通知用的"
special_table=''
exec_sql="SELECT concat('【[库:',t.TABLE_SCHEMA,'] [表:',t.table_name,'] [字段:',t.COLUMN_NAME,'] [类型:',t.data_type,'] [使用率:',concat(t.range_value,'%'),']】') '' FROM (SELECT t.*,TRUNCATE (t.next_value/t.max_value,2)*100 AS range_value FROM (SELECT c.TABLE_SCHEMA,c.table_name,c.COLUMN_NAME,c.COLUMN_TYPE,c.data_type,(CASE WHEN data_type='tinyint' AND locate('unsigned',column_type)=0 THEN 127 WHEN data_type='tinyint' AND locate('unsigned',column_type)> 0 THEN 255 WHEN data_type='smallint' AND locate('unsigned',column_type)=0 THEN 32767 WHEN data_type='smallint' AND locate('unsigned',column_type)> 0 THEN 65535 WHEN data_type='mediumint' AND locate('unsigned',column_type)=0 THEN 8388607 WHEN data_type='mediumint' AND locate('unsigned',column_type)> 0 THEN 16777215 WHEN data_type='int' AND locate('unsigned',column_type)=0 THEN 2147483647 WHEN data_type='int' AND locate('unsigned',column_type)> 0 THEN 4294967295 WHEN data_type='bigint' THEN 140737488355327 END) max_value,AUTO_INCREMENT AS next_value FROM information_schema.COLUMNS c INNER JOIN information_schema.TABLES t ON c.TABLE_SCHEMA=t.TABLE_SCHEMA AND c.table_name=t.table_name WHERE c.extra='auto_increment' $special_table) t) t WHERE range_value>=$notie_limit"
echo -e "执行sql:"$exec_sql"\n"
###############################################################################
HOST=xx.xx.xx.xx
PORT=
USER=
PASS=
## 惨无人道手机号
at_mobiles=['176xxxxxxxx','176xxxxxxxx']
cur_time=$(($(date +%s%N)/1000000))
result=$(mysql -u$USER -p$PASS -h$HOST -P$PORT -e "$exec_sql")
if [ "$result" != "" ]; then
send_msg="$HOST:$PORT预付款数据库自增键使用率："$result
echo -e "send_msg:\n$send_msg"
curl "https://oapi.dingtalk.com/robot/send?access_token=${access_token}" \-H 'Content-Type: application/json' \-d "{'msgtype': 'text','text': {'content': '$send_msg'},'at':{'atMobiles':${at_mobiles}}}"
fi
echo "$HOST:$PORT执行"$(($(($(date +%s%N)/1000000))-$cur_time))"毫秒"
###############################从这里开始. 需要监控几个数据库就写几个. 上面是公共部分. 不用动.################################################
HOST=
PORT=
USER=
PASS=
at_mobiles=['176xxxxxxxx','176xxxxxxxx']
cur_time=$(($(date +%s%N)/1000000))
result=$(mysql -u$USER -p$PASS -h$HOST -P$PORT -e "$exec_sql")
if [ "$result" != "" ]; then
send_msg="$HOST:$PORT结算、定价数据库自增键使用率："$result
echo -e "send_msg:\n$send_msg"
curl "https://oapi.dingtalk.com/robot/send?access_token=${access_token}" \-H 'Content-Type: application/json' \-d "{'msgtype': 'text','text': {'content': '$send_msg'},'at':{'atMobiles':${at_mobiles}}}"
fi
echo "$HOST:$PORT执行"$(($(($(date +%s%N)/1000000))-$cur_time))"毫秒"
8. Mysql慢查询监控.
9. 分区警告.
#!/bin/bash
cur_time=$(($(date +%s%N)/1000000))
send_msg_pre="警告！"
send_msg=""
exec_sql_file=/NAS/NAS_KY_SCHOOL/xxl_job/partition_warn.sql
### 这里面的sql 是 : SELECT CONCAT('【',PARTITION_NAME,'】',table_schema,'.',table_name) AS '' FROM (SELECT table_schema,table_name,max(PARTITION_NAME) PARTITION_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE PARTITION_NAME IS NOT NULL AND table_name NOT LIKE '%tmp%' AND table_name NOT LIKE '%20%' AND table_name NOT LIKE '%bak%' AND PARTITION_NAME LIKE '%20%' GROUP BY table_name) t WHERE concat('p',DATE_FORMAT(DATE_ADD(now(),INTERVAL 30 DAY),'%Y%m%d'))>=t.PARTITION_NAME ORDER BY PARTITION_NAME ASC
### 钉钉的一个警告群token
access_token="xxxxxxxxxxxxxxxx"
#echo $exec_sql > $exec_sql_file
while read line
do
exec_sql_info=$line
done < "$exec_sql_file"
###############################################################################
HOST=xx.xx.xx.xx
PORT=xxxx
USER=xxxxx
PASS=xxxxxxxx
echo -e "执行sql:"$exec_sql_info"\n"
result=$(mysql -u$USER -p$PASS -h$HOST -P$PORT -e "$exec_sql_info")
if [ "$result" != "" ]; then
send_msg=$send_msg"\n\n$HOST:$PORT预付款数据库当前分区："$result
echo -e "$HOST:$PORT当前分区：\n$result"
fi
echo "$HOST:$PORT执行"$(($(($(date +%s%N)/1000000))-$cur_time))"毫秒"
cur_time=$(($(date +%s%N)/1000000))
###############################################################################
HOST=xxx.xxx.xx.xxx
PORT=xxx
USER=xxx
PASS=xxxx
echo -e "执行sql:"$exec_sql_info"\n"
result=$(mysql -u$USER -p$PASS -h$HOST -P$PORT -e "$exec_sql_info")
if [ "$result" != "" ]; then
send_msg=$send_msg"\n\n$HOST:$PORT结算、定价数据库当前分区："$result
echo -e "$HOST:$PORT当前分区：\n$result"
fi
echo "$HOST:$PORT执行"$(($(($(date +%s%N)/1000000))-$cur_time))"毫秒"
cur_time=$(($(date +%s%N)/1000000))
###############################################################################
#echo -e "send_msg:\n$send_msg"
if [ "$send_msg" != "" ]; then
send_msg=$send_msg_pre$send_msg
curl "https://oapi.dingtalk.com/robot/send?access_token=${access_token}" \-H 'Content-Type: application/json' \-d "{'msgtype': 'text','text': {'content': '$send_msg'}}"
fi
exit 0
10.RabbitMQ监控.
11.Redis集群监控.
12.服务器监控.
这里的例子是 es服务器的监视例子.
#!/bin/bash
mem_warn_value=90
swap_warn_value=90
cpu_warn_value=70
jvm_warn_value=85
log_file=/u02/cpu.txt
## 钉钉通知群token
access_token="xxxxxxxxxx"
remote_port=11111
remote_user=zhangsan
send_msg=
ip_list=1.1.1.1,1.1.1.1,1.1.1.1,1.1.1.1,1.1.1.1
ip_array=(${ip_list//\,/ })
for i in "${!ip_array[@]}"; do
remote_ip=${ip_array[i]}
echo -e "$remote_ip\n"
#JVM使用率
tomcat_path='/usr/local/elasticsearch-7.6.2/lib/'
jvm_pid=$(ssh -p ${remote_port} ${remote_user}@${remote_ip} "ps -ef" | grep $tomcat_path | grep -v grep | awk '{print $2}')
java_home="/usr/local/elasticsearch-7.6.2/jdk/bin/jstat -gcutil $jvm_pid 1s 1"
echo "JVM进程号：$jvm_pid"
if [ "$jvm_pid" != "" ]; then
use_rate=$(sudo ssh -p $remote_port $remote_user@$remote_ip $java_home|head -2| grep -v O|awk '{print int($4)}')
echo "JVM使用值：$use_rate"
if (($use_rate > $jvm_warn_value)); then
result="\n"$(sudo ssh -p $remote_port $remote_user@$remote_ip $java_home)
send_msg=$send_msg"$remote_ip JVM老年代使用率超标，当前值$use_rate%："$result"\n\n"
fi
else
send_msg=$send_msg"$remote_ip JVM进程不存在\n\n"
fi
#MEM使用率
echo $(sudo ssh -p $remote_port $remote_user@$remote_ip "free -m")
use_rate=$(sudo ssh -p $remote_port $remote_user@$remote_ip "free -m" | grep Mem | awk '{print int($3/$2*100)}')
echo "MEM使用率：$use_rate"
if (($use_rate >= $mem_warn_value)); then
result="\n"$(sudo ssh -p $remote_port $remote_user@$remote_ip "ps aux|head -1" |awk '{print $2" "$3" "$4" "$11}')
result=$result"\n"$(sudo ssh -p $remote_port $remote_user@$remote_ip "ps aux|grep -v PID|sort -rn -k +4|head -5" |awk '{print $2" "$3" "$4" "substr($11,1,50)"..."}')
send_msg=$send_msg"$remote_ip 内存使用率超标，当前值$use_rate%："$result"\n\n"
fi
#Swap使用率
use_rate=$(sudo ssh -p $remote_port $remote_user@$remote_ip "free -m" | grep Swap | awk '{print int($3/$2*100)}')
echo "Swap使用率：$use_rate"
if (($use_rate >= $swap_warn_value)); then
result="\n"$(sudo ssh -p $remote_port $remote_user@$remote_ip "ps aux|head -1" |awk '{print $2" "$3" "$4" "$11}')
result=$result"\n"$(sudo ssh -p $remote_port $remote_user@$remote_ip "ps aux|grep -v PID|sort -rn -k +4|head -5" |awk '{print $2" "$3" "$4" "substr($11,1,50)"..."}')
send_msg=$send_msg"$remote_ip Swap使用率超标，当前值$use_rate%："$result"\n\n"
fi
#Cpu使用率
cpu_info=$(sudo ssh -p $remote_port $remote_user@$remote_ip "top -b -n 1" | grep Cpu)
cpu_info=${cpu_info//","/", "}
echo $cpu_info
echo $(sudo echo $cpu_info | awk '{print $8}')
use_rate=$(sudo echo $cpu_info | awk '{print int(100-$8)}')
echo "Cpu使用率：$use_rate"
if (($use_rate >= $cpu_warn_value)); then
result="\n"$(sudo echo $cpu_info)
result=$result"\n"$(sudo echo $cpu_info | awk '{print $8}')
result=$result"\n"$(sudo ssh -p $remote_port $remote_user@$remote_ip "top -b -n 1 | tail -n +7 | head -n 10" |awk '{print $1" "$2" "$9" "$12}')
send_msg=$send_msg"$remote_ip Cpu使用率超标，当前值$use_rate%："$result"\n\n"
fi
echo -e $send_msg
if [ "$send_msg" != "" ]; then
curl "https://oapi.dingtalk.com/robot/send?access_token=${access_token}" \-H 'Content-Type: application/json' \-d "{'msgtype': 'text','text': {'content': '$send_msg'}}"
fi
send_msg=""
done
exit 0
13.Mysql并发超标预警.
#!/bin/bash
common="information"
cur_time=$(($(date +%s%N)/1000000))
send_msg_pre="警告！"
send_msg=""
exec_sql_info="SELECT * FROM ${common}_schema.PROCESSLIST WHERE info IS NOT NULL AND DB IS NOT NULL AND DB<> '${common}_schema'"
#钉钉群token
access_token="17xxxxxxxxxxxxxx"
#exec_sql="SELECT CONCAT(DB,'【',concat('{并发数:',total,'} {'),(CASE WHEN time_5> 0 THEN concat('[ 5S内:',time_5,' ]') ELSE '' END),(CASE WHEN time_10> 0 THEN concat('[ 10S内:',time_10,' ]') ELSE '' END),(CASE WHEN time_20> 0 THEN concat('[ 20S内:',time_20,' ]') ELSE '' END),(CASE WHEN time_30> 0 THEN concat('[ 30S内:',time_30,' ]') ELSE '' END),(CASE WHEN time_40> 0 THEN concat('[ 40S内:',time_40,' ]') ELSE '' END),(CASE WHEN time_50> 0 THEN concat('[ 50S内:',time_50,' ]') ELSE '' END),(CASE WHEN time_60> 0 THEN concat('[ 60S内:',time_60,' ]') ELSE '' END),(CASE WHEN time_70> 0 THEN concat('[ 70S内:',time_70,' ]') ELSE '' END),(CASE WHEN time_80> 0 THEN concat('[ 80S内:',time_80,' ]') ELSE '' END),(CASE WHEN time_90> 0 THEN concat('[ 90S内:',time_90,' ]') ELSE '' END),(CASE WHEN time_100> 0 THEN concat('[ 100S内:',time_100,' ]') ELSE '' END),(CASE WHEN time_110> 0 THEN concat('[ 110S内:',time_110,' ]') ELSE '' END),(CASE WHEN time_120> 0 THEN concat('[ 120S内:',time_120,' ]') ELSE '' END),(CASE WHEN time_240> 0 THEN concat('[ 240S内:',time_240,' ]') ELSE '' END),(CASE WHEN time_other> 0 THEN concat('[ 240S以上:',time_other,' ]') ELSE '' END),'}】') '' FROM (SELECT *,sum(CASE WHEN TIME>=0 AND TIME<=50 THEN 1 ELSE 0 END) time_5,sum(CASE WHEN TIME> 5 AND TIME<=10 THEN 1 ELSE 0 END) time_10,sum(CASE WHEN TIME> 10 AND TIME<=20 THEN 1 ELSE 0 END) time_20,sum(CASE WHEN TIME> 20 AND TIME<=30 THEN 1 ELSE 0 END) time_30,sum(CASE WHEN TIME> 30 AND TIME<=40 THEN 1 ELSE 0 END) time_40,sum(CASE WHEN TIME> 40 AND TIME<=50 THEN 1 ELSE 0 END) time_50,sum(CASE WHEN TIME> 50 AND TIME<=60 THEN 1 ELSE 0 END) time_60,sum(CASE WHEN TIME> 60 AND TIME<=70 THEN 1 ELSE 0 END) time_70,sum(CASE WHEN TIME> 70 AND TIME<=80 THEN 1 ELSE 0 END) time_80,sum(CASE WHEN TIME> 80 AND TIME<=90 THEN 1 ELSE 0 END) time_90,sum(CASE WHEN TIME> 90 AND TIME<=100 THEN 1 ELSE 0 END) time_100,sum(CASE WHEN TIME> 100 AND TIME<=110 THEN 1 ELSE 0 END) time_110,sum(CASE WHEN TIME> 110 AND TIME<=120 THEN 1 ELSE 0 END) time_120,sum(CASE WHEN TIME> 120 AND TIME<=240 THEN 1 ELSE 0 END) time_240,sum(CASE WHEN TIME> 240 THEN 1 ELSE 0 END) time_other,count(1) total FROM ${common}_schema.PROCESSLIST WHERE DB IS NOT NULL GROUP BY DB) t"
###############################################################################
HOST=
PORT=
USER=
PASS=
at_mobiles=['176xxxxxxxxx','176xxxxxxxxx']
time_limit=20
process_count_limit=5
cur_time=$(($(date +%s%N)/1000000))
send_msg_pre="\n\n$HOST:$PORT张三李四1数据库session："
exec_sql="SELECT CONCAT(DB,'【',concat('{并发数:',total,'} {'),(CASE WHEN time_5> 0 THEN concat('[ 5S内:',time_5,' ]') ELSE '' END),(CASE WHEN time_10> 0 THEN concat('[ 10S内:',time_10,' ]') ELSE '' END),(CASE WHEN time_20> 0 THEN concat('[ 20S内:',time_20,' ]') ELSE '' END),(CASE WHEN time_30> 0 THEN concat('[ 30S内:',time_30,' ]') ELSE '' END),(CASE WHEN time_40> 0 THEN concat('[ 40S内:',time_40,' ]') ELSE '' END),(CASE WHEN time_50> 0 THEN concat('[ 50S内:',time_50,' ]') ELSE '' END),(CASE WHEN time_60> 0 THEN concat('[ 60S内:',time_60,' ]') ELSE '' END),(CASE WHEN time_70> 0 THEN concat('[ 70S内:',time_70,' ]') ELSE '' END),(CASE WHEN time_80> 0 THEN concat('[ 80S内:',time_80,' ]') ELSE '' END),(CASE WHEN time_90> 0 THEN concat('[ 90S内:',time_90,' ]') ELSE '' END),(CASE WHEN time_100> 0 THEN concat('[ 100S内:',time_100,' ]') ELSE '' END),(CASE WHEN time_110> 0 THEN concat('[ 110S内:',time_110,' ]') ELSE '' END),(CASE WHEN time_120> 0 THEN concat('[ 120S内:',time_120,' ]') ELSE '' END),(CASE WHEN time_240> 0 THEN concat('[ 240S内:',time_240,' ]') ELSE '' END),(CASE WHEN time_other> 0 THEN concat('[ 240S以上:',time_other,' ]') ELSE '' END),'}】') '' FROM (SELECT *,sum(CASE WHEN TIME>=0 AND TIME<=50 THEN 1 ELSE 0 END) time_5,sum(CASE WHEN TIME> 5 AND TIME<=10 THEN 1 ELSE 0 END) time_10,sum(CASE WHEN TIME> 10 AND TIME<=20 THEN 1 ELSE 0 END) time_20,sum(CASE WHEN TIME> 20 AND TIME<=30 THEN 1 ELSE 0 END) time_30,sum(CASE WHEN TIME> 30 AND TIME<=40 THEN 1 ELSE 0 END) time_40,sum(CASE WHEN TIME> 40 AND TIME<=50 THEN 1 ELSE 0 END) time_50,sum(CASE WHEN TIME> 50 AND TIME<=60 THEN 1 ELSE 0 END) time_60,sum(CASE WHEN TIME> 60 AND TIME<=70 THEN 1 ELSE 0 END) time_70,sum(CASE WHEN TIME> 70 AND TIME<=80 THEN 1 ELSE 0 END) time_80,sum(CASE WHEN TIME> 80 AND TIME<=90 THEN 1 ELSE 0 END) time_90,sum(CASE WHEN TIME> 90 AND TIME<=100 THEN 1 ELSE 0 END) time_100,sum(CASE WHEN TIME> 100 AND TIME<=110 THEN 1 ELSE 0 END) time_110,sum(CASE WHEN TIME> 110 AND TIME<=120 THEN 1 ELSE 0 END) time_120,sum(CASE WHEN TIME> 120 AND TIME<=240 THEN 1 ELSE 0 END) time_240,sum(CASE WHEN TIME> 240 THEN 1 ELSE 0 END) time_other,count(1) total FROM ${common}_schema.PROCESSLIST WHERE info IS NOT NULL AND DB IS NOT NULL AND DB<> '${common}_schema' AND TIME>=${time_limit} GROUP BY DB) t WHERE t.total>=${process_count_limit}"
echo -e "执行sql:"$exec_sql"\n"
cur_time=$(($(date +%s%N)/1000000))
result=$(mysql -u$USER -p$PASS -h$HOST -P$PORT -e "$exec_sql")
if [ "$result" != "" ]; then
send_msg=$send_msg_pre$result
result=$(mysql -u$USER -p$PASS -h$HOST -P$PORT -e "$exec_sql_info")
echo -e "$HOST:$PORT当前线程：\n$result"
fi
echo "$HOST:$PORT执行"$(($(($(date +%s%N)/1000000))-$cur_time))"毫秒"
echo -e "send_msg:\n$send_msg"
if [ "$send_msg" != "" ]; then
curl "https://oapi.dingtalk.com/robot/send?access_token=${access_token}" \-H 'Content-Type: application/json' \-d "{'msgtype': 'text','text': {'content': '$send_msg'},'at':{'atMobiles':${at_mobiles}}}"
fi
##################################第二个数据库##########################################
HOST=
PORT=
USER=
PASS=
at_mobiles=['176xxxxxxxxx','176xxxxxxxxx']
time_limit=20
process_count_limit=10
cur_time=$(($(date +%s%N)/1000000))
send_msg_pre="\n\n$HOST:$PORT张三李四2数据库session："
send_msg=""
exec_sql="SELECT CONCAT(DB,'【',concat('{并发数:',total,'} {'),(CASE WHEN time_5> 0 THEN concat('[ 5S内:',time_5,' ]') ELSE '' END),(CASE WHEN time_10> 0 THEN concat('[ 10S内:',time_10,' ]') ELSE '' END),(CASE WHEN time_20> 0 THEN concat('[ 20S内:',time_20,' ]') ELSE '' END),(CASE WHEN time_30> 0 THEN concat('[ 30S内:',time_30,' ]') ELSE '' END),(CASE WHEN time_40> 0 THEN concat('[ 40S内:',time_40,' ]') ELSE '' END),(CASE WHEN time_50> 0 THEN concat('[ 50S内:',time_50,' ]') ELSE '' END),(CASE WHEN time_60> 0 THEN concat('[ 60S内:',time_60,' ]') ELSE '' END),(CASE WHEN time_70> 0 THEN concat('[ 70S内:',time_70,' ]') ELSE '' END),(CASE WHEN time_80> 0 THEN concat('[ 80S内:',time_80,' ]') ELSE '' END),(CASE WHEN time_90> 0 THEN concat('[ 90S内:',time_90,' ]') ELSE '' END),(CASE WHEN time_100> 0 THEN concat('[ 100S内:',time_100,' ]') ELSE '' END),(CASE WHEN time_110> 0 THEN concat('[ 110S内:',time_110,' ]') ELSE '' END),(CASE WHEN time_120> 0 THEN concat('[ 120S内:',time_120,' ]') ELSE '' END),(CASE WHEN time_240> 0 THEN concat('[ 240S内:',time_240,' ]') ELSE '' END),(CASE WHEN time_other> 0 THEN concat('[ 240S以上:',time_other,' ]') ELSE '' END),'}】') '' FROM (SELECT *,sum(CASE WHEN TIME>=0 AND TIME<=50 THEN 1 ELSE 0 END) time_5,sum(CASE WHEN TIME> 5 AND TIME<=10 THEN 1 ELSE 0 END) time_10,sum(CASE WHEN TIME> 10 AND TIME<=20 THEN 1 ELSE 0 END) time_20,sum(CASE WHEN TIME> 20 AND TIME<=30 THEN 1 ELSE 0 END) time_30,sum(CASE WHEN TIME> 30 AND TIME<=40 THEN 1 ELSE 0 END) time_40,sum(CASE WHEN TIME> 40 AND TIME<=50 THEN 1 ELSE 0 END) time_50,sum(CASE WHEN TIME> 50 AND TIME<=60 THEN 1 ELSE 0 END) time_60,sum(CASE WHEN TIME> 60 AND TIME<=70 THEN 1 ELSE 0 END) time_70,sum(CASE WHEN TIME> 70 AND TIME<=80 THEN 1 ELSE 0 END) time_80,sum(CASE WHEN TIME> 80 AND TIME<=90 THEN 1 ELSE 0 END) time_90,sum(CASE WHEN TIME> 90 AND TIME<=100 THEN 1 ELSE 0 END) time_100,sum(CASE WHEN TIME> 100 AND TIME<=110 THEN 1 ELSE 0 END) time_110,sum(CASE WHEN TIME> 110 AND TIME<=120 THEN 1 ELSE 0 END) time_120,sum(CASE WHEN TIME> 120 AND TIME<=240 THEN 1 ELSE 0 END) time_240,sum(CASE WHEN TIME> 240 THEN 1 ELSE 0 END) time_other,count(1) total FROM ${common}_schema.PROCESSLIST WHERE info IS NOT NULL AND DB IS NOT NULL AND DB<> '${common}_schema' AND TIME>=${time_limit} GROUP BY DB) t WHERE t.total>=${process_count_limit}"
echo -e "执行sql:"$exec_sql"\n"
cur_time=$(($(date +%s%N)/1000000))
result=$(mysql -u$USER -p$PASS -h$HOST -P$PORT -e "$exec_sql")
if [ "$result" != "" ]; then
send_msg=$send_msg_pre$result
result=$(mysql -u$USER -p$PASS -h$HOST -P$PORT -e "$exec_sql_info")
echo -e "$HOST:$PORT当前线程：\n$result"
fi
echo "$HOST:$PORT执行"$(($(($(date +%s%N)/1000000))-$cur_time))"毫秒"
echo -e "send_msg:\n$send_msg"
if [ "$send_msg" != "" ]; then
curl "https://oapi.dingtalk.com/robot/send?access_token=${access_token}" \-H 'Content-Type: application/json' \-d "{'msgtype': 'text','text': {'content': '$send_msg'},'at':{'atMobiles':${at_mobiles}}}"
fi
#############################数据库3####################################
HOST=
PORT=
USER=
PASS=
at_mobiles=['176xxxxxxxx','176xxxxxxxxx']
time_limit=20
process_count_limit=10
cur_time=$(($(date +%s%N)/1000000))
send_msg=""
send_msg_pre="\n\n$HOST:$PORT张三李四数据库session："
exec_sql="SELECT CONCAT(DB,'【',concat('{并发数:',total,'} {'),(CASE WHEN time_5> 0 THEN concat('[ 5S内:',time_5,' ]') ELSE '' END),(CASE WHEN time_10> 0 THEN concat('[ 10S内:',time_10,' ]') ELSE '' END),(CASE WHEN time_20> 0 THEN concat('[ 20S内:',time_20,' ]') ELSE '' END),(CASE WHEN time_30> 0 THEN concat('[ 30S内:',time_30,' ]') ELSE '' END),(CASE WHEN time_40> 0 THEN concat('[ 40S内:',time_40,' ]') ELSE '' END),(CASE WHEN time_50> 0 THEN concat('[ 50S内:',time_50,' ]') ELSE '' END),(CASE WHEN time_60> 0 THEN concat('[ 60S内:',time_60,' ]') ELSE '' END),(CASE WHEN time_70> 0 THEN concat('[ 70S内:',time_70,' ]') ELSE '' END),(CASE WHEN time_80> 0 THEN concat('[ 80S内:',time_80,' ]') ELSE '' END),(CASE WHEN time_90> 0 THEN concat('[ 90S内:',time_90,' ]') ELSE '' END),(CASE WHEN time_100> 0 THEN concat('[ 100S内:',time_100,' ]') ELSE '' END),(CASE WHEN time_110> 0 THEN concat('[ 110S内:',time_110,' ]') ELSE '' END),(CASE WHEN time_120> 0 THEN concat('[ 120S内:',time_120,' ]') ELSE '' END),(CASE WHEN time_240> 0 THEN concat('[ 240S内:',time_240,' ]') ELSE '' END),(CASE WHEN time_other> 0 THEN concat('[ 240S以上:',time_other,' ]') ELSE '' END),'}】') '' FROM (SELECT *,sum(CASE WHEN TIME>=0 AND TIME<=50 THEN 1 ELSE 0 END) time_5,sum(CASE WHEN TIME> 5 AND TIME<=10 THEN 1 ELSE 0 END) time_10,sum(CASE WHEN TIME> 10 AND TIME<=20 THEN 1 ELSE 0 END) time_20,sum(CASE WHEN TIME> 20 AND TIME<=30 THEN 1 ELSE 0 END) time_30,sum(CASE WHEN TIME> 30 AND TIME<=40 THEN 1 ELSE 0 END) time_40,sum(CASE WHEN TIME> 40 AND TIME<=50 THEN 1 ELSE 0 END) time_50,sum(CASE WHEN TIME> 50 AND TIME<=60 THEN 1 ELSE 0 END) time_60,sum(CASE WHEN TIME> 60 AND TIME<=70 THEN 1 ELSE 0 END) time_70,sum(CASE WHEN TIME> 70 AND TIME<=80 THEN 1 ELSE 0 END) time_80,sum(CASE WHEN TIME> 80 AND TIME<=90 THEN 1 ELSE 0 END) time_90,sum(CASE WHEN TIME> 90 AND TIME<=100 THEN 1 ELSE 0 END) time_100,sum(CASE WHEN TIME> 100 AND TIME<=110 THEN 1 ELSE 0 END) time_110,sum(CASE WHEN TIME> 110 AND TIME<=120 THEN 1 ELSE 0 END) time_120,sum(CASE WHEN TIME> 120 AND TIME<=240 THEN 1 ELSE 0 END) time_240,sum(CASE WHEN TIME> 240 THEN 1 ELSE 0 END) time_other,count(1) total FROM ${common}_schema.PROCESSLIST WHERE info IS NOT NULL AND DB IS NOT NULL AND DB<> '${common}_schema' AND TIME>=${time_limit} GROUP BY DB) t WHERE t.total>=${process_count_limit}"
echo -e "执行sql:"$exec_sql"\n"
cur_time=$(($(date +%s%N)/1000000))
result=$(mysql -u$USER -p$PASS -h$HOST -P$PORT -e "$exec_sql")
if [ "$result" != "" ]; then
send_msg=$send_msg_pre$result
result=$(mysql -u$USER -p$PASS -h$HOST -P$PORT -e "$exec_sql_info")
echo -e "$HOST:$PORT当前线程：\n$result"
fi
echo "$HOST:$PORT执行"$(($(($(date +%s%N)/1000000))-$cur_time))"毫秒"
echo -e "send_msg:\n$send_msg"
if [ "$send_msg" != "" ]; then
curl "https://oapi.dingtalk.com/robot/send?access_token=${access_token}" \-H 'Content-Type: application/json' \-d "{'msgtype': 'text','text': {'content': '$send_msg'},'at':{'atMobiles':${at_mobiles}}}"
fi
14.ES日志服务清理历史日志（按月）
#!/bin/bash
## es 地址。 账号密码
request_ip="xx.xx.xx.xx:9200"
request_user="elastic"
request_pass="xxxxxxxx"
#*skywalking*month-###################################################
echo -e "\n"
index_pre="*skywalking*month-"
echo -e "-------------${index_pre}：开始清理-------------"
time_limit=-41
request_url="http://${request_ip}/${index_pre}$(date -d "${time_limit} day " +%Y%m)*"
echo -e "request_url：${request_url}"
result=$(curl --user ${request_user}:${request_pass} -XGET "http://${request_ip}/_cat/indices?index=${index_pre}$(date -d "${time_limit} day " +%Y%m)*")
echo -e "${index_pre}查询结果:\n${result}"
result=$(curl --user ${request_user}:${request_pass} -XDELETE ${request_url})
echo -e "${index_pre}清理结果：$result"
echo -e "-------------${index_pre}：结束清理-------------"
echo -e "\n"
exit 0
... ...
