监控mysql主从状态与延迟

监控mysql主从状态

环境

名称	主机名	IP
zabbix服务端	wangyitong	192.168.232.128
zabbix客户端，mysql主库	wyt3	192.168.232.132
zabbix客户端，mysql从库	wyt2	192.168.232.130

1.配置MySQL主从

安装数据库

[root@wyt3 ~]# yum -y install mariadb*
[root@wyt2 ~]# yum -y install mariadb*

配置主数据库

[root@wyt3 ~]# mysql  //在主数据库里创建一个同步账号授权给从数据库使用
MariaDB [(none)]> grant replication slave on *.* to 'repl'@'192.168.232.130' identified by 'repl123';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.232.130';
Query OK, 0 rows affected (0.00 sec)

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

[root@wyt3 ~]# vim /etc/my.cnf  //编辑配置文件
server-id = 10  //数据库服务器唯一标识符，主库的server-id值必须比从库的大
log-bin = mysql_bin  //启用binlog日志

[root@wyt3 ~]# systemctl restart mariadb //重启mysql服务
[root@wyt3 scripts]# ss -antl|grep 3306
LISTEN     0      50           *:3306                     *:* 

[root@wyt3 ~]# mysql 
MariaDB [(none)]> show master status; //查看主库状态
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      245 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.01 sec)

配置从数据库

[root@wyt2 ~]# vim /etc/my.cnf //编辑配置文件
server-id = 20  //设置从库的唯一标识符，从库的server-id值必须小于主库的该值
relay-log = myrelay_bin //启用中继日志relay-log

[root@wyt3 ~]# systemctl restart mariadb //重启mysql服务
[root@wyt3 scripts]# ss -antl|grep 3306
LISTEN     0      50           *:3306                     *:* 

//配置并启动主从复制
MariaDB [(none)]> change master to \
    -> master_host='192.168.232.132',
    -> master_user='repl',
    -> master_password='repl123',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=245;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

MariaDB [(none)]> start slave; //启用
Query OK, 0 rows affected (0.03 sec)

//查看从服务器状态
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.232.132
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 245
               Relay_Log_File: myrelay.000002
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes   //此处必须是Yes
            Slave_SQL_Running: Yes  //此处必须是Yes
.....

2.安装zabbix服务端及客户端

配置zabbix服务端

[root@wangyitong ~]# wget https://cdn.zabbix.com/zabbix/sources/stable/5.0/zabbix-5.0.1.tar.gz //下载zabbix
[root@wangyitong ~]# ls
anaconda-ks.cfg  lamp  zabbix-5.0.1.tar.gz
[root@wangyitong ~]# tar xf zabbix-5.0.1.tar.gz //解压
[root@wangyitong ~]# useradd -r -M -s /sbin/nologin zabbix  //创建zabbix用户和组
[root@wangyitong ~]# mysql -uroot -p123456 //配置zabbix数据库

mysql> create database zabbix character set utf8 collate utf8_bin;
Q