Ubuntu搭建Mysql+Keepalived高可用的实现(双主热备)

本文主要介绍了Ubuntu搭建Mysql+Keepalived高可用的实现(双主热备),文中通过示例代码介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们可以参考一下

Mysql5.5双机热备

实现方案

Ubuntu搭建Mysql+Keepalived高可用的实现(双主热备)

安装两台Mysql

安装Mysql5.5

?

1

2

3

4

5

6

7

sudo apt-get update

apt-get install aptitude

aptitude install mysql-server-5.5

sudo apt-cache search mariadb-server

apt-get install -y mariadb-server-5.5

卸载

?

1

2

sudo apt-get remove mysql-*

dpkg -l |grep ^rc|awk '{print $2}' |sudo xargs dpkg -P

配置权限

?

1

2

3

4

5

6

vim /etc/mysql/my.cnf

#bind-address = 127.0.0.1

mysql -u root -p

grant all on *.* to root@'%' identified by 'root' with grant option;

flush privileges;

配置两台Mysql主主同步

配置节点1

vim /etc/mysql/my.cnf

?

1

2

3

4

5

6

7

8

server-id = 1 #节点ID

log_bin = mysql-bin.log #日志

binlog_format = "ROW" #日志格式

auto_increment_increment = 2 #自增ID间隔(=节点数,防止ID冲突)

auto_increment_offset = 1 #自增ID起始值(节点ID)

binlog_ignore_db=mysql #不同步的数据库

binlog_ignore_db=information_schema

binlog_ignore_db=performance_schema

重启mysql

?

1

2

service mysql restart

mysql -u root -p

记录节点1的binlog日志位置

?

1

2

show master status;

mysql-bin.000001 245 mysql,information_schema,performance_schema

配置节点2

vim /etc/mysql/my.cnf

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

server-id = 2

log_bin = mysql-bin.log

relay_log = mysql-relay-bin.log #中继日志

log_slave_updates = ON #中继日志执行后,变化计入日志

read_only = 0

binlog_format = "ROW"

auto_increment_increment = 2

auto_increment_offset = 2

binlog_ignore_db=mysql

binlog_ignore_db=information_schema

binlog_ignore_db=performance_schema

replicate_ignore_db=mysql

replicate_ignore_db=information_schema

replicate_ignore_db=performance_schema

配置主从

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

mysql -u root -p

CHANGE MASTER TO

MASTER_HOST='192.168.1.21',

MASTER_USER='root',

MASTER_PASSWORD='root',

MASTER_LOG_FILE='mysql-bin.000001',

MASTER_LOG_POS=245;

#开启同步

start slave

#查看同步状态 Slave_IO_Running和Slave_SQL_Running需要均为Yes

show slave status;

记录节点2的binlog日志位置

?

1

2

3

show master status;

mysql-bin.000001 1029 mysql,information_schema,performance_schema

配置主主(节点1)

vim /etc/mysql/my.cnf

?

1

2

3

4

5

6

relay_log = mysql-relay-bin.log

log_slave_updates = ON

read_only = 0

replicate_ignore_db=mysql

replicate_ignore_db=information_schema

replicate_ignore_db=performance_schema

开启同步

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

mysql -u root -p

CHANGE MASTER TO

MASTER_HOST='192.168.1.20',

MASTER_USER='root',

MASTER_PASSWORD='root',

MASTER_LOG_FILE='mysql-bin.000001',

MASTER_LOG_POS=1029;

#开启同步

start slave

#查看同步状态 Slave_IO_Running和Slave_SQL_Running需要均为Yes

show slave status;

异常处理

Could not initialize master info structure, more error messages can be found in the MySQL error log
解决:reset slave

安装配置Keepalived

安装Keepalived

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

#依赖

sudo apt-get install -y libssl-dev

sudo apt-get install -y openssl

sudo apt-get install -y libpopt-dev

sudo apt-get install -y libnl-dev libnl-3-dev libnl-genl-3.dev

apt-get install daemon

apt-get install libc-dev

apt-get install libnfnetlink-dev

apt-get install libnl-genl-3.dev

#安装

apt-get install keepalived

#编译安装

cd /usr/local

wget https://www.keepalived.org/software/keepalived-2.2.2.tar.gz

tar -zxvf keepalived-2.2.2.tar.gz

mv keepalived-2.2.2 keepalived

./configure –prefix=/usr/local/keepalived

sudo make && make install

#开启日志

sudo vim /etc/rsyslog.d/50-default.conf

*.=info;*.=notice;*.=warn;\\

auth,authpriv.none;\\

cron,daemon.none;\\

mail,news.none -/var/log/messages

sudo service rsyslog restart

tail -f /var/log/messages

sudo mkdir /etc/sysconfig

sudo cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/

sudo cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/

sudo cp /usr/local/keepalived/sbin/keepalived /sbin/

sudo mkdir /etc/keepalived

sudo cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/

配置节点信息

节点1 192.168.1.21

vim /etc/keepalived/keepalived.conf

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

global_defs {

router_id MYSQL_HA  #当前节点名

}

vrrp_instance VI_1 {

state BACKUP #两台配置节点均为BACKUP

interface eth0 #绑定虚拟IP的网络接口

virtual_router_id 51 #VRRP组名,两个节点的设置必须一样,以指明各个节点属于同一VRRP组

priority 101 #节点的优先级,另一台优先级改低一点

advert_int 1 #组播信息发送间隔,两个节点设置必须一样

nopreempt #不抢占,只在优先级高的机器上设置即可,优先级低的机器不设置

authentication { #设置验证信息,两个节点必须一致

auth_type PASS

auth_pass 123456

}

virtual_ipaddress { #指定虚拟IP,两个节点设置必须一样

192.168.1.111

}

}

virtual_server 192.168.1.111 3306 { #linux虚拟服务器(LVS)配置

delay_loop 2 #每个2秒检查一次real_server状态

lb_algo wrr #LVS调度算法,rr|wrr|lc|wlc|lblc|sh|dh

lb_kind DR #LVS集群模式 ,NAT|DR|TUN

persistence_timeout 60 #会话保持时间

protocol TCP #使用的协议是TCP还是UDP

real_server 192.168.1.21 3306 {

weight 3 #权重

notify_down /usr/local/bin/mysql.sh #检测到服务down后执行的脚本

TCP_CHECK {

connect_timeout 10 #连接超时时间

nb_get_retry 3 #重连次数

delay_before_retry 3 #重连间隔时间

connect_port 3306 #健康检查端口

}

}

}

节点2 192.168.1.20

vim /etc/keepalived/keepalived.conf

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

global_defs {

router_id MYSQL_HA  #当前节点名

}

vrrp_instance VI_1 {

state BACKUP #两台配置节点均为BACKUP

interface eth0 #绑定虚拟IP的网络接口

virtual_router_id 51 #VRRP组名,两个节点的设置必须一样,以指明各个节点属于同一VRRP组

priority 100 #节点的优先级,另一台优先级改低一点

advert_int 1 #组播信息发送间隔,两个节点设置必须一样

nopreempt #不抢占,只在优先级高的机器上设置即可,优先级低的机器不设置

authentication { #设置验证信息,两个节点必须一致

auth_type PASS

auth_pass 123456

}

virtual_ipaddress { #指定虚拟IP,两个节点设置必须一样

192.168.1.111

}

}

virtual_server 192.168.1.111 3306 { #linux虚拟服务器(LVS)配置

delay_loop 2 #每个2秒检查一次real_server状态

lb_algo wrr #LVS调度算法,rr|wrr|lc|wlc|lblc|sh|dh

lb_kind DR #LVS集群模式 ,NAT|DR|TUN

persistence_timeout 60 #会话保持时间

protocol TCP #使用的协议是TCP还是UDP

real_server 192.168.1.20 3306 {

weight 3 #权重

notify_down /usr/local/bin/mysql.sh #检测到服务down后执行的脚本

TCP_CHECK {

connect_timeout 10 #连接超时时间

nb_get_retry 3 #重连次数

delay_before_retry 3 #重连间隔时间

connect_port 3306 #健康检查端口

}

}

}

编写异常处理脚本

vim /usr/local/bin/mysql.sh

?

1

2

#!/bin/sh

killall keepalived

分配权限

chmod +x /usr/local/bin/mysql.sh
###测试
重启keepalived

?

1

service keepalived restart

查看日志

?

1

tail -f /var/log/messages

查看虚拟IP

?

1

2

3

4

5

6

7

8

9

ip addr #或ip a 或ifconfig

#主节点会有虚拟IP

eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000

link/ether 52:54:9e:17:53:e5 brd ff:ff:ff:ff:ff:ff

inet 192.168.1.21/24 brd 192.168.1.255 scope global eth0

valid_lft forever preferred_lft forever

inet 192.168.1.111/32 scope global eth0

valid_lft forever preferred_lft forever

关闭主节点的mysql服务

?

1

service mysql stop

日志信息

?

1

2

3

4

5

6

7

8

9

10

11

#主节点

Aug 10 15:00:30 i-7jaope92 Keepalived_healthcheckers[4949]: TCP connection to [192.168.1.20]:3306 failed !!!

Aug 10 15:00:30 i-7jaope92 Keepalived_healthcheckers[4949]: Removing service [192.168.1.20]:3306 from VS [192.168.1.111]:3306

Aug 10 15:00:30 i-7jaope92 Keepalived_healthcheckers[4949]: Executing [/usr/local/bin/mysql.sh] for service [192.168.1.20]:3306 in VS [192.168.1.111]:3306

Aug 10 15:00:30 i-7jaope92 Keepalived_healthcheckers[4949]: Lost quorum 1-0=1 > 0 for VS [192.168.1.111]:3306

Aug 10 15:00:30 i-7jaope92 Keepalived_vrrp[4950]: VRRP_Instance(VI_1) sending 0 priority

Aug 10 15:00:30 i-7jaope92 kernel: [100918.976041] IPVS: __ip_vs_del_service: enter

#从节点

Aug 10 15:00:31 i-6gxo6kx7 Keepalived_vrrp[718]: VRRP_Instance(VI_1) Transition to MASTER STATE

Aug 10 15:00:32 i-6gxo6kx7 Keepalived_vrrp[718]: VRRP_Instance(VI_1) Entering MASTER STATE

虚拟IP从主节点漂移到从节点

?

1

2

3

4

5

6

7

8

ip a

eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000

link/ether 52:54:9e:e7:26:5c brd ff:ff:ff:ff:ff:ff

inet 192.168.1.20/24 brd 192.168.1.255 scope global eth0

valid_lft forever preferred_lft forever

inet 192.168.1.111/32 scope global eth0

valid_lft forever preferred_lft forever

Mysql连接测试

?

1

mysql -h 192.168.1.111 -u root -p<font face="Arial, Verdana, sans-serif"><span style="white-space: normal;"> </span></font>

到此这篇关于Ubuntu搭建Mysql+Keepalived高可用的实现(双主热备)的文章就介绍到这了,更多相关Mysql+Keepalived高可用内容请搜索钦钦技术栈以前的文章或继续浏览下面的相关文章希望大家以后多多支持钦钦技术栈!

原文链接:https://blog.csdn.net/yyyy_11119/article/details/121596994

版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/3865/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

(0)
上一篇 2022-07-20 10:15:18
下一篇 2022-07-20 10:15:31

软件定制开发公司

相关阅读

发表回复

登录后才能评论
通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!