MySQL高可用MMM双主复制

参考资料：https://www.cnblogs.com/sunnydou/p/28f747c35471965e0610eda5efa06a15.html

一：环境

MySQL 5.7

Centos7

监控机：

192.168.6.143 mon01

数据库：
192.168.6.139 db1 （主节点）
192.168.6.138 db2 （备用主节点）
192.168.6.140 db3 （读节点）
192.168.6.141 db4 （读节点）
192.168.6.142 db5 （读节点）

虚拟IP：

192.168.6.100 writer

192.168.6.101 reader

192.168.6.102 reader

二：配置服务器上的MySQL

安装MySQL，使用yum源安装

echo '''[mysql-release-$basearch] 
name = Percona-Release YUM repository - $basearch 
baseurl = https://mirror.tuna.tsinghua.edu.cn/mysql/yum/mysql57-community-el7/
gpgcheck=0''' > /etc/yum.repos.d/mysql.repo 

安装MySQL

yum -y install mysql mysql-server

编辑配置文件/etc/my.cnf ，注意server-id 值每个数据库节点要不一致

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
basedir=/var/lib/mysql
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
pid-file=/var/run/mysqld/mysqld.pid

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

default-storage-engine = InnoDB

########basic settings########
#服务器ID，不能重复
server-id = 1
character_set_server=utf8mb4
skip_name_resolve = 1
max_connections = 1000
#MySQL读入缓冲区的大小
read_buffer_size = 16M
#MySQL的随机读缓冲区大小
read_rnd_buffer_size = 8M
#MySQL的顺序读缓冲区大小
sort_buffer_size = 8M

####innodb配置#################
innodb_buffer_pool_size=2G
innodb_log_file_size=256M
innodb_flush_method=O_DIRECT
#innodb独享表空间，优点很多，缺点会导致单个表文件过大
innodb_file_per_table = 1
# 默认monitor会控制mmm_agent会将writer db服务器read_only修改为OFF，其它的db服务器read_only修改为ON
# 所以为了严谨可以在所有的服务器的my.cnf文件中加入read_only=1由monitor控制来控制writer和read,root用户和复制用户不受read_only参数的影响
read_only=1

########log settings########
log_error = /var/log/mysql/error.log
#开启慢查询日志
slow_query_log = 1
#超出次设定值的SQL即被记录到慢查询日志
long_query_time = 5
slow_query_log_file = /var/log/mysql/slow.log
#表示记录下没有使用索引的查询
log_queries_not_using_indexes = 1
#记录管理语句
log_slow_admin_statements = 1
#开启复制从库复制的慢查询的日志
log_slow_slave_statements = 1
#设置每分钟增长的没有使用索引查询的日志数量
log_throttle_queries_not_using_indexes = 10
expire_logs_days = 90
min_examined_row_limit = 100

log-bin = mysql-bin
log-bin-index = mysql-bin.index
max-binlog-size = 100M
# binlog-do-db = test
# binlog-ignore-db = mysql
sync-binlog = 1
relay-log = mysql-relay
relay-log-index = mysql-relay.index
log-slave-updates = 1
# replicate-do-db = test
# replicate-ignore-db = mysql
#设置为主节点人数
auto_increment_increment = 2
#在每台服务器上设置为唯一的递增数字，小于auto_increment_increment（主节点为1，备用主节点为2）
auto_increment_offset = 1

启动MySQL

mkdir /var/log/mysql && chown -R mysql.mysql /var/log/mysql
systemctl start mysqld
systemctl status mysqld
systemctl stop mysqld # 关闭mysql服务
systemctl start mysqld # 开启mysql服务
systemctl enable mysqld # 将mysql设置为服务

开放3306端口

iptables -I INPUT -s 0/0 -p tcp --dport 3306 -j ACCEPT

查看mysql初始密码

grep 'temporary password' /var/log/mysql/error.log

 登录并修改密码

mysql -uroot -p

set password for 'root'@'localhost'=password('123456');

设置其它用于复制和监听的账号

GRANT REPLICATION CLIENT                 ON *.* TO 'mmm_monitor'@'192.168.6.%' IDENTIFIED BY 'monitor_password';
GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO 'mmm_agent'@'192.168.6.%'   IDENTIFIED BY 'agent_password';
GRANT REPLICATION SLAVE                  ON *.* TO 'replication'@'192.168.6.%' IDENTIFIED BY 'replication_password';
flush privileges;
exit;

三：设置数据库节点之间的复制关系

所有服务器节点配置Host

vim /etc/hosts

192.168.6.143 mon01
192.168.6.139 db1
192.168.6.138 db2
192.168.6.140 db3
192.168.6.141 db4
192.168.6.142 db5

数据准备

db1主库安装测试用例库

yum install -y wget unzip
cd /tmp
wget https://downloads.mysql.com/docs/world.sql.zip -O world.sql.zip
unzip world.sql.zip
mysql -u root -p < /tmp/world.sql

db1主库，获取二进制日志文件的位置

SHOW MASTER STATUS;

 导出全量备份

mysqldump -u root -p --all-databases> /tmp/database-backup.sql

将备份复制到其它服务器，db2db3db4db5

scp /tmp/database-backup.sql  root@192.168.6.138:/tmp 
scp /tmp/database-backup.sql  root@192.168.6.140:/tmp 
scp /tmp/database-backup.sql  root@192.168.6.141:/tmp
scp /tmp/database-backup.sql  root@192.168.6.142:/tmp

然后在其它服务器上进行数据恢复

mysql -u root -p < /tmp/database-backup.sql
mysql -u root -p < /tmp/database-backup.sql 
mysql -u root -p < /tmp/database-backup.sql 
mysql -u root -p < /tmp/database-backup.sql 

并刷新权限

flush privileges;
flush privileges;
flush privileges;
flush privileges;

安装复制

在db2db3db4db5上配置复制

CHANGE MASTER TO master_host ='192.168.6.139',
master_port = 3306,
master_user ='replication',
master_password ='replication_password',
master_log_file ='mysql-bin.000006',
master_log_pos = 692946; 

并启动slave进程

START SLAVE; 

检查复制是否正确运行：

show slave statusG

 配置db1和db2的双主复制

在db2上，获取二进制日志文件的位置

SHOW MASTER STATUS;

 在db1上配置slave进程，并启动

CHANGE MASTER TO master_host ='192.168.6.138',
master_port = 3306,
master_user ='replication',
master_password ='replication_password',
master_log_file ='mysql-bin.000003',
master_log_pos = 1014732; 
START SLAVE;

检查复制是否正确运行：

show slave statusG

四：安装MMM

epel源安装

CentOS默认没有mysql-mmm软件包，推荐使用epel的网络源，6台都按照epel:

yum -y install wget
wget https://mirror.tuna.tsinghua.edu.cn/epel/7/x86_64/Packages/e/epel-release-7-12.noarch.rpm
wget https://mirror.tuna.tsinghua.edu.cn/epel/RPM-GPG-KEY-EPEL-7
rpm --import RPM-GPG-KEY-EPEL-7
rpm -ivh epel-release-7-12.noarch.rpm

在监控主机mon01上安装控制器

yum install mysql-mmm mysql-mmm-agent mysql-mmm-tools mysql-mmm-monitor -y

在所有数据库节点上安装监听器

yum install -y mysql-mmm-agent

配置MMM

通用配置文件/etc/mysql-mmm/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        replication
    replication_password    replication_password 
    agent_user              mmm_agent
    agent_password          agent_password
</host>

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

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

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

<host db4>
    ip      192.168.6.141
    mode    slave
</host>
<host db5>
    ip      192.168.6.142
    mode    slave
</host>

<role writer>
    hosts   db1, db2
    ips     192.168.6.100
    mode    exclusive   # exclusive代表只允许存在一个主，也就是只能提供一个写的IP
</role>

<role reader>
    hosts   db3, db4, db5
    ips     192.168.6.101,192.168.6.102
    mode    balanced   # balanced代表负载均衡
</role>

将配置拷贝到其它服务器（包括监控节点）

scp /etc/mysql-mmm/mmm_common.conf root@192.168.6.138:/etc/mysql-mmm/
scp /etc/mysql-mmm/mmm_common.conf root@192.168.6.140:/etc/mysql-mmm/
scp /etc/mysql-mmm/mmm_common.conf root@192.168.6.141:/etc/mysql-mmm/
scp /etc/mysql-mmm/mmm_common.conf root@192.168.6.142:/etc/mysql-mmm/
scp /etc/mysql-mmm/mmm_common.conf root@192.168.6.143:/etc/mysql-mmm/

在各数据库节点，编辑/etc/mysql-mmm/mmm_agent.conf，在其它数据库节点上修改为对应的名称db2等等

include mmm_common.conf
# 设置为本机的名称
this db1

在监控节点（mon01)上，编辑/etc/mysql-mmm/mmm_mon.conf

include mmm_common.conf

<monitor>
    ip                  192.168.6.143
    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.6.139, 192.168.6.138, 192.168.6.140, 192.168.6.141, 192.168.6.142
    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>

<host default>
    monitor_user        mmm_monitor
    monitor_password    monitor_password
</host>

debug 0

启动MMM

在所有数据库节点上，启动agent

systemctl start mysql-mmm-agent
systemctl enable mysql-mmm-agent

查看端口

iptables -I INPUT -s 0/0 -p tcp --dport 9989 -j ACCEPT # 开放9989端口
yum install -y net-tools
netstat -anop |grep 9989  # 查看9989端口进程

在监控节点（mon01)上，启动monitor

systemctl start mysql-mmm-monitor
systemctl enable mysql-mmm-monitor

等待几秒让mmm启动(配置文件中auto_set_online=10，等待10秒就会自动联机）。几秒之后，检查集群状态

mmm_control show

五：验证MMM

 查看集群节点状态

mmm_control checks all

 对读节点进行offline/online操作

mmm_control set_offline db3
mmm_control show

mmm_control set_online db3
mmm_control show

 对写节点进行offline/online操作

db1的master自动切换到db2，db1重新online，master不会逆转，需要手动切换

手动切换writer

mmm_control move_role writer db1