Mysql-MMM主-主从复制 高可用HA

参考方案一

Mysql Master-Master Replication Manager(主-主复制 高可用HA) ，文档有点长，需要一步一步操作才能成功配置，要有点耐心。



1.环境
机器3台，2台MYSQL，1台Monitor,Ubuntu操作系统
IP 5个以上，MYSQL台数 x 2 ,再加上Monitor要一个IP
接口必须使用eth0,去掉eth0上增加的所有接口
DB1        10.0.0.5
DB2        10.0.0.6
DB-Monitor    10.0.0.7

这两个R/W IP自动设置
DB-R        10.0.0.10  10.0.0.11
DB-W        10.0.0.12            写IP不能和读IP一样


2.添加mysql用户密码
MySQL Replication:
      replication    密码: Replication
      Monitor: rep_monitor    密码:RepMonitor
      MySQL Agent: rep_agent    密码:RepAgent

2)最小权限：
monitor user: replication client
agent user: super,replication client,process

DB1上添加
grant all privileges on *.* to 'rep_agent' @ '%' identified by 'RepAgent';
grant all privileges on *.* to 'rep_monitor' @ '%' identified by 'RepMonitor';
grant replication slave on *.* to  'replication' @ '%' identified by 'Replication';

DB2上添加
grant all privileges on *.* to 'rep_agent' @ '%' identified by 'RepAgent';
grant all privileges on *.* to 'rep_monitor' @ '%' identified by 'RepMonitor';
grant replication slave on *.* to  'replication' @ '%' identified by 'Replication';

3.配置DB1和DB2互为主/从
1)修改配置文件
DB1
vi /etc/mysql/my.cnf
log-bin = mysql-bin
server-id = 1
log-slave-updates             #很重要，从前一台机器上同步过来的数据才能同步到下一台机器
binlog_do_db      = hugwww    #设置同步数据库，如果有多个数据库，每个数据库一行
replicate_do_db   = hugwww    #设置同步数据库，如果有多个数据库，每个数据库一行

DB2
vi /etc/mysql/my.cnf
log-bin = mysql-bin
server-id = 2
log-slave-updates           #很重要，从前一台机器上同步过来的数据才能同步到下一台机器
binlog_do_db    = hugwww    #设置同步数据库，如果有多个数据库，每个数据库一行
replicate_do_db = hugwww    #设置同步数据库，如果有多个数据库，每个数据库一行

重启两台MYSQL

2)设置主/从关系
DB1
change master to master_host='10.0.0.6',master_port=3306,master_user='Replication',master_password='replication',master_log_file='mysql-bin.000001′,master_log_pos=636;

DB2
change master to master_host='10.0.0.5',master_port=3306,master_user='replication',master_password='Replication',master_log_file='mysql-bin.000009',master_log_pos=497;

注：设置DB1的主为DB2(10.0.0.6)，设置DB2的主为DB1(10.0.0.5)
    master_log_file和master_log_pos的值是通过show master status\G;查看,一定要设置正确

在DB1和DB2上
测试，查看是否配置成功
slave start;
show slave status \G;
SHOW PROCESSLIST\G;
注:Mysql服务必须监听0.0.0.0

4.架设MMM
DB1和DB2
1)安装软件包
apt-get install libproc-daemon-perl libalgorithm-diff-perl libdbi-perl libdbd-mysql-perl iproute

下载最新版
wget

解压
tar xvzf mysql-master-master-1.2.3.tar.gz
cd mysql-master-master-1.2.3

安装
./install.pl
cp /usr/local/mmm/etc/examples/mmm_agent.conf.example /usr/local/mmm/etc/mmm_agent.conf

2)配置
DB1
vim /usr/local/mmm/etc/mmm_agent.conf
#
# Master-Master Manager config (agent)
#
# Debug mode
debug no
# Paths
pid_path /usr/local/mmm/var/mmmd_agent.pid
bin_path /usr/local/mmm/bin
# Logging setup
log mydebug
    file /usr/local/mmm/var/mmm-debug.log
    level debug
log mytraps
    file /usr/local/mmm/var/mmm-traps.log
    level trap
# MMMD command socket tcp-port and ip
bind_port 9989
# Cluster interface
cluster_interface eth0
# Define current server id
this db1
mode master
# For masters
peer db2
# Cluster hosts addresses and access params
host db1
    ip 10.0.0.5
    port 3306
    user rep_agent
    password RepAgent

host db2
    ip 10.0.0.6
    port 3306
    user rep_agent
    password RepAgent

———————————————————————————-
DB2
vim /usr/local/mmm/etc/mmm_agent.conf
#
# Master-Master Manager config (agent)
#
# Debug mode
debug no
# Paths
pid_path /usr/local/mmm/var/mmmd_agent.pid
bin_path /usr/local/mmm/bin
# Logging setup
log mydebug
    file /usr/local/mmm/var/mmm-debug.log
    level debug
log mytraps
    file /usr/local/mmm/var/mmm-traps.log
    level trap
# MMMD command socket tcp-port and ip
bind_port 9989
# Cluster interface
cluster_interface eth0
# Define current server id
this db2
mode master
# For masters
peer db1
# Cluster hosts addresses and access params
host db1
    ip 10.0.0.5
    port 3306
    user rep_agent
    password RepAgent

host db2
    ip 10.0.0.6
    port 3306
    user rep_agent
    password RepAgent

在DB1和DB2上面修改完配置文件后运行
mmmd_agent    或者     /usr/local/mmm/scripts/init.d/mmm_agent start
ps aux | grep mmmd    查看是否运行


5.db-mon安装
安装依赖包
apt-get install libproc-daemon-perl libalgorithm-diff-perl libdbi-perl libdbd-mysql-perl  iproute
下载最新版
wget
解压
tar xvzf mysql-master-master-1.2.3.tar.gz
cd mysql-master-master-1.2.3
安装
./install.pl
cp /usr/local/mmm/etc/examples/mmm_mon.conf.example /usr/local/mmm/etc/mmm_mon.conf

修改配置文件
vim /usr/local/mmm/etc/mmm_mon.conf
#
# Master-Master Manager config (monitor)
#
#include mmm_common.conf
# Paths
pid_path /usr/local/mmm/var/mmmd.pid
status_path /usr/local/mmm/var/mmmd.status
bin_path /usr/local/mmm/bin
# Choose the default failover method [manual|wait|auto]
failover_method auto
# How many seconds to wait for both masters to become ONLINE
# before switching from WAIT to AUTO failover method, 0 = wait indefinitely
wait_for_other_master 2
# How many seconds to wait before switching node status from AWAITING_RECOVERY to ONLINE
# 0 = disabled
auto_set_online 1
# Debug mode
debug no
# Logging setup
log mydebug
    file /usr/local/mmm/var/mmm-debug.log
    level debug
log mytraps
    file /usr/local/mmm/var/mmm-traps.log
    level trap
    email

# MMMD command socket tcp-port
bind_port 9988
agent_port 9989
monitor_ip 127.0.0.1
# Cluster interface
cluster_interface eth0
# Cluster hosts addresses and access params
host db1
    ip 10.0.0.5
    port 3306
    user rep_monitor
    password RepMonitor
    mode master
    peer db2
host db2
    ip 10.0.0.6
    port 3306
    user rep_monitor
    password RepMonitor
    mode master
    peer db1
#
# Define roles
#
active_master_role writer
# Mysql Reader role
role reader
    mode balanced
    servers db1, db2
    ip 10.0.0.10,10.0.0.11
# Mysql Writer role
role writer
    mode exclusive
    servers db1, db2
    ip 10.0.0.12
#
# Checks parameters
#
# Ping checker
check ping
    check_period 1
    trap_period 5
    timeout 2
# Mysql checker
# (restarts after 10000 checks to prevent memory leaks)
check mysql
    check_period 1
    trap_period  2
    timeout 2
    restart_after 10000
# Mysql replication backlog checker
# (restarts after 10000 checks to prevent memory leaks)
check rep_backlog
    check_period 5
    trap_period 10
    max_backlog 60
    timeout 2
    restart_after 10000
# Mysql replication threads checker
# (restarts after 10000 checks to prevent memory leaks)
check rep_threads
    check_period 1
    trap_period 5
    timeout 2
    restart_after 10000

启动监控db-mon
/usr/local/mmm/scripts/init.d/mmm_mon start
ps aux|grep mmm        查看是否启动
mmm_control show
mmm_control set_online db1    让db1上线
mmm_control set_online db2    让db2上线
mmm_control show    查看状态，出现以下内容正常
Servers status:
  db1(192.168.1.135): master/ONLINE. Roles: reader(10.0.0.10;), writer(10.0.0.12;)
  db2(192.168.1.229): master/ONLINE. Roles: reader(10.0.0.11;)


6.日志设置
在 db1, db2, mon 上
cp /usr/local/mmm/scripts/logrotate.d/mmm /etc/logrotate.d/
vim /etc/logrotate.d/mmm
/opt/mmm/var/*.log { 修改成 /usr/local/mmm/var/*.log
olddir /opt/mmm/var/old 修改成 olddir /usr/local/mmm/var/old


7.设置开机自动启动
在DB1，DB2上
cp /usr/local/mmm/scripts/init.d/mmm_agent /etc/init.d/
apt-get install rcconf
rcconf
选择mmm_agent 即可
在mon上
cp /usr/local/mmm/scripts/init.d/mmm_mon /etc/init.d/
apt-get install rcconf
rcconf
选择 mmm_mon 即可
再下來就只要會 mmm_control show, mmm_control set_online, mmm_control set_offline 即可


8.当机/重开机的 SOP
若有重开机等状况, 检查步骤:
mysql -u root # 看 MySQL 是否有启动, 若没启动 /etc/init.d/mysql start
ps aux | grep mmm # 看 mmm_agent 或 mmm_mon 是否有启动,
若没启动 /etc/init.d/mmm_[agent|mon] start
再来在 mon 的机器下: mmm_control show, 若没上线, 发现已经在 AWAITING_RECOVERY 状态, 就可以 set_online 让他上线

9.测试
在db1, db2
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON *.* TO ' IDENTIFIED BY ‘mmm’;
mysql> FLUSH PRIVILEGES;
写程式去对 10.0.0.9, 10.0.0.8 做写入/读取的动作, 并试着重开等看看反应~
註: 目前测试状况, 机器死掉时, 在 2秒内就会自动切换过去
———————————————————————————————————————
添加一台slave数据库DB3到两台主MMM
ip:10.0.0.20
1)添加mysql用户密码
      Monitor: rep_monitor    密码:RepMonitor
      MySQL Agent: rep_agent    密码:RepAgent

grant all privileges on *.* to ' by 'RepAgent';
grant all privileges on *.* to ' identified by 'RepMonitor';

2)配置DB3为DB1的从服务器
修改配置文件
vi /etc/mysql/my.cnf
log-bin = mysql-bin
server-id = 3
log-slave-updates
replicate_do_db  = hugwww    #定义需要同步的库

查看主服务器的状态，设置以下参数
change master to master_host='10.0.0.5',master_port=3306,master_user='replication',master_password='Replication',master_log_file='mysql-bin.000009',master_log_pos=497;

3)安装MMM
安装软件包
apt-get install libproc-daemon-perl libalgorithm-diff-perl libdbi-perl libdbd-mysql-perl iproute
下载最新版
wget
解压
tar xvzf mysql-master-master-1.2.3.tar.gz
cd mysql-master-master-1.2.3
安装
./install.pl
cp /usr/local/mmm/etc/examples/mmm_agent.conf.example /usr/local/mmm/etc/mmm_agent.conf

4)修改配置文件
vim /usr/local/mmm/etc/mmm_agent.conf
#
# Master-Master Manager config (agent)
#
# Debug mode
debug no
# Paths
pid_path /usr/local/mmm/var/mmmd_agent.pid
bin_path /usr/local/mmm/bin
# Logging setup
log mydebug
    file /usr/local/mmm/var/mmm-debug.log
    level debug
log mytraps
    file /usr/local/mmm/var/mmm-traps.log
    level trap
# MMMD command socket tcp-port and ip
bind_port 9989
# Cluster interface
cluster_interface eth0
# Define current server id
this db3
mode slave
# For masters
#peer db1
# Cluster hosts addresses and access params
host db3
    ip 10.0.0.20
    port 3306
    user rep_agent
    password RepAgent

5)修改db-mon配置文件，添加db3相关设置
vim /usr/local/mmm/etc/mmm_mon.conf
host db3
        ip 10.0.0.20
        port 3306
        user rep_monitor
        password yRepMonitor 
        mode slave
role reader
    mode balanced
    servers db1, db2,db3
    ip 10.0.0.10, 10.0.0.11, 10.0.0.9
注：以上是需要添加或修改的地方

在所有DB和监控服务器上面修改hosts文件
vim /etc/hosts
10.0.0.5    db1
10.0.0.6    db2
10.0.0.20    db3

 

 

————————————————————————————————————————————————

 

参考方案二

使用MMM搭建Mysql同步高可用性 

http://niyunjiu.javaeye.com/blog/344482

先介绍下MMM MMM是 mysql-master-master的缩写。
MMM项目来自 Google：

MySQL本身没有提供replication failover的解决方案，那么如何使Replication方案具有HA的功能呢，那就是MMM。
大家都知道mysql的同步机制在解决实际问题上具有很高的可行性，特别对读写负载比较高的web2.0来说，那么在实际应用中必须保证mysql的稳定性及性能。

Master-Master Replication
1、使用两个MySQL数据库db1,db2，互为Master和Slave，即：
一边db1作为db2的master，一旦有数据写向db1时，db2定时从db1更新
另一边db2也作为db1的master，一旦有数据写向db2时，db1也定时从db2获得更新

2、但从AppServer的角度来说，同时只有一个结点db1扮演Master，另外一个结点db2扮演Slave，不能同时两个结点扮演Master。即AppSever总是把write操作分配某个数据库(db1)，除非db1 failed，被切换。

3、如果扮演Slave的数据库结点db2 Failed了：
a)此时appServer要能够把所有的read,write分配给db1，read操作不再指向db2
b)一旦db2恢复过来后，继续充当Slave角色，并告诉AppServer可以将read分配给它了

4、如果扮演Master的数据库结点db1 Failed了
a)此时appServer要能够把所有的写操作从db1切换分配给db2，也就是切换Master由db2充当
b)db1恢复过来后，充当Slave的角色，Master由db2继续扮演


MMM利用了虚拟IP的技术：1个网卡可以同时使用多个IP。
(所以使用MMM时，需要2*n+1个IP，n为mysql数据库结点个数，包括master,slave)

MMM有3个重要的器件:
1、mmmd_mon - 数据库控制节点运行。
2、mmm_control - 数据库控制节点运行。
3、mmmd_agent - 数据库节点（master/slave）运行。


本文环境：
server1 ip: 192.168.1.225 ; virtual read ip:192.168.1.229
server2 ip: 192.168.1.226 ; virtual read ip:192.168.1.230

server3 ip: 192.168.1.227
virtual write ip: 192.168.1.231


配置过程很简单，
server1 server2 replication双向 master-master
server1 server2 安装mmm并配置mmm_agent.conf
server3 安装mmm并配置mmm_mon.conf

1，首先我们要先配置好master-master双向同步，这一步就不多说了。
2，安装部署MMM
3台服务器都要安装MMM软件，安装过程如下：

安装如下perl模块，为MMM安装做好准备，
cpan Algorithm::Diff
cpan Proc::Daemon
cpan Time::HiRes
cpan DBI
cpan DBD::mysql

wget
tar xzf mmm-1.0-pre2.tar.bz2
cd mmm-1.0-pre2
./install.pl

3，在server1上配置MMM
修改配置 /usr/local/mmm/etc/mmm_agent.conf，如下：

#
# Master-Master Manager config (agent)
#

# Debug mode
debug no

# Paths
pid_path /usr/local/mmm/var/mmmd_agent.pid
bin_path /usr/local/mmm/bin

# Logging setup
log mydebug
file /usr/local/mmm/var/mmm-debug.log
level debug

log mytraps
file /usr/local/mmm/var/mmm-traps.log
level trap

# MMMD command socket tcp-port and ip
bind_port 9989

# Cluster interface
cluster_interface eth0

# Define current server id
this db1
mode master

# For masters
peer db2

# Cluster hosts addresses and access params
host db1
ip 192.168.1.225
port 3306
user rep_agent
password RepAgent

host db2
ip 192.168.1.226
port 3306
user rep_agent
password RepAgent

GRANT ALL PRIVILEGES on *.* to 'rep_agent'@'%′ identified by 'RepAgent';
GRANT ALL PRIVILEGES on *.* to 'rep_monitor'@'%' identified by 'RepMonitor';

3，在server2上配置MMM
修改配置 /usr/local/mmm/etc/mmm_agent.conf，如下：

#
# Master-Master Manager config (agent)
#

# Debug mode
debug no

# Paths
pid_path /usr/local/mmm/var/mmmd_agent.pid
bin_path /usr/local/mmm/bin

# Logging setup
log mydebug
file /usr/local/mmm/var/mmm-debug.log
level debug

log mytraps
file /usr/local/mmm/var/mmm-traps.log
level trap

# MMMD command socket tcp-port and ip
bind_port 9989

# Cluster interface
cluster_interface eth0

# Define current server id
this db2
mode master

# For masters
peer db1

# Cluster hosts addresses and access params
host db1
ip 192.168.1.225
port 3306
user rep_agent
password RepAgent

host db2
ip 192.168.1.226
port 3306
user rep_agent
password RepAgent

GRANT ALL PRIVILEGES on *.* to 'rep_agent'@'%′ identified by 'RepAgent';
GRANT ALL PRIVILEGES on *.* to 'rep_monitor'@'%' identified by 'RepMonitor';


3，在server3上配置MMM
修改配置 /usr/local/mmm/etc/mmm_mon.conf，如下：

#
# Master-Master Manager config (monitor)
#

# Debug mode
debug no

# Paths
pid_path /usr/local/mmm/var/mmmd.pid
status_path /usr/local/mmm/var/mmmd.status
bin_path /usr/local/mmm/bin

# Logging setup
log mydebug
file /usr/local/mmm/var/mmm-debug.log
level debug

log mytraps
file /usr/local/mmm/var/mmm-traps.log
level trap
email root@localhost


# MMMD command socket tcp-port
bind_port 9988
agent_port 9989
monitor_ip 127.0.0.1

# Cluster interface
cluster_interface eth0

# Cluster hosts addresses and access params
host db1
ip 192.168.1.225
port 3306
user rep_monitor
password RepMonitor
mode master
peer db2

host db2
ip 192.168.1.226
port 3306
user rep_monitor
password RepMonitor
mode master
peer db1



#
# Define roles
#

active_master_role writer

# Mysql Reader role
role reader
mode balanced
servers db1, db2
ip 192.168.1.229, 192.168.1.230

# Mysql Writer role
role writer
mode exclusive
servers db1, db2
ip 192.168.1.231

#
# Checks parameters
#

# Ping checker
check ping
check_period 1
trap_period 5
timeout 2

# Mysql checker
# (restarts after 10000 checks to prevent memory leaks)
check mysql
check_period 1
trap_period 2
timeout 2
restart_after 10000

# Mysql replication backlog checker
# (restarts after 10000 checks to prevent memory leaks)
check rep_backlog
check_period 5
trap_period 10
max_backlog 60
timeout 2
restart_after 10000

# Mysql replication threads checker
# (restarts after 10000 checks to prevent memory leaks)
check rep_threads
check_period 1
trap_period 5
timeout 2
restart_after 10000


启动
server1 与 server2上分别启动mmmd_agent
[root@localhost ~]# mmmd_agent
[root@localhost ~]# netstat -tlnp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:9989 0.0.0.0:* LISTEN 26010/perl

出现9989端口说明启动成功。


server3上启动mmmd_mon
[root@localhost etc]# mmmd_mon
Reading config file: 'mmm_mon.conf'
$VAR1 = {
'db2' => {
'roles' => [
'reader(192.168.1.229;)'
],
'version' => '0',
'state' => 'ONLINE'
},
'db1' => {
'roles' => [
'reader(192.168.1.230;)',
'writer(192.168.1.231;)'
],
'version' => '0',
'state' => 'ONLINE'
}
};
Role: 'reader(192.168.1.229;)'
Adding role: 'reader' with ip '192.168.1.229'
Role: 'reader(192.168.1.230;)'
Adding role: 'reader' with ip '192.168.1.230'
Role: 'writer(192.168.1.231;)'
Adding role: 'writer' with ip '192.168.1.231'

在管理节点上（server3）启动db节点：
mmm_control set_online db1
mmm_control set_online db2

查看下节点状态：
[root@localhost etc]# mmm_control show
Config file: mmm_mon.conf
Daemon is running!
Servers status:
db1(192.168.1.225): master/ONLINE. Roles: reader(192.168.1.230;), writer(192.168.1.231;)
db2(192.168.1.226): master/ONLINE. Roles: reader(192.168.1.229;)

OK,能看到这个效果说明你已经配置成功了，下面就可以随心所欲的折腾mysql的异常状态，来进行测试。