MySQL集群与LVS负载均衡配置实战-CSDN博客

本文链接：https://blog.csdn.net/weixin_50301100/article/details/110006206

Mysql集群配置

LVS–DR模式配置

环境信息

主机	ip	角色
VM1	192.168.163.128	LVS
VM2	192.168.163.134	RS1
VM3	192.168.163.137	RS2

mysql环境部署

安装mysql
分别创建数据库

mysql> create database test1;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
+--------------------+
5 rows in set (0.00 sec)

mysql> create database test2;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test2              |
+--------------------+
5 rows in set (0.00 sec)

lvs配置

分别修改VM2.VM3参数

[root@localhost]#vim /etc/sysctl.conf 
......
# For more information, see sysctl.conf(5) and sysctl.d(5).
net.ipv4.conf.all.arp_ignore = 1
net.ipv4.conf.all.arp_announce = 2
[root@localhost # sysctl -p
net.ipv4.conf.all.arp_ignore = 1
net.ipv4.conf.all.arp_announce = 2

配置IP

## RS1
[root@localhost conf.d]# vim /etc/sysconfig/network-scripts/ifcfg-ens160 
TYPE=Ethernet
BOOTPROTO=static
NAME=ens160
DEVICE=ens160
ONBOOT=yes

IPADDR0=192.168.163.134
NETMASK0=255.255.255.0
GATEWAY0=192.168.163.1

IPADDR1=192.168.0.250           
NETMASK1=255.255.255.0

## RS2
[root@localhost conf.d]# vim /etc/sysconfig/network-scripts/ifcfg-ens160 
TYPE=Ethernet
BOOTPROTO=static
NAME=ens160
DEVICE=ens160
ONBOOT=yes

IPADDR0=192.168.163.137
NETMASK0=255.255.255.0
GATEWAY0=192.168.163.1

IPADDR1=192.168.0.250
NETMASK1=255.255.255.0

调度器上配置ipvsadm

[root@localhost ~]# ipvsadm -A -t 192.168.0.250:3306 -s rr

[root@localhost ~]# ipvsadm -a -t 192.168.0.250:3306 -r 192.168.163.134:3306 -g
[root@localhost ~]# ipvsadm -a -t 192.168.0.250:3306 -r 192.168.163.137:3306 -g
[root@localhost ~]# ipvsadm -ln
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
TCP  192.168.0.250:3306 rr
  -> 192.168.0.134:3306         Route   1      0          0         
  -> 192.168.0.137:3306         Route   1      0          0  
[root@localhost ~]# ipvsadm -Sn > /etc/sysconfig/ipv

验证

## RS1创建表
mysql> create table student(id int primary key not null,name varchar(100) not null);
Query OK, 0 rows affected (0.75 sec)

mysql> desc student;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   | PRI | NULL    |       |
| name  | varchar(100) | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.04 sec)

## RS2查看表
mysql> show tables;
+------------------+
| Tables_in_TANG |
+------------------+
| student          |
+------------------+
1 row in set (0.01 sec

LVS–NAT模式配置

环境信息

主机	ip	角色
VM1	192.168.163.128	LVS
VM2	192.168.163.134	RS1
VM3	192.168.163.137	RS2

测试真机(192.168.0.102)
VIP(192.168.0.250)

mysql环境部署

分别创建数据库

mysql> create database test1;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
+--------------------+
5 rows in set (0.00 sec)

mysql> create database test2;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test2              |
+--------------------+
5 rows in set (0.00 sec)

LVS配置

VM2,VM3设为默认值

[root@localhost ~]# sysctl -p /etc/sysctl.conf 
net.ipv4.conf.all.arp_ignore = 0
net.ipv4.conf.all.arp_announce = 0

配置IP

#VM1
[root@vm1 ~]# vim /etc/sysconfig/network-scripts/ifcfg-ens160
TYPE=Ethernet
BOOTPROTO=static
IPADDR=192.168.163.128
NETMASK=255.255.225.0
GATEWAY=192.168.163.1
DNS1=192.168.163.1
NAME=ens160
DEVICE=ens160
ONBOOT=yes

[root@vm1 ~]# vim /etc/sysconfig/network-scripts/ifcfg-ens161
TYPE=Ethernet
BOOTPROTO=static
IPADDR=192.168.0.250
NETMASK=255.255.225.0
NAME=ens161
DEVICE=ens161
ONBOOT=yes


#VM2
[root@vm2 ~]# vim /etc/sysconfig/network-scripts/ifcfg-ens160
TYPE=Ethernet
BOOTPROTO=static
IPADDR=192.168.163.134
NETMASK=255.255.225.0
GATEWAY=192.168.163.128
DNS1=192.168.163.1
NAME=ens160
DEVICE=ens160
ONBOOT=yes
[root@vm2 ~]# route -n 
Kernel IP routing table
Destination     Gateway         Genmask         Flags Metric Ref    Use Iface
0.0.0.0         192.168.163.128 0.0.0.0         UG    100    0        0 ens160
192.168.163.0   0.0.0.0         255.255.255.0   U     100    0        0 ens160


#VM3
[root@vm3 ~]# vim /etc/sysconfig/network-scripts/ifcfg-ens160
TYPE=Ethernet
BOOTPROTO=static
IPADDR=192.168.163.137
NETMASK=255.255.225.0
GATEWAY=192.168.163.128
DNS1=192.168.163.1
NAME=ens160
DEVICE=ens160
ONBOOT=yes
[root@vm3 ~]# route -n 
Kernel IP routing table
Destination     Gateway         Genmask         Flags Metric Ref    Use Iface
0.0.0.0         192.168.163.128 0.0.0.0         UG    100    0        0 ens160
192.168.163.0   0.0.0.0         255.255.255.0   U     100    0        0 ens160

开启DS调度器IP转发功能

[root@vm1~]# vim /etc/sysctl.conf
net.ipv4.ip_forward = 1
[root@vm1 ~]# sysctl -p
net.ipv4.ip_forward = 1

配置LVS规则

#添加规则
[root@vm1 ~]# ipvsadm -A -t 192.168.0.250:3306 -s wrr
[root@vm1 ~]# ipvsadm -a -t 192.168.0.250:3306 -r 192.168.163.134:3306 -m
[root@vm1 ~]# ipvsadm -a -t 192.168.0.250:3306 -r 192.168.163.137:3306 -m
[root@vm1 ~]# ipvsadm -ln
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
TCP  192.168.0.250:3306 wrr
  -> 192.168.163.134:3306         Masq    1      0          0         
  -> 192.168.163.137:3306         Masq    1      0          0    
[root@vm1 ~]# ipvsadm -Sn > /etc/sysconfig/ipvsadm
[root@vm1 ~]# cat /etc/sysconfig/ipvsadm
-A -t 192.168.0.250:3306 -s wrr
-a -t 192.168.0.250:3306 -r 192.168.163.134:3306 -m -w 1
-a -t 192.168.0.250:3306 -r 192.168.163.137:3306 -m -w 1

验证

#配置远程mysql权限
mysql> grant all privileges on *.* to 'root'@'192.168.0.102' identified by '123456'
mysql> grant all privileges on *.* to 'root'@'192.168.163.1' identified by '123456';

#真机验证远程登录mysql
[root@localhost]mysql -uroot -h192.168.0.250 -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.22 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
+--------------------+
5 rows in set (0.009 sec)