积累经验
2014年（55）
分类： Mysql/postgreSQL
2014-01-22 22:48:56
MMM即Master-Master Replication Managerfor MySQL（mysql主主复制管理器）关于mysql主主复制配置的监控、故障转移和管理的一套可伸缩的脚本套件（在任何时候只有一个节点可以被写入），这个套件也能对居于标准的主从配置的任意数量的从服务器进行读负载均衡，所以你可以用它来在一组居于复制的服务器启动虚拟ip，除此之外，它还有实现数据备份、节点之间重新同步功能的脚本。
MySQL本身没有提供replication failover的解决方案，通过MMM方案能实现服务器的故障转移，从而实现mysql的高可用。MMM不仅能提供浮动IP的功能，更牛的是如果当前的主服务器挂掉后，会将你后端的从自动转向新的主服务器进行同步复制，不用手工更改同步资料。
MMM流程：
1．准备环境，6台机器以上，拓扑图，规划IP，同步hosts，时间
2．安装包
3．配置双master
4．配置slave
5．安装mysql-mmm
6．配置monitor mmm-common.conf 定义d1-d4.IP.虚拟IP
7．Agent端mmm-agent.conf db1-db4
8．Monitor端 monIP地址. Ping真实IP
9．授权用户添加mmm.agent用户（4条）mmm.mom用户（1条）
10．启动
Red Hat Enterprise Linux Server release 6.3
192.168.11.128 desktop128.example.com monitor
192.168.11.130 desktop130.example.com master1
192.168.11.131 desktop131.example.com master2
192.168.11.133 desktop133.example.com slave1
192.168.11.134 desktop134.example.com slave2
虚拟IP：
192.168.11.200 (w)
192.168.11.201 (r)
192.168.11.202 (r)
192.168.11.203 (r)
192.168.11.204 (r)
host
iptables -F
setenforce 0
cat >> /etc/hosts << ENDF
192.168.11.128 desktop128.example.com
192.168.11.133 desktop133.example.com
192.168.11.130 desktop130.example.com
192.168.11.131 desktop131.example.com
192.168.11.134 desktop134.example.com
ENDF
--------------------------------------------------------------
（监管端需要安装）
yum install libart_lgpl perl-Date-Manip perl-XML-DOM perl-XML-DOM-XPath perl-XML-Parser perl-XML-RegExp rrdtool perl perl-DBD-MySQL perl-Params-Validate perl-MailTools perl-Time-HiRes perl-Class-Singleton -y
（客户端需要安装）
yum install libart_lgpl perl-Date-Manip perl-XML-DOM perl-XML-DOM-XPath perl-XML-Parser perl-XML-RegExp rrdtool perl perl-DBD-MySQL perl-Params-Validate perl-MailTools perl-Time-HiRes mysql mysql-server mysql-connector-odbc -y
(监控、客户也可以全部安装)
yum install libart_lgpl perl-Date-Manip perl-XML-DOM perl-XML-DOM-XPath perl-XML-Parser perl-XML-RegExp rrdtool perl perl-DBD-MySQL perl-Params-Validate perl-MailTools perl-Time-HiRes mysql mysql-server mysql-connector-odbc perl-Class-Singleton -y
光盘中没有的包
rpm -ivh perl-Algorithm-Diff-1.1902-9.el6.noarch.rpm perl-Log-Dispatch-2.26-1.el6.rf.noarch.rpm perl-Log-Log4perl-1.26-1.el6.rf.noarch.rpm perl-Net-ARP-1.0.6-2.1.el6.x86_64.rpm perl-Proc-Daemon-0.06-1.el6.noarch.rpm perl-Proc-ProcessTable-0.44-4.el6.x86_64.rpm rrdtool-perl-1.3.8-6.el6.x86_64.rpm perl-IPC-Shareable-0.60-2.el6.rf.noarch.rpm
-----------------------
2台master修改配置文件
master1
在[mysqld]标签下加入
server_id = 1
log_bin = mysql
log_slave_updates = 1
auto_increment_increment = 2
auto_increment_offset = 1 //区分2台master
master2
在[mysqld]标签下加入
server_id = 2
log_bin = mysql
log_slave_updates = 1
auto_increment_increment = 2
auto_increment_offset = 2
------------------------------------------
同步两台机器的数据库
[root@desktop130 ~]# mysqldump --all-databases > /tmp/mysql-all.sql
[root@desktop130 ~]# scp /tmp/mysql-all.sql root@desktop131.uplooking.com:/tmp
[root@desktop131 ~]# mysql < /tmp/mysql-all.sql
-----------------------------------------------
双向master //都在master1授权，change master分别做
[root@desktop130 ~]# cat << ENDF | mysql
> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.11.130' IDENTIFIED BY
'uplooking';
> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.11.131' IDENTIFIED BY
'uplooking';
[root@desktop131 mysql]# mysql
mysql> change master to master_host="192.168.11.130",master_user="slave",master_password="uplooking",master_log_file="mysql.000001",master_log_pos=106;
mysql> slave start;
mysql> show slave status\G
[root@desktop130 mysql]# mysql
mysql> change master to master_host="192.168.11.131",master_user="slave",master_password="uplooking",master_log_file="mysql.000002",master_log_pos=506;
mysql> slave start;
mysql> show slave status\G
---------------------------------------------------
2台slave
[root@desktop133 ~]# vi /etc/my.cnf
在[mysqld]标签下加入
server-id = 3
[root@desktop133 ~]# service mysqld start
[root@desktop134 ~]# vi /etc/my.cnf
在[mysqld]标签下加入
server-id = 4
[root@desktop134 ~]# service mysqld start
------------------------------
配置两台mysql的slave模式
master1中授权
[root@destop130 ~]# cat << ENDF | mysql
> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.11.133' IDENTIFIED BY
'uplooking';
> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.11.134' IDENTIFIED BY
'uplooking';
> FLUSH PRIVILEGES;
------------------------------------------------
133,134同步主分别在slave1 slave2 change master
mysql> change master to master_host="192.168.11.130",master_user="slave",master_password="uplooking",master_log_file="mysql.000001",master_log_pos=106;
slave start
show slave status\G
----------------------------------
安装配置mysql-mmm
在每台机器上都安装mysql-mmm
tar xf mysql-mmm-2.2.1.tar.gz
cd mysql-mmm-2.2.1
make install
---------------------------------------------
修改5台机器mmm_common.conf配置文件
[root@desktop131 mysql-mmm-2.2.1]# 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 slave
replication_password uplooking
agent_user mmm_agent
agent_password uplooking
ip 192.168.11.130
mode master
peer db2
ip 192.168.11.131
mode master
peer db1
ip 192.168.11.133
mode slave
ip 192.168.11.134
mode slave
hosts db1, db2 // 写
ips 193.168.11.200 // 虚拟IP
mode exclusive
hosts db1, db2, db3,db4 // 读
ips 192.168.11.201,192.168.11.202,192.168.11.203,192.168.11.204 //虚拟ip
mode balanced
将此文件同步到所有机器上
[root@desktop130 ~]# for i in 128 131 133 134 ; do scp /etc/mysql-mmm/mmm_common.conf
192.168.11.$i:/etc/mysql-mmm/ ; done
-------------------------------------
修改agent端配置文件
[root@desktop130 ~]# vi /etc/mysql-mmm/mmm_agent.conf
include mmm_common.conf
this db1
[root@desktop131 ~]# vi /etc/mysql-mmm/mmm_agent.conf
include mmm_common.conf
this db2
[root@desktop133 ~]# vi /etc/mysql-mmm/mmm_agent.conf
include mmm_common.conf
this db3
[root@desktop134 ~]# vi /etc/mysql-mmm/mmm_agent.conf
include mmm_common.conf
this db4
----------------------------------------------
修改monitor端配置文件
[root@desktop128 ~]# vi /etc/mysql-mmm/mmm_mon.conf
include mmm_common.conf
ip 192.168.11.128
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.0.1, 192.168.0.2, 192.168.0.3 // 真实IP
monitor_user mmm_monitor
monitor_password uplooking
debug 0
---------------------------------------------------------------
master1中授权mmm_agent
[root@desktop130 ~]# mysql
mysql> grant super,replication client,process on *.* to mmm_agent@192.168.11.130 identified by 'uplooking';
mysql> grant super,replication client,process on *.* to mmm_agent@192.168.11.131 identified by 'uplooking';
mysql> grant super,replication client,process on *.* to mmm_agent@192.168.11.133 identified by 'uplooking';
mysql> grant super,replication client,process on *.* to mmm_agent@192.168.11.134 identified by 'uplooking';
master1中授权mmm_monitor
[root@desktop130 ~]# mysql
mysql> grant replication client on *.* to mmm_monitor@192.168.11.128 identified by 'uplooking';
mysql> flush privileges;
起monitor服务
[root@desktop128 mysql-mmm]# /etc/init.d/mysql-mmm-monitor start
4台agent起服务
[root@desktop130 mysql-mmm]# service mysql-mmm-agent start
[root@desktop128 ~]# netstat -ntlp //出现以下信息表明监听成功
tcp 0 0 127.0.0.1:9988 0.0.0.0:* LISTEN 20608/mmm_mond
[root@desktop130 ~]# netstat -ntlp
tcp 0 0 192.168.11.130:9989 0.0.0.0:* LISTEN 14782/mmm_agentd
[root@desktop128 mysql-mmm]# mmm_control show //找到4台agent，但没有Online
db1(192.168.11.130) master/AWAITING_RECOVERY. Roles:
db2(192.168.11.131) master/AWAITING_RECOVERY. Roles:
db3(192.168.11.133) slave/AWAITING_RECOVERY. Roles:
db4(192.168.11.134) slave/AWAITING_RECOVERY. Roles:
[root@desktop128 mysql-mmm]#
mmm_control set_online db1
mmm_control set_online db2
mmm_control set_online db3
mmm_control set_online db4
如遇以下报错，重启下mysqld
[root@desktop128 Desktop]# mmm_control set_online db3
ERROR: Host 'db3' is 'HARD_OFFLINE' at the moment. It can't be switched to ONLINE.
[root@desktop128 mysql-mmm]# mmm_control show
db1(192.168.11.130) master/ONLINE. Roles: reader(192.168.11.202)
db2(192.168.11.131) master/ONLINE. Roles: reader(192.168.11.201), writer(193.168.11.200)
db3(192.168.11.133) slave/ONLINE. Roles: reader(192.168.11.203)
db4(192.168.11.134) slave/ONLINE. Roles: reader(192.168.11.204)
-----------------------------------------------------------------------------
故障模拟
slave1,slave2同步的是master1，把master1挂掉
[root@desktop130 ~]# iptables -A INPUT -s 192.168.11.128 -j DROP
//添加一条规则，INPUT链 –s 拒绝monitor端网络数据包 –j 直接丢掉
[root@desktop128 Desktop]# mmm_control show
# Warning: agent on host db1 is not reachable
db1(192.168.11.130) master/HARD_OFFLINE. Roles:
db2(192.168.11.131) master/ONLINE. Roles: reader(192.168.11.204), writer(193.168.11.200)
db3(192.168.11.133) slave/ONLINE. Roles: reader(192.168.11.201), reader(192.168.11.203)
db4(192.168.11.134) slave/ONLINE. Roles: reader(192.168.11.202)
然后再恢复master1，看看结果
[root@desktop130 Desktop]#iptables –D INPUT 1
[root@desktop128 Desktop]# mmm_control set_online db1
[root@desktop128 Desktop]# mmm_control show
db1(192.168.11.130) master/ONLINE. Roles: reader(192.168.11.201)
db2(192.168.11.131) master/ONLINE. Roles: reader(192.168.11.204), writer(193.168.11.200)
db3(192.168.11.133) slave/ONLINE. Roles: reader(192.168.11.203)
db4(192.168.11.134) slave/ONLINE. Roles: reader(192.168.11.202)
