mysql 主从监控

mysql 主从监控

主从简介

在现代企业中，数据显得尤为重要，而存储数据的数据库选择又五花八门，但无论是何种数据库，均存在着一种隐患。

当数据规模非常大，读写量也很高时，一台数据库已经无法负担全部读写任务，就需要多台数据库同时运作分担负载。

主从作用

实时灾备，用于故障切换

读写分离，提供查询服务

备份，避免影响业务

主从形式

一主一从

主主复制

一主多从(扩展系统读取的性能，读在从库,写在主库)

多主一从(5.7开始支持)

联级复制

主从复制原理

主从复制步骤：

主库将所有的写操作记录到binlog日志中并生成一个log dump线程，将binlog日志传给从库的I/O线程

从库生成两个线程，一个I/O线程，一个SQL线程

I/O线程去请求主库的binlog，并将得到的binlog日志写到relay log(中继日志) 文件中

SQL线程，会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，达到最终数据一致的目的

传统主从弊端

互为主从时，可能会由于循环写导致数据失去一致性。

主从配置

需求：

搭建两台MySQL服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作

环境：

主数据库

CentOS7/RedHat7

IP-192.168.233.129

Hostname-Lynk

有数据

副数据库

CentOS7/RedHat7

IP-192.168.233.247

Hostname-Hyrule

无数据

主从复制配置步骤：

确保从数据库与主数据库里的数据一样

在主数据库里创建一个同步账号授权给从数据库使用

配置主数据库(修改配置文件)

配置从数据库(修改配置文件)

安装数据库

给从库授权

#以下操作在主库进行

mysql> CREATE USER 'repl'@'192.168.233.247' IDENTIFIED BY 'repl123';

Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.233.247';

Query OK, 0 rows affected (0.00 sec)

确保数据一致

#新开一个终端，对主库锁表，防止配置期间有其他人写入，锁表期间不能关闭终端或退出mysql交互式命令行

mysql> FLUSH TABLES WITH READ LOCK;

#全备主库

[root@lynk ~]# mysqldump -uroot -plynk123~ --all-databases > /opt/all-201902271419.sql

#复制备份文件到从库

[root@lynk ~]# scp /opt/all-201902271419.sql root@192.168.233.247:/opt/

#在从库恢复主库的备份

[root@Hyrule ~]# mysql -uroot -plynk123~ < /opt/all-201902271419.sql

配置主库

[root@lynk ~]# vim /etc/myf

#在[mysqld]后添加如下内容(已有的内容不要改变)

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

#启用binlog日志

log-bin=mysql-bin

#数据库服务器唯一标识符，主库的server-id值必须比从库的大

server-id=1

symbolic-links=0

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

#结束主库锁表状态，只要退出另一个终端中mysql交互式命令行就行了

mysql> quit

#重启mysql服务

[root@lynk ~]# systemctl restart mysqld

#查看主库状态

[root@lynk ~]# mysql -uroot -plynk123~ -e 'show master status;'

mysql: [Warning] Using a password on the command line interface can be insecure.

+------------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000001 | 23912 | | | |

+------------------+----------+--------------+------------------+-------------------+

配置从库

[root@Hyrule ~]# vim /etc/myf

#添加如下内容

server-id=2

relay-log=mysql-relay-bin

#重启从库

[root@Hyrule ~]# systemctl restart mysqld

#配置主从复制

mysql> CHANGE MASTER TO

-> MASTER_HOST='192.168.233.129',

-> MASTER_USER='repl',

-> MASTER_PASSWORD='repl123',

-> MASTER_LOG_FILE='mysql-bin.000001',

-> MASTER_LOG_POS=23912;

Query OK, 0 rows affected, 2 warnings (0.04 sec)

mysql> start slave;

Query OK, 0 rows affected (0.02 sec)

mysql> show slave status G

#确保以下两项是Yes

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

测试验证

#在主库中新建一个库或表，在从库中查看是否有主库中新建的表

主从监控

zabbix可以满足很多监控需求，其自定义监控尤其强大，主从监控就是通过配置zabbix自定义监控实现的。

请先部署zabbix，可以参考zabbix简介与部署

#在从库添加一个mysql用户密码配置文件

[root@Hyrule ~]# vim /root/.myf

[client]

user=root

password=mysql登录密码

[root@Hyrule ~]# chmod 755 /root

在客户端进行如下操作：

[root@localhost Hyrule]# vim /usr/local/etc/f

#修改UnsafeUserParameters=1

#在最后添加UserParameter=,,如

UserParameter=check_delay,/scripts/mysql_delay.sh

UserParameter=check_status,/scripts/mysql_status.sh

#重启客户端

[root@Hyrule001 ~]# pkill zabbix

[root@Hyrule001 ~]# zabbix_agentd

#编写监控延迟的脚本

[root@Hyrule ~]# mkdir /scripts

[root@Hyrule ~]# vim /scripts/mysql_delay.sh

#!/bin/bash

master_log_pos=$(mysql -e 'show slave status G;'|egrep "Read_Master_Log_Pos"|awk '{print $NF}')

exec_log_pos=$(mysql -e 'show slave status G;'|egrep "Exec_Master_Log_Pos"|awk '{print $NF}')

echo "$[$master_log_pos-$exec_log_pos]"

[root@Hyrule001 Hyrule]# chown zabbix.zabbix /scripts/mysql_delay.sh

[root@Hyrule001 Hyrule]# chmod 777 /scripts/mysql_delay.sh

#编写监控运行状态的脚本

[root@Hyrule ~]# vim /scripts/mysql_status.sh

#!/bin/bash

IO_status=$(mysql -e 'show slave status G;'|egrep "Slave_IO_Running"|awk '{print $NF}'|grep "Yes"|wc -l)

SQL_status=$(mysql -e 'show slave status G;'|egrep "Slave_SQL_Running"|awk '{print $NF}'|grep "Yes"|wc -l)

if [ $[$IO_status+$SQL_status] -eq 2 ];

then

echo 0

else

echo 1

fi

[root@Hyrule001 Hyrule]# chown zabbix.zabbix /scripts/mysql_status.sh

[root@Hyrule001 Hyrule]# chmod 777 /scripts/mysql_status.sh

添加监控项、触发器和报警媒介

配置报警媒介详见zabbix监控配置与邮件告警的邮件告警章节