#!/bin/bash
#***************************************************************#
# ScriptName: grade03.sh
# Author: xhy
# Create Date: 2025-07-01 21:53
# Modify Author: xhy
# Modify Date: 2025-07-14 22:13
# Version: v1
#***************************************************************#

# 初始化总分
total_score=0

mount -a

# 1. 检查MySQL版本 (3分)
mysql_version=$(mysql -V | awk '{print $3}' | cut -d '.' -f1)
if [[ $mysql_version -ge 8 ]]; then
    echo "a: 正确 (3分)"
    total_score=$((total_score + 3))
else
    echo "a: MySQL版本低于8.0 (当前版本: $mysql_version)"
fi

# 2. 检查防火墙规则 (5分)
if firewall-cmd --list-services | grep -qw mysql; then
    echo "b: 正确 (4分)"
    total_score=$((total_score + 4))
else
    echo "b: 防火墙未放通MySQL!"
fi

# 3. 检查root密码 (5分)
if mysql -u root -p'Xyz1b2c3%y' -e "SELECT 1;" &>/dev/null; then
    echo "c: 正确 (4分)"
    total_score=$((total_score + 4))
else
    echo "c: root密码设置不正确!"
fi

# 4. 检查NFS挂载 (5分)
if findmnt /var/lib/mysql | grep -q "172.24.8.31:/datamysql"; then
    echo "d: 正确 (5分)"
    total_score=$((total_score + 5))
else
    echo "d: NFS挂载配置错误!"
fi

# 5. 检查binlog忽略设置 (5分)
if grep -q "binlog-ignore-db=test" /etc/my.cnf && 
   grep -q "binlog-ignore-db=information_schema" /etc/my.cnf; then
    echo "e: 正确 (5分)"
    total_score=$((total_score + 5))
else
    echo "e: binlog忽略设置错误!"
fi

# 6. 检查复制账号和主从配置 (8分)
repl_user=$(mysql -u root -p'Xyz1b2c3%y' -e "SELECT user FROM mysql.user WHERE user='repl_user' AND host='172.24.8.%';" 2>/dev/null)
repl_priv=$(mysql -u root -p'Xyz1b2c3%y' -e "SHOW GRANTS FOR 'repl_user'@'172.24.8.%';" 2>/dev/null | grep "REPLICATION SLAVE")

if [[ -n $repl_user && -n $repl_priv ]]; then
    echo "f: 正确 (5分)"
    total_score=$((total_score + 5))
else
    echo "f: 复制账号配置错误!"
fi

# 检查从库状态（需要手动在slave01上运行）
#echo "请手动在slave01服务器上执行以下命令检查主从状态："
#echo "mysql -u root -p'Xyz1b2c3%y' -e \"SHOW SLAVE STATUS\G\" | grep -E 'Slave_IO_Running|Slave_SQL_Running'"
#echo "如果Slave_IO_Running和Slave_SQL_Running都是Yes，请给此项加4分"

# 7. 检查主从同步状态 (8分)
slave_status=$(ssh -T -o LogLevel=ERROR -o StrictHostKeyChecking=no root@172.24.8.13 \
"mysql -u root -p'Xyz1b2c3%y' -e \"SHOW SLAVE STATUS\G\" | \
grep -E 'Slave_IO_Running|Slave_SQL_Running' | grep -v waiting" 2>/dev/null)

if [[ "$slave_status" == *"Slave_IO_Running: Yes"* ]] && \
   [[ "$slave_status" == *"Slave_SQL_Running: Yes"* ]]; then
    echo "g: 正确 (8分)"
    total_score=$((total_score + 8))
else
    echo "g: 主从同步异常!"
    [[ -n "$slave_status" ]] && echo "当前状态: $slave_status" || echo "无法获取状态信息"
fi

# 8. 检查admin用户 (5分)
admin_user=$(mysql -u root -p'Xyz1b2c3%y' -e "SELECT user FROM mysql.user WHERE user='admin' AND host='172.24.8.%';" 2>/dev/null)
admin_priv=$(mysql -u root -p'Xyz1b2c3%y' -e "SHOW GRANTS FOR 'admin'@'172.24.8.%';" 2>/dev/null | grep -E "SELECT|INSERT|UPDATE|DELETE|CREATE|DROP|RELOAD|SHUTDOWN|PROCESS")

if [[ -n $admin_user && -n $admin_priv ]]; then
    echo "h: 正确 (4分)"
    total_score=$((total_score + 4))
else
    echo "h: admin用户配置错误!"
fi

# 9. 检查class数据库和表 (5分)
table_check=$(mysql -u root -p'Xyz1b2c3%y' -e "SELECT * FROM class.classroom;" 2>/dev/null)
if echo "$table_check" | grep -q "zhangsan" && echo "$table_check" | grep -q "lisi"; then
    echo "i: 正确 (3分)"
    total_score=$((total_score + 3))
else
    echo "i: class数据库和表错误!"
fi

# 输出总分
echo -e "\n第三题得分/总分: $total_score/41分\n\n"
