## 1. 巡检内容
– 集群状态：检查集群是否正常运行
– 组件状态：检查TiDB、TiKV、PD是否正常运行
– 性能状态：检查QPS、响应时间等
– 资源使用：检查CPU、内存、磁盘使用率
– 连接状态：检查连接数是否正常

## 2. 巡检脚本
$ cat > daily_check.sh << EOF #!/bin/bash # daily_check.sh # from:www.itpux.com.qq113257174.wx:itpux-com # web: http://www.fgedu.net.cn # 配置 TIUP_BIN="/tidb/app/tiup/bin/tiup" MYSQL_BIN="/tidb/app/mysql/bin/mysql" HOST="192.168.1.100" PORT="4000" USER="root" PASSWORD="password" DB="fgedudb" LOG_FILE="/tidb/logs/daily_check.log" # 日志函数 log() { echo "[$(date +'%Y-%m-%d %H:%M:%S')] $1">> $LOG_FILE
echo “[$(date +’%Y-%m-%d %H:%M:%S’)] $1”
}

# 检查集群状态
log “开始检查集群状态”
$TIUP_BIN cluster display fgedu-tidb-cluster >> $LOG_FILE

# 检查TiDB状态
log “开始检查TiDB状态”
$MYSQL_BIN -h $HOST -P $PORT -u $USER -p$PASSWORD -e “SHOW STATUS LIKE ‘Queries’;” >> $LOG_FILE
$MYSQL_BIN -h $HOST -P $PORT -u $USER -p$PASSWORD -e “SHOW STATUS LIKE ‘Slow_queries’;” >> $LOG_FILE
$MYSQL_BIN -h $HOST -P $PORT -u $USER -p$PASSWORD -e “SHOW STATUS LIKE ‘Threads_connected’;” >> $LOG_FILE

# 检查资源使用
log “开始检查资源使用”
top -b -n 1 | head -20 >> $LOG_FILE
df -h >> $LOG_FILE
free -h >> $LOG_FILE

# 检查磁盘空间
log “开始检查磁盘空间”
df -h | grep /tidb >> $LOG_FILE

# 检查网络状态
log “开始检查网络状态”
ping -c 4 $HOST >> $LOG_FILE
netstat -tuln | grep 4000 >> $LOG_FILE

log “日常巡检完成”
EOF

## 3. 执行脚本
$ chmod +x daily_check.sh
$ ./daily_check.sh

## 4. 定时执行
$ crontab -e
# 添加以下内容
0 8 * * * /tidb/scripts/daily_check.sh

## 1. 巡检内容
– 日常巡检内容
– 集群配置：检查集群配置是否正确
– 备份状态：检查备份是否正常
– 慢查询分析：分析慢查询
– 性能趋势：分析性能趋势

## 2. 巡检脚本
$ cat > weekly_check.sh << EOF #!/bin/bash # weekly_check.sh # from:www.itpux.com.qq113257174.wx:itpux-com # web: http://www.fgedu.net.cn # 配置 TIUP_BIN="/tidb/app/tiup/bin/tiup" MYSQL_BIN="/tidb/app/mysql/bin/mysql" HOST="192.168.1.100" PORT="4000" USER="root" PASSWORD="password" DB="fgedudb" LOG_FILE="/tidb/logs/weekly_check.log" REPORT_FILE="/tidb/reports/weekly_report_$(date +'%Y%m%d').txt" # 日志函数 log() { echo "[$(date +'%Y-%m-%d %H:%M:%S')] $1">> $LOG_FILE
echo “[$(date +’%Y-%m-%d %H:%M:%S’)] $1”
}

# 生成报告
generate_report() {
echo “# TiDB周度巡检报告” > $REPORT_FILE
echo “生成时间: $(date +’%Y-%m-%d %H:%M:%S’)” >> $REPORT_FILE
echo “” >> $REPORT_FILE
echo “## 1. 集群状态” >> $REPORT_FILE
$TIUP_BIN cluster display fgedu-tidb-cluster >> $REPORT_FILE
echo “” >> $REPORT_FILE
echo “## 2. 性能状态” >> $REPORT_FILE
$MYSQL_BIN -h $HOST -P $PORT -u $USER -p$PASSWORD -e “SHOW STATUS LIKE ‘Queries’;” >> $REPORT_FILE
$MYSQL_BIN -h $HOST -P $PORT -u $USER -p$PASSWORD -e “SHOW STATUS LIKE ‘Slow_queries’;” >> $REPORT_FILE
$MYSQL_BIN -h $HOST -P $PORT -u $USER -p$PASSWORD -e “SHOW STATUS LIKE ‘Threads_connected’;” >> $REPORT_FILE
echo “” >> $REPORT_FILE
echo “## 3. 资源使用” >> $REPORT_FILE
top -b -n 1 | head -20 >> $REPORT_FILE
df -h >> $REPORT_FILE
free -h >> $REPORT_FILE
echo “” >> $REPORT_FILE
echo “## 4. 慢查询分析” >> $REPORT_FILE
$MYSQL_BIN -h $HOST -P $PORT -u $USER -p$PASSWORD -e “SELECT * FROM information_schema.slow_query_log ORDER BY
start_time DESC LIMIT 10;” >> $REPORT_FILE
echo “” >> $REPORT_FILE
echo “## 5. 备份状态” >> $REPORT_FILE
ls -la /tidb/backup >> $REPORT_FILE
}

# 执行巡检
log “开始周度巡检”

# 检查集群状态
log “开始检查集群状态”
$TIUP_BIN cluster display fgedu-tidb-cluster >> $LOG_FILE

# 检查TiDB状态
log “开始检查TiDB状态”
$MYSQL_BIN -h $HOST -P $PORT -u $USER -p$PASSWORD -e “SHOW STATUS LIKE ‘Queries’;” >> $LOG_FILE
$MYSQL_BIN -h $HOST -P $PORT -u $USER -p$PASSWORD -e “SHOW STATUS LIKE ‘Slow_queries’;” >> $LOG_FILE
$MYSQL_BIN -h $HOST -P $PORT -u $USER -p$PASSWORD -e “SHOW STATUS LIKE ‘Threads_connected’;” >> $LOG_FILE

# 检查资源使用
log “开始检查资源使用”
top -b -n 1 | head -20 >> $LOG_FILE
df -h >> $LOG_FILE
free -h >> $LOG_FILE

# 检查磁盘空间
log “开始检查磁盘空间”
df -h | grep /tidb >> $LOG_FILE

# 检查网络状态
log “开始检查网络状态”
ping -c 4 $HOST >> $LOG_FILE
netstat -tuln | grep 4000 >> $LOG_FILE

# 检查备份状态
log “开始检查备份状态”
ls -la /tidb/backup >> $LOG_FILE

# 分析慢查询
log “开始分析慢查询”
$MYSQL_BIN -h $HOST -P $PORT -u $USER -p$PASSWORD -e “SELECT * FROM information_schema.slow_query_log ORDER BY
start_time DESC LIMIT 10;” >> $LOG_FILE

# 生成报告
log “开始生成巡检报告”
generate_report

log “周度巡检完成”
EOF

## 3. 执行脚本
$ chmod +x weekly_check.sh
$ ./weekly_check.sh

## 4. 定时执行
$ crontab -e
# 添加以下内容
0 9 * * 1 /tidb/scripts/weekly_check.sh

## 1. 脚本结构
$ cat > basic_check.sh << EOF #!/bin/bash # basic_check.sh # from:www.itpux.com.qq113257174.wx:itpux-com # web: http://www.fgedu.net.cn # 配置 TIUP_BIN="/tidb/app/tiup/bin/tiup" MYSQL_BIN="/tidb/app/mysql/bin/mysql" HOST="192.168.1.100" PORT="4000" USER="root" PASSWORD="password" DB="fgedudb" LOG_FILE="/tidb/logs/basic_check.log" # 日志函数 log() { echo "[$(date +'%Y-%m-%d %H:%M:%S')] $1">> $LOG_FILE
echo “[$(date +’%Y-%m-%d %H:%M:%S’)] $1”
}

# 错误处理函数
error_handler() {
log “错误: $1”
exit 1
}

# 检查命令是否存在
check_command() {
if ! command -v $1 &> /dev/null; then
error_handler “命令 $1 不存在”
fi
}

# 检查集群状态
check_cluster_status() {
log “检查集群状态”
$TIUP_BIN cluster display fgedu-tidb-cluster >> $LOG_FILE
if [ $? -ne 0 ]; then
error_handler “集群状态检查失败”
fi
}

# 检查TiDB状态
check_tidb_status() {
log “检查TiDB状态”
$MYSQL_BIN -h $HOST -P $PORT -u $USER -p$PASSWORD -e “SHOW STATUS LIKE ‘Queries’;” >> $LOG_FILE
if [ $? -ne 0 ]; then
error_handler “TiDB状态检查失败”
fi
}

# 检查资源使用
check_resource_usage() {
log “检查资源使用”
top -b -n 1 | head -20 >> $LOG_FILE
df -h >> $LOG_FILE
free -h >> $LOG_FILE
}

# 检查磁盘空间
check_disk_space() {
log “检查磁盘空间”
df -h | grep /tidb >> $LOG_FILE
DISK_USAGE=$(df -h | grep /tidb | awk ‘{print $5}’ | sed ‘s/%//g’)
if [ $DISK_USAGE -gt 80 ]; then
log “警告: 磁盘使用率超过80%”
fi
}

# 检查网络状态
check_network_status() {
log “检查网络状态”
ping -c 4 $HOST >> $LOG_FILE
netstat -tuln | grep 4000 >> $LOG_FILE
}

# 主函数
main() {
log “开始基础巡检”

# 检查命令
check_command $TIUP_BIN
check_command $MYSQL_BIN

# 检查集群状态
check_cluster_status

# 检查TiDB状态
check_tidb_status

# 检查资源使用
check_resource_usage

# 检查磁盘空间
check_disk_space

# 检查网络状态
check_network_status

log “基础巡检完成”
}

# 执行主函数
main
EOF

## 2. 执行脚本
$ chmod +x basic_check.sh
$ ./basic_check.sh

## 3. 测试脚本
$ ./basic_check.sh
# 输出示例
# [2024-01-01 00:00:00] 开始基础巡检
# [2024-01-01 00:00:00] 检查集群状态
# [2024-01-01 00:00:00] 检查TiDB状态
# [2024-01-01 00:00:00] 检查资源使用
# [2024-01-01 00:00:00] 检查磁盘空间
# [2024-01-01 00:00:00] 检查网络状态
# [2024-01-01 00:00:00] 基础巡检完成

## 1. 脚本结构
$ cat > advanced_check.py << EOF #!/usr/bin/env python3 # advanced_check.py # from:www.itpux.com.qq113257174.wx:itpux-com # web: http://www.fgedu.net.cn import os import sys import subprocess import datetime import smtplib from email.mime.text import MIMEText from email.mime.multipart import MIMEMultipart # 配置 TIUP_BIN="/tidb/app/tiup/bin/tiup" MYSQL_BIN="/tidb/app/mysql/bin/mysql" HOST="192.168.1.100" PORT="4000" USER="root" PASSWORD="password" DB="fgedudb" LOG_FILE="/tidb/logs/advanced_check.log" REPORT_FILE="/tidb/reports/advanced_report_{}.txt" .format(datetime.datetime.now().strftime("%Y%m%d")) # 日志函数 def log(message): timestamp=datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S") log_message="[{}] {}" .format(timestamp, message) with open(LOG_FILE, "a" ) as f: f.write(log_message + "\n" ) print(log_message) # 错误处理函数 def error_handler(message): log("错误: {}".format(message)) sys.exit(1) # 检查命令是否存在 def check_command(command): if subprocess.call(["which", command], stdout=subprocess.PIPE, stderr=subprocess.PIPE) !=0: error_handler("命令 {} 不存在".format(command)) # 执行命令 def execute_command(command): try: result=subprocess.check_output(command, shell=True, stderr=subprocess.STDOUT, universal_newlines=True) return result except subprocess.CalledProcessError as e: error_handler("命令执行失败: {}\n输出: {}".format(command, e.output)) # 检查集群状态 def check_cluster_status(): log("检查集群状态") result=execute_command("{} cluster display fgedu-tidb-cluster".format(TIUP_BIN)) with open(LOG_FILE, "a" ) as f: f.write(result) return result # 检查TiDB状态 def check_tidb_status(): log("检查TiDB状态") queries=execute_command("{} -h {} -P {} -u {} -p{} -e 'SHOW STATUS LIKE "Queries";'" .format(MYSQL_BIN, HOST, PORT, USER, PASSWORD)) slow_queries = execute_command(" {} -h {} -P {} -u {} -p{} -e 'SHOW STATUS LIKE "Slow_queries";'" .format(MYSQL_BIN, HOST, PORT, USER, PASSWORD)) connections = execute_command(" {} -h {} -P {} -u {} -p{} -e 'SHOW STATUS LIKE "Threads_connected";'" .format(MYSQL_BIN, HOST, PORT, USER, PASSWORD)) with open(LOG_FILE, " a") as f: f.write(queries) f.write(slow_queries) f.write(connections) return queries, slow_queries, connections # 检查资源使用 def check_resource_usage(): log("检查资源使用") top=execute_command("top -b -n 1 | head -20") df=execute_command("df -h") free=execute_command("free -h") with open(LOG_FILE, "a" ) as f: f.write(top) f.write(df) f.write(free) return top, df, free # 检查磁盘空间 def check_disk_space(): log("检查磁盘空间") df=execute_command("df -h | grep /tidb") with open(LOG_FILE, "a" ) as f: f.write(df) # 检查磁盘使用率 for line in df.split("\n"): if line: parts=line.split() if len(parts)>= 5:
usage = parts[4].replace(“%”, “”)
if int(usage) > 80:
log(“警告: 磁盘使用率超过80%: {}”.format(line))

return df

# 检查网络状态
def check_network_status():
log(“检查网络状态”)
ping = execute_command(“ping -c 4 {}”.format(HOST))
netstat = execute_command(“netstat -tuln | grep 4000”)

with open(LOG_FILE, “a”) as f:
f.write(ping)
f.write(netstat)

return ping, netstat

# 检查备份状态
def check_backup_status():
log(“检查备份状态”)
backup = execute_command(“ls -la /tidb/backup”)
with open(LOG_FILE, “a”) as f:
f.write(backup)
return backup

# 分析慢查询
def analyze_slow_queries():
log(“分析慢查询”)
slow_queries = execute_command(“{} -h {} -P {} -u {} -p{} -e ‘SELECT * FROM information_schema.slow_query_log
ORDER BY start_time DESC LIMIT 10;'”
.format(MYSQL_BIN, HOST, PORT, USER, PASSWORD))
with open(LOG_FILE, “a”) as f:
f.write(slow_queries)
return slow_queries

# 生成报告
def generate_report(cluster_status, tidb_status, resource_usage, disk_space, network_status, backup_status,
slow_queries):
log(“生成巡检报告”)

with open(REPORT_FILE, “w”) as f:
f.write(“# TiDB高级巡检报告\n”)
f.write(“生成时间: {}\n\n”.format(datetime.datetime.now().strftime(“%Y-%m-%d %H:%M:%S”)))

f.write(“## 1. 集群状态\n”)
f.write(cluster_status)
f.write(“\n”)

f.write(“## 2. TiDB状态\n”)
f.write(tidb_status[0])
f.write(tidb_status[1])
f.write(tidb_status[2])
f.write(“\n”)

f.write(“## 3. 资源使用\n”)
f.write(resource_usage[0])
f.write(resource_usage[1])
f.write(resource_usage[2])
f.write(“\n”)

f.write(“## 4. 磁盘空间\n”)
f.write(disk_space)
f.write(“\n”)

f.write(“## 5. 网络状态\n”)
f.write(network_status[0])
f.write(network_status[1])
f.write(“\n”)

f.write(“## 6. 备份状态\n”)
f.write(backup_status)
f.write(“\n”)

f.write(“## 7. 慢查询分析\n”)
f.write(slow_queries)
f.write(“\n”)

return REPORT_FILE

# 发送邮件
def send_email(report_file):
log(“发送巡检报告”)

# 邮件配置
smtp_server = “smtp.example.com”
smtp_port = 587
smtp_user = “admin@example.com”
smtp_password = “password”
sender = “admin@example.com”
receivers = [“admin@example.com”]

# 读取报告内容
with open(report_file, “r”) as f:
report_content = f.read()

# 创建邮件
msg = MIMEMultipart()
msg[“From”] = sender
msg[“To”] = “, “.join(receivers)
msg[“Subject”] = “TiDB高级巡检报告”

# 添加邮件正文
part = MIMEText(report_content, “plain”, “utf-8”)
msg.attach(part)

# 发送邮件
try:
server = smtplib.SMTP(smtp_server, smtp_port)
server.starttls()
server.login(smtp_user, smtp_password)
server.send_message(msg)
server.quit()
log(“邮件发送成功”)
except Exception as e:
error_handler(“邮件发送失败: {}”.format(e))

# 主函数
def main():
log(“开始高级巡检”)

# 检查命令
check_command(TIUP_BIN)
check_command(MYSQL_BIN)

# 检查集群状态
cluster_status = check_cluster_status()

# 检查TiDB状态
tidb_status = check_tidb_status()

# 检查资源使用
resource_usage = check_resource_usage()

# 检查磁盘空间
disk_space = check_disk_space()

# 检查网络状态
network_status = check_network_status()

# 检查备份状态
backup_status = check_backup_status()

# 分析慢查询
slow_queries = analyze_slow_queries()

# 生成报告
report_file = generate_report(cluster_status, tidb_status, resource_usage, disk_space, network_status,
backup_status, slow_queries)

# 发送邮件
send_email(report_file)

log(“高级巡检完成”)

# 执行主函数
if __name__ == “__main__”:
main
EOF

## 2. 执行脚本
$ chmod +x advanced_check.py
$ python3 advanced_check.py

## 3. 测试脚本
$ python3 advanced_check.py
# 输出示例
# [2024-01-01 00:00:00] 开始高级巡检
# [2024-01-01 00:00:00] 检查集群状态
# [2024-01-01 00:00:00] 检查TiDB状态
# [2024-01-01 00:00:00] 检查资源使用
# [2024-01-01 00:00:00] 检查磁盘空间
# [2024-01-01 00:00:00] 检查网络状态
# [2024-01-01 00:00:00] 检查备份状态
# [2024-01-01 00:00:00] 分析慢查询
# [2024-01-01 00:00:00] 生成巡检报告
# [2024-01-01 00:00:00] 发送巡检报告
# [2024-01-01 00:00:00] 邮件发送成功
# [2024-01-01 00:00:00] 高级巡检完成

## 1. 创建脚本目录
$ mkdir -p /tidb/scripts
$ mkdir -p /tidb/logs
$ mkdir -p /tidb/reports

## 2. 复制脚本
$ cp daily_check.sh /tidb/scripts/
$ cp weekly_check.sh /tidb/scripts/
$ cp basic_check.sh /tidb/scripts/
$ cp advanced_check.py /tidb/scripts/

## 3. 设置权限
$ chmod +x /tidb/scripts/*.sh
$ chmod +x /tidb/scripts/*.py

## 4. 配置环境变量
$ cat > /etc/profile.d/tidb_scripts.sh << EOF export TIUP_BIN="/tidb/app/tiup/bin/tiup" export MYSQL_BIN="/tidb/app/mysql/bin/mysql" export TIDB_HOST="192.168.1.100" export TIDB_PORT="4000" export TIDB_USER="root" export TIDB_PASSWORD="password" export TIDB_DB="fgedudb" EOF $ source /etc/profile.d/tidb_scripts.sh ## 5. 测试脚本 $ /tidb/scripts/daily_check.sh $ /tidb/scripts/weekly_check.sh $ /tidb/scripts/basic_check.sh $ python3 /tidb/scripts/advanced_check.py ## 6. 配置定时任务 $ crontab -e # 添加以下内容 # 日常巡检 0 8 * * * /tidb/scripts/daily_check.sh # 周度巡检 0 9 * * 1 /tidb/scripts/weekly_check.sh # 基础巡检 0 12 * * * /tidb/scripts/basic_check.sh # 高级巡检 0 10 * * 0 /tidb/scripts/advanced_check.py

## 1. 配置告警脚本
$ cat > alert.sh << EOF #!/bin/bash # alert.sh # from:www.itpux.com.qq113257174.wx:itpux-com # web: http://www.fgedu.net.cn # 配置 ALERT_LEVEL="$1" ALERT_MESSAGE="$2" LOG_FILE="/tidb/logs/alert.log" # 日志函数 log() { echo "[$(date +'%Y-%m-%d %H:%M:%S')] $1">> $LOG_FILE
echo “[$(date +’%Y-%m-%d %H:%M:%S’)] $1″
}

# 发送邮件
send_email() {
# 邮件配置
SMTP_SERVER=”smtp.example.com”
SMTP_PORT=”587″
SMTP_USER=”admin@example.com”
SMTP_PASSWORD=”password”
SENDER=”admin@example.com”
RECEIVERS=”admin@example.com”

# 创建邮件内容
SUBJECT=”[TiDB告警] $ALERT_LEVEL: $ALERT_MESSAGE”
BODY=”告警级别: $ALERT_LEVEL\n告警信息: $ALERT_MESSAGE\n告警时间: $(date +’%Y-%m-%d %H:%M:%S’)”

# 发送邮件
echo “$BODY” | mail -s “$SUBJECT” -r “$SENDER” $RECEIVERS
}

# 发送微信通知
send_wechat() {
# 微信配置
CORP_ID=”your_corp_id”
AGENT_ID=”your_agent_id”
API_SECRET=”your_api_secret”
TO_USER=”@all”

# 创建消息内容
MESSAGE=”告警级别: $ALERT_LEVEL\n告警信息: $ALERT_MESSAGE\n告警时间: $(date +’%Y-%m-%d %H:%M:%S’)”

# 发送微信通知
# 这里需要调用企业微信API，具体实现略
echo “发送微信通知: $MESSAGE”
}

# 主函数
main() {
log “告警: $ALERT_LEVEL – $ALERT_MESSAGE”

# 发送邮件
send_email

# 发送微信通知
send_wechat
}

# 执行主函数
main
EOF

## 2. 配置巡检脚本调用告警
$ cat >> /tidb/scripts/daily_check.sh << EOF # 检查磁盘空间 log "开始检查磁盘空间" df -h | grep /tidb>> $LOG_FILE
DISK_USAGE=$(df -h | grep /tidb | awk ‘{print $5}’ | sed ‘s/%//g’)
if [ $DISK_USAGE -gt 80 ]; then
log “警告: 磁盘使用率超过80%”
/tidb/scripts/alert.sh “WARNING” “磁盘使用率超过80%”
fi

# 检查连接数
log “开始检查连接数”
CONNECTIONS=$($MYSQL_BIN -h $HOST -P $PORT -u $USER -p$PASSWORD -e “SHOW STATUS LIKE ‘Threads_connected’;”
| grep Threads_connected | awk ‘{print $2}’)
if [ $CONNECTIONS -gt 5000 ]; then
log “警告: 连接数超过5000”
/tidb/scripts/alert.sh “WARNING” “连接数超过5000”
fi

# 检查慢查询
log “开始检查慢查询”
SLOW_QUERIES=$($MYSQL_BIN -h $HOST -P $PORT -u $USER -p$PASSWORD -e “SHOW STATUS LIKE ‘Slow_queries’;” |
grep Slow_queries | awk ‘{print $2}’)
if [ $SLOW_QUERIES -gt 100 ]; then
log “警告: 慢查询数超过100”
/tidb/scripts/alert.sh “WARNING” “慢查询数超过100”
fi
EOF

## 3. 测试告警
$ /tidb/scripts/alert.sh “TEST” “测试告警”

## 4. 配置告警阈值
$ cat > /tidb/scripts/alert_thresholds.conf << EOF # 告警阈值配置 # 磁盘使用率阈值 DISK_USAGE_THRESHOLD=80 # 连接数阈值 CONNECTIONS_THRESHOLD=5000 # 慢查询数阈值 SLOW_QUERIES_THRESHOLD=100 # QPS阈值 QPS_THRESHOLD=10000 EOF ## 5. 修改脚本使用配置文件 $ cat> /tidb/scripts/daily_check.sh << EOF #!/bin/bash # daily_check.sh # from:www.itpux.com.qq113257174.wx:itpux-com # web: http://www.fgedu.net.cn # 配置 TIUP_BIN="/tidb/app/tiup/bin/tiup" MYSQL_BIN="/tidb/app/mysql/bin/mysql" HOST="192.168.1.100" PORT="4000" USER="root" PASSWORD="password" DB="fgedudb" LOG_FILE="/tidb/logs/daily_check.log" ALERT_SCRIPT="/tidb/scripts/alert.sh" THRESHOLD_CONFIG="/tidb/scripts/alert_thresholds.conf" # 加载阈值配置 source $THRESHOLD_CONFIG # 日志函数 log() { echo "[$(date +'%Y-%m-%d %H:%M:%S')] $1">> $LOG_FILE
echo “[$(date +’%Y-%m-%d %H:%M:%S’)] $1”
}

# 检查磁盘空间
check_disk_space() {
log “开始检查磁盘空间”
df -h | grep /tidb >> $LOG_FILE
DISK_USAGE=$(df -h | grep /tidb | awk ‘{print $5}’ | sed ‘s/%//g’)
if [ $DISK_USAGE -gt $DISK_USAGE_THRESHOLD ]; then
log “警告: 磁盘使用率超过${DISK_USAGE_THRESHOLD}%”
$ALERT_SCRIPT “WARNING” “磁盘使用率超过${DISK_USAGE_THRESHOLD}%”
fi
}

# 检查连接数
check_connections() {
log “开始检查连接数”
CONNECTIONS=$($MYSQL_BIN -h $HOST -P $PORT -u $USER -p$PASSWORD -e “SHOW STATUS LIKE
‘Threads_connected’;” | grep Threads_connected | awk ‘{print $2}’)
if [ $CONNECTIONS -gt $CONNECTIONS_THRESHOLD ]; then
log “警告: 连接数超过${CONNECTIONS_THRESHOLD}”
$ALERT_SCRIPT “WARNING” “连接数超过${CONNECTIONS_THRESHOLD}”
fi
}

# 检查慢查询
check_slow_queries() {
log “开始检查慢查询”
SLOW_QUERIES=$($MYSQL_BIN -h $HOST -P $PORT -u $USER -p$PASSWORD -e “SHOW STATUS LIKE ‘Slow_queries’;”
| grep Slow_queries | awk ‘{print $2}’)
if [ $SLOW_QUERIES -gt $SLOW_QUERIES_THRESHOLD ]; then
log “警告: 慢查询数超过${SLOW_QUERIES_THRESHOLD}”
$ALERT_SCRIPT “WARNING” “慢查询数超过${SLOW_QUERIES_THRESHOLD}”
fi
}

# 主函数
main() {
log “开始日常巡检”

# 检查集群状态
log “开始检查集群状态”
$TIUP_BIN cluster display fgedu-tidb-cluster >> $LOG_FILE

# 检查TiDB状态
log “开始检查TiDB状态”
$MYSQL_BIN -h $HOST -P $PORT -u $USER -p$PASSWORD -e “SHOW STATUS LIKE ‘Queries’;” >> $LOG_FILE
$MYSQL_BIN -h $HOST -P $PORT -u $USER -p$PASSWORD -e “SHOW STATUS LIKE ‘Slow_queries’;” >> $LOG_FILE
$MYSQL_BIN -h $HOST -P $PORT -u $USER -p$PASSWORD -e “SHOW STATUS LIKE ‘Threads_connected’;” >>
$LOG_FILE

# 检查资源使用
log “开始检查资源使用”
top -b -n 1 | head -20 >> $LOG_FILE
df -h >> $LOG_FILE
free -h >> $LOG_FILE

# 检查磁盘空间
check_disk_space

# 检查连接数
check_connections

# 检查慢查询
check_slow_queries

# 检查网络状态
log “开始检查网络状态”
ping -c 4 $HOST >> $LOG_FILE
netstat -tuln | grep 4000 >> $LOG_FILE

log “日常巡检完成”
}

# 执行主函数
main
EOF

## 案例1：日常巡检脚本

### 步骤1：编写脚本
$ cat > daily_check.sh << EOF #!/bin/bash # daily_check.sh # from:www.itpux.com.qq113257174.wx:itpux-com # web: http://www.fgedu.net.cn # 配置 TIUP_BIN="/tidb/app/tiup/bin/tiup" MYSQL_BIN="/tidb/app/mysql/bin/mysql" HOST="192.168.1.100" PORT="4000" USER="root" PASSWORD="password" DB="fgedudb" LOG_FILE="/tidb/logs/daily_check.log" ALERT_SCRIPT="/tidb/scripts/alert.sh" # 日志函数 log() { echo "[$(date +'%Y-%m-%d %H:%M:%S')] $1">> $LOG_FILE
echo “[$(date +’%Y-%m-%d %H:%M:%S’)] $1”
}

# 检查集群状态
check_cluster_status() {
log “检查集群状态”
$TIUP_BIN cluster display fgedu-tidb-cluster >> $LOG_FILE
if [ $? -ne 0 ]; then
log “错误: 集群状态检查失败”
$ALERT_SCRIPT “ERROR” “集群状态检查失败”
fi
}

# 检查TiDB状态
check_tidb_status() {
log “检查TiDB状态”
$MYSQL_BIN -h $HOST -P $PORT -u $USER -p$PASSWORD -e “SHOW STATUS LIKE ‘Queries’;” >> $LOG_FILE
$MYSQL_BIN -h $HOST -P $PORT -u $USER -p$PASSWORD -e “SHOW STATUS LIKE ‘Slow_queries’;” >> $LOG_FILE
$MYSQL_BIN -h $HOST -P $PORT -u $USER -p$PASSWORD -e “SHOW STATUS LIKE ‘Threads_connected’;” >> $LOG_FILE
}

# 检查资源使用
check_resource_usage() {
log “检查资源使用”
top -b -n 1 | head -20 >> $LOG_FILE
df -h >> $LOG_FILE
free -h >> $LOG_FILE
}

# 检查磁盘空间
check_disk_space() {
log “检查磁盘空间”
df -h | grep /tidb >> $LOG_FILE
DISK_USAGE=$(df -h | grep /tidb | awk ‘{print $5}’ | sed ‘s/%//g’)
if [ $DISK_USAGE -gt 80 ]; then
log “警告: 磁盘使用率超过80%”
$ALERT_SCRIPT “WARNING” “磁盘使用率超过80%”
fi
}

# 检查网络状态
check_network_status() {
log “检查网络状态”
ping -c 4 $HOST >> $LOG_FILE
netstat -tuln | grep 4000 >> $LOG_FILE
}

# 主函数
main() {
log “开始日常巡检”

# 检查集群状态
check_cluster_status

# 检查TiDB状态
check_tidb_status

# 检查资源使用
check_resource_usage

# 检查磁盘空间
check_disk_space

# 检查网络状态
check_network_status

log “日常巡检完成”
}

# 执行主函数
main
EOF

### 步骤2：测试脚本
$ chmod +x daily_check.sh
$ ./daily_check.sh

### 步骤3：配置定时任务
$ crontab -e
# 添加以下内容
0 8 * * * /tidb/scripts/daily_check.sh

## 案例2：高级巡检脚本

### 步骤1：编写脚本
$ cat > advanced_check.py << EOF #!/usr/bin/env python3 # advanced_check.py # from:www.itpux.com.qq113257174.wx:itpux-com # web: http://www.fgedu.net.cn import os import sys import subprocess import datetime import smtplib from email.mime.text import MIMEText from email.mime.multipart import MIMEMultipart # 配置 TIUP_BIN="/tidb/app/tiup/bin/tiup" MYSQL_BIN="/tidb/app/mysql/bin/mysql" HOST="192.168.1.100" PORT="4000" USER="root" PASSWORD="password" DB="fgedudb" LOG_FILE="/tidb/logs/advanced_check.log" REPORT_FILE="/tidb/reports/advanced_report_{}.txt" .format(datetime.datetime.now().strftime("%Y%m%d")) # 日志函数 def log(message): timestamp=datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S") log_message="[{}] {}" .format(timestamp, message) with open(LOG_FILE, "a" ) as f: f.write(log_message + "\n" ) print(log_message) # 执行命令 def execute_command(command): try: result=subprocess.check_output(command, shell=True, stderr=subprocess.STDOUT, universal_newlines=True) return result except subprocess.CalledProcessError as e: log("命令执行失败: {}\n输出: {}".format(command, e.output)) return "" # 检查集群状态 def check_cluster_status(): log("检查集群状态") result=execute_command("{} cluster display fgedu-tidb-cluster".format(TIUP_BIN)) return result # 检查TiDB状态 def check_tidb_status(): log("检查TiDB状态") queries=execute_command("{} -h {} -P {} -u {} -p{} -e 'SHOW STATUS LIKE "Queries";'" .format(MYSQL_BIN, HOST, PORT, USER, PASSWORD)) slow_queries = execute_command(" {} -h {} -P {} -u {} -p{} -e 'SHOW STATUS LIKE "Slow_queries";'" .format(MYSQL_BIN, HOST, PORT, USER, PASSWORD)) connections = execute_command(" {} -h {} -P {} -u {} -p{} -e 'SHOW STATUS LIKE "Threads_connected";'" .format(MYSQL_BIN, HOST, PORT, USER, PASSWORD)) return queries, slow_queries, connections # 生成报告 def generate_report(cluster_status, tidb_status): log(" 生成巡检报告") with open(REPORT_FILE, "w" ) as f: f.write("# TiDB高级巡检报告\n") f.write("生成时间: {}\n\n".format(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))) f.write("## 1. 集群状态\n") f.write(cluster_status) f.write("\n") f.write("## 2. TiDB状态\n") f.write(tidb_status[0]) f.write(tidb_status[1]) f.write(tidb_status[2]) return REPORT_FILE # 主函数 def main(): log("开始高级巡检") cluster_status=check_cluster_status() tidb_status=check_tidb_status() report_file=generate_report(cluster_status, tidb_status) log("高级巡检完成，报告已生成: {}".format(report_file)) if __name__=="__main__" : main() EOF ### 步骤2：测试脚本 $ chmod +x advanced_check.py $ python3 advanced_check.py ### 步骤3：配置定时任务 $ crontab -e # 添加以下内容 0 10 * * 0 /tidb/scripts/advanced_check.py

## 案例1：脚本部署

### 步骤1：创建目录结构
$ mkdir -p /tidb/scripts
$ mkdir -p /tidb/logs
$ mkdir -p /tidb/reports

### 步骤2：复制脚本
$ cp daily_check.sh /tidb/scripts/
$ cp weekly_check.sh /tidb/scripts/
$ cp basic_check.sh /tidb/scripts/
$ cp advanced_check.py /tidb/scripts/
$ cp alert.sh /tidb/scripts/

### 步骤3：设置权限
$ chmod +x /tidb/scripts/*.sh
$ chmod +x /tidb/scripts/*.py

### 步骤4：配置环境变量
$ cat > /etc/profile.d/tidb_scripts.sh << EOF export TIUP_BIN="/tidb/app/tiup/bin/tiup" export MYSQL_BIN="/tidb/app/mysql/bin/mysql" export TIDB_HOST="192.168.1.100" export TIDB_PORT="4000" export TIDB_USER="root" export TIDB_PASSWORD="password" export TIDB_DB="fgedudb" EOF $ source /etc/profile.d/tidb_scripts.sh ### 步骤5：测试脚本 $ /tidb/scripts/daily_check.sh $ /tidb/scripts/weekly_check.sh $ /tidb/scripts/basic_check.sh $ python3 /tidb/scripts/advanced_check.py ### 步骤6：配置定时任务 $ crontab -e # 添加以下内容 0 8 * * * /tidb/scripts/daily_check.sh 0 9 * * 1 /tidb/scripts/weekly_check.sh 0 12 * * * /tidb/scripts/basic_check.sh 0 10 * * 0 /tidb/scripts/advanced_check.py ## 案例2：监控集成 ### 步骤1：配置Prometheus采集脚本执行状态 $ cat> script_status.sh << EOF #!/bin/bash # script_status.sh # from:www.itpux.com.qq113257174.wx:itpux-com # web: http://www.fgedu.net.cn # 配置 LOG_DIR="/tidb/logs" METRICS_FILE="/tidb/metrics/script_status.prom" # 创建指标文件目录 mkdir -p /tidb/metrics # 检查脚本执行状态 check_script_status() { SCRIPT_NAME=$1 LOG_FILE="${LOG_DIR}/${SCRIPT_NAME}.log" if [ -f $LOG_FILE ]; then LAST_EXECUTION=$(tail -1 $LOG_FILE | grep "完成" ) if [ -n "$LAST_EXECUTION" ]; then STATUS=1 else STATUS=0 fi else STATUS=0 fi echo "# HELP tidb_script_status Script execution status" echo "# TYPE tidb_script_status gauge" echo "tidb_script_status{script=\" $SCRIPT_NAME\"} $STATUS" } # 生成指标 cat> $METRICS_FILE << EOF $(check_script_status "daily_check" ) $(check_script_status "weekly_check" ) $(check_script_status "basic_check" ) $(check_script_status "advanced_check" ) EOF EOF ### 步骤2：配置Prometheus采集 $ cat>> prometheus.yml << EOF - job_name: 'script_status' static_configs: - targets: ['localhost:9101'] metrics_path: '/metrics' EOF ### 步骤3：启动指标服务 $ nohup bash -c 'while true; do ./script_status.sh; sleep 60; done' & $ python3 -m http.server 9101 --directory /tidb/metrics ## 案例3：告警集成 ### 步骤1：配置告警脚本 $ cat> alert.sh << EOF #!/bin/bash # alert.sh # from:www.itpux.com.qq113257174.wx:itpux-com # web: http://www.fgedu.net.cn # 配置 ALERT_LEVEL="$1" ALERT_MESSAGE="$2" LOG_FILE="/tidb/logs/alert.log" # 日志函数 log() { echo "[$(date +'%Y-%m-%d %H:%M:%S')] $1">> $LOG_FILE
echo “[$(date +’%Y-%m-%d %H:%M:%S’)] $1″
}

# 发送邮件
send_email() {
# 邮件配置
SMTP_SERVER=”smtp.example.com”
SMTP_PORT=”587″
SMTP_USER=”admin@example.com”
SMTP_PASSWORD=”password”
SENDER=”admin@example.com”
RECEIVERS=”admin@example.com”

# 创建邮件内容
SUBJECT=”[TiDB告警] $ALERT_LEVEL: $ALERT_MESSAGE”
BODY=”告警级别: $ALERT_LEVEL\n告警信息: $ALERT_MESSAGE\n告警时间: $(date +’%Y-%m-%d %H:%M:%S’)”

# 发送邮件
echo “$BODY” | mail -s “$SUBJECT” -r “$SENDER” $RECEIVERS
}

# 主函数
main() {
log “告警: $ALERT_LEVEL – $ALERT_MESSAGE”
send_email
}

# 执行主函数
main
EOF

### 步骤2：修改巡检脚本添加告警
$ cat >> /tidb/scripts/daily_check.sh << EOF # 检查磁盘空间 DISK_USAGE=$(df -h | grep /tidb | awk '{print $5}' | sed 's/%//g' ) if [ $DISK_USAGE -gt 80 ]; then /tidb/scripts/alert.sh "WARNING" "磁盘使用率超过80%" fi # 检查连接数 CONNECTIONS=$($MYSQL_BIN -h $HOST -P $PORT -u $USER -p$PASSWORD -e "SHOW STATUS LIKE 'Threads_connected';" | grep Threads_connected | awk '{print $2}' ) if [ $CONNECTIONS -gt 5000 ]; then /tidb/scripts/alert.sh "WARNING" "连接数超过5000" fi EOF ### 步骤3：测试告警 $ /tidb/scripts/alert.sh "TEST" "测试告警"

## 案例1：邮件告警

### 步骤1：配置邮件告警
$ cat > email_alert.sh << EOF #!/bin/bash # email_alert.sh # from:www.itpux.com.qq113257174.wx:itpux-com # web: http://www.fgedu.net.cn # 配置 ALERT_LEVEL="$1" ALERT_MESSAGE="$2" LOG_FILE="/tidb/logs/alert.log" # 日志函数 log() { echo "[$(date +'%Y-%m-%d %H:%M:%S')] $1">> $LOG_FILE
echo “[$(date +’%Y-%m-%d %H:%M:%S’)] $1″
}

# 发送邮件
send_email() {
# 邮件配置
SMTP_SERVER=”smtp.example.com”
SMTP_PORT=”587″
SMTP_USER=”admin@example.com”
SMTP_PASSWORD=”password”
SENDER=”admin@example.com”
RECEIVERS=”admin@example.com”

# 创建邮件内容
SUBJECT=”[TiDB告警] $ALERT_LEVEL: $ALERT_MESSAGE”
BODY=”告警级别: $ALERT_LEVEL\n告警信息: $ALERT_MESSAGE\n告警时间: $(date +’%Y-%m-%d %H:%M:%S’)”

# 发送邮件
echo “$BODY” | mail -s “$SUBJECT” -r “$SENDER” $RECEIVERS
}

# 主函数
main() {
log “告警: $ALERT_LEVEL – $ALERT_MESSAGE”
send_email
}

# 执行主函数
main
EOF

### 步骤2：配置巡检脚本调用告警
$ cat >> /tidb/scripts/daily_check.sh << EOF # 检查磁盘空间 DISK_USAGE=$(df -h | grep /tidb | awk '{print $5}' | sed 's/%//g' ) if [ $DISK_USAGE -gt 80 ]; then /tidb/scripts/email_alert.sh "WARNING" "磁盘使用率超过80%" fi EOF ### 步骤3：测试邮件告警 $ /tidb/scripts/email_alert.sh "TEST" "测试邮件告警" ## 案例2：微信告警 ### 步骤1：配置微信告警 $ cat>
wechat_alert.sh << EOF #!/bin/bash # wechat_alert.sh # from:www.itpux.com.qq113257174.wx:itpux-com # web: http://www.fgedu.net.cn # 配置 ALERT_LEVEL="$1" ALERT_MESSAGE="$2" LOG_FILE="/tidb/logs/alert.log" # 日志函数 log() { echo "[$(date +'%Y-%m-%d %H:%M:%S')] $1">> $LOG_FILE
echo “[$(date +’%Y-%m-%d %H:%M:%S’)] $1″
}

# 发送微信通知
send_wechat() {
# 企业微信配置
CORP_ID=”your_corp_id”
AGENT_ID=”your_agent_id”
API_SECRET=”your_api_secret”
TO_USER=”@all”

# 获取access_token
ACCESS_TOKEN=$(curl -s
“https://qyapi.weixin.qq.com/cgi-bin/gettoken?corpid=$CORP_ID&corpsecret=$API_SECRET”
| jq -r ‘.access_token’)

# 创建消息内容
MESSAGE=”告警级别: $ALERT_LEVEL\n告警信息: $ALERT_MESSAGE\n告警时间: $(date +’%Y-%m-%d
%H:%M:%S’)”

# 发送微信通知
curl -s -X POST
“https://qyapi.weixin.qq.com/cgi-bin/message/send?access_token=$ACCESS_TOKEN” \
-H “Content-Type: application/json” \
-d “{
\”touser\”: \”$TO_USER\”,
\”msgtype\”: \”text\”,
\”agentid\”: $AGENT_ID,
\”text\”: {
\”content\”: \”$MESSAGE\”
},
\”safe\”: 0
}”
}

# 主函数
main() {
log “告警: $ALERT_LEVEL – $ALERT_MESSAGE”
send_wechat
}

# 执行主函数
main
EOF

### 步骤2：配置巡检脚本调用微信告警
$ cat >> /tidb/scripts/daily_check.sh << EOF # 检查连接数 CONNECTIONS=$($MYSQL_BIN -h $HOST -P $PORT -u $USER -p$PASSWORD -e "SHOW STATUS LIKE 'Threads_connected';" |