linuxweb集群mysql_企业web高可用集群实战之lvs+keepalived+mysql

「爱情、让人受尽委屈。」 2023-01-12 07:48 43阅读 0赞

前端用两台server做lvs+keepalived负载调度器，中间用apache+php作为web应用服务器，后端用两台做mysql高可用，用nfs、mfs或作为web文件共享服务器

系统环境：

LVS调度主服务器：192.168.8.11

LVS调度从服务器：192.168.8.12

LVS VIP：192.168.8.10

apache服务器：192.168.8.20 192.168.8.21

MySQL主服务器：192.168.8.31

MySQL从服务器：192.168.8.32

MySQL VIP：192.168.8.30

由于工作时间比较紧，同时还要培训，架构图后期补上!!

用到软件准备：

所有服务器软件包都放在/opt 目录下，下面是下载地址：

wget http://www.keepalived.org/software/keepalived-1.1.20.tar.gz

wget http://downloads.mysql.com/archives/mysql-5.5/mysql-5.5.24-linux2.6-x86\_64.tar.gz \#64位

wget http://downloads.mysql.com/archives/mysql-5.5/mysql-5.5.24-linux2.6-i686.tar.gz \#32位

一、LVS+Keepalived

1.安装ipvsadm

yum -y install ipvsadm kernel-devel

ln -sv /usr/src/kernels/2.6.18-308.8.2.el5-x86\_64/ /usr/src/linux

2.安装keepalived

cd /opt/

tar zxf keepalived-1.1.20.tar.gz

cd keepalived-1.1.20

./configure --prefix=/usr/local/keepalived --with-kernel-dir=/usr/src/kernels/2.6.18-308.11.1.el5-x86\_64/

出现如下信息内核加载成功：

Keepalived configuration

\------------------------

Keepalived version : 1.1.20

Compiler : gcc

Compiler flags : -g -O2

Extra Lib : -lpopt -lssl -lcrypto

Use IPVS Framework : Yes

IPVS sync daemon support : Yes

Use VRRP Framework : Yes

Use Debug flags : No

make;make install

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

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

mkdir /etc/keepalived

chkconfig keepalived on

3.创建配置文件

cat /etc/keepalived/keepalived.conf

! Configuration File forkeepalived

global\_defs \{

notification\_email \{

250621008@qq.com

notification\_email\_from 250621008@qq.com

smtp\_server smtp.163.com

\#smtp\_connect\_timeout 30

router\_id LVS\_DEVEL

vrrp\_instance VI\_1 \{

state MASTER \#备份服务器上将MASTER改为BACKUP

interfaceeth0

lvs\_sync\_daemon\_inteface eth0

virtual\_router\_id 51

priority 100 \#备份服务上将100改为低于100的数值

advert\_int 5

authentication \{

auth\_type PASS

auth\_pass 1111

virtual\_ipaddress \{

192.168.8.10

virtual\_server 192.168.8.10 80 \{

delay\_loop 6 \#(每隔10秒查询realserver状态)

lb\_algo wlc \#(lvs 算法)

lb\_kind DR \#(Direct Route)

persistence\_timeout 60 \#(同一IP的连接60秒内被分配到同一台realserver)

protocol TCP \#(用TCP协议检查realserver状态)

real\_server 192.168.8.11 80 \{

weight 100 \#(权重)

TCP\_CHECK \{

connect\_timeout 10 \#(10秒无响应超时)

nb\_get\_retry 3

delay\_before\_retry 3

connect\_port 80

real\_server 192.168.8.12 80 \{

weight 100

TCP\_CHECK \{

connect\_timeout 10

nb\_get\_retry 3

delay\_before\_retry 3

connect\_port 80

PS :从配置文件参考以上主的配置文件，不同的地方是红色标注两处！

二、WEB集群

1、这里可以选择lamp、lnmp、lanmp等web架构，至于搭建略！

2、在各web节点上创建realserver脚本

cat /root/sh/lvs\_real.sh

\#!/bin/bash

\#Description: Config realserver script

\#Written by : opsren----http://linuxops.blog.51cto.com

SNS\_VIP=192.168.8.10

/etc/rc.d/init.d/functions

case"$1"in

start)

/sbin/ifconfig lo:0 $SNS\_VIP netmask 255.255.255.255 broadcast $SNS\_VIP

/sbin/route add -host $SNS\_VIP dev lo:0

echo "1">/proc/sys/net/ipv4/conf/lo/arp\_ignore

echo "2">/proc/sys/net/ipv4/conf/lo/arp\_announce

echo "1">/proc/sys/net/ipv4/conf/all/arp\_ignore

echo "2">/proc/sys/net/ipv4/conf/all/arp\_announce

sysctl -p >/dev/null2>&1

echo "RealServer Start OK"

;;

stop)

/sbin/ifconfig lo:0 down

/sbin/route del $SNS\_VIP >/dev/null2>&1

echo "0">/proc/sys/net/ipv4/conf/lo/arp\_ignore

echo "0">/proc/sys/net/ipv4/conf/lo/arp\_announce

echo "0">/proc/sys/net/ipv4/conf/all/arp\_ignore

echo "0">/proc/sys/net/ipv4/conf/all/arp\_announce

echo "RealServer Stoped"

;;

\*)

echo "Usage: $0 \{start|stop\}"

exit 1

esac

exit 0

给予可执行权限：

chmod +755 /roo/sh/lvs\_real.sh

启动脚本：

/root/sh/lvs\_real.sh start

三、MySQL高可用(双主)部署

1.MySQL安装与配置

这里采用mysql5.5.24二进制tar包

tar zxf mysql-5.5.24-linux2.6-x86\_64.tar.gz -C /usr/local/mysql5.5

设置配置文件：

cat >> /etc/my.cnf << EOF

\[client\]

port = 3306

socket = /tmp/mysql.sock

\#character-set-server = utf8

\[mysqld\]

connect\_timeout = 20

basedir = /usr/local/mysql

datadir = /data/mysql-5.5/data

user = mysql

port = 3306

socket = /tmp/mysql.sock

pid-file = /data/mysql-5.5/data/mysql.pid

\#\#init\_connect = 'SET AUTOCOMMIT=1'

character-set-server = utf8

open\_files\_limit = 10240

table\_open\_cache = 6144

table\_definition\_cache = 4096

table\_cache = 512

max\_connections = 1100

max\_connect\_errors = 1000

back\_log = 500

event\_scheduler = ON

external-locking = FALSE

skip-name-resolve

\#default\_table\_type = MyISAM

server-id = 10

\#master-host = 192.168.8.31

\#master-user = repl

\#master-password = repl

\#replicate-ignore-db=mysql

slave-net-timeout=35

\#master-connect-retry=30

\#relay-log = relay-bin

\#max-relay-log-size = 128M

\#skip-slave-start

\#replicate-same-server-id=0

\#log\_slave\_update

\#replicate-do-db=WATCDB01

\#replicate-do-db=WATCDB02

\#replicate-do-db=WATCDB03

\#replicate-do-db=WATCDB04

wait\_timeout=100

interactive\_timeout = 100

\#\#Add for debug,record all the sql

\#\#log = /usr/local/mysql/fetiondata/data/g\_log.log

\#\#innodb\_status\_file = 1

\#tmpdir = /tmp

tmpdir = /data/mysql-5.5/tmp

tmp\_table\_size = 32M

max\_heap\_table\_size = 64M

thread\_cache\_size = 64

thread\_concurrency = 32

thread\_stack = 192K

max\_allowed\_packet = 16M

sort\_buffer\_size = 512k

join\_buffer\_size = 512k

query\_cache\_size = 0

query\_cache\_limit = 2M

key\_buffer\_size = 64M

read\_buffer\_size = 512k

read\_rnd\_buffer\_size = 512k

bulk\_insert\_buffer\_size = 32M

binlog\_format = mixed

\#binlog\_cache\_size = 1M

\#log-bin = /data/mysql-5.5/logs/mysql-bin

max\_binlog\_size = 128M

\#log\_long\_format

slow-query-log

slow-query-log-file = mysql.slow

\#log\_queries\_not\_using\_indexes = 0

long\_query\_time = 1

myisam\_sort\_buffer\_size = 32M

myisam\_max\_sort\_file\_size = 10G

\#myisam\_max\_extra\_sort\_file\_size = 10G

myisam\_repair\_threads = 1

myisam\_recover

log\_error = /data/mysql-5.5/logs/error.log

\# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE

\#default\#transaction\_isolation = REPEATABLE-READ

transaction\_isolation = READ-COMMITTED

\#innodb\_checksums = 0

\#innodb\_doublewrite = 0

innodb\_flush\_method = O\_DIRECT

\#96G

innodb\_buffer\_pool\_size = 48G

\#innodb\_buffer\_pool\_size = 60G

\#innodb\_buffer\_pool\_instances = 10

innodb\_buffer\_pool\_instances = 4

\#innodb\_buffer\_pool\_size = 33G

innodb\_additional\_mem\_pool\_size = 16M

innodb\_data\_home\_dir =

\#innodb\_data\_file\_path = ibdata1:20G;ibdata2:20G;ibdata3:20G;ibdata4:20G;ibdata5:20G;ibdata6:20G;ibdata7:20G;ibdata8:100M:autoextend

innodb\_data\_file\_path = ibdata1:2048M:autoextend

innodb\_log\_group\_home\_dir = /data/mysql-5.5/logs

innodb\_log\_buffer\_size = 16M

innodb\_log\_file\_size = 1024M

innodb\_log\_files\_in\_group = 3

innodb\_thread\_concurrency = 288

innodb\_thread\_sleep\_delay = 500

\#innodb\_file\_io\_threads = 8

innodb\_concurrency\_tickets = 1000

innodb\_sync\_spin\_loops=40

\#\#innodb\_force\_recovery is only used for recovery mode;

\#\#innodb\_force\_recovery = 3

innodb\_flush\_log\_at\_trx\_commit = 0

innodb\_max\_dirty\_pages\_pct = 90

\#innodb\_lock\_wait\_timeout = 120

innodb\_lock\_wait\_timeout = 10

innodb\_support\_xa = 0

\#Added to allow create function

log\_bin\_trust\_function\_creators = 1

innodb\_file\_per\_table = 1

\#turn

innodb\_stats\_on\_metadata=0

innodb\_old\_blocks\_pct=20

innodb\_change\_buffering=all

\#innodb\_max\_purge\_lag

innodb\_purge\_threads=1

innodb\_purge\_batch\_size = 32

innodb\_read\_io\_threads = 8

innodb\_write\_io\_threads = 8

innodb\_io\_capacity = 2000

innodb\_file\_format = Barracuda

\#manage

performance\_schema

performance\_schema\_events\_waits\_history\_size=100

performance\_schema\_events\_waits\_history\_long\_size=30000

\#innodb\_strict\_mode=1

\[mysqldump\]

quick

max\_allowed\_packet = 128M

\[mysql\]

no-auto-rehash

\[mysqlhotcopy\]

interactive-timeout

\[mysqld\_safe\]

open-files-limit = 28192

EOF

注意两点：

1).在两台MySQL服务器上开启bin-log日志，默认是开启的！

2).另外一台服务器节点的server-id设置为20，本来设置为10

2.将31服务器设置32的主服务器

在31服务器上建立授权用户！

mysql> grant replication slave on \*.\* to'rep'@'%'identified by"rep";

Query OK, 0 rows affected (0.09 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.09 sec)

mysql> show master status;

\+---------------+----------+--------------+------------------+

\+---------------+----------+--------------+------------------+

| binlog.000004 | 138637 | | |

\+---------------+----------+--------------+------------------+

1 row inset (0.03 sec)

在32服务器上把31服务器设为自己的主服务器，mysql客户端连接到32服务器：

mysql> change master to master\_host='192.168.8.31',master\_user='rep',master\_password='rep',master\_log\_file='binlog.000004',master\_log\_pos=138637;

Query OK, 0 rows affected (0.01 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\\G

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Slave\_IO\_State: Waiting formaster to send event

Master\_Host: 192.168.8.31

Master\_User: rep

Master\_Port: 3306

Connect\_Retry: 60

Master\_Log\_File: binlog.000004

Read\_Master\_Log\_Pos: 138637

Relay\_Log\_File: relaylog.000002

Relay\_Log\_Pos: 250

Relay\_Master\_Log\_File: binlog.000004

Slave\_IO\_Running: Yes

Slave\_SQL\_Running: Yes

Replicate\_Do\_DB:

Replicate\_Ignore\_DB: mysql,test,information\_schema

Replicate\_Do\_Table:

Replicate\_Ignore\_Table:

Replicate\_Wild\_Do\_Table:

Replicate\_Wild\_Ignore\_Table:

Last\_Errno: 0

Last\_Error:

Skip\_Counter: 0

Exec\_Master\_Log\_Pos: 138637

Relay\_Log\_Space: 399

Until\_Condition: None

Until\_Log\_File:

Until\_Log\_Pos: 0

Master\_SSL\_Allowed: No

Master\_SSL\_CA\_File:

Master\_SSL\_CA\_Path:

Master\_SSL\_Cert:

Master\_SSL\_Cipher:

Master\_SSL\_Key:

Seconds\_Behind\_Master: 0

Master\_SSL\_Verify\_Server\_Cert: No

Last\_IO\_Errno: 0

Last\_IO\_Error:

Last\_SQL\_Errno: 0

Last\_SQL\_Error:

Replicate\_Ignore\_Server\_Ids:

Master\_Server\_Id: 1

1 row inset (0.00 sec)

PS:

Slave\_IO\_Running: Yes

Slave\_SQL\_Running: Yes

两处都为YES，表示OK！

3.将32服务器设置31的主服务器

在32服务器上建立授权用户：

mysql> grant replication slave on \*.\* to'rep'@'%'identified by"rep";

Query OK, 0 rows affected (0.03 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> show master status;

\+---------------+----------+--------------+------------------+

\+---------------+----------+--------------+------------------+

| binlog.000004 | 320 | | |

\+---------------+----------+--------------+------------------+

1 row inset (0.00 sec)

在31服务器上把32服务器设为自己的主服务器，mysql客户端连接到31服务器：

mysql> change master to master\_host='192.168.8.32',master\_user='rep',master\_password='rep',master\_log\_file='binlog.000004',master\_log\_pos=320;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 74

Current database: \*\*\* NONE \*\*\*

Query OK, 0 rows affected (0.07 sec)

mysql> start slave;

Query OK, 0 rows affected (0.03 sec)

mysql> show slave status\\G

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Slave\_IO\_State: Waiting formaster to send event

Master\_Host: 192.168.8.32

Master\_User: rep

Master\_Port: 3306

Connect\_Retry: 60

Master\_Log\_File: binlog.000004

Read\_Master\_Log\_Pos: 320

Relay\_Log\_File: relaylog.000002

Relay\_Log\_Pos: 250

Relay\_Master\_Log\_File: binlog.000004

Slave\_IO\_Running: Yes

Slave\_SQL\_Running: Yes

Replicate\_Do\_DB:

Replicate\_Ignore\_DB: mysql,test,information\_schema

Replicate\_Do\_Table:

Replicate\_Ignore\_Table:

Replicate\_Wild\_Do\_Table:

Replicate\_Wild\_Ignore\_Table:

Last\_Errno: 0

Last\_Error:

Skip\_Counter: 0

Exec\_Master\_Log\_Pos: 320

Relay\_Log\_Space: 399

Until\_Condition: None

Until\_Log\_File:

Until\_Log\_Pos: 0

Master\_SSL\_Allowed: No

Master\_SSL\_CA\_File:

Master\_SSL\_CA\_Path:

Master\_SSL\_Cert:

Master\_SSL\_Cipher:

Master\_SSL\_Key:

Seconds\_Behind\_Master: 0

Master\_SSL\_Verify\_Server\_Cert: No

Last\_IO\_Errno: 0

Last\_IO\_Error:

Last\_SQL\_Errno: 0

Last\_SQL\_Error:

Replicate\_Ignore\_Server\_Ids:

Master\_Server\_Id: 2

1 row inset (0.00 sec)

4.主主同步测试

先在31服务器上建个库：

mysql> create database t\_test;

Query OK, 1 row affected (0.03 sec)

mysql> show databases;

\+--------------------+

| Database |

\+--------------------+

| information\_schema |

| mysql |

| performance\_schema |

| t\_test |

| test |

\+--------------------+

5 rows inset (0.00 sec)

再到32服务器查询是否同步过来这个库：

mysql> show databases;

\+--------------------+

| Database |

\+--------------------+

| information\_schema |

| bbs |

| mysql |

| performance\_schema |

| t\_test |

| test |

\+--------------------+

6 rows inset (0.00 sec)

可以看到同步过来了！

反过来一样！这步略！

5.keepalived安装配置

yum -y install kernel-devel ipvsadm

ln -sv /usr/src/kernels/2.6.18-308.8.2.el5-x86\_64/ /usr/src/linux

cd /opt

tar zxf keepalived-1.1.20.tar.gz

cd keepalived-1.1.20

./configure --prefix=/usr/local/keepalived --with-kernel-dir=/usr/src/kernels/2.6.18-308.11.1.el5-x86\_64/

make;make install

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

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

mkdir /etc/keepalived

chkconfig keepalived on

创建配置文件：

主服务器：

cat /etc/keepalived/keepalived.conf

! Configuration File forkeepalived

global\_defs \{

notification\_email \{

250621008@qq.com

notification\_email\_from 250621008@qq.com

smtp\_server 127.0.0.1

smtp\_connect\_timeout 30

router\_id mysql-ha

vrrp\_instance VI\_1 \{

state BACKUP

interfaceeth0

virtual\_router\_id 51

priority 100

advert\_int 1

nopreempt \#不抢占,只在priority高的节点上设置

authentication \{

auth\_type PASS

auth\_pass 1111

virtual\_ipaddress \{

192.168.8.30

virtual\_server 192.168.8.30 3306 \{

delay\_loop 2

lb\_algo wrr

lb\_kind DR

persistence\_timeout 60

protocol TCP

real\_server 192.168.8.31 3306

weight 3

notify\_down /root/sh/mysql.sh

TCP\_CHECK \{

connect\_timeout 10

nb\_get\_retry 3

delay\_before\_retry 3

connect\_port 3306

\++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

从服务器：

cat /etc/keepalived/keepalived.conf

! Configuration File forkeepalived

global\_defs \{

notification\_email \{

250621008@qq.com

notification\_email\_from 250621008@qq.com

smtp\_server 127.0.0.1

smtp\_connect\_timeout 30

router\_id mysql-ha

vrrp\_instance VI\_1 \{

state BACKUP

interfaceeth0

virtual\_router\_id 51

priority 90

advert\_int 1

authentication \{

auth\_type PASS

auth\_pass 1111

virtual\_ipaddress \{

192.168.8.30

virtual\_server 192.168.8.30 3306 \{

delay\_loop 2

lb\_algo wrr

lb\_kind DR

persistence\_timeout 60

protocol TCP

real\_server 192.168.8.32 3306

weight 3

notify\_down /root/sh/mysql.sh

TCP\_CHECK \{

connect\_timeout 10

nb\_get\_retry 3

delay\_before\_retry 3

connect\_port 3306

主备配置文件注意几点：

1).router\_id 两边必须相同

2).state 两边都为BACKUP

3).virtual\_router\_id 两边必须相同

4).priority 主节点的值必须大于从节点的值

5).nopreempt 不抢占，只在priority高的节点(即主节点)上设置

6).real\_server 只需要本机的IP，不需要其它节点的！

7).notify\_down 作用是监测到当mysql停止工作时自动关闭本机的keepalived的脚本，实现故障转移！

在主从上都创建当mysql停止工作时自动关闭本机的keepalived的脚本：

cat /root/sh/mysql.sh

\#!/bin/bash

MYSQL=/usr/local/mysql/bin/mysql

MYSQL\_HOST=localhost

MYSQL\_USER=root

MYSQL\_PASSWORD="admin"

CHECK\_TIME=3

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

MYSQL\_OK=1

functioncheck\_mysql\_helth ()\{

$MYSQL -h $MYSQL\_HOST -u $MYSQL\_USER -p $MYSQL\_PASSWORD -e "show status;"> /dev/null2>&1

if\[ $? = 0 \] ;then

MYSQL\_OK=0

else

MYSQL\_OK=1

return$MYSQL\_OK

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

let "CHECK\_TIME -= 1"

check\_mysql\_helth

if\[ $MYSQL\_OK = 0 \] ; then

CHECK\_TIME=0

exit 0

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

then

/etc/init.d/keepalived stop

exit 1

sleep 1

done

6.故障转移测试

分别启动主备节点的keepalived服务，然后在主节点查看VIP有没有启动：

31服务器：

\[root@mysql1 ~\]\# ip a |grep eth0

2: eth0: mtu 1500 qdisc pfifo\_fast qlen 1000

inet 192.168.8.31/24 brd 192.168.8.255 scope global eth0

inet 192.168.8.30/32 scope global eth0

32服务器：

\[root@mysql2 keepalived-1.1.19\]\# ip a |grep eth0

2: eth0: mtu 1500 qdisc pfifo\_fast qlen 1000

inet 192.168.8.32/24 brd 192.168.8.255 scope global eth0

现在把31服务器的mysql服务停掉：

\[root@mysql1 keepalived\]\# ip a

1: lo: mtu 16436 qdisc noqueue

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

inet 127.0.0.1/8 scope host lo

2: eth0: mtu 1500 qdisc pfifo\_fast qlen 1000

link/ether 00:0c:29:d4:fd:b3 brd ff:ff:ff:ff:ff:ff

inet 192.168.8.31/24 brd 192.168.8.255 scope global eth0

inet 192.168.8.30/32 scope global eth0

\[root@mysql1 keepalived\]\# service mysqld stop

Shutting down MySQL. \[ OK \]

\[root@mysql1 keepalived\]\# service keepalived status

keepalived is stopped

\[root@mysql1 keepalived\]\#

可以看到mysql服务停掉后，keepalived服务也马上停掉了!

再到32节点上查看下VIP有没有接管：

\[root@mysql2 ~\]\# ip a

1: lo: mtu 16436 qdisc noqueue

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

inet 127.0.0.1/8 scope host lo

2: eth0: mtu 1500 qdisc pfifo\_fast qlen 1000

link/ether 00:0c:29:e3:dd:5a brd ff:ff:ff:ff:ff:ff

inet 192.168.8.32/24 brd 192.168.8.255 scope global eth0

inet 192.168.8.30/32 scope global eth0

可以看到接管过来了！

linuxweb集群mysql_企业web高可用集群实战之lvs+keepalived+mysql

发表评论取消回复

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

相关阅读

相关 linuxweb集群mysql_企业web高可用集群实战之lvs+keepalived+mysql

相关 MySQL高可用方案之集群(Cluster)

相关 mysql高可用集群之MMM

相关高可用集群

相关集群之高可用集群

相关 MySQL高可用集群之MySQL-MMM

相关企业主流MySQL高可用集群

相关 MySQL集群高可用架构

相关 mysql高可用集群方案

相关 Haproxy+keepalived高可用集群实战