双vip的MySQL高可用集群

• 项目环境
• 项目详细配置
• • 1.项目图解
  • 2.项目步骤
• 出现问题及解决方法
• 总结

项目环境

5台服务器（1G，2核）centos7 mysql5.7.30 mysqlrouter8.0.21 keepalived2.0.10

项目详细配置

1.项目图解

2.项目步骤

一、安装好centos7的系统,.除了作为中间件的两台机器，其他机器都用二进制安装好mysql
二、在3台MySQL服务器上配置主从复制，建立读写分离的用户，形成一个master+2个slave节点的集群，提供数据库服务

①.在master上布置
1.确认mysql已经开启二进制日志
在/etc/my.cnf内配置
#开启二进制日志
log_bin
server_id=1
2.建立一个授权有复制权限的用户

[root@localhost ~]# mysql -uroot -p’lesleyC123#’
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.30-log MySQL Community Server (GPL)

Copyright © 2000, 2020, 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.

root@(none) 12:54 mysql>
root@(none) 12:54 mysql>grant replication slave on . to ‘lily’@‘192.168.116.141’ identified by ‘123456’;
Query OK, 0 rows affected, 1 warning (0.11 sec)
3.备份所有的库
[root@localhost ~]# cd /
[root@localhost /]# mkdir backup
[root@localhost /]# cd backup
[root@localhost backup]# mysqldump -uroot -p’lesleyC123#’ --all-databases >/backup/2021-07-13-all-db.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost backup]# ls
2021-07-13-all-db.sql
4.上传数据到从服务器
[root@localhost backup]# scp 2021-07-13-all-db.sql root@192.168.116.141:/root
root@192.168.116.141’s password:
2021-07-13-all-db.sql 100% 832KB 4.4MB/s 00:00
5.查看二进制日志文件名和位置号
root@(none) 12:57 mysql>show master status;
±---------------------±---------±-------------±-----------------±------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±---------------------±---------±-------------±-----------------±------------------+
| localhost-bin.000002 | 451 | | | |
±---------------------±---------±-------------±-----------------±------------------+
1 row in set (0.00 sec)

==========
②.在slave上配置
1.配置server_id（必须的）也开启二进制日志（二进制日志不是必须的）
也是配置/etc/my.cnf
server_id=2
log_bin
2.刷新mysql服务
[root@localhost ~]# yum install psmisc -y
已加载插件：fastestmirror
Loading mirror speeds from cached hostfile

• base: mirrors.163.com
• extras: mirrors.aliyun.com
• updates: mirrors.163.com
  base | 3.6 kB 00:00
  extras | 2.9 kB 00:00
  updates | 2.9 kB 00:00
  软件包 psmisc-22.20-17.el7.x86_64 已安装并且是最新版本
  无须任何处理
  [root@localhost ~]# killall -9 mysqld_safe
  [root@localhost ~]# killall -9 mysqld
  [root@localhost ~]# ps aux|grep mysqld
  root 2236 0.0 0.0 112824 980 pts/0 S+ 13:26 0:00 grep --color=auto mysqld
  [root@localhost ~]# service mysqld start
  Starting MySQL… SUCCESS!
  [root@localhost ~]# ps aux|grep mysqld
  root 2270 0.0 0.0 11820 1620 pts/0 S 13:26 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/localhost.localdomain.pid
  mysql 2450 0.7 11.7 1694972 219108 pts/0 Sl 13:26 0:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=localhost.localdomain.err --open-files-limit=8192 --pid-file=/data/mysql/localhost.localdomain.pid --socket=/data/mysql/mysql.sock --port=3306
  root 2485 0.0 0.0 112824 984 pts/0 S+ 13:28 0:00 grep --color=auto mysqld

3.确保mysql已经运行

[root@localhost ~]# ps aux|grep mysqld
root 2270 0.0 0.0 11820 1620 pts/0 S 13:26 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/localhost.localdomain.pid
mysql 2450 0.7 11.7 1694972 219108 pts/0 Sl 13:26 0:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=localhost.localdomain.err --open-files-limit=8192 --pid-file=/data/mysql/localhost.localdomain.pid --socket=/data/mysql/mysql.sock --port=3306
root 2485 0.0 0.0 112824 984 pts/0 S+ 13:28 0:00 grep --color=auto mysqld
4.导入master上的数据
[root@localhost ~]# mysql -uroot -p’lesleyC123#’ < 2021-07-13-all-db.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
5.在slave上配置master的信息和授权的用户名和密码
在主机上查看信息
root@(none) 13:22 mysql>show master status\G
*************************** 1. row ***************************
File: localhost-bin.000002
Position: 451
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

打开slave上的mysql，在slave上执行
CHANGE MASTER TO MASTER_HOST=‘192.168.116.140’,
MASTER_USER=‘lily’,
MASTER_PASSWORD=‘123456’,
MASTER_PORT=3306,
MASTER_LOG_FILE=‘localhost-bin.000003’,
MASTER_LOG_POS=154;
root@(none) 13:35 mysql> CHANGE MASTER TO MASTER_HOST=‘192.168.116.140’,
-> MASTER_USER=‘lily’,
-> MASTER_PASSWORD=‘123456’,
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE=‘node3-db-bin.000003’,
-> MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.05 sec
5.查看slave状态
root@(none) 13:36 mysql>show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.116.140
Master_User: lily
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: node3-db-bin.000002
Read_Master_Log_Pos: 5582
Relay_Log_File: localhost-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: node3-db-bin.000002
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 5582
Relay_Log_Space: 154
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 1f0678e7-e24f-11eb-9b34-000c2975b4b7
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
6.启动slave功能
root@(none) 14:05 mysql>start slave;
Query OK, 0 rows affected (0.00 sec)

root@(none) 14:05 mysql>show slave status\G 再次查看状态
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.116.140
Master_User: lily
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: localhost-bin.000003
Read_Master_Log_Pos: 154
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 324
Relay_Master_Log_File: localhost-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:

7.去主机验证
在主机进行操作
root@(none) 14:03 mysql>create database lan;
Query OK, 1 row affected (0.11 sec)

root@(none) 14:11 mysql>use lan
Database changed
root@lan 14:12 mysql>create table user(id int, name varchar(20))
-> ;
Query OK, 0 rows affected (0.24 sec)

root@lan 14:12 mysql>insert into user(id,name) values(1,‘caca’);
Query OK, 1 row affected (0.03 sec)

root@lan 14:13 mysql>insert into user(id,name) values(2,‘lala’);
Query OK, 1 row affected (0.05 sec)

root@lan 14:13 mysql>
在从服务器上查看
root@(none) 14:14 mysql>show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| chen |
| lan |
| lu |
| lulu |
| mysql |
| performance_schema |
| sys |
| xionghan |
±-------------------+
9 rows in set (0.07 sec)

root@(none) 14:15 mysql>use lan
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
root@lan 14:15 mysql>show tables;
±--------------+
| Tables_in_lan |
±--------------+
| user |
±--------------+
1 row in set (0.00 sec)

root@lan 14:15 mysql>select * from user;
±-----±-----+
| id | name |
±-----±-----+
| 1 | caca |
| 2 | lala |
±-----±-----+
2 rows in set (0.00 sec)

主从复制服务搭建成功

同样配置slave2和master间的主从复制

三、在两台中间件服务器上安装MySQLrouter和keepalived，实现读写法分离和高可用，在keepalived上配置2个实例，实现2个vip，互为master和backup，更加好的提升高可用的性能。
1.去官方网站下载mysql-router-community-8.0.23-1.el7.x86_64.rpm
2.将上述安装包安装到虚拟机并且解压
[root@localhost ~]# rpm -ivh mysql-router-community-8.0.23-1.el7.x86_64.rpm
警告：mysql-router-community-8.0.23-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY
准备中… ################################# [100%]
正在升级/安装…
1:mysql-router-community-8.0.23-1.e################################# [100%]
3.进入mysqlrouter配置文件所在处，修改配置文件
[root@localhost mysqlrouter]# pwd
/etc/mysqlrouter

[root@localhost mysqlrouter]# vim mysqlrouter.conf
[routing:read_write]
bind_address = 192.168.116.142
bind_port = 7001
mode = read-write
destinations = 192.168.116.140:3306
max_connections = 65535
max_connect_errors = 100
client_connect_timeout = 9

[routing:read_only]
bind_address = 192.168.116.142
bind_port = 7002
mode = read-only
destinations = 192.168.0.146:3306,192.168.116.143:3306
max_connections = 65535
max_connect_errors = 100
client_connect_timeout = 9

[root@localhost mysqlrouter]# cat /var/log/mysqlrouter/mysqlrouter.log
出错看日志
4.启动MySQLrouter服务
[root@localhost mysqlrouter]# service mysqlrouter start
看状态来分辨服务是否启动
[root@localhost mysqlrouter]# netstat -anplut
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1012/sshd
tcp 0 0 0.0.0.0:7001 0.0.0.0:* LISTEN 2464/mysqlrouter
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 1110/master
tcp 0 0 0.0.0.0:7002 0.0.0.0:* LISTEN 2464/mysqlrouter
tcp 0 0 192.168.116.142:22 192.168.116.1:53931 ESTABLISHED 1402/sshd: root@pts
tcp6 0 0 :::22 ::: * LISTEN 1012/sshd
tcp6 0 0 ::1:25 ::: * LISTEN 1110/master
udp 0 0 127.0.0.1:323 0.0.0.0:* 687/chronyd
udp 0 0 0.0.0.0:68 0.0.0.0:* 1350/dhclient
udp6 0 0 ::1:323 ::: * 687/chronyd

新建两个用户，在后端的mysql服务器（master）上新建
Lili --》read-write–>7001–>192.168.116.142:3306
Lele-》read-only–>7002–>192.168.116.146:3306,192.168.116.143:3306
root@(none) 11:41 mysql>grant all on . to ‘lili’@’%’ identified by ‘Sanchuang123#’;
Query OK, 0 rows affected, 1 warning (0.44 sec)

root@(none) 11:42 mysql>grant select on . to ‘lele’@’%’ identified by ‘Sanchuang123#’;
Query OK, 0 rows affected, 1 warning (0.03 sec)
root@(none) 11:44 mysql>select user,host from mysql.user;
±--------------±----------------+
| user | host |
±--------------±----------------+
| lele | % |
| lili | % |
| xionghan | 192.168.0.48 |
| lily | 192.168.116.141 |
| xionghan | 192.168.116.141 |
| lola | 192.168.116.143 |
| wen | 192.168.116.146 |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
±--------------±----------------+
10 rows in set (0.16 sec)

用外面服务器连接测试
[root@localhost ~]# mysql -h 192.168.116.142 -P7001 -ulili -p’Sanchuang123#’
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 211
Server version: 5.7.30-log MySQL Community Server (GPL)

Copyright © 2000, 2020, 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.

lili@(none) 13:41 mysql>show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| chen |
| lan |
| lu |
| lulu |
| mysql |
| performance_schema |
| sys |
| wangwang |
| wangwang1 |
| wangwang2 |
| wangwang3 |
| xionghan |
±-------------------+
13 rows in set (0.29 sec)

lili@(none) 13:41 mysql>drop database wangwang;
Query OK, 0 rows affected (0.46 sec)

配置vip，在中间件上配置keepalived
1.安装Keepalived
[root@localhost mysqlrouter]# yum install keepalived -y
2.修改Keepalived配置
[root@localhost /]# cd /etc/keepalived
[root@localhost keepalived]# ls
keepalived.conf
[root@localhost keepalived]# vim keepalived.conf
! Configuration File for keepalived

global_defs {
notification_email {
acassen@firewall.loc
failover@firewall.loc
sysadmin@firewall.loc
}
notification_email_from Alexandre.Cassen@firewall.loc
smtp_server 192.168.200.1
smtp_connect_timeout 30
router_id LVS_DEVEL
vrrp_skip_check_adv_addr
#vrrp_strict
vrrp_garp_interval 0
vrrp_gna_interval 0
}

vrrp_instance VI_1 { #表示实例名
state MASTER #状态是master
interface ens33 #在哪个网络接口提供服务
virtual_router_id 151 #虚拟路由器的id，id一样表示是一个虚拟路由组，主和备id要一样
priority 110 #表示优先级，优先级数字越大优先级越高
advert_int 1 #通告消息发送的间隔时间 默认为1秒 advertising interval
{ #认证的密码
auth_type PASS #认证类型
auth_pass 1111 #认证密码
}
virtual_ipaddress { #虚拟ip地址 vip
192.168.116.188
}
vrrp_instance VI_2 {
state BACKUP
interface ens33
virtual_router_id 159
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.116.189
}
}
第二台中间件机器的配置和第一台号位有点不同
第二台配置如下
! Configuration File for keepalived

global_defs {
notification_email {
acassen@firewall.loc
failover@firewall.loc
sysadmin@firewall.loc
}
notification_email_from Alexandre.Cassen@firewall.loc
smtp_server 192.168.200.1
smtp_connect_timeout 30
router_id LVS_DEVEL
vrrp_skip_check_adv_addr
#vrrp_strict
vrrp_garp_interval 0
vrrp_gna_interval 0
}

vrrp_instance VI_1 {
state BACKUP
interface ens33
virtual_router_id 151
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.116.188
}
}

vrrp_instance VI_2 {
state MASTER
interface ens33
virtual_router_id 159
priority 110
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.116.189
}
}

上面所示为一台机器配置MySQLrouter和Keepalived，第二台机器只需要进行相同配置（除了上述标出的不同）即可
两台机器都配好后
可以看到
Master
[root@localhost keepalived]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:12:f1:9b brd ff:ff:ff:ff:ff:ff
inet 192.168.116.142/24 brd 192.168.116.255 scope global noprefixroute dynamic ens33
valid_lft 1723sec preferred_lft 1723sec
inet 192.168.116.188/32 scope global ens33
valid_lft forever preferred_lft forever
inet6 fe80::2afc:9e30:218b:c829/64 scope link noprefixroute
valid_lft forever preferred_lft forever

Backup
[root@localhost keepalived]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:09:6b:54 brd ff:ff:ff:ff:ff:ff
inet 192.168.116.145/24 brd 192.168.116.255 scope global dynamic ens33
valid_lft 1538sec preferred_lft 1538sec
inet 192.168.116.189/32 scope global ens33
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fe09:6b54/64 scope link
valid_lft forever preferred_lft forever
3: ens37: <BROADCAST,MULTICAST> mtu 1500 qdisc noop state DOWN group default qlen 1000
link/ether 00:0c:29:09:6b:5e brd ff:ff:ff:ff:ff:ff

项目完成

验证vip是否能在master和backup上漂移
在master上停止Keepalived服务
[root@localhost keepalived]# service keealived stop
在backup上看是否有vip
[root@localhost keepalived]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:09:6b:54 brd ff:ff:ff:ff:ff:ff
inet 192.168.116.145/24 brd 192.168.116.255 scope global dynamic ens33
valid_lft 1792sec preferred_lft 1792sec
inet 192.168.116.189/32 scope global ens33
valid_lft forever preferred_lft forever
inet 192.168.116.188/32 scope global ens33
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fe09:6b54/64 scope link
valid_lft forever preferred_lft forever
3: ens37: <BROADCAST,MULTICAST> mtu 1500 qdisc noop state DOWN group default qlen 1000
link/ether 00:0c:29:09:6b:5e brd ff:ff:ff:ff:ff:ff
3.在master上启动Keepalived服务，会发现vip又漂移到master上了
[root@localhost keepalived]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:12:f1:9b brd ff:ff:ff:ff:ff:ff
inet 192.168.116.142/24 brd 192.168.116.255 scope global noprefixroute dynamic ens33
valid_lft 1582sec preferred_lft 1582sec
inet 192.168.116.188/32 scope global ens33
valid_lft forever preferred_lft forever
inet6 fe80::2afc:9e30:218b:c829/64 scope link noprefixroute
valid_lft forever preferred_lft forever
3: ens37: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:12:f1:a5 brd ff:ff:ff:ff:ff:ff
inet 192.168.116.147/24 brd 192.168.116.255 scope global noprefixroute dynamic ens37
valid_lft 936sec preferred_lft 936sec
inet6 fe80::548a:dc59:eb1:ed2d/64 scope link noprefixroute
valid_lft forever preferred_lft forever

当master 出现问题，backup会成为master，但是当原来的master有修好了，vip又会重新飘到原来的master上

出现问题及解决方法

项目中容易出错的地方：
1.主从复制修改配置文件的时候，要核对好主机ip，pos，二进制文件名字和名称密码，错一个服务就起不来
一旦出错，可以用下述方法解决：
清除从服务器上填写的主服务器的信息
stop slave;
reset slave all;
show slave status\G
再重新配置
二进制文件名没有打错，但是I/O进程起不来可以在master上的mysql
输入 flush logs;
系统会重新生成二进制文件
再用 show master status查看，然后在slave上重新配置就可以了
2.配置中间件
配置文件一定要核对清楚，删除原有配置文件时，要注意括号是否一一对应，括号少了也会出问题

总结

项目过程中修改配置文件一定要仔细，在配置Keepalived文件时，注意注释 vrrp_strict，防止出现脑裂现象。配置文件时要分清楚各个机器的功能，不要选择配置的机器错误，修改起来会很麻烦。