mysql 搭建keeeplived_Mysql之配置双主热备+keeepalived.md

朴灿烈づ我的快乐病毒、 2022-10-24 13:21 111阅读 0赞

准备

1 1. 双主 master1 192.168.199.49

2 master2 192.168.199.50

3 VIP 192.168.199.52 //虚拟IP

4 2.环境 master:nginx + php + mysql + keepalived

5 VIP:只要和master在一个局域网内即可。

6 3. 服务器之间网络通畅，可以互相ping通。

7 4. 2个服务器的mysql版本要一致。数据库密码一致

8 5. 防火墙增加允许组播和允许VRRP(虚拟路由器冗余协)通信，这样主服务器在故障恢复后才能抢回资源

9 -A INPUT -s 182.148.15.0/24 -d 224.0.0.18 -j ACCEPT

10 -A INPUT -s 182.148.15.0/24 -p vrrp -j ACCEPT

11 重启生效：service iptables reload

![48304ba5e6f9fe08f3fa1abda7d326ab.png][]

配置服务器

![48304ba5e6f9fe08f3fa1abda7d326ab.png][]

1 服务器1：

3 在my.cnf文件的\[mysqld\]配置区域添加下面内容：

4 log-bin=mysql-bin \#记录二进制文件

5 binlog\_format=mixed \#mysql默认采用的二进制格式

6 server-id = 2 \#服务号，必须是唯一的，一般取IP的后8位

7 expire\_logs\_days = 10 \#binlog过期清理时间

9 binlog-do-db = db1 \#需要服务的数据库。

10 \#replicate-ignore-db = db3,mysql,test,information\_schema,performance\_schema,transfer\_new \#不需要复制的数据库

11 \#replicate-wild-ignore-table=garbage.% \#有问题可以尝试使用此项配置

12 sync\_binlog = 1 \#表示binlog日志在每1次写入后与硬盘同步，设置1最安全也最慢

13 auto-increment-increment = 2 \#服务器个数

14 auto-increment-offset = 2 \#自增偏移量，如果有2台，则一台设置1，另一台设置2

15 slave-skip-errors = all \#从库复制时跳过所有的错误

16 服务器2：

18 log-bin=mysql-bin

19 binlog\_format=mixed

20 server-id = 1

21 expire\_logs\_days = 10

22 binlog-do-db = db1

23 \#replicate-ignore-db = db3,mysql,test,information\_schema,performance\_schema,transfer\_new

24 \#replicate-wild-ignore-table=garbage.% \#有问题可以尝试使用此项配置

25 sync\_binlog = 1

26 auto-increment-increment = 2

27 auto-increment-offset = 1

28 slave-skip-errors = all

![48304ba5e6f9fe08f3fa1abda7d326ab.png][]

2台服务器都重启mysql服务：

1 service mysql restart

同步配置(2台服务器分别执行)

![48304ba5e6f9fe08f3fa1abda7d326ab.png][]

1 给对方授权复制权限

3 //对方服务器60.205.182.26允许复制本服务器数据库，用户名repl 密码ocnt-123

4 mysql> grant replication slave,replication client on \*.\* to repl@'60.205.182.26' identified by "ocnt-123";

5 //生效

6 mysql> flush privileges;

7 //锁定表

8 mysql> flush tables with read lock;

![48304ba5e6f9fe08f3fa1abda7d326ab.png][]

同步数据

1 1.锁定表

2 mysql> flush tables with read lock;

3 2.同步数据，可使用navicat

同步操作(2台服务器分别执行)

![48304ba5e6f9fe08f3fa1abda7d326ab.png][]

1 //查看各自服务器当前二进制日志文件名称和记录位置

2 mysql> show master status;

5 //解锁表

6 mysql> unlock tables;

7 //停止slave

8 mysql> slave stop;

9 //开始同步: IP、用户名和密码为服务器2刚才grant授权配置的,日志文件和pos值得位置通过服务器2 show master status查看得来

10 mysql> change master to master\_host='101.200.63.35',master\_user='repl',master\_password='123456',master\_log\_file='mysql-bin.000016',master\_log\_pos=363;

11 //开启slave

12 mysql> start slave;

15 //解锁表

16 mysql> unlock tables;

17 //停止slave

18 mysql> slave stop；

20 change master to master\_host='60.205.182.26',master\_user='repl',master\_password='123456',master\_log\_file='mysql-bin.000028',master\_log\_pos=107;

22 mysql> start slave;

![48304ba5e6f9fe08f3fa1abda7d326ab.png][]

查看同步状态，如下出现两个“Yes”，表明同步成功！(Slave\_IO\_Running和Slave\_SQL\_Running)

1 mysql> show slave status \\G;

配置到此处就完成了双主热备。

keepalived故障自动切换配置

![48304ba5e6f9fe08f3fa1abda7d326ab.png][]

1 master1:192.168.199.49 主机1

2 master2:192.168.199.50 主机2 备份

3 VIP:192.168.199.52 虚拟IP

4 \#\#\# 2台服务器安装keepalived

6 \[root@localhost \]\#yum install -y openssl-devel

7 \[root@localhost src\]\#cd /usr/local/src/

8 \[root@localhost src\]\# wget http://www.keepalived.org/software/keepalived-1.3.5.tar.gz

9 \[root@localhost src\]\# tar -zvxf keepalived-1.3.5.tar.gz

10 \[root@localhost src\]\# cd keepalived-1.3.5

11 \[root@localhost keepalived-1.3.5\]\# ./configure --prefix=/usr/local/keepalived

12 \[root@localhost keepalived-1.3.5\]\# make && make install

14 \[root@localhost keepalived-1.3.5\]\# cp /usr/local/src/keepalived-1.3.5/keepalived/etc/init.d/keepalived /etc/rc.d/init.d/

15 \[root@localhost keepalived-1.3.5\]\# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/

16 \[root@localhost keepalived-1.3.5\]\# mkdir /etc/keepalived/

17 \[root@localhost keepalived-1.3.5\]\# cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/

18 \[root@localhost keepalived-1.3.5\]\# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/

19 \[root@localhost keepalived-1.3.5\]\# echo "/etc/init.d/keepalived start" >> /etc/rc.local

![48304ba5e6f9fe08f3fa1abda7d326ab.png][]

master1服务器配置

![48304ba5e6f9fe08f3fa1abda7d326ab.png][]

1 \[root@master1 ~\]\# cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.bak

2 \[root@master1 ~\]\# vim /etc/keepalived/keepalived.conf ＃删除所有内容，重新配置如下

4 ! Configuration File for keepalived

5 global\_defs \{

6 notification\_email \{

7 412140451@qq.com

8 \}

10 notification\_email\_from 412140451@qq.com

11 smtp\_server 127.0.0.1

12 smtp\_connect\_timeout 30

13 router\_id MASTER-HA

14 \}

16 vrrp\_script chk\_mysql\_port \{ \#检测mysql服务是否在运行

17 script "/opt/chk\_mysql.sh" \#这里通过脚本监测

18 interval 3 \#脚本执行间隔，每3s检测一次

19 weight -5 \#脚本结果导致的优先级变更，检测失败(脚本返回非0)则优先级 -5

20 fall 2 \#检测连续2次失败才算确定是真失败。会用weight减少优先级(1-255之间)

21 rise 1 \#检测1次成功就算成功。但不修改优先级

22 \}

24 vrrp\_instance VI\_1 \{

25 state MASTER

26 interface eth0 \#指定虚拟ip的网卡接口

27 mcast\_src\_ip 192.168.199.49 \#本机master的 IP地址

28 virtual\_router\_id 100 \#路由器标识，MASTER和BACKUP必须是一致的

29 priority 100 \#定义优先级，数字越大，优先级越高，在同一个vrrp\_instance下，MASTER的优先级必须大于BACKUP的优先级。这样MASTER故障恢复后，就可以将VIP资源再次抢回来

30 advert\_int 1

31 authentication \{ \#加密协议

32 auth\_type PASS

33 auth\_pass 1111

34 \}

35 virtual\_ipaddress \{

36 192.168.199.52 \#虚拟IP地址 VIP

37 \}

39 track\_script \{

40 chk\_mysql\_port

41 \}

42 \}

![48304ba5e6f9fe08f3fa1abda7d326ab.png][]

master2服务器配置 修改 mcast\_src\_ip priority

![48304ba5e6f9fe08f3fa1abda7d326ab.png][]

1 global\_defs \{

2 notification\_email \{

3 412140451@qq.com

4 \}

6 notification\_email\_from 412140451@qq.com

7 smtp\_server 127.0.0.1

8 smtp\_connect\_timeout 30

9 router\_id MASTER-HA

10 \}

12 vrrp\_script chk\_mysql\_port \{

13 script "/opt/chk\_mysql.sh"

14 interval 3

15 weight -5

16 fall 2

17 rise 1

18 \}

20 vrrp\_instance VI\_1 \{

21 state BACKUP

22 interface eth0

23 mcast\_src\_ip 192.168.199.50

24 virtual\_router\_id 100

25 priority 90

26 advert\_int 1

27 authentication \{

28 auth\_type PASS

29 auth\_pass 1111

30 \}

31 virtual\_ipaddress \{

32 192.168.199.52

33 \}

35 track\_script \{

36 chk\_mysql\_port

37 \}

38 \}

![48304ba5e6f9fe08f3fa1abda7d326ab.png][]

2个服务器都编写检测切换mysql脚本

![48304ba5e6f9fe08f3fa1abda7d326ab.png][]

1 KeepAlived做心跳检测，如果Master的MySQL服务挂了(3306端口挂了),那么它就会选择自杀。Slave的KeepAlived通过心跳检测发现这个情况，就会将VIP的请求接管

2 vim /opt/chk\_mysql.sh

4 \#!/bin/bash

5 CHECK\_TIME=3

7 \#Mysql

8 host='localhost'

9 port='3306'

10 user='root'

11 password='ocnt-123'

12 mysqlclient="mysql"

14 \#mysql is working MYSQL\_OK is 1 , mysql down MYSQL\_OK is 0

15 function check\_mysql\_helth ()\{

16 \#NEW=$MYSQL -h $MYSQL\_HOST -u $MYSQL\_USER -p$\{MYSQL\_PASSWORD\} -e "show status;" >/dev/null 2>&1

17 $mysqlclient --host=$host --port=$port --user=$user --password=$password -e "show databases;" > /dev/null 2>&1

19 if \[ $? == 0 \]

20 then

21 MYSQL\_OK=1

22 else

23 MYSQL\_OK=0

24 fi

25 \# echo $MYSQL\_OK

26 return $MYSQL\_OK

27 \}

29 while \[ $CHECK\_TIME -ne 0 \]

30 do

31 let "CHECK\_TIME -= 1"

32 check\_mysql\_helth

33 if \[ $MYSQL\_OK == 1 \] ; then

34 CHECK\_TIME=0

35 exit 0

36 fi

37 if \[ $MYSQL\_OK -eq 0 \] && \[ $CHECK\_TIME -eq 0 \]

38 then

39 pkill keepalived

40 exit 1

41 fi

42 sleep 1

43 done

![48304ba5e6f9fe08f3fa1abda7d326ab.png][]

赋权限

1 chmod 755 /opt/chk\_mysql.sh

启动服务

1 关闭防火墙

2 service iptables stop

3 启动服务

4 /etc/init.d/keepalived start

数据库测试

1 使用局域网内的客户端连接VIP

2 mysql -h192.168.199.52 -uroot -pocnt-123

3 测试是否可用

故障切换检测

![48304ba5e6f9fe08f3fa1abda7d326ab.png][]

1 默认情况下，vip是在master1上的。使用"ip addr"命令查看vip切换情况

2 ip addr

3 查找

4 inet 192.168.199.49/32 scope global eth0 //这个32位子网掩码的vip地址表示该资源目前还在master1机器上，而master2机器上没有

5 同时查看 tail -f /var/log/message 会发现

6 Dec 13 17:52:38 localhost Keepalived\_vrrp\[65545\]: VRRP\_Instance(VI\_1) Entering MASTER STATE

7 Dec 13 17:52:38 localhost Keepalived\_vrrp\[65545\]: VRRP\_Instance(VI\_1) setting protocol VIPs.

8 Dec 13 17:52:38 localhost Keepalived\_vrrp\[65545\]: Sending gratuitous ARP on eth0 for 192.168.199.51

10 关掉master1的mysql服务

11 service mysql stop

12 查看 master1的日志 tail -f /var/log/message 会发现

13 Dec 13 18:52:44 localhost Keepalived\_vrrp\[65545\]: Stopped

14 Dec 13 18:52:44 localhost Keepalived\[65542\]: Stopped Keepalived v1.3.5 (03/19,2017), git commit v1.3.5-6-g6fa32f2

16 查看master2的日志

17 Dec 13 18:52:45 localhost Keepalived\_vrrp\[28451\]: VRRP\_Instance(VI\_1) Transition to MASTER STATE

18 Dec 13 18:52:46 localhost Keepalived\_vrrp\[28451\]: VRRP\_Instance(VI\_1) Entering MASTER STATE

19 Dec 13 18:52:46 localhost Keepalived\_vrrp\[28451\]: VRRP\_Instance(VI\_1) setting protocol VIPs.

20 Dec 13 18:52:46 localhost Keepalived\_vrrp\[28451\]: Sending gratuitous ARP on eth0 for 192.168.199.51

22 此时就已经转移到master2上，

23 恢复master1 的mysql服务

24 service mysql start

25 恢复master1 的keepalive

26 /etc/init.d/keepalived start

28 查看master1的日志

29 Dec 13 18:54:41 localhost Keepalived\_vrrp\[71002\]: VRRP\_Instance(VI\_1) Transition to MASTER STATE

30 Dec 13 18:54:42 localhost Keepalived\_vrrp\[71002\]: VRRP\_Instance(VI\_1) Entering MASTER STATE

31 Dec 13 18:54:42 localhost Keepalived\_vrrp\[71002\]: VRRP\_Instance(VI\_1) setting protocol VIPs.

32 Dec 13 18:54:42 localhost Keepalived\_vrrp\[71002\]: Sending gratuitous ARP on eth0 for 192.168.199.51

34 查看master2的日志

35 Dec 13 18:54:41 localhost Keepalived\_vrrp\[28451\]: VRRP\_Instance(VI\_1) Received advert with higher priority 100, ours 90

36 Dec 13 18:54:41 localhost Keepalived\_vrrp\[28451\]: VRRP\_Instance(VI\_1) Entering BACKUP STATE

37 Dec 13 18:54:41 localhost Keepalived\_vrrp\[28451\]: VRRP\_Instance(VI\_1) removing protocol VIPs.

39 说明服务已经转移到master2上了。

40 至此完成

![48304ba5e6f9fe08f3fa1abda7d326ab.png][]

完成

其它：删除主从复制

1 mysql> stop slave;

2 mysql> slave reset;

3 mysql> reset master;

keepalived

1 启动

2 /etc/init.d/keepalived start

异常解决办法

1 如果不能正常复制，查看状态

2 sql> show slave status \\G;

3 然后可以看到错误信息，一般情况下，重启一下slave就好了

4 sql> stop slave;

[48304ba5e6f9fe08f3fa1abda7d326ab.png]: /images/20221024/246b8356192246758280eadb453aa682.png

mysql 搭建keeeplived_Mysql之配置双主热备+keeepalived.md

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 mysql数据库搭建，主从同步搭建，双机热备(主主复制)

相关搭建keepalived+nginx热备高可用(主备+双主模式)

相关 Nginx：配置Keepalived双主热备

相关 mysql 双主热备主从复制

相关实现keepalived双主热备配置

相关 MySQL高可用性之Keepalived+MySQL（双主热备）

相关 rds mysql双主热备方案_rds主备切换

相关 mysql 搭建keeeplived_Mysql之配置双主热备+keeepalived.md

相关 MySQL-----MySQL主-主双机热备

相关 MySQL-----MySQL主-从双机热备