双vip高可用的MySQL集群

项目介绍

本项目旨在构建一个高度可靠、自动化且高性能的MySQL数据库集群系统，通过集成Keepalived的高可用功能和MySQL的半同步复制技术，以及GTID（全局事务标识符）来保证数据的一致性和服务的连续性。该系统不仅支持读写分离以优化性能，还具备自动故障切换能力，确保在任何单点故障发生时都能迅速恢复服务。

项目架构

项目环境

8台服务器（2G，2核），centos7.8，mysql5.7.30，mysqlrouter8.0.21，keepalived2.0.10，prometheus，ansible，rsync，sersync，sysbench

项目步骤

环境准备

主机	IP地址	描述
ansible	192.168.163.61	中台服务器，管理和监控mysql集群
master	192.168.163.62	mysql集群主服务器
slave-1	192.168.163.63	mysql集群从服务器
slave-2	192.168.163.64	mysql集群从服务器
slave-3	192.168.163.65	mysql集群延迟备份服务器
mysqlrouter-1	192.168.163.66	实现mysql集群读写分离
mysqlrouter-2	192.168.163.67	mysql集群读写分离的备用服务器
test-client	192.168.163.68	sysbench压力测试mysql集群

首先准备上述八台服务器，关闭防火墙和SELinux，仅需开启对外服务器。

Ansible服务器部署

1、安装Ansible

// 安装ansible软件，需要先安装epel源
[root@ansible ~] yum install epel-release -y
[root@ansible ~] yum install ansible -y

2、配置免密登录

// 生成RSA密钥
[root@ansible ~] ssh-keygen

// 上传公钥到需要管理的服务器上
[root@ansible ~] ssh-copy-id -i .ssh/id_rsa.pub root@192.168.163.62

// 验证免密通道是否建立成功
[root@ansible ~] ssh 'root@192.168.163.62'

将ansible服务器与每台服务器都配置免密登录。

3、修改Ansible的主机清单

在ansible的host文件(/etc/ansible/hosts)中添加需要管理的服务器IP地址，没有配置免密登录的服务器则需要指明用户名和密码。需要注意的是，使用密码登录的服务器在使用ansible控制之前要先使用ssh登录一次。

[root@ansible ~] vim /etc/ansible/hosts
# 密码登录示例
# [web]
# 192.168.163.63 ansible_ssh_user='root' ansible_ssh_pass='123456'
# 密钥登录
[db]
192.168.163.62
192.168.163.63
192.168.163.64
192.168.163.65
[slaves]
192.168.163.63
192.168.163.64
192.168.163.65
[mysqlrouter]
192.168.163.66
192.168.163.67

Prometheus部署

Prometheus 是一个开源的监控软件，监控某台主机的cpu、内存、磁盘、网络流量等信息。

1、下载软件包

Prometheus官网下载地址：https://prometheus.io/download/
下载如下软件包到服务器root用户家目录下：

• node_exporter：安装到被监控服务器上，用于采集通用性能指标。
• mysql_exporter：安装到被监控服务器上，用于采集mysql的性能指标。
• prometheus：服务端软件，用于接收exporter采集到的数据

// 将下载好的软件包使用ansible分发到其他服务器上
[root@ansible ~] ansible db -m copy -a "src=/root/node_exporter-1.4.0-rc.0.linux-amd64.tar.gz dest=/root/node_exporter-1.4.0-rc.0.linux-amd64.tar.gz"
[root@ansible ~] ansible mysqlrouter -m copy -a "src=/root/mysqld_exporter-0.15.1.linux-amd64.tar.gz dest=/root/mysqld_exporter-0.15.1.linux-amd64.tar.gz"

2、二进制安装Prometheus Server

// 解压prometheus server
[root@ansible ~] mkdir /prome
[root@ansible ~] cp prometheus-2.43.0.linux-amd64.tar.gz /prome/
[root@ansible ~] cd /prome
[root@ansible prome] tar xf prometheus-2.43.0.linux-amd64.tar.gz
[root@ansible prome] mv prometheus-2.43.0.linux-amd64 prometheus
[root@ansible prometheus] cd prometheus

// 永久修改PATH变量
[root@ansible prometheus] vim /root/.bashrc
末尾追加：PATH=/prome/prometheus:$PATH

// 后台启动prometheus server
[root@ansible prometheus] nohup prometheus --config.file=/prome/prometheus/prometheus.yml &

3、通过服务管理Prometheus

因为systemctl方式默认不会去管理我们源码二进制安装的程序，因此这里将Prometheus做成一个服务来进行管理，方便之后的操作。

[root@ansible prometheus] vim /usr/lib/systemd/system/prometheus.service
# 添加下面内容
[Unit]
Description=prometheus
[Service]
ExecStart=/prome/prometheus/prometheus --config.file=/prome/prometheus/prometheus.yml
ExecReload=/bin/kill -HUP $MAINPID
KillMode=process
Restart=on-failure
[Install]
WantedBy=multi-user.target

// 重新加载systemd相关的服务
[root@ansible prometheus] systemctl daemon-reload

// 设置开机启动
[root@ansible prometheus] systemctl enable prometheus

浏览器访问192.168.163.61:9090出现界面，即安装运行成功。

4、安装node_exporter

由于被监控服务器均需要安装node_exporter，通过编写脚本install_node_exporter.sh来实现node_exporter的安装

#!/bin/bash
# 解压，永久修改环境变量
tar xf /root/node_exporter-1.4.0-rc.0.linux-amd64.tar.gz  -C /
cd  /
mv node_exporter-1.4.0-rc.0.linux-amd64/ node_exporter
cd /node_exporter/
echo 'PATH=/node_exporter/:$PATH' >>/etc/profile
#生成nodeexporter.service文件
cat >/usr/lib/systemd/system/node_exporter.service <<EOF
[Unit]
Description=node_exporter
[Service]
ExecStart=/node_exporter/node_exporter --web.listen-address 0.0.0.0:9090 
ExecReload=/bin/kill -HUP $MAINPID
KillMode=process
Restart=on-failure
[Install]
WantedBy=multi-user.target
EOF
#让systemd进程识别node_exporter服务
systemctl daemon-reload
#设置开机启动
systemctl enable node_exporter
#启动node_exporter
systemctl start node_exporter

通过Ansible将安装脚本分发给被监控服务器。

// 传输脚本
[root@ansible prometheus] ansible db -m copy -a "src=install_node_exporter.sh dest=/root"
[root@ansible prometheus] ansible mysqlrouter -m copy -a "src=install_node_exporter.sh dest=/root"

// 执行脚本
[root@ansible prometheus] ansible db -m shell -a "bash /root/install_node_exporter.sh"
[root@ansible prometheus] ansible mysqlrouter -m shell -a "bash /root/install_node_exporter.sh"

5、安装mysqld_exporter

首先在master服务器上mysql程序中创建新的mysql用户

// 在mysql服务器上进行操作
root@(none) mysql> create user 'exporter'@'%' identified by '123456' with MAX_USER_CONNECTIONS 3;
root@(none) mysql> grant process,replication client,select on *.* to 'exporter'@'%';

通过编写脚本install_mysqld_exporter.sh来实现mysqld_exporter的安装：

#!/bin/bash
#解压
tar xf mysqld_exporter-0.15.1.linux-amd64.tar.gz -C /usr/local
cd /usr/local/
mv mysqld_exporter-0.15.1.linux-amd64 mysqld_exporter
cd mysqld_exporter/
#配置mysqld_exporter
cat >.my.cnf <<EOF
[client]
user=exporter
password=123456
EOF
#生成nodeexporter.service文件
cat >/usr/lib/systemd/system/node_exporter.service <<EOF
[Unit]
Description=mysqld_exporter
[Service]
Environment=DATA_SOURCE_NAME=exporter:123456@(localhost:3306)/
ExecStart=/usr/local/mysqld_exporter/mysqld_exporter --config.my-cnf=/usr/local/mysqld_exporter/.my.cnf --web.listen-address=:9104
Restart=on-failure
[Install]
WantedBy=multi-user.target
EOF
#让systemd进程识别node_exporter服务
systemctl daemon-reload
#设置开机启动
systemctl enable mysqld_exporter
#启动node_exporter
systemctl start mysqld_exporter

通过Ansible将安装脚本分发给被监控服务器。

// 传输脚本
[root@ansible prometheus] ansible db -m copy -a "src=install_mysqld_exporter.sh dest=/root"

// 执行脚本
[root@ansible prometheus] ansible db -m shell -a "bash /root/install_mysqld_exporter.sh"

6、添加被监控的服务器

[root@ansible prometheus] vim prometheus.yml
# 找到最后一段，修改成下面这样，注意yml格式缩进
scrape_configs:
  - job_name: "prometheus-node"
    static_configs:
      - targets: ["192.168.163.61:9090"]
  - job_name: "master-node"
    static_configs:
      - targets: ["192.168.163.62:9090"]
  - job_name: "slave-1-node"
    static_configs:
      - targets: ["192.168.163.63:9090"]
  - job_name: "slave-2-node"
    static_configs:
      - targets: ["192.168.163.64:9090"]
  - job_name: "slave-3-node"
    static_configs:
      - targets: ["192.168.163.65:9090"]
  - job_name: "master-mysql"
    static_configs:
      - targets: ["192.168.163.62:9104"]
  - job_name: "slave-1-mysql"
    static_configs:
      - targets: ["192.168.163.63:9104"]
  - job_name: "slave-2-mysql"
    static_configs:
      - targets: ["192.168.163.64:9104"]
  - job_name: "slave-3-mysql"
    static_configs:
      - targets: ["192.168.163.65:9104"]
  - job_name: "mysqlrouter-1-node"
    static_configs:
      - targets: ["192.168.163.66:9090"]
  - job_name: "mysqlrouter-2-node"
    static_configs:
      - targets: ["192.168.163.67:9090"]

// 重启服务
[root@ansible prometheus] systemctl restart prometheus

部署MySQL集群（基于GTID的半同步）

1、安装MySQL

将mysql软件包和安装脚本上传到ansible服务器上，使用ansible服务器批量为服务器安装mysql程序。

// script默认在家目录下运行，需要将软件包也放在家目录，或者修改脚本cd进入到软件包所在目录
[root@ansible ~] ansible db -m copy -a "src=[软件包] dest=[目的地目录]"
[root@ansible ~] ansible db -m script -a "[脚本路径]"

mysql安装脚本：

#!/bin/bash
#解决软件的依赖关系并且安装需要工具
yum  install cmake ncurses-devel gcc  gcc-c++  vim libaio lsof bzip2 openssl-devel ncurses-compat-libs net-tools -y
#解压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用户启动的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}')
#修改PATH变量，加入mysql bin目录的路径
#临时修改PATH变量的值
export 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 mysql>
EOF
#修改内核的open file的数量
ulimit -n 1000000
#设置开机启动的时候也配置生效
echo "ulimit -n 1000000" >>/etc/rc.local
chmod +x /etc/rc.d/rc.local
#将mysqld添加到linux系统里服务管理名单里
/sbin/chkconfig --add mysqld
#设置mysqld服务开机启动
/sbin/chkconfig mysqld on
#启动mysqld进程
service mysqld start
#初次修改密码需要使用--connect-expired-password 选项
#-e 后面接的表示是在mysql里需要执行命令  execute 执行
#set password='Sanchuang123#';  修改root用户的密码为Sanchuang123#
mysql -uroot -p$tem_passwd --connect-expired-password   -e  "set password='Sanchuang123#';"
#检验上一步修改密码是否成功，如果有输出能看到mysql里的数据库，说明成功。
mysql -uroot -p'Sanchuang123#'  -e "show databases;"

2、安装MySQL半同步主从复制插件

在master服务器上安装semisync_master插件，在mysql内进行安装，而不是在Shell中安装。

root@(none) mysql>INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

在slave服务器上安装semisync_slave插件。

root@(none) mysql>INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

3、添加MySQL授权用户

给master服务器和slave-1服务器中的mysql添加用户，允许其余服务器来拿取二进制日志进行同步。由于slave-3延迟服务器是去slave-1中获取二进制日志的，所以slave-1服务器也需要添加mysql授权用户。

// master服务器上操作
root@(none) mysql> grant replication slave on *.* to 'panmq'@'192.168.163.%' identified by '123456';

// slave-1服务器上操作
root@(none) mysql> grant replication slave on *.* to 'panmq'@'192.168.163.65' identified by '123456';

注：在mysql5.7版本之后不支持创建用户同时创建密码。

4、修改MySQL主从服务器配置文件

mysql的配置文件为：/etc/my.cnf
修改master服务器中mysql服务端配置如下：

[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
# 开启半同步主从复制
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000
# 开启GTID功能
gtid-mode=ON
enforce-gtid-consistency=ON

修改slave-1服务器中mysql服务端配置如下。log_slave_updates=ON选项用于提供二进制日志给slave-3延迟服务器同步。

[mysqld]
socket=/data/mysql/mysql.sock
port = 3306
open_files_limit = 8192
innodb_buffer_pool_size = 512M
character-set-server=utf8
# 开启通用日志和慢日志
general_log
slow_query_log=1
long_query_time=0.001
# 开启二进制日志
log_bin
server_id = 2
expire_logs_days = 15
# 开启GTID功能
gtid-mode=ON
enforce-gtid-consistency=ON
# 保留同步时使用的二进制日志
log_slave_updates=ON
# 开启半同步主从复制
rpl_semi_sync_slave_enabled=1

slave-2服务器和slave-3服务器中mysql服务端配置一样，修改如下。需要注意的时server_id选项不能一样。

[mysqld]
socket=/data/mysql/mysql.sock
port = 3306
open_files_limit = 8192
innodb_buffer_pool_size = 512M
character-set-server=utf8
# 开启通用日志和慢日志
general_log
slow_query_log=1
long_query_time=0.001
# 开启二进制日志
log_bin
server_id = 3
expire_logs_days = 15
# 开启GTID功能
gtid-mode=ON
enforce-gtid-consistency=ON
# 开启半同步主从复制
rpl_semi_sync_slave_enabled=1

5、备份同步MySQL集群数据

备份master服务器上所有数据。如果开启了GTID功能，需要在mysqldump备份语句中添加参数--set-gtid-purged=OFF，在备份的使用就会忽略GTID，避免导入slave服务器时报错。

[root@master ~] mysqldump -uroot -p'Sanchuang123#' --set-gtid-purged=OFF --all-databases >all_db.SQL

将master服务器导出的备份数据写入到slave服务器中，可以先将SQL文件上传ansible服务器，通过ansible服务器批量将数据写入slave服务器中，确保数据已经同步。

mysql -uroot -p'Sanchuang123#' <all_db.SQL

6、启动半同步主从复制

清理master服务器上mysql二进制日志文件，在master服务器上操作：

root@(none) mysql> reset master;
root@(none) mysql> show master status;
+-------------------+----------+--------------+------------------+------------------------------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+-------------------+----------+--------------+------------------+------------------------------------------+
| master-bin.000001 |      918 |              |                  | 20968907-3ea5-11ef-ae42-000c29cd4845:1-5 |
+-------------------+----------+--------------+------------------+------------------------------------------+

在slave-1服务器和slave-2服务器上操作，master_auto_position的值为master服务器上执行show master status;查询到的GTID值。

root@(none) mysql> reset master;
root@(none) mysql> reset slave all;
root@(none) mysql> CHANGE MASTER TO MASTER_HOST='192.168.163.62' ,
  -> MASTER_USER='panmq',
  -> MASTER_PASSWORD='123456',
  -> MASTER_PORT=3306,
  -> master_auto_position=5;
root@(none) mysql> start slave;

在slave-3服务器上操作，master_auto_position的值为slave-1服务器上执行show master status;查询到的GTID值。MASTER_HOST指向slave-1服务器IP。

root@(none) mysql> reset master;
root@(none) mysql> reset slave all;
root@(none) mysql> CHANGE MASTER TO MASTER_HOST='192.168.163.63' ,
  -> MASTER_USER='panmq',
  -> MASTER_PASSWORD='123456',
  -> MASTER_PORT=3306,
  -> master_auto_position=5;
// 设置延迟备份的时间，单位为秒
root@(none) mysql> CHANGE MASTER TO MASTER_DELAY = 10;
root@(none) mysql> start slave;

在slave服务器上通过show slave status\G;查看是否开启成功。出现下面结果则表示开启成功

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Retrieved_Gtid_Set: 20968907-3ea5-11ef-ae42-000c29cd4845:1-5
Executed_Gtid_Set: 20968907-3ea5-11ef-ae42-000c29cd4845:1-5
Auto_Position: 1

MySQL定时备份

1、创建计划任务

使用crontab在master服务器上创建计划任务，每天凌晨2：30备份一次数据库，将备份文件保存到/backup目录下，并以备份时间命名备份文件。
备份脚本：

#!/bash/bin
mkdir -p /backup
mysqldump -uroot -p'Sanchuang123#' --set-gtid-purged=OFF --all-databases >/backup/$(date +%Y%m%d%H%M%S)_all_db.SQL

将备份脚本放到指定目录下，并添加到计划任务中。

[root@sc-master backup] crontab -l
30 2 * * * bash /backup/backup_alldb.sh 

2、rsync备份服务器安装配置

将ansible服务器作为一台异地备份服务器，使用rsync+sersync将master服务器上/backup目录下文件实时地远程同步到ansible服务器上。
在ansible服务器上操作：

// 安装rsync服务端软件
[root@ansible backup] yum install rsync xinetd -y

// 设置开机启动
[root@ansible backup] vim /etc/rc.d/rc.local
/usr/bin/rsync --daemon --config=/etc/rsyncd.conf

// 授予开机启动文件执行的权限
[root@ansible backup] chmod +x /etc/rc.d/rc.local

// rsync就是一个非独立的服务，依赖xinetd来管理
[root@ansible backup] systemctl start xinetd

创建rsyncd.conf配置文件，注意不要打错了。

[root@ansible backup] vim /etc/rsyncd.conf
uid = root
gid = root
use chroot = yes
max connections = 0
log file = /var/log/rsyncd.log
pid file = /var/run/rsyncd.pid
lock file = /var/run/rsync.lock
secrets file = /etc/rsync.pass
motd file = /etc/rsyncd.Motd
[back_data]  #配置项名称（自定义）
     path = /backup  #备份文件存储地址 -->需要提前在备份服务器上新建，不然后面会报错
     comment = A directory in which data is stored
     ignore errors = yes
     read only = no
     hosts allow = 192.168.163.62     #允许的ip地址(数据源服务器地址)

创建用户认证文件：

// 配置文件，添加以下内容，添加允许传输用户和密码
// 格式为，用户名:密码，可以设置多个，每行一个
[root@ansible backup] vim /etc/rsync.pass
pmq:pmq123456

启动rsync和xinetd，可以通过ps aux|grep rsync、ps aux|grep xinetd和netstat -anplut|grep rsync查看是否成功启动。

// 设置文件权限
[root@ansible backup] chmod 600 /etc/rsyncd.conf
[root@ansible backup] chmod 600 /etc/rsync.pass

// 启动
[root@ansible backup] /usr/bin/rsync --daemon --config=/etc/rsyncd.conf
[root@ansible backup] systemctl start xinetd

3、rsync数据源服务器安装配置

在master服务器上操作：

// 安装rsync服务端软件
[root@master backup] yum install rsync xinetd -y

// 设置开机启动
[root@master backup] vim /etc/rc.d/rc.local
/usr/bin/rsync --daemon --config=/etc/rsyncd.conf

// 授予开机启动文件执行的权限
[root@master backup] chmod +x /etc/rc.d/rc.local

// rsync就是一个非独立的服务，依赖xinetd来管理
[root@master backup] systemctl start xinetd

创建rsyncd.conf配置文件，注意这里的配置与上面备份服务器配置有一点点差异。

[root@ansible backup] vim /etc/rsyncd.conf
log file = /var/log/rsyncd.log
pid file = /var/run/rsyncd.pid
lock file = /var/run/rsync.lock
motd file = /etc/rsyncd.Motd
[Sync]
    comment = Sync
    uid = root
    gid = root
    port= 873  # 这里端口号要与备份服务器上rsync监听的端口号一致

创建认证密码文件：

[root@master backup] vim /etc/passwd.txt
#编辑文件，添加以下内容，该密码应与备份服务器中的/etc/rsync.pass中的密码一致
pmq123456

// 设置文件权限，只设置文件所有者具有读取、写入权限即可
[root@master backup] chmod 600 /etc/passwd.txt

测试数据同步：

[root@master backup] rsync -avH --port=873 --progress --delete /backup root@192.168.163.61::back_data --password-file=/etc/passwd.txt

4、安装sersync工具，实时同步

由于rsync同步需要手工进行，这不符合我们实际工作环境，还需要安装sersync工具，实时触发rsync进行同步。sersync工具安装在数据源服务器也就是master服务器上。
首先修改inotify默认参数：

[root@master backup] sysctl -w fs.inotify.max_queued_events="99999999"
[root@master backup] sysctl -w fs.inotify.max_user_watches="99999999"
[root@master backup] sysctl -w fs.inotify.max_user_instances="65535"

// 永久修改参数
[root@master backup] vim /etc/sysctl.conf
fs.inotify.max_queued_events=99999999
fs.inotify.max_user_watches=99999999
fs.inotify.max_user_instances=65535

安装sersync：

[root@master backup] yum install wget -y
[root@master backup] wget  http://down.whsir.com/downloads/sersync2.5.4_64bit_binary_stable_final.tar.gz
[root@master backup] tar xf sersync2.5.4_64bit_binary_stable_final.tar.gz 
[root@master backup] mv GNU-Linux-x86/ /usr/local/sersync

配置sersync：

[root@master backup] cd /usr/local/sersync/

// 备份配置文件，防止修改错了，不知道哪里出错，好还原
[root@master sersync] cp confxml.xml confxml.xml.bak
[root@master sersync] cp confxml.xml data_configxml.xml

// 修改配置data_configxml.xml文件第24行后的配置
[root@master sersync] vim data_configxml.xml
<localpath watch="/backup">
    <remote ip="192.168.163.61" name="back_data"/>
    <!--<remote ip="192.168.8.39" name="tongbu"/>-->
    <!--<remote ip="192.168.8.40" name="tongbu"/>-->
</localpath>
<rsync>
	<commonParams params="-artuz"/>
    <auth start="false" users="root" passwordfile="/etc/passwd.txt"/>
    <userDefinedPort start="false" port="874"/><!-- port=874 -->
    <timeout start="false" time="100"/><!-- timeout=100 -->
    <ssh start="false"/>
</rsync>

启动服务：

[root@master sersync] echo 'PATH=/usr/local/sersync/:$PATH' >>/root/.bashrc 
[root@master sersync] source /root/.bashrc
[root@master sersync] sersync2 -d -r -o /usr/local/sersync/data_configxml.xml

// 设置开机启动
[root@master backup] vim /etc/rc.local 
/usr/local/sersync/sersync2 -d -r -o  /usr/local/sersync/data_configxml.xml

搭建读写分离

在mysqlrouter-1服务器和mysqlrouter-2服务器上都需要进行安装，过程和配置都一致，可以编写安装脚本通过ansible服务器批量配置。下面以mysqlrouter-1服务器上安装配置为例。

1、安装MySQL Router中间件

MySQL Router中间件是MySQL官方给我们提供的一个读写分离的中间件。
MySQL Router中间件下载地址：https://dev.mysql.com/get/Downloads/MySQL-Router/mysql-router-community-8.0.23-1.el7.x86_64.rpm

[root@mysqlrouter-1 ~] ls
anaconda-ks.cfg mysql-router-community-8.0.23-1.el7.x86_64.rpm
[root@mysqlrouter-1 ~] rpm -ivh mysql-router-community-8.0.23-1.el7.x86_64.rpm 

2、修改MySQL Router配置文件

mysqlrouter服务器中，端口7001为mysql读端口，7002为mysql读写端口。mysqlrouter服务器会将连接7001端口的mysql用户通过轮询法，依次分流到slave-1服务器和slave-2服务器上。

[root@mysqlrouter-1 ~] vim /etc/mysqlrouter/mysqlrouter.conf
#在配置文件末尾追加下面内容
[routing:slaves]
bind_address = 0.0.0.0:7001
destinations = 192.168.168.63:3306,192.168.163.64:3306
mode = read-only
connect_timeout = 1

[routing:masters]
bind_address = 0.0.0.0:7002
destinations = 192.168.163.62:3306
mode = read-write
connect_timeout = 1

3、启动MySQL Router服务

[root@mysqlrouter-1 ~] systemctl start mysqlrouter
[root@mysqlrouter-1 ~] systemctl enable mysqlrouter

// 查看端口、进程是否启动
[root@mysqlrouter-1 ~] ps aux|grep mysqlrouter
[root@mysqlrouter-1 ~] netstat -anplut|grep mysqlrouter

4、测试读写分离

在master服务器上创建两个mysql用户，一个用于读，一个用于读写，从而实现读写分离。由于半同步主从复制集群搭建好了，在master服务器上创建用户会同步到其余slave服务器上，所以只需要在master服务器上创建用户即可。

root@(none) mysql>grant all on *.*  to 'write'@'%' identified by 'Panmq123#';
root@(none) mysql>grant select on *.*  to 'read'@'%' identified by 'Panmq123#';

在测试服务器上使用mysql连接mysqlrouter-1服务器：

// 读功能测试
[root@test ~] mysql -h 192.168.163.66 -P 7001 -uread -p'Panmq123#'
// 写功能测试
[root@test ~] mysql -h 192.168.163.66 -P 7002 -uwrite -p'Panmq123#'

可以在mysql中使用show processlist;查看连接主机，到此读写分离搭建完成，mysqlrouter-2服务器与上述操作一致。

搭建双vip高可用

1、安装keepalived软件

在2台mysqlrouter服务器上都要安装：

// mysqlrouter-1服务器上操作
[root@mysqlrouter-1 ~] yum install keepalived -y

// mysqlrouter-2服务器上操作
[root@mysqlrouter-2 ~] yum install keepalived -y

2、修改配置文件

mysqlrouter-1服务器上keepalived配置文件如下：

[root@mysqlrouter-1 ~] vim /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_garp_interval 0
   vrrp_gna_interval 0
}
vrrp_instance VI_1 {
    state MASTER
    interface ens33
    virtual_router_id 80
    priority 200
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.2.188
    }
}

mysqlrouter-2服务器上keepalived配置文件如下：

[root@mysqlrouter-2 ~] vim /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_garp_interval 0
   vrrp_gna_interval 0
}
vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    virtual_router_id 80
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.2.188
    }
}

配置文件主要修改点在于vrrp_instance VI_1，注意两台mysqlrouter服务器的virtual_router_id和auth_pass要一致。

3、邮箱预警

当keepalived中的master挂掉后，需要发送邮件来告警我们。
修改备用服务器（mysqlrouter-2）keepalived配置，当备用服务器转变为主服务器时，执行脚本，发送邮件告警我们：

[root@mysqlrouter-2 ~] vim /etc/keepalived/keepalived.conf 
#仅需修改vrrp_instance VI_1配置
vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    virtual_router_id 80
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    #邮箱预警
    notify_master /root/sh/send_mail.sh
    virtual_ipaddress {
        192.168.2.188
    }
}

4、配置双vip

mysqlrouter-1服务器配置：

[root@mysqlrouter-1 ~] vim /etc/keepalived/keepalived.conf 
#在配置文件末尾追加下面内容
vrrp_instance VI_2 {
    state BACKUP
    interface ens33
    virtual_router_id 81
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 2222
    }
    #邮箱预警
    notify_master /root/sh/send_mail.sh
    virtual_ipaddress {
        192.168.2.189
    }
}

mysqlrouter-2服务器配置：

[root@mysqlrouter-2 ~] vim /etc/keepalived/keepalived.conf 
#在配置文件末尾追加下面内容
vrrp_instance VI_2 {
    state MASTER
    interface ens33
    virtual_router_id 81
    priority 200
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 2222
    }
    #邮箱预警
    notify_master /root/sh/send_mail.sh
    virtual_ipaddress {
        192.168.2.189
    }
}

5、启动双vip高可用

[root@mysqlrouter-1 ~] systemctl start keepalived
[root@mysqlrouter-1 ~] systemctl enable keepalived

启动这步操作mysqlrouter-1服务器与mysqlrouter-2服务器操作一致，通过ip add查看IP地址是否配置成功。

MySQL集群压力测试

1、安装sysbench

去官方网站下载mysql的repo文件：https://dev.mysql.com/downloads/repo/yum/

[root@test-client ~] rpm -ivh mysql84-community-release-el7-1.noarch.rpm
[root@test-client ~] yum -y install mysql-community-devel
[root@test-client ~] yum install epel-release -y
[root@test-client ~] yum install sysbench -y

2、测试准备

sysbench测试时需要提前在数据库里新建sbtest库

// 在master服务器上建库
[root@master ~] mysql -uwrite -p'Panmq123#'
root@(none) mysql> CREATE DATABASE sbtest;

// 添加数据
[root@test-client ~] sysbench \
> --mysql-host=192.168.163.188 \
> --mysql-port=7002 \
> --mysql-user=write \
> --mysql-password='Panmq123#' \
> /usr/share/sysbench/oltp_common.lua \
> --tables=10 \
> --table_size=10000 \
> prepare

--tables=10：创建10张表，--table_size=10000：每张表1万条数据。

3、压力测试

// 读写测试
[root@test-client ~] sysbench \
> --threads=4 \
> --time=20 \
> --report-interval=5 \
> --mysql-host=192.168.163.188 \
> --mysql-port=7002 \
> --mysql-user=write \
> --mysql-password='Panmq123#' \
> /usr/share/sysbench/oltp_read_write.lua \
> --tables=10 \
> --table_size=10000 \
> run

结果如下：

[ 5s ] thds: 4 tps: 68.55 qps: 1379.47 (r/w/o: 965.75/275.81/137.91) lat (ms,95%): 95.81 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 4 tps: 76.61 qps: 1531.90 (r/w/o: 1073.81/304.86/153.23) lat (ms,95%): 90.78 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 4 tps: 71.00 qps: 1423.32 (r/w/o: 996.34/284.98/141.99) lat (ms,95%): 97.55 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 4 tps: 51.17 qps: 1019.19 (r/w/o: 713.18/203.68/102.34) lat (ms,95%): 123.28 err/s: 0.00 reconn/s: 0.00

SQL statistics:
    queries performed:
        read:                            18774
        write:                           5364
        other:                           2682
        total:                           26820
    transactions:                        1341   (66.68 per sec.)
    queries:                             26820  (1333.51 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          20.1107s
    total number of events:              1341

Latency (ms):
         min:                                   20.81
         avg:                                   59.85
         max:                                  156.42
         95th percentile:                      106.75
         sum:                                80261.60

Threads fairness:
    events (avg/stddev):           335.2500/1.09
    execution time (avg/stddev):   20.0654/0.02

项目心得

1. 一定要规划好整个集群的架构，配置要细心，脚本和软件要提前准备好，边做边修改。
2. 防火墙和SELinux的问题需要多注意。
3. IP地址要设置为静态IP，避免重启后IP地址无效。
4. 对Keepalived的配置需要更加细心和IP地址的规划有了新的认识。
5. 认识到了数据备份的重要性。
6. 对Ansible自动化批量部署和Prometheus监控有了更加多的应用和理解。
7. 对MySQL的集群和Keepalived高可用有了深入的理解。
8. 对双vip的使用，添加2条负载均衡记录实现dns轮询，达到向2个vip负载均衡器上分流。