MMM(Master-Master replication manager for MySQL,MySQL主主复制管理器)

07-09 1404阅读

概述

MMM(Master-Master replication manager for MySQL,MySQL主主复制管理器)

        MMM是一套支持双主故障切换和双主日常管理的脚本程序。MMM 使用 Perl 语言开发,主要用来监控和管理 MySQL Master-Master (双主)复制,虽然叫做双主复制,但是业务上同一时刻只允许对一个主进行写入,另一台备选主上提供部分读服务,以加速在主主切换时备选主的预热,可以说MMM这套脚本程序一方面实现了故障切换的功能,另一方面其内部附加的工具脚本也可以实现多个 Slave 的 read 负载均衡。

        MMM提供了自动和手动两种方式移除一组服务器中复制延迟较高的服务器的虚拟ip,同时它还可以备份数据,实现两节点之间的数据同步等。由于MMM无法完全保证数据的一致性,所以MMM适用于对数据的一致性要求不是很高,但是又想最大程度地保证业务可用性的场景。

        MMM是一套灵活的脚本程序,基于perl实现,用来对 mysql replication 进行监控和故障迁移,并能管理 MySQL Master-Master 复制的配置。

MMM 高可用架构的说明

  • mmm_mon:监控进程,负责所有的监控工作,决定和处理所有节点角色活动。此脚本需要在监控主机上运行。
  • mmm_agent:运行在每个MySQL服务器上的代理进程,完成监控的探针工作和执行简单的远端服务设置。此脚本需要在被监管机上运行。
  • mmm_control:一个简单的脚本,提供管理 mmm_mon 进程的命令。
  • mysql-mmm 的监管端会提供多个虚拟 IP(VIP),包括一个可写 VIP,多个可读 VIP,通过监管的管理,这些 IP 会绑定在可用 MySQL 之上,当某一台 MySQL 宕机时,监管会将 VIP 迁移至其他 MySQL。

            在整个监管过程中,需要在 MySQL 中添加相关授权用户,以便让 MySQL 可以支持监控主机的维护。 授权的用户包括一个 mmm_monitor 用户和一个 mmm_agent 用户。

    搭建 MySQL MMM

    • 准备环境

      master01(db1)            192.168.80.30                 mysql5.7、mysql-mmm

      master02(db2)            192.168.80.40                 mysql5.7、mysql-mmm

      slave01(db3)               192.168.80.10                 mysql5.7、mysql-mmm

      slave02(db4)               192.168.80.50                 mysql5.7、mysql-mmm

      monitor                             192.168.80.20                 mysql-mmm

      • 初始化关闭防火墙

        [root@localhost ~]# systemctl stop firewalld 
        [root@localhost ~]# setenforce 0
        setenforce: SELinux is disabled
        [root@localhost ~]# vim /etc/selinux/config 
        • 修改 master01 配置文件

          [root@localhost ~]# vim /etc/my.cnf
          修改内容如下:
          [mysqld]
          user = mysql
          basedir=/usr/local/mysql
          datadir=/usr/local/mysql/data
          port = 3306
          character-set-server=utf8
          pid-file = /usr/local/mysql/mysqld.pid
          socket=/usr/local/mysql/mysql.sock
          bind-address = 0.0.0.0
          skip-name-resolve
          max_connections=2048
          default-storage-engine=INNODB
          max_allowed_packet=16M
          server-id = 1
          log-error=/usr/local/mysql/data/mysql_error.log
          general_log=ON
          general_log_file=/usr/local/mysql/data/mysql_general.logslow_query_log=ONslow_query_log_file=mysql_slow_query.log
          long_query_time=5
          binlog-ignore-db=mysql,information_schema
          log_bin=mysql_bin
          log_slave_updates=true
          sync_binlog=1
          innodb_flush_log_at_trx_commit=1
          auto_increment_increment=2
          auto_increment_offset=1
          
          • 把配置文件复制到其它 3 台数据库服务器上并启动服务器,注意:配置文件中的 server_id 要修改

            [root@localhost ~]# scp /etc/my.cnf root@192.168.80.10:/etc/
            The authenticity of host '192.168.80.10 (192.168.80.10)' can't be established.
            ECDSA key fingerprint is SHA256:yDomXwGmwNaWFHx/DbtaoneMurNRY4HdV5eSmEb0LVM.
            ECDSA key fingerprint is MD5:80:de:f8:94:82:75:37:b3:d9:a8:7e:e8:cf:ba:7b:b9.
            Are you sure you want to continue connecting (yes/no)? yes
            Warning: Permanently added '192.168.80.10' (ECDSA) to the list of known hosts.
            root@192.168.80.10's password: 
            my.cnf 100%  933   340.7KB/s   00:00    
            [root@localhost ~]# scp /etc/my.cnf root@192.168.80.40:/etc/
            The authenticity of host '192.168.80.40 (192.168.80.40)' can't be established.
            ECDSA key fingerprint is SHA256:3wpHjsT7r1YEEQipTCugtbtifmQ9zIfJyhbG44m0HFc.
            ECDSA key fingerprint is MD5:3b:8a:09:fc:dd:98:99:a6:1c:ce:6d:68:e6:b5:27:9f.
            Are you sure you want to continue connecting (yes/no)? yes
            Warning: Permanently added '192.168.80.40' (ECDSA) to the list of known hosts.
            root@192.168.80.40's password: 
            my.cnf 100%  933     1.9MB/s   00:00    
            [root@localhost ~]# scp /etc/my.cnf root@192.168.80.50:/etc/
            The authenticity of host '192.168.80.50 (192.168.80.50)' can't be established.
            ECDSA key fingerprint is SHA256:kXP0zouJrRojfwV62JejGdSgywSGAJ1C/GVHt3RPvpQ.
            ECDSA key fingerprint is MD5:9a:9f:d1:d6:bb:de:9b:7f:e0:b9:95:35:99:45:d6:1e.
            Are you sure you want to continue connecting (yes/no)? yes
            Warning: Permanently added '192.168.80.50' (ECDSA) to the list of known hosts.
            root@192.168.80.50's password: 
            my.cnf 100%  933     1.4MB/s   00:00  
            [root@localhost ~]# systemctl restart mysqld
            • 配置主主复制,两台主服务器相互复制

              (1)在两台主服务器上都执行授予从的权限,从服务器上不需要执行

              [root@localhost ~]# mysql -uroot -pabc123
              mysql: [Warning] Using a password on the command line interface can be insecure.
              Welcome to the MySQL monitor.  Commands end with ; or \g.
              Your MySQL connection id is 4
              Server version: 5.7.44-log Source distribution
              Copyright (c) 2000, 2023, Oracle and/or its affiliates.
              Oracle is a registered trademark of Oracle Corporation and/or its
              affiliates. Other names may be trademarks of their respective
              owners.
              Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
              mysql> grant replication slave on *.* to 'replication'@'192.168.80.%' identified by '123456';
              Query OK, 0 rows affected, 1 warning (0.01 sec)
              

              (2)在两台主服务器上查看,记录日志文件名称和同步点

              mysql> show master status;
              +------------------+----------+--------------+--------------------------+-------------------+
              | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
              +------------------+----------+--------------+--------------------------+-------------------+
              | mysql_bin.000001 |      460 |              | mysql,information_schema |                   |
              +------------------+----------+--------------+--------------------------+-------------------+
              1 row in set (0.00 sec)
              

              (3)在 master01 上配置同步

              mysql> change master to master_host='192.168.80.40',master_user='replication',master_passsword='123456',master_log_file='mysql_bin.000001',master_log_pos=460;
              Query OK, 0 rows affected, 2 warnings (0.01 sec)
              mysql> start slave;
              Query OK, 0 rows affected (0.00 sec)
              mysql> show slave status\G
              

              MMM(Master-Master replication manager for MySQL,MySQL主主复制管理器)

              (4)在 master02 上配置同步

              mysql> change master to master_host='192.168.80.30',master_user='replication',master_password='123456',master_log_file='mysql_bin.000001',master_log_pos=460;
              Query OK, 0 rows affected, 2 warnings (0.00 sec)
              mysql> start slave;
              Query OK, 0 rows affected (0.00 sec)
              mysql> show slave status\G
              

              MMM(Master-Master replication manager for MySQL,MySQL主主复制管理器)

              • 配置主从复制,在两台从服务器上做

                [root@localhost ~]# mysql -uroot -pabc123
                mysql: [Warning] Using a password on the command line interface can be insecure.
                Welcome to the MySQL monitor.  Commands end with ; or \g.
                Your MySQL connection id is 2
                Server version: 5.7.44-log Source distribution
                Copyright (c) 2000, 2023, Oracle and/or its affiliates.
                Oracle is a registered trademark of Oracle Corporation and/or its
                affiliates. Other names may be trademarks of their respective
                owners.
                Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
                mysql> change master to master_host='192.168.80.30',master_user='replication',master_passsword='123456',master_log_file='mysql_bin.000001',master_log_pos=460;
                Query OK, 0 rows affected, 2 warnings (0.01 sec)
                mysql> start slave;
                Query OK, 0 rows affected (0.00 sec)
                mysql> show slave status\G
                
                • 测试主主、主从 同步情况

                  mysql> create database db_test;
                  Query OK, 1 row affected (0.00 sec)
                  mysql> show databases;
                  +--------------------+
                  | Database           |
                  +--------------------+
                  | information_schema |
                  | db_test            |
                  | mysql              |
                  | performance_schema |
                  | sys                |
                  +--------------------+
                  5 rows in set (0.01 sec)
                  

                  安装配置 MySQL-MMM

                  • 在所有服务器上安装 MySQL-MMM

                    [root@localhost ~]# mount /dev/sr0 /mnt
                    mount: /dev/sr0 写保护,将以只读方式挂载
                    [root@localhost ~]# wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
                    --2024-07-05 16:55:18--  http://mirrors.aliyun.com/repo/Centos-7.repo
                    正在解析主机 mirrors.aliyun.com (mirrors.aliyun.com)... 61.162.13.241, 61.162.13.242, 61.162.13.235, ...
                    正在连接 mirrors.aliyun.com (mirrors.aliyun.com)|61.162.13.241|:80... 已连接。
                    已发出 HTTP 请求,正在等待回应... 200 OK
                    长度:2523 (2.5K) [application/octet-stream]
                    正在保存至: “/etc/yum.repos.d/CentOS-Base.repo”
                    100%[==============================================>] 2,523       --.-K/s 用时 0.007s  
                    2024-07-05 16:55:18 (365 KB/s) - 已保存 “/etc/yum.repos.d/CentOS-Base.repo” [2523/2523])
                    [root@localhost ~]# yum -y install epel-release
                    [root@localhost ~]# yum -y install mysql-mmm*
                    
                    • 在 master01 上对 MySQL-MMM 进行配置

                      [root@localhost ~]# cd /etc/mysql-mmm/
                      [root@localhost mysql-mmm]# vim mmm_common.conf
                      修改内容如下:
                      active_master_role      writer
                      
                          cluster_interface       ens33
                          pid_path                /run/mysql-mmm-agent.pid
                          bin_path                /usr/libexec/mysql-mmm/
                          replication_user        replication
                          replication_password    123456
                          agent_user              mmm_agent
                          agent_password          123456
                      
                      
                          ip      192.168.80.30
                          mode    master
                          peer    db2
                      
                      
                          ip      192.168.80.40
                          mode    master
                          peer    db1
                      
                      
                          ip      192.168.80.10
                          mode    slave
                      
                      
                          ip      192.168.80.50
                          mode    slave
                      
                      
                          hosts   db1, db2
                          ips     192.168.80.250
                          mode    exclusive
                      
                      
                          hosts   db3, db4
                          ips     192.168.80.251, 192.168.80.252
                          mode    balanced
                      
                      
                      • 把配置文件复制到其它 4 台主机,所有主机该配置文件内容都是一样的

                        [root@localhost mysql-mmm]# scp mmm_common.conf root@192.168.80.40:/etc/mysql-mmm/
                        root@192.168.80.40's password: 
                        mmm_common.conf                                       100%  833     1.7MB/s   00:00    
                        [root@localhost mysql-mmm]# scp mmm_common.conf root@192.168.80.10:/etc/mysql-mmm/
                        root@192.168.80.10's password: 
                        mmm_common.conf                                       100%  833     1.0MB/s   00:00    
                        [root@localhost mysql-mmm]# scp mmm_common.conf root@192.168.80.50:/etc/mysql-mmm/
                        root@192.168.80.50's password: 
                        mmm_common.conf                                       100%  833     1.6MB/s   00:00    
                        [root@localhost mysql-mmm]# scp mmm_common.conf root@192.168.80.20:/etc/mysql-mmm/
                        The authenticity of host '192.168.80.20 (192.168.80.20)' can't be established.
                        ECDSA key fingerprint is SHA256:6hV+Qg/wIfw3mNnj7ncRmPK32NfsA9863CVOYAYD1dg.
                        ECDSA key fingerprint is MD5:da:b4:c7:c0:f8:bf:be:0f:05:f5:ae:da:2b:05:4c:97.
                        Are you sure you want to continue connecting (yes/no)? yes
                        Warning: Permanently added '192.168.80.20' (ECDSA) to the list of known hosts.
                        root@192.168.80.20's password: 
                        mmm_common.conf                                       100%  833     1.1MB/s   00:00    
                        
                        • 修改所有数据库服务器的代理配置文件 mmm_agent.conf

                          vim /etc/mysql-mmm/mmm_agent.conf
                          include mmm_common.conf
                          this db1				#根据不同的主机分别修改为 db1,db2,db3,db4

                          MMM(Master-Master replication manager for MySQL,MySQL主主复制管理器)

                          • 在 monitor 监控服务器上修改监控配置文件 mmm_mon.conf

                            [root@localhost ~]# vim /etc/mysql-mmm/mmm_mon.conf
                            修改内容如下:
                            include mmm_common.conf
                            
                                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.80.30,192.168.80.40,192.168.80.10,192.168.80.50
                                auto_set_online     10
                                # 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_user        mmm_monitor
                                monitor_password    123456
                            
                            debug 0
                            
                            • 在所有数据库上为 mmm_agent(代理进程)授权

                              mysql> grant super, replication client, process on *.* to 'mmm_agent'@'192.168.80.%' identified by '123456';
                              Query OK, 0 rows affected, 1 warning (0.00 sec)
                              • 在所有数据库上为 mmm_moniter(监控进程)授权

                                mysql> grant replication client on *.* to 'mmm_monitor'@'192.168.80.%' identified by '123456';
                                Query OK, 0 rows affected, 1 warning (0.00 sec)
                                mysql> flush privileges;
                                Query OK, 0 rows affected (0.00 sec)
                                
                                • 在所有数据库服务器上启动 mysql-mmm-agent

                                  [root@localhost ~]# systemctl start mysql-mmm-agent.service
                                  [root@localhost ~]# systemctl enable mysql-mmm-agent.service
                                  Created symlink from /etc/systemd/system/multi-user.target.wants/mysql-mmm-agent.service to /usr/lib/systemd/system/mysql-mmm-agent.service.
                                  
                                  • 在 monitor 服务器上启动 mysql-mmm-monitor

                                    [root@localhost ~]# systemctl start mysql-mmm-monitor.service 
                                    • 在 monitor 服务器上测试群集

                                      (1)查看各节点的情况

                                      [root@localhost ~]# mmm_control show
                                        db1(192.168.80.30) master/ONLINE. Roles: writer(192.168.80.250)
                                        db2(192.168.80.40) master/ONLINE. Roles: 
                                        db3(192.168.80.10) slave/ONLINE. Roles: reader(192.168.80.251)
                                        db4(192.168.80.50) slave/ONLINE. Roles: reader(192.168.80.252)
                                      

                                      (2)检测监控功能是否都完善,需要各种OK

                                      [root@localhost ~]# mmm_control checks all
                                      db4  ping         [last change: 2024/07/08 11:21:36]  OK
                                      db4  mysql        [last change: 2024/07/08 11:21:36]  OK
                                      db4  rep_threads  [last change: 2024/07/08 11:21:36]  OK
                                      db4  rep_backlog  [last change: 2024/07/08 11:21:36]  OK: Backlog is null
                                      db2  ping         [last change: 2024/07/08 11:21:36]  OK
                                      db2  mysql        [last change: 2024/07/08 11:21:36]  OK
                                      db2  rep_threads  [last change: 2024/07/08 11:21:36]  OK
                                      db2  rep_backlog  [last change: 2024/07/08 11:21:36]  OK: Backlog is null
                                      db3  ping         [last change: 2024/07/08 11:21:36]  OK
                                      db3  mysql        [last change: 2024/07/08 11:21:36]  OK
                                      db3  rep_threads  [last change: 2024/07/08 11:21:36]  OK
                                      db3  rep_backlog  [last change: 2024/07/08 11:21:36]  OK: Backlog is null
                                      db1  ping         [last change: 2024/07/08 11:21:36]  OK
                                      db1  mysql        [last change: 2024/07/08 11:21:36]  OK
                                      db1  rep_threads  [last change: 2024/07/08 11:21:36]  OK
                                      db1  rep_backlog  [last change: 2024/07/08 11:21:36]  OK: Backlog is null
                                      

                                      (3)指定绑定 VIP 的主机

                                      [root@localhost ~]# mmm_control move_role writer db2
                                      OK: Role 'writer' has been moved from 'db1' to 'db2'. Now you can wait some time and check new roles info!
                                      [root@localhost ~]# mmm_control show
                                        db1(192.168.80.30) master/ONLINE. Roles: 
                                        db2(192.168.80.40) master/ONLINE. Roles: writer(192.168.80.250)
                                        db3(192.168.80.10) slave/ONLINE. Roles: reader(192.168.80.251)
                                        db4(192.168.80.50) slave/ONLINE. Roles: reader(192.168.80.252)
                                      
                                      • 故障测试

                                        停止 master02 确认 VIP 是否移动到 master01 上。注意:master02 主服务器恢复服务后,不会抢占

                                        [root@localhost ~]# mmm_control move_role writer db1
                                        OK: Role is on 'db1' already. Skipping command.
                                        [root@localhost ~]# mmm_control show
                                        # Warning: agent on host db2 is not reachable
                                          db1(192.168.80.30) master/ONLINE. Roles: writer(192.168.80.250)
                                          db2(192.168.80.40) master/HARD_OFFLINE. Roles: 
                                          db3(192.168.80.10) slave/ONLINE. Roles: reader(192.168.80.251)
                                          db4(192.168.80.50) slave/ONLINE. Roles: reader(192.168.80.252)
                                        
VPS购买请点击我

文章版权声明:除非注明,否则均为主机测评原创文章,转载或复制请以超链接形式并注明出处。

目录[+]