DBA成长之路---MySQL-MMM集群部署
MySQL-MMM集群部署MMM(Master-Master replication managerfor Mysql，Mysql主主复制管理器)是一套灵活的脚本程序，基于perl实现，用来对mysql replication进行监控和故障迁移，并能管理mysql Master-Master复制的配置(同一时间只有一个节点是可写的)。
mmm_mond：监控进程，负责所有的监控工作，决定和处理所有节点角色活动。此脚本需要在监管机上运行。
mmm_agentd：运行在每个mysql服务器上的代理进程，完成监控的探针工作和执行简单的远端服务设置。此脚本需要在被监管机上运行。
mmm_control：一个简单的脚本，提供管理mmm_mond进程的命令。
实验拓扑图
实验环境准备
五台虚拟机器 IP 主机名分别为：
192.168.4.10主机名：mysql10
192.168.4.11主机名：mysql11
192.168.4.12主机名：mysql12
192.168.4.13主机名：mysql13
192.168.4.120主机名：client120
每台虚拟机关闭防火墙和SELinux的限制 以方便实验
10-13 安装mysql数据库服务
一，配置主从同步结构
1.1 配置主主结构 10 / 11
共同配置
虚拟机10上：用户授权启动binlog日志重启数据库服务 管理员登录指定主库信息
mysql> grant replication slave on *.* to slaveuser@"%"> # vim /etc/my.cnf
server_id=10
log-bin=master10
binlog_format="mixed"
# systemctl restart mysqld
mysql> show master status;
| master10.000001 | 154 |
mysql> change master to master_host="192.168.4.11", master_user="slaveuser",master_password="123456",master_log_file="master11.000001",master_log_pos=154;
mysql> start slave;
mysql> show slave status\G;
Relay_Master_Log_File: master13.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
虚拟机11上：用户授权启用binlog日志 并允许级联复制重启数据库服务管理员登录指定主库信息
mysql> grant replication slave on *.* to slaveuser@"%"> # vim /etc/my.cnf
server_id=11
log-bin=master11
binlog_format="mixed"
log_slave_updates
# systemctl stop mysqld
# systemctl startmysqld
mysql> show master status;
| master11.000001 | 154 |
mysql> change master to master_host="192.168.4.10", master_user="slaveuser",master_password="123456",master_log_file="master10.000001",master_log_pos=154;
mysql> start slave;
mysql> show slave status\G;
Relay_Master_Log_File: master10.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
配置一主多从结构（把12，13分别配置11的从库）
虚拟机12上
# vim /etc/my.cnf
server_id=12
# systemctl stop mysqld
# systemctl startmysqld
mysql> change master to master_host="192.168.4.11", master_user="slaveuser",master_password="123456",master_log_file="master11.000001",master_log_pos=154;
mysql> start slave;
mysql> show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
虚拟机13上
#vim /etc/my.cnf
server_id=13
#systemctl stop mysqld
#systemctl startmysqld
mysql> change master to master_host="192.168.4.11", master_user="slaveuser",master_password="123456",master_log_file="master11.000001",master_log_pos=154;
mysql> start slave;
mysql> show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
虚拟机10上：
在10主机上添加访问用户guser 能够在其他3台主机上也有相同的授权用户
mysql> create database gamedb;
mysql> grant all on gamedb.* to guser@"%"> 在客户端245 使用授权用户guser 连接10服务器 产生的新数据放在其他3台主机上也有
# mysql -h192.168.4.10 -uguser -p123456
MySQL [(none)]> create table gamedb.a(id int);
MySQL [(none)]> insert into gamedb.a values(100);
MySQL [(none)]> insert into gamedb.a values(100);
MySQL [(none)]> insert into gamedb.a values(100);
二，配置mysql-mmm
mysql-mmm介绍：
监控服务： 运行在管理节点上 用来监控数据节点
代理服务： 运行在数据节点 用来提供系统给监控主机
1)在所有主机上安装mysql-mmm软件 （10-13,120）
yum -y install perl-*
tar -zxvf mysql-mmm.zip
unzip mysql-mmm.zip
cd mysql-mmm/
tar -zxvf mysql-mmm-2.2.1.tar.gz
cd mysql-mmm-2.2.1/
make install
ls /etc/mysql-mmm/
mmm_agent.confmmm_common.confmmm_mon.confmmm_tools.conf
2)修改配置文件
a.修改数据节点代理服务配置文件（10 11 12 13）
# vim /etc/mysql-mmm/mmm_agent.conf
include mmm_common.conf
this db10#自定义名称
# vim /etc/mysql-mmm/mmm_agent.conf
include mmm_common.conf
this db11
# vim /etc/mysql-mmm/mmm_agent.conf
include mmm_common.conf
this db12
# vim /etc/mysql-mmm/mmm_agent.conf
include mmm_common.conf
this db13
b.修改管理节点监控服务的配置文件（120）
# vim /etc/mysql-mmm/mmm_mon.conf
include mmm_common.conf
ip 192.168.4.120
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.4.10, 192.168.4.11, 192.168.4.12, 192.168.4.13
monitor_user monitor#监视用户名
monitor_password 123456#监视用户密码
debug 00不显示调试信息1显示调试信息
c.修改公共文件(10,11,12,13,120)
vim /etc/mysql-mmm/mmm_common.conf
10 replication_user slaveuser#代理用户
11 replication_password 123456
12
13 agent_user agent#数据库
14 agent_password123456
17 #修改四台服务器
18 ip 192.168.4.10
19 mode master
20 peer db11
21
22
23
24 ip 192.168.4.11
25 mode master
26 peer db10
27
28
29
30 ip 192.168.4.12
31 mode slave
32
33
34
35 ip 192.168.4.13
36 mode slave
37
39
40 hosts db10, db11
41 ips 192.168.4.100
42 mode exclusive
43
44
45
46 hosts db12, db13
47 ips 192.168.4.102, 192.168.4.105
48 mode balanced
49
d.根据配置文件的设置，在数据节点上添加对应的授权用户
monitor
agent
3)在虚拟机10上 授权
mysql> grant replication client on *.* to monitor@"%">
mysql> grant replication client,process,super on *.* to agent@"%"> 4)其他三台测试
mysql> select user,host from mysql.user where user in ('agent','monitor');
三,启动服务
a.启动数据节点主机代理服务(10-13)：安装服务运行依赖软件包 安装获取vip地址软件包 启动服务
cd /root/mysql-mmm/
tar -zxf Algorithm-Diff-1.1902.tar.gz
cd Algorithm-Diff-1.1902/
perl Makefile.PL
make
make install
cd /root/mysql-mmm/
tar -zxf Proc-Daemon-0.03.tar.gz
cd Proc-Daemon-0.03/
perl Makefile.PL
make
make install
cd /root/mysql-mmm/
rpm -ivh --nodeps perl-Log-Log4perl-1.26-1.el6.rf.noarch.rpm
/etc/init.d/mysql-mmm-agent start
/etc/init.d/mysql-mmm-agent status
netstat -pantu | grep mmm
netstat -pantu | grep :9989
tcp 0 0 192.168.4.11:9989 0.0.0.0:* LISTEN 10059/mmm_agentd
yum -y install gcc gcc-c++
cd /root/mysql-mmm/
gunzip Net-ARP-1.0.8.tgz
tar -xf Net-ARP-1.0.8.tar
cd Net-ARP-1.0.8/
perl Makefile.PL
make
make install
b.启动管理节点主机监控服务 (120)：安装服务运行软件包 启动服务
cd /root/mysql-mmm/
tar -zxf Algorithm-Diff-1.1902.tar.gz
cd Algorithm-Diff-1.1902/
perl Makefile.PL
make
make install
cd /root/mysql-mmm/
tar -zxf Proc-Daemon-0.03.tar.gz
cd Proc-Daemon-0.03/
perl Makefile.PL
make
make install
cd /root/mysql-mmm/
rpm -ivh --nodeps perl-Log-Log4perl-1.26-1.el6.rf.noarch.rpm
/etc/init.d/mysql-mmm-monitor start
/etc/init.d/mysql-mmm-monitor status
netstat -pantu | grep mmm_mond
netstat -pantu | grep 9988
tcp 0 0 192.168.4.120:9988 0.0.0.0:* LISTEN 30047/mmm_mond
四,验证mysql-mmm的配置
a 查看数据库节点上的数据库服务是运行的
IO线程和SQ线程 是否OK
# mysql -uroot -p123456 -e"show slave status\G;" | grep -i yes
mysql: Using a password on the command line interface can be insecure.
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
b.在监控服务器本机登录管理界面查看，查看数据库服务状态
# mmm_control show
defined(@array) is deprecated at /usr/share/perl5/vendor_perl/Log/Log4perl/Config.pm line 863.
(Maybe you should just omit the defined()?)
db10(192.168.4.10) master/AWAITING_RECOVERY. Roles:
db11(192.168.4.11) master/AWAITING_RECOVERY. Roles:
db12(192.168.4.12) slave/AWAITING_RECOVERY. Roles:
db13(192.168.4.13) slave/AWAITING_RECOVERY. Roles:
# mmm_control set_online db10
# mmm_control set_online db11
# mmm_control set_online db12
# mmm_control set_online db13
# mmm_control show
defined(@array) is deprecated at /usr/share/perl5/vendor_perl/Log/Log4perl/Config.pm line 863.
(Maybe you should just omit the defined()?)
db10(192.168.4.10) master/ONLINE. Roles: writer(192.168.4.100)
db11(192.168.4.11) master/ONLINE. Roles:
db12(192.168.4.12) slave/ONLINE. Roles: reader(192.168.4.105)
db13(192.168.4.13) slave/ONLINE. Roles: reader(192.168.4.102)
c.在数据接待年本机查看是否获取到vip地址
# ping -c 2 192.168.4.100
PING 192.168.4.100 (192.168.4.100) 56(84) bytes of data.
64 bytes from 192.168.4.100: icmp_seq=1 ttl=64 time=0.367 ms
64 bytes from 192.168.4.100: icmp_seq=2 ttl=64 time=0.383 ms
# ip addr show | grep 192.168.4.
inet 192.168.4.10/24 brd 192.168.4.255 scope global eth0
inet 192.168.4.100/32 scope global eth0
# ip addr show | grep 192.168.4.
inet 192.168.4.12/24 brd 192.168.4.255 scope global eth0
inet 192.168.4.105/32 scope global eth0
#ip addr show | grep 192.168.4.
inet 192.168.4.13/24 brd 192.168.4.255 scope global eth0
inet 192.168.4.102/32 scope global eth0
d.客户端连接VIP访问数据库服务
# mysql -h192.168.4.100 -uguser -p123456
MySQL [(none)]> select @@hostname;
+---------------------+
| @@hostname |
+---------------------+
| mysql10 |
+---------------------+
模拟 虚拟机10挂掉
# systemctl stop mysqld
# mmm_control show
defined(@array) is deprecated at /usr/share/perl5/vendor_perl/Log/Log4perl/Config.pm line 863.
(Maybe you should just omit the defined()?)
db10(192.168.4.10) master/HARD_OFFLINE. Roles:
db11(192.168.4.11) master/ONLINE. Roles: writer(192.168.4.100)
db12(192.168.4.12) slave/ONLINE. Roles: reader(192.168.4.105)
db13(192.168.4.13) slave/ONLINE. Roles: reader(192.168.4.102)
# ip addr show | grep 192.168.4
inet 192.168.4.11/24 brd 192.168.4.255 scope global eth0
inet 192.168.4.100/32 scope global eth0
MySQL [(none)]> select @@hostname;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection> Current database: *** NONE ***
+---------------------+
| @@hostname |
+---------------------+
| mysql11 |
+---------------------+
1 row in set (0.00 sec)
模拟 虚拟机10 故障修好了
# systemctl start mysqld
# mmm_control show
defined(@array) is deprecated at /usr/share/perl5/vendor_perl/Log/Log4perl/Config.pm line 863.
(Maybe you should just omit the defined()?)
db10(192.168.4.10) master/AWAITING_RECOVERY. Roles
db11(192.168.4.11) master/ONLINE. Roles: writer(192.168.4.100):
db12(192.168.4.12) slave/ONLINE. Roles: reader(192.168.4.105)
db13(192.168.4.13) slave/ONLINE. Roles: reader(192.168.4.102)
# mmm_control set_online db10
# mmm_control show
defined(@array) is deprecated at /usr/share/perl5/vendor_perl/Log/Log4perl/Config.pm line 863.
(Maybe you should just omit the defined()?)
db10(192.168.4.10) master/ONLINE. Roles:
db11(192.168.4.11) master/ONLINE. Roles: writer(192.168.4.100)
db12(192.168.4.12) slave/ONLINE. Roles: reader(192.168.4.105)
db13(192.168.4.13) slave/ONLINE. Roles: reader(192.168.4.102)
10不会立即占用VIP地址 当11 出现故障时 10 会重新获得VIP地址
页:
[1]