mysql lvs ha_企业web高可用集群实战之lvs+keepalived+mysql HA

前端用两台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.安装keepalivedcd /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;

+---------------+----------+--------------+------------------+

| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+---------------+----------+--------------+------------------+

| 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;

+---------------+----------+--------------+------------------+

| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+---------------+----------+--------------+------------------+

| 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

fi

return$MYSQL_OK

}

while[ $CHECK_TIME -ne 0 ]

do

let "CHECK_TIME -= 1"

check_mysql_helth

if[ $MYSQL_OK = 0 ] ; then

CHECK_TIME=0

exit 0

fi

if[ $MYSQL_OK -eq 1 ] && [ $CHECK_TIME -eq 1 ]

then

/etc/init.d/keepalived stop

exit 1

fi

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

可以看到接管过来了！

四、存储部分

请参考笔者的相关文章：