监控mysql主从状态与延迟_mysql主从延迟监控报警

监控mysql主从状态

环境

1.配置MySQL主从

安装数据库

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

配置主数据库

[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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

配置从数据库

[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
.....
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36