MySQL master-slave replication with lvs+keepalived single-point write read load balancer High availability experiment "go"

First, the environment
Master (Host a): 192.168.1.1
Slave (Host B): 192.168.1.2
W-VIP (write): 192.168.1.3
R-VIP (Read): 192.168.1.4
Client (test): 192.168.1.100

Operating system version: CentOS release 6.4
MySQL database version: 5.6.14
Keepalived version: 1.2.7
LVS Version: 1.26

All environments are virtual machines

Second, design ideas

1. Server A and B, synchronize the data through the MySQL slave process.
2. Enable two virtual Ip:w-vip/r-vip via keepalived, one responsible for writing, one responsible for reading, and the realization of read and write separation.
3. When both A and B are present, W-VIP forwards the request to the host A,R-VIP and forwards the request to A and B for load balancing.
4. When host A is abnormal, B takes over the service, W-VIP/R-VIP at this time drift to host B, at this time the two virtual IP is Host B, to achieve high availability
5. When Host B is abnormal, R-VIP will kick the B out and the other unchanged

Third, the structure diagram

Iv. Software Installation
The following software is installed on both master and slave hosts:
1. mysql installation (slightly)
2. keepalived Installation
Yum Install keepalived
2. LVS Installation
Yum Install Ipvsadm

Five, configuration
1. Configure master-slave replication for MySQL (slightly)
2. Configure keepalived
The configuration on Master
Vi/etc/keepalived/keepalived.conf

[Plain]View Plaincopy

2. ! Configuration File for Keepalived
6. Global_defs {
8. router_id mysql-ha
10. }
14. Vrrp_instance Vi_1 {
16. State BACKUP
18. Interface eth1
20. VIRTUAL_ROUTER_ID 90
22. Priority 100
24. Advert_int 1
26. Notify_master "/usr/local/mysql/bin/remove_slave.sh"
28. Nopreempt
30. Authentication {
32. Auth_type PASS
34. Auth_pass 1111
36. }
38. virtual_ipaddress {
40. 192.168.1.3 Label Eth1:1
42. 192.168.1.4 label Eth1:2
44. }
46. }
50. Virtual_server 192.168.1.3 6603 {
52. Delay_loop 2
54. Lb_algo WRR
56. Lb_kind DR
58. Persistence_timeout 60
60. Protocol TCP
62. Real_server 192.168.1.1 6603 {
64. Weight 3
66. notify_down/usr/local/mysql/bin/mysql.sh
68. Tcp_check {
70. Connect_timeout 10
72. Nb_get_retry 3
74. Delay_before_retry 3
76. Connect_port 6603
78. }
80. }
82. }
86. Virtual_server 192.168.1.4 6603 {
88. Delay_loop 2
90. Lb_algo WRR
92. Lb_kind DR
94. Persistence_timeout 60
96. Protocol TCP
98. Real_server 192.168.1.1 6603 {
100. Weight 1
102. notify_down/usr/local/mysql/bin/mysql.sh
104. Tcp_check {
106. Connect_timeout 10
108. Nb_get_retry 3
110. Delay_before_retry 3
112. Connect_port 6603
114. }
116. }
118. Real_server 192.168.1.2 6603 {
120. Weight 3
122. Tcp_check {
124. Connect_timeout 10
126. Nb_get_retry 3
128. Delay_before_retry 3
130. Connect_port 6603
132. }
134. }
136. }

Keepalived configured as a service and boot up

[Plain]View Plaincopy

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]View Plaincopy

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 Mast er_host= ' localhost '; ">> $log
7. /bin/sed-i ' s#read-only#\ #read-only# '/etc/my.cnf

vi/usr/local/mysql/bin/mysql.sh

[Plain]View Plaincopy

1. #!/bin/bash
2. /etc/init.d/keepalived stop

Configuration on the slave
Vi/etc/keepalived/keepalived.conf

[Plain]View Plaincopy

2. ! Configuration File for Keepalived
6. Global_defs {
8. router_id mysql-ha
10. }
14. Vrrp_instance Vi_1 {
16. State BACKUP
18. Interface eth1
20. VIRTUAL_ROUTER_ID 90
22. Priority 99
24. Advert_int 1
26. Notify_master "/usr/local/mysql/bin/remove_slave.sh"
28. Authentication {
30. Auth_type PASS
32. Auth_pass 1111
34. }
36. virtual_ipaddress {
38. 192.168.1.3 Label Eth1:1
40. 192.168.1.4 label Eth1:2
42. }
44. }
48. Virtual_server 192.168.1.3 6603 {
50. Delay_loop 2
52. Lb_algo WRR
54. Lb_kind DR
56. Persistence_timeout 60
58. Protocol TCP
60. Real_server 192.168.1.2 6603 {
62. Weight 3
64. notify_down/usr/local/mysql/bin/mysql.sh
66. Tcp_check {
68. Connect_timeout 10
70. Nb_get_retry 3
72. Delay_before_retry 3
74. Connect_port 6603
76. }
78. }
80. }
84. Virtual_server 192.168.1.4 6603 {
86. Delay_loop 2
88. Lb_algo WRR
90. Lb_kind DR
92. Persistence_timeout 60
94. Protocol TCP
96. Real_server 192.168.1.2 6603 {
98. Weight 3
100. notify_down/usr/local/mysql/bin/mysql.sh
102. Tcp_check {
104. Connect_timeout 10
106. Nb_get_retry 3
108. Delay_before_retry 3
110. Connect_port 6603
112. }
114. }
116. }

Keepalived configured as a service and boot up

[Plain]View Plaincopy

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]View Plaincopy

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 Mast er_host= ' localhost '; ">> $log
7. /bin/sed-i ' s#read-only#\ #read-only# '/etc/my.cnf

vi/usr/local/mysql/bin/mysql.sh

[Plain]View Plaincopy

1. #!/bin/bash
2. /etc/init.d/keepalived stop

3. Configuring LVS
Master is the same as the configuration on slave:
vi/usr/local/bin/lvs_real.sh

[Plain]View Plaincopy

2. #!/bin/bash
4. # Description:config Realserver lo and apply Noarp
8. sns_vip=192.168.1.3
10. sns_vip2=192.168.1.4
12. Source/etc/rc.d/init.d/functions
14. Case "$" in
18. Start
20. Ifconfig lo:0 $SNS _vip netmask 255.255.255.255 broadcast $SNS _VIP
22. Ifconfig lo:1 $SNS _vip2 netmask 255.255.255.255 broadcast $SNS _VIP2
24. /sbin/route add-host $SNS _vip Dev lo:0
26. /sbin/route add-host $SNS _vip2 Dev lo:1
28. echo "1" >/proc/sys/net/ipv4/conf/lo/arp_ignore
30. echo "2" >/proc/sys/net/ipv4/conf/lo/arp_announce
32. echo "1" >/proc/sys/net/ipv4/conf/all/arp_ignore
34. echo "2" >/proc/sys/net/ipv4/conf/all/arp_announce
36. Sysctl-p >/dev/null 2>&1
38. echo "Realserver Start OK"
40. ;;
44. Stop
46. Ifconfig lo:0 Down
48. Ifconfig lo:1 Down
50. Route del $SNS _VIP >/dev/null 2>&1
52. Route del $SNS _vip2 >/dev/null 2>&1
54. echo "0" >/proc/sys/net/ipv4/conf/lo/arp_ignore
56. echo "0" >/proc/sys/net/ipv4/conf/lo/arp_announce
58. echo "0" >/proc/sys/net/ipv4/conf/all/arp_ignore
60. echo "0" >/proc/sys/net/ipv4/conf/all/arp_announce
62. echo "Realserver stoped"
64. ;;
68. *)
72. echo "Usage: $ {start|stop}"
74. Exit 1
76. Esac
78. Exit 0

[Plain]View Plaincopy

1. chmod 755/usr/local/bin/lvs_real.sh
2. echo "/usr/local/bin/lvs_real.sh start" >>/etc/rc.local

V. Start of Master and slave
1. Start MySQL on master
Service MySQL Start
2. Start MySQL on the slave
Service MySQL Start
3. Start the Realserver script on master
/usr/local/bin/lvs_real.sh start
4. Start the Realserver script on the slave
/usr/local/bin/lvs_real.sh start
5. Start the keepalived on master
Service keepalived Start
6. Start the keepalived on the slave
Service keepalived Start


VI. Testing
1. See if LVS can load-balance the forwarding
Execute separately on master and slave:
Ipvsadm-ln

2. Verify connectivity on the client:
Ping 192.168.1.3
Ping 192.168.1.4
Mysql-u u1-p12345-p 6603-h 192.168.1.3-e "show variables like ' server_id '"
Mysql-u u1-p12345-p 6603-h 192.168.1.4-e "show variables like ' server_id '"

3. Stop MySQL on master, read IP no automatically switch to slave, see if IP is removed from master MySQL
Execute separately on master and slave:
Ipvsadm-ln
Execute on the client:
Mysql-u u1-p12345-p 6603-h 192.168.1.3-e "show variables like ' server_id '"
Mysql-u u1-p12345-p 6603-h 192.168.1.4-e "show variables like ' server_id '"

4. Stop keepalived on master to see if the read-write VIP will migrate to slave.
Execute separately on master and slave:
Ipvsadm-ln
Execute on the client:
Mysql-u u1-p12345-p 6603-h 192.168.1.3-e "show variables like ' server_id '"
Mysql-u u1-p12345-p 6603-h 192.168.1.4-e "show variables like ' server_id '"

5. Stop MySQL on slave and read if IP is removed from slave mysql
Execute separately on master and slave:
Ipvsadm-ln
Execute on the client:
Mysql-u u1-p12345-p 6603-h 192.168.1.3-e "show variables like ' server_id '"
Mysql-u u1-p12345-p 6603-h 192.168.1.4-e "show variables like ' server_id '"

6. Restart the master System to see if the switching process is normal

Vii. Reference
Http://wenku.baidu.com/link?url=17K0UDApzfK18A8F5Of9wl0lmGx6AD_ 0pq8gyvqmcsbmsz16xjt2ji88lpaeid2qecs7piuxkzqlyatttduejxwzey6joibfbmt3vgtfdus
Http://blog.chinaunix.net/uid-23500957-id-3781918.html
Http://blog.chinaunix.net/uid-23500957-id-3781919.html
Http://blog.chinaunix.net/uid-20639775-id-3337471.html

MySQL master-slave replication with lvs+keepalived single-point write read load balancer High availability experiment "go"