mysql MMM 高可用
测试环境 Redhat AS4，如果是cenos 5版本那么yum 安装非常简单。mysql MMM 方案
版本：mysql-mmm-2.2.0.tar
ip：192.168.1.250（及做monitor又做agent,安装好mysql）
192.168.1.249（只做agent，安装好mysql）
vip：192.168.1.243
192.168.1.244
192.168.1.245
下载好mysql-mmm-2.2.0.tar 解压后进目录make;make install
2台机器perl版本：
# perl --version
This is perl, v5.8.5 built for i386-linux-thread-multi
Copyright 1987-2004, Larry Wall
安装mysql的部分修改：
echo "/usr/local/mysql/lib/mysql" >> /etc/ld.so.conf
ldconfig
ln -s /usr/local/mysql/lib/mysql /usr/lib/mysql
ln -s /usr/local/mysql/include/mysql /usr/include/mysql
2台机器分别执行下面的操作：
复制账号
mysql> grant replication slave on *.* to 'replication'@'192.168.1.%'> 监听账号（mon使用）
mysql> GRANT ALL PRIVILEGES ON *.* TO 'mmm_monitor'@'192.168.1.%'> 代理账号(db间使用的mmm账号)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'mmm_agent'@'192.168.1.%'> 配置192.168.1.250和192.168.1.249互为主从
考虑到可能主键冲突故做如下操作添加到2台机器的my.cnf配置里:
192.168.1.250 my.cnf上加入参数
auto_increment_offset = 1
auto_increment_increment = 2
这样192.168.1.250的auto_increment字段产生的数值是：1, 3, 5, 7, …等奇数ID了
192.168.1.249 my.cnf 上加入参数
auto_increment_offset = 2
auto_increment_increment = 2
这样192.168.1.249的auto_increment字段产生的数值是：2, 4, 6, 8, …等偶数ID了
重启2台mysql
安装perl模块：
192.168.1.250：
cpan -i Algorithm::Diff> 192.168.1.249:
cpan -i Algorithm::DiffDBI DBD::mysql File::Basename Net::ARP File::stat Log::Dispatch Log::Log4perl Mail::Send Net::Ping Proc::Daemon Time::HiRes Params::Validate
192.168.1.250配置如下：
# cat /etc/mysql-mmm/mmm_agent.conf
include mmm_common.conf
this db1
# cat /etc/mysql-mmm/mmm_common.conf
active_master_role writer
cluster_interface eth0
pid_path /var/run/mmm_agentd.pid
bin_path /usr/lib/mysql-mmm/
replication_user replication
replication_password slave
agent_user mmm_agent
agent_password RepAgent
ip 192.168.1.250
mode master
peer db2
ip 192.168.1.249
mode master
peer db1
#
# ip 192.168.0.33
# mode slave
#
hosts db1, db2
ips 192.168.1.243
mode exclusive
hosts db1, db2
ips 192.168.1.244, 192.168.1.245
mode balanced
#
# cat /etc/mysql-mmm/mmm_mon.conf
include mmm_common.conf
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.249, 192.168.1.250
auto_set_online 6
monitor_user mmm_monitor
monitor_password RepMonitor
debug 0
192.168.1.249配置：
# cat /etc/mysql-mmm/mmm_agent.conf
include mmm_common.conf
this db2
# cat /etc/mysql-mmm/mmm_common.conf
active_master_role writer
cluster_interface eth0
pid_path /var/run/mmm_agentd.pid
bin_path /usr/lib/mysql-mmm/
replication_user replication
replication_password slave
agent_user mmm_agent
agent_password RepAgent
ip 192.168.1.250
mode master
peer db2
ip 192.168.1.249
mode master
peer db1
#
# ip 192.168.0.33
# mode slave
#
hosts db1, db2
ips 192.168.1.243
mode exclusive
hosts db1, db2
ips 192.168.1.244, 192.168.1.245
mode balanced
#
192.168.1.249机器执行如下：
# /etc/init.d/mysql-mmm-agent restart
Daemon bin: '/usr/sbin/mmm_agentd'
Daemon pid: '/var/run/mmm_agentd.pid'
Daemon bin: '/usr/sbin/mmm_agentd'
Daemon pid: '/var/run/mmm_agentd.pid'
Shutting down MMM Agent daemon not running.
Daemon bin: '/usr/sbin/mmm_agentd'
Daemon pid: '/var/run/mmm_agentd.pid'
Starting MMM Agent daemon... Ok
# /etc/init.d/mysql-mmm-agent status
Daemon bin: '/usr/sbin/mmm_agentd'
Daemon pid: '/var/run/mmm_agentd.pid'
Checking MMM Agent process: running.
#
192.168.1.250机器执行如下：
# /etc/init.d/mysql-mmm-agent restart
Daemon bin: '/usr/sbin/mmm_agentd'
Daemon pid: '/var/run/mmm_agentd.pid'
Daemon bin: '/usr/sbin/mmm_agentd'
Daemon pid: '/var/run/mmm_agentd.pid'
Shutting down MMM Agent daemon not running.
Daemon bin: '/usr/sbin/mmm_agentd'
Daemon pid: '/var/run/mmm_agentd.pid'
Starting MMM Agent daemon... Ok
# /etc/init.d/mysql-mmm-agent status
Daemon bin: '/usr/sbin/mmm_agentd'
Daemon pid: '/var/run/mmm_agentd.pid'
Checking MMM Agent process: running.
# /etc/init.d/mysql-mmm-monitor restart
Daemon bin: '/usr/sbin/mmm_mond'
Daemon pid: '/var/run/mmm_mond.pid'
Daemon bin: '/usr/sbin/mmm_mond'
Daemon pid: '/var/run/mmm_mond.pid'
Shutting down MMM Monitor daemon: ... Ok
Daemon bin: '/usr/sbin/mmm_mond'
Daemon pid: '/var/run/mmm_mond.pid'
Starting MMM Monitor daemon: Ok
# /etc/init.d/mysql-mmm-monitor status
Daemon bin: '/usr/sbin/mmm_mond'
Daemon pid: '/var/run/mmm_mond.pid'
Checking MMM Monitor process: running.
# mmm_control show
db1(192.168.1.250) master/ONLINE. Roles: reader(192.168.1.245), writer(192.168.1.243)
db2(192.168.1.249) master/ONLINE. Roles: reader(192.168.1.244)
# mmm_control checks
db2ping OK
db2mysql OK
db2rep_threadsOK
db2rep_backlogOK: Backlog is null
db1ping OK
db1mysql OK
db1rep_threadsOK
db1rep_backlogOK: Backlog is null
# ip add
1: lo:mtu 16436 qdisc noqueue
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 brd 127.255.255.255 scope host lo
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0:mtu 1500 qdisc pfifo_fast qlen 1000
link/ether 00:0c:29:ac:7b:64 brd ff:ff:ff:ff:ff:ff
inet 192.168.1.250/24 brd 192.168.1.255 scope global eth0
inet 192.168.1.245/32 scope global eth0
inet 192.168.1.243/32 scope global eth0
inet6 fe80::20c:29ff:feac:7b64/64 scope link
valid_lft forever preferred_lft forever
3: sit0:mtu 1480 qdisc noop
link/sit 0.0.0.0 brd 0.0.0.0
192.168.1.249机器执行如下：
# ip add
1: lo:mtu 16436 qdisc noqueue
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 brd 127.255.255.255 scope host lo
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0:mtu 1500 qdisc pfifo_fast qlen 1000
link/ether 00:0c:29:32:86:55 brd ff:ff:ff:ff:ff:ff
inet 192.168.1.249/24 brd 192.168.1.255 scope global eth0
inet 192.168.1.244/32 scope global eth0
inet6 fe80::20c:29ff:fe32:8655/64 scope link
valid_lft forever preferred_lft forever
3: sit0:mtu 1480 qdisc noop
link/sit 0.0.0.0 brd 0.0.0.0
说明：
过程中会遇到不能mysql不能及时上线即可立即上线：
# mmm_control set_online db1
OK: This host is already ONLINE. Skipping command.
# mmm_control set_online db2
OK: This host is already ONLINE. Skipping command.
#
查看安装的perl模块:
# instmodsh
Available commands are:
l - List all installed modules
m - Select a module
q - Quit the program
cmd? l
Installed modules are:
Algorithm::Diff
Class::Singleton
DBD::mysql
DBI
ExtUtils::CBuilder
ExtUtils::MakeMaker
ExtUtils::ParseXS
File::Temp
Log::Dispatch
Log::Log4perl
Module::Build
Net::ARP
Net::Ping
ParamsValidate
Perl
Pod::Escapes
Proc::Daemon
Sys::Syslog
Test::Harness
Test::Simple
Time::HiRes
TimeDate
cmd?
程序读写分离的话 连接到这些vip 进行读和写
每个vip 都是通的 可以telnet 3306，此架构是在mysql replication基础上建立起来的且具有可扩容性。
备注：
1、设置从服务器从主服务器的真实IP同步，当该主服务器挂了以后就会导致后端的从服务器同步出现问题，需要手工更改同步到另一个主服务器上，缺点不能自动化。
2、设置后端从服务器利用浮动IP来进行同步，当主库down了以后会自动将浮动IP转换到另外一个主server上，另外一个server的同步参数如master_log_file、master_log_pos可能会不一样，这样同步就会出现问题，如何保证两个主mysql server的那些参数一致呢？
有没有什么好的办法？
mysql-mmm 中某个writer down了以后，mond 会自动把所有的reader change master to 到新的writer 的实体IP上去。
初始配置的时候也是把所有slave 指向 writer的实体IP。
3.mysql MMM mond单点问题可以结合heartbeat来解决。
4.如果机器内存比较大的话，可以考虑一台安装多个mysql实例来跑这样充分利用资源。
参考配置如下：
active_master_role writer
cluster_interface eth0
pid_path /var/run/mysql-mmm/mmm_agentd.pid
bin_path /usr/libexec/mysql-mmm/
replication_user replicant
replication_password slave
agent_user mmm_agent
agent_password RepAgent
ip 10.10.10.51
mode master
peer db2
mysql_port 3300
ip 10.10.10.52
mode master
peer db1
mysql_port 3300
ip 10.10.10.51
mode master
peer db4
mysql_port 3306
ip 10.10.10.52
mode master
peer db3
mysql_port 3306
ip 10.10.10.51
mode master
peer db6
mysql_port 3309
ip 10.10.10.52
mode master
peer db7
mysql_port 3309
##########################################
ip 10.10.10.52
mode master
peer db8
mysql_port 3307
ip 10.10.10.51
mode master
peer db7
mysql_port 3307
ip 10.10.10.52
mode master
peer db10
mysql_port 3308
ip 10.10.10.51
mode master
peer db9
mysql_port 3308
ip 10.10.10.52
mode master
peer db12
mysql_port 3310
ip 10.10.10.51
mode master
peer db11
mysql_port 3310
#
# ip 192.168.100.51
# mode slave
#
hosts db1, db2
ips 10.10.10.53
mode exclusive
hosts db1, db2
ips 10.10.10.54
mode balanced
hosts db3, db4
ips 10.10.10.55
mode exclusive
hosts db3, db4
ips 10.10.10.56
mode balanced
hosts db5, db6
ips 10.10.10.57
mode exclusive
hosts db5, db6
ips 10.10.10.58
mode balanced
hosts db7, db8
ips 10.10.10.59
mode exclusive
hosts db7, db8
ips 10.10.10.60
mode balanced
hosts db9, db10
ips 10.10.10.61
mode exclusive
hosts db9, db10
ips 10.10.10.62
mode balanced
hosts db11, db12
ips 10.10.10.63
mode exclusive
hosts db11, db12
ips 10.10.10.64
mode balanced
然后程序里去连接这些vipread和write操作，宕机的话vip就跳到其他的机器。
页:
[1]