druid配置说明及监控展示_druid sql监控页面说明

一、配置说明

二、开启页面统计

一、引用starter包的方式

1. 引入依赖

<!-- druid -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.9</version>
</dependency>
1
2
3
4
5
6

2. 数据源配置
   yml：

  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    url: jdbc:mysql://localhost:3306/dwsurvey?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&serverTimezone=Asia/Shanghai
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver
    druid:
#      初始化时建立物理连接的个数
      initial-size: 5
#       最小连接池数量
      min-idle: 5
#        最大连接池数量
      max-active: 20
#        获取连接时最大等待时间，单位毫秒
      max-wait: 5000
      # 状态监控
      filter:
        stat:
        enabled: true
        db-type: mysql
        log-slow-sql: true
        slow-sql-millis: 2000
      # 监控过滤器
      web-stat-filter:
        enabled: true
        exclusions:
          - "*.js"
          - "*.gif"
          - "*.jpg"
          - "*.png"
          - "*.css"
          - "*.ico"
          - "/druid/*"
      # druid 监控页面
      stat-view-servlet:
#        是否开启监控
        enabled: true
#        监控路径
        url-pattern: /druid/*
#        是否可以重置数据
        reset-enable: false
#        登录密码
        login-password: root
#        登录名
        login-username: root
      validation-query: "SELECT 1 FROM DUAL "
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

或者
web.xml

    <!-- 添加druid监控-->
    <servlet>
        <servlet-name>DruidStatView</servlet-name>
        <servlet-class>com.alibaba.druid.support.http.StatViewServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>DruidStatView</servlet-name>
        <url-pattern>/druid/*</url-pattern>
    </servlet-mapping>
    <!-- 添加Web应用等监控-->
    <filter>
        <filter-name>DruidWebStatFilter</filter-name>
        <filter-class>com.alibaba.druid.support.http.WebStatFilter</filter-class>
        <init-param>
            <param-name>exclusions</param-name>
            <param-value>*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*</param-value>
        </init-param>
        <init-param>
            <param-name>profileEnable</param-name>
            <param-value>true</param-value>
        </init-param>
        <init-param>
            <param-name>principalCookieName</param-name>
            <param-value>USER_COOKIE</param-value>
        </init-param>
        <init-param>
            <param-name>principalSessionName</param-name>
            <param-value>USER_SESSION</param-value>
        </init-param>
    </filter>
    <filter-mapping>
        <filter-name>DruidWebStatFilter</filter-name>
        <url-pattern>/*</url-pattern>
    </filter-mapping>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

3. 打开链接
   可视化页面地址: http://localhost:8080/druid/sql.html

二、单包方式

如果引入的包是单druid的包就还要配置一个bean

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import com.alibaba.druid.wall.WallConfig;
import com.alibaba.druid.wall.WallFilter;
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.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;

@Configuration
public class DruidConfiguration {

    
    @Bean
    public ServletRegistrationBean druidServlet() {
        //创建servlet注册实体
        ServletRegistrationBean<Servlet> bean = new ServletRegistrationBean<>(new StatViewServlet(),"/druid/*");
        //设置ip白名单
        //servletRegistrationBean.addInitParameter("allow","127.0.0.1");
        //设置ip黑名单，如果allow与deny共同存在时,deny优先于allow
        //servletRegistrationBean.addInitParameter("deny","192.168.0.250");
        //设置控制台管理用户
        servletRegistrationBean.addInitParameter("loginUsername","druid");
        servletRegistrationBean.addInitParameter("loginPassword","123456");
        //是否可以重置数据
        servletRegistrationBean.addInitParameter("resetEnable","false");
        bean.setInitParameters(map);
        return bean;

    }

    @Bean
    public FilterRegistrationBean filterRegistrationBean() {
        FilterRegistrationBean<Filter> bean = new FilterRegistrationBean<>();
        //创建过滤器
        bean.setFilter(new WebStatFilter());
        Map<String,String> map = new HashMap<>();
        //忽略过滤的形式
        map.put("exclusions","*.js,*.css,/druid/*");
        //设置过滤器过滤路径
        bean.setUrlPatterns(Arrays.asList("/*"));
        bean.setInitParameters(map);
        return bean;

    }


    @Bean
    public WallFilter wallFilter(){
        WallFilter wallFilter = new WallFilter();
        wallFilter.setConfig(wallConfig());
        return wallFilter;
    }

    @Bean
    public WallConfig wallConfig(){
        WallConfig wallConfig = new WallConfig();
        wallConfig.setMultiStatementAllow(true);
        //允许一次执行多条语句
        wallConfig.setNoneBaseStatementAllow(true);
        //是否允许非以上基本语句的其他语句
        wallConfig.setStrictSyntaxCheck(false);
        //是否进行严格的语法检测
        return wallConfig;
    }

    @Bean(name="dataSource")
    public DataSource dataSource(){
        return new DruidDataSource();
    }

    // 配置事物管理器
    @Bean(name="transactionManager")
    public DataSourceTransactionManager transactionManager(){
        return new DataSourceTransactionManager(dataSource());
    }

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85

这时候spring配置文件中的druid下面的所有的配置默认都不会生效，需要自己绑定相应的参数

有些springboot项目启动打开druid可视化界面正常，但是看不了监控中的sql语句，这时候我们在jvm启动的时候配置一个命令：
-Ddruid.filters=mergeStat -Ddruid.useGlobalDataSourceStat=true
开启druid的全局监控，这样就行了！

三、日志文件监控

Druid提供了多种日志文件监控 commons-logging、log4j等，这里我们主要使用slf4j和logback来进行日志监控配置。

首先要引入slf4j和logback相关的jar文件（从Maven公共仓库下载 http://search.maven.org/）

<slf4j.version>1.7.7</slf4j.version> 
<logback.version>1.1.2</logback.version>

<dependency> 
    <groupId>org.slf4j</groupId> 
    <artifactId>slf4j-api</artifactId> 
    <version>${slf4j.version}</version> 
 </dependency> 
<dependency> 
    <groupId>ch.qos.logback</groupId> 
    <artifactId>logback-access</artifactId> 
    <version>${logback.version}</version> 
</dependency> 
<dependency> 
    <groupId>ch.qos.logback</groupId> 
    <artifactId>logback-core</artifactId> 
    <version>${logback.version}</version> 
</dependency> 
<dependency> 
    <groupId>ch.qos.logback</groupId> 
    <artifactId>logback-classic</artifactId> 
    <version>${logback.version}</version> 
</dependency>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

接下配置logback的配置文件(./conf/logback.xml)

<configuration>

 <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender"> 
  <layout class="ch.qos.logback.classic.PatternLayout"> 
   <Pattern>%d{HH:mm:ss.SSS} [%thread] %-5level %logger{36} - %msg%n 
   </Pattern> 
  </layout> 
 </appender>

 <appender name="FILE" class="ch.qos.logback.core.FileAppender"> 
  <file>./logs/druid_info.log</file> 
  <layout class="ch.qos.logback.classic.PatternLayout"> 
   <Pattern>%d{HH:mm:ss.SSS} [%thread] %-5level %logger{36} - %msg%n</Pattern> 
  </layout> 
  <filter class="ch.qos.logback.classic.filter.ThresholdFilter"> 
   <level>debug</level> 
  </filter> 
 </appender>

 <root level="DEBUG"> 
  <appender-ref ref="FILE" /> 
 </root> 
</configuration>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

测试

public class TestMain {
 public static void loadLoggerContext() { 
  System.getProperties().put("logback.configurationFile", "./conf/logback.xml"); 
  LoggerContext lc = (LoggerContext) LoggerFactory.getILoggerFactory(); 
  StatusPrinter.setPrintStream(System.err); 
  StatusPrinter.print(lc); 
 }

 public static void main(String[] args) { 
  try { 
   loadLoggerContext(); 
   FileSystemXmlApplicationContext context = new FileSystemXmlApplicationContext("./conf/spring-base.xml"); 

  } catch (Exception e) { 
   System.out.println(e); 
  } 
 } 
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18