安装:
// yum -y install mariadb mariadb-server
搭建mysql主从
报错解决:
// vim /etc/my.cnf
添加:
server-id=1
// slave-skip-errors=all
重启mysql
systemctl restart mariadb
配置主从:
> grant all on *.* to slave@'%' identified by 'slave'; #授权
> show master status; #查看二进制
> change master to master_host='192.168.157.128',master_user='slave',master_password='slave',master_log_file='mysql-bin.000001',master_log_pos=245; #同步
> show slave status \G; #查看是否同步
编辑:
vim /etc/zabbix/scripts/check_mysql.sh
#!/bin/bash
mysql="mysql -ucheck -p123 -h 192.168.1.8"
case $1 in
mysql_status)
$mysql -e "show slave status\G"|grep "Yes"|awk '{print $2}'|wc -l
;;
esac
自定义key
vim /etc/zabbix/zabbix_agentd.d/mysql.conf
UserParameter=mysql_check[*],/etc/zabbix/scripts/check_mysql.sh $1
报错处理:
// zabbix_get -s 192.168.1.8 -k mysql_check[mysql_status]
ERROR 1227 (42000) at line 1: Access denied; you need (at least one of) the SUPER,REPLICATION CLIENT privilege(s) for this operation
1:在slave节点上添加授权用户“check”
grant all on . to ‘check’@’%’ identified by ‘123’;
2:修改check_mysql.sh脚本
zabbix 监控mysql的(流量、带宽,吞吐量)
#流量之发送
Bytes_sent)
mysqladmin extended-status|grep "Bytes_sent"|awk '{print $4}'
;;
#流量之接收
Bytes_received)
mysqladmin extended-status|grep "Bytes_received"|awk '{print $4}'
;;
验证:
// zabbix_get -s 192.168.1.8 -k mysql_check[Bytes_sent]
1763940
// zabbix_get -s 192.168.1.8 -k mysql_check[Bytes_received]
2070093
zabbix 监控mysql 的常规操作(增删改查)
#增
Com_insert)
mysqladmin extended-status|grep -w "Com_insert"|awk '{print $4}'
;;
#删除
Com_delete)
mysqladmin extended-status|grep -w "Com_delete"|awk '{print $4}'
;;
#修改
Com_update)
mysqladmin extended-status|grep -w "Com_update"|awk '{print $4}'
;;
#查
Com_select)
mysqladmin extended-status|grep -w "Com_select"|awk '{print $4}'
;;
验证:
// zabbix_get -s 192.168.1.8 -k mysql_check[Com_insert]
11486
// zabbix_get -s 192.168.1.8 -k mysql_check[Com_delete]
117
// zabbix_get -s 192.168.1.8 -k mysql_check[Com_update]
270
// zabbix_get -s 192.168.1.8 -k mysql_check[Com_select]
3326
zabbbix 监控myql 的性能指标(qps和tps)
注意:
添加监控项时,选择信息类型为:浮点数!!!
QPS(Questions Per second:)每秒能处理多少次请求数
计算公式:questions/uptime=qps
qps)
mysqladmin status|awk '{print $6/$2}'
;;
TPS(Transactions Per Second)每秒查询处理的事务数
最大的特点:要么成功都成功,要么失败都失败!!
计算公式:(commit+rollback)/uptime=tps
tps)
rollback=$(mysqladmin extended-status|grep -w "Com_rollback"|awk '{print $4}')
commit=$(mysqladmin extended-status|grep -w "Com_commit"|awk '{print $4}')
uptime=$(mysqladmin status|awk '{print $2}')
count=$[$rollback+$commit]
echo "$count $uptime" > /tmp/re.txt
cat /tmp/re.txt|awk '{print $1/$2}'
zabbix 监控myql的某个库的库大小和表大小
库名为mysql的库大小
db_size)
$mysql -e "select sum(data_length) from information_schema.tables where table_schema='mysql';"|sed '1d'
;;
表名为userde 表大小
tb_size)
$mysql -e "select sum(data_length) from information_schema.tables where table_schema='mysql' and table_name='user';"|sed '1d'
;;