MMM实现mysql高可用性-原创手记-慕课网

由于mysql没有提供高可用性方案,如果一台数据库down就会影响到业务,为了实现高可用性可以使用hearbeat,这里我使用MMM来提高mysql的高可用性,以提高业务的不中断.

环境:

服务器类型 IP VIP

node1 10.10.1.65 10.10.1.77

node2 10.10.1.58 10.10.1.78

mon 10.10.1.161 10.10.1.79

配置过程:

1.首先要实现node1和node2的双向同步,这个本人博客已经有相关的文章,在这里就不进行说明了

2.安装和配置MMM

3台服务器都要安装MMM安装包,由于要使用到perl模块,所以要进行如下模块的安装

Algorithm::Diff

Proc::Daemon

Time::HiRes

DBI

DBD::mysql

以上模块你可以通过cpan进行安装,也可以去cpan.org网站上去下载相应的安装包,我已经安装好了

MMM下载地址如下:http://code.google.com/p/mysql-master-master/downloads/list,现在

最新的安装是mysql-master-master-1.2.6.tar.gz.

#tar zxvf mysql-master-master-1.2.6.tar.gz

#cd mysql-master-master-1.2.6

#./install.pl

在nod1上的配置:

#cp usr/local/mmm/etc/examples/mmm_agent.conf.example mmm_agent.conf

#vi usr/local/mmm/etc/examples/mmm_agent.conf //这个配置文件的内容很少,直接清空

添加如下的内容

# Master-Master Manager config (agent)

# Debug mode

debug no //是否开启debug模式

# 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 //debug日志文件路径

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 //真实IP的接口

# Define current server id

this db1

mode master

# For masters

peer db2

# Cluster hosts addresses and access params

host db1

ip 10.10.1.65 //node1的真实IP

port 3306 //数据库端口

user rep_agent //如下创建的mysql用户

password RepAgent //创建mysql用户的密码

host db2

ip 10.10.1.58 //node2的真实IP

port 3306

user rep_agent

password RepAgent

#mysql -u root -p123456

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

GRANT ALL PRIVILEGES on *.* to 'rep_monitor'@'%' identified by

'RepMonitor';

在node2上的配置:

#cp usr/local/mmm/etc/examples/mmm_agent.conf.example mmm_agent.conf

#vi usr/local/mmm/etc/examples/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.10.1.65

port 3306

user rep_agent

password RepAgent

host db2

ip 10.10.1.58

port 3306

user rep_agent

password RepAgent

#mysql -u root -p123456

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

GRANT ALL PRIVILEGES on *.* to 'rep_monitor'@'%' identified by

'RepMonitor';

在mon上的配置:

# 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 10.10.1.65

port 3306

user rep_monitor

password RepMonitor

mode master

peer db2

host db2

ip 10.10.1.58

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.10.1.77, 10.10.1.78 //这里是虚拟IP

# Mysql Writer role

role writer

mode exclusive

servers db1, db2

ip 10.10.1.79 //这里是虚拟IP

# 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

进行启动以及测试：

在node1和node2上启动mmmd_agent

#mmmd_agent

#netstat -tlnp

tcp 0 0 0.0.0.0:9989 0.0.0.0:* LISTEN 14301/perl

出现9989端口就表示启动正常

在mon上启动mmm_mon

#mmmd_mon

MySQL Multi-Master Replication Manager

Version: 1.2.6

Reading config file: 'mmm_mon.conf'

$VAR1 = {};

在管理节点上（mon）启动db节点：

#mmm_control set_online db1

MySQL Multi-Master Replication Manager

Version: 1.2.6

Config file: mmm_mon.conf

Daemon is running!

Command sent to monitoring host. Result: OK: State of 'db1' changed to ONLINE. Now you can wait some time and check its new roles!

#mmm_control set_online db2

MySQL Multi-Master Replication Manager

Version: 1.2.6

Config file: mmm_mon.conf

Daemon is running!

Command sent to monitoring host. Result: OK: State of 'db2' changed to ONLINE. Now you can wait some time and check its new roles!

查看下节点状态：

#mmm_control show

MySQL Multi-Master Replication Manager

Version: 1.2.6

Config file: mmm_mon.conf

Daemon is running!

===============================

Cluster failover method: AUTO

===============================

Servers status:

db1(10.10.1.65): master/ONLINE. Roles: reader(10.10.1.78;), writer(10.10.1.79;)

db2(10.10.1.58): master/ONLINE. Roles: reader(10.10.1.77;)

看到这个就说明配置没有问题,现在我们来进行切换测试,看切换是否正常,我现在把node1的mysql停止掉然后在看下节点的状态

#mmm_control show

[root@localhost etc]# mmm_control show

MySQL Multi-Master Replication Manager

Version: 1.2.6

Config file: mmm_mon.conf

Daemon is running!

===============================

Cluster failover method: AUTO

===============================

Servers status:

db1(10.10.1.65): master/HARD_OFFLINE. Roles: None

db2(10.10.1.58): master/ONLINE. Roles: reader(10.10.1.77;), reader(10.10.1.78;), writer(10.10.1.79;)

可以看到读写操作都转到了node2机器上了,看来切换没有问题,测试了下,切换需要大概3秒的时间,还算是可以接受的了.

mysql数据库休闲Mysql