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

最新推荐文章于 2023-01-03 10:12:50 发布

weixin_39867212

最新推荐文章于 2023-01-03 10:12:50 发布

阅读量61

点赞数

文章标签： mysql 搭建keeeplived

本文链接：https://blog.csdn.net/weixin_39867212/article/details/113403385

版权

准备

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

配置服务器

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

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

1 service mysql restart

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

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;

同步数据

1 1.锁定表

2 mysql> flush tables with read lock;

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

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

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;

查看同步状态，如下出现两个“Yes”，表明同步成功！(Slave_IO_Running和Slave_SQL_Running)

1 mysql> show slave status \G;

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

keepalived故障自动切换配置

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

master1服务器配置

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 }

master2服务器配置修改 mcast_src_ip priority

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 }

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

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

赋权限

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 测试是否可用

故障切换检测

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 至此完成

完成

其它：删除主从复制

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;

weixin_39867212

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql 搭建keeeplived_Mysql之配置双主热备+keeepalived.md

准备1 1. 双主 master1 192.168.199.492 master2 192.168.199.503 VIP 192.168.199.52 //虚拟IP4 2.环境 master:nginx + php + mysql + keepalived5 VIP:只要和master在一个局域网内即可。6 3. 服务器之间网络通畅，可以互相ping通。7...
复制链接

扫一扫