基于mha+mycat2+gtid的半同步主从复制双vip高可用MySQL集群

CentOS：CentOS Linux release 7.9.2009 (Core)
MySQL：mysql Ver 14.14 Distrib 5.7.42, for linux-glibc2.12 (x86_64)
mha manager：mha4mysql-manager-0.58
mha node：mha4mysql-node-0.58
mycat2：mycat2-1.22-release-jar-with-dependencies-2022-10-13.jar、mycat2-install-template-1.21.zip
Keepalived: Keepalived v1.3.5 (03/19,2017)
sysbench：sysbench 1.0.17
Ansible：ansible 2.9.27
Prometheus: prometheus, version 2.43.0
mysqld_exporter：mysqld_exporter, version 0.12.1
Grafana: grafana 9.4.7
DNS：BIND 9.11.4-P2-RedHat-9.11.4-26.P2.el7_9.13

项目概述

项目名称：基于mha+mycat2+gtid的半同步主从复制双vip高可用MySQL集群

项目环境：centos 7.9(11台1G1核)，MySQL 5.7.42，mha 0.58，mycat2 1.22，keepalived 1.3.5，prometheus 2.43.0，grafana 9.4.7，sysbench 1.0.17，ansible 2.9.27，dns 9.11.4

项目描述：构建一个高可用能读写分离的高效MySQL集群，处理大并发的后端MySQL业务。该项目使用gitd实现半同步主从复制，mha实现MySQL主从自动切换，引入了mycat2实现读写分离，利用keepalived对mycat2服务器做双vip架构，再用dns做负载均衡，prometheus+Grafana对MySQL集群监控和出图，最后用sysbench做压力测试。其中还用到了ansible自动化运维，整个流程尽量模拟企业中构建高可用高性能的MySQL集群。。

项目流程：

使用ansible编写palybook做4台二进制MySQL环境的部署，并把master上的基础数据下发到所有slave服务器上
配置gtid+半同步的主从复制功能，实现MySQL的热备，其中1台master，3台slave（2台gtid半同步主从复制，1台延迟服务器从slave1上拿取二进制文件）
ansible服务器还充当远程备份服务器，在master上创建计划任务实现按时将备份数据同步到远程备份服务器上，保障数据安全性
使用两台mycat2中间件服务器对MySQL集群进行读写分离
keepalived对两台读写分离器配置两个vrrp实例实现双vip高可用功能
搭建DNS服务器，增添两条负载均衡记录，实现对mycat2双vip地址的DNS负载均衡
使用mha实现MySQL集群的故障切换和主从提升，保证集群的高效
Prometheus+mysqld_exporter+Grafana实现对MySQL集群以及读写分离器的系统资源监控
使用压力测试软件（sysbench）测试整个MySQL集群的性能（cpu、IO、内存等）

项目心得：

对MySQL的gtid半同步主从复制集群和高可用有了更深刻的理解，观察到mha实现的自动故障切换，理解其背后的原理，以及对mycat2实现读写分离机制更熟悉。同时一定要规划好项目架构提，细心做配置，在不断试错的过程中提高了自己的trouble shooting能力。

项目准备

11台Linux服务器，配置好ip地址，修改好主机名，同时关闭防火墙和selinux，设置开机不自启，为后面做项目做好准备，以免影响项目进度。

IP地址	角色
192.168.107.45	ansible、异地备份服务器
192.168.107.179	master
192.168.107.180	slave1
192.168.107.181	slave2
192.168.107.182	slave3、延迟备份服务器
192.168.107.40	sysbench
192.168.107.41	dns
192.168.107.42	mycat1
192.168.107.43	mycat2
192.168.107.44	promehtues+grafana
192.168.107.46	mha

修改每台主机的ip地址和主机名，这里只演示一台的操作，其他机器同理

本项目所有主机的网络模式为nat模式，进入/etc/sysconfig/network-scripts/ifcfg-ens33文件，将内容修改为一下内容

备注信息只做提示用，建议配置时删掉

[root@master ~]# cd /etc/sysconfig/network-scripts/
[root@master network-scripts]# ls
ifcfg-ens33  ifdown-isdn      ifdown-tunnel  ifup-isdn    ifup-Team
ifcfg-lo     ifdown-post      ifup           ifup-plip    ifup-TeamPort
ifdown       ifdown-ppp       ifup-aliases   ifup-plusb   ifup-tunnel
ifdown-bnep  ifdown-routes    ifup-bnep      ifup-post    ifup-wireless
ifdown-eth   ifdown-sit       ifup-eth       ifup-ppp     init.ipv6-global
ifdown-ippp  ifdown-Team      ifup-ippp      ifup-routes  network-functions
ifdown-ipv6  ifdown-TeamPort  ifup-ipv6      ifup-sit     network-functions-ipv6
[root@master network-scripts]# vi ifcfg-ens33
BOOTPROTO=none"    #将dhcp改为none，为了实验的方便防止后面由于ip地址改变而出错，将ip地址静态化
NAME="ens33"
DEVICE="ens33"
ONBOOT="yes"
IPADDR=192.168.107.45    #修改为规划好的ip地址
PREFIX=24
GATEWAY=192.168.107.2
DNS1=114.114.114.114

刷新网络服务，使配置的ip地址生效

[root@master network-scripts]# service network restart
Restarting network (via systemctl):                        [  确定  ]
[root@master network-scripts]# ip add
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:f5:ea:8d brd ff:ff:ff:ff:ff:ff
    inet 192.168.107.45/24 brd 192.168.107.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:fef5:ea8d/64 scope link 
       valid_lft forever preferred_lft forever

修改主机名

[root@localhost ~]# hostnamectl set-hostname ansible
[root@localhost ~]# su - root
上一次登录：二 8月 22 20:49:56 CST 2023从 192.168.107.1pts/1 上
[root@ansible ~]#

关闭防火墙，并设置开机不自启

[root@localhost ~]# service firewalld stop
Redirecting to /bin/systemctl stop firewalld.service
[root@localhost ~]# systemctl disable firewalld 
Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.

关闭selinux，并设置永久不开启

[root@localhost ~]# getenforce
Enforcing
[root@localhost ~]# setenforce 0    临时关闭selinux，重启不生效
[root@localhost ~]# sed  -i '/^SELINUX=/ s/enforcing/disabled/' /etc/selinux/config  修改配置文件使selinux永久不开启
[root@localhost ~]# cat /etc/selinux/config

# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
SELINUX=disabled     上面那条命令即修改了这里
# SELINUXTYPE= can take one of three values:
#     targeted - Targeted processes are protected,
#     minimum - Modification of targeted policy. Only selected processes are protected. 
#     mls - Multi Level Security protection.
SELINUXTYPE=targeted

项目步骤

一、使用ansible编写palybook实现4台二进制安装MySQL环境的部署，并把master上的基础数据下发到所有slave服务器上

在ansible服务器上，安装ansible，对4台mysql服务器建立单向免密通道，编写一键二进制安装mysql脚本，并编写playbook实现将脚本上传到4台mysql服务器上并执行脚本，实现自动化批量部署mysql

为了后面的实验方便，这里直接将ansible与所有服务器建立免密通道

1. 建立免密通道

这里以ansible在master建立免密通道为例，在slave服务器上操作一致，只需要将公钥传送到服务器上即可，这里不做过多展示

[root@ansible ~]# ssh-keygen  #生成密钥对
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa): 
Created directory '/root/.ssh'.
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:5w1IMbd1ShmRmXnQ2A/Mz0xy3cQ2drCNKVNREMt+xco root@ansible
The key's randomart image is:
+---[RSA 2048]----+
|        o . */BO=|
|         + +B=OXO|
|        . . +.B@*|
|       . .   = .*|
|        S o   E .|
|         o o   . |
|          . .    |
|                 |
|                 |
+----[SHA256]-----+
[root@ansible ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.107.179   将公钥传到要建立免密通道的服务器上
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '192.168.107.47 (192.168.107.47)' can't be established.
ECDSA key fingerprint is SHA256:J3IBWu4o8KqU2ugsTaqXrRkSXZGSQtU43Wx75LZZzJ4.
ECDSA key fingerprint is MD5:73:0b:fb:80:f8:1e:ce:da:4f:a4:8b:b8:c0:44:42:cc.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@192.168.107.47's password:    第一次传送公钥到远程服务器上要输入远程服务器的登录密码

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh '192.168.107.179'"
and check to make sure that only the key(s) you wanted were added.
 
[root@ansible ~]# ssh root@192.168.107.179   验证免密通道是否建立成功
Last login: Tue Aug 22 21:35:12 2023 from 192.168.107.1
[root@master ~]#

2.安装ansible在ansible服务器上，并写好主机清单

[root@ansible ~]# yum install -y epel-release
[root@ansible ~]# yum install ansible -y

[root@ansible ~]# cd /etc/ansible/
[root@ansible ansible]# ls
ansible.cfg  hosts  roles
[root@ansible ansible]# vim hosts 
[mysql]
192.168.107.179  #master
192.168.107.180  #slave1
192.168.107.181  #slave2
192.168.107.182  #slave3

3.将mysql的软件包上传到ansible服务器的家目录下

[root@ansible ~]# ls
anaconda-ks.cfg  mysql-5.7.42-linux-glibc2.12-x86_64.tar.gz

4.编写一键安装mysql的脚本

备注只做解释用，建立用的时候把备注删掉

[root@slave1 ~]# cat onekey_install_mysql.sh 
#!/bin/bash
#解决软件依赖关系并且安装需要工具
yum install -y cmake ncurses-devel gcc gcc-c++ vim lsof bzip2 openssl-devel ncurses-compat-libs net-tools 
#解压二进制安装包
tar xf mysql-5.7.42-linux-glibc2.12-x86_64.tar.gz 
#移动mysql解压后的文件到/usr/local下改名为mysql，/usr/local/mysql是mysql的安装目录
mv mysql-5.7.42-linux-glibc2.12-x86_64 /usr/local/mysql
#新建组和用户 mysql
groupadd 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用户启动的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}')
tem_passwd=$(cat passwd.txt |grep temporary|awk '{print $NF}')
#修改PATH变量，加入mysql bin目录的路径
#临时修改PATH变量的值
export PATH=/usr/local/mysql/bin/:$PATH
#设置开机修改PATH变量
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/mysql脚本文件里的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
#修改内核的open file的数量
ulimit -n 65535
#设置开机启动的时候也配置生效
echo "* soft nofile 65535" >>/etc/security/limits.conf 
echo "* hard nofile 65535" >>/etc/security/limits.conf 
#启动mysqld进程
service mysqld start
#将mysqld添加到linux系统里的服务管理名单里
systemctl enable mysqld
#登录mysql，密码可以自行设置指定
mysql -uroot -p$tem_passwd --connect-expired-password  -e  "set password='Sanchuang123#';"
#校验上一步密码是否成功，如果有输出能看到mysql里的数据库，说明成功。
mysql -uroot -p'Sanchuang123#' -e "show databases;"

5.编写playbook，将mysql源码包传到远程服务器上，并调用本地传到远端执行，实现mysql的批量安装部署

[root@ansible ansible]# vim mysql_install.yaml
- hosts: mysql
  remote_user: root
  tasks:
  - name: copy file  #上传本地源码包到mysql主机组
    copy: src=/root/mysql-5.7.42-linux-glibc2.12-x86_64.tar.gz dest=/root/
  - name: one key binary install mysql  #调用本地二进制安装脚本，远程执行安装mysql
    script: /root/onekey_install_mysql.sh
  - name: alter path #确保mysql命令加入环境变量
    shell: export PATH=/usr/local/mysql/bin/:$PATH

6.检查yaml文件语法

[root@ansible ansible]# ansible-playbook --syntax-check /etc/ansible/mysql_install.yaml 

playbook: /etc/ansible/mysql_install.yaml

7.执行yaml文件

[root@ansible ansible]# ansible-playbook  mysql_install.yaml

使用ansible中的copy模块上传源码包时，速度可能比较慢—可以考虑直接使用xshell里的xftp工具直接上传，不写playbook

8.查看是否安装成功

查看进程

[root@slave1 ~]# ps aux|grep mysql
root      11863  0.0  0.1  11824  1604 ?        S    22:20   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/slave1.pid
mysql     12017  0.5 20.6 1544680 205360 ?      Sl   22:20   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=slave1.err --open-files-limit=8192 --pid-file=/data/mysql/slave1.pid --socket=/data/mysql/mysql.sock --port=3306
root      12130  0.0  0.0 112824   988 pts/0    R+   22:21   0:00 grep --color=auto mysql

测试登录

[root@slave1 bin]# mysql -uroot -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 5
Server version: 5.7.42 MySQL Community Server (GPL)

Copyright (c) 2000, 2023, 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.

root@(none) 22:26  mysql>

9.mysqldump+mysql命令模拟对企业mysql已有数据的冷备

9.1 master建库建表插入数据，模拟企业已经存在的真实数据，也方便后续效果测试

root@(none) 21:37  mysql>create database test1;
Query OK, 1 row affected (0.00 sec)

root@(none) 21:39  mysql>use test1;
Database changed
root@test1 21:39  mysql>create table t1(id int primary key,name varchar(20));
Query OK, 0 rows affected (0.00 sec)

root@test1 21:40  mysql>select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | coco |
|  2 | saa  |
|  3 | cali |
+----+------+
3 rows in set (0.00 sec)

root@test1 21:41  mysql>

9.2 数据同步，使用mysqldump+mysql命令数据同步到slave1，slave2

这里master要将文件传送到slave，要建立免密通道，后面做主从复制时salve要到master上拿取二进制日志，因此为了方便，这里直接将master和3台slave这4台服务器相互之间配置免密通道

这里展示master对3台slave配置免密通道，另外3台操作同理

[root@master ~]# ssh-keygen
[root@master ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.107.180
[root@master ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.107.181
[root@master ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.107.182

9.2.1 master使用mysqldump命令数据备份到/backup

[root@master ~]# mkdir /backup
[root@master ~]# cd /backup
[root@master backup]# mysqldump -uroot -p"Sanchuang123#" --all-databases >all_db.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@master backup]# ls
all_db.sql

9.2.2 将备份文件scp到slave1、slave2、slave3的/backup文件夹下

[root@master backup]# ssh root@192.168.107.180 mkdir -p /backup
[root@master backup]# ssh root@192.168.107.181 mkdir -p /backup
[root@master backup]# ssh root@192.168.107.182 mkdir -p /backup

[root@master backup]# scp /backup/all_db.sql root@192.168.107.180:/backup/
all_db.sql                                   100%  868KB  46.8MB/s   00:00    
[root@master backup]# scp /backup/all_db.sql root@192.168.107.181:/backup/
all_db.sql                                   100%  868KB  38.1MB/s   00:00    
[root@master backup]# scp /backup/all_db.sql root@192.168.107.182:/backup/
all_db.sql                               ls    100%  868KB  53.7MB/s   00:00

9.2.3 slave1、slave2、slave3使用mysql命令将备份文件导入到自己的数据库

这里只展示salve1操作，另外两台操作一致

[root@slave1 bin]# cd /backup
[root@slave1 backup]# ls
all_db.sql
[root@slave1 backup]# mysql -uroot -p"Sanchuang123#" <all_db.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.

9.2.4 查看效果

登录其中一台看到与master上的数据一致

root@(none) 22:47  mysql>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
+--------------------+
5 rows in set (0.00 sec)

root@(none) 22:47  mysql>use test1;
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@test1 22:47  mysql>select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | coco |
|  2 | lisa |
|  3 | gog  |
|  4 | mimi |
+----+------+
4 rows in set (0.00 sec)

root@test1 22:47  mysql>

至此，数据同步成功

二、配置gtid+半同步的主从复制功能，实现MySQL的热备，其中1台master，3台slave（2台gtid半同步主从复制，1台延迟服务器从slave1上拿取二进制文件）

1. 所有MySQL服务器修改配置文件,开启二进制日志、GIID、半同步主从复制功能

其实实现主从复制只需要在master上开启二进制日志，但为了方便后续故障转移的实现，slave1和slave2机都有可能成为master，就全部mysql服务器都开启二进制日志、GTID、半同步功能

因为slave1作为master的从服务器，又要作为slave3的主服务器,因此主和从的配置都要加上

主服务器

#开启主服务器半同步主从复制
rpl_semi_sync_master_enabled=1
#设置主服务器半同步超时时间
rpl_semi_sync_master_timeout=1000

从服务器

#开启主服务器半同步主从复制
rpl_semi_sync_master_enabled=1

需要注意server_id不能一样。server_id,我的master配置为1，slave1为2，slave2为3，slave3为4

master的配置

[root@master ~]# vim /etc/my.cnf
[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
#开启二进制日志
log_bin
server_id = 1
#开启GTID功能
gtid-mode=ON
enforce-gtid-consistency=ON
#开启主服务器半同步主从复制
rpl_semi_sync_master_enabled=1
#设置主服务器半同步超时时间
rpl_semi_sync_master_timeout=1000

[mysql]
auto-rehash
prompt=\u@\d \R:\m  mysql>

[root@master ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

slave1的配置

[root@master ~]# vim /etc/my.cnf
[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
#开启二进制日志
log_bin
server_id = 2
#开启GTID功能
gtid-mode=ON
enforce-gtid-consistency=ON
#开启主服务器半同步主从复制
rpl_semi_sync_master_enabled=1
#设置主服务器半同步超时时间
rpl_semi_sync_master_timeout=1000
#开启从服务器半同步主从复制
rpl_semi_sync_slave_enabled=1

[mysql]
auto-rehash
prompt=\u@\d \R:\m  mysql>

[root@master ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

slave2和slave3的配置，注意server_id一个是3，一个是4

[root@master ~]# vim /etc/my.cnf
[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
#开启二进制日志
log_bin
server_id = 3
#开启GTID功能
gtid-mode=ON
enforce-gtid-consistency=ON
#开启从服务器半同步主从复制
rpl_semi_sync_slave_enabled=1

[mysql]
auto-rehash
prompt=\u@\d \R:\m  mysql>

在配置文件中加入了半同步主从复制的配置后，要先在相应的master和slave上安装半同步插件

master服务器

root@(none) 23:42  mysql>INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.01 sec)

slave1服务器，由于它又充当master的slave，又充当slave3的master，因此需要安装两个

root@(none) 23:44  mysql>INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.01 sec)

root@(none) 23:44  mysql>INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.00 sec)

root@(none) 23:44  mysql>

slave2和slave3服务器

root@(none) 23:44  mysql>INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.00 sec)

root@(none) 23:45  mysql>

刷新服务

[root@slave1 backup]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

2. 查看是否开启二进制日志、GITD功能和半同步主从复制功能

在每台mysql服务器上查看

root@(none) 22:57  mysql>show variables like "%log_bin%";
+---------------------------------+------------------------------+
| Variable_name                   | Value                        |
+---------------------------------+------------------------------+
| log_bin                         | ON                           |   ON表示已开启
| log_bin_basename                | /data/mysql/master-bin       |
| log_bin_index                   | /data/mysql/master-bin.index |
| log_bin_trust_function_creators | OFF                          |
| log_bin_use_v1_row_events       | OFF                          |
| sql_log_bin                     | ON                           |
+---------------------------------+------------------------------+
6 rows in set (0.00 sec)

root@(none) 22:57  mysql>show variables like "%gtid%";
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | ON        |
| gtid_executed_compression_period | 1000      |
| gtid_mode                        | ON        |  已开启
| gtid_next                        | AUTOMATIC | 
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+
8 rows in set (0.00 sec)

root@(none) 00:30  mysql>show variables like "%semi_sync%";
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |  已开启
| rpl_semi_sync_master_timeout              | 1000       |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.00 sec)

root@(none) 00:35  mysql>

3. master新建授权用户slave，用于slave1、slave2来master上取二进制日志

root@(none) 22:57  mysql>grant replication slave on *.* to 'slave'@'192.168.107.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)


root@(none) 23:01  mysql>select user,host from mysql.user;
+---------------+---------------+
| user          | host          |
+---------------+---------------+
| slave         | 192.168.107.% |
| mysql.session | localhost     |
| mysql.sys     | localhost     |
| root          | localhost     |
+---------------+---------------+
4 rows in set (0.00 sec)

root@(none) 23:01  mysql>

grant replication slave on . to ‘slave’@‘192.168.107.%’ identified by ‘123456’;

含义：允许107网段的IO线程通过用户slave密码123456来拿取二进制日志

4. slave1,slave2修改master信息

两台操作一致

root@(none) 23:04  mysql>change master to master_host='192.168.107.179',master_user='slave',master_password='123456',master_port=3306,master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

root@(none) 23:05  mysql>

master_host=‘192.168.107.179’ #master ip地址
master_user=‘slave’ #master授权的用户
master_password=‘123456’ #授权用户密码
master_port=3306 #master端口号
master_auto_position=1 #gtid中事务数自增量为1

5.开启slave,查看master_info信息

root@(none) 23:17  mysql>stop slave;
Query OK, 0 rows affected (0.01 sec)

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

root@(none) 23:17  mysql>show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.107.179
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000002
          Read_Master_Log_Pos: 450
               Relay_Log_File: slave2-relay-bin.000003
                Relay_Log_Pos: 457
        Relay_Master_Log_File: master-bin.000002
             Slave_IO_Running: Yes   #IO线程启动成功
            Slave_SQL_Running: Yes   #SQL线程启动成功，两个yes代表主从复制搭建成功
              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: 450
              Relay_Log_Space: 1176
              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: 0
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: 0e3e7b03-40f7-11ee-b3c4-000c2999ff0b
             Master_Info_File: /data/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 0e3e7b03-40f7-11ee-b3c4-000c2999ff0b:1   #GTID号，出现则代表gtid功能已经实现了
            Executed_Gtid_Set: 0e3e7b03-40f7-11ee-b3c4-000c2999ff0b:1
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

6. 配置salve3作为延迟服务器,在slave1上新建授权用户shanshan，用于slave3延迟备份服务器来slave1上取二进制日志

root@(none) 00:43  mysql>grant replication slave on *.* to 'shanshan'@'192.168.107.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (1.01 sec)

root@(none) 00:49  mysql>select user,host from mysql.user;
+---------------+---------------+
| user          | host          |
+---------------+---------------+
| shanshan      | 192.168.107.% |
| slave         | 192.168.107.% |
| mysql.session | localhost     |
| mysql.sys     | localhost     |
| root          | localhost     |
+---------------+---------------+
5 rows in set (0.00 sec)

root@(none) 00:49  mysql>

7.在slave3主机上修改master信息

root@(none) 23:20  mysql>change master to master_host='192.168.107.180',master_user='shanshan',master_password='123456',master_port=3306,master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

root@(none) 23:20  mysql>CHANGE MASTER TO MASTER_DELAY = 10;
Query OK, 0 rows affected (0.01 sec)

CHANGE MASTER TO MASTER_DELAY = 10; 表示延迟时间为10s，这里只做测试，可以自行设置

8. 查看延迟备份服务器是否生效

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

root@(none) 01:41  mysql>show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.107.180
                  Master_User: shanshan
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: slave1-bin.000005
          Read_Master_Log_Pos: 194
               Relay_Log_File: slave3-relay-bin.000007
                Relay_Log_Pos: 409
        Relay_Master_Log_File: slave1-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              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: 194
              Relay_Log_Space: 705
              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: 0
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: 2
                  Master_UUID: 0521fef6-40f7-11ee-b351-000c2969f304
             Master_Info_File: /data/mysql/master.info
                    SQL_Delay: 10   这里出现10 表示配置成功
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more up
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 0521fef6-40f7-11ee-b351-000c2969f304:1
            Executed_Gtid_Set: 03a30794-40f7-11ee-a43b-000c292bce21:1-128,
0521fef6-40f7-11ee-b351-000c2969f304:1
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

root@(none) 01:41  mysql>

9. 查看效果

在master上新建表

若slave上都能看到新建的表，则配置成功

root@test1 23:12  mysql>create table t2(id int,name varchar(20));
Query OK, 0 rows affected (0.01 sec)

root@test1 23:13  mysql>show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| t2              |
+-----------------+
1 row in set (0.01 sec)

报错问题解决

1. 在修改完/etc/my.cnf配置文件的时候，重启失败，报下面的错误

经过排查，发现是/etc/my.cnf配置文件写错了，一定要好好检查配置文件，不然服务起不来

下面是正确的配置，根据需要添加和删改功能，关键是千万不要打错

2. 在启动slave3延迟备份服务器的时候，也是配置文件有问题，如果有问题，建议重新修改配置文件，再试着重启。除此之外另一个问题就是主从复制一直起不来

报错

详情如下:

root@(none) 01:15  mysql>show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.107.180
                  Master_User: shanshan
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: 
          Read_Master_Log_Pos: 4
               Relay_Log_File: slave3-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: 
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
              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: 0
              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: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1236
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires. Replicate the missing transactions from elsewhere, or provision a new slave from backup. Consider increasing the master's binary log expiration period. The GTID set sent by the slave is '03a30794-40f7-11ee-a43b-000c292bce21:1-128', and the missing transactions are '0e3e7b03-40f7-11ee-b3c4-000c2999ff0b:1'.'
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2
                  Master_UUID: 0521fef6-40f7-11ee-b351-000c2969f304
             Master_Info_File: /data/mysql/master.info
                    SQL_Delay: 10
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 230823 01:15:03
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 03a30794-40f7-11ee-a43b-000c292bce21:1-128
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

经查看，这是一个MySQL GTID主从模式下断链出现error 1236的错误

出现此错误的原因是Slave的GTID大于Master的GTID，这种问题的出现往往是在Slave执行了DML操作，下面总结两种修复主从的方式。

参考：

https://zhuanlan.zhihu.com/p/509461346

在MySQL数据库的主从复制中，master_auto_position=1是一个参数，它的作用是指定主数据库(Master)自动记录二进制日志文件与位置信息，以供从数据库(Slave)进行复制。具体来说，这个参数会让主数据库将主数据库的二进制日志的文件名和偏移量信息自动写入到传输给从数据库的复制的事件中。

3.mysql报错

参考 https://www.cnblogs.com/zhongyehai/p/10695334.html

在配置文件中添加skip-grant-tables（登录时跳过权限检查），然后重启服务

三、ansible服务器还充当远程备份服务器，在master上创建计划任务实现按时将备份数据同步到远程备份服务器上，保障数据安全性

1. 计划任务定时全备数据

[root@master backup]# vim backup_db.sh 
#!/bin/bash

#导出数据库数据到/backup目录下，以日期时间的格式命名
mysqldump -uroot -p"Sanchuang123#" --all-databases >/backup/$(date +%F)_all_db.sql

#将备份数据传到异地备份服务器上，前提是建立好免密通道
scp /backup/$(date +%F)_all_db.sql  192.168.107.45:/backup

2.编写计划任务，每天凌晨2点半全备数据

[root@master backup]# crontab -e
[root@master backup]# crontab -l
30 2 * * * bash /backup/backup_db.sh

四、使用两台mycat2中间件服务器对MySQL集群进行读写分离

1. 安装部署mycat2

1.1 安装好mycat2环境所需要的mysql数据库（和步骤一里安装mysql数据库一样，我就不重复了)

因为mycat2是部署成一个mysql服务，然后向操作mysql一样去操作mycat2，所以要安装好mysql环境

1.2 安装好mycat2的java环境

两台mycat都要做

[root@mycat1 ~]# yum install -y java
[root@mycat2 ~]# yum install -y java

1.3 官网下载源码包（核心源码包jar包、外壳zip包）

两台mycat都要下载

[root@mycat1 ~]# wget http://dl.mycat.org.cn/2.0/1.22-release/mycat2-1.22-release-jar-with-dependencies-2022-10-13.jar
[root@mycat1 ~]# wget http://dl.mycat.org.cn/2.0/install-template/mycat2-install-template-1.21.zip

1.4 解压zip包

两台mycat都要操作

[root@mycat1 ~]# yum install -y unzip
[root@mycat1 ~]# unzip -q mycat2-install-template-1.21.zip

1.5 将jar包放入解压后的mycat/lib下

两台都要操作

[root@mycat1 ~]# mv mycat2-1.22-release-jar-with-dependencies-2022-10-13.jar mycat/lib/

1.6 将整合好的mycat放入/usr/local文件夹下

两台都要操作

[root@mycat1 bin]# mv /root/mycat  /usr/local/

1.7 授予相关文件最高权限，防止启动报错

两台都要操作

[root@mycat1 bin]# cd /usr/local/mycat/bin/
[root@mycat1 bin]# chmod 777 mycat
[root@mycat1 bin]# chmod 777 wrapper-linux-ppc-64 
[root@mycat1 bin]# chmod 777 wrapper-linux-x86-32 
[root@mycat1 bin]# chmod 777 wrapper-linux-x86-64

1.8 将mycat2加入PATH环境变量，并设置开机启动

两台都要操作

[root@mycat1 ~]# echo "PATH=/usr/local/mycat/bin/:$PATH" >>/root/.bashrc
[root@mycat1 ~]# PATH=/usr/local/mycat/bin/:$PATH

1.9 编辑prototypeDs.datasource.json默认数据源文件，并重启mycat（连接本机mysql数据库环境）

两台都要操作

[root@mycat1 bin]# cd /usr/local/mycat/
[root@mycat1 mycat]# ls
bin  conf  lib  logs
[root@mycat1 mycat]# cd conf
[root@mycat1 conf]# cd datasources/
[root@mycat1 datasources]# vim prototypeDs.datasource.json 
{
	"dbType":"mysql",
	"idleTimeout":60000,
	"initSqls":[],
	"initSqlsGetConnection":true,
	"instanceType":"READ_WRITE",
	"maxCon":1000,
	"maxConnectTimeout":3000,
	"maxRetryCount":5,
	"minCon":1,
	"name":"prototypeDs",
	"password":"Sanchuang123#",    #修改这里为mysql的登录密码
	"type":"JDBC",
	"url":"jdbc:mysql://localhost:3306/mysql?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
	"user":"root",    #这里是mysql的登录用户
	"weight":0
}

[root@mycat1 datasources]# mycat restart
Stopping mycat2...
mycat2 was not running.
Starting mycat2...

1.10 启动mycat2

两台都要操作

[root@mycat1 datasources]# mycat start
Starting mycat2...
mycat2 is already running.

2. mysql集群（master、slave1、slave2）新建授权用户，允许mycat2访问

root@(none) 16:42  mysql>grant all on *.* to 'mycat2'@'192.168.107.%' identified by '123456';

3. 验证数据库访问情况

mycat2作为数据库中间件要和数据库部署在不同机器上，所以要验证远程访问情况

注意：因为故障转移时，写服务器的访问是通过vip地址的，所以进行读写分离时，绑定后端数据源写服务器的时候的ip应该是vip地址。

以下操作两台机器都要做

[root@mycat1 datasources]# mysql -umycat2 -p123456 -h 192.168.107.66 #测试写服务器vip地址连接
[root@mycat1 datasources]# mysql -umycat2 -p123456 -h 192.168.107.179
[root@mycat1 datasources]# mysql -umycat2 -p123456 -h 192.168.107.180
[root@mycat1 datasources]# mysql -umycat2 -p123456 -h 192.168.107.181

4. 登录mycat2客户端，并新建逻辑库test1

[root@mycat1 datasources]# mysql -uroot -p123456 -P8066 -h 192.168.107.42

mycat客户端默认的用户：root

默认密码123456

-h指定本机ip地址

-P8066 mycat客户端的端口

可以看到已经成功登录mycat客户端了

新建逻辑库test1

root@(none) 01:52  mysql>create database test1;
Query OK, 0 rows affected (0.52 sec)

新建完成后，会在schema目录下生成一个逻辑库文件

5. 修改配置test1.schema.json配置文件，配置逻辑库对应的集群

“targetName”:“cluster”，—新增一条配置，指向后端数据源集群的名字是cluster

[root@mycat1 schemas]# vim test1.schema.json 

{
        "customTables":{},
        "globalTables":{},
        "normalProcedures":{},
        "normalTables":{},
        "schemaName":"test1",
        "targetName":"cluster",
        "shardingTables":{},
        "views":{}
}

重启mycat

[root@mycat1 schemas]# mycat restart

6. 注解方式添加集群、数据源，对应后端真实数据库

注解方式相当于修改配置文件，但是一个一个去修改配置文件比较繁琐，我这里就使用了mycat2的特色：使用注解方式修改配置文件

注解方式可以直接在mycat2客户端命令行里直接运行，也可以通过连接SQLyog客户端，在SQLyog上运行，推荐在SQLyog等客户端上运行，效果展示的更好
登录mycat

[root@mycat1 ~]# mysql -uroot -p123456 -P8066 -h 192.168.107.42

添加只读数据源slave1

/*+ mycat:createDataSource{ "name":"r1",
"url":"jdbc:mysql://192.168.107.180:3306/test1?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true", "user":"mycat2",
"password":"123456" } */;

添加只读数据源slave2

/*+ mycat:createDataSource{ "name":"r2",
"url":"jdbc:mysql://192.168.107.181:3306/test1?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true", "user":"mycat2",
"password":"123456" } */;

添加读写数据源master

以通过vip地址接收写请求，所以数据源ip写vip地址192.168.107.66

/*+ mycat:createDataSource{ "name":"rw",
"url":"jdbc:mysql://192.168.107.66:3306/test1?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true", "user":"mycat2",
"password":"123456" } */;

查询配置数据源结果

root@(none) 22:12  mysql>/*+ mycat:showDataSources{} */\G;

创建集群信息

/*!mycat:createCluster{"name":"cluster","masters":["rw"],"replicas":["r1","r2"]} */;

查看集群信息

/*+ mycat:showClusters{} */;

7. 重启mycat

7.1 查看逻辑库真实数据库同步效果

[root@mycat1 schemas]# mysql -uroot -p123456 -P8066 -h 192.168.107.42

可以看到mycat逻辑库test1已经成功关联到了后端真实mysql数据库

7.2 为了让读写分离的效果更明显，逐步关闭从节点的主从复制

slave2 停止主从复制

root@test1 18:11  mysql>stop slave;

master 插入数据

root@test1 18:16  mysql>insert into t1 values(5,'ko');

slave1 停止主从复制

root@test1 18:16  mysql>insert into t1 values(5,'ko');

master 再插入数据

root@test1 18:16  mysql>insert into t1 values(5,'ko');

登录mycat客户端，查询t1表数据

root@test1 18:16  mysql>select * from t1;

可以看到，时而看到两条数据，时而没有数据，时而看到一条数据，说明读取数据的时候分布到后端的三台服务器上，实现了读写分离。

发现数据再不断的变化，这就是读请求的负载均衡效果！

对mycat逻辑库test1的读操作，负载均衡的将读请求转发到了后端3个真实数据库

而对mycat逻辑库test1的写操作，只会转发到后端的主服务器上

五、keepalived对两台读写分离器配置两个vrrp实例实现双vip高可用功能

1. 安装部署keepalived

下面的操作两台mycat服务器都要操作，这里只展示一台

[root@mycat2 ~]# yum install -y epel-release #安装epel源
[root@mycat2 ~]# yum install -y keepalived #安装keepalived

2. 修改配置文件/etc/keepalived/keepalived.conf

mycat1

[root@mycat1 ~]# cat /etc/keepalived/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广播
   vrrp_garp_interval 0
   vrrp_gna_interval 0
}

vrrp_instance VI_1 {
    state MASTER #选为master
    interface ens33  #绑定本机网卡ens33
    virtual_router_id 88 #虚拟路由器id，两个实例一定不能一样，否则会出现脑裂现象
    priority 150 #优先级
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.107.188 #vip地址
    }
}

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

mycat2

[root@mycat2 ~]# cat /etc/keepalived/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 BACKUP
    interface ens33
    virtual_router_id 88
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.107.188
    }
}

vrrp_instance VI_2 {
    state MASTER
    interface ens33
    virtual_router_id 99
    priority 150
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.107.199
    }
}

3. 重启keepalived，查看vip地址

[root@mycat1 ~]# service keepalived restart

4. 查看效果

5. 模拟主机宕机，观察vip漂移

mycat1停止keepalived

[root@mycat1 ~]# service keepalived stop
Redirecting to /bin/systemctl stop keepalived.service

查看mycat2的vip地址

发现mycat1的vip地址188漂移到了mycat2上

重启keepalived，发现vip地址188又漂回了mycat1上

6. 做mycat的健康检查功能，当本机的mycat挂了之后，本机的master优先级自动降低，vip飘到另一台mycat上

两台机器上都要操作

6.1 编写监控mycat的脚本

[root@mycat1 ~]# vim check_mycat.sh
#!/bin/bash


if ps -ef | grep mysql | grep -v grep;then
        exit 0
else
        exit 1
fi

因为mycat是随着mysql服务启动的，这里直接监控mysql即可

该命令会列出所有正在运行的进程，并使用grep过滤出包含"mycat"的进程，然后再使用grep -v grep过滤掉grep本身的进程。如果此命令没有任何输出，则表示mycat没有运行。如果有输出，则表示mycat正在运行。

6.2 给监控脚本添加执行权限

[root@mycat1 ~]# chmod +x check_mycat.sh

6.3 在keepalived配置文件里定义监控脚本和使用监控脚本

定义监控脚本chk_mycat

#定义监控脚本chk_mycat
vrrp_script chk_mycat {
#当脚本/root/check_mycat.sh脚本执行返回值为0的时候，不执行下面的weight  -100的操作，只有脚本执行失败，返回值非0的时候，就执行执行权重值减30的操作
script "/root/check_mycat.sh"
interval 1
weight -100
}

调用监控脚本，要用在vrrp实例里面

用在是master的那个实例里，两台机器都要操作

vrrp_instance VI_1 {
    state MASTER
    interface ens33
    virtual_router_id 88
    priority 150
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.107.188
    }
#调用监控脚本
track_script {
chk_mycat
}

}

7. 重启keepalived，查看效果

[root@mycat1 ~]# service keepalived start
Redirecting to /bin/systemctl start keepalived.service

7.1 将mycat1的mycat服务关掉，观察vip是否会漂移到另一台机器上

因为mycat是随着mysql启动的，这里直接把mysql关掉

[root@mycat1 ~]# service mysqld stop

可见，当mycat挂掉的时候，vip自动飘到另一台服务器上，健康检查功能配置生效。当mycat重新启动，vip会自动飘回来

六、搭建DNS服务器，增添两条负载均衡记录，实现对mycat2双vip地址的DNS负载均衡

1. 安装

[root@dns ~]# systemctl disable firewalld #关闭防火墙，防止windows客户机无法访问dns服务器 
[root@dns ~]# systemctl disable NetworkManager #关闭NetworkManager
[root@dns ~]# yum install bind* -y  #安装dns服务的软件包
[root@dns ~]# service named start #启动dns服务
[root@dns ~]# systemctl enable named  #开机启动dns服务

2. 设置域名解析的文件

[root@dns etc]# vim /etc/resolv.conf
# Generated by NetworkManager
#nameserver 114.114.114.114


nameserver 127.0.0.1

验证是否搭建成功

[root@dns etc]# nslookup
> www.baidu.com
Server:		127.0.0.1
Address:	127.0.0.1#53

Non-authoritative answer:
www.baidu.com	canonical name = www.a.shifen.com.
Name:	www.a.shifen.com
Address: 14.119.104.189
Name:	www.a.shifen.com
Address: 14.119.104.254

进入nslookup的交互模式，在本机联网的基础上搜索任意域名，若显示为本机的dns服务器地址以及端口号解析的相关域名，则说明搭建成功。

3. 修改dns配置文件，任意ip可以访问本机的53端口，并且允许dns解析。

[root@dns ~]# vim /etc/named.conf
listen-on port 53 { any; };#允许任意ip访问53端口
        listen-on-v6 port 53 { any; }; 
        directory       "/var/named";
        dump-file       "/var/named/data/cache_dump.db";
        statistics-file "/var/named/data/named_stats.txt";
        memstatistics-file "/var/named/data/named_mem_stats.txt";
        recursing-file  "/var/named/data/named.recursing";
        secroots-file   "/var/named/data/named.secroots";
        allow-query     { any; }; #允许任意dns解析

4. 搭建主域名服务器

4.1 编辑dns次要配置文件/etc/named.rfc1912.zones，增加一条主域名记录

zone "zx.top" IN {
        type master; #类型为主域名
        file "zx.top.zone"; #zx.top域名的数据文件，需要去/var/named/下创建
        allow-update { none; }; 
};

4.2 创建zx.top主域名的数据文件

[root@dns ~]# cd /var/named/
[root@dns named]# cp -a named.localhost zx.top.zone

4.3 编辑数据文件，增加两条负载均衡记录。

[root@dns named]# vim zx.top.zone
$TTL 1D
@       IN SOA  @ rname.invalid. (
                                        0       ; serial
                                        1D      ; refresh
                                        1H      ; retry
                                        1W      ; expire
                                        3H )    ; minimum
        NS      @
        A       192.168.107.41 #默认指向本机ip地址
www IN  A       192.168.107.188 #负载均衡记录指向mycat1的vip地址
www IN  A       192.168.107.199 #负载均衡记录指向mycat2的vip地址

4.4 刷新dns服务

[root@dns named]# service named restart

5. 修改另外一台服务器的域名解析地址为搭建的dns服务器192.168.107.41

[root@mycat2 ~]# vim /etc/resolv.conf
# Generated by NetworkManager
#nameserver 114.114.114.114

nameserver 192.168.107.41

6. 效果测试

[root@mycat2 etc]# ping www.zx.top
PING www.zx.top (192.168.107.188) 56(84) bytes of data.
64 bytes from 192.168.107.188 (192.168.107.188): icmp_seq=1 ttl=64 time=0.231 ms
64 bytes from 192.168.107.188 (192.168.107.188): icmp_seq=2 ttl=64 time=2.21 ms
^C
--- www.zx.top ping statistics ---
2 packets transmitted, 2 received, 0% packet loss, time 1009ms
rtt min/avg/max/mdev = 0.231/1.220/2.210/0.990 ms
[root@mycat2 etc]# ping www.zx.top
PING www.zx.top (192.168.107.199) 56(84) bytes of data.
64 bytes from 192.168.107.199 (192.168.107.199): icmp_seq=1 ttl=64 time=0.236 ms
64 bytes from 192.168.107.199 (192.168.107.199): icmp_seq=2 ttl=64 time=1.06 ms
64 bytes from 192.168.107.199 (192.168.107.199): icmp_seq=3 ttl=64 time=0.514 ms
^C
--- www.zx.top ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2014ms
rtt min/avg/max/mdev = 0.236/0.606/1.069/0.346 ms

同一域名解析除了中间件mycat的双vip地址，实现了基于dns的负载均衡

七、使用mha实现MySQL集群的故障切换和主从提升，保证集群的高效

1. 用ansible批量部署mha软件环境

编写主机清单，增添4个mha node节点ip地址，以及一个mha manager节点ip地址

[root@ansible ~]# vim /etc/ansible/hosts
[mha_manager]
192.168.107.46  #mha manager
[mha_node]
192.168.107.46  #mha manager
192.168.107.179  #master 
192.168.107.180  #slave1
192.168.107.181  #slave2

2. 编写一键安装mha node脚本和一键安装mha mangaer脚本

mha node脚本

[root@ansible ~]# vim onekey_install_mha_node.sh
#!/bin/bash
#查看可以安装或者已安装的rpm包，并且作缓存
yum list
#下载epel源
yum install epel-release --nogpgcheck -y
#下载依赖包
yum install -y perl-DBD-MySQL \
perl-Config-Tiny \
perl-Log-Dispatch \
perl-Parallel-ForkManager \
perl-ExtUtils-CBuilder \
perl-ExtUtils-MakeMaker \
perl-CPAN
#软件包mha4mysql-node-0.58.tar.gz放入/root目录下
cd ~
tar zxvf mha4mysql-node-0.58.tar.gz
cd mha4mysql-node-0.58
#编译安装
perl Makefile.PL
make && make install

mha manager脚本

因为在安装mha node时已经安装好了所有的依赖包，就可以直接进行解压编译安装了

[root@ansible ~]# cat onekey_install_mha_manager.sh 
#软件包mha4mysql-manager-0.58.tar.gz放入/root目录下
tar zxvf mha4mysql-manager-0.58.tar.gz
cd mha4mysql-manager-0.58
#编译安装
perl Makefile.PL
make && make install

3. 编写playbook，上传源码包到家目录下，调用本地脚本，远程安装部署mha相关软件环境

[root@ansible ~]# cd /etc/ansible
[root@ansible ansible]# ls
ansible.cfg  hosts  mysql_install.yaml  roles
[root@ansible ansible]# vim mha_install.yaml
- hosts: mha_node
  remote_user: root
  tasks: 
  - name: copy file
    copy: src=/root/mha4mysql-node-0.58.tar.gz dest=/root/
  - name: install mha_node
    script: /root/onekey_install_mha_node.sh
- hosts: mha_manager
  remote_user: root
  tasks:
  - name: copy file
    copy: src=/root/mha4mysql-manager-0.58.tar.gz dest=/root/
  - name: install mha_manager
    script: /root/onekey_install_mha_manager.sh

4. 执行playbook

[root@ansible ansible]# ansible-playbook mha_install.yaml

5. 确保mha和mysql集群所有服务器互相建立免密通道

参考上面的建立免密通道过程

6. 在搭建好的主从复制服务器里，配置mha相关信息

6.1 所有mysql服务器加入log_bin和log-slave-updates，并刷新配置文件

备注信息供提示用，使用时建议删除

[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
log_bin   #前面已经加了二进制文件的配置了
server_id = 1   #不同服务器不能一样
gtid-mode=ON
enforce-gtid-consistency=ON
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000
#主从切换时，主从服务器身份都可能对换，从服务器重演relay_log日志操作时，也写入自己的log_bin中
log-slave-updates   #加上这个  

[mysql]
auto-rehash
prompt=\u@\d \R:\m  mysql>

[root@master ~]# service mysqld restart
Shutting down MySQL............ SUCCESS! 
Starting MySQL. SUCCESS!

6.2 所有mysql服务器（master、slave1、slave2）将mysql命令和mysqlbinlog命令软链接到/usr/sbin，方便manager管理节点

这里只展示master操作，slave1和slave2操作一致

[root@master ~]# ln -s /usr/local/mysql/bin/mysql /usr/sbin/
[root@master ~]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/

6.3 所有mysql服务器新建允许manager访问的授权用户mha，密码123456

root@(none) 11:16  mysql>grant all on *.* to 'mha'@'192.168.107.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.03 sec)

root@(none) 00:17  mysql>select user,host from mysql.user;
+---------------+---------------+
| user          | host          |
+---------------+---------------+
| slave         | 192.168.107.% |
| mha           | 192.168.107.% |
| mysql.session | localhost     |
| mysql.sys     | localhost     |
| root          | localhost     |
+---------------+---------------+
5 rows in set (0.00 sec)

7. 在mha manager节点上配置好相关脚本、管理节点服务器

7.1 mha manager节点上复制相关脚本到/usr/local/bin下

[root@mha ~]# cd /usr/local/bin
[root@mha bin]# cp -rp /root/mha4mysql-manager-0.58/samples/scripts/ /usr/local/bin/
[root@mha bin]# ls
apply_diff_relay_logs  masterha_conf_host        masterha_stop
filter_mysqlbinlog     masterha_manager          purge_relay_logs
masterha_check_repl    masterha_master_monitor   save_binary_logs
masterha_check_ssh     masterha_master_switch    scripts
masterha_check_status  masterha_secondary_check
[root@mha bin]# cd scripts/
[root@mha scripts]# ls
master_ip_failover  master_ip_online_change  power_manager  send_report

-r 复制目录

-p 同时复制文件访问权限

7.2 复制自动切换时vip管理的脚本到/usr/local/bin下

[root@mha scripts]# cp /usr/local/bin/scripts/master_ip_failover /usr/local/bin/

7.3 修改master_ip_failover文件内容，配置vip（只配置vip相关参数，其他默认不修改）

[root@mha scripts]# >/usr/local/bin/master_ip_failover  #清空文件内容，复制以下内容
[root@mha scripts]# vim /usr/local/bin/master_ip_failover 
#!/usr/bin/env perl
use strict;
use warnings FATAL => 'all';

use Getopt::Long;

my (
$command, $ssh_user, $orig_master_host, $orig_master_ip,
$orig_master_port, $new_master_host, $new_master_ip, $new_master_port
);
#############################添加内容部分#########################################
my $vip = '192.168.107.66';								#指定vip的地址，自己指定
my $brdc = '192.168.107.255';								#指定vip的广播地址
my $ifdev = 'ens33';										#指定vip绑定的网卡
my $key = '1';												#指定vip绑定的虚拟网卡序列号
my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip";		#代表此变量值为ifconfig ens33:1 192.168.31.200
my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down";		#代表此变量值为ifconfig ens33:1 192.168.31.200 down
my $exit_code = 0;											#指定退出状态码为0
#my $ssh_start_vip = "/usr/sbin/ip addr add $vip/24 brd $brdc dev $ifdev label $ifdev:$key;/usr/sbin/arping -q -A -c 1 -I $ifdev $vip;iptables -F;";
#my $ssh_stop_vip = "/usr/sbin/ip addr del $vip/24 dev $ifdev label $ifdev:$key";
##################################################################################
GetOptions(
'command=s' => \$command,
'ssh_user=s' => \$ssh_user,
'orig_master_host=s' => \$orig_master_host,
'orig_master_ip=s' => \$orig_master_ip,
'orig_master_port=i' => \$orig_master_port,
'new_master_host=s' => \$new_master_host,
'new_master_ip=s' => \$new_master_ip,
'new_master_port=i' => \$new_master_port,
);

exit &main();

sub main {

print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";

if ( $command eq "stop" || $command eq "stopssh" ) {

my $exit_code = 1;
eval {
print "Disabling the VIP on old master: $orig_master_host \n";
&stop_vip();
$exit_code = 0;
};
if ($@) {
warn "Got Error: $@\n";
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "start" ) {

my $exit_code = 10;
eval {
print "Enabling the VIP - $vip on the new master - $new_master_host \n";
&start_vip();
$exit_code = 0;
};
if ($@) {
warn $@;
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "status" ) {
print "Checking the Status of the script.. OK \n";
exit 0;
}
else {
&usage();
exit 1;
}
}
sub start_vip() {
`ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}
## A simple system call that disable the VIP on the old_master
sub stop_vip() {
`ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
}

sub usage {
print
"Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";
}

7.4 创建 MHA 软件目录并复制配置文件，使用app1.cnf配置文件来管理 mysql 节点服务器，配置文件一般放在/etc/目录下

注意：注释只是提示用，编辑配置文件时最好不要加注释，否则很可能会出错

[root@mha scripts]# mkdir /etc/masterha
[root@mha scripts]# cp /root/mha4mysql-manager-0.58/samples/conf/app1.cnf /etc/masterha/
[root@mha scripts]# cd /etc/masterha/
[root@mha masterha]# ls
app1.cnf
[root@mha masterha]# >app1.cnf #清空原有内容
[root@mha masterha]# vim app1.cnf 

[server default]
manager_log=/var/log/masterha/app1/manager.log　　　　　  #manager日志
manager_workdir=/var/log/masterha/app1.log　　　　		#manager工作目录
master_binlog_dir=/data/mysql/　　　　　　　　 　#master保存binlog的位置，这里的路径要与master里配置的binlog的路径一致，以便MHA能找到
master_ip_failover_script=/usr/local/bin/master_ip_failover　          　#设置自动failover时候的切换脚本，也就是上面的那个脚本
master_ip_online_change_script=/usr/local/bin/master_ip_online_change　　#设置手动切换时候的切换脚本
user=mha					#设置监控用户mha
password=123456			#设置mysql中mha用户的密码，这个密码是前文中创建监控用户的那个密码
ping_interval=1				#设置监控主库，发送ping包的时间间隔1秒，默认是3秒，尝试三次没有回应的时候自动进行failover
remote_workdir=/tmp			#设置远端mysql在发生切换时binlog的保存位置
repl_user=slave			#设置复制用户的用户slave
repl_password=123456		#设置复制用户slave的密码
report_script=/usr/local/send_report　　　　　#设置发生切换后发送的报警的脚本
secondary_check_script=/usr/local/bin/masterha_secondary_check -s 192.168.107.180 -s 192.168.107.181	#指定检查的从服务器IP地址
shutdown_script=""			#设置故障发生后关闭故障主机脚本（该脚本的主要作用是关闭主机防止发生脑裂,这里没有使用）
ssh_user=root				#设置ssh的登录用户名

[server1]
#master
hostname=192.168.107.179
port=3306

[server2]
#slave1
hostname=192.168.107.180
port=3306
candidate_master=1
#设置为候选master，设置该参数以后，发生主从切换以后将会将此从库提升为主库，即使这个主库不是集群中最新的slave

check_repl_delay=0
#默认情况下如果一个slave落后master 超过100M的relay logs的话，MHA将不会选择该slave作为一个新的master， 因为对于这个slave的恢复需要花费很长时间；通过设置check_repl_delay=0，MHA触发切换在选择一个新的master的时候将会忽略复制延时，这个参数对于设置了candidate_master=1的主机非常有用，因为这个候选主在切换的过程中一定是新的master

[server3]
#slave2
hostname=192.168.107.181
port=3306

8. master服务器上手工开启vip

[root@localhost ~]# ifconfig ens33:1 192.168.107.66/24
[root@localhost ~]# ip add
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:15:bc:43 brd ff:ff:ff:ff:ff:ff
    inet 192.168.107.179/24 brd 192.168.107.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet 192.168.107.66/24 brd 192.168.107.255 scope global secondary ens33:1
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:fe15:bc43/64 scope link 
       valid_lft forever preferred_lft forever

9. manager节点上测试ssh免密通道，如果正常最后会输出successfully

[root@mha masterha]# masterha_check_ssh -conf=/etc/masterha/app1.cnf
Mon May  8 11:50:00 2023 - [info] All SSH connection tests passed successfully.

如果报错，多看日志/var/log/masterha/app1/manager.log

注意是否每台mysql间都建立了ssh免密通道，否则会报错

根据提示信息，修复错误

10. 在 manager 节点上测试 mysql 主从连接情况，最后出现 MySQL Replication Health is OK 字样说明正常

[root@mha masterha]# masterha_check_repl -conf=/etc/masterha/app1.cnf
MySQL Replication Health is OK.

如果报错，多看日志/var/log/masterha/app1/manager.log

如果报错，思考是否软链接建立好了？或者主从复制搭建正确了

11. manager节点后台开启MHA

[root@mha masterha]# nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/masterha/app1/manager.log 2>&1 &
[1] 5085

11.1 查看 MHA 状态，可以看到当前的 master 是 Mysql1 节点

[root@mha masterha]# masterha_check_status --conf=/etc/masterha/app1.cnf
app1 (pid:5085) is running(0:PING_OK), master:192.168.107.179

11.2 查看MHA日志，看到当前matser是192.168.107.179

[root@mha masterha]# cat /var/log/masterha/app1/manager.log | grep "current master"
Mon May  8 11:57:07 2023 - [info] Checking SSH publickey authentication settings on the current master..
192.168.107.179(192.168.107.179:3306) (current master)

11.3 查看mha进程

[root@mha bin]# ps aux|grep manager
root       5085  0.1  4.5 299656 21992 pts/0    S    11:57   0:12 perl /usr/local/bin/masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover
root      14939  0.0  0.2 112824   984 pts/0    S+   14:39   0:00 grep --color=auto manager

至此，mha就搭建成功了，它会在master挂机之后把slave1提升为master，并让slave2也指向新的master。当原master恢复后，原master会变为新master的slave

八、Prometheus+mysqld_exporter+Grafana实现对MySQL集群以及读写分离器的系统资源监控

1. 安装部署mysqld_exporter

由于篇幅有限，我只写了master的操作，slave1、slave2、mycat1、mycat2服务器的操作我就不写了，配置基本是一致的；

1.1 所有被监控数据库服务器建立允许mysqld_exporter访问的授权用户

root@(none) 20:28  mysql>grant all on *.* to 'mysqld_exporter'@'%' identified by '123456';

1.2 上传软件包到linux家目录下

1.3 解压，并移动到/usr/local/mysqld_exporter下

[root@master ~]# tar -zxvf mysqld_exporter-0.12.1.linux-amd64\ \(1\).tar.gz 
mysqld_exporter-0.12.1.linux-amd64/
mysqld_exporter-0.12.1.linux-amd64/NOTICE
mysqld_exporter-0.12.1.linux-amd64/mysqld_exporter
mysqld_exporter-0.12.1.linux-amd64/LICENSE
[root@master ~]# ls
11.text              mha4mysql-node-0.58.tar.gz                  mysqld_exporter-0.12.1.linux-amd64             nohup.out
anaconda-ks.cfg      my.cnf                                      mysqld_exporter-0.12.1.linux-amd64 (1).tar.gz
mha4mysql-node-0.58  mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz  node_exporter-1.5.0.linux-amd64.tar.gz
[root@master ~]# mv mysqld_exporter-0.12.1.linux-amd64 /usr/local/mysqld_exporter
[root@master ~]# cd /usr/local/mysqld_exporter/
[root@master mysqld_exporter]# ls
LICENSE  mysqld_exporter  NOTICE

1.4 在/usr/local/mysqld_exporter下编辑连接本机数据库的配置文件

注意：在配置mycat授权用户的时候，记得要登录mycat的客户端而不是mysql的，因为要监控的是mycat

[root@slave1 mysqld_exporter]# vim my.cnf
[client]
user=mysqld_exporter 
password=123456

user=mysqld_exporter #监控本机数据库的授权用户

password=123456 #密码

1.5 将mysqld_exporter命令加入环境变量，并设置开机自启

[root@master mysqld_exporter]# PATH=/usr/local/mysqld_exporter/:$PATH
[root@master mysqld_exporter]# echo "PATH=/usr/local/mysqld_exporter/:$PATH" >>/root/.bashrc

1.6 后台启动

[root@master mysqld_exporter]# nohup mysqld_exporter --config.my-cnf=/usr/local/mysqld_exporter/my.cnf &

1.7 看进程，看端口，网页访问测试

[root@master ~]# ps aux|grep exporter

[root@master ~]# netstat -anplut|grep mysqld

访问测试

2. 安装prometheus，配置成服务

2.1 上传安装包到linux家目录下

2.2 解压

[root@prometheus ~]# tar zvxf prometheus-2.43.0.linux-amd64.tar.gz
[root@prometheus ~]# mv prometheus-2.43.0.linux-amd64 /prometheus

2.3 永久修改PATH变量，添加prometheus的路径

[root@prometheus prometheus]# export PATH=$PATH:/prometheus
[root@prometheus prometheus]# vim /root/.bashrc
# .bashrc

# User specific aliases and functions

alias rm='rm -i'
alias cp='cp -i'
alias mv='mv -i'

# Source global definitions
if [ -f /etc/bashrc ]; then
        . /etc/bashrc
fi

export PATH=$PATH:/prometheus
[root@prometheus prometheus]# which prometheus
/prometheus/prometheus   #这个路径等下制作成服务要用

2.3 启动服务

[root@prometheus prometheus]# cd /prometheus
[root@prometheus prometheus]# ls
console_libraries  consoles  data  LICENSE  nohup.out  NOTICE  prometheus promtool
[root@prometheus prometheus]# vim prometheus.yaml
global:
  scrape_interval:     15s
  evaluation_interval: 15s

scrape_configs:
  - job_name: 'prometheus'
    scrape_interval: 5s
    static_configs:
      - targets: ['localhost:9090']

[root@prometheus prometheus]# nohup prometheus  --config.file=/prometheus/prometheus.yml &

2.4 测试访问

查看端口

访问页面

2. 4 将prometheus做成服务进行管理

[root@prometheus prometheus]# vim /usr/lib/systemd/system/prometheus.service
[Unit]
Description=Prometheus
Wants=network-online.target
After=network-online.target

[Service]
ExecStart=/prometheus --config.file=/prometheus/prometheus.yml
ExecReload=/bin/kill -HUP $MAINPID
KillMode=process

[Install]
WantedBy=default.target

[root@prometheus prometheus]# systemctl daemon-reload   #重新加载systemd中的配置文件
[root@prometheus prometheus]# ps aux|grep prometheus
root      11681  0.1  6.0 799468 60192 pts/1    Sl   23:32   0:00 prometheus --config.file=/prometheus/prometheus.yml
root      11720  0.0  0.0 112824   976 pts/1    R+   23:37   0:00 grep --color=auto prometheus
[root@prometheus prometheus]# kill -9 11681   先杀死使用nohup命令启动的进程，再使用service方式管理prometheus
[root@prometheus prometheus]# ps aux|grep prometheus
root      11722  0.0  0.0 112824   976 pts/1    R+   23:38   0:00 grep --color=auto prometheus
[1]+  已杀死               nohup prometheus --config.file=/prometheus/prometheus.yml
[root@prometheus prometheus]# ps aux|grep prometheus
root      11724  0.0  0.0 112824   976 pts/1    R+   23:38   0:00 grep --color=auto prometheus
[root@prometheus prometheus]# 
[root@prometheus prometheus]# service prometheus start 启动服务

3. Prometheus添加node节点

[root@prometheus prometheus]# vim prometheus.yml
scrape_configs:
  - job_name: "prometheus"
    static_configs:
      - targets: ["localhost:9090"]
  - job_name: "mycat1"
    static_configs:
      - targets: ["192.168.107.42:9104"]
  - job_name: "mycat2"
    static_configs:
      - targets: ["192.168.107.43:9104"]
  - job_name: "slave1"
    static_configs:
      - targets: ["192.168.107.180:9104"]
  - job_name: "slave2"
    static_configs:
      - targets: ["192.168.107.181:9104"]
  - job_name: "master"
    static_configs:
      - targets: ["192.168.107.179:9104"]

访问测试

成功

4. 安装grafana

4.1 上传rpm包

4.2 安装

[root@prometheus ~]# yum install -y grafana-enterprise-9.1.2-1.x86_64.rpm

4.3 启动

[root@prometheus ~]# service grafana-server start

4.4 访问测试

查看端口，为3000

页面访问，第一次登录账号密码默认都为admin

4.5 增添Prometheus数据源

14057模板比较美观，出图效果也比较好，推荐使用

可以到这个网站去找模板

Dashboards | Grafana Labs

4.6 出图效果

可以自行选择要看的资源的服务器

九、使用压力测试软件（sysbench）测试整个MySQL集群的性能（cpu、IO、内存等）

1. 安装部署sysbench

[root@sysbench ~]# yum install -y epel-release
[root@sysbench ~]# yum install -y sysbench

2. 调大内核资源限制

[root@sysbench ~]# ulimit -n 100000
[root@sysbench ~]# ulimit -u 100000
[root@sysbench ~]# ulimit -s 100000

3. 写请求压力测试

3.1 模拟1000个客户10秒内持续写入

数据准备

[root@sysbench ~]# sysbench --threads=1000 --time=10 --report-interval=5 --db-driver=mysql --mysql-user=root --mysql-password=123456 --mysql-port=8066 --mysql-host=192.168.107.188 --mysql-db=test1 oltp_insert prepare

–threads：并发数，这里模拟的是1000个客户端；
–time：测试时间，单位为秒；
–report-interval：每隔几秒输出一次详细结果；
–db-driver：测试的数据库，可以是mysql，postgresql等，
–mysql-user：数据库用户（因为这里测试入口是mycat，所以也创建也mycat用户）；
–mysql-password：数据库密码；
–mysql-port：数据库端口号；
–mysql-host：数据库ip地址；
–mysql-db：针对哪个库进行测试（这里我使用的之前建立的逻辑库test1）；
oltp_insert：测试的sql语句类型，因为场景为高并发写入，肯定是insert语句，所以选择oltp_insert

测试

[root@sysbench ~]# sysbench --threads=1000 --time=60 --report-interval=5 --db-driver=mysql --mysql-user=root --mysql-password=123456 --mysql-port=8066 --mysql-host=192.168.107.188 --mysql-db=test1 oltp_insert run

测试结果

关键指标：

tps：接收、处理的tps数量（tps是一个完成的请求，即：客户端发送一个请求，后端处理并正确返回给客户端是一个完整的tps）*
qps：接收、处理的qps数量（qps代表的是服务端接收的能力，客户端发送一个请求，就是一个qps，或者说是一个查询请求；）

最大tps：3485.09 最大qps：3485.09

清空测试数据

[root@sysbench ~]# sysbench --threads=1000 --time=60 --report-interval=5 --db-driver=mysql --mysql-user=root --mysql-password=123456 --mysql-port=8066 --mysql-host=192.168.107.188 --mysql-db=test1 oltp_insert cleanup

3.2 模拟3000个客户10秒内持续写入

测试结果
最大tps：4486.61 最大qps：4488.39

4. IO性能压力测试

mycat1主机安装sysbench工具

[root@mycat1 ~]# yum install -y epel-release
[root@mycat1 ~]# yum install -y sysbench

创建5个文件，总共2G，每个文件大概400M

准备数据

[root@mycat1 ~]# sysbench fileio --file-num=5 --file-total-size=2G prepare

测试

[root@mycat1 ~]# sysbench --events=5000 --threads=16 fileio --file-num=5 --file-total-size=2G --file-test-mode=rndrw --file-fsync-freq=0 --file-block-size=16384 run

测试结果

5. cpu性能压力测试

[root@mycat1 ~]# sysbench cpu --threads=40 --events=10000 --cpu-max-prime=20000 run

至此，整个项目就做完了！

本项目的遇到的问题

1.主从复制时，只在slave上进行了操作，导致事务数比主服务器还要多，主从复制一直起不来

是一个MySQL GTID主从模式下断链出现error 1236的错误

参考这篇文章解决：

https://zhuanlan.zhihu.com/p/509461346

2.dns配置效果不明显，在不同的机器上解析出vip1和vip2

3.使用ansible调用一键二进制安装脚本中mysql加入环境变量不成功
直接在客户端添加

4. 配置文件打错导致服务器不来

一定要好好检查配置文件是否有错误，最好不要在配置文件里加注释

项目心得

提前规划好整个集群的架构，可以提高项目开展时效率
运行报错，多看出错信息提示以及日志，帮助很大
对基于GTID+半同步的主从复制有了更深入的理解
对keepalived的脑裂和vip漂移现象也有了更加深刻的体会和分析
加强了mha架构中自动failover的实现原理的理解
对基于mycat2的读写分离过程更加的熟悉
一定要细心检查配置文件有无错误
熟练了sysbench下的压力测试，认识到了系统性能资源的重要性，对压力测试下整个集群的瓶颈有了一个整体概念
对dns负载均衡理解更加深刻
ansible使用更熟练了
对监控也有了的更进一步的认识，监控可以提前看到问题，做好预警
对很多软件之间的配合有了一定的理解，如mycat2、mha、mysql等
trouble shooting的能力得到了提升

宛如清风03

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
基于mha+mycat2+gtid的半同步主从复制双vip高可用MySQL集群

基于mha+mycat2+gtid的半同步主从复制双vip高可用MySQL集群11台Linux服务器构建一个高可用能读写分离的高效MySQL集群，处理大并发的后端MySQL业务。该项目使用gitd实现半同步主从复制，mha实现MySQL主从自动切换，引入了mycat2实现读写分离，利用keepalived对mycat2服务器做双vip架构，再用dns做负载均衡，prometheus+Grafana对MySQL集群监控和出图，最后用sysbench做压力测试。
复制链接

扫一扫