MySQL 高可用架构
一、MMM 架构
MMM(Master-Master replication manger for MySQL)是一套支持双主故障切换和双主日常管理的脚本程序，MMM使用Perl语言开发，
主要用来监控和管理MySQL Master-Master(双主)复制，虽然叫做双主复制，但业务上同一时刻只允许一个主进行写入，另一台备选主上提供部
分读服务，以加速在主主切换时刻备选主的预热，可以说MMM这套脚本程序一方面实现了故障切换功能，另一方面其内部附加的工具脚本也可以
实现多个slaves负载均衡。
MMM提供了自动和手动两种方式移除一组服务器中复制延时较高的服务器服务器的虚拟IP，同时它还可以备份数据、实现两节点之间的数据
同步等。
由于MMM无法完全地保证数据一致性，所以MMM适用于对数据的一致性要求不是很高，但又想最大程度的保证业务可用性的场景。
例：三台主机
角色 IP地址 主机名字 server id
--------------------------------------------------------------
monitor host 192.168.110.130 db3
--------------------------------------------------------------
master 1 192.168.110.128 db1 1 writer(192.168.110.132)
--------------------------------------------------------------
master 2 192.168.110.130 db2 2 reader(192.168.110.133)
--------------------------------------------------------------
slave 1 192.168.110.131 db3 3 reader(192.168.110.134)
--------------------------------------------------------------
1、主机配置
[root@www ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.110.128 db1.pancou.com db1
192.168.110.130 db2.pancou.com db2
192.168.110.131 db3.pancou.com db3
2、mysql的安装和配置
db1:
server-id = 1
log-slave-updates=true
#gtid-mode=on
#enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-threads=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
report-port=3306
report-host=www.pancou.com
db2:
server-id = 2
log-slave-updates=true
#gtid-mode=on
#enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-threads=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
report-port=3306
report-host=www.pancou.com
db3:
server-id = 3
log-slave-updates=true
#gtid-mode=on
#enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-threads=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
report-port=3306
report-host=www.pancou.com
3、主从复制，和双主复制看前面复制章节
4、安装mysql-mmm
1. 安装监控程序
在管理服务器和数据库服务器上分别要运行mysql-mmm monitor和agent程序。下面分别安装：
前提要安装
#rpm -ivh epel-release-6-8.noarch.rpm
在管理服务器（192.168.110.130）上，执行下面命令：
# yum -y install mysql-mmm-monitor*
与monitor依赖的所有文件也会随之安装，但是有一个例外perl-Time-HiRes，所以还需要执行下面的命令：
[plain] view plain copy print?
# yum -y install perl-Time-HiRes*
2. 安装代理程序
# yum -y install mysql-mmm-agent*
在192.168.110.128和192.168.110.131 上分别安装：
# yum -y install mysql-mmm-agent*
5、配置MMM
1.配置agent文件，需要在db1,db2,db3分别配置
完成安装后，所有的配置文件都放到了/etc/mysql-mmm/下面。管理服务器和数据库服务器上都要包含一个共同
的文件mmm_common.conf，
在db1上配置：
active_master_role writer
cluster_interface eth0
pid_path /var/run/mysql-mmm/mmm_agentd.pid
bin_path /usr/libexec/mysql-mmm/
replication_user repl_user
replication_password pancou
agent_user mmm-agent
agent_password mmm-agent
ip 192.168.110.128
mode master
peer db2
ip 192.168.110.130
mode master
peer db1
ip 192.168.110.131
mode slave
hosts db1, db2
ips 192.168.110.132
mode exclusive
hosts db2, db3
ips 192.168.110.133, 192.168.110.134
mode balanced
可以在db1上编辑该文件后，通过scp命令分别复制到monitor、db2、db3和db4上。
复制到db2上：
scp /etc/mysql-mmm/mmm_com.conf db2:/etc/mysql-mmm/
复制到db3上：
scp /etc/mysql-mmm/mmm_com.conf db3:/etc/mysql-mmm/
2. 编辑mmm_agent.conf。在数据库服务器上，还有一个mmm_agent.conf需要修改
db1:
# vim /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
db2:
# vim /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 db2
db3:
# vim /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 db3
3. 编辑mmm_mon.confg。在管理服务器上，修改mmm_mon.conf文件
在db2上
# vim /etc/mysql-mmm/mmm_mon.conf
include mmm_common.conf
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.110.128,192.168.110.130,192.168.110.131
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_user mmm_monitor
monitor_password mmm_monitor
6、创建监控
MariaDB [(none)]> grant replication client on *.* to 'mmm_monitor'@'192.168.110.%' identified by 'mmm_monitor';
MariaDB [(none)]> grant super,replication client,process on *.* to 'mmm-agent'@'192.168.110.%' identified by 'mmm-agent';
MariaDB [(none)]> grant replication slave on *.* to 'repl_user'@'192.168.110.%' identified by 'pancou';
MariaDB [(none)]> flush priviliges;
7、启动MMM
1. 在数据库服务器上启动代理程序
# service mysql-mmm-agent start
Starting MMM Agent Daemon: [ OK ]
2. 在管理服务器上启动监控程序
# service mysql-mmm-monitor start
Starting MMM Monitor Daemon: [ OK ]
8、在monitor上检查集群主机的状态
[root@www ~]# mmm_control checks all
db2 ping [last change: 2016/07/04 08:54:52] OK
db2 mysql [last change: 2016/07/04 08:54:52] OK
db2 rep_threads [last change: 2016/07/04 08:54:52] ERROR: Replication is broken
db2 rep_backlog [last change: 2016/07/04 08:54:52] OK: Backlog is null
db3 ping [last change: 2016/07/04 08:54:52] OK
db3 mysql [last change: 2016/07/04 08:55:57] OK
db3 rep_threads [last change: 2016/07/04 08:55:54] OK
db3 rep_backlog [last change: 2016/07/04 08:55:54] OK: Backlog is null
db1 ping [last change: 2016/07/04 08:54:52] OK
db1 mysql [last change: 2016/07/04 08:54:52] OK
db1 rep_threads [last change: 2016/07/04 08:55:25] ERROR: Replication is broken
db1 rep_backlog [last change: 2016/07/04 08:54:52] OK: Backlog is null
复制问题解决以后：
[root@www ~]# mmm_control checks all
db2 ping [last change: 2016/07/05 03:54:20] OK
db2 mysql [last change: 2016/07/05 03:54:20] OK
db2 rep_threads [last change: 2016/07/05 03:54:20] OK
db2 rep_backlog [last change: 2016/07/05 03:54:20] OK: Backlog is null
db3 ping [last change: 2016/07/05 03:54:20] OK
db3 mysql [last change: 2016/07/05 03:54:20] OK
db3 rep_threads [last change: 2016/07/05 03:54:20] OK
db3 rep_backlog [last change: 2016/07/05 03:54:20] OK: Backlog is null
db1 ping [last change: 2016/07/05 03:54:20] OK
db1 mysql [last change: 2016/07/05 03:54:20] OK
db1 rep_threads [last change: 2016/07/05 03:54:20] OK
db1 rep_backlog [last change: 2016/07/05 03:54:20] OK: Backlog is null
[root@www ~]# mmm_control show
# Warning: agent on host db1 is not reachable
# Warning: agent on host db3 is not reachable
db1(192.168.110.128) master/REPLICATION_FAIL. Roles:
db2(192.168.110.130) master/ONLINE. Roles: reader(192.168.110.128), reader(192.168.110.130), writer(192.168.110.132)
db3(192.168.110.131) slave/ONLINE. Roles:
复制问题解决以后：
[root@www ~]# mmm_control show
# Warning: agent on host db1 is not reachable
# Warning: agent on host db2 is not reachable
db1(192.168.110.128) master/ONLINE. Roles:
db2(192.168.110.130) master/ONLINE. Roles:
db3(192.168.110.131) slave/ONLINE. Roles:
iptales -F
[root@www ~]# mmm_control show
db1(192.168.110.128) master/ONLINE. Roles:
db2(192.168.110.130) master/ONLINE. Roles: reader(192.168.110.133), writer(192.168.110.132)
db3(192.168.110.131) slave/ONLINE. Roles: reader(192.168.110.134)
9、MMM高可用环境测试
在db2上：
[root@www ~]# service mysqld stop
Shutting down MySQL.. SUCCESS!
[root@www ~]# iptables -F
在monitor上：
[root@www ~]# mmm_control show
db1(192.168.110.128) master/ONLINE. Roles: writer(192.168.110.132)
db2(192.168.110.130) master/HARD_OFFLINE. Roles:
db3(192.168.110.131) slave/ONLINE. Roles: reader(192.168.110.133), reader(192.168.110.134)
[root@www ~]# tail -f /var/log/mysql-mmm/mmm_mond.log
2016/07/05 07:38:33 FATAL Agent on host 'db2' is reachable again
2016/07/05 07:38:41 FATAL Can't reach agent on host 'db2'
2016/07/05 07:38:45 FATAL Agent on host 'db2' is reachable again
2016/07/05 07:45:43 FATAL Agent on host 'db1' is reachable again
2016/07/05 07:48:48 FATAL Can't reach agent on host 'db3'
2016/07/05 07:49:03 FATAL Can't reach agent on host 'db2'
2016/07/05 07:49:12 FATAL Can't reach agent on host 'db1'
2016/07/05 07:49:18 FATAL Agent on host 'db1' is reachable again
2016/07/05 07:49:34 FATAL Agent on host 'db2' is reachable again
2016/07/05 07:49:46 FATAL Agent on host 'db3' is reachable again
2016/07/05 07:56:00 FATAL State of host 'db2' changed from ONLINE to HARD_OFFLINE (ping: OK, mysql: not OK)
此时，db2,的状态由ONLINE 变为 HARD_OFFLINE，把db2的读角色转移到db3,写角色转移到db1.
[root@www ~]# service mysqld start
Starting MySQL.. SUCCESS!
2016/07/05 08:00:29 FATAL State of host 'db2' changed from HARD_OFFLINE to AWAITING_RECOVERY
2016/07/05 08:01:29 FATAL State of host 'db2' changed from AWAITING_RECOVERY to ONLINE because of auto_set_online(60 seconds). It was in state AWAITING_RECOVERY for 60 seconds
查看集群状态：
[root@www ~]# mmm_control show
db1(192.168.110.128) master/ONLINE. Roles: writer(192.168.110.132)
db2(192.168.110.130) master/ONLINE. Roles: reader(192.168.110.133)
db3(192.168.110.131) slave/ONLINE. Roles: reader(192.168.110.134)
[root@www ~]# mysql -ummm-monitor -p -h292.168.110.132
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9108
Server version: 10.0.15-MariaDB-log Source distribution
Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
[root@www ~]# mysql -ummm-monitor -p -h292.168.110.133
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 184
Server version: 10.0.15-MariaDB-log Source distribution
Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
[root@www ~]# mysql -ummm-monitor -p -h292.168.110.134
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9446
Server version: 10.0.15-MariaDB-log Source distribution
Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
