mysql-mmm 架构详细教程

mysql-mmm 架构图

1. mysql mmm 特点

    优点：高可用性，扩展性好，出现故障自动切换，对于主主同步，在同一时间只提供一台数据库写操作，保证的数据的一致性。
    缺点：Monitor节点是单点，可以结合Keepalived实现高可用，对主机的数量有要求，需要实现读写分离，对程序来说是个挑战。

2. mysql-mmm 安装

    需要在所有server上都安装mysql-mmm
    wget http://dl.fedoraproject.org/pub/epel/6/i386/epel-release-6-8.noarch.rpm 
    rpm -Uvh epel-release-6-8.noarch.rpm 
    yum install -y mysql-mmm*

3. mysql-mmm 配置

3.1 配置mmm代理和监控账号的权限

    现在环境已经配置好，我没有忽略 mysql 库和 user 表，所以只要在任意一台主库上执行下面的操作，其他库就都有这俩账号了。 
    这里我在master1上进行的操作 
    mysql> grant replication client on *.* to 'mmm_monitor'@'192.168.0.%' identified by 'oldboy123';
    mysql> grant super, replication client, process on *.* to 'mmm_agent'@'192.168.0.%' identified by 'oldboy123';

3.2 所有服务器均需配置/etc/mysql-mmm/mmm_common.conf

    active_master_role writer 
    <host default> 
    cluster_interface eth0 
    pid_path /var/run/mysql-mmm/mmm_agentd.pid 
    bin_path /usr/libexec/mysql-mmm/ 
    replication_user rep                    #同步用户
    replication_password oldboy123          #同步密码
    agent_user mmm_agent                # mmm_agent用户
    agent_password oldboy123            # mmm_agent用户的密码
    </host> 
    <host db1> 
    ip 192.168.0.60                     # db1 master-1的物理ip
    mode master 
    peer db2 
    </host> 
    <host db2> 
    ip 192.168.0.61                     # db2 master-2的物理ip
    mode master 
    peer db1 
    </host> 
    <host db3> 
    ip 192.168.0.62                     # db3 slave的物理ip
    mode slave 
    </host> 
    <role writer> 
    hosts db1, db2 
    ips 192.168.0.20              #写入的虚拟IP地址 VIP
    mode exclusive 
    </role> 
    <role reader> 
    hosts db1, db2, db3 
    ips 192.168.0.21, 192.168.0.22, 192.168.0.23     #虚拟读取IP地址
    mode balanced 
    </role>

3.3 所有服务器均需配置/etc/mysql-mmm/mmm_agent.conf

    vi /etc/mysql-mmm/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   #分别修改为本机的主机名，即db1、db2、db3和db4

3.4 monitor主机配置/etc/mysql-mmm/mmm_mon.conf

    [root@mysql-mmm-monitor tools]# cat /etc/mysql-mmm/mmm_mon.conf 
    include mmm_common.conf 
    <monitor> 
    ip 127.0.0.1 
    pid_path /var/run/mysql-mmm/mmm_mond.pid 
    bin_path /usr/libexec/mysql-mmm 
    status_path /var/lib/mysql-mmm/mmm_mond.status 
    ping_ips 192.168.0.60,192.168.0.61,192.168.0.62 #监控的IP
    auto_set_online 30 #切换浮动IP的时间（默认为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 mmm_monitor #mmm_monitor 用户
    monitor_password oldboy123 #mmm_monitor 密码
    </host> 
    debug 0

4. mysql-mmm 启动

4.1master-1,master-2,slave启动代理：

    所有数据库主机启动mmm-agent: 
    /etc/init.d/mysql-mmm-agent start 

    10.204.3.4:9989        0.0.0.0:*         LISTEN      2407/mmm_agentd  

4.2 monitor主机启动mmm-monitor

    /etc/init.d/mysql-mmm-monitor start

5. mysql-mmm 测试

        mmm_control命令监控mysql服务器状态 
        [root@mysql-mmm-monitor ~]# mmm_control show 
        db1(192.168.0.60) master/ONLINE. Roles: reader(192.168.0.23), writer(192.168.0.20) 
        db2(192.168.0.61) master/ONLINE. Roles: reader(192.168.0.22) 
        db3(192.168.0.62) slave/ONLINE. Roles: reader(192.168.0.21) 

        测试两个mysql服务器能否实现故障自动切换 
        将db1的mysql服务停止 
        [root@mysql-mmm-master1 tools]# /etc/init.d/mysqld stop 
        Shutting down MySQL... SUCCESS! 
        等待30秒在mysql-mmm-monitor服务器上进行监控查看 
        [root@mysql-mmm-monitor ~]# mmm_control show 
        db1(192.168.0.60) master/HARD_OFFLINE. Roles:
        db2(192.168.0.61) master/ONLINE. Roles: reader(192.168.0.22), writer(192.168.0.20) 
        db3(192.168.0.62) slave/ONLINE. Roles: reader(192.168.0.21), reader(192.168.0.23)

        slave检查master_host是否切换到了另一个主库地址： 
        [root@mysql-mmm-slave ~]# mysql -e "show slave status\G"|egrep "Master_Host:" 
        Master_Host: 192.168.0.61 

        恢复master-1(db1) 
        [root@mysql-mmm-master1 tools]# /etc/init.d/mysqld start 
        Starting MySQL... SUCCESS! 
        monitor端检查恢复情况 
        [root@mysql-mmm-monitor ~]# mmm_control show 
        db1(192.168.0.60) master/ONLINE. Roles: reader(192.168.0.21) 
        db2(192.168.0.61) master/ONLINE. Roles: reader(192.168.0.22), writer(192.168.0.20) 
        db3(192.168.0.62) slave/ONLINE. Roles: reader(192.168.0.23) 
        我们可以看到当db1恢复后就充当slave的角色了！只有当db2挂了以后db1又会担当起主服务器的写入功能。

6.mmm_control命令介绍

        valid commands are: 
        help - show this message 
        #查看帮助信息 
        ping - ping monitor 
        #ping监控 
        show - show status 
        #查看状态信息 
        checks [<host>|all [<check>|all]] - show checks status 
        #显示检查状态，包括（ping、mysql、rep_threads、rep_backlog） 
        set_online <host> - set host <host> online 
        #设置某host为online状态 
        set_offline <host> - set host <host> offline 
        #设置某host为offline状态 
        mode - print current mode. 
        #打印当前的模式，是ACTIVE、MANUAL、PASSIVE？ 
        #默认ACTIVE模式 
        set_active - switch into active mode. 
        #更改为active模式 
        set_manual - switch into manual mode. 
        #更改为manual模式 
        set_passive - switch into passive mode. 
        #更改为passive模式 
        move_role [--force] <role> <host> - move exclusive role <role> to host <host> 
        #更改host的模式，比如更改处于slave的mysql数据库角色为write 
        (Only use --force if you know what you are doing!) 
        set_ip <ip> <host> - set role with ip <ip> to host <host> 
        #为host设置ip，只有passive模式的时候才允许更改！

转载于:https://blog.51cto.com/zhongliang/2152920