双机高可用、负载均衡、MySQL(读写分离、主从自动切换)架构设计

1、双机高可用、负载均衡、MySQL(读写分离、主从自动切换)架构设计原文出处： 朱茂海   架构简介前几天网友来信说帮忙实现这样一个架构：只有两台机器，需要实现其中一台死机之后另一台能接管这台机器的服务，并且在两台机器正常服务时，两台机器都能用上。于是设计了如下的架构。此架构主要是由keepalived实现双机高可用，维护了一个外网VIP,一个内网VIP。正常情况时，外网VIP和内网VIP都绑定在server1服务器，web请求发送到server1的nginx，nginx对于静态资源请求就直接在本机检索并返回，对于php的动态请求，则负载均衡到server

2、1和server2。对于SQL请求，会将此类请求发送到Atlas MySQL中间件，Atlas接收到请求之后，把涉及写操作的请求发送到内网VIP，读请求操作发送到mysql从，这样就实现了读写分离。当主服务器server1宕机时，keepalived检测到后，立即把外网VIP和内网VIP绑定到server2，并把server2的mysql切换成主库。此时由于外网VIP已经转移到了server2，web请求将发送给server2的nginx。nginx检测到server1宕机，不再把请求转发到server1的php-fpm。之后的sql请求照常发送给本地的atlas，atlas把写操作发送给内网

3、VIP，读操作发送给mysql从，由于内网VIP已经绑定到server2了，server2的mysql同时接受写操作和读操作。当主服务器server1恢复后，server1的mysql自动设置为从，与server2的mysql主同步。keepalived不抢占server2的VIP，继续正常服务。架构要求 要实现此架构，需要三个条件：· 1、服务器可以设置内网IP，并且设置的内网IP互通;· 2、服务器可以随意绑定IDC分配给我们使用的外网IP，即外网IP没有绑定MAC地址;· 3、MySQL服务器支持GTID，即MySQL-5.6.5以上版本。环境说明

4、server1· eth0: 10(对外IP)· eth1: 00(对内IP)server2· eth0: 14(对外IP)· eth1: 01(对内IP)系统都是CentOS-6。对外VIP: 39对内VIP: 50hosts设置/etc/hosts:00 server101 server2Nginx PHP MySQL Memcached安装这几个软件的安装

5、推荐使用EZHTTP来完成。解决session共享问题php默认的session存储是在/tmp目录下，现在我们是用两台服务器作php请求的负载，这样会造成session分布在两台服务器的/tmp目录下，导致依赖于session的功能不正常。我们可以使用memcached来解决此问题。上一步我们已经安装好了memcached，现在只需要配置php.ini来使用memcached，配置如下，打开php.ini配置文件，修改为如下两行的值： 12session.save_handler = memcachesession.save_path = "tcp:/00:1

6、1211,tcp:/01:11211"之后重启php-fpm生效。Nginx配置Server1配置1234567891011121314151617181920http .    upstream php-server            server 01:9000;           server

7、 :9000;           keepalive 100;    . server     .        location .php$               

8、0;         fastcgi_pass   php-server;                        fastcgi_index  index.php;     

9、;                   fastcgi_param  SCRIPT_FILENAME  $document_root$fastcgi_script_name;                &#

10、160;       include        fastcgi_params;            . .Server2配置1234567891011121314151617181920http .    upstream php-server    &

11、#160;       server 00:9000;           server :9000;           keepalive 100;    . server     .  

12、0;     location .php$                         fastcgi_pass   php-server;            &

13、#160;           fastcgi_index  index.php;                        fastcgi_param  SCRIPT_FILENAME 

14、0;$document_root$fastcgi_script_name;                        include        fastcgi_params;        &

15、#160;   . .这两个配置主要的作用是设置php请求的负载均衡。MySQL配置mysql util安装我们需要安装mysql util里的主从配置工具来实现主从切换。123456cd /tmpwget tar xzf mysql-utilities-1.5.3.tar.gzcd mysql-utilities-1.5.3python setup.py buildpython setup.py installmysql f配置server1: 1234567891011121314151617181920tocol=tcp.mysqld.#

16、BINARY LOGGING #log-bin = /usr/local/mysql/data/mysql-binexpire-logs-days = 14binlog-format= rowlog-slave-updates=truegtid-mode=onenforce-gtid-consistency =truemaster-info-repository=TABLErelay-log-info-repository=TABLEserver-id=1report-host=server1report-port=3306.server2: 1234567891011121314151617

17、1819tocol=tcp.mysqld.# BINARY LOGGING #log-bin = /usr/local/mysql/data/mysql-binexpire-logs-days = 14binlog-format= rowlog-slave-updates=truegtid-mode=onenforce-gtid-consistency =truemaster-info-repository=TABLErelay-log-info-repository=TABLEserver-id=2report-host=server2report-port=3306.这两

18、个配置主要是设置了binlog和启用gtid-mode，并且需要设置不同的server-id和report-host。开放root帐号远程权限我们需要在两台mysql服务器设置root帐号远程访问权限。1234mysql> grant all on *.* to 'root''192.168.1.%' identified by 'Xp29at5F37' with grant option;mysql> grant all on *.* to 'root''server1' identified by

19、'Xp29at5F37' with grant option;mysql> grant all on *.* to 'root''server2' identified by 'Xp29at5F37' with grant option;mysql> flush privileges;设置mysql主从在任意一台执行如下命令： 1mysqlreplicate -master=root:Xp29at5F37server1:3306 -slave=root:Xp29at5F37server2:3306 -rpl-user=

20、rpl:o67DhtaW# master on server1: connected.# slave on server2: connected.# Checking for binary logging on master# Setting up replication# done.显示主从关系1mysqlrplshow -master=root:Xp29at5F37server1 -discover-slaves-login=root:Xp29at5F37# master on server1: connected.# Finding slaves for master: server1:

21、3306# Replication Topology Graphserver1:3306 (MASTER)|+ server2:3306 (SLAVE)检查主从状态1mysqlrplcheck -master=root:Xp29at5F37server1 -slave=root:Xp29at5F37server2# master on server1: connected.# slave on server2: connected.Test Description StatusChecking for binary logging on master passAre there binlog

22、exceptions? passReplication user exists? passChecking server_id values passChecking server_uuid values passIs slave connected to master? passCheck master information file passChecking InnoDB compatibility passChecking storage engines compatibility passChecking lower_case_table_names settings passChe

23、cking slave delay (seconds behind master) pass# done.Keepalived配置keepalived安装(两台都装)1. yum -y install keepalived2. chkconfig keepalived onkeepalived配置(server1)vi /etc/keepalived/keepalived.conf123456789101112131415161718192021222324252627282930313233343536373839vrrp_sync_group VG_1 group inside_netwo

24、rkoutside_network vrrp_instance inside_network state BACKUPinterface eth1virtual_router_id 51priority 101advert_int 1authentication auth_type PASSauth_pass 3489virtual_ipaddress 50/24nopreemptnotify /data/sh/mysqlfailover-server1.sh vrrp_instance outside_network state BACKUPinterface eth0

25、virtual_router_id 50priority 101advert_int 1authentication auth_type PASSauth_pass 3489virtual_ipaddress 39/24nopreemptkeepalived配置(server2) 12345678910111213141516171819202122232425262728293031323334353637rrp_sync_group VG_1 group inside_networkoutside_network vrrp_instance inside_networ

26、k state BACKUPinterface eth1virtual_router_id 51priority 100advert_int 1authentication auth_type PASSauth_pass 3489virtual_ipaddress 50notify /data/sh/mysqlfailover-server2.sh vrrp_instance outside_network state BACKUPinterface eth0virtual_router_id 50priority 100advert_int 1authenticatio

27、n auth_type PASSauth_pass 3489virtual_ipaddress 39/24此keepalived配置需要注意的是：· 1、两台server的state都设置为backup，server1增加nopreempt配置，并且server1 priority比server2高，这样用来实现当server1从宕机恢复时，不抢占VIP;· 2、server1设置notify /data/sh/mysqlfailover-server1.sh,server2设置notify /data/sh/mysqlfailover-server2

28、.sh,作用是自动切换主从/data/sh/mysqlfailover-server1.sh脚本内容： 1234567891011121314151617181920#!/bin/bash sleep 10state=$3result=mysql -h -P3306 -uroot -pXp29at5F37 -e 'show slave status;' "$result" = "" && mysqlState="master" | mysqlState="slave"

29、; if "$state" = "MASTER" ;then  if "$mysqlState" = "slave" ;then    mysqlrpladmin -slave=root:Xp29at5F37server1:3306 failover  fi elif "$state" = "BACKUP" ;then  if "$mysqlState" =

30、 "master" ;then    mysqlreplicate -master=root:Xp29at5F37server2:3306 -slave=root:Xp29at5F37server1:3306 -rpl-user=rpl:o67DhtaW  fifi sed -i 's/proxy-read-only-backend-addresses.*/proxy-read-only-backend-addresses = 50:3306/' /usr/local/mysq

31、l-proxy/conf/fmysql -h -P2345 -uuser -ppwd -e "REMOVE BACKEND 2;"/data/sh/mysqlfailover-server2.sh脚本内容： 1234567891011121314151617181920#!/bin/bash sleep 10state=$3result=mysql -h -P3306 -uroot -pXp29at5F37 -e 'show slave status;' "$result" = ""

32、 && mysqlState="master" | mysqlState="slave" if "$state" = "MASTER" ;then  if "$mysqlState" = "slave" ;then    mysqlrpladmin -slave=root:Xp29at5F37server2:3306 failover  fi elif "$state&q

33、uot; = "BACKUP" ;then  if "$mysqlState" = "master" ;then    mysqlreplicate -master=root:Xp29at5F37server1:3306 -slave=root:Xp29at5F37server2:3306 -rpl-user=rpl:o67DhtaW  fifi sed -i 's/proxy-read-only-backend-addresses.*/proxy

34、-read-only-backend-addresses = 50:3306/' /usr/local/mysql-proxy/conf/fmysql -h -P2345 -uuser -ppwd -e "REMOVE BACKEND 2;"Atlas设置atlas安装到这里下载最新版本，123cd /tmpwget rpm -i Atlas-2.2.1.el6.x86_64.rpmatlas配置123cd /usr/local/mysql-proxy/confcp f fvi f调整如下参数， 1234proxy-backe

35、nd-addresses = 50:3306proxy-read-only-backend-addresses = 01:3306pwds = root:qtyU1btXOo074Itvx0UR9Q=event-threads = 8注意：proxy-backend-addresse设置为内网VIPproxy-read-only-backend-addresses设置为server2的IProot:qtyU1btXOo074Itvx0UR9Q=设置数据库的用户和密码，密码是通过/usr/local/mysql-proxy/bin/encrypt Xp

36、29at5F37生成。更详细参数解释请查看，Atlas配置详解。启动atlas1. /usr/local/mysql-proxy/bin/mysql-proxy defaults-file=/usr/local/mysql-proxy/conf/f之后程序里配置mysql就配置:1234就好。部署atlas自动维护脚本在两台机器都部署此脚本，并添加定时任务（如每2分钟运行一次）我们把脚本放在/data/sh/auto_maintain_atlas.sh,脚本内容为： 123456789101112131415161718192021#!/bin/bash count=mysq

37、l -N -h -P2345 -uuser -ppwd -e "select * from backends;" | wc -l if "$count" = "1" ;then  result=mysql -hserver1 -P3306 -uroot -pXp29at5F37 -e 'show slave statusG'  if echo "$result" | grep Slave_IO_State;then  &#

38、160; slaveIP=00  else    result=mysql -hserver2 -P3306 -uroot -pXp29at5F37 -e 'show slave statusG'    slaveIP=01  fi         slaveIORunning=echo "$result"

39、| awk -F':' '/Slave_IO_Running:/print $2'        slaveSQLRunning=echo "$result" | awk -F':' '/Slave_SQL_Running:/print $2'        SlaveSQLRunning_State=echo "$result" |

40、 awk -F':' '/Slave_SQL_Running_State:/print $2'      if "$slaveIORunning" = "Yes" && "$slaveSQLRunning" = "Yes" && "$SlaveSQLRunning_State" = "Slave has read all relay log" ;then

41、    mysql -h -P2345 -uuser -ppwd -e "add slave $slaveIP:3306;"  fifi为什么需要这个脚本呢？假设目前mysql主服务器在s1,s1宕机后，s2接管VIP，接着删除atlas中设置的slave backend，其mysql提升为主。过一段时间后，s1从宕机中恢复，这时候s1的mysql自动切换为从，接着删除atlas中设置的slave backend，开始连接s2的mysql主同步数据。到这个时候我们发现，已经不存在读写分离了，所有的

42、sql都发送给了s2的mysql。auto_maintain_atlas.sh脚本就派上用场了，此脚本会定时的检查主从是否已经同步完成，如果完成就自动增加slave backend，这样读写分离又恢复了，完全不需要人工干预。server1主宕机测试测试keepalived是否工作正常我们来模拟server1宕机。在server1上执行shutdown关机命令。此时我们登录server2，执行ip addr命令，输出如下：1: lo: mtu 16436 qdisc noqueue state UNKNOWNlink/loopback 00:00:00:00:00:00 brd 00:00:00

43、:00:00:00inet /8 scope host loinet6 :1/128 scope hostvalid_lft forever preferred_lft forever2: eth0: mtu 1500 qdisc pfifo_fast state UP qlen 1000link/ether 00:0c:29:81:9d:42 brd ff:ff:ff:ff:ff:ffinet 14/24 brd 55 scope global eth0inet 39/24 scope global seco

44、ndary eth0inet6 fe80:20c:29ff:fe81:9d42/64 scope linkvalid_lft forever preferred_lft forever3: eth1: mtu 1500 qdisc pfifo_fast state UP qlen 1000link/ether 00:0c:29:81:9d:4c brd ff:ff:ff:ff:ff:ffinet 01/24 brd 55 scope global eth1inet 50/32 scope global eth1inet6 fe8

45、0:20c:29ff:fe81:9d4c/64 scope linkvalid_lft forever preferred_lft forever我们看到对外VIP 39和对内IP 50已经转移到server2了，证明keepalived运行正常。测试是否自动切换了主从登录server2的mysql服务器，执行show slave status;命令，如下：mysql> show slave statusGEmpty set (0.00 sec)我们发现从状态已经为空，证明已经切换为主了。测试server1是否抢占VIP为什么要测试这个呢？如果

46、server1恢复之后抢占了VIP，而我们的Atlas里后端设置的是VIP，这样server1启动之后，sql的写操作就会向server1的mysql发送，而server1的mysql数据是旧于server2的，所以这样会造成数据不一致，这个是非常重要的测试。我们先来启动server1，之后执行ip addr，输出如下：1: lo: mtu 16436 qdisc noqueue state UNKNOWNlink/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00inet /8 scope host loinet6 :1/128

47、 scope hostvalid_lft forever preferred_lft forever2: eth0: mtu 1500 qdisc pfifo_fast state UP qlen 1000link/ether 00:0c:29:f1:4f:4e brd ff:ff:ff:ff:ff:ffinet 10/24 brd 55 scope global eth0inet6 fe80:20c:29ff:fef1:4f4e/64 scope linkvalid_lft forever preferred_lft forever3: eth1: mtu 1500 qdisc pfifo_fast state UP qlen 1000link/ether 00:0c:29:f1:4f:58 brd ff:ff:ff:ff:ff:ffinet 00/24 brd 55 scope global eth1inet6 fe80:20c:29ff:fef1:4f58/64 scope linkvalid_lft forever preferred_lft forever我们看到，server1并没有抢占V