1. 环境准备

1.1 系统软件列表

No	主机名	作用	IP
1	mmm1	master	192.168.8.201
2	mmm2	master	192.168.8.202
3	mmm3	slave	192.168.8.203
4	mmm1	监控	192.168.8.201
5	mmm1	监控用户	monitor
6	*	代理用户	agent
7	mmm1/mmm2	复制用户	repli

1.2 hostname

[root@mmm1 ~]# hostnamectl set-hostname mmm1 --static
[root@mmm2 ~]# hostnamectl set-hostname mmm2 --static
[root@mmm3 ~]# hostnamectl set-hostname mmm3 --static

需要重新启动操作系统

1.3 网络设置

[root@mmm1 ~]# vi /etc/sysconfig/network-scripts/ifcfg-ens33
[root@mmm2 ~]# vi /etc/sysconfig/network-scripts/ifcfg-ens33
[root@mmm3 ~]# vi /etc/sysconfig/network-scripts/ifcfg-ens33
BOOTPROTO="static" #dhcp改为static 
ONBOOT="yes" #开机启用本配置
IPADDR=192.168.8.201 #静态IP 192.168.8.202 /192.168.8.203同
GATEWAY=192.168.8.2 #默认网关
NETMASK=255.255.255.0 #子网掩码
DNS1=114.114.114.114 #DNS 配置
DNS2=8.8.8.8 #DNS 配置

$# service network restart

1.4 防火墙开放

firewall-cmd --zone=public --list-ports
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --zone=public --add-port=9989/tcp --permanent
firewall-cmd --reload

2. mysql配置

2.1 mysql安装

yum install wget
wget http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm
yum localinstall mysql57-community-release-el7-8.noarch.rpm
yum repolist enabled | grep "mysql.*-community.*"
yum install -y  mysql-community-server

2.2 查看MySQL运行状态

systemctl status mysqld.service

2.3 查找初始密码并修改

grep "password" /var/log/mysqld.log
mysql -u root -p
Enter password:

mysql>set global validate_password_policy=0;
mysql>set global validate_password_length=6;
mysql>ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
mysql>exit;

2.4 mysql 配置

mmm1

vi /etc/my.cnf
[mysqld]
log_bin
server-id=1

mmm2

vi /etc/my.cnf
[mysqld]
log_bin
server-id=2

mmm3

vi /etc/my.cnf
[mysqld]
log_bin
server-id=3

2.5 重启mysql

systemctl restart mysqld

3. 复制关系实施

image-20201227155630648.png

3.1 mmm1,mmm2登录mysql并创建主从复制用用户(repli)

mysql -uroot -p
mysql>use mysql;
mysql>select user,host from user;
mysql>set global validate_password_policy=0;
mysql>set global validate_password_length=6;
mysql> create user repli@'%' identified by '123456';
mysql> grant all privileges on *.* to repli@'%' identified by '123456';

3.2 查看数据库的状态

mmm1

mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| mmm1-bin.000001 |      691 |              |                  |                   |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mmm2

mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| mmm2-bin.000001 |      691 |              |                  |                   |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mmm3

mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| mmm3-bin.000001 |      154 |              |                  |                   |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

3.3 主从复制（mmm1,mmm2）

mmm1

[root@mmm1 ~]# mysql -uroot -p
mysql>change master to master_host='192.168.8.202',master_user='repli',master_password='123456',master_log_file='mmm2-bin.000001',master_log_pos=691;
mysql> start slave;
mysql> show slave status\G
mysql> exit;
Bye

mmm2

[root@mmm2 ~]# mysql -uroot -p
mysql>change master to master_host='192.168.8.201',master_user='repli',master_password='123456',master_log_file='mmm1-bin.000001',master_log_pos=691;
mysql> start slave;
mysql> show slave status\G
mysql> exit;
Bye

mmm3

[root@mmm3 ~]# mysql -uroot -p
mysql>change master to master_host='192.168.8.201',master_user='repli',master_password='123456',master_log_file='mmm1-bin.000001',master_log_pos=691;
mysql> start slave;
mysql> show slave status\G
mysql> exit;
Bye

4 退出mysql,安装MMM

yum -y install epel-release
wget http://rpms.remirepo.net/enterprise/remi-release-7.rpm
rpm -Uvh remi-release-7.rpm

yum install -y mysql-mmm-agent.noarch

5 mmm1安装监控

yum install -y mysql-mmm*
[root@mmm1 ~]# cd /etc/mysql-mmm
[root@mmm1 mysql-mmm]# ll
total 20
-rw-r-----. 1 root root  230 May  5  2018 mmm_agent.conf
-rw-r-----. 1 root root  777 May  5  2018 mmm_common.conf
-rw-r-----. 1 root root  680 May  5  2018 mmm_mon.conf
-rw-r-----. 1 root root  827 May  5  2018 mmm_mon_log.conf
-rw-r--r--. 1 root root 1432 May  5  2018 mmm_tools.conf
[root@mmm1 mysql-mmm]# cat mmm_agent.conf
include mmm_common.conf

# The 'this' variable refers to this server.  Proper operation requires
# that 'this' server (db1 by default), as well as all other servers, have the
# proper IP addresses set in mmm_common.conf.
this db1
[root@mmm1 mysql-mmm]# vi mmm_common.conf
active_master_role      writer

<host default>
    cluster_interface       ens33
    pid_path                /run/mysql-mmm-agent.pid
    bin_path                /usr/libexec/mysql-mmm/
    replication_user        repli
    replication_password    123456
    agent_user              agent
    agent_password          123456
</host>

<host db1>
    ip      192.168.8.201
    mode    master
    peer    db2
</host>

<host db2>
    ip      192.168.8.202
    mode    master
    peer    db1
</host>

<host db3>
    ip      192.168.8.203
    mode    slave
</host>

<role writer>
    hosts   db1, db2
    ips     192.168.8.66
    mode    exclusive
</role>

<role reader>
    hosts   db2, db2, db3
    ips     192.168.8.88
    mode    balanced
</ro1e>
:wq
[root@mmm1 mysql-mmm]# vi 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.8.201,192.168.8.202,192.168.8.203
    auto_set_online     60

    # 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        monitor
    monitor_password    123456
</host>
:wq

6 mmm2,mmm3的agent修改

$ cd /etc/mysql-mmm/
$ ll
[root@mmm2 mysql-mmm]#  vi mmm_agent.conf
include mmm_common.conf

# The 'this' variable refers to this server.  Proper operation requires
# that 'this' server (db1 by default), as well as all other servers, have the
# proper IP addresses set in mmm_common.conf.
this db2
:wq
[root@mmm3 mysql-mmm]#  vi mmm_agent.conf
include mmm_common.conf

# The 'this' variable refers to this server.  Proper operation requires
# that 'this' server (db1 by default), as well as all other servers, have the
# proper IP addresses set in mmm_common.conf.
this db3
:wq

7 因三台机器的mmm_common.conf都相同，mmm1.mysql的文件直接SCP过去

[root@mmm1 mysql-mmm]# scp mmm_common.conf root@'192.168.8.202':/etc/mysql-mmm/
[root@mmm1 mysql-mmm]# scp mmm_common.conf root@'192.168.8.203':/etc/mysql-mmm/

8 mysql环境创建agent用户和monitor用户（mmm1）

[root@mmm1 mysql-mmm]# mysql -uroot -p
Enter password:
mysql> set global validate_password_policy=0;
mysql> set global validate_password_length=6;
mysql> create user agent@'%' identified by '123456';
mysql> grant all privileges on *.* to agent@'%' identified by '123456';
mysql> create user monitor@'%' identified by '123456';
mysql> grant all privileges on *.* to monitor@'%' identified by '123456';

注意：只在mmm1机器上创建完agent和monitor后，在mmm2和mmm3上就自动创建完了

10 启动MMM（all sessions）

mysql> exit;
Bye
$ systemctl start mysql-mmm-agent
$ systemctl status mysql-mmm-agent

11 mmm1机器上，启动monitor

[root@mmm1 mysql-mmm]# systemctl start mysql-mmm-monitor
[root@mmm1 mysql-mmm]# systemctl status mysql-mmm-monitor

错误对应

$ ss -anplt |grep mmm_agent
LISTEN     0      10     192.168.8.201:9989                     *:*                   users:(("mmm_agentd",pid=2326,fd=3))

这里还有一个隐藏端口9989没有放开

12 验证一下

mmm1 查看一下VIP，可以找到192.168.8.66

[root@mmm1 mysql-mmm]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:04:4e:61 brd ff:ff:ff:ff:ff:ff
    inet 192.168.8.201/24 brd 192.168.8.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet 192.168.8.66/32 scope global ens33
       valid_lft forever preferred_lft forever
    inet6 fd15:4ba5:5a2b:1008:8b50:cae1:edfc:5618/64 scope global noprefixroute dynamic
       valid_lft 86393sec preferred_lft 14393sec
    inet6 fe80::ea5a:1a30:855b:aea5/64 scope link noprefixroute
       valid_lft forever preferred_lft forever

mmm1 停掉mysql，再看一下192.168.8.66是否存在；如果不存在，看是否漂移到mmm2

[root@mmm1 mysql-mmm]# systemctl stop mysqld
[root@mmm1 mysql-mmm]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:04:4e:61 brd ff:ff:ff:ff:ff:ff
    inet 192.168.8.201/24 brd 192.168.8.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet6 fd15:4ba5:5a2b:1008:8b50:cae1:edfc:5618/64 scope global noprefixroute dynamic
       valid_lft 86375sec preferred_lft 14375sec
    inet6 fe80::ea5a:1a30:855b:aea5/64 scope link noprefixroute
       valid_lft forever preferred_lft forever

查看mmm2,是否存在192.168.8.66，切换VIP会花点儿时间

[root@mmm2 mysql-mmm]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:6f:18:ea brd ff:ff:ff:ff:ff:ff
    inet 192.168.8.202/24 brd 192.168.8.255 scope global ens33
       valid_lft forever preferred_lft forever
    inet 192.168.8.66/32 scope global ens33
       valid_lft forever preferred_lft forever