Keepalived MySQL master-master配置高可用

最新推荐文章于 2022-09-09 19:10:14 发布

weixin_34161083

最新推荐文章于 2022-09-09 19:10:14 发布

阅读量67

点赞数

文章标签：数据库网络运维

原文链接：http://blog.51cto.com/heyiyi/1718363

版权

一、软件需求：

1.环境描述

操作系统： CentOS 6.5 X64

MySQL数据库： MySQL 5.1.71.1

keepalived角色划分：

MASTER：10.8.1.11

BACKUP：10.8.1.12

VIP：10.8.1.13

2.系统包安装

1）gcc\keepalived相关

yum install -y pcre-devel openssl-devel popt-devel

2）MySQL相关

yum install -y mysql-server mysql

二、配置MySQL数据库Dual-Master

MySQL服务器配置

同一时刻只有一台keepalived角色MASTER STATE的MySQL数据库主机提供读写服务。

1）mdb01

[root@mdb01 ~]# more /etc/my.cnf

[mysqld]

server-id = 1

log-bin = mysql-bin

binlog-ignore-db = mysql,information_schema

auto-increment-increment = 2

auto-increment-offset = 1

slave-skip-errors = all

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

[root@mdb01 ~]#

2）mdb02

[root@mdb02 ~]# more /etc/my.cnf

[mysqld]

server-id = 2

log-bin = mysql-bin

binlog-ignore-db = mysql,information_schema

auto-increment-increment = 2

auto-increment-offset = 1

slave-skip-errors = all

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

[root@mdb02 ~]#

2.复制配置

1）复制用户设置

GRANT REPLICATION SLAVE ON *.* TO 'replication'@'10.8.1.%' IDENTIFIED BY 'replication';

flush privileges;

2）配置复制点

change master to

master_host='10.8.1.12',(Master IP)

master_user='replication',

master_password='replication',

master_log_file='mysql-bin.000001',(Master binlog)

master_log_pos=106; (Master binlog position)

3）启动复制进程

start slave;

4）查看复制状态

mysql> show slave status\G;

....

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

....

5)验证配置

在两个数据库端分别建立数据库、表，插入记录，如果在两边客户端均可见即成功。

三、安装配置keepalived

1.安装keepalived

[root@mdb01 keepalived-1.2.7]# tar zxvf keepalived-1.2.7.tar.gz

[root@mdb01 keepalived-1.2.7]# cd keepalived-1.2.7

[root@mdb01 keepalived-1.2.7]# ./configure --prefix=/usr/local/keepalived

....

Keepalived configuration

------------------------

Keepalived version : 1.2.7

Compiler : gcc

Compiler flags : -g -O2

Extra Lib : -lpopt -lssl -lcrypto

Use IPVS Framework : Yes

IPVS sync daemon support : Yes

IPVS use libnl : No

Use VRRP Framework : Yes

Use VRRP VMAC : Yes

SNMP support : No

Use Debug flags : No

[root@mdb01 keepalived-1.2.7]#

[root@mdb01 keepalived-1.2.7]# make

[root@mdb01 keepalived-1.2.7]# make install

2.复制相关文件

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

# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/

# mkdir /etc/keepalived/

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

# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/

3.设置启动项

启动优先级：启动MySQL之后启动keepalived

4.keepalived配置文件

1）mdb01

[root@mdb01 ~]# more /etc/keepalived/keepalived.conf

! Configuration File forkeepalived

global_defs {

notification_email {

test@sina.com

}

notification_email_from admin@test.com

smtp_server 127.0.0.1

smtp_connect_timeout 30

router_id MYSQL_HA

}

vrrp_instance VI_1 {

state BACKUP

interface eth0

virtual_router_id 51

priority 100

advert_int 1

nopreempt

authentication {

auth_type PASS

auth_pass 1111

}

virtual_ipaddress {

10.8.1.13

}

virtual_server 10.8.1.13 3306 {

delay_loop 2

#lb_algo rr

#lb_kind DR

persistence_timeout 50

protocol TCP

real_server 10.8.1.11 3306 {

weight 3

notify_down /usr/local/keepalived/mysql.sh

TCP_CHECK {

connect_timeout 3

nb_get_retry 3

delay_before_retry 3

}

[root@mdb01 ~]#

2）mdb02

[root@mdb02 ~]# more /etc/keepalived/keepalived.conf

! Configuration File forkeepalived

global_defs {

notification_email {

test@sina.com

}

notification_email_from admin@test.com

smtp_server 127.0.0.1

smtp_connect_timeout 30

router_id MYSQL_HA

}

vrrp_instance VI_1 {

state BACKUP

interface eth0

virtual_router_id 51

priority 90

advert_int 1

authentication {

auth_type PASS

auth_pass 1111

}

virtual_ipaddress {

10.8.1.13

}

virtual_server 10.8.1.13 3306 {

delay_loop 2

#lb_algo rr

#lb_kind DR

persistence_timeout 50

protocol TCP

real_server 10.8.1.12 3306 {

weight 3

notify_down /usr/local/keepalived/mysql.sh

TCP_CHECK {

connect_timeout 3

nb_get_retry 3

delay_before_retry 3

}

[root@mdb02 ~]#

5.相关切换脚本

当检测到MySQL进程端口不存在时执行以下脚本mysql.sh，

停止本机keepalived进程及移除VIP，备机状态由Backup转换

Master State同时获取VIP。

[root@mdb01 ~]# more /usr/local/keepalived/mysql.sh

#!/bin/bash

#pkill keepalived（此行可能会出现VIP漂移过程中两台都有VIP的现象，杀进程不彻底）

/etc/init.d/keepalived stop

[root@mdb01 ~]#

6.启动keepalived

查看VIP绑定情况

四.切换测试

1.停止master state主机MySQL数据库

[root@mdb02 ~]# /etc/init.d/mysqld stop

Stopping mysqld: [ OK ]

2.mdb02移除VIP和停止keepalived进程

1）mdb02移除 protocol VIPs.

注意命令行输出中已无VIP显示

[root@mdb02 ~]# ip addr

1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN

link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00

inet 127.0.0.1/8 scope host lo

inet6 ::1/128 scope host

valid_lft forever preferred_lft forever

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

link/ether 00:0c:29:ec:6e:29 brd ff:ff:ff:ff:ff:ff

inet 10.8.1.12/16 brd 10.8.255.255 scope global eth0

inet6 fe80::20c:29ff:feec:6e29/64 scope link

valid_lft forever preferred_lft forever

[root@mdb02 ~]#

Keepalived日志输出：

[root@mdb02 ~]# tail -f /var/log/messages

Dec 1 22:01:05 mdb02 Keepalived_healthcheckers[1121]: TCP connection to [10.8.1.12]:3306 failed !!!

Dec 1 22:01:05 mdb02 Keepalived_healthcheckers[1121]: Removing service [10.8.1.12]:3306 from VS [10.8.1.13]:3306

Dec 1 22:01:05 mdb02 Keepalived_healthcheckers[1121]: IPVS: Service not defined

Dec 1 22:01:05 mdb02 Keepalived_healthcheckers[1121]: Executing [/usr/local/keepalived/mysql.sh] for service [10.8.1.12]:3306 in VS [10.8.1.13]:3306

Dec 1 22:01:05 mdb02 Keepalived_healthcheckers[1121]: Lost quorum 1-0=1 > 0 for VS [10.8.1.13]:3306

Dec 1 22:01:05 mdb02 Keepalived_healthcheckers[1121]: Remote SMTP server [127.0.0.1]:25 connected.

Dec 1 22:01:05 mdb02 Keepalived[1118]: Stopping Keepalived v1.2.7 (12/01,2015)

Dec 1 22:01:05 mdb02 Keepalived_healthcheckers[1121]: IPVS: No such service

Dec 1 22:01:05 mdb02 Keepalived_vrrp[1122]: VRRP_Instance(VI_1) sending 0 priority

Dec 1 22:01:05 mdb02 Keepalived_vrrp[1122]: VRRP_Instance(VI_1) removing protocol VIPs.

2）keepalived进程消失

[root@mdb02 ~]# /etc/init.d/keepalived status

keepalived is stopped

3.mdb01 绑定VIP

[root@mdb01 ~]# ip addr

1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN

link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00

inet 127.0.0.1/8 scope host lo

inet6 ::1/128 scope host

valid_lft forever preferred_lft forever

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

link/ether 00:0c:29:02:3a:68 brd ff:ff:ff:ff:ff:ff

inet 10.8.1.11/16 brd 10.8.255.255 scope global eth0

inet 10.8.1.13/32 scope global eth0

inet6 fe80::20c:29ff:fe02:3a68/64 scope link

valid_lft forever preferred_lft forever

4.mdb01转换为MASTER STATE

[root@mdb01 ~]# tail -f /var/log/messages

Dec 1 22:06:34 mdb01 Keepalived_vrrp[1137]: VRRP_Instance(VI_1) Transition to MASTER STATE

Dec 1 22:06:35 mdb01 Keepalived_vrrp[1137]: VRRP_Instance(VI_1) Entering MASTER STATE

Dec 1 22:06:35 mdb01 Keepalived_vrrp[1137]: VRRP_Instance(VI_1) setting protocol VIPs.

Dec 1 22:06:35 mdb01 Keepalived_vrrp[1137]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 10.8.1.13

Dec 1 22:06:35 mdb01 Keepalived_healthcheckers[1136]: Netlink reflector reports IP 10.8.1.13 added

Dec 1 22:06:40 mdb01 Keepalived_vrrp[1137]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 10.8.1.13

5.mdb02启动MySQL和Keepalived

MySQL和Keepalived服务均启动后，主机转换为BACKUP STATE

[root@mdb02 ~]# tail -f /var/log/messages

Dec 1 22:10:54 mdb02 Keepalived[2484]: Starting Keepalived v1.2.7 (12/01,2015)

Dec 1 22:10:54 mdb02 Keepalived[2485]: Starting Healthcheck child process, pid=2487

Dec 1 22:10:54 mdb02 Keepalived[2485]: Starting VRRP child process, pid=2488

Dec 1 22:10:54 mdb02 Keepalived_vrrp[2488]: Interface queue is empty

Dec 1 22:10:54 mdb02 Keepalived_vrrp[2488]: Netlink reflector reports IP 10.8.1.12 added

Dec 1 22:10:54 mdb02 Keepalived_vrrp[2488]: Netlink reflector reports IP fe80::20c:29ff:feec:6e29 added

Dec 1 22:10:54 mdb02 Keepalived_vrrp[2488]: Registering Kernel netlink reflector

Dec 1 22:10:54 mdb02 Keepalived_vrrp[2488]: Registering Kernel netlink command channel

Dec 1 22:10:54 mdb02 Keepalived_vrrp[2488]: Registering gratuitous ARP shared channel

Dec 1 22:10:54 mdb02 Keepalived_vrrp[2488]: Opening file '/etc/keepalived/keepalived.conf'.

Dec 1 22:10:54 mdb02 Keepalived_healthcheckers[2487]: Interface queue is empty

Dec 1 22:10:54 mdb02 Keepalived_healthcheckers[2487]: Netlink reflector reports IP 10.8.1.12 added

Dec 1 22:10:54 mdb02 Keepalived_healthcheckers[2487]: Netlink reflector reports IP fe80::20c:29ff:feec:6e29 added

Dec 1 22:10:54 mdb02 Keepalived_healthcheckers[2487]: Registering Kernel netlink reflector

Dec 1 22:10:54 mdb02 Keepalived_healthcheckers[2487]: Registering Kernel netlink command channel

Dec 1 22:10:54 mdb02 Keepalived_healthcheckers[2487]: Opening file '/etc/keepalived/keepalived.conf'.

Dec 1 22:10:54 mdb02 Keepalived_vrrp[2488]: Configuration is using : 62808 Bytes

Dec 1 22:10:54 mdb02 Keepalived_vrrp[2488]: Using LinkWatch kernel netlink reflector...

Dec 1 22:10:54 mdb02 Keepalived_healthcheckers[2487]: Configuration is using : 11379 Bytes

Dec 1 22:10:54 mdb02 Keepalived_vrrp[2488]: VRRP_Instance(VI_1) Entering BACKUP STATE

Dec 1 22:10:54 mdb02 Keepalived_vrrp[2488]: VRRP sockpool: [ifindex(2), proto(112), fd(11,12)]

Dec 1 22:10:54 mdb02 Keepalived_healthcheckers[2487]: IPVS: Scheduler not found

Dec 1 22:10:54 mdb02 Keepalived_healthcheckers[2487]: IPVS: No such process

Dec 1 22:10:54 mdb02 Keepalived_healthcheckers[2487]: Using LinkWatch kernel netlink reflector...

Dec 1 22:10:54 mdb02 Keepalived_healthcheckers[2487]: Activating healthchecker for service [10.8.1.12]:3306

Dec 1 22:10:54 mdb02 kernel: IPVS: Scheduler module ip_vs_ not found

Dec 1 22:10:55 mdb02 Keepalived_healthcheckers[2487]: TCP connection to [10.8.1.12]:3306 failed !!!

Dec 1 22:10:55 mdb02 Keepalived_healthcheckers[2487]: Removing service [10.8.1.12]:3306 from VS [10.8.1.13]:3306

Dec 1 22:10:55 mdb02 Keepalived_healthcheckers[2487]: IPVS: Service not defined

Dec 1 22:10:55 mdb02 Keepalived_healthcheckers[2487]: Executing [/usr/local/keepalived/mysql.sh] for service [10.8.1.12]:3306 in VS [10.8.1.13]:3306

Dec 1 22:10:55 mdb02 Keepalived_healthcheckers[2487]: Lost quorum 1-0=1 > 0 for VS [10.8.1.13]:3306

Dec 1 22:10:55 mdb02 Keepalived_healthcheckers[2487]: Remote SMTP server [127.0.0.1]:25 connected.

Dec 1 22:10:55 mdb02 Keepalived[2485]: Stopping Keepalived v1.2.7 (12/01,2015)

Dec 1 22:10:55 mdb02 Keepalived_healthcheckers[2487]: IPVS: No such service

VIP不做漂移

[root@mdb02 ~]# ip addr

1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN

link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00

inet 127.0.0.1/8 scope host lo

inet6 ::1/128 scope host

valid_lft forever preferred_lft forever

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

link/ether 00:0c:29:ec:6e:29 brd ff:ff:ff:ff:ff:ff

inet 10.8.1.12/16 brd 10.8.255.255 scope global eth0

inet6 fe80::20c:29ff:feec:6e29/64 scope link

valid_lft forever preferred_lft forever

[root@mdb02 ~]#

五、总结：

正常情况下两台Master机器的MySQL及Keepalived均为启动状态：

1.当角色为MASTER STATE的MySQL数据库主机3306端口无法连接时即发生切换

1）停止keepalived进程。

2）移除VIP，

3）主机为故障状态，需要人工修复。

2.BACKUP STATE状态主机切换过程：

1）.转变角色为MASTER STATE。

向224.0.0.18发送VRRP心跳信息。

[root@mdb02 ~]# tcpdump vrrp

tcpdump: verbose output suppressed, use -v or -vv for full protocol decode

listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes

22:24:42.550593 IP mdb02 > 224.0.0.18: VRRPv2, Advertisement, vrid 51, prio 90, authtype simple, intvl 1s, length 20

22:24:43.563523 IP mdb02 > 224.0.0.18: VRRPv2, Advertisement, vrid 51, prio 90, authtype simple, intvl 1s, length 20

22:24:44.565224 IP mdb02 > 224.0.0.18: VRRPv2, Advertisement, vrid 51, prio 90, authtype simple, intvl 1s, length 20

22:24:45.567882 IP mdb02 > 224.0.0.18: VRRPv2, Advertisement, vrid 51, prio 90, authtype simple, intvl 1s, length 20

22:24:46.570211 IP mdb02 > 224.0.0.18: VRRPv2, Advertisement, vrid 51, prio 90, authtype simple, intvl 1s, length 20

22:24:47.572696 IP mdb02 > 224.0.0.18: VRRPv2, Advertisement, vrid 51, prio 90, authtype simple, intvl 1s, length 20

22:24:48.575324 IP mdb02 > 224.0.0.18: VRRPv2, Advertisement, vrid 51, prio 90, authtype simple, intvl 1s, length 20

22:24:49.578470 IP mdb02 > 224.0.0.18: VRRPv2, Advertisement, vrid 51, prio 90, authtype simple, intvl 1s, length 20

22:24:50.580935 IP mdb02 > 224.0.0.18: VRRPv2, Advertisement, vrid 51, prio 90, authtype simple, intvl 1s, length 20

2）.绑定VIP，对外提供读写服务。

3.正常情况下，步骤1的故障主机修复后需要继续对外服务，启动需按照如下顺序：

1）启动MySQL数据库。

2）启动keepalived进程。

3）角色为BACKUP STATE。

4）接收vrrp心跳信息。

root@mdb01 ~]# tcpdump vrrp

tcpdump: verbose output suppressed, use -v or -vv for full protocol decode

listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes

22:29:35.056846 IP 10.8.1.12 > 224.0.0.18: VRRPv2, Advertisement, vrid 51, prio 90, authtype simple, intvl 1s, length 20

22:29:36.058852 IP 10.8.1.12 > 224.0.0.18: VRRPv2, Advertisement, vrid 51, prio 90, authtype simple, intvl 1s, length 20

22:29:37.061641 IP 10.8.1.12 > 224.0.0.18: VRRPv2, Advertisement, vrid 51, prio 90, authtype simple, intvl 1s, length 20

5）不对外提供读写服务。

注意事项：

1.当两台服务器MySQL及Keepalived均为启动状态时，当发送故障切换，

VIP在故障机与备份机漂移需要适当时间间隔，一般10-20秒左右。

2.授权两台Mysql服务器允许root远程登录，用于在其他服务器登陆测试！

mysql> grant all on *.* to'root'@'10.8.1.%' identified by '123456';

mysql> flush privileges;

转载于:https://blog.51cto.com/heyiyi/1718363

weixin_34161083

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫