MySQL高可用MMM方案安装部署分享

1 install mysql

请参考

2. basic configuration of master 1

3. create users
grant replication client on *.* to 'mmm_monitor'@'%' identified by 'mmm_monitor';
grant super, replication client, process on *.* to 'mmm_agent'@'%' identified by 'mmm_agent';
grant replication slave on *.* to 'repl'@'10.88.49.%' identified by 'repl';
grant insert,create,delete,update,select on *.* to 'tim'@'%' identified by 'tim';
note: don not use a replication_password longer than 32 characters

4. synchronisation of data between both databases

5. setup replication
set m-s:
change master to master_host='10.88.49.119',master_log_file='mysql56-bin.000026',master_log_pos=332, master_user='repl',master_password='repl';

6. install mmm
6.1 download mmm.tar.gz
wget http://mysql-mmm.org/_media/:mmm2:mysql-mmm-2.2.1.tar.gz
6.2 mv :mmm2:mysql-mmm-2.2.1.tar.gz mysql-mmm-2.2.1.tar.gz
tar -xvf mysql-mmm-2.2.1.tar.gz
cd mysql-mmm-2.2.1
make
cmake
[] don not require make and make install, there have *.conf in /etc/mysql-mmm folder.

7. install lib package
yum install -y perl-*
yum install -y libart_lgpl.x86_64
yum install -y mysql-mmm.noarch fail
yum install -y rrdtool.x86_64
yum install -y rrdtool-perl.x86_64
7.1 []another way to install lib package in network
cpan -i algorithm::diff class::singleton dbi dbd::mysql log::dispatch log::log4perl mail::send net::ping proc::daemon time::hires params::validate net::arp

8. config mmm of db host
vim /etc/mysql-mmm/mmm_common.conf
don not forget to copy this file to all other hosts (including the monitoring host).
#bugsfor$
scp /etc/mysql-mmm/mmm_common.conf 10.88.49.119:/etc/mysql-mmm/
scp /etc/mysql-mmm/mmm_common.conf 10.88.49.122:/etc/mysql-mmm/
scp /etc/mysql-mmm/mmm_common.conf 10.88.49.123:/etc/mysql-mmm/

on the database hosts we need to edit /etc/mysql-mmm/mmm_agent.conf. change “db1” accordingly on the other hosts:
chkconfig --add mysql-mmm-agent

9. config monitor
on the monitor host(10.88.49.123) we need to edit /etc/mysql-mmm/mmm_mon.conf:
include mmm_common.conf
<monitor>
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
auto_set_online 5
ping_ips 10.88.49.254,10.88.49.130,10.88.49.131,10.88.49.132,10.88.49.133,10.88.49.134
</monitor>
<host default>
monitor_user mmm_monitor
monitor_password mmm_monitor
</host>
debug 0
ping_ips are some ips that are pinged to determine whether the network connection of the monitor is ok. i used my switch of gateway (10.88.49.254) and the four database server.follow this
[root@oracle mysql-mmm]# cat /etc/sysconfig/network-scripts/ifcfg-eth0
device=eth0
ipaddr=10.88.49.118
netwask=255.255.254.0
gateway=10.88.49.254
dns1=10.106.185.143
dns2=10.106.185.138
onboot=yes
bootproto=none
type=ethernet
hwaddr=00:15:5d:01:6a:0c

10. start in database hosts
chkconfig --add mysql-mmm-agent
[root@oracle ~]# mysql-mmm-agent start
-bash: mysql-mmm-agent: command not found
[root@oracle ~]# service mysql-mmm-agent start
daemon bin: '/usr/sbin/mmm_agentd'
daemon pid: '/var/run/mmm_agentd.pid'
starting mmm agent daemon... can not locate proc/daemon.pm in @inc (@inc contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /usr/sbin/mmm_agentd line 7.
begin failed--compilation aborted at /usr/sbin/mmm_agentd line 7.
[root@oracle ~]# cpan proc::daemon
[root@oracle ~]# cpan log::log4perl
[root@oracle ~]# /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

11. start in monitor hosts
chkconfig --add mysql-mmm-monitor
[root@localhost mysql-mmm-2.2.1]# service mysql-mmm-monitor start
daemon bin: '/usr/sbin/mmm_mond'
daemon pid: '/var/run/mmm_mond.pid'
starting mmm monitor daemon: can not locate proc/daemon.pm in @inc (@inc contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /usr/sbin/mmm_mond line 11.
begin failed--compilation aborted at /usr/sbin/mmm_mond line 11.
failed
[root@oracle ~]# cpan proc::daemon
[root@oracle ~]# cpan log::log4perl
[root@localhost mysql-mmm-2.2.1]# service mysql-mmm-monitor start
daemon bin: '/usr/sbin/mmm_mond'
daemon pid: '/var/run/mmm_mond.pid'
starting mmm monitor daemon: ok

12. check fail
[root@oracle mysql-mmm]# ping 10.88.49.130
ping 10.88.49.130 (10.88.49.130) 56(84) bytes of data.
from 10.88.49.118 icmp_seq=2 destination host unreachable
from 10.88.49.118 icmp_seq=3 destination host unreachable
from 10.88.49.118 icmp_seq=4 destination host unreachable
from 10.88.49.118 icmp_seq=6 destination host unreachable
from 10.88.49.118 icmp_seq=7 destination host unreachable
from 10.88.49.118 icmp_seq=8 destination host unreachable

12.1 debug error info
在agent.conf 和 monitor.conf 分别加上 debug 1
然后看输出的日志

[root@localhost mysql-mmm]# mmm_control show
db1(10.88.49.118) master/awaiting_recovery. roles:
db2(10.88.49.119) master/awaiting_recovery. roles:
db3(10.88.49.122) slave/awaiting_recovery. roles:
[root@localhost mysql-mmm]# mmm_control set_online db1
ok: state of 'db1' changed to online. now you can wait some time and check its new roles!
[root@localhost mysql-mmm]# mmm_control set_online db2
ok: state of 'db2' changed to online. now you can wait some time and check its new roles!
[root@localhost mysql-mmm]# mmm_control set_online db3
ok: state of 'db3' changed to online. now you can wait some time and check its new roles!

12.2 ping vip fail
2013/02/19 10:00:15 fatal couldn't configure ip '10.88.49.131' on interface 'eth1': undef
2013/02/19 10:00:15 debug executing /usr/lib/mysql-mmm//agent/mysql_allow_write
can't locate net/arp.pm in @inc (@inc contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /usr/share/perl5/vendor_perl/mmm/agent/helpers/network.pm line 11.
[1.1]
cpan net/arp.pm
yum install libuuid* error
[2.1] if [1.1] fail , try this
[root@localhost mysql-mmm]# perl -mcpan -e shell
cpan> install net::arp
[ok]

12.3 fail info :
2013/02/19 10:25:23 info added: reader(10.88.49.131), writer(10.88.49.130)
2013/02/19 10:25:23 debug executing /usr/lib/mysql-mmm//agent/configure_ip eth1 10.88.49.131
device "eth1" does not exist.
2013/02/19 10:25:23 fatal couldn't configure ip '10.88.49.131' on interface 'eth1': error: could not check if ip 10.88.49.131 is configured on eth1:
2013/02/19 10:25:23 debug executing /usr/lib/mysql-mmm//agent/sync_with_master
2013/02/19 10:25:23 debug executing /usr/lib/mysql-mmm//agent/mysql_allow_write
2013/02/19 10:25:23 debug executing /usr/lib/mysql-mmm//agent/configure_ip eth1 10.88.49.130
device "eth1" does not exist.
2013/02/19 10:25:23 fatal couldn't configure ip '10.88.49.130' on interface 'eth1': error: could not check if ip 10.88.49.130 is configured on eth1:
2013/02/19 10:25:23 debug fetching uptime from /proc/uptime
2013/02/19 10:25:23 debug uptime is 158489.10
2013/02/19 10:25:23 debug daemon: answer = 'ok: status applied successfully!'
[ok] cluster_interface should set the

12.4 when connect reader vip,lost package, info :
[root@localhost mysql-mmm]# ping 10.88.49.134
ping 10.88.49.134 (10.88.49.134) 56(84) bytes of data.
64 bytes from 10.88.49.134: icmp_seq=3 ttl=64 time=0.265 ms
64 bytes from 10.88.49.134: icmp_seq=6 ttl=64 time=0.699 ms
64 bytes from 10.88.49.134: icmp_seq=9 ttl=64 time=0.482 ms
64 bytes from 10.88.49.134: icmp_seq=12 ttl=64 time=0.405 ms
64 bytes from 10.88.49.134: icmp_seq=15 ttl=64 time=0.430 ms

14. check all
[root@localhost ~]# mmm_control checks
db2 ping [last change: 2013/02/19 12:41:45] ok
db2 mysql [last change: 2013/02/19 12:41:45] ok
db2 rep_threads [last change: 2013/02/19 12:41:45] ok
db2 rep_backlog [last change: 2013/02/19 12:41:45] ok: backlog is null
db3 ping [last change: 2013/02/19 12:41:45] ok
db3 mysql [last change: 2013/02/19 12:41:45] ok
db3 rep_threads [last change: 2013/02/19 12:41:45] ok
db3 rep_backlog [last change: 2013/02/19 12:41:45] ok: backlog is null
db1 ping [last change: 2013/02/19 12:41:45] ok
db1 mysql [last change: 2013/02/19 12:41:45] ok
db1 rep_threads [last change: 2013/02/19 12:41:45] ok
db1 rep_backlog [last change: 2013/02/19 12:41:45] ok: backlog is null

15 check m<->m<->s change
15.1change writer from 10.88.49.118 to 10.88.49.119 stop mysqld in db1 10.88.49.118
show slave status on 10.88.49.122, see master_host is '10.88.49.118'
[root@oracle ~]# service mysqld56 stop
shutting down mysql... success!

15.1.1 show info in monitor host log
[root@localhost ~]# tail -f /var/log/mysql-mmm/mmm_mond.log
2013/02/20 10:34:42 info removing all roles from host 'db1':
2013/02/20 10:34:42 info removed role 'reader(10.88.49.134)' from host 'db1'
2013/02/20 10:34:42 info removed role 'writer(10.88.49.130)' from host 'db1'
2013/02/20 10:34:42 info orphaned role 'writer(10.88.49.130)' has been assigned to 'db2'
2013/02/20 10:34:42 info orphaned role 'reader(10.88.49.134)' has been assigned to 'db3'
15.1.2 show info in slave host of 10.88.49.122, slave will change its master_host
[root@localhost ~]# mysql -p3307 -s /data56/mysql.sock -p123456
mysql> show slave status\g
*************************** 1. row ***************************
slave_io_state: waiting for master to send event
master_host: 10.88.49.119

15.1.3 but there are some bad info in monitor host of 10.88.49.123, when you run 'mmm_control show', it may hold on .
2013/02/20 10:37:25 debug listener: waiting for connection...
2013/02/20 10:37:28 debug listener: waiting for connection...

15.1.4 [] why ?
i have solved this problem, it maybe occur when:
(1): 'peer' paramter is wrong .
(2): 'ping_ips' and 'ips' are wrong

15.2 change writer from 10.88.49.119 to 10.88.49.118
15.2.1 start mysqld in 10.88.49.118, stop mysqld in 10.88.49.119, run 'mmm_control set_online db1' in monitor_host
15.2.2 show slave info in slave_host of 10.88.49.122
[root@localhost ~]# mysql -p3307 -s /data56/mysql.sock -p123456
mysql> show slave status\g
*************************** 1. row ***************************
slave_io_state: waiting for master to send event
master_host: 10.88.49.118
15.3 stop mysqld on slave_host of 10.88.49.122
15.3.1 show info in monitor_host
[root@localhost mysql-mmm]# mmm_control show
db1(10.88.49.118) master/online. roles: reader(10.88.49.134), reader(10.88.49.135), writer(10.88.49.130)
db2(10.88.49.119) master/online. roles: reader(10.88.49.131), reader(10.88.49.132), reader(10.88.49.133)
db3(10.88.49.122) slave/hard_offline. roles:
[] the ips change to mm.

15.3.2 start mysqld on slave_host of 10.88.49.122

15.3.3 show info in monitor_host
[root@localhost mysql-mmm]# mmm_control show
db1(10.88.49.118) master/online. roles: reader(10.88.49.134), reader(10.88.49.135), writer(10.88.49.130)
db2(10.88.49.119) master/online. roles: reader(10.88.49.131), reader(10.88.49.132), reader(10.88.49.133)
db3(10.88.49.122) slave/awaiting_recovery. roles:
[] need set online
[root@localhost mysql-mmm]# mmm_control set_online db3
ok: state of 'db3' changed to online. now you can wait some time and check its new roles!
[root@localhost mysql-mmm]# mmm_control show
db1(10.88.49.118) master/online. roles: reader(10.88.49.135), writer(10.88.49.130)
db2(10.88.49.119) master/online. roles: reader(10.88.49.131), reader(10.88.49.133)
db3(10.88.49.122) slave/online. roles: reader(10.88.49.132), reader(10.88.49.134)

15.4 stop master_host db2 of 10.88.49.119
15.4.1 show info in monitor_host
[root@localhost mysql-mmm]# mmm_control show
db1(10.88.49.118) master/online. roles: reader(10.88.49.131), reader(10.88.49.135), writer(10.88.49.130)
db2(10.88.49.119) master/hard_offline. roles:
db3(10.88.49.122) slave/online. roles: reader(10.88.49.132), reader(10.88.49.133), reader(10.88.49.134)
see db2 is hard_offline

15.4.2 start master_host db2 of 10.88.49.119
[root@localhost mysql-mmm]# mmm_control show
db1(10.88.49.118) master/online. roles: reader(10.88.49.131), reader(10.88.49.135), writer(10.88.49.130)
db2(10.88.49.119) master/awaiting_recovery. roles:
db3(10.88.49.122) slave/online. roles: reader(10.88.49.132), reader(10.88.49.133), reader(10.88.49.134)
see db2 is awaiting_recovery, so need set online
[root@localhost mysql-mmm]# mmm_control set_online db2
ok: state of 'db2' changed to online. now you can wait some time and check its new roles!
[root@localhost mysql-mmm]# mmm_control show
db1(10.88.49.118) master/online. roles: reader(10.88.49.135), writer(10.88.49.130)
db2(10.88.49.119) master/online. roles: reader(10.88.49.131), reader(10.88.49.132)
db3(10.88.49.122) slave/online. roles: reader(10.88.49.133), reader(10.88.49.134)
see db2 is online

15.5 change writer to db2
mmm_control move_role writer db2

相关文章:

http://blog.csdn.net/hguisu/article/details/7349562
http://blog.chinaunix.net/uid-28437434-id-3471237.html
http://mysql-mmm.org/downloads ;
http://mysql-mmm.org/mmm2:guide
http://dev.mysql.com/doc/internals/en/optimizer-primary-optimizations.html