zabbix mysql mariadb多實例監控

來源:本站原創 mysql_mariadb 超過1,216 views圍觀 0條評論

http://blog.csdn.net/qq942477618/article/details/60960865

1、定義每個實例的端口

cat mysql_port
3325
3346
 

2、給所有實例創建一個zabbix用戶只允許本地訪問,生產環境具體權限各位看官自己看著給吧O(∩_∩)O

登陸:
@shtsmysql02 script]#  mysql -u root -p -S /tmp/mysql3346.sock

MySQL [(none)]> GRANT ALL PRIVILEGES ON *.* TO [email protected]’127.0.0.1′ IDENTIFIED BY ‘zabbix’;
Query OK, 0 rows affected (0.01 sec)

MySQL [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

 

3、創建自動發現MySQL端口腳本
cat /opt/zabbixscript/mysqlzabbix/discovery_mysql.sh

#!/bin/bash
# ——————————————————————————-
# Script_name: discovery_mysql.sh
# Vision:    1.0
# ——————————————————————————-

res=`cat /opt/zabbixscript/mysqlzabbix/mysqlport|grep -v "^#"`
port=($res)
printf ‘{\n’
printf ‘\t"data":[\n’
for key in ${!port[@]}
do
    if [[ "${#port[@]}" -gt 1 && "${key}" -ne "$((${#port[@]}-1))" ]];then
        printf ‘\t {\n’
        printf "\t\t\t\"{#MYSQLPORT}\":\"${port[${key}]}\"},\n"
    else [[ "${key}" -eq "((${#port[@]}-1))" ]]
        printf ‘\t {\n’
        printf "\t\t\t\"{#MYSQLPORT}\":\"${port[${key}]}\"}\n"
    fi
done
printf ‘\t ]\n’
printf ‘}\n’

4、創建MySQL檢測腳本

# cat /data/PRG/zabbix/scripts/check_Multimysql.sh
#!/bin/bash
# ——————————————————————————-
# FileName:    check_Multimysql.sh
# Vision:    1.0
# ——————————————————————————-
 
# 用戶名
MYSQL_USER=’zabbix’
 
# 密碼
MYSQL_PWD=’zabbix’
 
# 主機地址/IP
MYSQL_HOST=’127.0.0.1′
 
# 端口
MYSQL_PORT=$2
 
# 數據連接
MYSQL_CONN="/usr/bin/mysqladmin -u${MYSQL_USER} -p${MYSQL_PWD} -h${MYSQL_HOST} -P${MYSQL_PORT}"

##help函數
help() {
        echo "Usage:$0  [ping|Uptime|Com_update|Slow_queries|Com_select|Com_rollback|Questions|Com_insert|Com_delete|Com_commit|Bytes_sent|Bytes_received|Com_begin]  port"
}

# 參數是否正確
if [ $# -lt "2" ];then
    echo "參數缺失!"
    help
    exit 2
fi
 
# 獲取數據 腳本是錯的需要修證
case $1 in
    ping)
        result=`${MYSQL_CONN} ping | grep -c alive`
        echo $result
        ;;
    Uptime)
        result=`${MYSQL_CONN} status|cut -f2 -d":"|cut -f1 -d"T"`
        echo $result
        ;;
    Com_update)
        result=`${MYSQL_CONN} extended-status |grep -w "Com_update"|cut -d"|" -f3`
        echo $result
        ;;
    Slow_queries)
        result=`${MYSQL_CONN} status |cut -f5 -d":"|cut -f1 -d"O"`
        echo $result
        ;;
    Com_select)
        result=`${MYSQL_CONN} extended-status |grep -w "Com_select"|cut -d"|" -f3`
        echo $result
                ;;
    Com_rollback)
        result=`${MYSQL_CONN} extended-status |grep -w "Com_rollback"|cut -d"|" -f3`
                echo $result
                ;;
    Questions)
        result=`${MYSQL_CONN} status|cut -f4 -d":"|cut -f1 -d"S"`
                echo $result
                ;;
    Com_insert)
        result=`${MYSQL_CONN} extended-status |grep -w "Com_insert"|cut -d"|" -f3`
                echo $result
                ;;
    Com_delete)
        result=`${MYSQL_CONN} extended-status |grep -w "Com_delete"|cut -d"|" -f3`
                echo $result
                ;;
    Com_commit)
        result=`${MYSQL_CONN} extended-status |grep -w "Com_commit"|cut -d"|" -f3`
                echo $result
                ;;
    Bytes_sent)
        result=`${MYSQL_CONN} extended-status |grep -w "Bytes_sent" |cut -d"|" -f3`
                echo $result
                ;;
    Bytes_received)
        result=`${MYSQL_CONN} extended-status |grep -w "Bytes_received" |cut -d"|" -f3`
                echo $result
                ;;
    Com_begin)
        result=`${MYSQL_CONN} extended-status |grep -w "Com_begin"|cut -d"|" -f3`
                echo $result
                ;;
                       
        *)
        help
        ;;
esac
——————驗證用戶———————
MariaDB [mysql]> select user,host from user where user= "zabbix"
    -> ;
+——–+———–+
| user   | host      |
+——–+———–+
| zabbix | 127.0.0.1 |
+——–+———–+
1 row in set (0.00 sec)

MariaDB [mysql]>
—————-驗證腳本——————–
[[email protected] ~]# zabbix_get -s 192.168.9.62 -k mysqlmulti.ping[3325]
1

————————–多實例多版本修改—————————
以上腳本多版本多實例服務不好用需要進行修改
連接串按這列格式進行修改
[[email protected] mysqlzabbix]# mysql -uzabbix -pzabbix -h127.0.0.1 -P3346 -S /tmp/mysql3346.sock -e status
————–
mysql  Ver 15.1 Distrib 5.5.56-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:        310364
Current database:   
Current user:        [email protected]
SSL:            Not in use
Current pager:        stdout

extended-status: 顯示mysqld的所有服務器變量和他們的當前值
mariadb不支持 extended-status 所以腳本也要改
[[email protected] mysqlzabbix]# mysql -uzabbix -pzabbix -h127.0.0.1 -P3346 -S /tmp/mysql3346.sock -e extended-status
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘extended-status’ at line 1

修改成如下命令
[[email protected] mysqlzabbix]# mysqladmin -uzabbix -pzabbix -h127.0.0.1 -P3346 -S /tmp/mysql3346.sock  extended-status
+———————————————–+———————+
| Variable_name                                 | Value               |
+———————————————–+———————+
| Aborted_clients                               | 10                  |

按測試結果來看。mysql 可以替換成mysqladmin命令
[[email protected] mysqlzabbix]# mysqladmin -uzabbix -pzabbix -h127.0.0.1 -P3346 -S /tmp/mysql3346.sock  status
Uptime: 1863765  Threads: 5  Questions: 4422  Slow queries: 0  Opens: 167  Flush tables: 1  Open tables: 115  Queries per second avg: 0.002
[[email protected] mysqlzabbix]#
————————–多實例多版本修改—————————
 
5、測試腳本是否正常
mysqlzabbix/discovery_mysql.sh
{
    "data":[
     {
            "{#MYSQLPORT}":"3325"},
     {
            "{#MYSQLPORT}":"3346"}
     ]
}

 

check_Mmysql.sh腳本測試結果
[[email protected] ]# sh  mysql /check_Mmysql.sh Bytes_sent 3346
1726305
[[email protected] ]# sh  mysql /check_Mmysql.sh Bytes_received 3346
370292
[[email protected] ]# sh  mysql /check_Mmysql.sh Com_begin 3346
 

6、zabbix agent配置文件添加自定義腳本,并重啟zabbix agent

vim /data/PRG/zabbix/etc/zabbix_agentd.conf

UserParameter=mysql_discovery[*],/bin/bash /opt/zabbixscript/mysqlzabbix/discovery_mysql.sh
#####$1==command   $2===port
UserParameter=mysql.status[*],/opt/zabbixscript/mysqlzabbix/check_Mmysql.sh $1 $2
UserParameter=mysql.ping[*],/opt/zabbixscript/mysqlzabbix/check_Mmysql.sh ping $1
 

/etc/init.d/zabbix_agentd restart

 

7、使用zabbix_get檢查定義的key是否生效

# /usr/local/zabbix/bin/zabbix_get -s 192.168.62.200 -k mysql.ping[3307]  
1
# /usr/local/zabbix/bin/zabbix_get -s 192.168.62.200 -k mysql.status[Uptime,3307]
18180813
# /usr/local/zabbix/bin/zabbix_get -s 192.168.62.200 -k mysql.status[Uptime,3306]
18180842
# /usr/local/zabbix/bin/zabbix_get -s 192.168.62.200 -k mysql.status[Uptime,3310]
18180847
# /usr/local/zabbix/bin/zabbix_get -s 192.168.62.200 -k mysql.status[Bytes_sent,3310]
58803854473
 

8、導入模板Template Multi Mysql.xml 

左右這個x下載保存成xml

9、被監控主機關聯模板

10、完成

文章出自:CCIE那點事 http://www.qdxgqk.live/ 版權所有。本站文章除注明出處外,皆為作者原創文章,可自由引用,但請注明來源。 禁止全文轉載。
本文鏈接:http://www.qdxgqk.live/?p=3644轉載請注明轉自CCIE那點事
如果喜歡:點此訂閱本站
?
?
萌宠夺宝游戏