本文介绍了一种使用MySQL MMM实现数据库高可用性的部署方案。包括基本的服务器信息、MySQL及MMM软件的安装配置过程，以及如何通过MMM进行主主复制、主从复制，并通过实例演示了故障转移和角色切换。
基本信息和规划：
#服务器信息：
====================================================================
ip address hostname ServerVersion MySql
------------ --------- -------------- -----------
192.168.100.151 server1 CentOS 5.6 MySql 5.0.95
192.168.100.12 server12 CentOS 5.6 MySql 5.6.30
192.168.100.13 server13 CentOS 5.6 MySql 5.6.30
192.168.100.14 server14 CentOS 5.6 MySql 5.6.30
====================================================================
#MySQL-MMM 信息：
====================================================================
function ip address role VIP
-------- ------------ ------ ---------------
monitor 192.168.100.151 monitor 192.168.100.251
master/agent 192.168.100.12 witer 192.168.100.212
master/agent 192.168.100.13 reader 192.168.100.213
slave/agent 192.168.100.14 reader 192.168.100.214
====================================================================
说明：151 作为 monitor，12 与 13 进行主主复制，14 为 12 的从slave。
MySQL 下载地址：
http://dev.mysql.com/downloads/mysql/
http://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.12-linux-glibc2.5-i686.tar.gz
MySQL-MMM yum源下载地址：(注意服务器对应的版本：epel-release-X-X.noarch.rpm)
http://download.fedoraproject.org/pub/epel/5/i386/epel-release-5-4.noarch.rpm
安装 MySQL：（ip 12，13，14 ,151 均安装）
MySQL 安装（RPM安装模式）及目录结构
MySQL 安装（二进制安装模式）
MySQL 安装（源码安装模式）
MySQL 创建3个用户：（ip 12，13，14 ，151均创建）
GRANT REPLICATION CLIENT ON *.* TO 'mmm_monitor'@'192.168.100.%' IDENTIFIED BY 'mmm_monitor';
GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO 'mmm_agent'@'192.168.100.%' IDENTIFIED BY 'mmm_agent';
GRANT REPLICATION SLAVE ON *.* TO 'mmm_replication'@'192.168.100.%' IDENTIFIED BY 'mmm_replication';
FLUSH PRIVILEGES;
配置复制：
12 与 13 配置主主复制：MySQL 高可用：主主复制
12 与 14 配置主从复制：MySQL 高可用：主从复制
#MySQL配置（/etc/my.cnf）如下，注意 server-id 在每台服务器实例中都不一样，与ip一样方便区分。
# cd /var/log/
# mkdir mysql
# chown mysql:mysql mysql
#MySQL配置（/etc/my.cnf）如下，注意 server-id 在每台服务器实例中都不一样，与ip一样方便区分。
[mysqld]
basedir =/usr/local/mysql
datadir =/usr/local/mysql/data
socket=/tmp/mysql.sock
port = 3306
user=mysql
default-storage-engine = innodb
replicate-ignore-db = mysql,information_schema,performance_schema
binlog-ignore-db = mysql,information_schema,performance_schema
server-id = 12
log-bin = /var/log/mysql/mysql-bin.log
log_bin_index = /var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-bin.relay
relay_log_index = /var/log/mysql/mysql-bin.relay.index
expire_logs_days = 10
max_binlog_size = 100M
log_slave_updates = 1
MySQL-MMM 安装：
cd /usr/local/src
rpm -ivh epel-release-5-4.noarch.rpm
#agent节点安装（ip 12，13，14 执行）
yum -y install mysql-mmm.noarch mysql-mmm-tools.noarch mysql-mmm-agent.noarch
# monitor节点安装（ip 151 执行）
yum -y install mysql-mmm.noarch mysql-mmm-tools.noarch mysql-mmm-monitor.noarch
MySQL-MMM 配置：
#vi /etc/mysql-mmm/mmm_common.conf (ip 12，13，14，151 执行)
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 mmm_replication
replication_password mmm_replication
agent_user mmm_agent
agent_password mmm_agent
</host>
<host master12>
ip 192.168.100.12
mode master
peer master13
</host>
<host master13>
ip 192.168.100.13
mode master
peer master12
</host>
<host master14>
ip 192.168.100.14
mode slave
</host>
<role writer>
hosts master12, master13
ips 192.168.100.251
mode exclusive
</role>
<role reader>
hosts master12, master13, master14
ips 192.168.100.212, 192.168.100.213, 192.168.100.214
mode balanced
</role>
#vi /etc/mysql-mmm/mmm_agent.conf (ip 12，13，14 执行)
#在 ip 12 改为
this master12
#在 ip 13 改为
this master13
#在 ip 14 改为
this master14
#vi /etc/mysql-mmm/mmm_mon.conf (ip 151 执行)
include mmm_common.conf
<monitor>
ip 192.168.100.151
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.100.12, 192.168.100.13, 192.168.100.14
auto_set_online 60
</monitor>
<host default>
monitor_user mmm_monitor
monitor_password mmm_monitor
</host>
debug 0
MySQL-MMM 启动
# 在 agent 启动（ip 12，13，14 执行）
# chkconfig mysql-mmm-agent on
# service mysql-mmm-agent start
/etc/init.d/mysql-mmm-agent start
# 在 monitor 启动（ip 151）
# chkconfig mysql-mmm-monitor on
# service mysql-mmm-monitor start
/etc/init.d/mysql-mmm-monitor start
配置完成！！！
# 在 monitor 检查和维护
mmm_control help #mmm_control 帮助信息
ping - ping monitor
show - show status
checks [<host>|all [<check>|all]] - show checks status
set_online <host> - set host <host> online
set_offline <host> - set host <host> offline
mode - print current mode.
set_active - switch into active mode.
set_manual - switch into manual mode.
set_passive - switch into passive mode.
move_role [--force] <role> <host> - move exclusive role <role> to host <host>
(Only use --force if you know what you are doing!)
set_ip <ip> <host> - set role with ip <ip> to host <host>
检查：
#monitor 启动服务器时，等待连接：
[root@server1 src]# mmm_control show
master12(192.168.100.12) master/AWAITING_RECOVERY. Roles:
master13(192.168.100.13) master/AWAITING_RECOVERY. Roles:
master14(192.168.100.14) slave/AWAITING_RECOVERY. Roles:
#正常情况下：
[root@server1 src]# mmm_control show
master12(192.168.100.12) master/ONLINE. Roles: reader(192.168.100.213)
master13(192.168.100.13) master/ONLINE. Roles: reader(192.168.100.214), writer(192.168.100.251)
master14(192.168.100.14) slave/ONLINE. Roles: reader(192.168.100.212)
#切换“writer”到 master12：
[root@server1 src]# mmm_control move_role writer master12
OK: Role 'writer' has been moved from 'master13' to 'master12'. Now you can wait some time and check new roles info!
#切换后：
[root@server1 src]# mmm_control show
master12(192.168.100.12) master/ONLINE. Roles: reader(192.168.100.213), writer(192.168.100.251)
master13(192.168.100.13) master/ONLINE. Roles: reader(192.168.100.214)
master14(192.168.100.14) slave/ONLINE. Roles: reader(192.168.100.212)
#验证群集
[root@server1 src]# mmm_control checks all
master12 ping [last change: 2016/04/23 18:33:14] OK
master12 mysql [last change: 2016/04/23 18:33:28] OK
master12 rep_threads [last change: 2016/04/23 18:33:14] OK
master12 rep_backlog [last change: 2016/04/23 18:33:14] OK: Backlog is null
master13 ping [last change: 2016/04/23 18:33:14] OK
master13 mysql [last change: 2016/04/23 18:33:14] OK
master13 rep_threads [last change: 2016/04/23 18:33:14] OK
master13 rep_backlog [last change: 2016/04/23 18:33:14] OK: Backlog is null
master14 ping [last change: 2016/04/23 18:33:14] OK
master14 mysql [last change: 2016/04/23 18:33:14] OK
master14 rep_threads [last change: 2016/04/23 18:33:14] OK
master14 rep_backlog [last change: 2016/04/23 18:33:14] OK: Backlog is null
测试：
#创建一个mysql测试帐户(在 ip 12，13，14 执行)
grant select,insert,update,delete on *.* to 'test'@'192.168.100.%' identified by 'test';
flush privileges;
#在 192.168.100.151 中连接 writer 角色的虚拟IP。可以操作数据。
mysql -h 192.168.100.251 -u test -p
#停止 192.168.100.12 （writer 角色）的 mysqld 服务
service mysqld stop
#查看群集信息，12 处于 HARD_OFFLINE 状态，writer 自动切换到 13。
[root@server1 src]# mmm_control show
master12(192.168.100.12) master/HARD_OFFLINE. Roles:
master13(192.168.100.13) master/ONLINE. Roles: reader(192.168.100.214), writer(192.168.100.251)
master14(192.168.100.14) slave/ONLINE. Roles: reader(192.168.100.212), reader(192.168.100.213)
#继续在刚才连接的 192.168.100.251 中操作数据。
1. 客户端将重新连接；
2. 操作数据时，13 写入，会同步到 14 中；
#启动 192.168.100.12 的 mysqld 服务（刚才操作的数据会从主库 13 同步到主库 12。群集中需要一点时间恢复）
service mysqld start
=========================================================================
测试二：双主高可用
=========================================================================
文档：https://launchpadlibrarian.net/368918243/mysql-mmm-2.2.2.pdf
安装：http://mysql-mmm.org/mmm2:guide
下载安装：
# 下载安装
wget http://mysql-mmm.org/_media/:mmm2:mysql-mmm-2.2.1.tar.gz
tar zxvf :mmm2:mysql-mmm-2.2.1.tar.gz
cd mysql-mmm-2.2.1
make install配置以下几个文件：
两台服务器配置一样，注意 cluster_interface 与现有的网卡名称一样
# vim /etc/mysql-mmm/mmm_common.conf
active_master_role writer
<host default>
cluster_interface ens33
pid_path /var/run/mmm_agentd.pid
bin_path /usr/lib/mysql-mmm/
replication_user mmm_replication
replication_password mmm_replication
agent_user mmm_agent
agent_password mmm_agent
</host>
<host server171>
ip 192.168.1.171
mode master
peer server172
</host>
<host server172>
ip 192.168.1.172
mode master
peer server171
</host>
<role writer>
hosts server171, server172
ips 192.168.1.181
mode exclusive
</role>
<role reader>
hosts server171, server172
ips 192.168.1.182,192.168.1.183
mode balanced
</role>
每天服务器配置的不一样，只配置当前服务器名称（如本测试服务器 192.168.1.172 名称为 server172，另一台服务器同理）
vim /etc/mysql-mmm/mmm_agent.conf
include mmm_common.conf
this server172监控服务器只要一台
vim /etc/mysql-mmm/mmm_mon.conf
include mmm_common.conf
<monitor>
ip 127.0.0.1
pid_path /var/run/mmm_mond.pid
bin_path /usr/lib/mysql-mmm/
status_path /var/lib/misc/mmm_mond.status
ping_ips 192.168.1.171, 192.168.1.172
</monitor>
<host default>
monitor_user mmm_monitor
monitor_password mmm_monitor
</host>
debug 0
# vim /etc/mysql-mmm/mmm_tools.conf
启动服务
service mysql-mmm-agent status
service mysql-mmm-monitor status
/etc/init.d/mysql-mmm-agent start
/etc/init.d/mysql-mmm-monitor start
chkconfig --add /etc/rc.d/init.d/mysql-mmm-agent
chkconfig --add /etc/rc.d/init.d/mysql-mmm-monitor检查 & 日志
mmm_control --help
mmm_control show
mmm_control checks all
# 日志
tail -f /var/log/mysql-mmm/mmm_mond.log
tail -f /var/log/mysql-mmm/mmm_agentd.log
测试，连接 writer 角色的虚拟IP。测试故障转移
# 测试，连接 writer 角色的虚拟IP
mysql -h 192.168.1.181 -u root -p
mysql -h 192.168.1.181 -P3306 -u test -p
show variables like 'server_id';
# writer 角色所在服务器 server172重启mysql实例，角色将切换
[root@server172 ~]# service mysqld stop
# 重启服务后，角色不会变
[root@server172 ~]# service mysqld start
# 设置在线后，此实例作为 reader 角色 （双主同步自动恢复）
[root@server172 ~]# mmm_control set_online server172
扫一扫
308
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
