[root@nginx-mysqldb home]# yum install mariadb mariadb-server -y
啓動mariadb,並且設定開機啓動
systemctl start mariadb
systemctl enable mariadb
檢查進程是否啓動
[root@nginx-mysqldb yum.repos.d]# ps aux|grep mysql檢視mysql的進程
mysql 22539 0.7 2.3 1756668 88704 ? Ssl 10:48 0:00 /usr/libexec/mysqld --basedir=/usr
root 22611 0.0 0.0 12320 1076 pts/0 R+ 10:49 0:00 grep --color=auto mysql
[root@nginx-mysqldb yum.repos.d]#
[root@nginx-mysqldb yum.repos.d]# netstat -anplut|grep mysql
tcp6 0 0 :::3306 :::* LISTEN 22539/mysqld
[root@nginx-mysqldb yum.repos.d]# lsof -i:3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 22539 mysql 21u IPv6 236875 0t0 TCP *:mysql (LISTEN)
[root@nginx-mysqldb yum.repos.d]#
檢查下selinu和防火牆是否關閉
[root@nginx-mysqldb yum.repos.d]# iptables -L
Chain INPUT (policy ACCEPT)
target prot opt source destination
Chain FORWARD (policy ACCEPT)
target prot opt source destination
Chain OUTPUT (policy ACCEPT)
target prot opt source destination
[root@nginx-mysqldb yum.repos.d]# getenforce
Permissive
[root@nginx-mysqldb yum.repos.d]#
[root@zabbix-client-1 zabbix_agentd.d]# cat userparameter_mysql.conf
UserParameter=mysql.status[*],/etc/zabbix/zabbix_agentd.d/mysql_status.sh $1
UserParameter=mysql.ping,/usr/bin/mysqladmin -ucali -p'cali123456' -h localhost ping 2>/dev/null | grep -c alive
UserParameter=mysql.version,/usr/bin/mysql -V
UserParameter=mysql.bytes_sent,/etc/zabbix/zabbix_agentd.d/mysql_status.sh $1
一行一個對應的參數，可以新增很多行，也就是很多參數，方便zabbix-server和zabbix-agent之間溝通
UserParameter=mysql.bytes_sent 是zabbix-server後面採集數據傳遞的參數 mysql.bytes_sent
/etc/zabbix/zabbix_agentd.d/mysql_status.sh $1 是接受到mysql.bytes_sent後，zabbix-agent需要執行的命令
在伺服器端使用zabbix-get採集數據的參數如下：
[root@zabbix-server-1 ~]# zabbix_get -s 192.168.1.141 -k mysql.status[Uptime]
44373
[root@zabbix-server-1 ~]#
mysql的採集數據的指令碼
[root@zabbix-client-1 zabbix_agentd.d]# cat mysql_status.sh
#!/bin/bash
#Desc：zabbix 監控 MySQL 狀態
#Date：2020-3-19
#by：cali
#主機
HOST="localhost"
#使用者
USER="cali"
#密碼
PASSWORD="cali123456"
#埠
PORT="3306"
#MySQL連線
CONNECTION="mysqladmin -h ${HOST} -u ${USER} -P ${PORT} -p${PASSWORD}"
if [ $# -ne "1" ];then
echo "arg error!"
fi
case $1 in
Uptime)
result=`${CONNECTION} status 2>/dev/null |awk '{print $2}'`
echo $result
;;
Questions)
result=`${CONNECTION} status 2>/dev/null |awk '{print $6}'`
echo $result
;;
Com_update)
result=`${CONNECTION} extended-status 2>/dev/null |grep -w "Com_update" |awk '{print $4}'`
echo $result
;;
Slow_queries)
result=`${CONNECTION} extended-status 2>/dev/null |grep -w "Slow_queries" |awk '{print $4}'`
echo $result
;;
Com_select)
result=`${CONNECTION} extended-status 2>/dev/null |grep -w "Com_select" |awk '{print $4}'`
echo $result
;;
Com_rollback)
result=`${CONNECTION} extended-status 2>/dev/null |grep -w "Com_rollback" |awk '{print $4}'`
echo $result
;;
Com_insert)
result=`${CONNECTION} extended-status 2>/dev/null |grep -w "Com_insert" |awk '{print $4}'`
echo $result
;;
Com_delete)
result=`${CONNECTION} extended-status 2>/dev/null |grep -w "Com_delete" |awk '{print $4}'`
echo $result
;;
Com_commit)
result=`${CONNECTION} extended-status 2>/dev/null |grep -w "Com_commit" |awk '{print $4}'`
echo $result
;;
Bytes_sent)
result=`${CONNECTION} extended-status 2>/dev/null |grep -w "Bytes_sent" |awk '{print $4}'`
echo $result
;;
Bytes_received)
result=`${CONNECTION} extended-status 2>/dev/null |grep -w "Bytes_received" |awk '{print $4}'`
echo $result
;;
Com_begin)
result=`${CONNECTION} extended-status 2>/dev/null |grep -w "Com_begin" |awk '{print $4}'`
echo $result
;;
Threads_connected)
result=`${CONNECTION} extended-status 2>/dev/null |grep -w "Threads_connected" |awk '{print $4}'`
echo $result
;;
*)
echo "Usage:$0(Uptime|Questions|Com_update|Slow_queries|Com_select|Com_rollback|Com_insert|Com_delete|Com_commit|Bytes_sent|Bytes_received|Com_begin|Threads_connected)"
;;
esac
[root@zabbix-client-1 zabbix_agentd.d]#
授予可執行許可權
[root@nginx-mysqldb zabbix_agentd.d]# chmod +x mysql_status.sh
[root@nginx-mysqldb ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.17-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> grant all on *.* to 'cali'@'%' identified by 'cali123456';
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]>
all 代表所有的許可權 --》增刪改查都可以
. 表示可以存取任何的庫，任何庫裡的表
‘cali’@’%’ 使用者名稱 %代表可以從任何地方連線過來
identified by ‘cali123456’ 設定密碼爲cali123456
採集數據的核心命令 mysqladmin
[root@nginx-mysqldb ~]# mysqladmin -h localhost -ucali -p'cali123456' -P 3306 version
mysqladmin Ver 9.1 Distrib 10.3.17-MariaDB, for Linux on x86_64
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Server version 10.3.17-MariaDB
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 27 min 50 sec
Threads: 8 Questions: 42 Slow queries: 0 Opens: 37 Flush tables: 1 Open tables: 31 Queries per second avg: 0.025
[root@nginx-mysqldb ~]#
重新整理zabbix-agent服務，因爲修改了組態檔
[root@nginx-mysqldb ~]# service zabbix-agent restart
Redirecting to /bin/systemctl restart zabbix-agent.service
[root@nginx-mysqldb ~]#
在zabbix-server這邊測試，是否可以採集數據
[root@zabbix-server-1 ~]# zabbix_get -s 192.168.111.133 -k mysql.status[Uptime]
2754
[root@zabbix-server-1 ~]#
在zabbix-server伺服器上的web介面新增監控主機和相關的監控項
自己建立應用集mysql-monitor，新增監控項，然後新增圖形，出圖
1.mysql是否正常啓動
2.mysql接受的位元組和發送的位元組數
Bytes_sent
Bytes_received
3.有多少使用者登錄到mysql裡
mysql.status[Threads_connected]
4.瞭解mysql查詢的次數
Com_select
自己增加一個監控專案的流程：
1.在zabbix-agent上的userparameter_mysql.conf 裡新增參數
UserParameter=mysql.processlist,/usr/bin/mysqladmin -h localhost -ucali -p’cali123456’ processlist|awk ‘{print $4}’|uniq |sort -rn|tail -n +2|wc -w
2.重新整理zabbix-agent服務
3.zabbix-server上新增監控項–》出圖
真正的難點就是在zabbix-agent上去採集數據的指令碼或者命令–》可以使用shell指令碼或者python指令碼