MYSQL集群配置
LVS——DR模式配置MYSQL集群
环境信息
| 主机 | ip | 角色 |
|---|---|---|
| 调度器 | 192.168.197.141 | dip |
| 调度器,RS1,RS2 | 192.168.197.250 | vip |
| Real server1 | 192.168.197.142 | rip |
| Real server2 | 192.168.197.143 | rip |
| NFS服务器 | 192.168.197.137 | 共享mysql库 |
- 设置RS上的内核参数
[root@localhost conf.d]# 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 conf.d]# sysctl -p
net.ipv4.conf.all.arp_ignore = 1
net.ipv4.conf.all.arp_announce = 2
- 配置RS上的vip
## 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.197.142
NETMASK0=255.255.255.0
GATEWAY0=192.168.197.2
IPADDR1=192.168.197.250 # 指定vip
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.197.143
NETMASK0=255.255.255.0
GATEWAY0=192.168.197.2
IPADDR1=192.168.197.250
NETMASK1=255.255.255.0
-
验证访问vip是否访问到DR上
.jpg)
.jpg)
-
调度器上配置ipvsadm
[root@localhost ~]# ipvsadm -A -t 192.168.197.250:3306 -s rr
[root@localhost ~]# ipvsadm -a -t 192.168.197.250:3306 -r 192.168.197.142:3306 -g
[root@localhost ~]# ipvsadm -a -t 192.168.197.250:3306 -r 192.168.197.143: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.197.250:3306 rr
-> 192.168.197.142:3306 Route 1 0 0
-> 192.168.197.143:3306 Route 1 0 0
## 保存设置
[root@localhost ~]# ipvsadm -Sn > /etc/sysconfig/ipvsadm
- NFS服务器配置
[root@localhost lib]# vim /etc/exports
/var/lib/mysql 192.168.197.0/24(rw,anonuid=27,anongid=27)
# 共享文件的所属用户与组变更
[root@localhost lib]# chown -R mysql.mysql mysql/
# RS1挂载到nfs共享文件中
[root@localhost mysql]# mount -t nfs 192.168.197.137:/var/lib/mysql /var/lib/mysql/
[root@localhost mysql]# df -h
Filesystem Size Used Avail Use% Mounted on
devtmpfs 1.9G 0 1.9G 0% /dev
tmpfs 2.0G 0 2.0G 0% /dev/shm
tmpfs 2.0G 9.0M 2.0G 1% /run
tmpfs 2.0G 0 2.0G 0% /sys/fs/cgroup
/dev/mapper/rhel-root 17G 2.6G 15G 15% /
/dev/nvme0n1p1 1014M 154M 861M 16% /boot
tmpfs 392M 0 392M 0% /run/user/0
/dev/sr0 7.4G 7.4G 0 100% /mnt
192.168.197.137:/var/lib/mysql 47G 1.8G 46G 4% /var/lib/mysql # 成功挂载
- RS1初始化数据库并开启服务
[root@localhost mysql]# !vim
vim /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/var/lib/mysql
user=mysql
socket=/tmp/mysql.sock
port=3306
pid-file=/var/lib/mysql/mysql.pid
skip-name-resolve
## 初始化
[root@localhost mysql]# mysqld --initialize-insecure --user=mysql --datadir=/var/lib/mysql --explicit_defaults_for_timestamp
......
## 查看nfs共享文件中的内容
[root@localhost mysql]# ls
auto.cnf ibdata1 ib_logfile1 performance_schema
ib_buffer_pool ib_logfile0 mysql sys
# 配置mysql，开启服务
[root@localhost mysql]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
[root@localhost mysql]# vim /etc/init.d/mysqld
[root@localhost mysql]# chkconfig --add mysqld
[root@localhost mysql]# service mysqld start
Starting MySQL.Logging to '/var/lib/mysql/localhost.localdomain.err'.
. SUCCESS!
# 成功登录
[root@localhost mysql]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.22 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
- RS2配置mysql并将数据库存放文件目录挂载到nfs共享目录
## 必须安装nfs
[root@localhost local]# yum install -y nfs-utils
## 进行挂载
[root@localhost local]# mount -t nfs 192.168.197.137:/var/lib/mysql /var/lib/mysql/
[root@localhost local]# df -h
Filesystem Size Used Avail Use% Mounted on
devtmpfs 1.9G 0 1.9G 0% /dev
tmpfs 2.0G 0 2.0G 0% /dev/shm
tmpfs 2.0G 9.0M 2.0G 1% /run
tmpfs 2.0G 0 2.0G 0% /sys/fs/cgroup
/dev/mapper/rhel-root 17G 4.6G 13G 28% /
/dev/sr0 7.4G 7.4G 0 100% /mnt
/dev/nvme0n1p1 1014M 154M 861M 16% /boot
tmpfs 392M 0 392M 0% /run/user/0
192.168.197.137:/var/lib/mysql 47G 1.9G 46G 4% /var/lib/mysql
[root@localhost local]# cd mysql
[root@localhost mysql]# ls
bin COPYING docs include lib man README share support-files
## mysql配置
[root@localhost mysql]# vim /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/var/lib/mysql
socket=/tmp/mysql.sock
user=mysql
port=3306
pid-file=/var/lib/mysql.pid
skip-name-resolve
## 为共享文件上级目录增加权限
[root@localhost mysql]# setfacl -m u:mysql:rwx /var/lib/
[root@localhost mysql]# ll -d /var/lib/mysql/
## 开启服务
[root@localhost mysql]# service mysqld stop
Shutting down MySQL.. SUCCESS!
[root@localhost mysql]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.22 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
- 验证rs1与rs2是否同步数据
## 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_sawyer |
+------------------+
| student |
+------------------+
1 row in set (0.01 sec)
LVS——NAT模式配置MYSQL集群
- 打开调度器的流量转发功能
[root@localhost yum.repos.d]# vim /etc/sysctl.conf
......
# name in /etc/sysctl.d/ and put new settings there.
#
# For more information, see sysctl.conf(5) and sysctl.d(5).
net.ipv4.ip_forward = 1
# 重读配置文件
[root@localhost yum.repos.d]# sysctl -p
net.ipv4.ip_forward = 1
- 配置调度器的DIP与VIP
[root@localhost yum.repos.d]# vim /etc/sysconfig/network-scripts/ifcfg-ens160
TYPE=Ethernet
BOOTPROTO=static
DEFROUTE=yes
NAME=ens160
UUID=a6015bbf-3304-4390-b6a7-9a7392fdf8cc
DEVICE=ens160
ONBOOT=yes
IPADDR0=192.168.197.141
NETMASK0=255.255.255.0
GATEWAY=192.168.197.2
IPADDR1=192.168.197.250
NETMASK1=255.255.255.0
[root@localhost yum.repos.d]# ip a
......
2: ens160: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000
link/ether 00:0c:29:16:30:38 brd ff:ff:ff:ff:ff:ff
inet 192.168.197.141/24 brd 192.168.197.255 scope global noprefixroute ens160
valid_lft forever preferred_lft forever
inet 192.168.197.250/24 brd 192.168.197.255 scope global secondary noprefixroute ens160
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fe16:3038/64 scope link
valid_lft forever preferred_lft forever
- 配置RS1上的RIP
[root@localhost ~]# vim /etc/sysconfig/network-scripts/ifcfg-ens160
TYPE=Ethernet
BOOTPROTO=static
NAME=ens160
UUID=90719a96-f96b-456d-85c0-2c02e6295a23
DEVICE=ens160
ONBOOT=yes
IPADDR=192.168.197.142
NETMASK=255.255.255.0
GATEWAY=192.168.197.141
[root@localhost ~]# route -n
Kernel IP routing table
Destination Gateway Genmask Flags Metric Ref Use Iface
0.0.0.0 192.168.197.141 0.0.0.0 UG 100 0 0 ens160
192.168.197.0 0.0.0.0 255.255.255.0 U 100 0 0 ens160
- 配置RS2上的RIP
[root@localhost ~]# vim /etc/sysconfig/network-scripts/ifcfg-ens160
TYPE=Ethernet
BOOTPROTO=static
NAME=ens160
UUID=eec4f364-f69b-43d9-977f-79fbdbd46a0e
DEVICE=ens160
ONBOOT=yes
IPADDR=192.168.197.143
NETMASK=255.255.255.0
GATEWAY=192.168.197.141
[root@localhost ~]# route -n
Kernel IP routing table
Destination Gateway Genmask Flags Metric Ref Use Iface
0.0.0.0 192.168.197.141 0.0.0.0 UG 100 0 0 ens160
192.168.197.0 0.0.0.0 255.255.255.0 U 100 0 0 ens160
- 配置RS1与RS2的MYSQL环境
[root@localhost ~]# tar -xf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@localhost ~]# cd /usr/local
[root@localhost local]# ls
bin games lib libexec sbin src
etc include lib64 mysql-5.7.22-linux-glibc2.12-x86_64 share
[root@localhost local]# ln -s mysql-5.7.22-linux-glibc2.12-x86_64/ mysql
[root@localhost local]# useradd -r -M -s /sbin/nologin mysql
[root@localhost local]# chown -R mysql.mysql mysql*
[root@localhost local]# ll
total 0
drwxr-xr-x. 2 root root 6 Aug 12 2018 bin
drwxr-xr-x. 2 root root 6 Aug 12 2018 etc
drwxr-xr-x. 2 root root 6 Aug 12 2018 games
drwxr-xr-x. 2 root root 6 Aug 12 2018 include
drwxr-xr-x. 2 root root 6 Aug 12 2018 lib
drwxr-xr-x. 2 root root 6 Aug 12 2018 lib64
drwxr-xr-x. 2 root root 6 Aug 12 2018 libexec
lrwxrwxrwx. 1 mysql mysql 36 Nov 22 14:46 mysql -> mysql-5.7.22-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 mysql mysql 129 Nov 22 14:45 mysql-5.7.22-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root 6 Aug 12 2018 sbin
drwxr-xr-x. 5 root root 49 Nov 11 11:05 share
drwxr-xr-x. 2 root root 6 Aug 12 2018 src
[root@localhost local]# echo 'export PATH=$PATH:/usr/local/mysql' > /etc/profile.d/mysql.sh
[root@localhost local]# source /etc/profile.d/mysql.sh
[root@localhost local]# mkdir /var/mysql_data ; chown mysql.mysql /var/mysql_data
[root@localhost local]# vim /etc/my.cnf
[mysql]
basedir=/usr/local/mysql
datadir=/var/mysql_data
user=mysql
socket=/tmp/mysql.sock
port=3306
pid-file=/var/mysql_data/mysql.pid
[root@localhost local]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@localhost local]# /usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --datadir=/var/mysql_data --explicit_defaults_for_timestamp
[root@localhost local]# service mysqld start
Starting MySQL.Logging to '/var/mysql_data/localhost.localdomain.err'.
SUCCESS!
[root@localhost mysql]# mysql
......
mysql>
- 调度器配置ipvsadm
[root@localhost yum.repos.d]# ipvsadm -A -t 192.168.197.250:3306 -s rr
[root@localhost yum.repos.d]# 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.197.250:3306 rr
[root@localhost yum.repos.d]# ipvsadm -a -t 192.168.197.250:3306 -r 192.168.197.142:3306 -g
[root@localhost yum.repos.d]# ipvsadm -a -t 192.168.197.250:3306 -r 192.168.197.143:3306 -g
[root@localhost yum.repos.d]# 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.197.250:3306 rr
-> 192.168.197.142:3306 Route 1 0 0
-> 192.168.197.143:3306 Route 1 0 0
- RS1与RS2上对异地账户授权
mysql> grant all privileges on *.* to 'test'@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
HAproxy配置mysql集群
- 安装必备环境
[root@localhost ~]# yum install -y make gcc pcre-devel bzip2-devel openssl-devel systemd-devel
- 创建用户
[root@localhost ~]# useradd -r -M -s /sbin/nologin haproxy
- 编译安装HAproxy
[root@localhost src]# tar -xf HAproxy2.3.tar.gz
[root@localhost src]# cd haproxy-2.3.0/
[root@localhost haproxy-2.3.0]# make clean
[root@localhost sbin]# make TARGET=linux-glibc USE_OPENSSL=1 USE_ZLIB=1 USE_PCRE=1 USE_SYSTEMD=1
......
[root@localhost sbin]# make install PREFIX=/usr/local/haproxy/
......
## 环境变量配置
[root@localhost haproxy-2.3.0]# cd /usr/local/haproxy/
[root@localhost haproxy]# cd sbin/
[root@localhost sbin]# cp haproxy /usr/sbin/
- HAproxy配置文件
cat > /etc/haproxy/haproxy.cfg <<EOF
#--------------全局配置----------------
global
log 127.0.0.1 local0 info
#log loghost local0 info
maxconn 20480
#chroot /usr/local/haproxy
pidfile /var/run/haproxy.pid
#maxconn 4000
user haproxy
group haproxy
daemon
#---------------------------------------------------------------------
#common defaults that all the 'listen' and 'backend' sections will
#use if not designated in their block
#---------------------------------------------------------------------
defaults
mode http
log global
option dontlognull
option httpclose
option httplog
#option forwardfor
option redispatch
balance roundrobin
timeout connect 10s
timeout client 10s
timeout server 10s
timeout check 10s
maxconn 60000
retries 3
#--------------统计页面配置------------------
listen admin_stats
bind 0.0.0.0:8189
stats enable
mode http
log global
stats uri /haproxy_stats
stats realm Haproxy\ Statistics
stats auth admin:admin
#stats hide-version
stats admin if TRUE
stats refresh 30s
#---------------web设置-----------------------
listen webcluster
bind 0.0.0.0:80
mode tcp ## MYSQL连接时需要基于tcp四层协议
#option httpchk GET /index.html
log global
maxconn 3000
balance roundrobin
cookie SESSION_COOKIE insert indirect nocache
server 192.168.197.142 192.168.197.142:3306 check inter 2000 fall 5
server 192.168.197.143 192.168.197.143:3306 check inter 2000 fall 5
#server web01 192.168.80.102:80 cookie web01 check inter 2000 fall 5
EOF
## 打开流量转发功能，重读配置文件
[root@localhost haproxy]# vim /etc/sysctl.conf
For more information, see sysctl.conf(5) and sysctl.d(5).
net.ipv4.ip_nonlocal_bind = 1
net.ipv4.ip_forward = 1
[root@localhost haproxy]# sysctl -p
net.ipv4.ip_nonlocal_bind = 1
net.ipv4.ip_forward = 1
## 配置HAproxy.service 文件
[root@localhost haproxy]# cat > /usr/lib/systemd/system/haproxy.service <<EOF
> [Unit]
> Description=HAProxy Load Balancer
> After=syslog.target network.target
>
> [Service]
> ExecStartPre=/usr/local/haproxy/sbin/haproxy -f /etc/haproxy/haproxy.cfg -c -q
> ExecStart=/usr/local/haproxy/sbin/haproxy -Ws -f /etc/haproxy/haproxy.cfg -p /var/run/haproxy.pid
> ExecReload=/bin/kill -USR2 $MAINPID
>
> [Install]
> WantedBy=multi-user.target
> EOF
## 重置服务
[root@localhost haproxy]# systemctl daemon-reload
- 打开日志服务，并配置HAproxy日志功能
[root@localhost haproxy]# systemctl start postfix
[root@localhost haproxy]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 100 127.0.0.1:25 0.0.0.0:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 100 [::1]:25 [::]:*
## 配置文件
[root@localhost postfix]# vim /etc/rsyslog.conf
......
# Save boot messages also to boot.log
local7.* /var/log/boot.log
local0.* /var/log/haproxy.log
- 打开HAproxy服务
[root@localhost postfix]# systemctl restart haproxy
[root@localhost postfix]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 100 127.0.0.1:25 0.0.0.0:*
LISTEN 0 128 0.0.0.0:8189 0.0.0.0:*
LISTEN 0 128 0.0.0.0:3306 0.0.0.0:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 100 [::1]:25 [::]:*
- 验证服务
.jpg)
## HAproxy服务机上远程连接服务器
[root@localhost postfix]# mysql -utest -p -h 192.168.197.141
Enter password:
......
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.04 sec)
## 创建数据库
mysql> create database student;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| student |
| sys |
+--------------------+
5 rows in set (0.01 sec)
- 到RS1的主机上发现已经添加数据库
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| student |
| sys |
+--------------------+
5 rows in set (0.00 sec)
## RS2并没有添加
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
- 将RS1的数据库停止服务，再次添加数据库并查看
[root@localhost ~]# service mysqld stop;
Shutting down MySQL.. SUCCESS!
## 再次连接HAproxy主机
[root@localhost postfix]# mysql -utest -p -h 192.168.197.141
Enter password:
......
mysql> create database student2;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| student2 |
| sys |
+--------------------+
5 rows in set (0.00 sec)
- 到RS2主机上查看,发现新增的数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| student2 |
| sys |
+--------------------+
5 rows in set (0.00 sec)
## 利用HAproxy达到高可用的集群方式
