mysql安装 环境：Ubuntu 18.04.1 准备包：mysql-5.6.42-linux-glibc2.12-x86_64.tar.gz my.cnf [mysqld] socket=/data/mysql/mysql.sock user=mysql symbolic-links=0 datadir=/data/mysql innodb_file_per_table=1 max_connections=10000 [client] port=3306 socket=/var/lib/mysql/mysql.sock [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/tmp/mysql.sock 一键安装脚本 #!/bin/bash DIR=`pwd` NAME="mysql-5.6.42-linux-glibc2.12-x86_64.tar.gz" FULL_NAME=${DIR}/${NAME} DATA_DIR="/data/mysql" #yum install vim gcc gcc-c++ wget autoconf net-tools lrzsz iotop lsof iotop bash-completion -y #yum install curl policycoreutils openssh-server openssh-clients postfix -y apt-get install libaio* -y if [ -f ${FULL_NAME} ];then echo "安装文件存在" else echo "安装文件不存在" exit 3 fi if [ -h /usr/local/mysql ];then echo "Mysql 已经安装" exit 3 else tar xvf ${FULL_NAME} -C /usr/local/src ln -sv /usr/local/src/mysql-5.6.42-linux-glibc2.12-x86_64 /usr/local/mysql if id mysql;then echo "mysql 用户已经存在，跳过创建用户过程" fi useradd mysql -s /sbin/nologin if id mysql;then chown -R mysql.mysql /usr/local/mysql/* -R if [ ! -d /data/mysql ];then mkdir -pv /data/mysql /var/lib/mysql && chown -R mysql.mysql /data -R /usr/local/mysql/scripts/mysql_install_db --user=mysql --datadir=/data/mysql --basedir=/usr/local/mysql/ cp /usr/local/src/mysql-5.6.42-linux-glibc2.12-x86_64/support-files/mysql.server /etc/init.d/mysqld chmod a+x /etc/init.d/mysqld cp ${DIR}/my.cnf /etc/my.cnf ln -sv /usr/local/mysql/bin/mysql /usr/bin/mysql ln -sv /data/mysql/mysql.sock /var/lib/mysql/mysql.sock /etc/init.d/mysqld start else echo "MySQL数据目录已经存在," exit 3 fi fi fi mysql主从配置 [192.168.47.106] vim /etc/my.cnf ------------------------------------- server_id=106 log-bin=/data/mysql_log_bin ------------------------------------- #授权 mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'rsync-user'@'192.168.47.%' IDENTIFIED BY '123456'; mysql> SHOW MASTER STATUS; +----------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +----------------------+----------+--------------+------------------+-------------------+ | mysql_log_bin.000001 | 467 | | | | +----------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) #备份 /usr/local/mysql/bin/mysqldump --all-databases --single_transaction --flush-logs --master-data=2 --lock-tables > /data/backup.sql scp /data/backup.sql 192.168.47.105:/data/ [192.168.47.105] vim /etc/my.cnf ------------------------------------- server_id=105 ------------------------------------- #导入 root@ubuntu:/data# mysql CHANGE MASTER TO MASTER_HOST='192.168.47.106',MASTER_USER='rsync-user',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql_log_bin.000001',MASTER_LOG_POS=467; Query OK, 0 rows affected, 2 warnings (0.11 sec) mysql> START SLAVE; Query OK, 0 rows affected (0.01 sec) mysql> show slave status\G; 第三方监控 #下载及安装 wget https://www.percona.com/downloads/percona-monitoring-plugins/1.1.7/binary/debian/xenial/x86_64/percona-zabbix-templates_1.1.7-1.xenial_all.deb dpkg -i percona-zabbix-templates_1.1.7-1.xenial_all.deb #查看 root@ubuntu:/usr/local/src# tree /var/lib/zabbix/percona/ /var/lib/zabbix/percona/ ├── scripts │ ├── get_mysql_stats_wrapper.sh │ └── ss_get_mysql_stats.php └── templates ├── userparameter_percona_mysql.conf └── zabbix_agent_template_percona_mysql_server_ht_2.0.9-sver1.1.7.xml 这个模板zabbix_agent_template_percona_mysql_server_ht_2.0.9-sver1.1.7.xml直接导入有问题 #参考解决办法： 将zabbix_agent_template_percona_mysql_server_ht_2.0.9-sver1.1.7.xml导入zabbix2.4版本中 再导出。之后将新的导出xml导入到3.2中问题解决 #安装教程 https://www.percona.com/doc/percona-monitoring-plugins/LATEST/zabbix/index.html#installation-instructions cp /var/lib/zabbix/percona/templates/userparameter_percona_mysql.conf /etc/zabbix/zabbix_agentd.conf.d/userparameter_percona_mysql.conf systemctl restart zabbix-agent 修改mysql密码 set password for root@localhost = password('123456'); set password for root@localhost = password(''); vim /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php.cnf ------------------------------------------------- <?php $mysql_user = 'root'; $mysql_pass = ''; ------------------------------------------------- 测试 /var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh gg 该脚本需要Php的环境 apt-get install php php-mysql /apps/zabbix_agent/bin/zabbix_get -s 192.168.47.105 -p 10050 -k "MySQL.Key-read-requests" /apps/zabbix_agent/bin/zabbix_get -s 192.168.47.105 -p 10050 -k "MySQL.Threads-connected" 获取不到监控值--待解决 如果测试能获取到值，后续就是web界面导入模板，主机添加模板，然后查看是否监控有数据 自定义监控 监控脚本：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 脚本测试： root@ubuntu:/etc/zabbix/zabbix_agentd.conf.d# bash mysql_monitor.sh master_slave_check 50 配置： root@ubuntu:/etc/zabbix/zabbix_agentd.conf.d# pwd /etc/zabbix/zabbix_agentd.conf.d root@ubuntu:/etc/zabbix/zabbix_agentd.conf.d# cat mysql.conf --------------------------------------------------------------------------------------- UserParameter=mysql[*],/etc/zabbix/zabbix_agentd.conf.d/mysql_monitor.sh $1 --------------------------------------------------------------------------------------- #测试 [192.168.47.104] root@ubuntu:~# /apps/zabbix_agent/bin/zabbix_get -s 192.168.47.105 -p 10050 -k "mysql["master_slave_check"]" 50 [192.168.47.102] root@ubuntu:~# /usr/local/zabbix/bin/zabbix_get -s 192.168.47.105 -p 10050 -k "mysql["master_slave_check"]" 50 模板制定 参考：https://www.cnblogs.com/zeq912/p/10593647.html
zabbix-mysql监控
