基于keepalived+gtid半同步主从复制的MySQL集群

mysql的安装（二进制安装）

[root@my-slv1 ~]# cat mysql_install.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解压后的文件到/var/local下改名叫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用户可>以对这个件夹进行读写了
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/目录下叫mysql
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

#启动mysql进程
service mysqld start

#将mysqld添加到linux系统里服务管理名单里
/sbin/chkconfig --add mysqld
#设置mysqld服务开机启动
/sbin/chkconfig mysqld on

#初次修改密码需要使用--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;"

一. GTID半同步复制+ansible配置

半同步复制要先在mysql里安装这个插件

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

# MASTER配置
[root@mysql ~]# cat /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-error = tanxue.err
general_log
slow_query_log = 0.001
log_bin
server_id = 1
expire_logs_days = 3
rpl_semi_sync_master_enabled=1		# 半同步复制
rpl_semi_sync_master_timeout=1000	# 半同步复制
gtid-mode=ON
enforce-gtid-consistency=ON

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

# slave配置
[root@my-slv1 ~]# cat /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
server-id=2
log-bin
rpl_semi_sync_slave_enabled=1	# 半同步复制
gtid-mode=ON
enforce-gtid-consistency=ON
log_slave_updates=ON

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

root@(none) 21:38 mysql>grant replication slave on . to 'zhangwz'@'192.168.138.%' identified by 'sanchuang123#';

root@(none) 21:42 mysql>CHANGE MASTER TO MASTER_HOST='192.168.138.133' ,
    -> MASTER_USER='zhangwz',
    -> MASTER_PASSWORD='sanchuang123#',
    -> MASTER_PORT=3306,
    -> master_auto_position=1;

root@(none) 21:43 mysql>start slave;

root@(none) 21:45 mysql>show slave status\G;

************************* 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.138.133
                  Master_User: zhangwz
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 452
               Relay_Log_File: my-slvdelay-relay-bin.000002
                Relay_Log_Pos: 665
        Relay_Master_Log_File: mysql-bin.000001
             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: 452
              Relay_Log_Space: 878
              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: 7cc8fa5e-0f19-11ee-935c-000c29576596
             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: 7cc8fa5e-0f19-11ee-935c-000c29576596:1
            Executed_Gtid_Set: 7cc8fa5e-0f19-11ee-935c-000c29576596:1
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

验证GTID配置是否成功

root@(none) 15:16 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) 15:17 mysql>show variables like "%semi_sync%";
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
2 rows in set (0.01 sec)

root@(none) 14:22 mysql>show variables like 'gtid%';
+----------------------------------+-------------------------------------------+
| Variable_name                    | Value                                     |
+----------------------------------+-------------------------------------------+
| gtid_executed_compression_period | 1000                                      |
| gtid_mode                        | ON                                        |
| gtid_next                        | AUTOMATIC                                 |
| gtid_owned                       |                                           |
| gtid_purged                      | 7cc8fa5e-0f19-11ee-935c-000c29576596:1-12 |
+----------------------------------+-------------------------------------------+
5 rows in set (0.00 sec)

on说明master和slave的gtid半同步已开启

1.从slave1上拿数据做延迟备份

延迟备份配置

root@(none) 20:34 mysql>stop slave;

root@(none) 20:35 mysql>reset master;

root@(none) 20:35 mysql>reset slave all;

root@(none) 20:35 mysql>CHANGE MASTER TO MASTER_HOST='192.168.138.132' ,    **//slave1 ip地址**
    -> MASTER_USER='zhangwz',
    -> MASTER_PASSWORD='sanchuang123#',
    -> MASTER_PORT=3306,
    -> master_auto_position=1;

root@(none) 20:36 mysql>CHANGE MASTER TO MASTER_DELAY = 10;

delay-slave

root@(none) 21:35 mysql>show slave status\G;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.138.132
                  Master_User: zhangwz
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: my-slv1-bin.000001
          Read_Master_Log_Pos: 2130
               Relay_Log_File: my-slvdelay-relay-bin.000007
                Relay_Log_Pos: 662
        Relay_Master_Log_File: my-slv1-bin.000001
             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: 2130
              Relay_Log_Space: 1221
              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: 935799ae-2fa9-11ee-add1-000c290ec7d1
             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: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 7cc8fa5e-0f19-11ee-935c-000c29576596:2-8,
935799ae-2fa9-11ee-add1-000c290ec7d1:1-5
            Executed_Gtid_Set: 0e4011eb-2f9a-11ee-8c5c-000c29c7fe14:1-2,
7cc8fa5e-0f19-11ee-935c-000c29576596:2-8,
935799ae-2fa9-11ee-add1-000c290ec7d1:1-5
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version:

gtid+relay-log半同步复制配置完成

2.在ansible和master之间建立免密双向通道

[root@mysql ~]# ssh-keygen -t rsa

[root@mysql ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub [email protected]

3.创建备份脚本和计划任务

crontab -e

[root@sc-master backup]# crontab -l

30 2 * * * bash /backup/backup_alldb.sh 

[root@sc-master backup]# cat backup_alldb.sh

#!/bin/bash

mkdir -p /backup
mysqldump -uroot -p'sanchuang124#'  --all-databases --triggers --routines --events  >/backup/$(date +%Y%m%d%H%M)_all_db.SQL
scp /backup/$(date +%Y%m%d%H%M)_all_db.SQL  192.168.2.103:/backup

二：读写分离 --> mysqlrouter配置

mysql-router-community-8.0.21-1.el7.x86_64.rpm

 [root@mysqlrouter ~]# rpm -ivh mysql-router-community-8.0.21-1.el7.x86_64.rpm

[root@mysqlrouter ~]# cd /etc/mysqlrouter
[root@mysqlrouter mysqlrouter]# vim mysqlrouter.conf

[routing:slaves]
bind_address = 0.0.0.0:7001
destinations = 192.168.138.132:3306,192.168.138.131:3306
mode = read-only
connect_timeout = 1

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

2.刷新服务

[root@mysqlrouter mysqlrouter]# service mysqlrouter restart

3.在master上授权（二进制同步到slave）

root@(none) 20:03 mysql>grant select on *.* to 'scread'@'%' identified by 'sanchuang123#';

root@(none) 20:04 mysql>grant all on *.* to 'scwrite'@'%' identified by 'sanchuang123#';

4.查看端口

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

5.测试

scwrite写操作连接成功

scread只读连接成功

思考：读写分离的关键点是授权用户，真正检查权限的是后端的mysql服务器

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

三.中间件mysqlroute的HA配置

1.在另一台mysqlroute也装好mysqlrouter

[root@keepalived mysqlrouter]# ps aux|grep mysql
mysqlro+   3165  0.1  0.3 366804  6072 ?        Ssl  21:32   0:00 /usr/bin/mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf
root       3173  0.0  0.0 112824   980 pts/0    S+   21:32   0:00 grep --color=auto mysql

2.安装keepalived

[root@keepalived mysqlrouter]# yum install keepalived

3.修改配置文件

backup

[root@mysqlrouter keepalived]# cat keepalived.conf
! Configuration File for keepalived

global_defs {
   notification_email {
     [email protected]
     [email protected]
     [email protected]
   }
   notification_email_from [email protected]
   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 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.138.16
    }
}

master

[root@keepalived ~]# cat /etc/keepalived/keepalived.conf 
! Configuration File for keepalived

global_defs {
   notification_email {
     [email protected]
     [email protected]
     [email protected]
   }
   notification_email_from [email protected]
   smtp_server 192.168.200.1
   smtp_connect_timeout 30
   router_id LVS_DEVEL
   vrrp_skip_check_adv_addr

 #  vrrp_strict

   vrrp_garp_interval 0
   vrrp_gna_interval 0
}

vrrp_instance VI_1 {
    state MASTER
    interface ens33
    virtual_router_id 51
    priority 200
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.138.16
    }
}

4.刷新服务查看ip地址

[root@keepalived ~]# service keepalived restart
Redirecting to /bin/systemctl restart keepalived.service
[root@keepalived ~]# ip add
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:74:9f:54 brd ff:ff:ff:ff:ff:ff
    inet 192.168.138.135/24 brd 192.168.138.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet 192.168.138.16/32 scope global ens33
       valid_lft forever preferred_lft forever

5.模拟vip漂移

​ 5.1 停止有vip的那台服务器的keepalived服务，在另一台keepalived服务器查看是否有vip

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

[root@mysqlrouter ~]# ip add
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:f0:9d:ae brd ff:ff:ff:ff:ff:ff
    inet 192.168.138.134/24 brd 192.168.138.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet 192.168.138.16/32 scope global ens33
       valid_lft forever preferred_lft forever

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

[root@keepalived ~]# ip add
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:74:9f:54 brd ff:ff:ff:ff:ff:ff
    inet 192.168.138.135/24 brd 192.168.138.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet 192.168.138.16/32 scope global ens33
       valid_lft forever preferred_lft forever

6.发送告警

[root@keepalived mail]# cat sendmail.sh
#!/bin/bash

mkdir  -p /mail/$RANDOM-sc

! Configuration File for keepalived

global_defs {
   notification_email {
     [email protected]
     [email protected]
     [email protected]
   }
   notification_email_from [email protected]
   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
}
#keepalived进程去封装vrrp报文
#当keepalived进程发送vrrp报文，执行的脚本，每隔3秒钟执行一次，配置名字叫send_mail
#在vrrp实例里的配置，只要启动keepalived进程就会每隔3秒执行一次，不管你是master还是backup
#/mail/sendmail.sh 需要自己去创建，编写
vrrp_script send_mail {
        script "/mail/sendmail.sh"
        interval 3	#每隔3秒钟就执行一次这个脚本
}

vrrp_instance VI_1 {
    state MASTER
    interface ens33
    virtual_router_id 51
    priority 200
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
	#追踪执行脚本，只要成为master，发送vrrp宣告消息就执行脚本
    track_script {
            send_mail
    }
#notify_master 状态改变为MASTER后执行的脚本
notify_master /mail/master.sh
#notify_backup 状态改变为BACKUP后执行的脚本
notify_backup /mail/backup.sh
#notify_stop  VRRP停止后后执行的脚本
notify_stop /mail/stop.sh

    virtual_ipaddress {
        192.168.138.16
    }
}

[root@mysqlrouter mail]# ls
15107-master  19951-stop  21961-backup  backup.sh  master.sh  sendmail.sh  stop.sh

keepalived正常启动的时候，共启动3个进程：
一个是父进程，负责监控其子进程；一个是VRRP子进程，另外一个是checkers子进程；
两个子进程都被系统watchdog看管，两个子进程各自负责自己的事。
Healthcheck子进程检查各自服务器的健康状况，，例如http,lvs。如果healthchecks进程检查到master上服务不可用了，就会通知本机上的VRRP子进程，让他删除通告，并且去掉虚拟IP，转换为BACKUP状态。

[root@keepalived mail]# ps aux|grep keepalived
root       3524  0.0  0.0 123064  1408 ?        Ss   21:55   0:00 /usr/sbin/keepalived -D
root       3525  0.0  0.1 134036  3396 ?        S    21:55   0:00 /usr/sbin/keepalived -D
root       3526  0.0  0.1 133972  2912 ?        S    21:55   0:00 /usr/sbin/keepalived -D
root       3749  0.0  0.0 112824   980 pts/0    S+   21:59   0:00 grep --color=auto keepalived

四.keepalived实现双vip功能

思路：2个vrrp实例，2个vip，2个实例互为主备

1.第一台服务器的配置

[root@keepalived ~]# cat /etc/keepalived/keepalived.conf
vrrp_instance VI_1 {
    state MASTER
    interface ens33
    virtual_router_id 51
    priority 200
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    track_script {
	send_mail 
    }
#notify_master 状态改变为MASTER后执行的脚本
    notify_master /mail/master.sh

#notify_backup 状态改变为BACKUP后执行的脚本
    notify_backup /mail/backup.sh

#notify_stop  VRRP停止后后执行的脚本
    notify_stop /mail/stop.sh
    virtual_ipaddress {
        192.168.138.16
    }
}

vrrp_instance VI_2 {
    state backup
    interface ens33
    virtual_router_id 52
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    track_script {
        send_mail
    }
#notify_master 状态改变为MASTER后执行的脚本
    notify_master /mail/master.sh

#notify_backup 状态改变为BACKUP后执行的脚本
    notify_backup /mail/backup.sh

#notify_stop  VRRP停止后后执行的脚本
    notify_stop /mail/stop.sh
    virtual_ipaddress {
        192.168.138.17
    }
}

# 第二台服务器的配置
vrrp_script send_mail {
   script "/mail/sendmail.sh"
   interval 3  #每隔3秒钟就执行一次这个脚本
}

vrrp_instance VI_1 {
    state backup
    interface ens33
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
#追踪执行脚本，只要成为master，发送vrrp宣告消息就执行脚本
track_script {
   send_mail
}
#notify_master 状态改变为MASTER后执行的脚本
notify_master /mail/master.sh

#notify_backup 状态改变为BACKUP后执行的脚本
notify_backup /mail/backup.sh

#notify_stop  VRRP停止后后执行的脚本
notify_stop /mail/stop.sh
    virtual_ipaddress {
        192.168.138.16
    }
}

vrrp_instance VI_2 {
    state MASTER
    interface ens33
    virtual_router_id 52
    priority 200
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    track_script {
        send_mail
    }
#notify_master 状态改变为MASTER后执行的脚本
    notify_master /mail/master.sh

#notify_backup 状态改变为BACKUP后执行的脚本
    notify_backup /mail/backup.sh

#notify_stop  VRRP停止后后执行的脚本
    notify_stop /mail/stop.sh
    virtual_ipaddress {
        192.168.138.17
    }
}

2.停掉其中一台服务器的keepalived

[root@mysqlrouter mail]# ip add
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:f0:9d:ae brd ff:ff:ff:ff:ff:ff
    inet 192.168.138.134/24 brd 192.168.138.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet 192.168.138.17/32 scope global ens33
       valid_lft forever preferred_lft forever

[root@keepalived ~]# ip add
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:74:9f:54 brd ff:ff:ff:ff:ff:ff
    inet 192.168.138.135/24 brd 192.168.138.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet 192.168.138.16/32 scope global ens33
       valid_lft forever preferred_lft forever

五.mysql监控

六.压力测试

1.安装sysbench

[root@localhost ~]# rpm -ivh mysql80-community-release-el7-9.noarch.rpm

[root@localhost ~]# yum install mysql-community-devel

[root@localhost sysbench-1.0.15]# yum -y install automake

[root@localhost sysbench-1.0.15]# yum -y install libtool

[root@localhost ~]# tar xf sysbench-1.0.15.tar.gz

[root@localhost ~]# cd sysbench-1.0.15

[root@localhost sysbench-1.0.15]# ./autogen.sh

[root@localhost sysbench-1.0.15]# ./configure

[root@localhost sysbench-1.0.15]# make -j

[root@localhost sysbench-1.0.15]# make install

[root@localhost sysbench-1.0.15]# which sysbench
/usr/local/bin/sysbench

2.测试

在mysql里创建sbtest（默认）这个数据库用于测试

[root@my-slv1 mysql]# mysql -uscwrite -p'sanchuang123#' -h 192.168.138.134 -P 7001 
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 6
Server version: 5.7.38-log MySQL Community Server (GPL)

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

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

scwrite@(none) 15:04 mysql>create database sbtest;
Query OK, 1 row affected (0.01 sec)

[root@localhost lianxi]# sysbench --mysql-host=192.168.138.134 --mysql-port=7001 --mysql-user=scwrite --mysql-password='sanchuang123#'  /usr/share/sysbench/oltp_common.lua   --tables=10  --table_size=10000 prepare
sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)

Creating table 'sbtest1'...
Inserting 10000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...
Creating table 'sbtest2'...
Inserting 10000 records into 'sbtest2'
Creating a secondary index on 'sbtest2'...
Creating table 'sbtest3'...
Inserting 10000 records into 'sbtest3'
Creating a secondary index on 'sbtest3'...
Creating table 'sbtest4'...
Inserting 10000 records into 'sbtest4'
Creating a secondary index on 'sbtest4'...
Creating table 'sbtest5'...
Inserting 10000 records into 'sbtest5'
Creating a secondary index on 'sbtest5'...
Creating table 'sbtest6'...
Inserting 10000 records into 'sbtest6'
Creating a secondary index on 'sbtest6'...
Creating table 'sbtest7'...
Inserting 10000 records into 'sbtest7'
Creating a secondary index on 'sbtest7'...
Creating table 'sbtest8'...
Inserting 10000 records into 'sbtest8'
Creating a secondary index on 'sbtest8'...
Creating table 'sbtest9'...
Inserting 10000 records into 'sbtest9'
Creating a secondary index on 'sbtest9'...
Creating table 'sbtest10'...
Inserting 10000 records into 'sbtest10'
Creating a secondary index on 'sbtest10'..

[root@localhost lianxi]# sysbench --threads=4 --time=20 --report-interval=5  --mysql-host=192.168.138.134  --mysql-port=7001 --mysql-user=scwrite  --mysql-password='sanchuang123#' /usr/share/sysbench/oltp_read_write.lua --tables=10  --table_size=100000  run
sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 4
Report intermediate results every 5 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 5s ] thds: 4 tps: 174.75 qps: 3511.07 (r/w/o: 2459.35/183.75/867.97) lat (ms,95%): 27.66 err/s: 0.60 reconn/s: 0.00
[ 10s ] thds: 4 tps: 177.61 qps: 3552.83 (r/w/o: 2487.16/203.61/862.06) lat (ms,95%): 27.66 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 4 tps: 173.38 qps: 3469.66 (r/w/o: 2428.96/214.98/825.72) lat (ms,95%): 29.19 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 4 tps: 172.41 qps: 3449.02 (r/w/o: 2414.35/231.61/803.05) lat (ms,95%): 28.67 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            48972
        write:                           4175
        other:                           16807
        total:                           69954
    transactions:                        3495   (174.58 per sec.)
    queries:                             69954  (3494.25 per sec.)
    ignored errors:                      3      (0.15 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          20.0191s
    total number of events:              3495

Latency (ms):
         min:                                   15.84
         avg:                                   22.90
         max:                                   49.10
         95th percentile:                       28.16
         sum:                                80028.98

Threads fairness:
    events (avg/stddev):           873.7500/3.34
    execution time (avg/stddev):   20.0072/0.01