一、概览

二、实例管理

三、SQL审核

四、SQL查询

五、日志(慢日志/错误日志)

六、慢日志使用TABLE保存

ELK可以和bigops系统共用一套,也可以单独找机器搭建。

1)Logstash配置

编辑/opt/logstash-conf/mysql.conf,在output部分配置es相关信息。

input{
  beats{
    type => "mysql"
    host => "0.0.0.0"
    port => 6044
    codec => plain{ charset => "UTF-8" }
  }
}

filter {

  grok {
    match => [ 'message', "(?m)^%{NUMBER:date} *%{NOTSPACE:time} %{NUMBER:bytes} %{GREEDYDATA:message}" ]
    overwrite => [ 'message' ]
    remove_field => ["time"]
  }

  grok {
    # 有ID有use
    match => [ "message", "^#\s+User@Host:\s+%{USER:user}\[[^\]]+\]\s+@\s+(?:(?<clienthost>\S*) )?\[(?:%{IP:clientip})?\]\s+Id:\s+%{NUMBER:id}\n# Query_time: %{NUMBER:query_time}\s+Lock_time: %{NUMBER:lock_time}\s+Rows_sent: %{NUMBER:rows_sent}\s+Rows_examined: %{NUMBER:rows_examined}\nuse\s(?<dbname>\w+);\nSET\s+timestamp=%{NUMBER:timestamp_mysql};\n(?<query>[\s\S]*)" ]

    # 有ID无use
    match => [ "message", "^#\s+User@Host:\s+%{USER:user}\[[^\]]+\]\s+@\s+(?:(?<clienthost>\S*) )?\[(?:%{IP:clientip})?\]\s+Id:\s+%{NUMBER:id}\n# Query_time: %{NUMBER:query_time}\s+Lock_time: %{NUMBER:lock_time}\s+Rows_sent: %{NUMBER:rows_sent}\s+Rows_examined: %{NUMBER:rows_examined}\nSET\s+timestamp=%{NUMBER:timestamp_mysql};\n(?<query>[\s\S]*)" ]

    # 无ID有use
    match => [ "message", "^#\s+User@Host:\s+%{USER:user}\[[^\]]+\]\s+@\s+(?:(?<clienthost>\S*) )?\[(?:%{IP:clientip})?\]\n# Query_time: %{NUMBER:query_time}\s+Lock_time: %{NUMBER:lock_time}\s+Rows_sent: %{NUMBER:rows_sent}\s+Rows_examined: %{NUMBER:rows_examined}\nuse\s(?<dbname>\w+);\nSET\s+timestamp=%{NUMBER:timestamp_mysql};\n(?<query>[\s\S]*)" ]

    # 无ID无use
    match => [ "message", "^#\s+User@Host:\s+%{USER:user}\[[^\]]+\]\s+@\s+(?:(?<clienthost>\S*) )?\[(?:%{IP:clientip})?\]\n# Query_time: %{NUMBER:query_time}\s+Lock_time: %{NUMBER:lock_time}\s+Rows_sent: %{NUMBER:rows_sent}\s+Rows_examined: %{NUMBER:rows_examined}\nSET\s+timestamp=%{NUMBER:timestamp_mysql};\n(?<query>[\s\S]*)" ]

    remove_field => ["@version","message"]
  }

  date {
    match => ["timestamp_mysql","UNIX"]
    target => "@timestamp"
  }

  mutate {
    remove_field => ["timestamp_mysql"]
  }

}2

output {
  if [type] == "mysqlerrorlog" {
    elasticsearch {
      hosts => ["es_ip:9200"]
      user => "elastic"
      password => "changeme"
      index => "mysqlerrorlog-%{+YYYYMMdd}"
    }
  }

  if [type] == "mysqlslowlog" {
    elasticsearch {
      hosts => ["es_ip:9200"]
      user => "elastic"
      password => "changeme"
      index => "mysqlslowlog-%{+YYYYMMdd}"
    }
  }

    stdout {  }
}

2)启动Logstash

调试模式

/opt/logstash/bin/logstash -f /opt/logstash-conf/mysql.conf

后台启动

systemctl restart logstash

3)安装Filebeat

点击桌面/数据库/日志,左侧树有3个选项卡,点击第三个选项卡配置。

第一步:点击Logstash设置,设置Logstash主机,格式是IP:PORT,端口默认是6044,根据logstash配置设定。

第二步:然后在页面给MySQL实例安装Filebeat,需要输入慢日志和错误日志的文件路径,点击安装即可。

第二步:安装完成后可以在慢日志和错误日志选项卡查看实例的测试日志信息。

4)配置Kibana dashboard

错误日志大屏
慢日志大屏

六、慢日志使用TABLE保存

设置启用命令

set global general_log =OFF;
set global slow_query_log=OFF;
set global log_output='TABLE';
ALTER TABLE mysql.slow_log engine=myisam;
DROP TABLE if exists mysql.slow_log_temp;
ALTER TABLE mysql.slow_log RENAME mysql.slow_log_temp; 
CREATE TABLE mysql.slow_log LIKE mysql.slow_log_temp;
set global slow_query_log=ON;
set global long_query_time=2;
set global log_queries_not_using_indexes=ON;
set global log_slow_admin_statements=0;
set global min_examined_row_limit=50;

设置禁用命令

set global log_output='NONE';
set global slow_query_log=OFF;
set global log_queries_not_using_indexes=OFF;

results matching ""

    No results matching ""