构建高可用MYSQL主从复制集群

一、项目描述

项目名称：

        使用zabbix监控的高可用mysql主从复制集群

项目环境：

系统版本	IP	主机名	角色
CentOS 7.9.2009	192.168.8.128	mysql-master	主服务器
CentOS 7.9.2009	192.168.8.131	mysql-slave	从服务器
CentOS 7.9.2009	192.168.8.26	mysql-router-1	路由中间件-1
CentOS 7.9.2009	192.168.8.204	mysql-router-2	路由中间件-2
CentOS 7.9.2009	192.168.8.178	master-zabbix	监控主机

项目描述： 

        利用Keepalived构建一个高可用的MYSQL集群，提供读写分离、半同步模式的主从复制等功能，使用zabbix对集群进行监控，确保业务的稳定。

项目步骤：

1、准备好五台虚拟机，安装好centos7的操作系统，做好初始化工作，例如：配置IP地址，更改主机名，关闭防火墙和selinux；

2、编写脚本使用二进制安装的的方式在master服务器和slave服务器上一键安装好MySQL；

3、在MySQL集群服务器是配置好主从复制，一主一从，并且验证主从复制的效果；

4、在两个mysql-router节点安装好mysqlrouter中间件，配置好读写分离；

5、在两个mysql-router节点上安装好Keepalived，做好相关配置，实现双VIP，互为master和backup，实现高可用

6、在监控主机上安装zabbix，配置好监控整个集群服务器的CPU、内存、磁盘、和mysql服务以及微信告警；

7、测试验证读写分离和高可用，以及告警验证

遇到的问题一：重启mysql服务失败

故障原因： ERROR! The server quit without updating PID file

解决办法：查看日志，发现是mysql的配置文件my.cnf中开起慢日志的命令slow_query_log写成了

slow_qurey_log，更正过来就好了

遇到的问题二：关闭了firewalld 测试机还是无法使用VIP访问master服务器

故障原因：mysql-router节点中的 iptables 规则链阻止了测试机的访问

解决办法：iptables -F 清空了规则链

项目心得：

        通过这个项目我对MySQL主从复制、HA、负载均衡以及MYSQL读写分离有了更加深刻的理解，认识到了部署一个项目过程中细心有非常重要的，并且需要提前规划项目架构；我在项目过程中也遇到了很多问题，自己都花时间去一一解决了，这也锻炼了自己的解决问题的能力。

二、项目拓扑结构

三、搭建mysql基于GTID模式的主从复制

GTID的概念：

        GTID是在整个复制环境中对一个事务的唯一标识，全局唯一，一个事务对应一个GTID代替传统的binlog+pos复制，使用master_auto_position=1自动匹配GTID断点进行复制，在slave端也必须开起binlog，slave在接受master的binlog时，会校验GTID值

原理：

        master更新数据时，会在事务前产生GTID，异同记录到binlog日志中，slave端的i/o 线程将变更的binlog，写入到本地的relay log中，sql线程从relay log中获取GTID，然后对比slave端的binlog是否有记录，如果有记录，说明该GTID的事务已经执行，slave会忽略，如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog。

搭建步骤：

1、二进制安装mysql

在mysql-master和mysql-slave上都安装mysql

版本：mysql-5.7.37

安装包可以去官网下载：MySQL ：： 下载 MySQL Community Server

二进制安装脚本如下：

#!/bin/bash
#解压mysql二进制安装包
tar xf mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz
#移动MySQL解压后的文件到/usr/local下改名叫mysql
mv mysql-5.7.37-linux-glibc2.12-x86_64 /usr/local/mysql
#新建组和用户 mysql
groupadd mysql
#mysql这个用户的shell 是/bin/false 属于mysql组
useradd -r -g mysql -s /bin/false mysql
#关闭firewalld防火墙服务，并且设置开机不要启动
service firewalld stop
systemctl disable firewalld
#临时关闭selinux
setenforce 0
#永久关闭selinux
sed -i '/^SELINUX=/ s/enforcing/disabled/' /etc/selinux/config

#新建存放数据的目录
mkdir /data/mysql -p
#修改/data/mysql目录的权限归mysql用户和mysql组所有，这样mysql用户可以对这个文件夹进行读写了
chown mysql:mysql /data/mysql/
#只是允许mysql这个用户和mysql组可以访问，其他人都不能访问
chmod 750 /data/mysql/
#进入/usr/local/mysql/bin目录
cd /usr/local/mysql/bin/
#初始化mysql
./mysqld --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/data/mysql &>passwd.txt


#让mysql支持ssl方式登录的设置
 ./mysql_ssl_rsa_setup --datadir=/data/mysql/

#获得临时密码
tem_passwd=$(cat passwd.txt |grep "temporary"|awk '{print $NF}')
#$NF表示最后一个字段
# abc=$(命令)  优先执行命令，然后将结果赋值给abc
#修改PATH变量，加入mysql bin目录的路径
#临时修改path变量的值
PATH=/usr/local/mysql/bin/:$PATH
#重新启动Linux系统后也生效，永久修改
echo 'PATH=/usr/local/mysql/bin:$PATH' >>/root/.bashrc

#复制support-files里的mysql.server文件到/etc/init.d/目录下叫mysqld
cp ../support-files/mysql.server /etc/init.d/mysqld
#修改/etc/init.d/mysqld脚本文件里面的datadir目录的值
sed -i '70c datadir=/data/mysql' /etc/init.d/mysqld

#生成/etc/my.cnf配置文件
cat >/etc/my.cnf <<EOF
[mysqld_safe]
[client]
socket=/data/mysql/mysql.sock
[mysqld]
socket=/data/mysql/mysql.sock
port = 3306
open_files_limit = 8192
innodb_buffer_pool_size = 512M
character-set-server=utf8
[mysql]
auto-rehash
prompt=\\u@\\d \\R:\\m  mysql>
EOF

#启动mysqld服务
service mysqld start
#设置mysqld服务开机启动

/sbin/chkconfig mysqld on
#将mysqld添加到Linux系统的管理服务名单里
/sbin/chkconfig --add mysqld
#修改密码（初次修改密码需要使用--connetc-expired-password  选项 -e 后面接的表示是在MySQL里面需要执行的命令  execute 执行）
mysql -uroot -p$tem_passwd --connect-expired-password  -e "set password='123456'"

2、配置master和slave上的相关设置

修改配置文件：vim /etc/my.cnf

配置master：

[mysqld]

.....
general_log   # 开起二进制日志
server_id = 1  #设置server_id ,主从不能一样
gtid-mode=ON
enforce-gtid-consistency=ON
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000  # 1 second
.....

配置slave：

vim /etc/my.cnf

[mysqld]

.....
server_id = 2 #添加server_id 配置
log_bin
gtid-mode=ON
enforce-gtid-consistency=ON
log_slave_updates=ON
rpl_semi_sync_slave_enabled=1
.....

配置半同步还需要安装一个插件：

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';  #安装插件

3、在master上创建给slave过来复制二进制日志的用户

创建用户：

root@(none) 10:57  mysql>create user 'chen_slave'@'%' identified by '123456';

授权用户可以有复制权限：
 

grant replication slave on *.* to 'chen_slave'@'%' identified by '123456';

4、在slave上添加授权信息

 CHANGE MASTER TO MASTER_HOST='192.168.8.128', #master ip地址
 MASTER_USER='chen_slave',  # master上拥有复制权限的用户名
 MASTER_PASSWORD='123456',  # master的密码
 MASTER_PORT=3306,          #开放的端口
 MASTER_AUTO_POSITION=1     #事务id,从1开始

5、给master上做全备，导出数据到slave，保持master和slave的原始数据一致

使用mysqldump给主服务做备份

mysqldump -uroot -p'123456' --all-databases >/backup/all_db.sql

使用scp传输到从服务器上并导入

#传输
scp /backup/all_db.sql root@192.168.8.131:/


#导入
mysql -uroot -p'123456' <all_db.sql 

6、在slave上启动salve服务，查看IO线程和SQL线程是否启动

启动slave服务:

root@(none) 18:58  mysql>start slave;
Query OK, 0 rows affected (0.00 sec)

查看slave的状态：IP线程和SQL线程都是yes表示启动成功

root@(none) 19:00  mysql>show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.8.128
                  Master_User: chen_slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: localhost-bin.000012
          Read_Master_Log_Pos: 154
               Relay_Log_File: localhost-relay-bin.000020
                Relay_Log_Pos: 375
        Relay_Master_Log_File: localhost-bin.000012
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

查看master的状态：

root@test 19:09  mysql>show master status;
+----------------------+----------+--------------+------------------+----------------------------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
+----------------------+----------+--------------+------------------+----------------------------------------+
| localhost-bin.000013 |      336 |              |                  | dbccc6ea-c1e8-11ec-bdbc-000c29a39b44:1 |
+----------------------+----------+--------------+------------------+----------------------------------------+

reset slave ：在从服务器上清除填写的master的信息

基于GTID复制的优点：

1、方便进行故障转移，记录master最后事务的GTID值。

2、slave不会丢失master的任何修改（开起了log_slave_updates）

四、使用MySQL Router实现读写分离

读写分离的优势：

负载均衡，提高并发能力

MySQL Router 部署图

1、在mysql-router-1和mysql-router-2上安装MySQL Router中间件

版本：mysql-router-8.0.29

下载地址：MySQL :: Download MySQL Router

解压：

tar xf mysql-router-community-8.0.29-1.el7.x86_64.rpm 

就OK了

日志：/var/log/mysqlrouter

2、配置/etc/mysqlrouter/mysqlrouter/conf文件

[routing:read_write]
bind_address = 0.0.0.0 #监听请求的IP地址（0.0.0.0表示监听主机上的所有地址）
bind_port = 7001       #监听请求的端口
mode = read-write
destinations = 192.168.8.131:3306   # 转发的目标
max_connections = 65535
max_connect_errors = 100
client_connect_timeout = 9


[routing:read_only]
bind_address = 0.0.0.0
bind_port = 7002
mode = read-only
destinations = 192.168.8.128:3306
max_connections = 65535
max_connect_errors = 100
client_connect_timeout = 9

查看服务是否起来

[root@localhost mysqlrouter]# netstat -anplut|grep mysql
tcp        0      0 0.0.0.0:7001            0.0.0.0:*               LISTEN      4790/mysqlrouter    
tcp        0      0 0.0.0.0:7002            0.0.0.0:*               LISTEN      4790/mysqlrouter

3、验证读写分离的效果

[root@localhost ~]# mysql -h 192.168.8.26 -P 7001 -uyeye -p'123456';
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 4
Server version: 5.7.37-log MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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.

yeye@(none) 20:00  mysql>

五、使用Keepalived实现高可用

原理：

VRRP原理及配置 - 榕霖 - 博客园 (cnblogs.com)

1、在mysql-router-1和mysql-router-2上安装Keepalived

yum install keepalived -y

2、修改配置文件，配置好双VIP

mysql-router-1

vrrp_instance VI_1 {
    state MASTER            # 状态
    interface ens33         #监听的网卡
    virtual_router_id 111   #虚拟路由id
    priority 120            #优先级，高的成为master
    advert_int 1            # 隔多久发一次通告报文
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {

        192.168.8.186       #vip
    }
}

vrrp_instance VI_2 {
    state BACKUP
    interface ens33
    virtual_router_id 222
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {

        192.168.8.188
    }
}
    

mysql-router-2

vrrp_instance VI_1 {
    state BACKUP            # 状态
    interface ens33         #监听的网卡
    virtual_router_id 111   #虚拟路由id
    priority 100            #优先级，高的成为master
    advert_int 1            # 隔多久发一次通告报文
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {

        192.168.8.186       #vip
    }
}

vrrp_instance VI_2 {
    state MASTER
    interface ens33
    virtual_router_id 222
    priority 120
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {

        192.168.8.188
    }
}
    

3、测试验证

使用VIP登录mysql，模拟其中一台mysql-router宕机的情况

脑裂：

现象：master和backup上都有VIP，用户可以通过master上的VIP进行连接也可以通过backup上的VIP进行连接。

产生的原因：

• virtual_router_id 不一致
• 防火墙阻止了master和backup之间的通信
• 网络故障
• 高可用服务器上心跳网卡地址等信息配置不正确，导致发送心跳失败

解决办法：

• 添加冗余的心跳线
• 做好做脑裂现象的监控报警
• 设置仲裁机制