基于keepalived+gtid+双vip半同步主从复制的MySQL高性能集群

项目名称：基于keepalived+gtid+双vip半同步主从复制的MySQL高性能集群

项目规划图

1.配置4台MySQL服务器（1台master，2台slave，1台backup），安装好MySQL软件，安装好半同步相关的插件

2.配置好ansible服务器，定义好主机清单，在master和ansible服务器之间建立双向免密通道

3.在master上使用mysqldump,将数据冷备到anaible中控机上，然后ansible下发到所有的slave服务器上（其实也可以使用ansible批量进行mysql安装）

4.所有mysql机器开启gtid功能，启动主从复制服务，配置bakcup服务器上从master上拿二进制日志,设置延迟300秒.

5.安装prometheus监控系统服务器，同时配置grafana，使用美观的图表监控我们的mysql集群

6.将所有的mysql服务器上安装node_exporter,采集数据进行数据监控

7.在master上创建一个计划任务每天定时进行master数据库的备份，编写备份脚本每天备份数据，备份文件包含当前的日期，scp远程同步到ansible服务器（相当于一台异地备份服务器），保障业务的稳定

8.在2台服务器安装部署了mysqlrouter中间件软件，实现读写分离

9.安装keepalived实现高可用，配置2个vrrp实例实现双vip的高可用功能

10.进行压力测试，使用sysbench进行测试

项目环境：9台服务器（2G，2核），centos7.8 mysql5.7.30 mysqlrouter8.0.21 keepalived2.0.10 prometheus grafana ansible mysqlrouter

项目描述：
本项目的目的是模拟企业构建一个高可用的能实现读写分离的高效的MySQL集群，确保业务的稳定，能沟通方便的监控整个集群，同时能批量的去部署和管理整个集群。

项目规划图

项目IP地址具体规划：

master 192.168.0.110

salve 192.168.0.111

salve3 192.168.0.112

delay-backup 192.168.0.113

mysql-router2 192.168.0.202 vip1:192.168.0.187

mysql-router2 192.168.0.202 vip2:192.168.0.186

ansible 192.168.0.205

prometheus 192.168.0.204

clinet 192.168.0.10

一定要关闭selinux和firewalld

1.配置4台MySQL服务器（1台master，2台slave，1台backup），安装好MySQL软件，安装好半同步相关的插件

#准备好实验的环境安装包

[root@master ~]# ls
install_exporter.sh  mha4mysql-node-0.58.tar.gz                                              onekey_install_mha_node.sh     mha4mysql-node-0.58  mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz  node_exporter-1.4.0-rc.0.linux-amd64.tar.gz  onekey_instal

mysql一键安装脚本

[root@master ~]# cat onekey_install_mysql_binary.sh 
#!/bin/bash
#解决软件的依赖关系
yum  install cmake ncurses-devel gcc  gcc-c++  vim  lsof bzip2 openssl-devel ncurses-compat-libs -y

#解压mysql二进制安装包
tar  xf  mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz

#移动mysql解压后的文件到/usr/local下改名叫mysql
#/usr/local/mysql 是mysql的安装目录--》门店
mv mysql-5.7.38-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}')
  #$NF表示最后一个字段
  # abc=$(命令)  优先执行命令，然后将结果赋值给abc 

# 修改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 \\R:\\m  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='123456';  修改root用户的密码为123456
mysql -uroot -p$tem_passwd --connect-expired-password   -e  "set password='123456';"


#检验上一步修改密码是否成功，如果有输出能看到mysql里的数据库，说明成功。
mysql -uroot -p'123456'  -e "show databases;"

#其他的slave机器都安装好mysql就行

2.配置好ansible服务器，定义好主机清单，在master和ansible服务器之间建立双向免密通道

生成好密钥对，一直回车，都按照默认的就好

[root@ansible ~]# ssh-keygen

将ansible的公钥发送的mysql集群

[root@ansible ~]# ssh-copy-id root@192.168.0.110
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '192.168.0.10 (192.168.0.10)' can't be established.
ECDSA key fingerprint is SHA256:xactOuiFsm9merQVjdeiV4iZwI4rXUnviFYTXL2h8fc.
ECDSA key fingerprint is MD5:69:58:6b:ab:c4:8c:27:e2:b2:7c:31:bb:63:20:81:61.
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.0.110's password:
#这里填好你的密码

#提示这个就是你的成功

Now try logging into the machine, with: "ssh 'root@192.168.0.110'"
and check to make sure that only the key(s) you wanted were added.

##同样，将ansible的公钥发送给目标主机上，实现ansible对整个mysql集群的管理

192.168.0.111

192.168.0.112

192.168.0.113

mysql-router2 192.168.0.202

prometheus 192.168.0.204

#配置ansible里面的hosts配置文件

[root@ansible ~]# vim /etc/ansible/hosts
#将需要管理的主机添加到mysql-servers组
#默认端口号就是22
#如果通过ssh登录的端口不是22端口，就需要在配置文件里面指明端口号
[mysql]
192.168.0.110 #master
192.168.0.111 #slave1
192.168.0.112 #slave2
192.168.0.113 #delay-backup

[mysql-slave]
192.168.0.111 #salve1
192.168.0.112 #slave2
192.168.0.113 #delay_bakcup


[mysql-master]
192.168.0.110  #master

[mysql-router]
192.168.0.201 #router1
192.168.0.202 #router2

3.在master上使用mysqldump,将数据冷备到anaible中控机上，然后ansible下发到所有的slave服务器上（其实也可以使用ansible批量进行mysql安装）

#这里给出了批量安装的yaml文件

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

#将master上的数据冷备到其他的mysql服务器上

注意备份之前需要将gtid功能关闭

#开启gtid
#gtid-mode=ON
#enforce-gtid-consistency=ON
log-slave-updates

数据热备--》注意要是出现我这个样是没有什么影响的

[root@master backup]# mysqldump -uroot -p'1232456' --all-databases >/backup/data.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@master backup]# ls
20240319130622_all_db.SQL  passwd
all_backup_db.sh           position.sh
data.sql

复制数据到ansible上，然后在通过ansible下发到其他的机器

[root@master backup]# scp ./data.sql ansible:/backup/
The authenticity of host 'ansible (192.168.0.205)' can't be established.
ECDSA key fingerprint is SHA256:pRY8ueQygPcheh2SCJd8V9PBhT7RlgDADPMzY82Fd4o.
ECDSA key fingerprint is MD5:02:ca:7e:56:d1:f9:e5:9c:83:3f:67:ee:9f:2c:38:20.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'ansible' (ECDSA) to the list of known hosts.
all_data.sql                              100%  203    89.1KB/s   00:00

[root@slave backup]# ls
data.sql
[root@slave3 backup]# ls
data.sql
[root@delay-backup backup]# ls
data.sql

##其他机器也有这个，表示ansible 下发任务成功

#编写yaml文件，将数据全部热备到mysql集群上

[root@ansible backup]# cat copy_data.yaml 
- name: Upload all_data.sql to MySQL slave servers
  hosts: mysql-slave
  tasks:
    - name: Run script to upload all_data.sql
      script: /backup/backup.sh  # 替换为你的上传脚本路径

[root@ansible backup]# ansible-playbook copy_data.yaml
[WARNING]: Invalid characters were found in group names but not replaced, use -vvvv to see details

PLAY [Upload all_data.sql to MySQL slave servers] *****************************************************************************

TASK [Gathering Facts] ********************************************************************************************************
ok: [192.168.0.113]
ok: [192.168.0.112]
ok: [192.168.0.111]

TASK [Run script to upload all_data.sql] **************************************************************************************
changed: [192.168.0.113]
changed: [192.168.0.112]
changed: [192.168.0.111]

PLAY RECAP ********************************************************************************************************************
192.168.0.111 : ok=2 changed=1 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0
192.168.0.112 : ok=2 changed=1 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0
192.168.0.113 : ok=2 changed=1 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0

4.所有mysql机器开启gtid功能，启动主从复制服务，配置bakcup服务器上从master上拿二进制日志,设置延迟300秒.

#开启之后gtid---》所有机器上都要开启
[root@master backup]# 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=utf8mb4

#二进制日志
log_bin
server_id=1

#开启半同步
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000 #1秒

#开启gtid
gtid-mode=ON
enforce-gtid-consistency=ON
log-slave-updates


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

#刷新服务 ---》所有mysql机器
[root@master backup]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

#防止出错重置所有机器上的master和slave
root@(none) 15:41  mysql>reset master;
root@(none) 15:43  mysql>reset slave all;

#重新指认master
CHANGE MASTER TO 
    MASTER_HOST='192.168.0.110',
    MASTER_USER='wylwrite',
    MASTER_PASSWORD='123456',
    MASTER_PORT=3306,
    MASTER_AUTO_POSITION=1;
####注意！！！这里我是之前创建了一个用户
create user 'wylwrite'@'%' identified by '123456';
grant all on *.* to 'wylwrite'@'%';
FLUSH PRIVILEGES;   ##刷新权限

#开启所有的从服务器上开启slave
root@(none) 15:43  mysql>start slave;
root@(none) 15:43  mysql>show slave status\G;
#查看状态
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.110
                  Master_User: wylwrite
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000002
          Read_Master_Log_Pos: 154
               Relay_Log_File: slave-relay-bin.000004
                Relay_Log_Pos: 369
        Relay_Master_Log_File: master-bin.000002
             Slave_IO_Running: Yes  ##  #这个是io线程，就是帮你去master的二进制日志的
            Slave_SQL_Running: Yes  ## 这个表示是本机的mysql进程
              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: 154
              Relay_Log_Space: 791
              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  ##这个id绝对是不可以一样的
                  Master_UUID: e1ae3fd1-db8f-11ee-859f-000c2974ba51
             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: 
            Executed_Gtid_Set: 
                Auto_Position: 1  ##表示gtid功能开启了
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version:

5.安装prometheus监控系统服务器，同时配置grafana，使用美观的图表监控我们的mysql集群

[root@prometheus prom]# ls
 node_exporter-1.4.0-rc.0.linux-amd64.tar.gz  prometheus-2.43.0.linux-amd64.tar.gz
grafana-enterprise-9.1.2-1.x86_64.rpm  prometheus

[root@prometheus ansible]# mkdir /prom

[root@prometheus ansible]# cd /prom

[root@prometheus prom]# ls

grafana-enterprise-9.1.2-1.x86_64.rpm prometheus-2.43.0.linux-amd64.tar.gz

node_exporter-1.4.0-rc.0.linux-amd64.tar.gz

[root@nfs-ansible-prom prom]#

2.解压源码包

[root@ prometheu prom]# tar xf prometheus-2.43.0.linux-amd64.tar.gz

[root@prometheus prom]# ls

grafana-enterprise-9.1.2-1.x86_64.rpm prometheus-2.43.0.linux-amd64

node_exporter-1.4.0-rc.0.linux-amd64.tar.gz prometheus-2.43.0.linux-amd64.tar.gz

修改解压后的压缩包名字prometheus

[root@promethues prom]# mv prometheus-2.43.0.linux-amd64 prometheus

[root@promethues prom]# ls

grafana-enterprise-9.1.2-1.x86_64.rpm prometheus

node_exporter-1.4.0-rc.0.linux-amd64.tar.gz prometheus-2.43.0.linux-amd64.tar.gz

[root@promethues prom]#

临时和永久修改PATH变量，添加prometheus的路径

[root@promethues prom]#PATH=/prom/prometheus:$PATH

[ [root@promethues prom]# echo 'PATH=/prom/prometheus:$PATH' >>/etc/profile

[root@promethues prom]#which prometheus ##查看命令在哪里

/prom/prometheus/prometheus

[root@promethues prom]#

把prometheus做成一个服务来进行管理，非常方便日后维护和使用

[root@promethues prom]#vim /usr/lib/systemd/system/prometheus.service

[Unit]

Description=prometheus

[Service]

ExecStart=/prom/prometheus/prometheus --config.file=/prom/prometheus/prometheus.yml

ExecReload=/bin/kill -HUP $MAINPID

KillMode=process

Restart=on-failure

[Install]

WantedBy=multi-user.target

重新加载systemd相关的服务，识别Prometheus服务的配置文件

[root@promethues prom]# systemctl daemon-reload

启动Prometheus服务

[root@promethues prom]#systemctl start prometheus

[root@promethues prom]#systemctl restart prometheus

[root@promethues prom]#ps aux|grep prome

root 2740 2.7 0.9 798956 37312 ? Ssl 14:57 0:00 /prom/prometheus/prometheus --config.file=/prom/prometheus/prometheus.yml

root 2748 0.0 0.0 112824 976 pts/0 S+ 14:57 0:00 grep --color=auto prome

设置开机启动

[root@promethues prom]# systemctl enable prometheus

Created symlink from /etc/systemd/system/multi-user.target.wants/prometheus.service to /usr/lib/systemd/system/prometheus.service.

##这里面的文档是需要到官方下载

6.将所有的mysql服务器上安装node_exporter,采集数据进行数据监控

在整个mysql集群上安装node_exporter

exporter 是Prometheus的客户端的数据采集工具--》go语言编写的

可以通过scp 命令传到其他机器

也可以通过ansible命令传到其他机器，也可以写yaml文件

#编写好一键安装文档
[root@nfs-ansible-prom prom]# vim install_node_exporter.sh 
#!/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

##都安装好之后可以进行查看端口和进程号

[root@prometheus prom]# ps -aux| grep prometheus
root        703  0.1  2.1 1136324 82212 ?       Ssl  13:09   0:14 /prom/prometheus/prometheus --config.file=/prom/prometheus/prometheus.yml
root       1702  0.0  0.0 112828   976 pts/0    S+   16:12   0:00 grep --color=auto prometheus

##查看端口号
[root@prometheus prom]# netstat -anplut|grep prom
tcp        0      0 192.168.0.204:38750     192.168.0.110:9090      ESTABLISHED 703/prometheus      
tcp        0      0 192.168.0.204:44714     192.168.0.111:9090      ESTABLISHED 703/prometheus      
tcp        0      0 192.168.0.204:52838     192.168.0.112:9090      ESTABLISHED 703/prometheus      
tcp        0      0 192.168.0.204:37038     192.168.0.113:9090      ESTABLISHED 703/prometheus      
tcp6       0      0 :::9090                 :::*                    LISTEN      703/prometheus      
tcp6       0      0 ::1:41556               ::1:9090                ESTABLISHED 703/prometheus      
tcp6       0      0 ::1:9090                ::1:41556               ESTABLISHED 703/prometheus

#登录自己的prometheus的端口一般是9090。

7.在master上创建一个计划任务每天定时进行master数据库的备份，编写备份脚本每天备份数据，备份文件包含当前的日期，scp远程同步到ansible服务器（相当于一台异地备份服务器），保障业务的稳定

##编辑计划任务
crontab -e

#每天2点30分,备份数据库信息
30 2 * * * bash /backup/all_backup_db.sh

脚本内容
[root@master backup]# cat all_backup_db.sh 
mysqldump -uroot -p'123456' --all-databases > "$(date +"%Y-%m-%d_%H-%M-%S")_database_backup.sql"

#也可以设置计划任务将数据远程备份到ansible中控机上，实现异地备份

[root@master backup]# crontab -l
#每天2点30分,备份数据到远程ansible中控机上
30 2 * * * scp ./"$(date +"%Y-%m-%d_%H-%M-%S")

8.在2台服务器安装部署了mysqlrouter中间件软件，实现读写分离

#安装好--》可以去官方文档，或者查资料
[root@mysql-router1 ~]# ls
anaconda-ks.cfg                                 mysql-router-community-8.0.21-1.el7.x86_64.rpm.1
mysql-router-community-8.0.21-1.el7.x86_64.rpm  node_exporter-1.4.0-rc.0.linux-amd64.tar.gz

[root@mysql-router1 ~]# rpm -ivh mysql-router-community-8.0.23-1.el7.x86_64.rpm 
警告：mysql-router-community-8.0.23-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql-router-community-8.0.23-1.e################################# [100%]
[root@mysql-router1 ~]# 
3.修改配置文件
[root@mysql-router1 ~]# cd /etc/mysqlrouter/  进入存放配置文件的目录
[root@mysql-router1 mysqlrouter]# ls
mysqlrouter.conf
[root@mysql-router1 mysqlrouter]# vim mysqlrouter.conf

[root@mysql-router1 mysqlrouter]# cat mysqlrouter.conf |grep -v "^#"

[DEFAULT]
logging_folder = /var/log/mysqlrouter
runtime_folder = /var/run/mysqlrouter
config_folder = /etc/mysqlrouter

[logger]
level = INFO

[keepalive]
interval = 60

[routing:slaves]
bind_address = 192.168.0.187:7001 #虚拟vip1
destinations = 192.168.0.111:3306,192.168.0.112:3306,192.168.0.113:3306
mode = read-only
connect_timeout = 1 

[routing:masters]
bind_address = 192.168.0.187:7002 #虚拟vip1
destinations = 192.168.0.110:3306
mode = read-write
connect_timeout = 1  #链接超时时间
##同理配置mysqlrouter2

4.启动MySQL router服务
[root@mysql-router1 ~]# service mysqlrouter start
Redirecting to /bin/systemctl start mysqlrouter.service
[root@mysql-router1 ~]#
mysqlrouter监听了7001和7002端口
[root@mysql-router1 ~]# netstat -anplut|grep mysql
tcp        0      0 192.168.2.106:7001      0.0.0.0:*               LISTEN      2258/mysqlrouter    
tcp        0      0 192.168.2.106:7002      0.0.0.0:*               LISTEN      2258/mysqlrouter    
[root@mysql-router1 ~]# 

5.在master上创建2个测试账号，一个是读的，一个是写的

root@(none) 15:34  mysql>grant all on *.*  to 'wylwrite'@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@(none) 15:35  mysql>grant select on *.*  to 'wylread'@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)

root@(none) 15:35  mysql>
6.在客户端上测试读写分离的效果，使用2个测试账号
[root@master backup]# mysql -uwylwrite -p'123456' 

wylwrite@(none) 16:49  mysql>create database teswyl;
Query OK, 1 row affected (0.01 sec)

wylwrite@(none) 16:49  mysql>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| 123w               |
| TENNIS             |
| XIESHAN            |
| hepeng123          |
| hunan              |
| jioajiao           |
| liangliang         |
| mysql              |
| performance_schema |
| sbtest             |
| sys                |
| tanxue             |
| teswyl             |
| wang123            |
| wangshuai          |
+--------------------+
16 rows in set (0.00 sec)
##从服务器
root@(none) 16:49  mysql>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| 123w               |
| TENNIS             |
| XIESHAN            |
| hepeng123          |
| hunan              |
| jioajiao           |
| liangliang         |
| mysql              |
| performance_schema |
| sbtest             |
| sys                |
| tanxue             |
| teswyl             |
| wang123            |
| wangshuai          |
+--------------------+
16 rows in set (0.01 sec)
主从复制同步了
##同理测试读的用户
wylread@(none) 16:57  mysql>create database nihaao;
ERROR 1044 (42000): Access denied for user 'wylread'@'%' to database 'nihao'
##没有权限建库
wylread@hunan 16:59  mysql>select * from width; 
+----+-----------+
| id | name      |
+----+-----------+
| 18 | 王老板    |
| 19 | 王老板    |
| 20 | 关牛马    |
| 22 | 关牛马    |
+----+-----------+
4 rows in set (0.00 sec)

读写分离的关键点：其实是用户的权限，让不同的用户连接不同的端口，最后任然要到后端的mysql服务器里去验证是否有读写的权限
mysqlrouter只是做了读写的分流，让应用程序去连接不同的端口--》mysqlrouter只是一个分流的工具
主要是用户权限的控制，有写权限的用户走读的通道也可以写，读的用户走写的通道只能读

7001 -->read-->wylread
7002 -->write/read -->wylwrite

9.安装keepalived实现高可用，配置2个vrrp实例实现双vip的高可用功能

keepalived ：
官方网站：https://www.keepalived.org/
Keepalived 是一个用 C 语言编写的路由软件。这个项目的主要目标是为 Linux 系统和基于 Linux 的基础设施提供简单而强大的负载均衡和高可用性功能。
Keepalived 开源并且免费的软件。

Keepalived 的2大核心功能：
   1.loadbalance 负载均衡 LB：ipvs--》lvs软件在linux内核里已经安装，不需要单独安装
   2. high-availability 高可用 HA ： vrrp协议
=====
vrrp协议：虚拟路由器冗余协议
           一组路由器协同工作，担任不同的角色，有master角色，也有backup角色
           master角色的路由器（的接口）承担实际的数据流量转发任务
           Backup路由器侦听Master路由器的状态，并在Master路由器发生故障时，接替其工作，从而保证业务流量的平滑切换。随时候命，是备胎

           选举：
           vip：虚拟ip
           在一个VRRP 组内的多个路由器接口共用一个虚拟IP地址，该地址被作为局域网内所有主机的缺省网关地址。

VRRP协议报文使用固定的组播地址224.0.0.18进行发送

vrrp协议工作在哪层？
   网络层
vrrp协议的组播地址：
       封装角度：
           帧：源mac，目的mac
           vrrp协议：封装
           ip协议
vrrp协议的工作原理：
   选举的过程：
       1.所有的路由器或者服务器发送vrrp宣告报文，进行选举，必须是相同vrid和认证密码的，优先级高的服务器或者路由器会被选举为master，其他的机器都是backup
       2.master定时（Advertisement Interval）发送VRRP通告报文，以便向Backup路由器告知自己的存活情况。默认是间隔1秒
       3.接收Master设备发送的VRRP通告报文，判断Master设备的状态是否正常。如果超过1秒没有收到vrrp报文，就认为master挂了，开始重新选举新的master，vip会漂移到新的master上

====
安装keepalived软件，在2台MySQLrouter上都安装
1.[root@mysql-router-1 keepalived]# yum install keepalived -y

2.修改配置文件
[root@mysql-router1 ~]# cd /etc/keepalived/
[root@mysql-router1 keepalived]# ls
keepalived.conf
[root@mysql-router1 ~]# 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_script send_mail {
   script "/mail/sendmail.sh"
   interval 3
}

vrrp_instance VI_1 {
    state MASTER
    interface ens33
    virtual_router_id 80
    priority 200
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
track_script {
   send_mail
}
    virtual_ipaddress {
        192.168.0.187
    }
}

vrrp_instance VI_2 {
    state backup
    interface ens33
    virtual_router_id 81
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
track_script {
   send_mail
}
    virtual_ipaddress {
        192.168.0.186
    }
}
##配置mysql-router2
[root@mysql-router2 ~]# 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_script send_mail {
   script "/mail/sendmail.sh"
   interval 3
}

vrrp_instance VI_1 {
    state MASTER
    interface ens33
    virtual_router_id 80
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
track_script {
   send_mail
}
    virtual_ipaddress {
        192.168.0.187
    }
}

vrrp_instance VI_2 {
    state backup
    interface ens33
    virtual_router_id 81
    priority 200
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
track_script {
   send_mail
}
    virtual_ipaddress {
        192.168.0.186
    }
}
#查看是否启动了vip
[root@mysql-router1 ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:70:16:6f brd ff:ff:ff:ff:ff:ff
    inet 192.168.0.201/24 brd 192.168.0.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet 192.168.0.187/32 scope global ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:fe70:166f/64 scope link 
       valid_lft forever preferred_lft forever

[root@mysql-router2 ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:48:ab:6e brd ff:ff:ff:ff:ff:ff
    inet 192.168.0.202/24 brd 192.168.0.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet 192.168.0.186/32 scope global ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:fe48:ab6e/64 scope link 
       valid_lft forever preferred_lft forever

10.进行压力测试，使用sysbench进行测试

测试前的

##我这个使用的sysbench压力测试
[root@clinet ~]#   sysbench --mysql-host=192.168.0.110 --mysql-port=3306 --mysql-user=wylwrite --mysql-password='123456' /usr/share/sysbench/oltp_read_write.lua  --tables=10  --table_size=10000 prepare

##/usr/share/sysbench/oltp_read_write.lua --》这个脚本的路径一定是要正确！！！

[root@clinet ~]# sysbench --mysql-host=192.168.0.110 --mysql-port=3306 --mysql-user=wylwrite --mysql-password='123456' /usr/share/sysbench/oltp_read_write.lua --tables=10 --table_size=100000 prepare
sysbench 1.0.17 (using system LuaJIT 2.0.4)

Creating table 'sbtest1'...
Inserting 100000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...
Creating table 'sbtest2'...
Inserting 100000 records into 'sbtest2'
Creating a secondary index on 'sbtest2'...
Creating table 'sbtest3'...
Inserting 100000 records into 'sbtest3'
Creating a secondary index on 'sbtest3'...
Creating table 'sbtest4'...
Inserting 100000 records into 'sbtest4'
Creating a secondary index on 'sbtest4'...
Creating table 'sbtest5'...
Inserting 100000 records into 'sbtest5'
Creating a secondary index on 'sbtest5'...
Creating table 'sbtest6'...
Inserting 100000 records into 'sbtest6'
Creating a secondary index on 'sbtest6'...
Creating table 'sbtest7'...
Inserting 100000 records into 'sbtest7'
Creating a secondary index on 'sbtest7'...
Creating table 'sbtest8'...
Inserting 100000 records into 'sbtest8'
Creating a secondary index on 'sbtest8'...
Creating table 'sbtest9'...
Inserting 100000 records into 'sbtest9'
Creating a secondary index on 'sbtest9'...
Creating table 'sbtest10'...
Inserting 100000 records into 'sbtest10'
Creating a secondary index on 'sbtest10'...
wylwrite@sbtest 17:35 mysql>show tables;
+------------------+
| Tables_in_sbtest |
+------------------+
| sbtest1 |
| sbtest10 |
| sbtest2 |
| sbtest3 |
| sbtest4 |
| sbtest5 |
| sbtest6 |
| sbtest7 |
| sbtest8 |
| sbtest9 |