CentOS 7.6安装配置MySQL 5.7 GTID双主复制+Keepalived实现高可用

一、 相关概念介绍：

1、 GTID 简介：

GTID ： Global Transaction ID ，全局事务 ID ，是一个已提交事务的编号，并且是一个全局唯一的编号。 MySQL 5.6 版本之后在主从复制类型上新增了 GTID 复制。通过 GTID 保证了每个在 master 节点上提交的事务在集群中有一个唯一的 ID ，这种方式强化了数据库的主从一致性、故障恢复及容错能力。

2、 GTID 工作原理：

GTID 用于在 binlog 中唯一标识一个事务。当事务提交时， MySQL 在写 binlog 时，会先写一个特殊的 binlog event ，类型为 GTID_Event ，指定下一个事务的 GTID ，然后再写事务的 binlog 。主从同步时 GTID_Event 和事务的 binlog 都会传递到 slave 节点， slave 节点在执行时也用同样的 GTID 写 binlog ，这样主从同步后，就可通过 GTID 确定 slave 节点同步到的位置了。 GTID 使用 master_auto_position=1 代替了基于 master_log_file 和 master_log_pos 的主从复制构建方式。

3、 GTID 组成： GTID=server_uuid:transaction_id

（1） server_uuid ： MySQL 实例的唯一标识，每台主机的 server_uuid 都不同（ 128 位），保存在数据目录下的 auto.cnf 文件中。 MySQL 第一次启动时创建 auto.cnf 文件，并生成 server_uuid ，之后 MySQL 再次启动时不会重复生成 server_uuid ，而是使用 auto.cnf 中的 server_uuid 。

（2） transaction_id ：事务提交时由系统顺序分配的一个不会重复的序列号，代表了该实例上已经提交的事务数量，一般来说随着事务提交递增。

备注：使用 server_uuid:transaction_id 共同组成一个 GTID 的好处是，由于 server_uuid 唯一，即使一个集群内多个节点同时写入，也不会造成 GTID 冲突。

4、 GTID 使用限制：

（1） 不支持 create table ... select 语句

（2） 不支持针对临时表的操作，即 create | drop temporary table

（3）不 支持非事务存储引擎（如： MyISAM ）

（4） 不支持 sql_slave_skip_counter

5、 Keepalived 简介：

Keepalived 是 VRRP 协议的软件实现，原生设计是为了高可用 IPVS 服务，可以实现如下功能：

（1） 基于 VRRP 协议完成地址流动

（2） 为 VIP 地址所在的 IPVS 集群节点生成 IPVS 规则（在配置文件中预先定义）

（3） 为 IPVS 集群的各 RS 做健康状态检测

（4） 基于脚本调用接口，通过执行脚本完成脚本中定义的功能，进而影响集群事务

Keepalived 官网： https://www.keepalived.org/

6、 Keepalived 架构图：

当 Keepalived 启动后，会生成 WatchDog 、 Checkers 和 VRRP 三个工作进程：

（1） WatchDog ：负责监控 Checkers 和 VRRP 进程

（2） Checkers ：负责检测主机健康状态

（3） VRRP ：负责当 master 节点上的服务不可用时，可通过 VRRP 将其切换到 backup 节点上

7、 VRRP 协议简介：

VRRP ： Virtual Router Redundancy Protocol ，虚拟路由冗余协议。 VRRP 是一种路由容错协议，也可称为备份路由协议。一个局域网内的所有主机都设置缺省路由，当网内主机发出的目的地址不在本网段时，报文将被通过缺省路由发往外部路由器，从而实现了主机与外部网络的通信。当缺省路由器 down 掉（即端口关闭）后，内部主机将无法与外部通信，如果路由器设置了 VRRP ，虚拟路由将启用备份路由器，从而实现全网通信。

8、 VRRP 相关术语简介：

（1） 虚拟路由器：由一个 master 路由器和多个 backup 路由器组成，主机将虚拟路由器当作默认网关

（2） VRID ：虚拟路由器标识，有相同 VRID 的一组路由器构成一个虚拟路由器，取值范围为 0~255

（3） master 路由器：虚拟路由器中承担报文转发任务的路由器

（4） backup 路由器： master 路由器出现故障时，能代替 master 路由器工作的路由器

（5） 虚拟 IP 地址：虚拟路由器的 IP 地址，一个虚拟路由器可以拥有一个或多个 IP 地址

（6） 虚拟 MAC 地址：一个虚拟路由器拥有一个虚拟 MAC 地址，虚拟 MAC 地址的格式为 00-00-5E-00-01-{VRID} ，通常情况下，虚拟路由器回应 ARP 请求使用的是虚拟 MAC 地址，只有虚拟路由器做特殊配置时，才回应接口的真实 MAC 地址

（7） 优先级： VRRP 根据优先级来确定虚拟路由器中每台路由器的地位，取值范围为 0~255 ，数值越小表示优先级越低，数值越大表示优先级越高，当数值为 0 时，表示当前 master 节点放弃持有 VIP ，当数值为 255 时，表示当前 master 节点的优先级最高且已持有 VIP ，所以 VRRP 的优先级可选取值范围为 1~254

（8） 非抢占模式：如果 backup 路由器工作在非抢占模式下，则只要 master 路由器没有出现故障， backup 路由器即使随后被配置了更高的优先级也不会成为 master 路由器

（9） 抢占模式：如果 backup 路由器工作在抢占模式下，当它收到 VRRP 报文后，会将自己的优先级与通告报文中的优先级进行比较，如果自己的优先级比当前的 master 路由器的优先级高，就会主动抢占成为 master 路由器，否则将保持 backup 状态

9、 VRRP 工作流程：

（1） 虚拟路由器中的路由器根据优先级选举出 master ， master 路由器通过发送免费 ARP 报文，将自己的虚拟 MAC 地址通知给与它连接的设备或主机，从而承担报文转发任务；

（2） master 路由器周期性发送 VRRP 报文，以公布其配置信息（优先级等）和工作状态；

（3） 如果 master 路由器出现故障，虚拟路由器中的 backup 路由器将根据优先级重新选举出新的 master ；

（4） 虚拟路由器状态切换时， master 路由器由一台设备切换为另一台设备，新的 master 路由器只是简单地发送一个携带虚拟路由器的 MAC 地址和虚拟 IP 地址信息的免费 ARP 报文，这样就可以更新与它连接的主机或设备中的 ARP 相关信息。网络中的主机感知不到 master 路由器已经切换为另一台设备。

备注： backup 路由器的优先级高于 master 路由器时，由 backup 路由器的工作模式（抢占模式和非抢占模式）决定是否重新选举 master

二、 准备工作（如未特殊说明，在两个节点中分别执行如下操作）：

1、 演示环境：

IP	操作系统	主机名	角色	软件包版本	安装方式
192.168.1.145	CentOS   7.6 x86_64	node1	master	Percona   Server ： 5.7.26-29-log Keepalived ： 1.3.5-8	yum
192.168.1.146	CentOS   7.6 x86_64	node2	backup	Percona   Server ： 5.7.26-29-log Keepalived ： 1.3.5-8	yum

备注： VIP 为 192.168.1.130

2、 集群架构：

3、 关闭 SELinux 和 firewalld

4、配置 epel 源

5、 配置 percona 源：

# yum -y install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

# yum repolist

# yum list | grep -i percona

6、 配置节点时间同步

7、 配置主机名

8、 配置 /etc/hosts 文件：

# vim /etc/hosts

192.168.1.145 node1

192.168.1.146 node2

三、 安装配置 Percona Server ，实现 GTID 双主复制（如未特殊说明，在两个节点中分别执行如下操作）：

1、 安装 Percona Server ： # yum -y install Percona-Server-server-57

2、 修改 MySQL 配置文件：

# mv /etc/my.cnf /etc/my.cnf.bak

# update-alternatives --install /etc/my.cnf my.cnf "/etc/percona-server.cnf" 200

# vim /etc/percona-server.conf.d/mysqld.cnf

master 节点：

[mysqld]

port=3306

socket=/var/lib/mysql/mysql.sock

datadir=/var/lib/mysql

pid-file=/var/run/mysqld/mysqld.pid

log-error=/var/log/mysqld.log

lower_case_table_names=1

character_set_server=utf8mb4

collation_server=utf8mb4_general_ci

innodb_file_per_table=1

skip_name_resolve=1

slow_query_log=1

slow_query_log_file=mysql-slow.log

symbolic-links=0

explicit_defaults_for_timestamp=1

log_bin=mysql-bin

log_bin_index=mysql-bin.index

relay_log=relay-log

relay_ log_index=relay-log.index

sync_binlog=1

innodb_flush_log_at_trx_commit=1

binlog_format=row

gtid_mode=on

enforce_gtid_consistency=on

server_id=1

备注：

（1） backup 节点的 server_id=2

（2） 从 MySQL 5.7 版本开始， gtid_mode 支持动态修改，可取值为：

a、 off ：不支持 GTID 事务，生成的是匿名事务， slave 节点也只能应用匿名事务

b、 off_permissive ：生成的是匿名事务， slave 节点可以应用匿名事务和 GTID 事务

c、 on_permissive ：生成的是 GTID 事务， slave 节点可以应用匿名事务和 GTID 事务（此步骤操作完成后， master 节点的二进制日志就会变成 GTID 模式）

d、 on ：支持 GTID 事务，生成的是 GTID 事务， slave 节点也只能应用 GTID 事务

注意：在生产环境中，可能有把传统复制改为 GTID 复制模式的需求， gtid_mode 虽然支持动态修改，但不支持跳跃式修改，比如从 on_permissive 修改为 off 是不可以的

（3） enforce_gtid_consistency 主要用于不让违反 GTID 的操作执行，可取值为：

a、 off ：允许所有操作

b、 on ：不允许有违反 GTID 的操作，且报错

c、 warn ： MySQL 5.7 版本新增，允许所有操作，但是违反 GTID 的操作会提示警告

（4） MySQL 5.6 版本中使用 GTID 复制模式，必须要开启参数 log_slave_updates=1 ，但在 MySQL 5.7 版本中使用 gtid_executed 系统表记录已经执行的 GTID 集合信息，所以就不用开启参数 log_slave_updates=1 ，开启的意义是把 relay log 中的日志内容再次记录到 slave 节点的本地 binlog 中

3、 初始化 MySQL 数据： # mysqld --initialize --user=mysql

备注：确保初始化前 /var/lib/mysql 目录为空，初始化完成后会在此目录中生成各类文件

4、 启动 MySQL 服务：

# systemctl start mysqld.service

# ss -tunlp | grep mysqld

# systemctl enable mysqld.service

# systemctl status mysqld.service

5、 查看 root@localhost 用户的初始密码： # grep password /var/log/mysqld.log

6、 配置 MySQL 安全向导： # mysql_secure_installation

7、 创建具有复制权限的用户：

master 节点：

# mysql -uroot -p

mysql> create user 'repluser1'@'192.168.1.%' identified by '123456';

mysql> grant replication slave on *.* to 'repluser1'@'192.168.1.%';

mysql> flush privileges;

mysql> show global variables like 'server_uuid';

备注：

（1） backup 节点创建的复制用户为 repluser2@192.168.1.% ，不能与 master 节点中的用户重名

（2） 内容和 /var/lib/mysql/auto.cnf 文件中 server-uuid 的值一致

8、 backup 节点使用具有复制权限的用户 repluser1 连接至 master 节点，并启动复制线程：

mysql> change master to master_host='192.168.1.145',master_user='repluser1',master_password='123456',master_port=3306, master_auto_position=1;

mysql> start slave;

备注：

（1） start slave 等同于分别执行 start slave io_thread 和 start slave sql_thread

（2） stop slave 表示停止主从复制线程

（3） 重启 slave 节点所在的主机，复制线程会自动启动

mysql> show slave status\G

备注：

（1）只 有当 Slave_IO_Running 和 Slave_SQL_Running 的值都为 Yes 时，复制线程才算启动成功

（2） Seconds_Behind_master 的值为 0 ，说明 backup 节点没有落后于 master 节点

（3） 复制时的详细信息记录在 backup 节点的错误日志 /var/log/mysqld.log 中

（4） backup 节点 start slave 时，会计算 show slave status 中 Retrieved_Gtid_Set 和 Executed_Gtid_Set 的并集，然后将此 GTID 并集发送给 master 节点。 master 节点会使用 backup 节点请求的 GTID 集合和 master 节点自身的 gtid_executed 比较，把 backup 节点 GTID 集合里缺失的事务全部发送给 backup 节点。如果 backup 节点缺失的 GTID 已经被 master 节点清除，则 backup 节点会提示 1236 错误， I/O 线程中断。

9、 master 节点使用具有复制权限的用户 repluser2 连接至 backup 节点，并启动复制线程：

mysql> change master to master_host='192.168.1.146',master_user='repluser2',master_password='123456',master_port=3306, master_auto_position=1;

mysql> start slave;

mysql> show slave status\G

10、 查看 GTID 相关信息：

mysql> show master status\G

master 节点：

backup 节点：

mysql> show slave hosts;

master 节点：

backup 节点：

mysql> show slave status\G

mysql> show global variables like '%gtid%';

master 节点：

backup 节点：

备注：

（1） gtid_executed ：当前实例上已经执行过的 GTID 集合，实际上包含了所有记录到 binlog 中的事务。如果 set sql_log_bin=0 ，执行的事务不会生成 binlog 事件，也不会记录到 gtid_executed 中。执行 reset master 可以将变量 @@global.gtid_executed 清空。

（2） gtid_owned ：当前实例正在执行中的 GTID ，以及对应的线程 ID

（3） gtid_purged ：记录当前实例执行过，但已被清除的 GTID 集合。 gtid_purged 是 gtid_executed 的子集。只有 gtid_executed 为空时才能手动设置 gtid_purged 变量，此时会将 gtid_executed 更新为和 gtid_purged 相同的值。

mysql> show variables like '%gtid%';

master 节点：

backup 节点：

备注： gtid_next ， session 会话级别的变量，如何产生下一个 GTID ，可取值为

（1） automatic ：默认取值，在每次事务提交时自动生成新的 GTID ，它从当前已执行的 GTID 集合（即 gtid_executed ）中，找一个大于 0 的、未使用的 transaction_id 最小值作为下个事务的 GTID ，同时在 binlog 的实际更新事务事件前插入一条 set gtid_next 事件，所以即使是同一个 server_uuid ，也不能通过 transaction_id 的大小来判断事务的顺序。

（2） anonymous ：执行事务不会产生 GTID

（3） 显示指定 GTID ：可以指定任意合法的 GTID ，但不能是当前 gtid_executed 中已经包含的 GTID

mysql> show processlist;

master 节点：

backup 节点：

11、 master 节点创建测试数据：

mysql> create database db;

mysql> use db;

mysql> create table tb(id int unsigned auto_increment primary key not null,age int not null);

mysql> desc tb;

mysql> insert into tb(age) values(35),(40);

mysql> select * from tb;

12、 backup 节点查看测试数据：

mysql> show databases like 'db';

mysql> select * from db.tb;

13、 backup 节点创建测试数据：

mysql> insert into db.tb(age) values(60),(80);

mysql> select * from db.tb;

14、 master 节点查看测试数据：

mysql> select * from db.tb;

四、 安装配置 Keepalived ，实现 MySQL 高可用（如未特殊说明，在两个节点中分别执行如下操作）：

1、 安装 Keepalived ： # yum -y install keepalived

2、 定义检测 MySQL 服务的脚本：

# vim /etc/keepalived/chk_mysqld.sh

#!/bin/bash

n=$(ps -C mysqld --no-headers | wc -l)

if [ $n -eq 0 ]; then

systemctl stop keepalived.service

fi

# chmod +x /etc/keepalived/chk_mysqld.sh

# bash -n /etc/keepalived/chk_mysqld.sh

备注：通过 Keepalived 自带的服务监控功能和自定义脚本实现 MySQL 故障自动转移

3、 master 节点配置 Keepalived ：

# cd /etc/keepalived

# mv keepalived.conf{,.bak}

# openssl rand -base64 7 --> IDDf1j+yfw==

# vim keepalived.conf

global_defs {

notification_email {

root@localhost

}

notification_email_from node1@localhost

smtp_server 127.0.0.1

smtp_connect_timeout 30

router_id node1

vrrp_mcast_group4 224.1.100.88

}

vrrp_script chk_mysqld {

script "/etc/keepalived/chk_mysqld.sh"

interval 10

}

vrrp_instance VI_1 {

state BACKUP

nopreempt

interface ens160

virtual_router_id 50

priority 100

advert_int 5

authentication {

auth_type PASS

auth_pass IDDf1j+yfw==

}

virtual_ipaddress {

192.168.1.130

}

track_script {

chk_mysqld

}

}

备注：常用指令说明

（1） notification_email ：收件箱

（2） notification_email_from ：发件箱

（3） vrrp_mcast_group4 ： VRRP 多播地址，必须为 D 类地址，即可用 IP 范围为 224.0.0.0~239.255.255.255

（4） script ：自定义检查脚本路径

（5） interval ：自定义检查脚本的执行时间间隔，单位为秒

（6） vrrp_instance ：配置虚拟路由器实例

（7） state ： MASTER 或 BACKUP ，当前节点在此虚拟路由器上的初始状态，只能有一个为 MASTER ，其余的都应该为 BACKUP ，此处都需要配置为 BACKUP

（8） nopreempt ：定义工作模式为非抢占模式，默认为抢占模式

（9） preempt_delay ：抢占模式下，节点上线后触发新选举操作的延迟时长，单位为秒

（10） interface ：绑定当前虚拟路由器使用的物理接口

（11） virtual_router_id ：当前虚拟路由器的唯一标识，取值范围为 0~255 ，两个节点必须一致

（12）p riority ：当前主机在此虚拟路由器中的优先级，取值范围为 0~255

（13） advert_int ： VRRP 通告心跳信息和优先级信息的时间间隔，单位为秒

（14） auth_type ：认证类型

（15） auth_pass ：认证密码，两个节点必须一致

（16） virtual_ipaddress ： VIP 地址

（17） 可通过命令 # man keepalived.conf 查看 keepalived.conf 配置文件的详细帮助文档

4、 backup 节点配置 Keepalived ：

# cd /etc/keepalived

# mv keepalived.conf{,.bak}

# vim keepalived.conf

global_defs {

notification_email {

root@localhost

}

notification_email_from node2@localhost

smtp_server 127.0.0.1

smtp_connect_timeout 30

router_id node2

vrrp_mcast_group4 224.1.100.88

}

vrrp_script chk_mysqld {

script "/etc/keepalived/chk_mysqld.sh"

interval 10

}

vrrp_instance VI_1 {

state BACKUP

nopreempt

interface ens160

virtual_router_id 50

priority 98

advert_int 5

authentication {

auth_type PASS

auth_pass IDDf1j+yfw==

}

virtual_ipaddress {

192.168.1.130

}

track_script {

chk_mysqld

}

}

备注： master 和 backup 节点配置 Keepalived 时，要注意两个节点的 state 都要采用 BACKUP 状态，而且都是 nopreempt 非抢占模式，通过优先级决定哪个节点为 master 节点，避免出现冲突，发生脑裂现象

5、 master 节点启动 Keepalived 服务，并查看 VIP 信息：

# systemctl start keepalived.service

# systemctl enable keepalived.service

# systemctl status keepalived.service

# tail -f /var/log/messages

备注：从 Keepalived 启动完成之后的日志中可以得出， VRRP_Instance(VI_1) 首先变成 BACKUP 状态，由于 192.168.1.145 主机上的优先级高，状态会从 BACKUP 升级为 MASTER ，且 chk_mysqld 脚本执行成功， VIP 已经绑定在 master 节点的 ens160 网卡上

# ip a l

6、 backup 节点启动 Keepalived 服务：

# systemctl start keepalived.service

# systemctl enable keepalived.service

# systemctl status keepalived.service

# tail -f /var/log/messages

备注：从 Keepalived 启动完成之后的日志中可以得出， VRRP_Instance(VI_1) 变成 BACKUP 状态，时刻准备接管 master 节点上的 MySQL 服务，且 chk_mysqld 脚本执行成功，但并没有绑定 VIP

# ip a l

7、 测试使用 VIP 连接 master 节点：

（1） 在任意一台主机上安装 MySQL 客户端（此处为 192.168.1.144 ）： # yum -y install mariadb

（2） 在 master 或 backup 任意一个节点中创建登录用户 root@192.168.1.% ，并赋予权限：

mysql> create user 'root'@'192.168.1.%' identified by '123456';

mysql> grant all on *.* to 'root'@'192.168.1.%';

mysql> flush privileges;

（3） 在 192.168.1.144 主机上使用 VIP 连接 master 节点，并使用 status 命令查看当前数据库状态：

# mysql -uroot -p -h192.168.1.130

8、 测试高可用：

（1） master 节点模拟 MySQL 服务宕机： # systemctl stop mysqld.service

（2） master 节点查看 Keepalived 服务、日志及 VIP 信息：

# systemctl status keepalived.service  # tail -f /var/log/messages  # ip a l

备注： master 节点已经自动停止 Keepalived 服务，且 VIP 没有绑定在 master 节点的 ens160 网卡上

（3） backup 节点查看 Keepalived 服务、日志及 VIP 信息：

# systemctl status keepalived.service  # tail -f /var/log/messages  # ip a l

备注： backup 节点的 Keepalived 已经变成 MASTER 状态，且 VIP 漂移至 backup 节点的 ens160 网卡上

（4） 在 192.168.1.144 主机上使用 VIP 连接 master 节点，并使用 status 命令查看当前数据库状态：

# mysql -uroot -p -h192.168.1.130

备注：客户端没有受到任何影响，依旧可以使用 VIP 连接已经变成 MASTER 状态的 backup 节点

（5） master 节点启动 MySQL 服务： # systemctl start mysqld.service

（6） master 节点启动 Keepalived 服务： # systemctl start keepalived.service

备注： master 节点的 Keepalived 已经变成 BACKUP 状态，且 chk_mysqld 脚本执行成功，但由于是非抢占模式，所以 VIP 依旧绑定在 backup 节点的 ens160 网卡上