一、部署mysql，實(shí)現(xiàn)主從同步

1、解壓文件包并安裝mysql（使用打包好的數(shù)據(jù)包一鍵安裝）

tar -xvf mysql-5.6.34-onekey-install.tar.gz
./mysql-install.sh

2、配置master mysql
（1）修改配置文件

vim /etc/my.cnf

[mysqld]
server-id=101
log-bin=/data/mysql/master-log

（2）重啟程序并配置用戶

/etc/init.d/mysqld restart

GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'mysql-monitor'@'192.168.43.%' IDENTIFIED BY '123456';

create database linux43;

3、配置slave mysql
（1）修改配置文件

vim /etc/my.cnf

[mysqld]
server-id=102
log-bin=/data/mysql/master-log

（2）重啟程序

/etc/init.d/mysqld restart

4、配置主從
（1）導(dǎo)出master的數(shù)據(jù)

/usr/local/mysql/bin/mysqldump --all-databases --single_transaction --flush-logs --master-data=2 --lock-tables > /opt/backup.sql

（2）復(fù)制到slave，并修改postion位置

mysql < /backup.sql


CHANGE MASTER TO MASTER_HOST='192.168.43.101',MASTER_USER='mysql-monitor',MASTER_PASSWORD='123456',MASTER_LOG_FILE='master-log.000002',MASTER_LOG_POS=120;

start slave;

二、mysql監(jiān)控

1、安裝procona
插件地址
下載地址
安裝教程
（1）安裝模板和php

yum install php php-mysql -y

rpm -ivh https://www.percona.com/downloads/percona-monitoring-plugins/percona-monitoring-plugins-1.1.8/binary/redhat/7/x86_64/percona-zabbix-templates-1.1.8-1.noarch.rpm

（2）修改zabbix-agent配置文件

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

（3）重啟程序

systemctl restart zabbix-agent.service

（4）配置鏈接數(shù)據(jù)庫(kù)的

vim /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php.cnf

<?php
$mysql_user= 'root';
$mysql_pass= '';

測(cè)試

[root@centos7 ~]# /var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh gg
6913

注意：本機(jī)測(cè)試的時(shí)候/tmp目錄下回出現(xiàn)一個(gè)文件，下面的文件所屬主是root，測(cè)試完記得刪除掉，要不然會(huì)有權(quán)限問(wèn)題

[root@centos7 ~]# ll /tmp/localhost-mysql_cacti_stats.txt 
-rw-r--r-- 1 root root 1230 Dec  3 16:36 /tmp/localhost-mysql_cacti_stats.txt

三、zabbix-web端配置

1、導(dǎo)入指定的模板（下載的指定模板有問(wèn)題，導(dǎo)入的模板為調(diào)整過(guò)的）

導(dǎo)入模板

2、修改模板監(jiān)控項(xiàng)為主動(dòng)模式，并關(guān)聯(lián)主機(jī)

四、自定義mysql監(jiān)控

由于percona監(jiān)控很多監(jiān)控項(xiàng)不明確，所以可以選擇自定義腳本監(jiān)控，主要監(jiān)控主從同步的線程和備份數(shù)據(jù)的延時(shí)，必要時(shí)可以將percona中的監(jiān)控項(xiàng)合并到自己寫(xiě)的模板中。
1、監(jiān)控腳本（監(jiān)控主從復(fù)制）

vim mysql_monitor.sh
#!/bin/bash
Seconds_Behind_Master(){
    NUM=`mysql -uroot -hlocalhost   -e "show slave status\G;"  | grep "Seconds_Behind_Master:" | awk -F: '{print $2}'`
    echo $NUM
}

master_slave_check(){
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
        Seconds_Behind_Master)
           Seconds_Behind_Master;
           ;;
    master_slave_check)
       master_slave_check
       ;;
    esac
}
main $1

2、監(jiān)控腳本（監(jiān)控?cái)?shù)據(jù)庫(kù)進(jìn)程端口，慢查詢，主從復(fù)制，數(shù)據(jù)表大小）

#!/bin/bash

Threads_connected(){
    NUM=`mysql -uzabbix -p -hlocalhost  -e "show global status;" 2>/dev/null |grep -w "Threads_connected" |cut -f2 `
    echo $NUM   
} #數(shù)據(jù)庫(kù)當(dāng)前打開(kāi)的連接數(shù)

Mysql_status(){
    NUM=`mysqladmin -uzabbix -p -hlocalhost ping 2>/dev/null |cut -f3 -d' '`
    if test $NUM == "alive" ;then
        echo 50
    else
        echo 100
    fi
} #數(shù)據(jù)庫(kù)狀態(tài)

Check_process(){
    NUM=`ps -ef |grep -wv grep |grep -wv bash |grep -wv sh  | grep ${NAME}|wc -l`
    if [ $NUM -eq 0 ];then
        echo 100
    else
        echo 50
    fi
}  #數(shù)據(jù)庫(kù)進(jìn)程

Check_port(){
    ss -tnl  | grep  ${PORT} &> /dev/null
    if [ $? -eq 0 ];then
        echo 50
    else 
        echo 100
    fi
} #數(shù)據(jù)庫(kù)端口

Slow_queries(){
    NUM=`mysql -uzabbix -p -hlocalhost -e "show global status;" 2>/dev/null |grep -w "Slow_queries" |cut -f2`
    echo $NUM
} #要花超過(guò)long_query_time時(shí)間的查詢數(shù)量

Seconds_Behind_Master(){
        NUM=`mysql -uzabbix -p -hlocalhost -e "show slave status\G;" 2>/dev/null | grep "Seconds_Behind_Master:" | awk -F: '{print $2}'`
        echo $NUM
} #主從復(fù)制中，從服務(wù)器從主服務(wù)器獲取數(shù)據(jù)延時(shí)時(shí)間，數(shù)越小越好

master_slave_check(){
    NUM1=`mysql -uzabbix -p -hlocalhost  -e "show slave status\G;" 2>/dev/null | grep "Slave_IO_Running" | awk -F:  '{print $2}' | sed 's/^[ \t]*//g'`
    #echo $NUM1
    NUM2=`mysql -uzabbix -p -hlocalhost  -e "show slave status\G;" 2>/dev/null | 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
} #主從復(fù)制的兩個(gè)進(jìn)程

Table_size(){
    NUM=`mysql -uzabbix -p -hlocalhost -e "select concat(round(sum(DATA_LENGTH/1024/1024),2)) size from information_schema.tables where TABLE_SCHEMA='$DATABASE' and TABLE_NAME ='$TABLE'" 2>/dev/null |grep -wv size`
    echo $NUM
}


main(){
    case $1 in
    Threads_connected)
    Threads_connected;
    ;;
    Mysql_status)
    Mysql_status;
    ;;
    Check_process)
    NAME=$2
    Check_process;
    ;;
    Check_port)
    PORT=$2
    Check_port;
    ;;
    Slow_queries)
    Slow_queries;
        ;;
    Seconds_Behind_Master)
        Seconds_Behind_Master;
        ;;
    master_slave_check)
        master_slave_check
        ;;
    Table_size)
    DATABASE=$2
    TABLE=$3
    Table_size;
    ;;
    esac
}
main $1 $2 $3

自定義發(fā)現(xiàn)規(guī)則（發(fā)現(xiàn)數(shù)據(jù)表）

#!/bin/bash

#DATABASE=`mysql -uzabbix -p -hlocalhost  -e "show databases;" 2>/dev/null |grep -Ev "information_schema|performance_schema|Database|mysql|sys"`
#NUM1=`mysql -uzabbix -p -hlocalhost  -e "show databases;" 2>/dev/null |grep -Ev "information_schema|performance_schema|Database|mysql|sys" |wc -l`

DATABASE=($(mysql -uzabbix -p -hlocalhost  -e "show databases;" 2>/dev/null |grep -Ev "information_schema|performance_schema|Database|mysql|sys"))
num1=$(echo $((${#DATABASE[@]}-1)))
#echo $num1
#echo ${DATABASE[@]}
printf "{\n"
printf '\t"data":[\n'
    for((i=0;i<${#DATABASE[@]};i++));do
        #TABLE=`mysql -uzabbix -p -hlocalhost  -e "use $i;show tables;" 2>/dev/null |grep -v "Tables_in"`
        #NUM2=`mysql -uzabbix -p -hlocalhost  -e "use $i;show tables;" 2>/dev/null |grep -v "Tables_in" |wc -l`
        TABLE=($(mysql -uzabbix -p -hlocalhost  -e "use ${DATABASE[$i]};show tables;" 2>/dev/null |grep -v "Tables_in"))
        #echo ${TABLE[@]}
        for((u=0;u<${#TABLE[@]};u++));do
            printf '{'
            num2=$(echo $((${#TABLE[@]}-1)))
            if [ "$u" == ${num2} ];
            then
                    printf "\"{#DATABASE}\":\"${DATABASE[$i]}\",\"{#TABLE}\":\"${TABLE[$u]}\"}\n"
            else
                    printf "\"{#DATABASE}\":\"${DATABASE[$i]}\",\"{#TABLE}\":\"${TABLE[$u]}\"},\n"
            fi
            
        done
        if [ "$i" == ${num1} ];then
            printf " "
        else
            printf ","
        fi
    done


printf "\t]\n"
printf "}\n"

zabbix參考模板