2.1 mysql高可用部署

2.1.1 环境介绍

2.1.2 开始部署mysql主从

初始化各主机环境

设置各角色主机名

1
2
3
4
5
6
7
8
9
10
11
12
# master节点配置主机名
cat > /etc/hostname <<EOF
stage-project-mysql-master
EOF
hostnamectl set-hostname stage-project-mysql-master && bash


# slave01节点配置主机名
cat > /etc/hostname <<EOF
stage-project-mysql-slave01
EOF
hostnamectl set-hostname stage-project-mysql-slave01 && bash

创建mysql57的数据目录

1
mkdir -p  /data/mysql

磁盘分区并挂载至data数据目录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# 查看磁盘情况
 lsblk
NAME            MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
sda               8:0    0   60G  0 disk
├─sda1            8:1    0    1G  0 part /boot
└─sda2            8:2    0   59G  0 part
  └─centos-root 253:0    0   59G  0 lvm  /
sdb               8:16   0  100G  0 disk	# 新添加磁盘

# 创建lvm卷
pvcreate /dev/sdb
vgcreate data /dev/sdb
lvcreate --name data_01 -l 100%FREE data
mkfs.ext4 /dev/mapper/data-data_01

cat >> /etc/fstab<<EOF
/dev/mapper/data-data_01 /data/mysql ext4 defaults,noatime 0
EOF

mkdir -p  /data/mysql

mount -a

master安装mysql57

配置mysql57的yum仓库地址并安装mysql57

1
2
3
4
5
6
7
8
9
10
11
12
13
14
#!/bin/bash
cat > /etc/yum.repos.d/mysql57-community.repo<<EOF
[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-5.7-community-el7-x86_64/
enabled=1
gpgcheck=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
EOF


yum install mysql-community-server -y
#systemctl start mysqld
systemctl status mysqld && systemctl enable mysqld

修改mysql的数据目录路径及权限

1
2
3
4
5
$ vim /etc/my.cnf
datadir=/data/mysql
socket=/data/mysql/mysql.sock

$ chown -R mysql:mysql /data/mysql

启动mysql服务并登录重置默认密码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
systemctl start mysqld 
grep 'temporary password' /var/log/mysqld.log
mysql -uroot -p

set global validate_password_policy=0;
set global validate_password_length=1;

# 修改本地root用户登录的密码。
alter user 'root'@'localhost' identified by 'xxx';

# admin账号为普通用户账号，防止root用户的super权限对slave的readonly不生效。
grant References,select,index,CREATE VIEW,SHOW VIEW,ALTER ROUTINE,CREATE ROUTINE,EXECUTE on *.* to 'backend_api'@'%' identified by 'xxx';
flush privileges;

主从配置

master创建repl账号,slave使用此账号来master同步数据。

1
2
3
grant replication slave, replication client on *.* to 'repl'@'%' identified by 'xxx';
flush privileges;

查看master的初始状态。

1
2
3
4
5
6
7
8
mysql> show master status\G
*************************** 1. row ***************************
             File: binlog44-master.000002
         Position: 2203
     Binlog_Do_DB:
 Binlog_Ignore_DB: information_schema,performation_schema,sys
Executed_Gtid_Set:
1 row in set (0.00 sec)

slave使用repl账号去连接master。

1
2
3
4
5
6
7
8
9
10
change master to master_host='192.168.99.58',
master_user='repl',
master_password='xxx',
master_port=3306,
master_log_file='binlog58-master.000002',
master_log_pos=1258,
master_connect_retry=30;

start slave;

2.1.3 mysql主从HA配置

实现思路是：master的优先级为100，backup的优先级为99；在master上面配置一个检测nginx监控状态的脚本（backup不用配置），当发现master的nginx故障后将master的优先级减2为98，使其backup优先级比master高，bakup获取到vip对外提供服务；

当master服务器上面的mysql服务恢复正常后，master的优先级不减2恢复原来的100，master获取到vip对外提供服务

邮件报警实现思路：使用keepalived的状态转换执行脚本参数notify_master|notify_backup；当角色变为master或backup时去调用发送邮件的脚本给指定用户发送邮件通知

参考文档：

https://blog.csdn.net/guoxilen/article/details/78460445

https://www.cnblogs.com/dhzg/p/11274811.html

https://www.cnblogs.com/dannylinux/p/14659310.html

配置详解

https://blog.csdn.net/mofiu/article/details/76644012

抢占和非抢占概念

https://www.jianshu.com/p/c55956882962

master及slave安装keepalived

1
$ yum install -y keepalived

master配置keepalived

修改master的keepalived配置文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
[root@stage-project-mysql-master /]# cat /etc/keepalived/keepalived.conf
global_defs {
   router_id project_stage_mysql_master
   # 默认是不跳过检查。检查收到的VRRP通告中的所有地址可能会比较耗时，设置此命令的意思是，如果通告与接收的上一个通告来自相同的master路由器，则不执行检查(跳过检查)。
   vrrp_skip_check_adv_addr
   # 严格遵守VRRP协议。下列情况将会阻止启动Keepalived：1. 没有VIP地址。2. 单播邻居。3. 在VRRP版本2中有IPv6地址。
   #vrrp_strict
   # 不添加任何iptables规则。默认是添加iptables规则的。
   vrrp_iptables
   # 在一个接口发送的两个免费ARP之间的延迟。可以精确到毫秒级。默认是0
   vrrp_garp_interval 0
   vrrp_gna_interval 0

}

vrrp_script chk_msql {
    script "/usr/local/bin/check_mysql.sh"
    interval 3
    fall 2
    rise 2
}

vrrp_instance VI_1 {
    state BACKUP
    nopreempt
    interface eth0
    virtual_router_id 58
    priority 100
    advert_int 1
    notify_master "/etc/keepalived/notify.sh master"
    notify_backup "/etc/keepalived/notify.sh backup"
    notify_fault "/etc/keepalived/notify.sh fault"
    unicast_src_ip 192.168.99.58
    unicast_peer {
        192.168.99.59
    }

    authentication {
        auth_type PASS
        auth_pass project_stage_mysql_ha
    }

    track_script {
        chk_msql
    }

    virtual_ipaddress {
        192.168.99.249 dev eth0 labl eth0:0
    }
}

添加check_mysql的脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
cat > /usr/local/bin/check_mysql.sh<<EOF
#!/bin/bash
MYSQL=mysql
MYSQL_HOST=localhost
MYSQL_USER=root
MYSQL_PASSWORD=xxx


mysql -uroot -p\$MYSQL_PASSWORD -e "show status;" >/dev/null 2>&1
if [ \$? == 0 ]
then
    echo " \$host mysql login successfully "
    exit 0
else
    echo " \$host mysql login faild"
    #/etc/init.d/keepalived stop
    exit 1
fi
EOF

Slave配置keepalived

master将check_mysql脚本传送到slve机器上

1
2
3
4
5
# 传送check_mysql脚本
$ scp /usr/local/bin/check_mysql.sh root@192.168.99.59:/usr/local/bin/

# 传送keepalived配置文件
$ scp /etc/keepalived/keepalived.conf root@192.168.99.59:/etc/keepalived/

修改slave的keepalived配置文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
[root@stage-project-mysql-slave01 ~]# cat /etc/keepalived/keepalived.conf
global_defs {
   router_id project_stage_mysql_slave
   # 默认是不跳过检查。检查收到的VRRP通告中的所有地址可能会比较耗时，设置此命令的意思是，如果通告与接收的上一个通告来自相同的master路由器，则不执行检查(跳过检查)。
   vrrp_skip_check_adv_addr
   # 严格遵守VRRP协议。下列情况将会阻止启动Keepalived：1. 没有VIP地址。2. 单播邻居。3. 在VRRP版本2中有IPv6地址。
   #vrrp_strict
   # 不添加任何iptables规则。默认是添加iptables规则的。
   vrrp_iptables
   # 在一个接口发送的两个免费ARP之间的延迟。可以精确到毫秒级。默认是0
   vrrp_garp_interval 0
   vrrp_gna_interval 0
}

vrrp_script chk_msql {
    script "/usr/local/bin/check_mysql.sh"
    interval 3
    fall 2
    rise 2
}

vrrp_instance VI_1 {
    state BACKUP
    nopreempt
    interface eth0
    virtual_router_id 58
    priority 80
    advert_int 1
    notify_master "/etc/keepalived/notify.sh master"
    notify_backup "/etc/keepalived/notify.sh backup"
    notify_fault "/etc/keepalived/notify.sh fault"
    unicast_src_ip 192.168.99.59
    unicast_peer {
        192.168.99.58
    }

    authentication {
        auth_type PASS
        auth_pass project_stage_mysql_ha
    }

    track_script {
        chk_msql
    }

    virtual_ipaddress {
        192.168.99.249 dev eth0 labl eth0:0
    }
}

keepalived加入到开机自启并启动服务

1
2
3
4
5
# master 先启动
$ systemctl enable keepalived && systemctl start keepalived && systemctl status keepalived

# slave后启动
$ systemctl enable keepalived && systemctl start keepalived && systemctl status keepalived

测试结论

1、master首先启动后master获得vip权限，slave后启动为backup状态。

2、当master的keepalived进程停止后，vip会从master漂移至slave上（进程重新启动vip不会漂移至master，因为是非抢占模式）。

3、当master的mysqld健康检查不通过时，vip会从master漂移至slave上（mysqld重新启动vip不会漂移至master，因为是非抢占模式）。

4、问题解决：当master修复完毕之后，如果要将vip从slave漂移至master，就将master的keepalived和mysqld服务启动，然后停止一下slave的keepalived进程再启动，这样vip就漂移至master了。

配置ip切换邮件告警

添加通知脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
$ vim /etc/keepalived/notify.sh
#!/bin/bash
#
contact='614214568@qq.com'
notify() {
    mailsubject="$(hostname) to be $1, vip转移"
    mailbody="$(date +'%F %T'): vrrp transition, $(hostname) changed to be $1"
    echo "$mailbody" | mail -s "$mailsubject" $contact
}
case $1 in
master)
    notify master
    ;;
backup)
    notify backup
    ;;
fault)
    notify fault
    ;;
*)
    echo "Usage:(basename $0) {master|backup|fault}"
    exit 1
    ;;
esac

$ chmod +x /etc/keepalived/notify.sh

配置smtp信息

1
2
3
4
5
6
$ yum -y install mailx
$ vim /etc/mail.rc
set from=smtp@xxx.cn
set smtp=smtp.qiye.aliyun.com
set smtp-auth-user="smtp@xxx.cn" smtp-auth-password="xxx"
set smtp-auth=login

添加脚本到keepalived配置文件中并重启

1
2
3
4
5
6
7
8
9
10
11
12
13
$ vim /etc/keepalived/keepalived.conf
vrrp_instance VI_1 {
    state MASTER
    interface eth0
    virtual_router_id 58
    priority 100
    advert_int 1
    nopreempt
    notify_master "/etc/keepalived/notify.sh master"	# 状态变为 master执行脚本
    notify_backup "/etc/keepalived/notify.sh backup"	# 状态变为 backup执行脚本
    notify_fault "/etc/keepalived/notify.sh fault"		# 状态变为fault执行脚本
    ...... 省略
}