MySQL主主复制+MMM实现高可用（一）
环境规划
|
角色 |
IP |
VIP |
用途 |
|
DB1 |
192.168.56.101 |
192.168.56.111/113 |
主Master提供wirte+read |
|
DB2 |
192.168.56.102 |
192.168.56.112 |
备Master提供read |
|
Monitor Server |
192.168.56.103 |
NULL |
负责所有的监控工作的监控守护进程，决定节点的移除等等 |
准备工作
所有节点上关闭iptables和selinux
~]# iptables -F
~]# iptables -t nat -F
~]# /etc/init.d/iptables stop
~]# chkconfig iptables off
~]# chkconfig --list iptables
iptables 0:off 1:off 2:off 3:off 4:off 5:off 6:off
~]# grep SELINUX= /etc/selinux/config |grep ^[^#]
SELINUX=disabled
软件环境准备
OS Version：Red Hat Enterprise Linux Server release 6.3 (Santiago)
MySQL Version: MySQL-server-5.6.21-1.el6.x86_64
MMM Version:mysql-mmm-2.2.1
MySQL DownLoad Path: http://dev.mysql.com/downloads/mysql/
MMM DownLoad Path:http://mysql-mmm.org/_media/:mmm2:mysql-mmm-2.2.1.tar.gz
前提：MySQL主主同步已经配置完成
DB1>show processlist\G
*************************** 2. row ***************************
Id: 6
User: slave
Host: 192.168.56.102:39971
db: NULL
Command: Binlog Dump
Time: 658
State: Master has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
*************************** 3. row ***************************
Id: 7
User: system user
Host:
db: NULL
Command: Connect
Time: 135
State: Waiting for master to send event
Info: NULL
*************************** 4. row ***************************
Id: 8
User: system user
Host:
db: NULL
Command: Connect
Time: 135
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
4 rows in set (0.00 sec)
DB2>show processlist\G
*************************** 1. row ***************************
Id: 1
User: system user
Host:
db: NULL
Command: Connect
Time: 778
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
*************************** 2. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 778
State: Waiting for master to send event
Info: NULL
*************************** 4. row ***************************
Id: 20
User: slave
Host: 192.168.56.101:32870
db: NULL
Command: Binlog Dump
Time: 255
State: Master has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
4 rows in set (0.00 sec)
注意
One should set read-only=1 in the confiuration of all MySQL servers,
MMM will change
that to read-only=0 on the host with the active master role.
应该把所有节点设置为read_only模式，MMM将会修改active master为read-only=0提供wirte功能
MMM搭建步骤
1.安装Mysql-mmm
#所有节点都安装
~]# tar -zxvf mysql-mmm-2.2.1.tar.gz
~]# cd mysql-mmm-2.2.1
~]# make && make install
2.创建MMM代理和监控账号
#在DB1和DB2上分别执行
由于AB互为主从，故在一边执行、两边检查即可
#创建监控账号
DB1> GRANT REPLICATION CLIENT ON *.* TO 'mmm_monitor'@'192.168.56.%' IDENTIFIED BY 'monitor_password';
Query OK, 0 rows affected (0.02 sec)
#创建代理账号
DB1>GRANT SUPER,REPLICATION CLIENT,PROCESS ON *.* TO 'mmm_agent'@'192.168.56.%' IDENTIFIED BY 'agent_password';
Query OK, 0 rows affected (0.00 sec)
DB1>flush privileges;
Query OK, 0 rows affected (0.03 sec)
#检查A和B两db上是否都存在监控和代理账号
DB1>select user,host,password from mysql.user where user in ('mmm_monitor','mmm_agent');
+-------------+--------------+-------------------------------------------+
| user | host | password |
+-------------+--------------+-------------------------------------------+
| mmm_monitor | 192.168.56.% | *C1F414D9BAF378B656A849B31F9F8AF3125F558B |
| mmm_agent | 192.168.56.% | *9404D503D0FAE1825CFEF873D0E7A38A26BBAB0F |
+-------------+--------------+-------------------------------------------+
2 rows in set (0.02 sec)
DB2>select user,host,password from mysql.user where user in ('mmm_monitor','mmm_agent');
+-------------+--------------+-------------------------------------------+
| user | host | password |
+-------------+--------------+-------------------------------------------+
| mmm_monitor | 192.168.56.% | *C1F414D9BAF378B656A849B31F9F8AF3125F558B |
| mmm_agent | 192.168.56.% | *9404D503D0FAE1825CFEF873D0E7A38A26BBAB0F |
+-------------+--------------+-------------------------------------------+
2 rows in set (0.00 sec)
3.配置mysql-mmm
所有的配置选项都集合在了一个叫/etc/mysql-mmm/mmm_common.conf的单独文件中，系统中所有主机的该文件内容都是一样的, 配置完后不要忘记了拷贝这个文件到所有的主机（包括监控主机）！
a) mmm_common.conf
#在DB1上配置mmm_common.conf，并且拷贝给其他所有主机（包括监控主机）
~]# vim /etc/mysql-mmm/mmm_common.conf
active_master_role writer
cluster_interface eth2
pid_path /var/run/mmm_agentd.pid
bin_path /usr/lib/mysql-mmm/
replication_user slave #MySQL主主同步的帐号（这些要和前面设置的保持一致！）
replication_password mysqlab #同步的密码
agent_user mmm_agent #mmm-agent用户名
agent_password agent_password #mmm-agent密码
ip 192.168.56.101 #DB1的IP
mode master
peer db1
ip 192.168.56.102 #DB2的IP
mode master
peer db2
hosts db1, db2
ips 192.168.56.113 #write VIP
mode exclusive
hosts db1, db2
ips 192.168.56.111,192.168.56.112 #Read VIP
mode balanced
#拷贝给所有主机（包括监控主机）
~]# scp /etc/mysql-mmm/mmm_agent.conf 192.168.56.102:/etc/mysql-mmm/
~]# scp /etc/mysql-mmm/mmm_agent.conf 192.168.56.103:/etc/mysql-mmm/
b) mmm_agent.conf
在MySQL DB主机（DB1和DB2）上，需要编辑/etc/mysql-mmm/mmm_agent.conf文件
#根据其他主机的不同更改db1的值（db2就将db1更改成db2）
~]# cat /etc/mysql-mmm/mmm_agent.conf
include mmm_common.conf
this db1 #DB2上此处为db2
c) mmm_mon.conf
监控主机上我们需要编辑/etc/mysql-mmm/mmm_mon.conf文件
~]# vim /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.56.101, 192.168.56.102 #监控服务器ip
auto_set_online 60
monitor_user mmm_monitor #mmm_monitor用户名
monitor_password RepMonitor #mmm_monitor用户名
debug 0
4.启动MMM代理
#（在DB1和DB2主机上）编辑/etc/default/mysql-mmm-agent来开启
~]# cat /etc/default/mysql-mmm-agent
ENABLED=1
#然后启动它：
~]# /etc/init.d/mysql-mmm-agent start
Daemon bin: '/usr/sbin/mmm_agentd'
Daemon pid: '/var/run/mmm_agentd.pid'
Starting MMM Agent daemon... Ok
5.启动MMM监控
#在监控主机上
~]# /etc/init.d/mysql-mmm-monitor start
Daemon bin: '/usr/sbin/mmm_mond'
Daemon pid: '/var/run/mmm_mond.pid'
Starting MMM Monitor daemon: Ok
6.设置开机自启动MMM服务
#DB节点
~]# chkconfig --add mysql-mmm-agent
~]# chkconfig mysql-mmm-agent on
~]# chkconfig --list mysql-mmm-agent
mysql-mmm-agent 0:off 1:off 2:on 3:on 4:on 5:on 6:off
#Monitor节点
~]# chkconfig --add mysql-mmm-monitor
~]# chkconfig mysql-mmm-monitor on
~]# chkconfig --list mysql-mmm-monitor
mysql-mmm-monitor 0:off 1:off 2:on 3:on 4:on 5:on 6:off
7.查看监控状态
~]# mmm_control show
db1(192.168.56.101) master/ONLINE. Roles: reader(192.168.56.111), writer(192.168.56.113)
db2(192.168.56.102) master/ONLINE. Roles: reader(192.168.56.112)
~]# mmm_control checks
db2 ping [last change: 2014/12/02 18:18:41] OK
db2 mysql [last change: 2014/12/02 18:18:41] OK
db2 rep_threads [last change: 2014/12/02 18:18:41] OK
db2 rep_backlog [last change: 2014/12/02 18:18:41] OK: Backlog is null
db1 ping [last change: 2014/12/02 18:18:41] OK
db1 mysql [last change: 2014/12/02 18:18:41] OK
db1 rep_threads [last change: 2014/12/02 18:18:41] OK
db1 rep_backlog [last change: 2014/12/02 18:18:41] OK: Backlog is null
8.测试切换
#以下所有测试都是基于如下状态
~]# mmm_control show
db1(192.168.56.101) master/ONLINE. Roles: reader(192.168.56.112)
db2(192.168.56.102) master/ONLINE. Roles: reader(192.168.56.111), writer(192.168.56.113)
a) 停止DB1上MySQL服务
~]# tail -f /var/log/mysql-mmm/mmm_mond.log
tem error: 111
2014/12/03 10:37:50 ERROR Check 'mysql' on 'db1' has failed for 10 seconds! Message: ERROR: Connect error (host = 192.168.56.101:3306, user = mmm_monitor)! Lost connection to MySQL server at 'reading initial communication packet', system error: 111
2014/12/03 10:37:52 FATAL State of host 'db1' changed from ONLINE to HARD_OFFLINE (ping: OK, mysql: not OK)
2014/12/03 10:37:52 INFO Removing all roles from host 'db1':
2014/12/03 10:37:52 INFO Removed role 'reader(192.168.56.112)' from host 'db1'
2014/12/03 10:37:52 INFO Orphaned role 'reader(192.168.56.112)' has been assigned to 'db2'
结果
~]# mmm_control show
db1(192.168.56.101) master/HARD_OFFLINE. Roles:
db2(192.168.56.102) master/ONLINE. Roles: reader(192.168.56.111), reader(192.168.56.112), writer(192.168.56.113)
DB1上的VIP【reader(192.168.56.112)】会自动迁移DB2上
b) 停止DB2上MySQL服务
~]# tail -f /var/log/mysql-mmm/mmm_mond.log
2014/12/03 10:54:10 ERROR Check 'mysql' on 'db2' has failed for 10 seconds! Message: ERROR: Connect error (host = 192.168.56.102:3306, user = mmm_monitor)! Lost connection to MySQL server at 'reading initial communication packet', system error: 111
2014/12/03 10:54:10 INFO Check 'rep_backlog' on 'db1' is ok!
2014/12/03 10:54:11 FATAL State of host 'db2' changed from ONLINE to HARD_OFFLINE (ping: OK, mysql: not OK)
2014/12/03 10:54:11 INFO Removing all roles from host 'db2':
2014/12/03 10:54:11 INFO Removed role 'reader(192.168.56.111)' from host 'db2'
2014/12/03 10:54:11 INFO Removed role 'writer(192.168.56.113)' from host 'db2'
2014/12/03 10:54:11 INFO Orphaned role 'writer(192.168.56.113)' has been assigned to 'db1'
2014/12/03 10:54:11 INFO Orphaned role 'reader(192.168.56.111)' has been assigned to 'db1'
2014/12/03 10:54:29 INFO Check 'rep_threads' on 'db1' is ok!
2014/12/03 10:54:32 INFO Check 'rep_backlog' on 'db1' is ok!
~]# tail -f /var/log/mysql-mmm/mmm_agentd.log
2014/12/03 10:53:59 FATAL Couldn't allow writes: ERROR: Can't connect to MySQL (host = 192.168.56.102:3306, user = mmm_agent)! Lost connection to MySQL server at 'reading initial communication packet', system error: 111
2014/12/03 10:54:02 FATAL Couldn't allow writes: ERROR: Can't connect to MySQL (host = 192.168.56.102:3306, user = mmm_agent)! Lost connection to MySQL server at 'reading initial communication packet', system error: 111
2014/12/03 10:54:05 FATAL Couldn't allow writes: ERROR: Can't connect to MySQL (host = 192.168.56.102:3306, user = mmm_agent)! Lost connection to MySQL server at 'reading initial communication packet', system error: 111
2014/12/03 10:54:08 FATAL Couldn't allow writes: ERROR: Can't connect to MySQL (host = 192.168.56.102:3306, user = mmm_agent)! Lost connection to MySQL server at 'reading initial communication packet', system error: 111
2014/12/03 10:54:11 INFO We have some new roles added or old rules deleted!
2014/12/03 10:54:11 INFO Deleted: reader(192.168.56.111), writer(192.168.56.113)
2014/12/03 10:54:11 FATAL Couldn't deny writes: ERROR: Can't connect to MySQL (host = 192.168.56.102:3306, user = mmm_agent)! Lost connection to MySQL server at 'reading initial communication packet', system error: 111
结果
~]# mmm_control show
db1(192.168.56.101) master/ONLINE. Roles: reader(192.168.56.111), reader(192.168.56.112), writer(192.168.56.113)
db2(192.168.56.102) master/HARD_OFFLINE. Roles:
DB2上负责读的VIP(192.168.56.111) 以及负责写的VIP(192.168.56.113) 会自动迁移到 DB1上
启动DB2上的MySQL服务
Monitor~]# tail -f /var/log/mysql-mmm/mmm_mond.log
2014/12/03 11:02:13 INFO Check 'mysql' on 'db2' is ok!
2014/12/03 11:02:14 FATAL State of host 'db2' changed from HARD_OFFLINE to AWAITING_RECOVERY
2014/12/03 11:02:14 WARN Check 'rep_backlog' on 'db1' is in unknown state! Message: UNKNOWN: Connect error (host = 192.168.56.101:3306, user = mmm_monitor)! Can't connect to MySQL server on '192.168.56.101' (4)
2014/12/03 11:02:19 INFO Check 'rep_backlog' on 'db1' is ok!
2014/12/03 11:02:24 WARN Check 'rep_threads' on 'db1' is in unknown state! Message: UNKNOWN: Connect error (host = 192.168.56.101:3306, user = mmm_monitor)! Can't connect to MySQL server on '192.168.56.101' (4)
2014/12/03 11:02:26 WARN Check 'rep_backlog' on 'db1' is in unknown state! Message: UNKNOWN: Connect error (host = 192.168.56.101:3306, user = mmm_monitor)! Can't connect to MySQL server on '192.168.56.101' (4)
2014/12/03 11:02:29 INFO Check 'rep_threads' on 'db1' is ok!
2014/12/03 11:02:31 INFO Check 'rep_backlog' on 'db1' is ok!
2014/12/03 11:02:41 WARN Check 'rep_threads' on 'db1' is in unknown state! Message: UNKNOWN: Connect error (host = 192.168.56.101:3306, user = mmm_monitor)! Can't connect to MySQL server on '192.168.56.101' (4)
2014/12/03 11:02:46 INFO Check 'rep_threads' on 'db1' is ok!
2014/12/03 11:02:54 WARN Check 'rep_backlog' on 'db1' is in unknown state! Message: UNKNOWN: Connect error (host = 192.168.56.101:3306, user = mmm_monitor)! Can't connect to MySQL server on '192.168.56.101' (4)
2014/12/03 11:02:59 INFO Check 'rep_backlog' on 'db1' is ok!
2014/12/03 11:03:04 WARN Check 'rep_threads' on 'db1' is in unknown state! Message: UNKNOWN: Connect error (host = 192.168.56.101:3306, user = mmm_monitor)! Can't connect to MySQL server on '192.168.56.101' (4)
2014/12/03 11:03:09 INFO Check 'rep_threads' on 'db1' is ok!
2014/12/03 11:03:14 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
2014/12/03 11:03:14 INFO Moving role 'reader(192.168.56.112)' from host 'db1' to host 'db2'
DB1~]# tail -f /var/log/mysql-mmm/mmm_agentd.log
2014/12/03 11:03:15 INFO We have some new roles added or old rules deleted!
2014/12/03 11:03:15 INFO Deleted: reader(192.168.56.112)
DB2~]# tail -f /var/log/mysql-mmm/mmm_agentd.log
2014/12/03 11:03:14 INFO We have some new roles added or old rules deleted!
2014/12/03 11:03:14 INFO Added: reader(192.168.56.112)
2014/12/03 11:03:15 FATAL Couldn't configure IP '192.168.56.112' on interface 'eth1': undef
结果
~]# mmm_control show
db1(192.168.56.101) master/ONLINE. Roles: reader(192.168.56.111), writer(192.168.56.113)
db2(192.168.56.102) master/ONLINE. Roles: reader(192.168.56.112)
DB1上负责读的VIP(192.168.56.112)自动迁移到 DB2上，但是负责写的VIP，仍在DB1上。
c) 只读节点上stop slave
~]# mmm_control show
db1(192.168.56.101) master/ONLINE. Roles: reader(192.168.56.111), writer(192.168.56.113)
db2(192.168.56.102) master/ONLINE. Roles: reader(192.168.56.112)
#DB2 stop slave
mysql> stop slave;
Query OK, 0 rows affected (0.07 sec)
Monitor~]# tail -f /var/log/mysql-mmm/mmm_mond.log
2014/12/03 12:10:14 FATAL State of host 'db2' changed from ONLINE to REPLICATION_FAIL
2014/12/03 12:10:14 INFO Removing all roles from host 'db2':
2014/12/03 12:10:14 INFO Removed role 'reader(192.168.56.112)' from host 'db2'
2014/12/03 12:10:14 INFO Orphaned role 'reader(192.168.56.112)' has been assigned to 'db1'
2014/12/03 12:10:17 FATAL State of host 'db2' changed from REPLICATION_FAIL to ONLINE
2014/12/03 12:10:17 INFO Moving role 'reader(192.168.56.112)' from host 'db1' to host 'db2'
2014/12/03 12:10:24 INFO Check 'rep_threads' on 'db1' is ok!
2014/12/03 12:10:51 INFO Check 'rep_threads' on 'db1' is ok!
2014/12/03 12:11:15 INFO Check 'rep_backlog' on 'db1' is ok!
2014/12/03 12:11:20 FATAL State of host 'db2' changed from ONLINE to REPLICATION_FAIL
2014/12/03 12:11:20 INFO Removing all roles from host 'db2':
2014/12/03 12:11:20 INFO Removed role 'reader(192.168.56.112)' from host 'db2'
2014/12/03 12:11:20 INFO Orphaned role 'reader(192.168.56.112)' has been assigned to 'db1'
2014/12/03 12:11:23 FATAL State of host 'db2' changed from REPLICATION_FAIL to ONLINE
2014/12/03 12:11:23 INFO Moving role 'reader(192.168.56.112)' from host 'db1' to host 'db2'
2014/12/03 12:11:54 INFO Check 'rep_backlog' on 'db1' is ok!
2014/12/03 12:12:22 INFO Check 'rep_backlog' on 'db1' is ok!
2014/12/03 12:12:26 FATAL State of host 'db2' changed from ONLINE to REPLICATION_FAIL
2014/12/03 12:12:26 INFO Removing all roles from host 'db2':
2014/12/03 12:12:26 INFO Removed role 'reader(192.168.56.112)' from host 'db2'
2014/12/03 12:12:26 INFO Orphaned role 'reader(192.168.56.112)' has been assigned to 'db1'
2014/12/03 12:12:29 FATAL State of host 'db2' changed from REPLICATION_FAIL to ONLINE
2014/12/03 12:12:29 INFO Moving role 'reader(192.168.56.112)' from host 'db1' to host 'db2'
2014/12/03 12:12:30 INFO Check 'rep_threads' on 'db1' is ok!
2014/12/03 12:12:53 INFO Check 'rep_threads' on 'db1' is ok!
2014/12/03 12:13:30 FATAL State of host 'db2' changed from ONLINE to REPLICATION_FAIL
2014/12/03 12:13:30 FATAL Host db2 is flapping!
2014/12/03 12:13:30 INFO Removing all roles from host 'db2':
2014/12/03 12:13:30 INFO Removed role 'reader(192.168.56.112)' from host 'db2'
2014/12/03 12:13:31 INFO Orphaned role 'reader(192.168.56.112)' has been assigned to 'db1'
2014/12/03 12:13:33 FATAL State of host 'db2' changed from REPLICATION_FAIL to AWAITING_RECOVERY (because it's flapping)
2014/12/03 12:13:45 INFO Check 'rep_threads' on 'db1' is ok!
结果
~]# mmm_control show
db1(192.168.56.101) master/ONLINE. Roles: reader(192.168.56.111), reader(192.168.56.112), writer(192.168.56.113)
db2(192.168.56.102) master/AWAITING_RECOVERY. Roles:
DB2上的VIP（192.168.56.112）会自动迁移到DB1上
d) 读写节点上stop slave
#正常
~]# mmm_control show
db1(192.168.56.101) master/ONLINE. Roles: reader(192.168.56.111), writer(192.168.56.113)
db2(192.168.56.102) master/ONLINE. Roles: reader(192.168.56.112)
#db1上stop slave
结果
~]# mmm_control show
db1(192.168.56.101) master/ONLINE. Roles: reader(192.168.56.111), writer(192.168.56.113)
db2(192.168.56.102) master/ONLINE. Roles: reader(192.168.56.112)
无任何变化,理论上也应该是对现有的环境无任何影响
e) 停止MMM监控主机上的monitor服务
~]# /etc/init.d/mysql-mmm-monitor stop
Daemon bin: '/usr/sbin/mmm_mond'
Daemon pid: '/var/run/mmm_mond.pid'
Shutting down MMM Monitor daemon: .. Ok
Monitor~]# tail -f /var/log/mysql-mmm/mmm_mond.log
2014/12/03 14:09:28 INFO Signal received: exiting...
2014/12/03 14:09:28 INFO Shutting down checker 'ping_ip'...
2014/12/03 14:09:28 INFO Shutting down checker 'mysql'...
2014/12/03 14:09:29 INFO Shutting down checker 'rep_backlog'...
2014/12/03 14:09:29 INFO Shutting down checker 'rep_threads'...
2014/12/03 14:09:29 INFO Shutting down checker 'ping'...
2014/12/03 14:09:29 INFO END
2014/12/03 14:09:29 INFO Child exited normally (with exitcode 0), shutting down
结果
DB1 ~]# ip a|grep eth1
3: eth1:
inet 192.168.56.101/24 brd 192.168.56.255 scope global eth1
inet 192.168.56.111/32 scope global eth1
inet 192.168.56.113/32 scope global eth1
DB2 ~]# ip a|grep eth1
3: eth1:
inet 192.168.56.102/24 brd 192.168.56.255 scope global eth1
inet 192.168.56.112/32 scope global eth1
VIP都还在之前的节点上
启动MMM监控服务
~]# mmm_control show
db1(192.168.56.101) master/ONLINE. Roles: reader(192.168.56.111), writer(192.168.56.113)
db2(192.168.56.102) master/ONLINE. Roles: reader(192.168.56.112)
结果
对DB1和DB2上的相关服务无影响。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/14710393/viewspace-1651913/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- Install and configure VMware_vSphere 5
- Docker run OGG Classic Architecture container from CIFS with docker-volume-netsh
- 【OERR】 oerr ora 错误 12.2.0.1 【20000-39999】
- Oracle Online Redefinition在线重定义（下）
- Oracle Online Redefinition在线重定义（上）
- Oracle Online Redefinition在线重定义（中）
- Oracle中查找依赖的无效对象(invalid object)
- goldengate部署实施案例
- goldengate常用函数使用说明
- goldengate基于表复制
