MySQL-MMM高可用
MMM 简介
成都创新互联公司-专业网站定制、快速模板网站建设、高性价比盘龙网站开发、企业建站全套包干低至880元,成熟完善的模板库,直接使用。一站式盘龙网站制作公司更省心,省钱,快速模板网站建设找我们，业务覆盖盘龙地区。费用合理售后完善，十多年实体公司更值得信赖。
MMM （Master-Master relication manager fro MySQL ,MySQK 主主复制管理器）是一套支持双主故障切换和双主日常管理的脚步程序。主要是用来监控和管理MySQL Master-Master （双主）复制，虽然叫做双主复制，到时业务上同一时刻值允许对一个主进行写入，另一台备选主提供部分读服务，以加速在主主切换时备选主的预热。一方面实现了故障切换的功能，也可实现多个 Slave 的read 负载均衡。
关于MMM 高可用架构的说明如下：
mmm_mon :监控进程，负载所有的监控工作，绝对和处理所有节点角色活动。此脚本需要在监管机上运行。
mmm_agent:运行在每个MySQL 服务器上的代理进程，完成监控的探针工作和执行简单的远程服务设置。此脚本需要在被监管机上运行。
mmm_control:提供管理 mmm_mond 进程命令。
mysql-mmm 的监管端会提供多个虚拟IP（VIP),包括一个可写VIP,多个可读VIP,这些IP会绑定在可用MySQL 上，当某一台MySQL 宕机时，监管会将VIP迁移到其他 MySQL。
案例环境
本案例环境使用五台服务器模拟搭建：
主机 操作系统 IP 地址 主要软件
mysql-master1 centos7x86_64 192.168.213.174 mysql-mmm*
mysql-master2 centos7x86_64 192.168.213.177 mysql-mmm*
mysql-slave 1 centos7x86_64 192.168.213.179 mysql-mmm*
mysql-slave 2 centos7x86_64 192.168.213.173 mysql-mmm*
mysql-monitor centos7x86_64 192.168.213.178 mysql-mmm*
一. 搭建MySQL 多主多从模式
（1）在线下载安装epel 源,五台服务器都要安装。
[root@master1 ~]# wget -O /etc/yum.repos.d/CentOS-Base.repohttp://mirrors.aliyun.com/repo/Centos-7.repo
#将aliyun 的 yum源下载到本地网络yum 源
[root@master1 ~]# yum -y install epel-release #下载epel 源
[root@master1 ~]# yum clean all && yum makecache #清空所有，重新设置原数据缓存
（2）下载 mariadb (mysql 的分支），关闭防火墙功能 （mariadb 只在主从4个服务器上安装）
[root@master1 ~]# yum -y install mariadb-server mariadb
[root@master1 ~]# systemctl stop firewalld.service #关闭防火墙
[root@master1 ~]# setenforce 0
（3）修改 mariadb 的配置文件
[root@master1 ~]# vim /etc/my.cnf
[mysqld]
log_error=/var/lib/mysql/mysql.err #错误日志路径
log=/var/lib/mysql/mysql_log.log #通用查询日志
log_slow_queries=/var/lib/mysql_slow_queris.log #慢查询日志路径
binlog-ignore-db=mysql,information_schema #不需要同步的数据库名称
character_set_server=utf8 #默认字符集为 utf8
log_bin=mysql_bin #启用二进制日志
server_id=1 #服务器id , 注意：每台服务器的id 都不一样，确保唯一性
log_slave_updates=ture #允许从服务器进行日志更新
sync_binlog=1 #允许从服务器同步二进制日志
auto_increment_increment=2 #字段一次递增多少
auto_increment_offset=1 #自增段的起始值
（4）启动mysql 服务，并查看默认端口是否开启
[root@master1 ~]# systemctl restart mariadb
[root@master1 ~]# netstat -ntap | grep 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 41241/mysqld
（5）设置数据库 root 用户登录的 密码
[root@master1 ~]# mysqladmin -u root password '123'
[root@master1 ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
其他三台数据库服务器安装与配置与 master1 相同，配置文件的其他参数都一样，仅 server-id 不同。因此可以在 master 1 服务器上传配置文件，注意修改 server-id.
[root@master01 ~]# scp -r /etc/my.cnfroot@192.168.213.177:/etc/my.cnf
[root@master01 ~]# scp -r /etc/my.cnfroot@192.168.213.179:/etc/my.cnf
[root@master01 ~]# scp -r /etc/my.cnfroot@192.168.213.173:/etc/my.cnf
二.配置 master 1 he master 2 主主模式
（1）先查看 log bin 日志和pos 值
master 1
MariaDB [(none)]> show master status;
+------------------+----------+--------------+--------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+--------------------------+
| mysql_bin.000003 | 554 | | mysql,information_schema |
+------------------+----------+--------------+--------------------------+
1 row in set (0.00 sec)
master 2
MariaDB [(none)]> show master status;
+------------------+----------+--------------+--------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+--------------------------+
| mysql_bin.000003 | 554| | mysql,information_schema |
+------------------+----------+--------------+--------------------------+
1 row in set (0.00 sec)
（2）master 1 和 master 2 互相提升访问权限
在m1上为m2授予从的权限
MariaDB [(none)]> grant replication slave on *.* to 'replication'@'192.168.213.%' identified by '123456';
Query OK, 0 rows affected (0.35 sec)MariaDB [(none)]> change master to master_host='192.168.213.177',master_user='replication',master_password='123456',master_log_file='mysql_bin.000003',master_log_pos= 554; #master 2 log bin 日志 和pos值
Query OK, 0 rows affected (0.02 sec)
在m2上也要为m1 授权从的权限
MariaDB [(none)]> grant replication slave on *.* to 'replication'@'192.168.213.%' identified by '123456';
Query OK, 0 rows affected (0.35 sec)MariaDB [(none)]> change master to master_host='192.168.213.174',master_user='replication',master_password='123456',master_log_file='mysql_bin.000003',master_log_pos= 554;
Query OK, 0 rows affected (0.02 sec)
（3）开启两台主主服务器同步功能，并查询主从状态
tart slave; //开启同步功能
show slave status\G;
Slave_I0_Running: Yes //这两个选项参数必须为yes
Slave_SQL_Running: Yes
主主同步配置完成，查看状态 Slave_IO 和 Slave_SQI 为 yes ,说明主主同步成功
三.配置从服务器 Slave1 和Slave2,作为 Master 1 的从库
查看 master1 的状态值
MariaDB [(none)]> show master status;
+------------------+----------+--------------+--------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+--------------------------+
| mysql_bin.000003 | 554 | | mysql,information_schema |
+------------------+----------+--------------+--------------------------+
1 row in set (0.00 sec)在 slave1 和slave2 分别做
MariaDB [(none)]> change master to master_host='192.168.213.174',master_user='replication',master_password='123456',master_log_file='mysql_bin.000003',master_log_pos= 554; #master1 的ip
Query OK, 0 rows affected (0.02 sec)
开启同步功能，并查看同步状态
start slave; //开启同步功能
show slave status\G;
Slave_I0_Running: Yes //这两个选项参数必须为yes
Slave_SQL_Running: Yes
四 .安装配置 MySQL-MMM
（1）安装MMM 。Centos 默认没有 mysql-mmm 软件包，使用epel 源，五台服务器都要安装 epel 和 MMM
[root@master1 ~]# yum install mysql-mmm* –y
（2）修改 MMM 的配置文件，系统中所有主机的该配置文件内容都是一样的，包括监控主机 mysql-monitor
[root@master1 ~]# cd /etc/mysql-mmm/ #配置文件路径
[root@master1 mysql-mmm]# vim mmm_common.conf
cluster_interface ens33 #网卡改为 ens33
pid_path /run/mysql-mmm-agent.pid
bin_path /usr/libexec/mysql-mmm/
replication_user replicant #Mariadb 给与权限的用户
replication_password 123456 #给与权限的用户密码
agent_user mmm_agent #agent 客户端代理用户
agent_password 123456 #代理用户密码
#主服务器 master1
ip 192.168.213.174
mode master #状态为：主
peer db2 #与主服务器master 2互相切换
#主服务器 master2
ip 192.168.213.177
mode master #状态为：主
peer db1
#从服务器 slave1
ip 192.168.213.179
mode slave #状态为：slave
#从服务器 slave2
ip 192.168.213.173
mode slave #状态为：slave
#写操作
hosts db1, db2 #主服务器master1 和master2 具有写的操作
ips 192.168.213.100 #指定虚拟IP
mode exclusive #指定模式，db1 和db2 只能存在一个IP地址，只能有一台主机进行写入操作
#读操作
hosts db3, db4
ips 192.168.213.110, 192.168.213.120 #虚拟IP
mode balanced #负载均衡，读取操作会从 db3 和db4 主机进行
（3）远程复制，将 mmm_common.conf 配置文件传送到其他4台主机上
[root@master1 mysql-mmm]# scp mmm_common.conf root@192.168.213.177:/etc/mysql-mmm/
The authenticity of host '192.168.213.177 (192.168.213.177)' can't be established.
ECDSA key fingerprint is SHA256:KM7QwLupjrfzZ2YQdMOoGKJtIUgtz2agvwTzZOPHu2k.
ECDSA key fingerprint is MD5:f1:32:f7:7f:b7:eb:4e:9e:2e:fa:7e:8a:56:88:fe:c1.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.213.177' (ECDSA) to the list of known hosts.
root@192.168.213.177's password: #对方root 用户登录密码
mmm_common.conf 100% 842 329.6KB/s 00:00
[root@master1 mysql-mmm]# scp mmm_common.conf root@192.168.213.179:/etc/mysql-mmm/
The authenticity of host '192.168.213.179 (192.168.213.179)' can't be established.
ECDSA key fingerprint is SHA256:HtLFtvYxQF5ER0eA1uKE8VgRx038LWpDYBbp1S1CrJ8.
ECDSA key fingerprint is MD5:23:41:18:56:8e:ed:f3:65:b1:5f:96:11:e9:11:cb:29.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.213.179' (ECDSA) to the list of known hosts.
root@192.168.213.179's password:
mmm_common.conf 100% 842 376.0KB/s 00:00
[root@master1 mysql-mmm]# scp mmm_common.conf root@192.168.213.173:/etc/mysql-mmm/
The authenticity of host '192.168.213.173 (192.168.213.173)' can't be established.
ECDSA key fingerprint is SHA256:w910JWPfehgM09d+OlOiC6q61NjELLHDh6LWojkuYL0.
ECDSA key fingerprint is MD5:94:a5:a1:e0:4d:14:cc:3c:ff:8c:24:e5:3e:e1:2b:cd.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.213.173' (ECDSA) to the list of known hosts.
root@192.168.213.173's password:
mmm_common.conf 100% 842 565.0KB/s 00:00[root@master1 mysql-mmm]# scp mmm_common.conf root@192.168.213.178:/etc/mysql-mmm/
The authenticity of host '192.168.213.178 (192.168.213.178)' can't be established.
ECDSA key fingerprint is SHA256:ABSTPGOHvqKvUsfwD/uf5ESPpdT1RjvucRpzMqcUuzI.
ECDSA key fingerprint is MD5:f5:3a:8c:8b:1e:d5:a3:33:24:32:03:2d:4d:3e:e8:68.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.213.178' (ECDSA) to the list of known hosts.
root@192.168.213.178's password:
mmm_common.conf 100% 842 277.8KB/s 00:00
（4）在4台数据库上为监控 monitor 授权访问
MariaDB [(none)]> grant super,replication client,process on *.* to 'mmm_agent'@'192.168.213.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> grant replication client on *.* to 'mmm_monitor'@'192.168.213.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> flush privileges; #刷新权限
Query OK, 0 rows affected (0.00 sec)
（5）在数据库主机上需要编辑 mmm_agent.conf 配置文件，根据不同主机修改为不同的值
[root@master1 mysql-mmm]# vim mmm_agent.conf
this db1 #分别修改为 db1 、db2 、db3 、db4
五 .监控服务器 monitor 配置
[root@localhost ~]# cd /etc/mysql-mmm/
[root@localhost mysql-mmm]# vim mmm_mon.conf
ping_ips 192.168.213.174,192.168.213.177,192.168.213.179,192.168.213.173 #监控所有数据库的 IP 地址
auto_set_online 10 #设置10秒上线
monitor_user mmm_monitor #用户名
monitor_password 123456 #密码
六 .启动监控和代理
（1）在所有数据库服务器启动 mysql-mmm-agent
[root@master1 mysql-mmm]# systemctl start mysql-mmm-agent.service
[root@master1 mysql-mmm]# systemctl enable mysql-mmm-agent.service
（2）启动监控服务 mysql-mmm-monitor
[root@localhost mysql-mmm]# systemctl start mysql-mmm-monitor.service
（3）测试群集
[root@localhost mysql-mmm]# mmm_control show
db1(192.168.213.174) master/ONLINE. Roles: writer(192.168.213.100) #虚拟IP
db2(192.168.213.177) master/ONLINE. Roles:
db3(192.168.213.179) slave/ONLINE. Roles: reader(192.168.213.110)
db4(192.168.213.173) slave/ONLINE. Roles: reader(192.168.213.120)#对应的真是的IP
检查状态都需要全部 OK
[root@localhost mysql-mmm]# mmm_control checks all
db4 ping [last change: 2018/09/10 10:50:52] OK
db4 mysql [last change: 2018/09/10 10:50:52] OK
db4 rep_threads [last change: 2018/09/10 10:50:52] OK
db4 rep_backlog [last change: 2018/09/10 10:50:52] OK: Backlog is null
db2 ping [last change: 2018/09/10 10:50:52] OK
db2 mysql [last change: 2018/09/10 10:50:52] OK
db2 rep_threads [last change: 2018/09/10 10:50:52] OK
db2 rep_backlog [last change: 2018/09/10 10:50:52] OK: Backlog is null
db3 ping [last change: 2018/09/10 10:50:52] OK
db3 mysql [last change: 2018/09/10 10:50:52] OK
db3 rep_threads [last change: 2018/09/10 10:50:52] OK
db3 rep_backlog [last change: 2018/09/10 10:50:52] OK: Backlog is null
db1 ping [last change: 2018/09/10 10:50:52] OK
db1 mysql [last change: 2018/09/10 10:50:52] OK
db1 rep_threads [last change: 2018/09/10 10:50:52] OK
db1 rep_backlog [last change: 2018/09/10 10:50:52] OK: Backlog is null
七 .故障测试
（1）停掉主 db1 的数据库，等待几秒，可以看到数据库 db1 处于 HARD_OFFLINE. Roles （离线状态），检测不到数据库的存在
[root@master1 mysql-mmm]# systemctl stop mariadb.service
[root@localhost mysql-mmm]# mmm_control show
db1(192.168.213.174) master/HARD_OFFLINE. Roles:
db2(192.168.213.177) master/ONLINE. Roles: writer(192.168.213.100)
db3(192.168.213.179) slave/ONLINE. Roles: reader(192.168.213.110)
db4(192.168.213.173) slave/ONLINE. Roles: reader(192.168.213.120)宕掉MySQL 数据库，虚拟IP 会全部在另一台正常数据库上
再启动主db1 的数据库
[root@master1 mysql-mmm]# systemctl start mariadb.service #启动mysql 数据库
[root@localhost mysql-mmm]# mmm_control show
db1(192.168.213.174) master/AWAITING_RECOVERY. Roles: #等待回复
db2(192.168.213.177) master/ONLINE. Roles: writer(192.168.213.100)
db3(192.168.213.179) slave/ONLINE. Roles: reader(192.168.213.110)
db4(192.168.213.173) slave/ONLINE. Roles:reader(192.168.213.120)[root@localhost mysql-mmm]# mmm_control show
db1(192.168.213.174) master/ONLINE. Roles: #正常状态（在线）
db2(192.168.213.177) master/ONLINE. Roles: writer(192.168.213.100)
db3(192.168.213.179) slave/ONLINE. Roles: reader(192.168.213.110)
db4(192.168.213.173) slave/ONLINE. Roles: reader(192.168.213.120)虽然主服务器 db1 从新上线，但是虚拟IP 并不会复位，
（3）停掉从服务器 db3 的数据库
[root@localhost mysql-mmm]# systemctl stop mariadb.service
[root@localhost mysql-mmm]# mmm_control show
# Warning: agent on host db3 is not reachable
db1(192.168.213.174) master/ONLINE. Roles:
db2(192.168.213.177) master/ONLINE. Roles: writer(192.168.213.100)
db3(192.168.213.179) slave/HARD_OFFLINE. Roles: #从服务器db3 关闭，虚拟IP 会全部飘移到灵位一台从服务器db4
db4(192.168.213.173) slave/ONLINE. Roles: reader(192.168.213.110), reader(192.168.213.120)
启动从 db3 的数据库
[root@localhost mysql-mmm]# systemctl start mariadb.service
[root@localhost mysql-mmm]# mmm_control show
# Warning: agent on host db3 is not reachable
db1(192.168.213.174) master/ONLINE. Roles:
db2(192.168.213.177) master/ONLINE. Roles: writer(192.168.213.100)
db3(192.168.213.179) slave/AWAITING_RECOVERY. Roles:
db4(192.168.213.173) slave/ONLINE. Roles: reader(192.168.213.110), reader(192.168.213.120)[root@localhost mysql-mmm]# mmm_control show
db1(192.168.213.174) master/ONLINE. Roles:
db2(192.168.213.177) master/ONLINE. Roles: writer(192.168.213.100)
db3(192.168.213.179) slave/ONLINE. Roles: reader(192.168.213.120) #从服务器上线后，虚拟IP又恢复
db4(192.168.213.173) slave/ONLINE. Roles: reader(192.168.213.110)
八 .测试数据同步状况
以监控服务器作为客户端进行远程登录 mysql 服务进行测试，在监控服务器上安装 mariadb 数据库
[root@localhost mysql-mmm]# yum install mariadb-server mariadb –y
在主数据库 db1 上为监控服务器授权登录MariaDB 数据库
MariaDB [(none)]> grant all on *.* to 'testdba'@'192.168.213.178' identified by '123456';
Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
在监控服务器上使用虚拟IP 登录MariaDB 数据库，进行数据插入，创建一个数据库
[root@localhost mysql-mmm]# mysql -utestdba -p -h 192.168.213.100
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.MariaDB [school]> create database mogo;
Query OK, 1 row affected (0.01 sec)
登录其他四台数据库，都可以查看到刚创建的数据库 mogo,证明群集同步成功
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mogo |
| mysql |
| performance_schema |
| test |
+--------------------+
当前名称：MySQL-MMM高可用
URL分享：http://ybzwz.com/article/jhpigg.html
