SpringBoot3.x使用Druid
- BiliBili视频
- 官网:druid/druid-spring-boot-starter at master · alibaba/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分钟)
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。