mysql5.6基于主从复制的mmm高可用架构详解

小灰灰 2021-03-19 15:00 550阅读 0赞

MMM规划  
192.168.3.12 master  
192.168.3.13 slave1  
192.168.3.198 slave2

MMM部署步骤  
1.配置主主复制及主从同步集群  
2.安装主从节点所需要的支持包  
3.安装及配置MMM工具集  
4.运行MMM监控服务  
5.测试

一、环境初始化

![1087718-20170607151109856-386484322.png][]  
1、修改主机名

主机: master执行命令  
\# sed -i 's/HOSTNAME=.\*/HOSTNAME=master/g' /etc/sysconfig/network && hostname master  
主机: slave01执行命令  
\# sed -i 's/HOSTNAME=.\*/HOSTNAME=slave01/g' /etc/sysconfig/network && hostname slave01  
主机: slave02执行命令(centos7.3)  
\# hostnamectl set-hostname slave02

2、主机名解析

在manager上执行如下命令

[root@master ~]# cat >> /etc/hosts << EOF
    192.168.3.200 manager
    192.168.3.12 master
    192.168.3.13 slave01
    192.168.3.198 slave02
    EOF
    
    [root@master ~]# scp -o StrictHostKeyChecking=no /etc/hosts root@slave01:/etc/
    [root@master ~]# scp -o StrictHostKeyChecking=no /etc/hosts root@slave02:/etc/

二、规划mysql  
①安装mysql

1.yum安装相关依赖

yum -y install gcc gcc-c++ compat-gcc-34-g77 autoconf automake zlib zlib-devel libxml2-devel ncurses-devel libmcrypt-devel libmcrypt libtool-ltdl-devel* make cmake bison git openssl openssl-devel

2.编译安装

groupadd mysql
    useradd -r -g mysql mysql
    tar xf mysql-5.6.34.tar.gz
    cd mysql-5.6.34
    
    cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/data/mydata -DSYSCONFDIR=/etc -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DWITH_SSL=system -DWITH_ZLIB=system -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DMYSQL_TCP_PORT=3306 -DENABLED_LOCAL_INFILE=1 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DWITH_LIBWRAP=0 -DDEFAULT_COLLATION=utf8_general_ci
    
    make && make install
    
    mkdir /data/mydata -p
    mkdir /data/binlogs/

\# slave节点要创建/data/relaylogs

mkdir /data/relaylogs
    
    chown -R mysql.mysql /usr/local/mysql
    chown -R mysql.mysql /data/
    
    cd /usr/local/mysql
    scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/mydata
    cp support-files/mysql.server /etc/rc.d/init.d/mysqld
    cp support-files/my-default.cnf /etc/my.cnf

修改配置  
  
mysql master配置：

[root@master ~]# cat /etc/my.cnf 
    [client]
    port = 3306
    socket = /tmp/mysql.sock
    default-character-set = utf8mb4
    
    [mysqld]
    port = 3306
    innodb_file_per_table = 1
    auto-increment-increment = 2
    auto-increment-offset = 1
    binlog-format=ROW
    log-slave-updates=true
    master-info-repository=table
    relay-log-info-repository=table
    sync-master-info=1
    slave-parallel-workers=4
    binlog-checksum=CRC32
    master-verify-checksum=1
    slave-sql-verify-checksum=1
    binlog-rows-query-log_events=1
    server-id=100
    report-port=3306
    log-bin=/data/binlogs/master-bin
    max_binlog_size = 200M
    datadir=/data/mydata
    socket=/tmp/mysql.sock
    sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
    
    
    init-connect = 'SET NAMES utf8mb4'
    character-set-server = utf8mb4
    
    skip-name-resolve
    skip-external-locking
    
    back_log = 300
    max_connections = 1024
    max_connect_errors = 6000
    open_files_limit = 65535
    table_open_cache = 128
    max_allowed_packet = 4M
    binlog_cache_size = 1M
    max_heap_table_size = 8M
    tmp_table_size = 16M
    
    read_buffer_size = 2M
    read_rnd_buffer_size = 8M
    sort_buffer_size = 8M
    join_buffer_size = 8M
    key_buffer_size = 4M
    thread_cache_size = 8
    query_cache_type = 1
    query_cache_size = 16M
    query_cache_limit = 2M
    ft_min_word_len = 4
    expire_logs_days = 10
    performance_schema = 0
    explicit_defaults_for_timestamp
    
    default_storage_engine = InnoDB
    innodb_open_files = 500
    innodb_buffer_pool_size = 64M
    innodb_write_io_threads = 4
    innodb_read_io_threads = 4
    innodb_thread_concurrency = 4
    innodb_purge_threads = 1
    innodb_flush_log_at_trx_commit = 2
    innodb_log_buffer_size = 2M
    innodb_log_file_size = 32M
    innodb_log_files_in_group = 3
    innodb_max_dirty_pages_pct = 90
    innodb_lock_wait_timeout = 120
    
    bulk_insert_buffer_size = 8M
    myisam_sort_buffer_size = 8M
    myisam_max_sort_file_size = 512M
    myisam_repair_threads = 1
    
    interactive_timeout = 28800
    wait_timeout = 28800
    
    [mysqldump]
    quick
    max_allowed_packet = 16M
    
    [myisamchk]
    key_buffer_size = 8M
    sort_buffer_size = 8M
    read_buffer = 4M
    write_buffer = 4M

mysql slave01 配置：

[root@slave01 src]# cat /etc/my.cnf 
    [client]
    port = 3306
    socket = /tmp/mysql.sock
    default-character-set = utf8mb4
    
    [mysqld]
    
    port = 3306
    innodb_file_per_table = 1
    binlog-format=ROW
    read_only=on
    log-slave-updates=true
    auto-increment-increment = 2
    auto-increment-offset = 2
    master-info-repository=table
    relay-log-info-repository=table
    sync-master-info=1
    slave-parallel-workers=4
    binlog-checksum=CRC32
    master-verify-checksum=1
    slave-sql-verify-checksum=1
    binlog-rows-query-log_events=1
    server-id=200
    report-port=3306
    log-bin=/data/binlogs/master-bin
    relay-log=/data/relaylogs/relay-bin
    max_binlog_size = 200M
    datadir=/data/mydata
    socket=/tmp/mysql.sock
    sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
    
    init-connect = 'SET NAMES utf8mb4'
    character-set-server = utf8mb4
    
    skip-name-resolve
    skip-external-locking
    
    back_log = 300
    max_connections = 1024
    max_connect_errors = 6000
    open_files_limit = 65535
    table_open_cache = 128
    max_allowed_packet = 4M
    binlog_cache_size = 1M
    max_heap_table_size = 8M
    tmp_table_size = 16M
    
    read_buffer_size = 2M
    read_rnd_buffer_size = 8M
    sort_buffer_size = 8M
    join_buffer_size = 8M
    key_buffer_size = 4M
    thread_cache_size = 8
    query_cache_type = 1
    query_cache_size = 16M
    query_cache_limit = 2M
    ft_min_word_len = 4
    expire_logs_days = 10
    performance_schema = 0
    explicit_defaults_for_timestamp
    
    default_storage_engine = InnoDB
    innodb_open_files = 500
    innodb_buffer_pool_size = 64M
    innodb_write_io_threads = 4
    innodb_read_io_threads = 4
    innodb_thread_concurrency = 4
    innodb_purge_threads = 1
    innodb_flush_log_at_trx_commit = 2
    innodb_log_buffer_size = 2M
    innodb_log_file_size = 32M
    innodb_log_files_in_group = 3
    innodb_max_dirty_pages_pct = 90
    innodb_lock_wait_timeout = 120
    
    bulk_insert_buffer_size = 8M
    myisam_sort_buffer_size = 8M
    myisam_max_sort_file_size = 512M
    myisam_repair_threads = 1
    
    interactive_timeout = 28800
    wait_timeout = 28800
    
    [mysqldump]
    quick
    max_allowed_packet = 16M
    
    [myisamchk]
    key_buffer_size = 8M
    sort_buffer_size = 8M
    read_buffer = 4M
    write_buffer = 4M

Mysql slave02配置

[root@slave02 ~]# cat /etc/my.cnf 
    [client]
    port = 3306
    socket = /tmp/mysql.sock
    default-character-set = utf8mb4
    
    [mysqld]
    
    port = 3306
    innodb_file_per_table = 1
    binlog-format=ROW
    log-slave-updates=true
    master-info-repository=table
    relay-log-info-repository=table
    sync-master-info=1
    slave-parallel-workers=4
    binlog-checksum=CRC32
    master-verify-checksum=1
    slave-sql-verify-checksum=1
    binlog-rows-query-log_events=1
    server-id=198
    report-port=3306
    log-bin=/data/binlogs/master-bin
    relay-log=/data/relaylogs/relay-bin # 从节点要配置relay-log
    max_binlog_size = 200M
    datadir=/data/mydata
    socket=/tmp/mysql.sock
    sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
    
    init-connect = 'SET NAMES utf8mb4'
    character-set-server = utf8mb4
    
    skip-name-resolve
    skip-external-locking
    
    back_log = 300
    max_connections = 1024
    max_connect_errors = 6000
    open_files_limit = 65535
    table_open_cache = 128
    max_allowed_packet = 4M
    binlog_cache_size = 1M
    max_heap_table_size = 8M
    tmp_table_size = 16M
    
    read_buffer_size = 2M
    read_rnd_buffer_size = 8M
    sort_buffer_size = 8M
    join_buffer_size = 8M
    key_buffer_size = 4M
    thread_cache_size = 8
    query_cache_type = 1
    query_cache_size = 16M
    query_cache_limit = 2M
    ft_min_word_len = 4
    expire_logs_days = 10
    performance_schema = 0
    explicit_defaults_for_timestamp
    
    default_storage_engine = InnoDB
    innodb_open_files = 500
    innodb_buffer_pool_size = 64M
    innodb_write_io_threads = 4
    innodb_read_io_threads = 4
    innodb_thread_concurrency = 4
    innodb_purge_threads = 1
    innodb_flush_log_at_trx_commit = 2
    innodb_log_buffer_size = 2M
    innodb_log_file_size = 32M
    innodb_log_files_in_group = 3
    innodb_max_dirty_pages_pct = 90
    innodb_lock_wait_timeout = 120
    
    bulk_insert_buffer_size = 8M
    myisam_sort_buffer_size = 8M
    myisam_max_sort_file_size = 512M
    myisam_repair_threads = 1
    
    interactive_timeout = 28800
    wait_timeout = 28800
    
    [mysqldump]
    quick
    max_allowed_packet = 16M
    
    [myisamchk]
    key_buffer_size = 8M
    sort_buffer_size = 8M
    read_buffer = 4M
    write_buffer = 4M

配置从数据库服务器推荐配置项：  
log-slave-updates = on  
read\_only=on \[建议\]  
masterjnfo—repository =TABLE \[建议\]  
relay-log-info-repository=TABLE \[建议\]

主主配置相关参数：  
master参数：  
auto-increment-increment = 2 \# 步长  
auto-increment-offset = 1 \# 起始值

slave01备用主库：  
auto-increment-increment = 2  
auto-increment-offset = 2

每个节点的server-id必须不同  
server-id=198  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

启动服务，保证master和两个slave节点都能正常启动  
/etc/init.d/mysqld start

3.配置master、slave01和slave02之间基于binlog的主从复制  
在MySQL5.6 的Replication配置中，master端同样要开启两个重要的选项，server-id和log-bin,并且选项server-id在全局架构中并且唯一，不能被其它主机使用，这里采用主机ip地址的最后一位充当server-id的值；slave端要开启relay-log

在master建立主从复制账号

mysql> create user repl@'192.168.3.%' identified by 'replpass';
    
    mysql> grant replication slave on *.* to repl@'192.168.3.%';

如果数据库有数据的情况下需要先对数据库进行备份，然后恢复到几个从库中：

方法①:

[root@master ~]# mysqldump --single-transaction --set-gtid-purged=OFF --master-data=2 --triggers --routines --all-databases -uroot -p >all2.sql
    Enter password:

在slave01和slave02上还原  
mysql -uroot -p < all2.sql

方法二：percona-xtrabackup

4.在slave01和slave02上执行主从同步

master:
    mysql> show master status;
    +-------------------+----------+--------------+------------------+------------------------------------------+
    | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +-------------------+----------+--------------+------------------+------------------------------------------+
    | master-bin.000017 | 191 | | | 4c6237f8-a7da-11e6-9966-000c29f333f8:1-2 |
    +-------------------+----------+--------------+------------------+------------------------------------------+

slave01:

mysql> change master to master_host='192.168.3.12',master_user='repl',master_password='replpass',master_log_file='master-bin.000017',master_log_pos=191;
    mysql> start slave;
    mysql> show slave status\G
    *************************** 1. row ***************************
    Slave_IO_State: Waiting for master to send event
    Master_Host: 192.168.3.12
    Master_User: repl
    Master_Port: 3306
    Connect_Retry: 60
    Master_Log_File: master-bin.000017
    Read_Master_Log_Pos: 191
    Relay_Log_File: relay-bin.000002
    Relay_Log_Pos: 315
    Relay_Master_Log_File: master-bin.000017
    Slave_IO_Running: Yes # 表示主从ok
    Slave_SQL_Running: Yes

slave02:

mysql> change master to master_host='192.168.3.12',master_user='repl',master_password='replpass',master_log_file='master-bin.000017',master_log_pos=191;
    mysql> start slave;
    mysql> show slave status\G
    *************************** 1. row ***************************
    Slave_IO_State: Waiting for master to send event
    Master_Host: 192.168.3.12
    Master_User: repl
    Master_Port: 3306
    Connect_Retry: 60
    Master_Log_File: master-bin.000017
    Read_Master_Log_Pos: 191
    Relay_Log_File: relay-bin.000002
    Relay_Log_Pos: 315
    Relay_Master_Log_File: master-bin.000017
    Slave_IO_Running: Yes 
    Slave_SQL_Running: Yes

配置master和slave01之间的主主复制：

slave01上查看状态

mysql> show master status\G
    *************************** 1. row ***************************
    File: master-bin.000016
    Position: 120
    Binlog_Do_DB: 
    Binlog_Ignore_DB: 
    Executed_Gtid_Set: 17062665-3b2a-11e7-99fe-000c29ba4e78:1-2202,
    4c6237f8-a7da-11e6-9966-000c29f333f8:3-681:684-690
    1 row in set (0.00 sec)

master上执行主从同步：

mysql> change master to master_host='192.168.3.13',master_user='repl',master_password='replpass',master_log_file='master-bin.000016',master_log_pos=120;
    Query OK, 0 rows affected, 2 warnings (0.22 sec)
    
    mysql> start slave;
    Query OK, 0 rows affected, 1 warning (0.01 sec)
    
    mysql> show slave status\G
    *************************** 1. row ***************************
    Slave_IO_State: Waiting for master to send event
    Master_Host: 192.168.3.13
    Master_User: repl
    Master_Port: 3306
    Connect_Retry: 60
    Master_Log_File: master-bin.000016
    Read_Master_Log_Pos: 120
    Relay_Log_File: master-relay-bin.000002
    Relay_Log_Pos: 284
    Relay_Master_Log_File: master-bin.000016
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes

三、安装配置mmm

1.在每个节点上都安装mmm客户端

# yum install -y mysql-mmm-agent.noarch

2.slave02上安装mysql-mmm即监控端monitor

# yum install mysql-mmm* -y

注意：经过测试在centos7上yum方式安装监控无法启动，源码编译安装也有各种问题，最好使用centos6

3.主库上建立mmm\_agent和mmm\_monitor用户：

创建mmm_agent用户
    mysql> grant super,replication client,process on *.* to 'mmm_agent'@'192.168.3.%' identified by '123456';
    创建mmm_monitor用户
    mysql> grant replication client on *.* to 'mmm_monitor'@'192.168.3.%' identified by '123456';

4.修改配置

①修改/etc/mysql-mmm/mmm\_common.conf配置文件，并拷贝到slave01和slave02上

# vim /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 repl # 
    replication_password replpass
    agent_user mmm_agent
    agent_password 123456
    </host>
    
    <host db1>
    ip 192.168.3.12
    mode master
    peer db2
    </host>
    
    <host db2>
    ip 192.168.3.13
    mode master
    peer db1
    </host>
    
    <host db3>
    ip 192.168.3.198
    mode slave
    </host>
    
    <role writer>
    hosts db1, db2
    ips 192.168.3.90
    mode exclusive
    </role>
    
    <role reader>
    hosts db1, db2, db3
    ips 192.168.3.91,192.168.3.92,192.168.3.93
    mode balanced
    </role>

②修改/etc/mysql-mmm/mmm\_agent.conf master、slave01和slave02内容分别是 this db1/this db2/this db3

master

# cat /etc/mysql-mmm/mmm_agent.conf 
    include mmm_common.conf
    
    this db1

slave01

# cat /etc/mysql-mmm/mmm_agent.conf 
    include mmm_common.conf
    
    this db2

slave01

# cat /etc/mysql-mmm/mmm_agent.conf 
    include mmm_common.conf
    
    this db3

③修改slave02即monitor的配置

[root@slave02 ~]# cat /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.3.12,192.168.3.13,192.168.3.198 # 三个节点的IP
    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 mmm_monitor # 监控用户
    monitor_password 123456    # 监控密码
    </host>
    
    debug 0

启动所有节点上的mmm\_agent服务  
\# /etc/init.d/mysql-mmm-agent start

启动slave02的monitor服务  
\# /etc/init.d/mysql-mmm-monitor start

四、进行主主切换测试：  
监控节点执行检测功能，可以看到master有3.90写角色和3.92读的角色

[root@slave02 mysql-mmm]# mmm_control show
    db1(192.168.3.12) master/ONLINE. Roles: reader(192.168.3.92), writer(192.168.3.90)
    db2(192.168.3.13) master/ONLINE. Roles: reader(192.168.3.93)
    db3(192.168.3.198) slave/ONLINE. Roles: reader(192.168.3.91)

1.停止master上的mysql服务

[root@master ~]# /etc/init.d/mysqld stop
    Shutting down MySQL.. [ OK ]
    [root@master ~]# /etc/init.d/mysqld status
    MySQL is not running [FAILED]

再次查看监控节点状态，可以看到主节点已经切换到了slave01上

[root@slave02 mysql-mmm]# mmm_control show
    db1(192.168.3.12) master/HARD_OFFLINE. Roles: 
    db2(192.168.3.13) master/ONLINE. Roles: reader(192.168.3.93), writer(192.168.3.90)
    db3(192.168.3.198) slave/ONLINE. Roles: reader(192.168.3.91), reader(192.168.3.92)

通过在slave02上查看从库状态也可以看到主服务器已经重新指向了slave02

2.重新启动原master节点上的mysql服务，主节点也不会切回原节点

[root@slave02 mysql-mmm]# mmm_control show
    db1(192.168.3.12) master/ONLINE. Roles: reader(192.168.3.91)
    db2(192.168.3.13) master/ONLINE. Roles: reader(192.168.3.93), writer(192.168.3.90)
    db3(192.168.3.198) slave/ONLINE. Roles: reader(192.168.3.92)

[1087718-20170607151109856-386484322.png]: /images/20210319/1616166043250.png

发表评论取消回复

表情：

评论列表（有 0 条评论，550人围观）

还没有评论，来说两句吧...

相关阅读

相关 MySQL主从复制基于二进制日志的高可用架构指南

前言在现代数据库架构中，MySQL主从复制技术扮演着重要角色。它不仅可以提升数据库性能和可扩展性，还赋予系统卓越的高可用性和灾难恢复能力。本文将深入剖析MySQL主从复

我不是女神ヾ/ 2023年10月14日 13:27/ 0 赞/ 56 阅读

相关 MySQL高可用——MMM

MMM 即 Multi-Master Replication Manager for MySQL:mysql 多主复制管理器,基于 perl 实现,关于 mysql 主主复制配

太过爱你忘了你带给我的痛/ 2023年09月28日 21:38/ 0 赞/ 13 阅读

相关 mysql高可用——MMM架构部署

mysql高可用架构有MHA、MMM、PXC、MGR等，其中MHA和MMM的数据一致性相比较PXC和MGR要差一些，因为PXC和MGR是强一致性，其实现原理这里不过多介绍，这篇

- 日理万妓/ 2022年11月09日 14:24/ 0 赞/ 313 阅读

相关高可用架构-- MySQL主从复制的配置

视频资源：[http://www.roncoo.com/course/view/3117ffd4c74b4a51a998f9276740dcfb][http_www.ronc

忘是亡心i/ 2022年07月14日 12:20/ 0 赞/ 208 阅读

相关 MySQL双主高可用架构之MMM实战

MMM简介： MMM即Master-Master Replication Manager for MySQL（mysql主主复制管理器），是关于mysql主主复

淡淡的烟草味﹌/ 2022年06月07日 07:43/ 0 赞/ 272 阅读

相关 mysql高可用详解（七）：高可用的复制

【冗余】如果一个组件出现故障，必须有一个替代品。该替代品既可以是闲置的，也可以是当前系统部署的一部分；【应急计划】为每个紧急事件都做好了计划

迷南。/ 2022年05月18日 05:39/ 0 赞/ 323 阅读

相关 MySQL 高可用架构之MMM

简介 MMM（Master-Master replication manager for MySQL）是一套支持双主故障切换和双主日常管理的脚本程序。MMM使用Perl语言开

谁践踏了优雅/ 2022年05月14日 02:09/ 0 赞/ 304 阅读

相关 MySQL学习（三）：主从复制Replication实现高可用的架构模式

概述 MySQL主从复制主要用于实现读写分离，所以主从架构一般为一主多从，即主库负责处理所有的写入请求，一个或多个从库负责处理所有的读请求，从而分散读写流量，实现读

川长思鸟来/ 2022年03月10日 07:29/ 0 赞/ 228 阅读

相关浅谈MySQL高可用复制架构

MySQL已然是目前业界最为流行的开源数据库，基于其强大的复制与集群架构构建的高可用数据中心正在被越来越多的公司所采纳。无独有偶，我们公司也正在为客户构建一个低成本、高可用的数

- 日理万妓/ 2022年02月13日 23:41/ 0 赞/ 310 阅读

相关 mysql5.6基于主从复制的mmm高可用架构详解

MMM规划 192.168.3.12 master 192.168.3.13 slave1 192.168.3.198 slave2 MMM部署步骤 1.配置主主复制及...

小灰灰/ 2021年03月19日 15:00/ 0 赞/ 551 阅读