mysql双主+keepalived架构

蔚落 2023-06-16 08:26 6阅读 0赞

架构展示

![image2019-11-21_2017_3A22_3A0.png_version_1_modificationDate_1574331312000_api_v2][]

操作系统 centos6.5

数据库 mysql5.7

master1 10.0.254.148

master2 10.0.254.147

VIP 10.0.254.88 （keepalived）  最好在自己的内网VIP

一、创建操作系统 centos6.5   关闭selinux  关闭防火墙 iptables

二、每台机器都安装数据库 编译安装 不作介绍需要配置/etc/my.cnf 文件

master1的my.cnf 文件配置

![image2019-11-21_2017_3A32_3A45.png_version_1_modificationDate_1574331312000_api_v2][]

启动mysql   /etc/init.d/mysqld start

master2的my.cnf文件配置

![image2019-11-21_2017_3A33_3A59.png_version_1_modificationDate_1574331312000_api_v2][]

启动mysql /etc/init.d/mysqld start

完成以上操作之后，需要对两台机器做权限分配以及主主配置 需要同时进行配置两台机器

grant replication slave,replication client on \*.\* to guoshang@'%' identified by '1qaz!QAZ1qaz';

flush privileges;

flush tables with read lock; 锁表

show master status; 查看master情况

分别开启同步对方

---------------master1服务器做同步操作---------------

mysql> unlock tables; 
    mysql> stop salve；

mysql> change  master to master_host='master2IP',master_user='root/guoshang',master_password='密码',master_log_file='mysql-bin.000001',master_log_pos=150;

Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

查看两个线程状态是否为YES

mysql> show slave status \G;

Slave\_IO\_Running: Yes  
Slave\_SQL\_Running: Yes

-------------master2服务器做同步操作---------------

mysql> unlock tables;  
    mysql> slave stop；

mysql> change  master to master_host='master1IP',master_user='root/guoshang',master_password='密码',master_log_file='mysql-bin.000006',master_log_pos=996;

Query OK, 0 rows affected, 2 warnings (0.06 sec)

mysql> start slave; Query OK, 0 rows affected (0.01 sec)

mysql> show slave status \G;

Master\_Log\_File: mysql-bin.000006  
Read\_Master\_Log\_Pos: 996  
Relay\_Log\_File: master2-relay-bin.000002  
Relay\_Log\_Pos: 312  
Relay\_Master\_Log\_File: mysql-bin.000006  
Slave\_IO\_Running: Yes  
Slave\_SQL\_Running: Yes

`以上表明双方已经实现了mysql主主同步。`

测试创建库 创建表 插入数据  测试即可

配置keepalived

1）安装keepalived并将其配置成系统服务。master1和master2两台机器上同样进行如下操作：  
\[root@master1 ~\]\# yum install -y openssl-devel  
\[root@master1 ~\]\# cd /usr/local/src/  
\[root@master1 src\]\# wget [http://www.keepalived.org/software/keepalived-1.3.5.tar.gz][http_www.keepalived.org_software_keepalived-1.3.5.tar.gz]  
\[root@master1 src\]\# tar -zvxf keepalived-1.3.5.tar.gz  
\[root@master1 src\]\# cd keepalived-1.3.5  
\[root@master1 keepalived-1.3.5\]\# ./configure --prefix=/usr/local/keepalived  
\[root@master1 keepalived-1.3.5\]\# make && make install  
\[root@master1 keepalived-1.3.5\]\# cp /usr/local/src/keepalived-1.3.5/keepalived/etc/init.d/keepalived /etc/rc.d/init.d/  
\[root@master1 keepalived-1.3.5\]\# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/  
\[root@master1 keepalived-1.3.5\]\# mkdir /etc/keepalived/  
\[root@master1 keepalived-1.3.5\]\# cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/  
\[root@master1 keepalived-1.3.5\]\# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/  
\[root@master1 keepalived-1.3.5\]\# echo "/etc/init.d/keepalived start" >> /etc/rc.local

master1和master2操作如下

vim /etc/keepalived/keepalived.conf master1

![image2019-11-21_2017_3A52_3A38.png_version_1_modificationDate_1574331312000_api_v2][]

vim /etc/keepalived/keepalived.conf    master2

![image2019-11-21_2017_3A53_3A45.png_version_1_modificationDate_1574331312000_api_v2][]

配置完成

查看 ip addr

重启mysqld   keepalived   iptables

编写切换脚本。KeepAlived做心跳检测，如果Master的MySQL服务挂了(3306端口挂了),那么它就会选择自杀。Slave的KeepAlived通过心跳检测发现这个情况，就会将VIP的请求接管  
\[root@master1 ~\]\# vim /opt/chk\_mysql.sh  
\#!/bin/bash  
counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)  
if \[ "$\{counter\}" -eq 0 \]; then  
/etc/init.d/keepalived stop  
fi  
\[root@master1 ~\]\# chmod 755 /opt/chk\_mysql.sh  
启动keepalived服务  
\[root@master1 ~\]\# /etc/init.d/keepalived start  
正在启动 keepalived： \[确定\]

`master1和master2两台服务器都要授权允许root用户远程登录，用于在客户端登陆测试！`

mysql> grant all on *.* to root@'%' identified by "密码";

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)
    
    
    keepalived报错提示

环境  
VMware虚拟机1： 192.168.1.39

VMware虚拟机2： 192.168.1.40

想配置的VIP地址：192.168.111.88

现象  
keepalived.conf中vip配置好后，通过ip addr可以看到vip已经顺利挂载，vip也能漂移。能在master上ping通，但是无法在其他机器上ping通(比如master在 192.168.1.39上，39是能ping通111.88，但是我在 192.168.1.40上就ping不通)，并且防火墙都已关闭，SELinux已关闭。

排查  
journalctl -ex 查看两台机器日志，没发现任何问题  
开启了百度谷歌之旅  
搜索来的解决方案  
原因是keepalived.conf配置中默认vrrp\_strict打开了，需要把它注释掉。重启keepalived即可ping通

结果发现我的配置文件中根本没这个vrrp\_strict,肯定不是这个原因。排除  
后来看到这篇文章  
\- 可能是对lvs手册不太熟悉，我只试了添加virtual\_server和real\_server

virtual\_server 192.168.111.88 8080 \{  
delay\_loop 6  
lb\_algo rr  
lb\_kind NAT  
protocol TCP  
real\_server 192.168.1.39 8080  
\{  
weight 1  
\}  
\}  
![1162158-20191122092222779-670626601.png][]

两台机器的配置都加了，还是原来的问题。再仔细看了下原文"NAT模式和路由器NAT模式类似，用于访问client和real\_server在不同网段实现通信。如果你在一个局域网内做负载均衡选用NAT，那恭喜你，你肯定是无法访问",我就把vip改成10.0.0.164(回头写笔记的时候，这里很惭愧啊。没懂这里，懂的话，其实已经能解决了)

virtual\_server 10.0.0.164 8080 \{  
delay\_loop 6  
lb\_algo rr  
lb\_kind NAT  
protocol TCP  
real\_server 192.168.1.39 8080  
\{  
weight 1  
\}  
\}  
![1162158-20191122092321862-2032496194.png][]

发现还是原来的问题。不行。

后来看到说arp绑定问题,需要清楚arp绑定  
这个不懂，我就查看了下怎么看arp绑定

arp -n  
发现两台机器一样，以为没有问题，就跳过了。(回头看下应该是有问题的，最后解决了再说)

再看了一篇说是云服务器的vip需要申请，跟服务商申请。反正就是云服务器跟虚拟机不一样，这里我就没继续研究了

抓包看问题

tcpdump -i ens33 arp -v

tcpdump -i ens33 vrrp -n

我也没看出啥。

最后看到下面这个，才知道问题所在

因为交换机上没配相关路由吧，跨网段的时候是会存在路由问题的，如果你把VIP也设置为192.168.2.\*应该就没这个问题了。  
因为其他机器放问192.168.100.100的时候会默认去192.168.100.\*的网段去寻找主机，所以就找不到具体的物理地址了。  
还有个办法就是在2.x的主机上都配置静态路由，add route ， 把192.168.100.100的路由配置到192.168.2.254（貌似这个是网关？）  
建议还是换成2.x的地址更合理。  
我们一般做地址规划的时候，200以内都是物理ip，200以上都给VIP预留，就是为了避免这种问题

所有我改了vip地址

VMware虚拟机1： 192.168.1.39

VMware虚拟机2： 192.168.1.40

想配置的VIP地址：192.168.111.88

改成

VMware虚拟机1： 192.168.1.39

VMware虚拟机2： 192.168.1.40

想配置的VIP地址：192.168.1.222

重启keepalived。发现正常了(\#.\#)

再回头看下arp  
master看arp

\[root@vmware39 keepalived\]\# arp -n  
Address HWtype HWaddress Flags Mask Iface  
192.168.1.40 ether 00:0c:29:e6:6b:d5 C ens33  
192.168.1.1 ether 14:30:04:a3:fe:d5 C ens33  
192.168.1.33 ether 00:e0:4c:36:05:bf C ens33  
![1162158-20191122092432139-282091842.png][]

slave看arp

\[root@vmware40 keepalived\]\# arp -n  
Address HWtype HWaddress Flags Mask Iface  
192.168.1.1 ether 14:30:04:a3:fe:d5 C ens33  
192.168.1.39 ether 00:0c:29:5d:57:e5 C ens33  
192.168.1.222 ether 00:0c:29:5d:57:e5 C ens33  
192.168.1.33 ether 00:e0:4c:36:05:bf C ens33

![1162158-20191122092501464-1246591440.png][]

参考链接

[https://blog.csdn.net/wade1010/article/details/88863780][https_blog.csdn.net_wade1010_article_details_88863780]

[https://www.cnblogs.com/benjamin77/p/8682360.html][https_www.cnblogs.com_benjamin77_p_8682360.html]

[image2019-11-21_2017_3A22_3A0.png_version_1_modificationDate_1574331312000_api_v2]: http://10.0.254.74:8090/download/attachments/851978/image2019-11-21%2017%3A22%3A0.png?version=1&modificationDate=1574331312000&api=v2
[image2019-11-21_2017_3A32_3A45.png_version_1_modificationDate_1574331312000_api_v2]: http://10.0.254.74:8090/download/attachments/851978/image2019-11-21%2017%3A32%3A45.png?version=1&modificationDate=1574331312000&api=v2
[image2019-11-21_2017_3A33_3A59.png_version_1_modificationDate_1574331312000_api_v2]: http://10.0.254.74:8090/download/attachments/851978/image2019-11-21%2017%3A33%3A59.png?version=1&modificationDate=1574331312000&api=v2
[http_www.keepalived.org_software_keepalived-1.3.5.tar.gz]: http://www.keepalived.org/software/keepalived-1.3.5.tar.gz
[image2019-11-21_2017_3A52_3A38.png_version_1_modificationDate_1574331312000_api_v2]: http://10.0.254.74:8090/download/attachments/851978/image2019-11-21%2017%3A52%3A38.png?version=1&modificationDate=1574331312000&api=v2
[image2019-11-21_2017_3A53_3A45.png_version_1_modificationDate_1574331312000_api_v2]: http://10.0.254.74:8090/download/attachments/851978/image2019-11-21%2017%3A53%3A45.png?version=1&modificationDate=1574331312000&api=v2
[1162158-20191122092222779-670626601.png]: https://img2018.cnblogs.com/blog/1162158/201911/1162158-20191122092222779-670626601.png
[1162158-20191122092321862-2032496194.png]: https://img2018.cnblogs.com/blog/1162158/201911/1162158-20191122092321862-2032496194.png
[1162158-20191122092432139-282091842.png]: https://img2018.cnblogs.com/blog/1162158/201911/1162158-20191122092432139-282091842.png
[1162158-20191122092501464-1246591440.png]: https://img2018.cnblogs.com/blog/1162158/201911/1162158-20191122092501464-1246591440.png
[https_blog.csdn.net_wade1010_article_details_88863780]: https://blog.csdn.net/wade1010/article/details/88863780
[https_www.cnblogs.com_benjamin77_p_8682360.html]: https://www.cnblogs.com/benjamin77/p/8682360.html