sql 数据库主动健康检查

1. 功能说明

sql 数据库主动健康检查功能基本等同于http主动健康检查和stream主动健康检查，现在支持对mysql进行健康检查。

2. 依赖模块

mysql主动健康检查，依赖模块：

load_module modules/njt_http_sendmsg_module.so;
load_module modules/njt_helper_health_check_module.so;

3. 配置说明

3.1 API说明

查询全部tcp类型的健康检查列表：

GET http://ip+port/api/v1/hc/smysql/

查询具体upstream名为backend的tcp类型的健康检查配置：

GET http://ip+port/api/v1/hc/smysql/backend

向具体upstream名为backend添加tcp类型健康检查：

POST http://ip+port/api/v1/hc/smysql/backend

删除具体upstream名为backend的tcp类型健康检查：

DELETE http://ip+port/api/v1/hc/smysql/backend

格式说明健康检查配置项参数说明

{
  #下面这些字段是通用字段，所有类型都使用，interval、jitter、timeout、passes、fails为必填字段
  "interval": "5s",
  "jitter": "1s",
  "timeout": "5s",
  "passes": 1,
  "fails": 1,
  "visit_interval": "5s",
  "port": 80,
  
  #stream块配置由stcp和sudp类型使用
  "stream": {
    "send": "string",
    "expect": "string"
  },
  
  #http块配置主要有http使用
  "http": {
    "uri": "/",
    "header": [
      "string"
    ],
    "body": "string",
    "status": "string"
  },
  
  #ssl块配置由stcp以及http使用，在需要配置ssl时配置
  "ssl": {
    "enable": false,
    "ntls": false,
    "session_reuse": false,
    "protocols": "TLSv1 TLSv1.1 TLSv1.2",
    "ciphers": "DEFAULT",
    "name": "upstream name",
    "serverName": false,
    "verify": false,
    "verifyDepth": 1,
    "trustedCertificate": "string",
    "crl": "string",
    "certificate": "string",
    "certificateKey": "string",
    "encCertificate": "string",
    "encCertificateKey": "string"
  },
  
  #sql块配置主要由smysql使用
  "sql": {
    "select": "select 1",
    "useSsl": true,
    "user": "string",
    "password": "string",
    "db": "string"
  }
}

配置项	必填	配置说明
interval	是	主动健康检查频率 (必填)
visit_interval	否	健康检查时，如果指定的时间间隔内该server被客户端访问过，则该server跳过此次健康检查,interval > visit_interval
jitter	是	设置健康检查项定时器最大偏差。防止所有检查项同时触发。 (必填)
timeout	是	超时时间 (必填)
passes	是	server_body server 块内的指令集，每条指令用分号分隔。server_body内容可以为空。
fails	是	连续不通过fails次检测，更新peer为unhealthy状态 (必填)
port	否	指定健康检查的端口，如果不指定，使用upstream中设置的端口

sql块配置字段详细介绍

字段名称	子字段名称	类型	必须配置	默认值	说明
sql块	select	字符串	否	select 1	查询语句
	useSsl	bool	否	true	是否使用ssl连接
	user	字符串	否	-	mysql数据库用户名
	password	字符串	否	-	mysql数据库密码
	db	字符串	是	-	mysql数据库名称

3.2 mysql健康检查配置方式

njet.conf配置文件

helper broker modules/njt_helper_broker_module.so conf/mqtt.conf;
helper ctrl modules/njt_helper_ctrl_module.so conf/ctrl.conf;



user  root root;

cluster_name helper;
node_name node-u01;

error_log  logs/error.log info;
pid        logs/njet.pid;

events {
    worker_connections  1024;
}


http {
    dyn_kv_conf conf/iot-work.conf;
    include       mime.types;
    default_type  application/octet-stream;

    access_log  logs/access.log;

    vhost_traffic_status_zone;
    vhost_traffic_status_filter_by_set_key $request_uri "$realip_remote_addr to $server_name";
    variables_hash_max_size  2048;

    sendfile        on;
    keepalive_timeout  65;
  
    upstream backend1 {
  
         zone backend1_zone 128k;
         server 127.0.0.1:5800;
   
    }

   server {
        listen 5555;
        server_name test-server;
  
        location / {
          alias html;
        }
   }

   server {
  
        listen 443 ssl;
        server_name dev.test.com;
  
        ssl_reject_handshake off;
        ssl_ntls     off;
  
        ssl_certificate       certs/rsa.dev.test.com.crt.pem;
        ssl_certificate_key    certs/rsa.dev.test.com.key.pem;

        ssl_ciphers     RSA+AES128:RSA+AES256:RSA+3DES:EECDH+CHACHA20:EECDH+CHACHA20-draft:EECDH+AES128:EECDH+AES256:EECDH+3DES:!MD5;

        ssl_protocols TLSv1 TLSv1.1 TLSv1.2 TLSv1.3;

        ssl_session_cache    shared:SSL:1m;
        ssl_session_timeout  5m;
        ssl_prefer_server_ciphers  on;

        location / {
            charset utf-8;
            default_type text/html;
            return 200 "dev.test.com 443 test ok";
        }
  
    }


}

stream {


    upstream backend_s1 {

         zone backend_s1 1m;

         server 127.0.0.1:3306;


    }



    server {

        listen 22222 mesh;

         proxy_pass backend_s1;


     } 

     server {

        listen 22223 udp mesh;

        return "22223 udp ok";


     } 

     server {

         listen 22224 ssl mesh;

          ssl_certificate     certs/ca/ECC/ecc-root_cert.pem;
          ssl_certificate_key certs/ca/ECC/ecc-root_private_key.pem;

          return "22224 ssl ok";

      }



}

请求BODY
{
    "interval": "3s",
    "jitter": "1s",
    "timeout": "10s",
    "passes": 2,
    "fails": 1,
    "sql": {
    	"select": "select 1",
    	"useSsl": true,
    	"user": "string",
    	"password": "string",
    	"db": "string"
  }
}

请求命令
curl -X POST http://127.0.0.1:8081/api/v1/hc/smysql/backend  -d '{
    "interval": "3s",
    "jitter": "1s",
    "timeout": "10s",
    "passes": 2,
    "fails": 1,
    "sql": {
    	"select": "select 1",
    	"useSsl": true,
    	"user": "string",
    	"password": "string",
    	"db": "string"
  }
}'

返回
{
    "code": 0,
    "msg": "success"
}

4. 调用样例

4.1 增加新的mysql健康检查并验证检查结果

请求

curl -X POST http://127.0.0.1:8081/api/v1/hc/smysql/backend_s1  -d '{
    "interval": "3s",
    "jitter": "1s",
    "timeout": "10s",
    "passes": 2,
    "fails": 1,
    "sql": {
    	"select": "select 1",
    	"useSsl": true,
    	"user": "root",
    	"password": "123456",
    	"db": "db"
  }
}'

返回值

{
  "code": 0,
  "msg": "success"
}

通过upstream_api接口查询状态，此时显示为通过

curl -X GET http://127.0.0.1:8081/api/v1/upstream_api/stream/upstreams/backend_s1

{
  "name": "backend_s1",
  "peers": [
    {
      "id": 0,
      "server": "192.168.40.119:3306",
      "name": "192.168.40.119:3306",
      "backup": false,
      "weight": 1,
      "state": "up",
      "active": 0,
      "connections": 0,
      "sent": 0,
      "received": 0,
      "fails": 0,
      "unavail": 0,
      "health_checks": {
        "checks": 3,
        "fails": 0,
        "unhealthy": 0,
        "last_passed": true
      },
      "downtime": 0
    }
  ],
  "zombies": 0,
  "zone": "backend_s1"
}

停止mysql服务，再次查询，结果为不通过。

curl -X GET http://127.0.0.1:8081/api/v1/upstream_api/stream/upstreams/backend_s1 | jq 
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   377  100   377    0     0   5971      0 --:--:-- --:--:-- --:--:--  5984
{
  "name": "backend_s1",
  "peers": [
    {
      "id": 0,
      "server": "192.168.40.119:3306",
      "name": "192.168.40.119:3306",
      "backup": false,
      "weight": 1,
      "state": "unhealthy",
      "active": 0,
      "connections": 0,
      "sent": 0,
      "received": 0,
      "fails": 0,
      "unavail": 0,
      "health_checks": {
        "checks": 5,
        "fails": 1,
        "unhealthy": 1,
        "last_passed": false
      },
      "downtime": 1475,
      "downstart": "2025-10-20T03:25:33.342Z"
    }
  ],
  "zombies": 0,
  "zone": "backend_s1"
}

检查日志，日志中提示健康检查异常。

2025/10/20 11:26:53 [error] 25810#0: hc smysql has error ret is 0, error:Can't connect to server on '192.168.40.119' (111)
2025/10/20 11:26:53 [warn] 25810#0: hc stream check peer update status peer:192.168.40.119:3306 peerid:0 status:-1
2025/10/20 11:26:58 [error] 25810#0: hc smysql has error ret is 0, error:Can't connect to server on '192.168.40.119' (111)
2025/10/20 11:26:58 [warn] 25810#0: hc stream check peer update status peer:192.168.40.119:3306 peerid:0 status:-1
2025/10/20 11:27:03 [error] 25810#0: hc smysql has error ret is 0, error:Can't connect to server on '192.168.40.119' (111)
2025/10/20 11:27:03 [warn] 25810#0: hc stream check peer update status peer:192.168.40.119:3306 peerid:0 status:-1
2025/10/20 11:27:08 [error] 25810#0: hc smysql has error ret is 0, error:Can't connect to server on '192.168.40.119' (111)
2025/10/20 11:27:08 [warn] 25810#0: hc stream check peer update status peer:192.168.40.119:3306 peerid:0 status:-1
2025/10/20 11:27:13 [error] 25810#0: hc smysql has error ret is 0, error:Can't connect to server on '192.168.40.119' (111)
2025/10/20 11:27:13 [warn] 25810#0: hc stream check peer update status peer:192.168.40.119:3306 peerid:0 status:-1