MySQL-mmm 高可用集群

笨鸟先飞geigeigei

已于 2023-09-24 01:23:16 修改

阅读量245

点赞数 2

分类专栏： mysql linux 高可用文章标签： mysql 数据库服务器运维

于 2023-02-14 21:01:57 首次发布

本文链接：https://blog.csdn.net/weixin_58131623/article/details/129020730

版权

linux 同时被 3 个专栏收录

12 篇文章 0 订阅

订阅专栏

mysql

5 篇文章 0 订阅

订阅专栏

高可用

3 篇文章 0 订阅

订阅专栏

一、mysql-mmm工作原理

mysql-mmm的监管端会提供多个虚拟ip(vip) 包括一个可写vip和多个可读vip通过监管的管理，这些ip会绑定在可用的mysql服务器上，当某一台mysql出现故障后，监管会将vip迁移至其他可用的mysql上。

二、工作原理图

三、实验环境

服务器操作系统：CentOS7.9

数据库：MySQL5.7.40

MMM：MySQL-MMM 2.2.1

数据库分配：

master1 192.168.43.101 master1

master2 192.168.43.102 master2

slave1 192.168.43.103 slave1

slave2 192.168.43.104 slave2

monitor 192.168.43.105 monitor

虚拟vip (ip地址) ：

192.168.43.100 writer

192.168.43.200 reader

192.168.43.201 reader

四、搭建环境

1、部署master1节点的ntp服务以及域名解析；

[root@master1 ~]# cat <<END >>/etc/hosts
192.168.43.101 master1
192.168.43.102 master2
192.168.43.103 slave1
192.168.43.104 slave2
192.168.43.105 monitor
END

[root@master1 ~]# yum -y install ntp 
[root@master1 ~]# sed -i '/^server/s/^/#/g' /etc/ntp.conf         #将/etc/ntp.conf配置文件以server开头的加上#号
[root@master1 ~]# cat <<END >>/etc/ntp.conf
server 127.127.1.0
fudge 127.127.1.0 stratum 8
END
[root@master1 ~]# systemctl enable ntpd --now

放心ntpd服务通过防火墙
[root@localhost ~]# firewall-cmd --add-service=ntp --permanent
[root@localhost ~]# firewall-cmd --reload

2 、配置master2、slave1、slave2、monitor节点同步ntp时间及域名解析（在此只列举master2单台主机配置）；

[root@master2 ~]# cat <<END >>/etc/hosts
192.168.43.101 master1
192.168.43.102 master2
192.168.43.103 slave1
192.168.43.104 slave2
192.168.43.105 monitor
END

[root@master2 ~]# yum -y install ntpdate
[root@master2 ~]# /usr/sbin/ntpdate 192.168.43.101
[root@master2 ~]# echo "/usr/sbin/ntpdate 192.168.43.101">>/etc/rc.local
[root@master2 ~]# chmod +x /etc/rc.local

3、配置master1实现双主复制；

在五台服务器放行mysql服务通过防火墙
[root@master1 ~]# firewall-cmd --add-port=3306/tcp --permanent
[root@master1 ~]# firewall-cmd --reload

[root@master1 ~]# cat <<END>>/etc/my.cnf
server-id=1
log-bin=mysql-bin
log-slave-updates
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1
relay-log=relay1-log-bin
relay-log-index=slave-relay1-bin.index
END
注解：
sync_binlog=1                                             ##主机每次提交事务的时候把二进制日志的内容同步到磁盘上，所以即使服务器崩溃，也会把时间写入到日志中；
auto_increment_increment=2                ##以下两参数用于主主复制中，用于错开增值，防止键值冲突
[root@master1 ~]# systemctl restart mysqld

[root@master1 ~]# mysql -uroot -p123
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> grant replication slave on  *.* to 'master'@'192.168.43.%' identified by '123123';
mysql> flush privileges;

4、配置master2实现双主复制

 cat<<END >>/etc/my.cnf
server-id=2
log-bin=mysql-bin
log-slave-updates
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1
relay-log=relay2-log-bin
relay-log-index=slave-relay2-bin.index
END
[root@master2 ~]# systemctl restart mysqld
[root@master2 ~]# firewall-cmd --add-port=3306/tcp --permanent
[root@master2 ~]# firewall-cmd --reload

[root@master2 ~]# mysql -uroot -p123
mysql> change master to master_host='192.168.43.101',master_user='master',master_password='123123',master_log_file='mysql-bin.000001',master_log_pos=154;
mysql> start slave;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> grant replication slave on *.* to 'master'@'192.168.43.%' identified by '123123';
mysql> flush privileges;
[root@master1 ~]# mysql -uroot -p123
mysql> change master to master_host='192.168.43.101',master_user='master',master_password='123123',master_log_file='mysql-bin.000001',master_log_pos=154;
mysql> start slave;
mysql> show slave status\G;

配置slave1和slave2实现主从复制（在此只列举slave1单台主机配置）；

[root@salve2 ~]# firewall-cmd --add-port=3306/tcp --permanent
[root@salve2 ~]# firewall-cmd --reload 
[root@slave1 ~]# cat <<END>>/etc/my.cnf
server-id=3
relay-log=relay4-log-bin
relay-log-index=slave-relay4-bin.index
END
[root@slave1 ~]# systemctl restart mysqld
[root@slave1 ~]# mysql -uroot -p123
mysql> change  master to master_host='192.168.43.101',master_user='master',master_password='123123',master_log_file='mysql-bin.000001',master_log_pos=154;
mysql> start slave;
mysql> show slave status\G;

测试主从复制是否可以运行

[root@master1 ~]# mysql -uroot -p123
mysql> create database aaa;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
[root@master2 ~]# mysql -uroot -p123
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

[root@slave1 ~]# mysql -uroot -p123
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

[root@slave2 ~]# mysql -uroot -p123
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

6、MySQL-MMM安装配置

CentOS默认没有mysql-mmm软件包，官方推荐使用epel的网络源，五台都安装epel

1、monitor节点安装
[root@monitor ~]#  yum -y install mysql-mmm-monitor

在监管端放行monitor通过防火墙
[root@monitor ~]#   firewall-cmd --add-port=9988/tcp --permanent
[root@monitor ~]#   firewall-cmd   --reload
2、四台mysql节点安装
[root@master1 ~]# yum -y install mysql-mmm-agent

[root@master1 ~]# vi /etc/mysql-mmm/mmm_agent.conf
include mmm_common.conf

在被监管端放行mmm-anget通过防火墙
[root@master1 ~]#   firewall-cmd --add-port=9989/tcp --permanent
[root@master1 ~]#   firewall-cmd  --reload
 
# The 'this' variable refers to thisserver.  Proper operation requires
# that 'this' server (db1 by default), aswell as all other servers, have the
# proper IP addresses set in mmm_common.conf.
this db1
:wq
 
[root@master2 ~]# egrep -v"^#|^$" /etc/mysql-mmm/mmm_agent.conf
include mmm_common.conf
this db2
 
[root@slave1 ~]# egrep -v "^#|^$"/etc/mysql-mmm/mmm_agent.conf
include mmm_common.conf
this db3
 
[root@slave2 ~]# egrep -v "^#|^$"/etc/mysql-mmm/mmm_agent.conf
include mmm_common.conf
this db4

7、在master1节点上授权monitor节点连接数据库集群；

[root@master1 ~]# mysql -uroot -p123
mysql> grant replication client on *.* to 'mmm_monitor'@'192.168.43.%' identified by 'monitor';
mysql> grant super,replication client,process on *.* to 'mmm_agent'@'192.168.43.%' identified by 'agent';
mysql>  flush privileges;
权限注解：
replication client：权限用于执行show master status等命令。这些命令是用来查看复制状态的；
replication slave：是用于连接主库从库进行读取二进制文件进而实现复制的；
super：杀死mysql中连接的进程，设置全局变量，重置主从配置的权限；
process：具有查看当前运行的sql的权限 ，以及explain执行计划；

配置 monitor节点上的mysql-mmm的配置文件并且复制到各个mysql节点；

[root@monitor ~]# vim /etc/mysql-mmm/mmm_common.conf
active_master_role      writer                                              ##指定活跃角色为写角色
<host default>
    cluster_interface       ens33                                           ##承载网卡
    pid_path                /var/run/mysql-mmm-agent.pid
    bin_path                /usr/libexec/mysql-mmm/            ##可执行文件路径
    replication_user        master                                        ##mysql集群主从复制账户
    replication_password    123123
    agent_user              mmm_agent                                 ##monitor连接集群的账户
    agent_password          agent
</host>
<host db1>
    ip      192.168.43.101
    mode    master
    peer    db2                                                                ##指定对立的节点名
</host>
<host db2>
    ip      192.168.43.102
    mode    master
    peer    db1
</host>
<host db3>
    ip      192.168.43.103
    mode    slave
</host>
<host db4>
    ip      192.168.43.104
    mode    slave
</host>
<role writer>
    hosts   db1, db2
    ips     192.168.43.100
    mode    exclusive                                               ##只有一个host可以writer，一般写操作是这个模式
</role>
<role reader>
    hosts   db3, db4
    ips     192.168.43.200,192.168.43.201
    mode    balanced                                             ##轮询
</role>

[root@monitor ~]# vim /etc/mysql-mmm/mmm_mon.conf
include mmm_common.conf
<monitor>
    ip                  127.0.0.1
    pid_path            /run/mysql-mmm-monitor.pid
    bin_path            /usr/libexec/mysql-mmm
    status_path         /var/lib/mysql-mmm/mmm_mond.status
    ping_ips            192.168.43.101,192.168.43.102,192.168.43.103,192.168.43.104         ##指定监听的所有节点ip
    auto_set_online     60                                                                                                    ##判定其online的时间，超过60s认为其down
    # The kill_host_bin does not exist by default, though the monitor will
    # throw a warning about it missing.  See the section 5.10 "Kill Host
    # Functionality" in the PDF documentation.
    #
    # kill_host_bin     /usr/libexec/mysql-mmm/monitor/kill_host
    #
</monitor>
<host default>
    monitor_user        mmm_monitor                                                                              ##monitor的工作用户
    monitor_password    monitor
</host>
debug 0

[root@monitor ~]# for i in 101 102 103 104;do scp /etc/mysql-mmm/mmm_common.conf root@192.168.43.$i:/etc/mysql-mmm/; done             ##将配置文件复制到mysql节点

启动master1、master2、slave1、slave2四个节点上的mysql-mmm服务（在此只列举master1单台主机配置）；

[root@master1 ~]# systemctl daemon-reload
[root@master1 ~]# systemctl start mysql-mmm-agent
[root@master1 ~]# netstat -anptu | grep mmm
tcp        0      0 192.168.43.101:9989     0.0.0.0:*               LISTEN      2822/mmm_agentd

10、启动monitor节点上的mysql-mmm服务并查看集群状态；

[root@monitor ~]# systemctl daemon-reload
[root@monitor ~]# systemctl start mysql-mmm-monitor
[root@monitor ~]# netstat -anptu |grep mmm
tcp        0      0 127.0.0.1:9988          0.0.0.0:*               LISTEN      7552/mmm_mond

[root@monitor ~]# mmm_control show
# Warning: agent on host db4 is notreachable
 db1(192.168.43.101) master/ONLINE. Roles: writer(192.168.43.100)
 db2(192.168.43.102) master/ONLINE. Roles: 
 db3(192.168.43.103) slave/ONLINE. Roles: reader(192.168.43.200)
 db4(192.168.43.104) slave/ONLINE. Roles:reader(192.168.43.201)

客户端验证测试读写数据；

[root@master1 ~]# mysql -uroot -p123123
mysql> grant all on *.* to 'client'@'192.168.43.%'identified by '123123';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
 
[root@monitor ~]# yum -y install mysql
[root@monitor ~]# mysql -uclient -p123123 -h192.168.43.100
MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.02 sec)MySQL [(none)]>exit
 
[root@monitor ~]# mysql -uclient -p123123 -h192.168.100.251
MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.02 sec)MySQL [(none)]>exit

将master1主节点的mysql服务停止，测试集群状况；

[root@master1 ~]# systemctl stop mysqld
[root@monitor ~]# mmm_control show
# Warning: agent on host db4 is notreachable
 db1(192.168.43.101) master/HARD_OFFLINE. Roles: 
 db2(192.168.43.102) master/ONLINE. Roles: writer(192.168.43.100)
 db3(192.168.43.103) slave/ONLINE. Roles: reader(192.168.43.200)
 db4(192.168.43.104) slave/ONLINE. Roles: reader(192.168.43.201)
 
注解：
         ONLINE. Roles表示在线节点；
         HARD_OFFLINE表示ping不通并且(或者)mysql连接中断,会导致hard_offline状态；
         admin_offline是手动下线的状态；