mybatis的ip配置错误

MyBatis IP 配置错误分析
一、典型错误堆栈
1. 未知主机错误
java
// 主机名解析失败
org.springframework.jdbc.CannotGetJdbcConnectionException: 
Failed to obtain JDBC Connection; nested exception is java.sql.SQLException: 
Unknown host 'wrong_host'

// 完整错误堆栈
Caused by: java.sql.SQLException: Unknown host 'wrong_host'
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:828)
    at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:448)
    at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:241)
    at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:198)
    at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:138)
    at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:364)
2. 连接超时错误
java
// IP地址无法访问
com.mysql.cj.jdbc.exceptions.CommunicationsException: 
Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. 
The driver has not received any packets from the server.

Caused by: java.net.UnknownHostException: 192.168.99.100  // 错误的IP
    at java.net.InetAddress.getAllByName0(InetAddress.java:1281)
    at java.net.InetAddress.getAllByName(InetAddress.java:1193)
    at java.net.InetAddress.getAllByName(InetAddress.java:1127)
    at com.mysql.cj.protocol.StandardSocketFactory.connect(StandardSocketFactory.java:132)

// 或者网络不可达
Caused by: java.net.NoRouteToHostException: No route to host (Host unreachable)
二、错误配置示例
1. application.yml IP错误配置
yaml
# 错误的主机名或IP配置
spring:
  datasource:
    url: jdbc:mysql://wrong_host:3306/my_database  # 错误的主机名
    username: root
    password: 123456
    driver-class-name: com.mysql.cj.jdbc.Driver

# 或者错误的IP地址
spring:
  datasource:
    url: jdbc:mysql://192.168.99.100:3306/my_database  # 错误的IP
    username: root
    password: 123456

# 或者使用本地特殊IP
spring:
  datasource:
    url: jdbc:mysql://127.0.0.2:3306/my_database  # 错误的回环地址
2. mybatis-config.xml IP错误
xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" 
                          value="jdbc:mysql://wrong_ip:3306/my_database"/>  <!-- 错误IP -->
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
</configuration>
三、不同环境的IP配置
1. 常见环境IP配置
环境	正确配置示例	错误配置示例
本地开发	localhost 或 127.0.0.1	local_host, 127.0.0.2
Docker环境	host.docker.internal	docker, localhost
测试环境	test-db.company.com	test_db, test-db
生产环境	prod-db.cluster.com	prod_db, 192.168.1.100
2. 多环境IP配置示例
yaml
# 多环境配置
---
spring:
  profiles: dev
  datasource:
    url: jdbc:mysql://localhost:3306/dev_db
    username: dev_user
    password: dev_pass

---
spring:
  profiles: test
  datasource:
    url: jdbc:mysql://test-db-server:3306/test_db
    username: test_user
    password: test_pass

---
spring:
  profiles: prod
  datasource:
    url: jdbc:mysql://prod-db-cluster:3306/prod_db
    username: prod_user
    password: prod_pass
四、排查和诊断步骤
1. 网络连通性检查
bash
# 1. 检查主机名解析
nslookup wrong_host
ping wrong_host
ping 192.168.99.100

# 2. 检查网络路由
traceroute 192.168.99.100
tracepath 192.168.99.100

# 3. 检查端口连通性
telnet wrong_host 3306
telnet 192.168.99.100 3306

# 4. 查看本地hosts文件
cat /etc/hosts

# 5. 检查DNS配置
cat /etc/resolv.conf
2. Java 诊断代码
java
@Component
public class HostConnectionValidator {
    
    private static final Logger logger = LoggerFactory.getLogger(HostConnectionValidator.class);
    
    @Value("${spring.datasource.url}")
    private String dbUrl;
    
    @PostConstruct
    public void validateDatabaseHost() {
        try {
            String host = extractHost(dbUrl);
            int port = extractPort(dbUrl);
            
            logger.info("检查数据库主机连接: {}:{}", host, port);
            
            // 1. 检查主机名解析
            testHostResolution(host);
            
            // 2. 检查端口连通性
            testPortConnectivity(host, port);
            
            // 3. 测试数据库连接
            testDatabaseConnection();
            
            logger.info("✅ 数据库主机连接正常");
            
        } catch (Exception e) {
            logger.error("❌ 数据库主机连接失败: {}", e.getMessage());
            suggestSolutions();
            throw new RuntimeException("数据库配置验证失败", e);
        }
    }
    
    private String extractHost(String url) {
        Pattern pattern = Pattern.compile("jdbc:mysql://([^:/]+)");
        Matcher matcher = pattern.matcher(url);
        return matcher.find() ? matcher.group(1) : "localhost";
    }
    
    private int extractPort(String url) {
        Pattern pattern = Pattern.compile("jdbc:mysql://[^:]+:(\\d+)");
        Matcher matcher = pattern.matcher(url);
        return matcher.find() ? Integer.parseInt(matcher.group(1)) : 3306;
    }
    
    private void testHostResolution(String host) throws UnknownHostException {
        try {
            InetAddress[] addresses = InetAddress.getAllByName(host);
            logger.info("主机 {} 解析到以下IP:", host);
            for (InetAddress addr : addresses) {
                logger.info("  - {} ({})", addr.getHostAddress(), 
                           addr.isReachable(5000) ? "可达" : "不可达");
            }
        } catch (UnknownHostException e) {
            logger.error("无法解析主机: {}", host);
            throw e;
        }
    }
    
    private void testPortConnectivity(String host, int port) throws IOException {
        try (Socket socket = new Socket()) {
            socket.connect(new InetSocketAddress(host, port), 10000);
            logger.info("✅ 端口连接成功: {}:{}", host, port);
        } catch (IOException e) {
            logger.error("❌ 端口连接失败: {}:{} - {}", host, port, e.getMessage());
            throw e;
        }
    }
    
    private void testDatabaseConnection() throws SQLException {
        // 实际的数据库连接测试
        String username = environment.getProperty("spring.datasource.username");
        String password = environment.getProperty("spring.datasource.password");
        
        try (Connection conn = DriverManager.getConnection(dbUrl, username, password)) {
            logger.info("✅ 数据库连接测试成功");
        }
    }
    
    private void suggestSolutions() {
        logger.info("可能的解决方案:");
        logger.info("1. 检查主机名拼写是否正确");
        logger.info("2. 检查DNS配置或/etc/hosts文件");
        logger.info("3. 检查网络连通性和防火墙设置");
        logger.info("4. 确认数据库服务器是否运行");
        logger.info("5. 尝试使用IP地址代替主机名");
    }
}
五、解决方案
1. 修正配置
yaml
# 修正主机名/IP配置
spring:
  datasource:
    # 使用正确的主机名
    url: jdbc:mysql://correct_host:3306/my_database
    username: root
    password: 123456
    driver-class-name: com.mysql.cj.jdbc.Driver

# 或者使用环境变量
spring:
  datasource:
    url: jdbc:mysql://${DB_HOST:localhost}:${DB_PORT:3306}/${DB_NAME:my_database}
    username: ${DB_USERNAME:root}
    password: ${DB_PASSWORD:}

# Docker环境特殊配置
spring:
  datasource:
    url: jdbc:mysql://host.docker.internal:3306/my_database  # Docker访问宿主机
2. 备用主机配置
java
@Configuration
public class FallbackDataSourceConfig {
    
    @Bean
    @Primary
    public DataSource dataSource(Environment env) {
        String primaryUrl = env.getProperty("spring.datasource.url");
        String[] fallbackHosts = {
            "primary-db.example.com",
            "secondary-db.example.com", 
            "localhost",
            "127.0.0.1"
        };
        
        for (String host : fallbackHosts) {
            String testUrl = primaryUrl.replaceFirst("//[^/]+/", "//" + host + "/");
            if (testConnection(testUrl, env)) {
                logger.info("使用数据库主机: {}", host);
                return createDataSource(testUrl, env);
            }
        }
        
        throw new RuntimeException("所有数据库主机都不可用");
    }
    
    private boolean testConnection(String url, Environment env) {
        try (Connection conn = DriverManager.getConnection(
                url,
                env.getProperty("spring.datasource.username"),
                env.getProperty("spring.datasource.password"))) {
            return true;
        } catch (SQLException e) {
            logger.warn("连接失败: {} - {}", url, e.getMessage());
            return false;
        }
    }
    
    private DataSource createDataSource(String url, Environment env) {
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setJdbcUrl(url);
        dataSource.setUsername(env.getProperty("spring.datasource.username"));
        dataSource.setPassword(env.getProperty("spring.datasource.password"));
        dataSource.setConnectionTimeout(10000);
        return dataSource;
    }
}
六、预防措施
1. 配置验证脚本
bash
#!/bin/bash
# validate-db-host.sh

CONFIG_FILE="src/main/resources/application.yml"
DB_URL=$(grep "url:" $CONFIG_FILE | awk '{print $2}')
HOST=$(echo $DB_URL | grep -oP 'jdbc:mysql://\K[^:/]+')
PORT=$(echo $DB_URL | grep -oP 'jdbc:mysql://[^:]+:\K\d+')

echo "配置的数据库主机: $HOST:$PORT"

# 检查主机名解析
if nslookup "$HOST" &>/dev/null; then
    echo "✅ 主机名 $HOST 解析成功"
    IP=$(nslookup "$HOST" | grep 'Address:' | tail -1 | awk '{print $2}')
    echo "解析到的IP: $IP"
else
    echo "❌ 主机名 $HOST 解析失败"
    # 检查是否是IP地址
    if [[ $HOST =~ ^[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+$ ]]; then
        echo "配置的是IP地址: $HOST"
    fi
fi

# 检查端口连通性
if timeout 5 telnet "$HOST" "$PORT" 2>&1 | grep -q "Connected"; then
    echo "✅ 主机 $HOST 端口 $PORT 连接成功"
else
    echo "❌ 主机 $HOST 端口 $PORT 连接失败"
fi
2. 启动时网络检测
java
@Component
public class NetworkPreflightCheck {
    
    @EventListener(ApplicationEnvironmentPreparedEvent.class)
    public void preflightCheck(ApplicationEnvironmentPreparedEvent event) {
        ConfigurableEnvironment env = event.getEnvironment();
        String dbUrl = env.getProperty("spring.datasource.url");
        
        if (dbUrl != null) {
            String host = extractHost(dbUrl);
            
            try {
                // 提前解析主机名
                InetAddress.getByName(host);
                logger.info("预检: 主机 {} 解析正常", host);
            } catch (UnknownHostException e) {
                logger.warn("预检: 主机 {} 解析失败，应用启动可能失败", host);
            }
        }
    }
}
七、不同场景的错误特征
1. 开发环境
症状：Unknown host 'localhos'（拼写错误）

常见原因：主机名拼写错误或本地MySQL未安装

解决：修正拼写或安装MySQL

2. Docker环境
症状：Unknown host 'localhost'（容器内）

常见原因：容器内localhost指向容器自身

解决：使用host.docker.internal或自定义网络

3. 云环境
症状：连接超时

常见原因：安全组规则阻止访问

解决：检查云服务商的安全组配置

4. 本地网络
症状：No route to host

常见原因：网络配置错误或VPN问题

解决：检查网络连接和路由表

关键识别特征：

Unknown host - 主机名解析失败

No route to host - 网络路由问题

Connection timed out - 防火墙阻止或服务未运行

Host is down - 主机不可达