MySQL主从复制与lvs+keepalived单点写入读负载均衡高可用实验要点.docx
《MySQL主从复制与lvs+keepalived单点写入读负载均衡高可用实验要点.docx》由会员分享，可在线阅读，更多相关《MySQL主从复制与lvs+keepalived单点写入读负载均衡高可用实验要点.docx（12页珍藏版）》请在冰豆网上搜索。
MySQL主从复制与lvs+keepalived单点写入读负载均衡高可用实验要点
一、环境
Master（主机A）：
192.168.1.1
Slave（主机B）：
192.168.1.2
W-VIP（写入） ：
192.168.1.3
R-VIP（读取） ：
192.168.1.4
Client（测试）：
192.168.1.100
操作系统版本：
CentOSrelease6.4
MySQL数据库版本：
5.6.14
keepalived版本：
1.2.7
LVS版本：
1.26
所有环境均为虚拟机
二、设计思路
1.服务器A和B，通过mysql的slave进程同步数据。
2.通过keepalived启用两个虚IP：
W-VIP/R-VIP，一个负责写入，一个负责读取，实现读写分离。
3.A和B都存在时，W-VIP下将请求转发至主机A，R-VIP将请求转发给A和B，实现负载均衡。
4.当主机A异常时，B接管服务，W-VIP/R-VIP此时漂到了主机B上，此时这两个虚IP下都是主机B，实现高可用
5.当主机B异常时，R-VIP会将B踢出，其他不变
三、架构图
四、软件安装
主从两个主机都要装以下软件：
1.MySQL的安装（略）
2.keepalived安装
yuminstallkeepalived
2.LVS安装
yuminstallipvsadm
五、配置
1.配置MySQL的主从复制（略）
2.配置keepalived
Master上的配置
vi/etc/keepalived/keepalived.conf
[plain] viewplaincopy
1.!
Configuration File for keepalived
2.
3.global_defs {
4. router_id MySQL-ha
5.}
6.
7.vrrp_instance VI_1 {
8. state BACKUP
9. interface eth1
10. virtual_router_id 90
11. priority 100
12. advert_int 1
13. notify_master "/usr/local/mysql/bin/remove_slave.sh"
14. nopreempt
15. authentication {
16. auth_type PASS
17. auth_pass 1111
18. }
19. virtual_ipaddress {
20. 192.168.1.3 label eth1:
1
21. 192.168.1.4 label eth1:
2
22. }
23.}
24.
25.virtual_server 192.168.1.3 6603 {
26. delay_loop 2
27. lb_algo wrr
28. lb_kind DR
29. persistence_timeout 60
30. protocol TCP
31. real_server 192.168.1.1 6603 {
32. weight 3
33. notify_down /usr/local/mysql/bin/mysql.sh
34. TCP_CHECK {
35. connect_timeout 10
36. nb_get_retry 3
37. delay_before_retry 3
38. connect_port 6603
39. }
40. }
41.}
42.
43.virtual_server 192.168.1.4 6603 {
44. delay_loop 2
45. lb_algo wrr
46. lb_kind DR
47. persistence_timeout 60
48. protocol TCP
49. real_server 192.168.1.1 6603 {
50. weight 1
51. notify_down /usr/local/mysql/bin/mysql.sh
52. TCP_CHECK {
53. connect_timeout 10
54. nb_get_retry 3
55. delay_before_retry 3
56. connect_port 6603
57. }
58. }
59. real_server 192.168.1.2 6603 {
60. weight 3
61. TCP_CHECK {
62. connect_timeout 10
63. nb_get_retry 3
64. delay_before_retry 3
65. connect_port 6603
66. }
67. }
68.}
keepalived配置成服务并开机启动
[plain] viewplaincopy
1.cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/
2.cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
3.cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
4.chkconfig --add keepalived
5.chkconfig --level 345 keepalived on
vi/usr/local/mysql/bin/remove_slave.sh
[plain] viewplaincopy
1.#!
/bin/bash
2.user=u1
3.password=12345
4.log=/usr/local/mysql/log/remove_slave.log
5.echo "`date`" >> $log
6./usr/local/mysql/bin/mysql -u$user -p$password -e "set global read_only=OFF;reset master;stop slave;change master to master_host='localhost';" >> $log
7./bin/sed -i 's#read-only#\#read-only#' /etc/f
vi/usr/local/mysql/bin/mysql.sh
[plain] viewplaincopy
1.#!
/bin/bash
2./etc/init.d/keepalived stop
Slave上的配置
vi/etc/keepalived/keepalived.conf
[plain] viewplaincopy
1.!
Configuration File for keepalived
2.
3.global_defs {
4. router_id MySQL-ha
5.}
6.
7.vrrp_instance VI_1 {
8. state BACKUP
9. interface eth1
10. virtual_router_id 90
11. priority 99
12. advert_int 1
13. notify_master "/usr/local/mysql/bin/remove_slave.sh"
14. authentication {
15. auth_type PASS
16. auth_pass 1111
17. }
18. virtual_ipaddress {
19. 192.168.1.3 label eth1:
1
20. 192.168.1.4 label eth1:
2
21. }
22.}
23.
24.virtual_server 192.168.1.3 6603 {
25. delay_loop 2
26. lb_algo wrr
27. lb_kind DR
28. persistence_timeout 60
29. protocol TCP
30. real_server 192.168.1.2 6603 {
31. weight 3
32. notify_down /usr/local/mysql/bin/mysql.sh
33. TCP_CHECK {
34. connect_timeout 10
35. nb_get_retry 3
36. delay_before_retry 3
37. connect_port 6603
38. }
39. }
40.}
41.
42.virtual_server 192.168.1.4 6603 {
43. delay_loop 2
44. lb_algo wrr
45. lb_kind DR
46. persistence_timeout 60
47. protocol TCP
48. real_server 192.168.1.2 6603 {
49. weight 3
50. notify_down /usr/local/mysql/bin/mysql.sh
51. TCP_CHECK {
52. connect_timeout 10
53. nb_get_retry 3
54. delay_before_retry 3
55. connect_port 6603
56. }
57. }
58.}
keepalived配置成服务并开机启动
[plain] viewplaincopy
1.cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/
2.cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
3.cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
4.chkconfig --add keepalived
5.chkconfig --level 345 keepalived on
vi/usr/local/mysql/bin/remove_slave.sh
[plain] viewplaincopy
1.#!
/bin/bash
2.user=u1
3.password=12345
4.log=/usr/local/mysql/log/remove_slave.log
5.echo "`date`" >> $log
6./usr/local/mysql/bin/mysql -u$user -p$password -e "set global read_only=OFF;reset master;stop slave;change master to master_host='localhost';" >> $log
7./bin/sed -i 's#read-only#\#read-only#' /etc/f
vi/usr/local/mysql/bin/mysql.sh
[plain] viewplaincopy
1.#!
/bin/bash
2./etc/init.d/keepalived stop
3.配置LVS
Master与Slave上的配置相同：
vi/usr/local/bin/lvs_real.sh
[plain] viewplaincopy
1.#!
/bin/bash
2.# description:
Config realserver lo and apply noarp
3.
4.SNS_VIP=192.168.1.3
5.SNS_VIP2=192.168.1.4
6.source /etc/rc.d/init.d/functions
7.case "$1" in
8.
9.start）
10. ifconfig lo:
0 $SNS_VIP netmask 255.255.255.255 broadcast $SNS_VIP
11. ifconfig lo:
1 $SNS_VIP2 netmask 255.255.255.255 broadcast $SNS_VIP2
12. /sbin/route add -host $SNS_VIP dev lo:
0
13. /sbin/route add -host $SNS_VIP2 dev lo:
1
14. echo "1" >/proc/sys/net/ipv4/conf/lo/arp_ignore
15. echo "2" >/proc/sys/net/ipv4/conf/lo/arp_announce
16. echo "1" >/proc/sys/net/ipv4/conf/all/arp_ignore
17. echo "2" >/proc/sys/net/ipv4/conf/all/arp_announce
18. sysctl -p >/dev/null 2>&1
19. echo "RealServer Start OK"
20. ;;
21.
22.stop）
23. ifconfig lo:
0 down
24. ifconfig lo:
1 down
25. route del $SNS_VIP >/dev/null 2>&1
26. route del $SNS_VIP2 >/dev/null 2>&1
27. echo "0" >/proc/sys/net/ipv4/conf/lo/arp_ignore
28. echo "0" >/proc/sys/net/ipv4/conf/lo/arp_announce
29. echo "0" >/proc/sys/net/ipv4/conf/all/arp_ignore
30. echo "0" >/proc/sys/net/ipv4/conf/all/arp_announce
31. echo "RealServer Stoped"
32. ;;
33.
34.*）
35.
36. echo "Usage:
$0 {start|stop}"
37. exit 1
38.esac
39.exit 0
[plain] viewplaincopy
1.chmod 755 /usr/local/bin/lvs_real.sh
2.echo "/usr/local/bin/lvs_real.sh start" >> /etc/rc.local
五、Master和Slave的启动
1.启动Master上的MySQL
servicemysqlstart
2.启动Slave上的MySQL
servicemysqlstart
3.启动Master上的realserver脚本
/usr/local/bin/lvs_real.shstart
4.启动Slave上的realserver脚本
/usr/local/bin/lvs_real.shstart
5.启动Master上的keepalived
servicekeepalivedstart
6.启动Slave上的keepalived
servicekeepalivedstart
六、测试
1.查看lvs能否进行负载均衡转发
在Master和Slave上分别执行：
ipvsadm-ln
2.在Client上验证连通性：
ping192.168.1.3
ping192.168.1.4
mysql-uu1-p12345-P6603-h192.168.1.3-e"showvariableslike'server_id'"
mysql-uu1-p12345-P6603-h192.168.1.4-e"showvariableslike'server_id'"
3.停掉Master上的MySQL，看写IP否自动切换到Slave，看读IP是否去掉了Master的MySQL
在Master和Slave上分别执行：
ipvsadm-ln
在Client上执行：
mysql-uu1-p12345-P6603-h192.168.1.3-e"showvariableslike'server_id'"
mysql-uu1-p12345-P6603-h192.168.1.4-e"showvariableslike'server_id'"
4.停掉Master上的keepalived，看读写VIP是否会迁移到Slave上。
在Master和Slave上分别执行：
ipvsadm-ln
在Client上执行：
mysql-uu1-p12345-P6603-h192.168.1.3-e"showvariableslike'server_id'"
mysql-uu1-p12345-P6603-h192.168.1.4-e"showvariableslike'server_id'"
5.停掉Slave上的MySQL，看读IP是否去掉了Slave的MySQL
在Master和Slave上分别执行：
ipvsadm-ln
在Client上执行：
mysql-uu1-p12345-P6603-h192.168.1.3-e"showvariableslike'server_id'"
mysql-uu1-p12345-P6603-h192.168.1.4-e"showvariableslike'server_id'"
6.重启Master的系统，看看切换过程是否正常
升级会员 