一、部署mysql，实现主从同步
1、解压文件包并安装mysql（使用打包好的数据包一键安装）
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）导出master的数据
/usr/local/mysql/bin/mysqldump --all-databases --single_transaction --flush-logs --master-data=2 --lock-tables > /opt/backup.sql
（2）复制到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监控
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）配置链接数据库的
vim /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php.cnf
<?php
$mysql_user= 'root';
$mysql_pass= '';
测试
[root@centos7 ~]# /var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh gg
6913
注意：本机测试的时候/tmp目录下回出现一个文件，下面的文件所属主是root，测试完记得删除掉，要不然会有权限问题
[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、导入指定的模板（下载的指定模板有问题，导入的模板为调整过的）
导入模板
2、修改模板监控项为主动模式，并关联主机
四、自定义mysql监控
由于percona监控很多监控项不明确，所以可以选择自定义脚本监控，主要监控主从同步的线程和备份数据的延时，必要时可以将percona中的监控项合并到自己写的模板中。
1、监控脚本（监控主从复制）
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、监控脚本（监控数据库进程端口，慢查询，主从复制，数据表大小）
#!/bin/bash
Threads_connected(){
NUM=`mysql -uzabbix -p -hlocalhost -e "show global status;" 2>/dev/null |grep -w "Threads_connected" |cut -f2 `
echo $NUM
} #数据库当前打开的连接数
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
} #数据库状态
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
} #数据库进程
Check_port(){
ss -tnl | grep ${PORT} &> /dev/null
if [ $? -eq 0 ];then
echo 50
else
echo 100
fi
} #数据库端口
Slow_queries(){
NUM=`mysql -uzabbix -p -hlocalhost -e "show global status;" 2>/dev/null |grep -w "Slow_queries" |cut -f2`
echo $NUM
} #要花超过long_query_time时间的查询数量
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
} #主从复制中，从服务器从主服务器获取数据延时时间，数越小越好
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
} #主从复制的两个进程
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
自定义发现规则（发现数据表）
#!/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"
