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

时间:2021-09-20 02:34:23

1 install mysql

请参考http://www.zzvips.com/article/51606.html

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

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_user mmm_monitor

monitor_password mmm_monitor

 

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