# 排查过程

1. 检查应用错误日志
Caused by: java.net.ConnectException: Connection timed out (Connection timed out)
at java.net.PlainSocketImpl.socketConnect(Native Method)
at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350)

2. 检查网络连通性
$ ping 192.168.1.101
PING 192.168.1.101 (192.168.1.101) 56(84) bytes of data.
64 bytes from 192.168.1.101: icmp_seq=1 ttl=64 time=0.5 ms

# 网络层正常

3. 检查端口连通性
$ telnet 192.168.1.101 2881
Trying 192.168.1.101…
telnet: connect to address 192.168.1.101: Connection timed out

# 端口不通

4. 检查防火墙
$ systemctl status firewalld
● firewalld.service – firewalld – dynamic firewall daemon
Active: active (running) since Mon 2024-01-20 09:00:00 CST

$ firewall-cmd –list-all
public (active)
target: default
icmp-block-inversion: no
interfaces: eth0
sources:
services: ssh dhcpv6-client
ports:
protocols:

# 发现2881端口未开放

5. 解决方法
# 开放OceanBase端口
$ firewall-cmd –permanent –add-port=2881/tcp
$ firewall-cmd –permanent –add-port=2882/tcp
$ firewall-cmd –reload

# 验证
$ firewall-cmd –list-all
public (active)
ports: 2881/tcp 2882/tcp

$ telnet 192.168.1.101 2881
Trying 192.168.1.101…
Connected to 192.168.1.101.

# 应用重新连接成功

# 预防措施
#!/bin/bash
# firewall_check.sh
# from:www.itpux.com.qq113257174.wx:itpux-com

# 检查防火墙状态
if systemctl is-active firewalld >/dev/null 2>&1; then
echo “Firewalld is active”
# 检查端口
if ! firewall-cmd –list-ports | grep -q “2881/tcp”; then
echo “Warning: Port 2881 is not open”
fi
else
echo “Firewalld is not running”
fi

# 排查过程

1. 检查应用错误日志
Caused by: java.sql.SQLException: Access denied for user ‘fgedu_app’@’192.168.1.10’ (using password: YES)
at com.oceanbase.jdbc.internal.protocol.AbstractConnectProtocol.checkErrorMessage(AbstractConnectProtocol.java:1234)

2. 手动测试连接
$ obclient -h192.168.1.101 -P2881 -ufgedu_app@fgedu_tenant -p
Enter password:
ERROR 1045 (42000): Access denied for user ‘fgedu_app’@’192.168.1.10′ (using password: YES)

3. 检查用户是否存在
$ obclient -h192.168.1.101 -P2881 -uroot@sys -p -e ”
SELECT user, host FROM mysql.user WHERE user=’fgedu_app’;
”

+———–+—————+
| user | host |
+———–+—————+
| fgedu_app | 192.168.1.% |
+———–+—————+

# 用户存在

4. 检查密码
# 应用配置文件中密码可能错误
# 检查应用配置
$ cat /app/config/db.properties | grep password
jdbc.password=WrongPassword

# 发现密码错误

5. 解决方法
# 重置密码
$ obclient -h192.168.1.101 -P2881 -uroot@sys -p -e ”
ALTER USER ‘fgedu_app’@’192.168.1.%’ IDENTIFIED BY ‘Correct@Pass2024!’;
”

# 更新应用配置
$ vi /app/config/db.properties
jdbc.password=Correct@Pass2024!

# 重启应用
$ systemctl restart app

# 连接成功

# 预防措施
#!/bin/bash
# password_check.sh
# from:www.itpux.com.qq113257174.wx:itpux-com

# 测试连接
if ! obclient -h192.168.1.101 -P2881 -ufgedu_app@fgedu_tenant -pCorrect@Pass2024! -e “SELECT 1” >/dev/null 2>&1; then
echo “Warning: Database connection test failed”
# 发送告警
echo “Database connection test failed” | mail -s “DB Alert” dba@fgedu.net.cn
fi

# 排查过程

1. 检查应用错误日志
Caused by: java.sql.SQLException: Too many connections
at com.oceanbase.jdbc.internal.protocol.AbstractConnectProtocol.checkErrorMessage(AbstractConnectProtocol.java:1234)

2. 检查当前连接数
$ obclient -h192.168.1.101 -P2881 -uroot@sys -p -e ”
SHOW STATUS LIKE ‘Threads_connected’;
SHOW STATUS LIKE ‘Max_used_connections’;
SHOW VARIABLES LIKE ‘max_connections’;
”

+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| Threads_connected | 100 |
| Max_used_connections | 100 |
| max_connections | 100 |
+——————-+——-+

# 连接数已满

3. 查看连接详情
$ obclient -h192.168.1.101 -P2881 -uroot@sys -p -e ”
SELECT
user,
host,
db,
command,
time,
state,
COUNT(*) as count
FROM information_schema.processlist
GROUP BY user, host, db, command, time, state
ORDER BY count DESC;
”

+—————-+——————+——–+———+——+——-+——-+
| user | host | db | command | time | state | count |
+—————-+——————+——–+———+——+——-+——-+
| fgedu_app@app| 192.168.1.10:xxx | fgedudb| Sleep | 3600 | | 80 |
| fgedu_app@app| 192.168.1.10:xxx | fgedudb| Query | 0 |executing| 10 |
+—————-+——————+——–+———+——+——-+——-+

# 发现大量空闲连接

4. 解决方法
# 方法1：增加最大连接数
$ obclient -h192.168.1.101 -P2881 -uroot@sys -p -e ”
SET GLOBAL max_connections = 500;
”

# 方法2：清理空闲连接
$ obclient -h192.168.1.101 -P2881 -uroot@sys -p -e ”
— 查看空闲连接
SELECT id, user, host, time
FROM information_schema.processlist
WHERE command=’Sleep’ AND time > 3600;

— 关闭空闲连接
SELECT CONCAT(‘KILL ‘, id, ‘;’)
FROM information_schema.processlist
WHERE command=’Sleep’ AND time > 3600;
”

# 方法3：优化应用连接池
# 减少连接池最大连接数
# 减少连接空闲时间
# 启用连接验证

5. 长期解决方案
# 配置连接池参数
spring.datasource.hikari.maximum-pool-size=50
spring.datasource.hikari.idle-timeout=600000
spring.datasource.hikari.max-lifetime=1800000

# 监控连接使用
#!/bin/bash
# connection_alert.sh
# from:www.itpux.com.qq113257174.wx:itpux-com

CONN_COUNT=$(obclient -e “SHOW STATUS LIKE ‘Threads_connected'” | awk ‘NR==2{print $2}’)
MAX_CONN=$(obclient -e “SHOW VARIABLES LIKE ‘max_connections'” | awk ‘NR==2{print $2}’)
USAGE=$((CONN_COUNT * 100 / MAX_CONN))

if [ $USAGE -gt 80 ]; then
echo “High connection usage: ${USAGE}%” | mail -s “Connection Alert” dba@fgedu.net.cn
fi