MySQL监控(小节11)

---

highlight: agate

theme: juejin

监控mysql

ubuntu服务器：要单独安装php5.6环境、默认7.2太新了、数据类型和模板不兼容

104主

配置主从 ``` vim /etc/mysql/mysql.conf.d/mysqld.cnf server-id=10 log-bin=/var/lib/mysql/master-log

重启服务

systemctl restart mysql ```

105从 ``` apt install -y mysql-server-5.7 mysql-client-5.7

vim /etc/mysql/mysql.conf.d/mysqld.cnf server-id=105

重启服务

systemctl restart mysql systemctl enable mysql ``` 104主

导出所有数据 mysqldump --all-databases --single_transaction --flush-logs --master-data=2 --lock-tables > /opt/backup.sql 拷贝到105的slave scp /opt/backup.sql 192.168.37.105:/opt

105从

导入数据 mysql < /opt/backup.sql 104主

创建同步账号 mysql GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'rsync-user'@'192.168.37.%' IDENTIFIED BY '123456'; flush privileges; exit 105从

看文件前30行 head -n 30 /opt/backup.sql ... -- CHANGE MASTER TO MASTER_LOG_FILE='master-log.000002', MASTER_LOG_POS=154;

``` mysql

CHANGE MASTER TO MASTERHOST='192.168.37.104',MASTERUSER='rsync-user',MASTERPASSWORD='123456',MASTERLOGFILE='master-log.000002',MASTERLOG_POS=154;

start slave;

看SlaveIO和SlaveSQL线程是否都为YES

show slave status\G; ******** 1. row ******** SlaveIOState: Waiting for master to send event MasterHost: 192.168.37.104 MasterUser: rsync-user MasterPort: 3306 ConnectRetry: 60 MasterLogFile: master-log.000002 ReadMasterLogPos: 3438522 RelayLogFile: ubuntu-5-relay-bin.000002 RelayLogPos: 3438689 RelayMasterLogFile: master-log.000002 SlaveIORunning: Yes SlaveSQLRunning: Yes ReplicateDoDB: ReplicateIgnoreDB: ReplicateDoTable: ReplicateIgnoreTable: ReplicateWildDoTable: ReplicateWildIgnoreTable: LastErrno: 0 LastError: SkipCounter: 0 ExecMasterLogPos: 3438522 RelayLogSpace: 3438899 UntilCondition: None UntilLogFile: UntilLogPos: 0 MasterSSLAllowed: No MasterSSLCAFile: MasterSSLCAPath: MasterSSLCert: MasterSSLCipher: MasterSSLKey: SecondsBehindMaster: 0 <--同步偏差值、越短越好 MasterSSLVerifyServerCert: No LastIOErrno: 0 LastIOError: LastSQLErrno: 0 LastSQLError: ReplicateIgnoreServerIds: MasterServerId: 10 MasterUUID: e7b20aff-968f-11ed-ac7a-000c293f405e MasterInfoFile: /var/lib/mysql/master.info SQLDelay: 0 SQLRemainingDelay: NULL SlaveSQLRunningState: Slave has read all relay log; waiting for more updates MasterRetryCount: 86400 MasterBind: LastIOErrorTimestamp: LastSQLErrorTimestamp: MasterSSLCrl: MasterSSLCrlpath: RetrievedGtidSet: ExecutedGtidSet: AutoPosition: 0 ReplicateRewriteDB: ChannelName: MasterTLSVersion: 1 row in set (0.00 sec)

ERROR: No query specified

exit ```

安装procona监控

下载包 wget https://downloads.percona.com/downloads/percona-monitoring-plugins/percona-monitoring-plugins-1.1.8/binary/debian/artful/x86_64/percona-zabbix-templates_1.1.8-1.artful_all.deb 安装 dpkg -i percona-zabbix-templates_1.1.8-1.artful_all.deb 安装依赖包

wget http://repo.zabbix.com/zabbix/4.0/ubuntu/pool/main/z/zabbix-release/zabbix-release_4.0-3%2Bbionic_all.deb dpkg -i zabbix-release_4.0-3+bionic_all.deb apt update && apt install zabbix-agent -y 版本太新需要用php5.6 ``` apt-get install -y software-properties-common sudo add-apt-repository ppa:ondrej/php

apt update apt install php5.6-mysql -y apt-get install -y php5.6 ```

107 ``` grep "^[a-Z]" /etc/zabbix/zabbixagentd.conf PidFile=/var/run/zabbix/zabbixagentd.pid LogFile=/var/log/zabbix/zabbixagentd.log LogFileSize=0 Server=192.168.37.101,192.168.37.103 ListenPort=10050 ListenIP=0.0.0.0 StartAgents=3 ServerActive=192.168.37.102 Hostname=192.168.37.107 Include=/etc/zabbix/zabbixagentd.d/*.conf

拷贝到105

scp /etc/zabbix/zabbixagentd.conf 192.168.37.105:/etc/zabbix/zabbixagentd.conf ```

105从 vim /etc/zabbix/zabbix_agentd.conf Server=192.168.37.101,192.168.37.102 <-- ServerActive=192.168.37.102 <-- Hostname=192.168.37.105 <--

systemctl enable zabbix-agent systemctl restart zabbix-agent 克隆

cp /var/lib/zabbix/percona/templates/userparameter_percona_mysql.conf /etc/zabbix/zabbix_agentd.d/

``` vim /var/lib/zabbix/percona/scripts/ssgetmysql_stats.php.cnf

user = 'root'; $mysqlpass = ''; ```

/var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh gg 43 <--

chown zabbix.zabbix /tmp/localhost-mysql_cacti_stats.txt

systemctl restart zabbix-agent

101 ``` systemctl restart zabbix-agent

zabbix_get -s 192.168.37.105 -p 10050 -k "MySQL.Key-read-requests" 43 ``` 105从

导出模板 ``` cd /var/lib/zabbix/percona/templates/

sz zabbixagenttemplateperconamysqlserverht_2.0.9-sver1.1.8.xml ``` 导入模板

点击下载监控模板

105

``` cd /var/lib/zabbix/percona/scripts/

时间改短

vim getmysqlstats_wrapper.sh

DIR=dirname $0 CMD="/usr/bin/php -q $DIR/ssgetmysqlstats.php --host $HOST --items gg" CACHEFILE="/tmp/$HOST-mysqlcacti_stats.txt"

if [ "$ITEM" = "running-slave" ]; then # Check for running slave RES=HOME=~zabbix mysql -e 'SHOW SLAVE STATUS\G' | egrep '(Slave_IO_Running|Slave_SQL_Running):' | awk -F: '{print $2}' | tr '\n' ',' if [ "$RES" = " Yes, Yes," ]; then echo 1 else echo 0 fi exit elif [ -e $CACHEFILE ]; then # Check and run the script TIMEFLM=stat -c %Y /tmp/$HOST-mysql_cacti_stats.txt TIMENOW=date +%s if [ expr $TIMENOW - $TIMEFLM -gt 5 ]; then <--时间改短 sudo rm -f $CACHEFILE $CMD 2>&1 > /dev/null fi else $CMD 2>&1 > /dev/null fi

Parse cache file

if [ -e $CACHEFILE ]; then cat $CACHEFILE | sed 's/ /\n/g; s/-1/0/g'| grep $ITEM | awk -F: '{print $2}' else echo "ERROR: run the command manually to investigate the problem: $CMD" fi ```

关联

105

更改权限 ```

注释掉下面两项后、会root启动zabbix-agent

vim /lib/systemd/system/zabbix-agent.service

User=zabbix

Group=zabbix

vim /etc/sudoers zabbix ALL=(ALL) NOPASSWD: ALL <--zabbix可以执行特殊命令

vim /etc/zabbix/zabbix_agentd.conf Timeout=30 <--超时时间 AllowRoot=1 <-- User=root <--

重启服务

systemctl daemon-reload systemctl restart zabbix-agent

可以看到是root启动zabbix服务

ps -ef|grep zabbix root 26259 1 0 12:09 ? 00:00:00 /usr/sbin/zabbixagentd -c /etc/zabbix/zabbixagentd.conf root 26261 26259 0 12:09 ? 00:00:00 /usr/sbin/zabbixagentd: collector [idle 1 sec] root 26262 26259 0 12:09 ? 00:00:00 /usr/sbin/zabbixagentd: listener #1 [waiting for connection] root 26263 26259 0 12:09 ? 00:00:00 /usr/sbin/zabbixagentd: listener #2 [waiting for connection] root 26264 26259 0 12:09 ? 00:00:00 /usr/sbin/zabbixagentd: listener #3 [waiting for connection] root 26265 26259 0 12:09 ? 00:00:00 /usr/sbin/zabbix_agentd: active checks #1 [idle 1 sec] root 26370 1648 0 12:10 pts/0 00:00:00 grep --color=auto zabbix

cd /var/lib/zabbix/percona/scripts bash -x getmysqlstats_wrapper.sh gg ```

101

zabbix_get -s 192.168.37.105 -p 10050 -k "MySQL.Key-read-requests" 29 <--

105

相当于进入mysql后、执行show slave status\G;后、过滤出来SecondsBehindMaster的值 mysql -uroot -hlocalhost -e "show slave status\G;" |grep "Seconds_Behind_Master:" |awk -F: '{print $2}' 0

``` cd /etc/zabbix/zabbix_agentd.d/

脚本

vim mysql_monitor.sh

!/bin/bash

SecondsBehindMaster(){ NUM=mysql -uroot -hlocalhost -e "show slave status\G;" | grep "Seconds_Behind_Master:" | awk -F: '{print $2}' echo $NUM }

masterslavecheck(){ NUM1=mysql -uroot -hlocalhost -e "show slave status\G;" | grep "Slave_IO_Running" | awk -F: '{print $2}' | sed 's/^[ \t]*//g'

echo $NUM1

NUM2=mysql -uroot -hlocalhost -e "show slave status\G;" | grep "Slave_SQL_Running:" | awk -F: '{print $2}' | sed 's/^[ \t]*//g'

echo $NUM2

if test $NUM1 == "Yes" && test $NUM2 == "Yes";then echo 50 else echo 100 fi }

main(){ case $1 in SecondsBehindMaster) SecondsBehindMaster; ;; masterslavecheck) masterslavecheck ;; esac } main $1

添加权限

chmod a+x mysql_monitor.sh

编辑配置文件

vim /etc/zabbix/zabbixagentd.conf UserParameter=mysqlmonitor[*],/etc/zabbix/zabbixagentd.d/mysqlmonitor.sh "$1"

重启服务

systemctl restart zabbix-agent ```

创建图形

创建触发器

关联主机

105

mysql stop slave; <--值会升到100、看下图、如果再次启动start slave;会恢复

值升到100