anxin-ruoyi/docker/scripts/diagnose-mysql-connection.sh

#!/bin/bash
# MySQL连接诊断脚本
# 用于诊断和修复MySQL外部连接问题

set -e

# 颜色定义
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
NC='\033[0m'

log_info() {
    echo -e "${BLUE}[INFO]${NC} $1"
}

log_success() {
    echo -e "${GREEN}[SUCCESS]${NC} $1"
}

log_warn() {
    echo -e "${YELLOW}[WARN]${NC} $1"
}

log_error() {
    echo -e "${RED}[ERROR]${NC} $1"
}

# 检查是否在正确的目录
check_directory() {
    if [[ ! -f "deploy.sh" ]]; then
        log_error "请在部署目录中运行此脚本 (应包含 deploy.sh)"
        exit 1
    fi
}

# 检查环境配置
check_environment_config() {
    log_info "检查环境配置..."
    
    if [[ -f "environments/.env.production" ]]; then
        source environments/.env.production
        
        log_info "数据库配置:"
        echo "  DB_HOST: $DB_HOST"
        echo "  DB_PORT: $DB_PORT"
        echo "  DB_NAME: $DB_NAME"
        echo "  DB_USER: $DB_USER"
        echo "  DB_PASSWORD: [HIDDEN]"
    else
        log_error "未找到生产环境配置文件"
        return 1
    fi
}

# 检查容器状态
check_container_status() {
    log_info "检查MySQL容器状态..."
    
    if docker ps --filter "name=anxin-mysql-prod" --format "table {{.Names}}\t{{.Status}}\t{{.Ports}}" | grep anxin-mysql-prod; then
        log_success "✓ MySQL容器正在运行"
        
        # 显示端口映射
        local ports=$(docker port anxin-mysql-prod 2>/dev/null || echo "无端口映射")
        log_info "端口映射: $ports"
    else
        log_error "✗ MySQL容器未运行"
        
        # 检查是否存在但已停止
        if docker ps -a --filter "name=anxin-mysql-prod" --format "{{.Names}}" | grep -q anxin-mysql-prod; then
            log_warn "MySQL容器存在但已停止"
            docker ps -a --filter "name=anxin-mysql-prod" --format "table {{.Names}}\t{{.Status}}"
        fi
        return 1
    fi
}

# 检查网络连接
check_network_connectivity() {
    log_info "检查网络连接..."
    
    if [[ -f "environments/.env.production" ]]; then
        source environments/.env.production
        
        # 检查端口是否开放
        log_info "检查端口 $DB_PORT 是否开放..."
        if netstat -tlnp 2>/dev/null | grep ":$DB_PORT " || ss -tlnp 2>/dev/null | grep ":$DB_PORT "; then
            log_success "✓ 端口 $DB_PORT 正在监听"
        else
            log_error "✗ 端口 $DB_PORT 未开放"
        fi
        
        # 测试本地连接
        log_info "测试本地连接..."
        if nc -z localhost "$DB_PORT" 2>/dev/null; then
            log_success "✓ 本地连接测试成功"
        else
            log_error "✗ 本地连接测试失败"
        fi
        
        # 测试容器内连接
        if docker ps --filter "name=anxin-mysql-prod" --format "{{.Names}}" | grep -q anxin-mysql-prod; then
            log_info "测试容器内连接..."
            if docker exec anxin-mysql-prod mysqladmin ping -h localhost -u root -p"$MYSQL_ROOT_PASSWORD" 2>/dev/null; then
                log_success "✓ 容器内连接测试成功"
            else
                log_error "✗ 容器内连接测试失败"
            fi
        fi
    fi
}

# 检查防火墙设置
check_firewall() {
    log_info "检查防火墙设置..."
    
    if [[ -f "environments/.env.production" ]]; then
        source environments/.env.production
        
        # 检查iptables规则
        if command -v iptables >/dev/null 2>&1; then
            log_info "检查iptables规则..."
            if iptables -L INPUT -n | grep -q "$DB_PORT"; then
                log_info "找到端口 $DB_PORT 的iptables规则"
                iptables -L INPUT -n | grep "$DB_PORT"
            else
                log_warn "未找到端口 $DB_PORT 的iptables规则"
            fi
        fi
        
        # 检查firewalld
        if command -v firewall-cmd >/dev/null 2>&1 && systemctl is-active firewalld >/dev/null 2>&1; then
            log_info "检查firewalld规则..."
            if firewall-cmd --list-ports | grep -q "$DB_PORT"; then
                log_success "✓ 端口 $DB_PORT 已在firewalld中开放"
            else
                log_warn "端口 $DB_PORT 未在firewalld中开放"
            fi
        fi
        
        # 检查ufw
        if command -v ufw >/dev/null 2>&1; then
            log_info "检查ufw规则..."
            if ufw status | grep -q "$DB_PORT"; then
                log_success "✓ 端口 $DB_PORT 已在ufw中开放"
            else
                log_warn "端口 $DB_PORT 未在ufw中开放"
            fi
        fi
    fi
}

# 检查MySQL配置
check_mysql_config() {
    log_info "检查MySQL配置..."
    
    if docker ps --filter "name=anxin-mysql-prod" --format "{{.Names}}" | grep -q anxin-mysql-prod; then
        # 检查bind-address配置
        log_info "检查bind-address配置..."
        if docker exec anxin-mysql-prod mysql -u root -p"$MYSQL_ROOT_PASSWORD" -e "SHOW VARIABLES LIKE 'bind_address';" 2>/dev/null; then
            log_success "✓ 获取bind_address配置成功"
        else
            log_error "✗ 无法获取bind_address配置"
        fi
        
        # 检查用户权限
        log_info "检查用户权限..."
        if [[ -f "environments/.env.production" ]]; then
            source environments/.env.production
            
            if docker exec anxin-mysql-prod mysql -u root -p"$MYSQL_ROOT_PASSWORD" -e "SELECT User, Host FROM mysql.user WHERE User='$DB_USER';" 2>/dev/null; then
                log_success "✓ 获取用户权限信息成功"
            else
                log_error "✗ 无法获取用户权限信息"
            fi
        fi
    fi
}

# 测试外部连接
test_external_connection() {
    log_info "测试外部连接..."
    
    if [[ -f "environments/.env.production" ]]; then
        source environments/.env.production
        
        # 获取服务器外部IP
        local external_ip=$(curl -s ifconfig.me 2>/dev/null || curl -s ipinfo.io/ip 2>/dev/null || echo "unknown")
        log_info "服务器外部IP: $external_ip"
        
        # 测试从外部连接
        log_info "尝试从外部连接MySQL..."
        if timeout 10 mysql -h "$external_ip" -P "$DB_PORT" -u "$DB_USER" -p"$DB_PASSWORD" -e "SELECT 1;" 2>/dev/null; then
            log_success "✓ 外部连接测试成功"
        else
            log_error "✗ 外部连接测试失败"
            log_info "请检查:"
            echo "  1. 防火墙是否开放端口 $DB_PORT"
            echo "  2. MySQL用户是否允许远程连接"
            echo "  3. 网络安全组是否允许该端口"
        fi
    fi
}

# 修复常见问题
fix_common_issues() {
    log_info "修复常见问题..."
    
    if [[ -f "environments/.env.production" ]]; then
        source environments/.env.production
        
        # 1. 重启MySQL容器
        log_info "重启MySQL容器..."
        ./deploy.sh restart mysql -e production
        sleep 10
        
        # 2. 检查并修复用户权限
        if docker ps --filter "name=anxin-mysql-prod" --format "{{.Names}}" | grep -q anxin-mysql-prod; then
            log_info "修复用户权限..."
            
            # 创建用户并授权（如果不存在）
            docker exec anxin-mysql-prod mysql -u root -p"$MYSQL_ROOT_PASSWORD" -e "
                CREATE USER IF NOT EXISTS '$DB_USER'@'%' IDENTIFIED BY '$DB_PASSWORD';
                GRANT ALL PRIVILEGES ON $DB_NAME.* TO '$DB_USER'@'%';
                FLUSH PRIVILEGES;
            " 2>/dev/null && log_success "✓ 用户权限修复完成" || log_error "✗ 用户权限修复失败"
        fi
        
        # 3. 开放防火墙端口（如果需要）
        if command -v firewall-cmd >/dev/null 2>&1 && systemctl is-active firewalld >/dev/null 2>&1; then
            log_info "开放firewalld端口..."
            firewall-cmd --permanent --add-port="$DB_PORT/tcp" 2>/dev/null && \
            firewall-cmd --reload 2>/dev/null && \
            log_success "✓ firewalld端口开放完成" || log_warn "firewalld端口开放失败"
        fi
        
        if command -v ufw >/dev/null 2>&1; then
            log_info "开放ufw端口..."
            ufw allow "$DB_PORT/tcp" 2>/dev/null && \
            log_success "✓ ufw端口开放完成" || log_warn "ufw端口开放失败"
        fi
    fi
}

# 显示连接信息
show_connection_info() {
    log_info "MySQL连接信息:"
    echo "========================================"
    
    if [[ -f "environments/.env.production" ]]; then
        source environments/.env.production
        
        local external_ip=$(curl -s ifconfig.me 2>/dev/null || curl -s ipinfo.io/ip 2>/dev/null || echo "your-server-ip")
        
        echo "外部连接信息:"
        echo "  主机: $external_ip"
        echo "  端口: $DB_PORT"
        echo "  数据库: $DB_NAME"
        echo "  用户名: $DB_USER"
        echo "  密码: $DB_PASSWORD"
        echo ""
        echo "连接命令示例:"
        echo "  mysql -h $external_ip -P $DB_PORT -u $DB_USER -p$DB_PASSWORD $DB_NAME"
        echo ""
        echo "JDBC连接字符串:"
        echo "  jdbc:mysql://$external_ip:$DB_PORT/$DB_NAME?useUnicode=true&characterEncoding=utf8&useSSL=false"
    fi
    
    echo "========================================"
}

# 主函数
main() {
    local action=${1:-"diagnose"}
    
    log_info "MySQL连接诊断工具"
    echo "========================================"
    
    check_directory
    
    case $action in
        "diagnose")
            check_environment_config
            echo ""
            check_container_status
            echo ""
            check_network_connectivity
            echo ""
            check_firewall
            echo ""
            check_mysql_config
            echo ""
            test_external_connection
            echo ""
            show_connection_info
            ;;
        "fix")
            fix_common_issues
            echo ""
            main "diagnose"
            ;;
        "info")
            show_connection_info
            ;;
        *)
            echo "用法: $0 [diagnose|fix|info]"
            echo "  diagnose - 诊断连接问题（默认）"
            echo "  fix      - 修复常见问题"
            echo "  info     - 显示连接信息"
            exit 1
            ;;
    esac
    
    echo "========================================"
    log_success "操作完成!"
}

# 如果脚本被直接执行
if [[ "${BASH_SOURCE[0]}" == "${0}" ]]; then
    main "$@"
fi