LVS-DR实现mysql负载均衡集群

lvs-dr实现mysql负载均衡集群

环境说明：

服务器的操作系统均为centos7,vip和rip在同一网段,使用lvs-dr模型来实现mysql集群服务

所有服务器均已配置好处VIP外的静态IP

两台mysql服务器的主机名分别是node1和node2,且已安装并启动好mysql

1. DR上配置VIP和转发规则

#配置VIP
[root@DR ~]# ip addr add 192.168.32.250/32 dev eth0



#配置ipvs转发规则
[root@DR ~]# yum -y install ipvsadm

#rr算法并不适合实际场景，这里仅作为测试
[root@DR ~]# ipvsadm -A -t 192.168.32.250:3306 -s rr
[root@DR ~]# ipvsadm -a -t 192.168.32.250:3306 -r 192.168.32.130:3306 -g
[root@DR ~]# ipvsadm -a -t 192.168.32.250:3306 -r 192.168.32.135:3306 -g
[root@DR ~]# ipvsadm -Sn
-A -t 192.168.32.250:3306 -s rr
-a -t 192.168.32.250:3306 -r 192.168.32.130:3306 -g -w 1
-a -t 192.168.32.250:3306 -r 192.168.32.135:3306 -g -w 1
#保存配置
[root@DR ~]# ipvsadm -Sn > /etc/sysconfig/ipvsadm

#如果误删，可以用保存的配置恢复
[root@DR ~]# ipvsadm -C
[root@DR ~]# ipvsadm -R < /etc/sysconfig/ipvsadm
[root@DR ~]# ipvsadm -Sn
-A -t 192.168.32.250:3306 -s rr
-a -t 192.168.32.250:3306 -r 192.168.32.130:3306 -g -w 1
-a -t 192.168.32.250:3306 -r 192.168.32.135:3306 -g -w 1

2. RS上配置arp内核参数和VIP

所有RS上都要做

[root@node1 ~]# vim /etc/sysctl.conf 
#添加以下两行
net.ipv4.conf.all.arp_ignore = 1
# 将对应网卡设置为只回应目标IP为自身接口地址的ARP请求
net.ipv4.conf.all.arp_announce = 2
# 将ARP请求的源IP设置为eth0上的IP，也就是RIP

[root@node1 ~]# sysctl -p
net.ipv4.conf.all.arp_ignore = 1
net.ipv4.conf.all.arp_announce = 2


[root@node1 ~]# ip addr add 192.168.32.250/32 dev lo
[root@node1 ~]# route add -host 192.168.32.250/32 dev lo

3. 配置数据库

两台数据库服务器上授权，并创建不同名字的数据库用于区分

#node1上登录MySQL
MariaDB [(none)]> grant all on *.* to 'root'@'192.168.%.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)


MariaDB [(none)]> create database node1;
Query OK, 1 row affected (0.00 sec)

#node2上登录MySQL
MariaDB [(none)]> grant all on *.* to 'root'@'192.168.%.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> create database node2;
Query OK, 1 row affected (0.00 sec)

4. 客户端访问测试

[root@localhost ~]# mysql -uroot -p123456 -h192.168.32.250 -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| node1              |
| performance_schema |
| test               |
+--------------------+
[root@localhost ~]# mysql -uroot -p123456 -h192.168.32.250 -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| node2              |
| performance_schema |
| test               |
+--------------------+
[root@localhost ~]# mysql -uroot -p123456 -h192.168.32.250 -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| node1              |
| performance_schema |
| test               |
+--------------------+
[root@localhost ~]# mysql -uroot -p123456 -h192.168.32.250 -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| node2              |
| performance_schema |
| test               |
+--------------------+



[root@localhost ~]# for i in `seq 10`;do mysql -uroot -p123456 -h192.168.32.250 -e 'show databases;'| grep node ; done
node2
node1
node2
node1
node2
node1
node2
node1
node2
node1