五.MMM高可用服务器构建
1.如果你是编译安装的mysql-mmm,那么你就需要安装依赖的perl库了
由于我这里是EPEL源提供的mysql-mmm,所以自动解决了依赖问题;建议你也配置好EPEL源后进行安装,非常方便;
mysql-mmm需要依赖的所有文件如下:
dejavu-lgc-sans-mono-fonts.noarch 0:2.30-2.el6
perl-Date-Manip.noarch 0:6.24-1.el6
perl-Email-Date-Format.noarch 0:1.002-5.el6
perl-Log-Dispatch.noarch 0:2.27-1.el6
perl-Log-Dispatch-FileRotate.noarch 0:1.19-4.el6
perl-Log-Log4perl.noarch 0:1.30-1.el6
perl-MIME-Lite.noarch 0:3.027-2.el6
perl-MIME-Types.noarch 0:1.28-2.el6
perl-Mail-Sender.noarch 0:0.8.16-3.el6
perl-Mail-Sendmail.noarch 0:0.79-12.el6
perl-MailTools.noarch 0:2.04-4.el6
perl-Params-Validate.x86_64 0:0.92-3.el6
perl-TimeDate.noarch 1:1.16-13.el6
perl-XML-DOM.noarch 0:1.44-7.el6
perl-XML-RegExp.noarch 0:0.03-7.el6
perl-YAML-Syck.x86_64 0:1.07-4.el6
rrdtool.x86_64 0:1.3.8-7.el6
rrdtool-perl.x86_64 0:1.3.8-7.el6
perl-Algorithm-Diff.noarch 0:1.1902-9.el6
perl-Class-Singleton.noarch 0:1.4-6.el6
perl-DBD-MySQL.x86_64 0:4.013-3.el6
perl-Net-ARP.x86_64 0:1.0.6-2.1.el6
perl-Path-Class.noarch 0:0.25-1.el6
perl-Proc-Daemon.noarch 0:0.14-9.el6
perl-Proc-ProcessTable.x86_64 0:0.48-1.el6
2.mysql-mmm软件包介绍
mysql-mmm软件包包含四个软件:
主软件包:
mysql-mmm.noarch 0:2.2.1-2.el6
客户端软件包:
mysql-mmm-agent.noarch 0:2.2.1-2.el6
监控软件包:
mysql-mmm-monitor.noarch 0:2.2.1-2.el6
工具包:
mysql-mmm-tools.noarch 0:2.2.1-2.el6
3.在所有节点安装mysql-mmm的软件包组;
# yum install -y mysql-mmm*
查看安装的后生成的文件:
[root@node3 ~]# rpm -ql mysql-mmm
/etc/logrotate.d/mysql-mmm
/etc/mysql-mmm
/etc/mysql-mmm/mmm_common.conf
/usr/share/doc/mysql-mmm-2.2.1
/usr/share/doc/mysql-mmm-2.2.1/COPYING
/usr/share/doc/mysql-mmm-2.2.1/INSTALL
/usr/share/doc/mysql-mmm-2.2.1/README
/usr/share/doc/mysql-mmm-2.2.1/VERSION
/usr/share/doc/mysql-mmm-2.2.1/mysql-mmm-2.2.1.pdf
/usr/share/perl5/vendor_perl/MMM/Common
/usr/share/perl5/vendor_perl/MMM/Common/Angel.pm
/usr/share/perl5/vendor_perl/MMM/Common/Config.pm
/usr/share/perl5/vendor_perl/MMM/Common/Log.pm
/usr/share/perl5/vendor_perl/MMM/Common/PidFile.pm
/usr/share/perl5/vendor_perl/MMM/Common/Role.pm
/usr/share/perl5/vendor_perl/MMM/Common/Socket.pm
/usr/share/perl5/vendor_perl/MMM/Common/Uptime.pm
/var/lib/mysql-mmm
/var/log/mysql-mmm
/var/run/mysql-mmm
4.在node3和node4两个主服务器节点配置mmm代理和监控账号的权限
12345 #前面已经执行过了,就不用执行了,让复制,monitor使用同一个用户,注意权限就可以行了。
MariaDB [(none)]> grant replication client,replication slave on *.* to 'repluser'@'172.16.%.%' identified by 'replpass';
MariaDB [(none)]> grant super,replication client,replication slave,process on *.* to 'mmm_agent'@'172.16.%.%' identified by 'agent_password';
MariaDB [(none)]> flush privileges;
在node3和node4节点都执行一遍;
5.配置mysql-mmm的配置文件;
所有的配置选项都集合在了一个叫/etc/mysql-mmm/mmm_common.conf的单独文件中,系统中所有主机的该文件内容都是一样的, 配置完后不要忘记了拷贝这个文件到所有的主机(包括监控主机)!,内容如下:
[root@node3 ~]# cat /etc/mysql-mmm/mmm_common.conf
active_master_role writer
<host default>
cluster_interface eth0
pid_path /var/run/mysql-mmm/mmm_agentd.pid
bin_path /usr/libexec/mysql-mmm/
replication_user repluser #复制授权帐号
replication_password replpass #复制授权密码
agent_user mmm_agent #mmm-agent的帐号
agent_password agent_password #mmm-agent的密码
</host>
#定义数据库节点
<host db1>
ip 172.16.31.20
mode master
peer db2
</host>
<host db2>
ip 172.16.31.21
mode master
peer db1
</host>
<host db3>
ip 172.16.31.23
mode slave
</host>
#可写角色主机节点
<role writer>
hosts db1, db2
ips 172.16.31.100
#互斥角色只有一个ip,并且同一时间只能分配给一个主机,你可以指定一个优先(preferred)主机,如果这个主机是ONLINE状态,那么角色就会被切换到这个主机。
mode exclusive
</role>
#可读角色主机节点
<role reader>
hosts db1, db2, db3
ips 172.16.31.101,172.16.31.102,172.16.31.103
#负载均衡角色可以有多个IP,这些IP被均衡的分配给多个主机,所以没有一个主机可以比其他主机多出两个角色。
mode balanced
</role>
配置完成后复制到所有节点,包括监控节点node5;
在数据库主机上我们需要编辑/etc/mysql-mmm/mmm_agent.conf文件,根据其他主机的不同更改db1的值(db2就将db1更改成db2,db3就将db1改为db3):
[root@node3 ~]# vim /etc/mysql-mmm/mmm_agent.conf
include mmm_common.conf
# The 'this' variable refers to this server. Proper operation requires
# that 'this' server (db1 by default), as well as all other servers, have the
# proper IP addresses set in mmm_common.conf.
this db1
在监控主机上我们需要编辑/etc/mysql-mmm/mmm_mon.conf文件:
[root@node5 ~]# cat /etc/mysql-mmm/mmm_mon.conf
include mmm_common.conf
<monitor>
ip 127.0.0.1
pid_path /var/run/mysql-mmm/mmm_mond.pid
bin_path /usr/libexec/mysql-mmm
status_path /var/lib/mysql-mmm/mmm_mond.status
#监控的数据库服务器的IP
ping_ips 172.16.31.20,172.16.31.21,172.16.31.23
auto_set_online 60
# The kill_host_bin does not exist by default, though the monitor will
# throw a warning about it missing. See the section 5.10 "Kill Host
# Functionality" in the PDF documentation.
#
# kill_host_bin /usr/libexec/mysql-mmm/monitor/kill_host
#
</monitor>
<host default>
#数据库服务器授权监控的用户名和密码;
monitor_user repluser
monitor_password replpass
</host>
debug 0
6.配置完成后,启动监控服务,在监控主机上启动:
[root@node5 ~]# /etc/init.d/mysql-mmm-monitor start
Starting MMM Monitor Daemon: [ OK ]
查看进程启动情况:
[root@node5 ~]# ps aux |grep mmm
root 2325 0.0 1.3 161564 13608 ? S 19:29 0:00 mmm_mond
root 2326 3.3 6.6 698592 67780 ? Sl 19:29 0:00 mmm_mond
root 2339 1.7 0.9 150600 10044 ? S 19:29 0:00 perl /usr/libexec/mysql-mmm/monitor/checker ping_ip
root 2343 2.2 1.1 181676 11920 ? S 19:29 0:00 perl /usr/libexec/mysql-mmm/monitor/checker mysql
root 2346 1.8 0.9 150600 10044 ? S 19:29 0:00 perl /usr/libexec/mysql-mmm/monitor/checker ping
root 2349 2.5 1.1 181676 11960 ? S 19:29 0:00 perl /usr/libexec/mysql-mmm/monitor/checker rep_backlog
root 2352 3.0 1.1 181676 11976 ? S 19:29 0:00 perl /usr/libexec/mysql-mmm/monitor/checker rep_threads
root 2355 0.0 0.0 103256 832 pts/0 S+ 19:29 0:00 grep mmm
7.在数据库服务器节点上启动agent���务;node3,node4,node6节点都需要执行:
[root@node3 ~]# /etc/init.d/mysql-mmm-agent start
Starting MMM Agent Daemon: [ OK ]
[root@node4 ~]# /etc/init.d/mysql-mmm-agent start
Starting MMM Agent Daemon: [ OK ]
[root@node6 ~]# /etc/init.d/mysql-mmm-agent start
Starting MMM Agent Daemon: [ OK ]
在节点上查看进程及监听端口状态:
就拿node6举例了:
[root@node6 ~]# ps aux |grep mmm
root 12821 0.0 0.9 149664 10044 ? S 19:32 0:00 mmm_agentd
root 12823 0.1 1.0 149796 10256 ? S 19:32 0:00 mmm_agentd
root 12878 0.0 0.0 103252 828 pts/0 S+ 19:33 0:00 grep mmm
查看监听端口:
[root@node6 ~]# netstat -tunlp |grep mmm
tcp 0 0 172.16.31.23:9989 0.0.0.0:* LISTEN 12823/mmm_agentd
注意:如果不能启动,可以查看/var/log/mysql-mmm/mmm_agentd.log 文件的提示,或者启动时候的报错,最大的可能是perl对应模块没有安装成功,成功安装对应的模块就可以解决问题了.
8.在监控主机上查看集群节点状态:
[root@node5 ~]# mmm_control mode
ACTIVE
[root@node5 ~]# mmm_control show
db1(172.16.31.20) master/ONLINE. Roles: reader(172.16.31.101), writer(172.16.31.100)
db2(172.16.31.21) master/ONLINE. Roles: reader(172.16.31.102)
db3(172.16.31.23) slave/ONLINE. Roles: reader(172.16.31.103)
所有节点都处于ONLINE在线状态;
我们的MMM集群搭建就完毕了,我们进行一次健康检查:
[root@node5 ~]# mmm_control checks all
db2 ping [last change: 2015/01/25 19:29:41] OK
db2 mysql [last change: 2015/01/25 19:29:41] OK
db2 rep_threads [last change: 2015/01/25 19:29:41] OK
db2 rep_backlog [last change: 2015/01/25 19:29:41] OK: Backlog is null
db3 ping [last change: 2015/01/25 19:29:41] OK
db3 mysql [last change: 2015/01/25 19:29:41] OK
db3 rep_threads [last change: 2015/01/25 19:29:41] OK
db3 rep_backlog [last change: 2015/01/25 19:29:41] OK: Backlog is null
db1 ping [last change: 2015/01/25 19:29:41] OK
db1 mysql [last change: 2015/01/25 19:29:41] OK
db1 rep_threads [last change: 2015/01/25 19:29:41] OK
db1 rep_backlog [last change: 2015/01/25 19:29:41] OK: Backlog is null
监控节点的集群节点健康检查都是成功的,我们的MMM集群是成功的。
9.集群的故障切换测试:
测试看两个mysql服务器能否实现故障自动切换
停掉作为写的db1上的mysql,查看写的服务器会不会自动转移到db2上去
[root@node3 ~]# service mysqld stop
Shutting down MySQL.. [ OK ]
停掉几秒钟后用mmm_control show查看:
[root@node5 ~]# mmm_control show
db1(172.16.31.20) master/HARD_OFFLINE. Roles:
db2(172.16.31.21) master/ONLINE. Roles: reader(172.16.31.102), writer(172.16.31.100)
db3(172.16.31.23) slave/ONLINE. Roles: reader(172.16.31.101), reader(172.16.31.103)
我们可以看到已经把db2当作主写服务器;
再来看看db1恢复后会是什么情况:
[root@node3 ~]# service mysqld start
Starting MySQL. [ OK ]
[root@node5 ~]# mmm_control show
db1(172.16.31.20) master/ONLINE. Roles: reader(172.16.31.103)
db2(172.16.31.21) master/ONLINE. Roles: reader(172.16.31.102), writer(172.16.31.100)
db3(172.16.31.23) slave/ONLINE. Roles: reader(172.16.31.101)
我们可以看到当db1恢复后就充当slave的角色了!只有当db2挂了以后db1又会担当起主服务器的写入功能
10.测试写入数据是否同步
在mmm-monitor上连接172.16.31.21,尝试写入数据,然后查看三个服务器是否同步数据。
[root@node5 ~]# mysql -uroot -pOracle -h172.16.31.21
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 613
Server version: 5.5.5-10.0.12-MariaDB-log Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
#查看数据库;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| binlog |
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| relaylog |
| test |
+--------------------+
7 rows in set (0.00 sec)
#使用hellodb数据库;
mysql> use hellodb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
#查看teachers表;
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | Huang Yaoshi | 56 | M |
| 7 | Feng Qingyang | 100 | M |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)
#插入一些数据;
mysql> insert into teachers(name,age,gender) values('Wang Chongyang',95,'m');
Query OK, 1 row affected (0.04 sec)
#查看数据修改情况;
mysql> select * from teachers;
+-----+----------------+-----+--------+
| TID | Name | Age | Gender |
+-----+----------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | Huang Yaoshi | 56 | M |
| 7 | Feng Qingyang | 100 | M |
| 8 | Wang Chongyang | 95 | M |
+-----+----------------+-----+--------+
7 rows in set (0.00 sec)
#退出数据库;
mysql> \q
Bye
我们在节点node3上查看数据是否同步:
[root@node3 ~]# mysql -uroot -poracle
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 229
Server version: 10.0.12-MariaDB-log Source distribution
Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use hellodb;
Database changed
MariaDB [hellodb]> select * from teachers;
+-----+----------------+-----+--------+
| TID | Name | Age | Gender |
+-----+----------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | Huang Yaoshi | 56 | M |
| 7 | Feng Qingyang | 100 | M |
| 8 | Wang Chongyang | 95 | M |
+-----+----------------+-----+--------+
7 rows in set (0.00 sec)
MariaDB [hellodb]> \q
Bye
在从服务器节点node6查看数据是否同步:
[root@node6 ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 645
Server version: 10.0.12-MariaDB-log Source distribution
Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use hellodb;
Database changed
MariaDB [hellodb]> select * from teachers;
+-----+----------------+-----+--------+
| TID | Name | Age | Gender |
+-----+----------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | Huang Yaoshi | 56 | M |
| 7 | Feng Qingyang | 100 | M |
| 8 | Wang Chongyang | 95 | M |
+-----+----------------+-----+--------+
7 rows in set (0.00 sec)
MariaDB [hellodb]> \q
Bye
数据全部同步了,我们的MMM集群是完美成功了的。
至此,基于Master-Master replication manager for Mysql的高可用双主复制集群搭建完毕。