zabbix 数据库 MySQL
背景
通过操作 zabbix 后端MySQL数据库实现web前端的配置，并开启数据库的语句查询日志功能，记录 zabbix前端 web 操作对应后端数据库的查询语句以便熟悉数据库查询语句。
MySQL日志部分配置
#错误日志
log-error = /var/log/mariadb/mariadb.log
#慢查询日志
slow_query_log = 1
slow_query_log_file = /var/log/mariadb/ZABBIX-DB1.slow.log
#查询语句日志
general-log = 1
general-log-file = /var/log/mariadb/sql_row.logMySQL 配置
egrep -v "*#|^$" /etc/my.cnf.d/server.cnf
[server]
[mysqld]
datadir = /mariadb-data/mysql
socket = /mariadb-data/mysql/mysql.sock
log-error = /var/log/mariadb/mariadb.log
max_connections = 1000
connect_timeout = 60
wait_timeout = 600
interactive_timeout = 600
lock_wait_timeout = 3600
skip_name_resolve = 1
skip_external_locking = 1
open_files_limit = 65535
explicit_defaults_for_timestamp = 1
slow_query_log = 1
slow_query_log_file = /var/log/mariadb/ZABBIX-DB1.slow.log
general-log = 1
general-log-file = /var/log/mariadb/sql_row.log
key_buffer_size = 1M
read_buffer_size = 4M
read_rnd_buffer_size = 2M
table_open_cache = 1024
table_definition_cache = 1024
table_open_cache_instances = 64
thread_cache_size = 512
sort_buffer_size = 16M
join_buffer_size = 64M
bulk_insert_buffer_size = 64M
tmp_table_size = 64M
max_heap_table_size = 776M
query_cache_limit = 1G
query_cache_size = 1G
symbolic-links = 0
max_allowed_packet = 512M
bind-address = 0.0.0.0
innodb_file_per_table = 1
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_log_file_size = 1G
innodb_log_buffer_size = 32M
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout = 600
innodb_autoinc_lock_mode = 2
innodb_doublewrite = OFF
innodb_thread_concurrency = 16
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_write_io_threads = 32
innodb_read_io_threads = 16
innodb_purge_threads = 4
innodb_page_cleaners = 4
innodb_open_files = 65535
innodb_max_dirty_pages_pct = 50
innodb_lru_scan_depth = 4000
innodb_status_file = 1
innodb_change_buffer_max_size = 50
[mysql]
prompt=(\\u@\\h)[\\d]>\\_
host=localhost
user=zabbix
password="zabbix@zabbix"
socket=/mariadb-data/mysql/mysql.sock
[mysqladmin]
host=localhost
user=zabbix
password="zabbix@zabbix"
socket=/mariadb-data/mysql/mysql.sock
[galera]
[embedded]
[mariadb]
[mariadb-10.3]重启 mariadb 服务
systemctl restart mariadb查看记录数据库查询日志是否开启
show variables like 'general%';
开启日志（默认开启）
set global general_log = on;关闭日志
set global general_log = off;查看查询日志
tail -f /var/log/mariadb/sql_row.log |grep tag
zabbix tags 标签
zabbix 中 tags 标签为例，前面的文章介绍过zabbix 标签分为模板级，主机级，触发级，其中模板级和主机级标签都在host_tag 表中，使用hostid 区分。
模板id
数据库查询模板的tag
主机id
数据库查询主机的tag
触发器id
数据库查询触发器的tag
查看 host_tag 表结构
desc host_tag;
增
INSERT INTO host_tag (hostid,tag,value,hosttagid) VALUES ('10264','zabbixelk','zabbixelk','4');
删
DELETE FROM host_tag WHERE hosttagid='6'
改
update zabbix.host_tag set value='zabbixelk' where hosttagid='10';
查
SELECT hosttagid,hostid,tag,value FROM host_tag;
查看hostid
select host,hostid from hosts where host='172.18.112.101';
select host,hostid from hosts where host IN ('172.18.112.101','172.18.112.102');
select hostid,ip from interface where ip IN ('192.168.99.50','192.168.99.51','192.168.99.185','192.168.99.186');
批量增
insert into host_tag(hostid,tag,value,hosttagid) values ('10399','qkl','qkl','11'),('10400','qkl','qkl','12'),('10401','qkl','qkl','13');
获取当前未恢复告警 Top10
/usr/bin/mysql -uroot -pxxxxx zabbix -e "SELECT p.eventid,p.clock,p.name,p.severity FROM problem p WHERE p.source='0' AND p.object='0' AND NOT EXISTS (SELECT NULL FROM event_suppress es WHERE es.eventid=p.eventid) AND p.r_eventid IS NULL ORDER BY p.eventid DESC LIMIT 10;"
unix 时间转换成北京时间
SELECT p.eventid,FROM_UNIXTIME(p.clock,'%Y-%m-%d %H:%i:%s') as clock,p.name,p.severity FROM problem p WHERE p.source='0' AND p.object='0' AND NOT EXISTS (SELECT NULL FROM event_suppress es WHERE es.eventid=p.eventid) AND p.r_eventid IS NULL ORDER BY p.eventid DESC LIMIT 10;
shell 获取当前未恢复告警 Top10发送email
#!/bin/bash
user="root"
passwd="xxxxxx"
datebase="zabbix"
host="192.168.99.51"
date=`date '+%Y-%m-%d-%H'`
command="SELECT p.eventid,FROM_UNIXTIME(p.clock,'%Y-%m-%d %H:%i:%s') as clock,p.name,p.severity FROM problem p WHERE p.source='0' AND p.object='0' AND NOT EXISTS (SELECT NULL FROM event_suppress es WHERE es.eventid=p.eventid) AND p.r_eventid IS NULL ORDER BY p.eventid DESC LIMIT 10;"
/usr/bin/mysql -u"$user" -p"$passwd" "$datebase" -h"$host" -e "$command" -H > /opt/sql_"$date".html
echo "${date} 运维监控平台告警未恢复事件TOP 10" | mailx -s "邮件标题" -a /opt/sql_"$date".html xxxxxx使用mailx发送邮件
安装mailx
yum -y install mailx进入mail.rc进行配置
vi /etc/mail.rc在其最下面加几句配置（stmp服务器自己查自己用到的邮箱）
# s-it-mode
# 指定邮箱
set from=xxxxxx@qq.com
# 邮箱协议
set smtp="smtp.qq.com"
# 邮箱账号
set smtp-auth-user="xxxxx@qq.com"
# 邮箱授权码，QQ邮箱后台里面去授权
set smtp-auth-password="xxxxxx"
# 登陆方式
set smtp-auth=login邮箱测试
echo "内容" | mail -v -s "标题" xxxx当前告警未恢复事件 Top 10
