springboot(十四)添加druid监控，开启慢日志，配置spring监控
1、添加druid依赖
maven:
<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.16</version>
</dependency>
gradle:
// https://mvnrepository.com/artifact/com.alibaba/druid
implementation 'com.alibaba:druid:1.2.16'2、添加druid properties文件内的数据库连接信息配置
spring.datasource.name=optocon
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.druid.filters=stat,wall,logback
spring.datasource.druid.filter.stat.log-slow-sql=true
spring.datasource.druid.filter.stat.merge-sql=true
spring.datasource.druid.filter.stat.slow-sql-millis=1
spring.datasource.druid.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.druid.url=jdbc:mysql://127.0.0.1:3306/optocon?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
spring.datasource.druid.username=root
spring.datasource.druid.password=root
#配置初始化大小/最小/最大
spring.datasource.druid.initial-size=1
spring.datasource.druid.min-idle=1
spring.datasource.druid.max-active=20
#获取连接等待超时时间
spring.datasource.druid.max-wait=60000
#间隔多久进行一次检测，检测需要关闭的空闲连接
spring.datasource.druid.time-between-eviction-runs-millis=60000
spring.datasource.druid.validation-query=SELECT 'x'
spring.datasource.druid.test-while-idle=true
spring.datasource.druid.test-on-borrow=false
spring.datasource.druid.test-on-return=false
#打开PSCache，并指定每个连接上PSCache的大小。oracle设为true，mysql设为false。分库分表较多推荐设置为false
spring.datasource.druid.pool-prepared-statements=false
spring.datasource.druid.max-pool-prepared-statement-per-connection-size=20
3、配置druid连接池以及慢日志配置
其中statFilter 配置了慢日志开启，慢日志阈值等。
package com.iscas.business.config.db;
import java.sql.SQLException;
import java.util.Arrays;
import javax.sql.DataSource;
import com.alibaba.druid.filter.logging.Slf4jLogFilter;
import com.alibaba.druid.filter.stat.StatFilter;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.cloud.context.config.annotation.RefreshScope;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import com.alibaba.druid.pool.DruidDataSource;
import lombok.extern.slf4j.Slf4j;
/**
*
*
- @author zhuquanwen
- @vesion 1.0
- @date 2018/8/31 11:01
- @since jdk1.8
*/
@Slf4j
@Configuration
@RefreshScope
public class DruidConfiguration {
@Value("\({spring.datasource.druid.url}") private String dbUrl; @Value("\){spring.datasource.druid.username}")
private String username;
@Value("\({spring.datasource.druid.password}") private String password; @Value("\){spring.datasource.druid.driver-class-name}")
private String driverClassName;
@Value("\({spring.datasource.druid.initial-size}") private int initialSize; @Value("\){spring.datasource.druid.min-idle}")
private int minIdle;
@Value("\({spring.datasource.druid.max-active}") private int maxActive; @Value("\){spring.datasource.druid.max-wait}")
private int maxWait;
@Value("\({spring.datasource.druid.time-between-eviction-runs-millis}") private int timeBetweenEvictionRunsMillis; @Value("\){spring.datasource.druid.min-evictable-idle-time-millis:60000}")
private int minEvictableIdleTimeMillis;
@Value("\({spring.datasource.druid.validation-query}") private String validationQuery; @Value("\){spring.datasource.druid.test-while-idle}")
private boolean testWhileIdle;
@Value("\({spring.datasource.druid.test-on-borrow}") private boolean testOnBorrow; @Value("\){spring.datasource.druid.test-on-return}")
private boolean testOnReturn;
@Value("\({spring.datasource.druid.pool-prepared-statements}") private boolean poolPreparedStatements; @Value("\){spring.datasource.druid.max-pool-prepared-statement-per-connection-size}")
private int maxPoolPreparedStatementPerConnectionSize;
@Value("${spring.datasource.druid.filters:stat,wall,logback}")
private String filters;
<span class="hljs-meta">@Value(<span class="hljs-meta-string">"<span class="hljs-subst">${spring.datasource.druid.filter.stat.log-slow-sql:true}</span>"</span>)</span>
<span class="hljs-keyword">private</span> boolean logslowSql;
<span class="hljs-meta">@Value(<span class="hljs-meta-string">"<span class="hljs-subst">${spring.datasource.druid.filter.stat.merge-sql:true}</span>"</span>)</span>
<span class="hljs-keyword">private</span> boolean mergeSql;
<span class="hljs-meta">@Value(<span class="hljs-meta-string">"<span class="hljs-subst">${spring.datasource.druid.filter.stat.slow-sql-millis:<span class="hljs-number">200</span>}</span>"</span>)</span>
<span class="hljs-keyword">private</span> long slowSqlMill;
// @Value("\({spring.datasource.connectionProperties}") // private String connectionProperties; // @Value("\){spring.datasource.useGlobalDataSourceStat}")
// private boolean useGlobalDataSourceStat;
<span class="hljs-meta">@Bean</span> <span class="hljs-comment">//声明其为Bean实例</span>
<span class="hljs-meta">@Primary</span> <span class="hljs-comment">//在同样的DataSource中，首先使用被标注的DataSource</span>
<span class="hljs-meta">@RefreshScope</span>
<span class="hljs-keyword">public</span> DataSource dataSource(){
DruidDataSource datasource = new DruidDataSource();
datasource.setUrl(<span class="hljs-keyword">this</span>.dbUrl);
datasource.setUsername(username);
datasource.setPassword(password);
datasource.setDriverClassName(driverClassName);
<span class="hljs-comment">//configuration</span>
datasource.setInitialSize(initialSize);
datasource.setMinIdle(minIdle);
datasource.setMaxActive(maxActive);
datasource.setMaxWait(maxWait);
datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
datasource.setValidationQuery(validationQuery);
datasource.setTestWhileIdle(testWhileIdle);
datasource.setTestOnBorrow(testOnBorrow);
datasource.setTestOnReturn(testOnReturn);
datasource.setPoolPreparedStatements(poolPreparedStatements);
datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
<span class="hljs-comment">// datasource.setUseGlobalDataSourceStat(useGlobalDataSourceStat);</span>
<span class="hljs-keyword">try</span> {
datasource.setFilters(filters);
} <span class="hljs-keyword">catch</span> (SQLException e) {
log.error(<span class="hljs-string">"druid configuration initialization filter: "</span>+ e);
}
datasource.setProxyFilters(Arrays.asList(statFilter(),logFilter()));
// datasource.setConnectionProperties(connectionProperties);
return datasource;
}
<span class="hljs-meta">@Bean</span>
<span class="hljs-meta">@Primary</span>
<span class="hljs-keyword">public</span> StatFilter statFilter(){
StatFilter statFilter = new StatFilter();
statFilter.setSlowSqlMillis(slowSqlMill);
statFilter.setLogSlowSql(logslowSql);
statFilter.setMergeSql(mergeSql);
<span class="hljs-keyword">return</span> statFilter;
}
<span class="hljs-meta">@Bean</span>
<span class="hljs-keyword">public</span> Slf4jLogFilter logFilter(){
Slf4jLogFilter filter = new Slf4jLogFilter();
// filter.setResultSetLogEnabled(false);
// filter.setConnectionLogEnabled(false);
// filter.setStatementParameterClearLogEnable(false);
// filter.setStatementCreateAfterLogEnabled(false);
// filter.setStatementCloseAfterLogEnabled(false);
// filter.setStatementParameterSetLogEnabled(false);
// filter.setStatementPrepareAfterLogEnabled(false);
return filter;
}
}
4、配置druid监控，过滤URL用户名密码黑名单白名单等
package com.iscas.business.config.db;
import com.alibaba.druid.filter.stat.StatFilter;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import com.iscas.business.config.StaticInfo;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.Ordered;
/**
-
Druid监控
-
@author zhuquanwen
-
@vesion 1.0
-
@date 2018/8/31 16:07
-
@since jdk1.8
*/
@Configuration
public class DruidMonitorConfiguration {
/**
-
注册一个StatViewServlet
-
@return
*/
@Bean
public ServletRegistrationBean DruidStatViewServle2(){
<span class="hljs-comment">//org.springframework.boot.context.embedded.ServletRegistrationBean提供类的进行注册.</span>
ServletRegistrationBean servletRegistrationBean = <span class="hljs-keyword">new</span> ServletRegistrationBean(<span class="hljs-keyword">new</span> StatViewServlet(),<span class="hljs-string">"/druid2/*"</span>);
<span class="hljs-comment">//添加初始化参数：initParams</span>
<span class="hljs-comment">//白名单：</span>
// servletRegistrationBean.addInitParameter("allow","127.0.0.1");
//IP黑名单 (存在共同时，deny优先于allow) : 如果满足deny的话提示:Sorry, you are not permitted to view this page.
servletRegistrationBean.addInitParameter("deny","192.168.1.73");
//登录查看信息的账号密码.
servletRegistrationBean.addInitParameter("loginUsername","druid");
servletRegistrationBean.addInitParameter("loginPassword","druid");
//是否能够重置数据.
servletRegistrationBean.addInitParameter("resetEnable","false");
<span class="hljs-keyword">return</span> servletRegistrationBean;
}
<span class="hljs-comment">/**
* 注册一个：filterRegistrationBean
* <span class="hljs-doctag">@return</span>
*/</span>
<span class="hljs-meta">@Bean</span>
<span class="hljs-function"><span class="hljs-keyword">public</span> FilterRegistrationBean <span class="hljs-title">druidStatFilter2</span><span class="hljs-params">()</span></span>{
FilterRegistrationBean filterRegistrationBean = <span class="hljs-keyword">new</span> FilterRegistrationBean(<span class="hljs-keyword">new</span> WebStatFilter());
<span class="hljs-comment">//添加过滤规则.</span>
filterRegistrationBean.addUrlPatterns(<span class="hljs-string">"/*"</span>);
<span class="hljs-comment">//添加不需要忽略的格式信息.</span>
filterRegistrationBean.addInitParameter(<span class="hljs-string">"exclusions"</span>,<span class="hljs-string">"*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid2/*，"</span> +
<span class="hljs-string">"/swagger-resources/*,/loginTest/*,/api/*,/webjars/*./webSocketServer/*,/webSocketTest/*,*.html,*.json"</span>);
<span class="hljs-keyword">return</span> filterRegistrationBean;
}
}
5、logback.xml中添加druid慢日志配置单独打包到一个文件内
<appender name="DruidFILE" class="ch.qos.logback.core.rolling.RollingFileAppender">
<!-- 正在记录的日志文件的路径及文件名 -->
<file>\({LOG_PATH}/\){APPDIR}/log_druid.log</file>
<!-- 日志记录器的滚动策略，按日期，按大小记录 -->
<rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
<!-- 归档的日志文件的路径，例如今天是2013-12-21日志，当前写的日志文件路径为file节点指定，可以将此文件与file指定文件路径设置为不同路径，从而将当前日志文件或归档日志文件置不同的目录。
而2013-12-21的日志文件在由fileNamePattern指定。%d{yyyy-MM-dd}指定日期格式，%i指定索引 -->
<fileNamePattern>\({LOG_PATH}/\){APPDIR}/info/log-druid-%d{yyyy-MM-dd}.%i.log</fileNamePattern>
<!-- 除按日志记录之外，还配置了日志文件不能超过2M，若超过2M，日志文件会以索引0开始，
命名日志文件，例如log-error-2013-12-21.0.log -->
<timeBasedFileNamingAndTriggeringPolicy class="ch.qos.logback.core.rolling.SizeAndTimeBasedFNATP">
<maxFileSize>2MB</maxFileSize>
</timeBasedFileNamingAndTriggeringPolicy>
</rollingPolicy>
<!-- 追加方式记录日志 -->
<append>true</append>
<!-- 日志文件的格式 -->
<encoder class="ch.qos.logback.classic.encoder.PatternLayoutEncoder">
<pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} %-5level %logger Line:%-3L - %msg%n</pattern>
<charset>utf-8</charset>
</encoder>
<!-- 此日志文件只记录info级别的 -->
<filter class="ch.qos.logback.classic.filter.LevelFilter">
<level>ERROR</level>
<onMatch>ACCEPT</onMatch>
<onMismatch>DENY</onMismatch>
</filter>
</appender><logger name="com.alibaba.druid.filter.stat.StatFilter" level="ERROR">
<appender-ref ref="DruidFILE" />
</logger>6、开启spring监控
package com.iscas.business.config.db;
import com.alibaba.druid.support.spring.stat.DruidStatInterceptor;
import org.springframework.aop.support.DefaultPointcutAdvisor;
import org.springframework.aop.support.JdkRegexpMethodPointcut;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Scope;
/**
-
//TODO
-
@author zhuquanwen
-
@vesion 1.0
-
@date 2018/8/31 17:37
-
@since jdk1.8
*/
@Configuration
public class SpringDaoMethodAspect {
@Bean
public DruidStatInterceptor druidStatInterceptor() {
DruidStatInterceptor dsInterceptor = new DruidStatInterceptor();
return dsInterceptor;
}
@Bean
@Scope("prototype")
public JdkRegexpMethodPointcut druidStatPointcut() {
JdkRegexpMethodPointcut pointcut = new JdkRegexpMethodPointcut();
pointcut.setPatterns("com.iscas.business.test.controller.",
"com.iscas.business.controller.");
<span class="hljs-keyword">return</span> pointcut;
}
@Bean
public DefaultPointcutAdvisor druidStatAdvisor(DruidStatInterceptor druidStatInterceptor, JdkRegexpMethodPointcut druidStatPointcut) {
DefaultPointcutAdvisor defaultPointAdvisor = new DefaultPointcutAdvisor();
defaultPointAdvisor.setPointcut(druidStatPointcut);
defaultPointAdvisor.setAdvice(druidStatInterceptor);
return defaultPointAdvisor;
}
}
7、访问 http://[ip]:[port]/[context-path]/druid2/index.html，使用配置的用户名密码登录
首页：
sql监控，按照配置的慢SQL，大于阈值的就显示为红色，也会记录上上面定义的慢日志文件中
URI监控
spring监控