SpringBoot3.x使用Druid

言午日尧耳总發表於2024-03-10
Spring BootUI

SpringBoot3.x使用Druid

  • BiliBili影片
  • 官網：druid/druid-spring-boot-starter at master · alibaba/druid
  • 參考文章
    • Spring Boot 3 整合 Druid 連線池詳解

起步

安裝

  • 最新版本查詢：https://mvnrepository.com/artifact/com.alibaba/druid-spring-boot-3-starter
  • 注意：Druid配置變更後需要重啟才能生效
    <properties>
        <druid.version>1.2.21</druid.version>
    </properties>

    <dependencies>

        <dependency>
           <groupId>com.alibaba</groupId>
           <artifactId>druid-spring-boot-starter</artifactId>
           <version>${druid.version}</version>
        </dependency>
        
        <!-- SpringBoot3.x使用這個庫，才能夠開啟Druid監控網頁 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-3-starter</artifactId>
            <version>${druid.version}</version>
        </dependency>
      
    </dependencies>

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource		# Druid連線池！
    
    # 以下注釋內容與原資料庫連線相同
    url: jdbc:mysql://***
    username: ***
    password: ***
    driver-class-name: com.mysql.cj.jdbc.Driver
  • 其他連線池設定
spring.datasource.druid.socket-timeout= 6000 # 請求超時時間（單位：毫秒）

spring.datasource.druid.initial-size=
spring.datasource.druid.max-active=
spring.datasource.druid.min-idle=
spring.datasource.druid.max-wait=
spring.datasource.druid.pool-prepared-statements=
spring.datasource.druid.max-pool-prepared-statement-per-connection-size= 
spring.datasource.druid.max-open-prepared-statements= #和上面的等價
spring.datasource.druid.validation-query=
spring.datasource.druid.validation-query-timeout=
spring.datasource.druid.test-on-borrow=
spring.datasource.druid.test-on-return=
spring.datasource.druid.test-while-idle=
spring.datasource.druid.time-between-eviction-runs-millis=
spring.datasource.druid.min-evictable-idle-time-millis=
spring.datasource.druid.max-evictable-idle-time-millis=
spring.datasource.druid.filters= #配置多個英文逗號分隔
....//more

基礎連線資訊

  • 非必須，無聊看著玩的
  • 建立任意控制器和任意路徑，返回Druid的連線資訊
@RestController
@RequestMapping("/index")
public class IndexController {
    @GetMapping("/druid-status")
    public Object druidStat(){
        return DruidStatManagerFacade.getInstance().getDataSourceStatDataList();
    }
}
  • 響應示例如下
[
  {
    "Identity": 1583082378,
    "Name": "DataSource-1583082378",
    "DbType": "h2",
    "DriverClassName": "org.h2.Driver",
    "URL": "jdbc:h2:file:./demo-db",
    "UserName": "sa",
    "FilterClassNames": [
      "com.alibaba.druid.filter.stat.StatFilter"
    ],
    "WaitThreadCount": 0,
    "NotEmptyWaitCount": 0,
    "NotEmptyWaitMillis": 0,
    "PoolingCount": 2,
    "PoolingPeak": 2,
    "PoolingPeakTime": 1533782955104,
    "ActiveCount": 0,
    "ActivePeak": 1,
    "ActivePeakTime": 1533782955178,
    "InitialSize": 2,
    "MinIdle": 2,
    "MaxActive": 30,
    "QueryTimeout": 0,
    "TransactionQueryTimeout": 0,
    "LoginTimeout": 0,
    "ValidConnectionCheckerClassName": null,
    "ExceptionSorterClassName": null,
    "TestOnBorrow": true,
    "TestOnReturn": true,
    "TestWhileIdle": true,
    "DefaultAutoCommit": true,
    "DefaultReadOnly": null,
    "DefaultTransactionIsolation": null,
    "LogicConnectCount": 103,
    "LogicCloseCount": 103,
    "LogicConnectErrorCount": 0,
    "PhysicalConnectCount": 2,
    "PhysicalCloseCount": 0,
    "PhysicalConnectErrorCount": 0,
    "ExecuteCount": 102,
    "ErrorCount": 0,
    "CommitCount": 100,
    "RollbackCount": 0,
    "PSCacheAccessCount": 100,
    "PSCacheHitCount": 99,
    "PSCacheMissCount": 1,
    "StartTransactionCount": 100,
    "TransactionHistogram": [
      55,
      44,
      1,
      0,
      0,
      0,
      0
    ],
    "ConnectionHoldTimeHistogram": [
      53,
      47,
      3,
      0,
      0,
      0,
      0,
      0
    ],
    "RemoveAbandoned": false,
    "ClobOpenCount": 0,
    "BlobOpenCount": 0,
    "KeepAliveCheckCount": 0,
    "KeepAlive": false,
    "FailFast": false,
    "MaxWait": 1234,
    "MaxWaitThreadCount": -1,
    "PoolPreparedStatements": true,
    "MaxPoolPreparedStatementPerConnectionSize": 5,
    "MinEvictableIdleTimeMillis": 30001,
    "MaxEvictableIdleTimeMillis": 25200000,
    "LogDifferentThread": true,
    "RecycleErrorCount": 0,
    "PreparedStatementOpenCount": 1,
    "PreparedStatementClosedCount": 0,
    "UseUnfairLock": true,
    "InitGlobalVariants": false,
    "InitVariants": false
  }
]

Druid監控皮膚

啟用皮膚

  • Druid提供了大量監控資料，監控資料只儲存在記憶體中，重啟後會丟失，且無法集中檢視
    • 皮膚中的"重置"按鈕，指的是重置監控資料
  • 訪問地址(在url-pattern配置)：http://localhost:8080/druid
  • 可訪問頁面
    • 首頁
    • 資料來源
    • JSON API
  • IP轉發問題
    • Nginx轉發
      • 使用Nginx代理轉發，原訪問IP會被替換成Nginx的IP，所以allow需要設定成Nginx伺服器的IP
      • 但是所有訪問IP都被替換成Nginx的IP，所有請求都會放行，相當於allow失去了作用，記得使用賬號
      • 最佳實踐：外網Nginx直接遮蔽druid介面，開發機直接內網訪問原始伺服器地址，allow設定成開發機IP
    • k8s叢集代理
      • k8s叢集Ingress的IP是動態的，無法設定成準確IP，可以設定為"192.168.0.0/16,10.0.0.0/8"放行所有IP（同樣有安全問題，記得增加賬號密碼）
      • 最佳實踐：不要暴露druid介面，開發機使用"kubectl port-forward"或者"ktctl forward"命令將服務轉到本地，再透過localhost訪問
    • 其他
      • Nginx中設定"proxy_set_header Host $host;"的解決方案，在目前的最新版"1.2.21"中是無效的
spring:
  datasource:
    druid:
      stat-view-servlet:
        enabled: true           # 啟用Druid監控皮膚
        url-pattern: /druid/*   # 皮膚路徑(預設"/druid/*")（Druid已做了處理，不會被自定義過濾器攔截）
        
        reset-enable: false     # 皮膚上的重置按鈕（禁用後皮膚上重置按鈕依然會顯示和互動，但是不起作用） 
        
        login-username: admin   # 賬號(預設不需要登入，設定了才需要)
        login-password: 123456  # 密碼
        
        allow: 127.0.0.1        # 白名單，多個使用逗號隔開（不在白名單中不能訪問，預設開啟localhost/127.0.0.1）
        deny: 127.0.0.1         # 黑名單，多個使用逗號隔開
        # 使用Nginx轉發導致訪問IP丟失，allow、deny均失效，可將allow設定為 192.168.0.0/16,10.0.0.0/8 允許所有請求，注意使用賬號保護

SQL監控

  • 啟用"SQL監控"，未開啟則皮膚無任何資料
spring:
  datasource:
    druid:
      filter:
        stat:
          enabled: true           # 啟動SQL語句監控，同時會影響其他皮膚中的SQL展示（如：影響"URI監控"中的jdbc資料）
          db-type: mysql          # 資料庫型別（實測沒啥用，會自動根據上面的連線自動識別）

          log-slow-sql: true      # 記錄慢日誌（"SQL監控"皮膚中數值會標記為紅色）
          slow-sql-millis: 3000   # 慢日誌判定標準（單位：毫秒）
          slow-sql-log-level: ERROR # 自定義日誌級別，預設日誌級別"ERROR"

SQL防火牆

  • 檢查SQL語句，防止惡意操作
  • 同時記錄每一次Select/Insert/Update/...等操作
spring:
  datasource:
    druid:
      filter:
        wall:
          enabled: true               # 啟用防火牆
          db-type: h2                 # 資料庫型別（實測沒啥用，會自動根據上面的連線自動識別）
          config:
            delete-allow: false       # 是否允許DELETE操作（報錯異常：java.sql.SQLException:sql injection violation, dbType mysql, druid-version 1.2.21, delete not allow : DELETE FROM ...）
            drop-table-allow: false   # 是否允許DROP TABLE操作

Web應用/URI監控/Session監控

  • 影響到"Web應用/URI監控/Session監控"皮膚
spring:
  datasource:
    druid:
      web-stat-filter:
        enabled: true     # 啟用"Web應用/URI監控/Session監控"皮膚
        url-pattern: /*   # 監控這裡指定的路徑
        exclusions: /druid/*,*.js,*.gif,*.jpg,*.png,*.css,*.ico,*.html,*.json   # 過濾路徑

        # "Session監控"皮膚設定（RestFul純後端沒有session）
        session-stat-enable: true
        session-stat-max-count: 1000
        principal-session-name: xxc
        principal-cookie-name: xxc
        profile-enable: true

Spring監控

  • 會記錄該切面下的執行時間，Jdbc執行資料
    • 需要開啟安裝aop依賴
spring:
  datasource:
    druid:
      aop-patterns: x.y.z.controller.* # Spring監控AOP切入點，多個用逗號分隔

其他

  • 官網：druid/druid-spring-boot-starter at master · alibaba/druid
  • 所有過濾器（上面配置值包含一部分）
    • StatFilter
    • WallFilter
    • ConfigFilter
    • EncodingConvertFilter
    • Slf4jLogFilter
    • Log4jFilter
    • Log4j2Filter
    • CommonsLogFilter

問題

超時

  • 基本
Caused by: com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure...
...
The last packet successfully received from the server was 11,034 milliseconds ago. The last packet sent successfully to the server was 20,386 milliseconds ago...

LastErrorMessage:
Communications link failure The last packet successfully received from the server was 11,034 milliseconds ago. The last packet sent successfully to the server was 20,386 milliseconds ago.

LastErrorClass:
com.mysql.cj.jdbc.exceptions.CommunicationsException

LastErrorStackTrace:
com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:174)
com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:64)
com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:912)
com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:354)
com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3462)
com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:434)
com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3460)
com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:158)
com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:483)
org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64)
org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
jdk.internal.reflect.GeneratedMethodAccessor43.invoke(Unknown Source)
java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
java.base/java.lang.reflect.Method.invoke(Method.java:568)
org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:64)
jdk.proxy2/jdk.proxy2.$Proxy170.query(Unknown Source)
org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)
org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:81)
org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:62)
jdk.proxy2/jdk.proxy2.$Proxy169.query(Unknown Source)
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151)
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:145)
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
...

spring:
  datasource:
    druid:
      socket-timeout: 60000 # 連線超時時間，60000毫秒(1分鐘)

相關文章