MySQL高可用搭建

一、MHA 架构搭建

参考文档：Mysql8 MHA高可用搭建_mysql8 mha0.58-CSDN博客

1.1 概述

MHA（Master High Availability）是一套比较成熟的 MySQL 高可用方案，也是一款优秀的故障切换和主从提升的高可用软件。在MySQL故障切换过程中，MHA能做到在30秒之内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA能在最大程度上保证数据的一致性，以达到真正的高可用。MHA还支持在线快速将master切换到其他主机，通常只需0.5-2秒。目前MHA主要支持一主多从的架构，要搭建MHA，要求一个复制集群中必须最少有三台数据库服务器。

1.2 MHA组成和原理

MHA由两部分组成：MHA Manager（管理节点）和MHA Node（数据节点）。

• MHA Manager可以单独部署在一台独立的机器上管理多个master-slave集群，也可以部署在一台slave节点上。负责检测master是否宕机、控制故障转移、检查MySQL复制状况等。
• MHA Node运行在每台MySQL服务器上，不管是Master角色，还是Slave角色，都称为Node，是被监控管理的对象节点，负责保存和复制master的二进制日志、识别差异的中继日志事件并将其差异的事件应用于其他的slave、清除中继日志。
  MHA Manager会定时探测集群中的master节点，当master出现故障时，它可以自动将最新数据的slave提升为新的master，然后将所有其他的slave重新指向新的master，整个故障转移过程对应用程序完全透明。

MHA故障处理机制：

• 把宕机master的binlog保存下来
• 根据binlog位置点找到最新的slave
• 用最新slave的relay log修复其它slave
• 将保存下来的binlog在最新的slave上恢复
• 将最新的slave提升为master
• 将其它slave重新指向新提升的master，并开启主从复制

MHA优点：

• 自动故障转移快
• 主库崩溃不存在数据一致性问题
• 性能优秀，支持半同步复制和异步复制
• 一个Manager监控节点可以监控多个集群

1.3 MHA 搭建

1.3.1 配置ssh

#其中一台master示例
#生成密钥，一直回车就行
ssh-keygen

#配置免密登入
cd /root/.ssh/
ssh-copy-id -i id_rsa root@192.168.19.50
ssh-copy-id -i id_rsa root@192.168.19.51
ssh-copy-id -i id_rsa root@192.168.19.52

其他机器也一样。

1.3.2 安装MAH软件

#下载地址：
	https://github.com/yoshinorim/mha4mysql-node/releases/tag/v0.58
#注意，所有节点都需要安装MHA node
#1、先安装相关依赖：
	yum -y install epel-release
	yum -y install perl-DBD-MySQL perl-DBI ncftp
#2、安装mha：
	rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm

#下载地址
	https://github.com/yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
#下载好了之后，先安装依赖
yum -y install epel-release
yum -y install perl-Config-Tiny perl-Time-HiRes perl-Parallel-ForkManager perl-Log-Dispatch perl-DBD-MySQL ncftp
#如果有些依赖没有，就重新安装epel包
yum -y remove epel-release
yum -y install epel-release

#下载manager包之后安装
 rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
######################################################################################
#离线安装epel-release
Download the latest epel-release rpm from
http://dl.fedoraproject.org/pub/epel/7/x86_64/Packages/e/，下载rpm文件
Install epel-release rpm:
# rpm -Uvh epel-release*rpm
	
#离线安装perl-Config-Tiny perl-Time-HiRes perl-Parallel-ForkManager perl-Log-Dispatch perl-DBD-MySQL ncftp
Download the latest epel-release rpm from
http://dl.fedoraproject.org/pub/epel/7/x86_64/Packages/p/，下载rpm文件


#创建相关目录
mkdir /etc/mha
mkdir /data1/mysql_mha
#编写配置文件
vim /etc/mha/mysql_mha.cnf
#添加

[server default]
#mha访问数据库的账号与密码
user=root
password=123456
port=3306
#指定mha的工作目录
manager_workdir=/data1/mysql_mha
#指定管理日志路径
manager_log=/data1/mysql_mha/manager.log
#指定master节点存放binlog的日志文件的目录 log_bin=mysql_bin默认是在/var/lib/mysql
master_binlog_dir=/usr/local/mysql-8.0.20/data
#指定mha在远程节点上的工作目录
remote_workdir=/data1/mysql_mha
#指定主从复制的mysq用户和密码
repl_user=root
repl_password=123456
#指定检测间隔时间
ping_interval=1
#指定一个脚本，该脚本实现了在主从切换之后，将虚拟ip漂移到新的master上
master_ip_failover_script=/data1/mysql_mha/master_ip_failover
#指定用于二次检查节点状态的节点，这里不要配置主节点的ip，否则主节点网络断掉或者机器断电就无法切换
secondary_check_script=/usr/bin/masterha_secondary_check -s 192.168.19.51 -s 192.168.19.52
#用于故障切换的时候发送邮件提醒
report_script=/data1/mysql_mha/send_mail
[server1]
hostname=192.168.19.50
port=3306
ssh_user=root
candidate_master=1
#check_repl_delay=0
[server2]
hostname=192.168.19.51
port=3306
ssh_user=root
candidate_master=1
#check_repl_delay=0

[server3]
hostname=192.168.19.52
ssh_user=root
port=3306
no_master=1
ignore_fail=1

编辑 master_ip_failover /data1/mysql_mha/master_ip_failover

vim /data1/mysql_mha/master_ip_failover

#!/usr/bin/env perl

use strict;
use warnings FATAL => 'all';

use Getopt::Long;

my (
    $command, $orig_master_host, $orig_master_ip,$ssh_user,
    $orig_master_port, $new_master_host, $new_master_ip,$new_master_port,
    $orig_master_ssh_port,$new_master_ssh_port,$new_master_user,$new_master_password
);

# 这里定义的虚拟IP配置要注意，这个ip必须要与你自己的集群在同一个网段，否则无效
my $vip = '192.168.19.54/24';
my $key = '1';
# 这里的网卡名称 “ens33” 需要根据你机器的网卡名称进行修改
# 如果多台机器直接的网卡名称不统一，有两种方式，一个是改脚本，二是把网卡名称修改成统一
# 我这边实际情况是修改成统一的网卡名称
my $ssh_start_vip = "sudo /sbin/ifconfig ens33:$key $vip";
my $ssh_stop_vip = "sudo /sbin/ifconfig ens33:$key down";
my $ssh_Bcast_arp= "sudo /sbin/arping -I bond0 -c 3 -A $vip";

GetOptions(
    'command=s'          => \$command,
    'ssh_user=s'         => \$ssh_user,
    'orig_master_host=s' => \$orig_master_host,
    'orig_master_ip=s'   => \$orig_master_ip,
    'orig_master_port=i' => \$orig_master_port,
    'orig_master_ssh_port=i' => \$orig_master_ssh_port,
    'new_master_host=s'  => \$new_master_host,
    'new_master_ip=s'    => \$new_master_ip,
    'new_master_port=i'  => \$new_master_port,
    'new_master_ssh_port' => \$new_master_ssh_port,
    'new_master_user' => \$new_master_user,
    'new_master_password' => \$new_master_password

);

exit &main();

sub main {
    $ssh_user = defined $ssh_user ? $ssh_user : 'root';
    print "\n\nIN SCRIPT TEST====$ssh_user|$ssh_stop_vip==$ssh_user|$ssh_start_vip===\n\n";

    if ( $command eq "stop" || $command eq "stopssh" ) {

        my $exit_code = 1;
        eval {
            print "Disabling the VIP on old master: $orig_master_host \n";
            &stop_vip();
            $exit_code = 0;
        };
        if ($@) {
            warn "Got Error: $@\n";
            exit $exit_code;
        }
        exit $exit_code;
    }
    elsif ( $command eq "start" ) {

        my $exit_code = 10;
        eval {
            print "Enabling the VIP - $vip on the new master - $new_master_host \n";
            &start_vip();
        &start_arp();
            $exit_code = 0;
        };
        if ($@) {
            warn $@;
            exit $exit_code;
        }
        exit $exit_code;
    }
    elsif ( $command eq "status" ) {
        print "Checking the Status of the script.. OK \n";
        exit 0;
    }
    else {
        &usage();
        exit 1;
    }
}

sub start_vip() {
    `ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}
sub stop_vip() {
    `ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
}

sub start_arp() {
    `ssh $ssh_user\@$new_master_host \" $ssh_Bcast_arp \"`;
}
sub usage {
    print
    "Usage: master_ip_failover --command=start|stop|stopssh|status --ssh_user=user --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";
}

增加可执行权限

chmod a+x /data1/mysql_mha/master_ip_failover

在其他节点创建

mkdir /data1/mysql_mha

修改MySQL从节点配置文件

# 停止MySQL服务
service mysql stop

# 修改配置
vim /etc/my.cnf

# 增加下面配置
# # 启用二进制日志
log-bin=mysql-bin
# # 设置不要复制的数据库(可设置多个)
binlog-ignore-db=sys
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
# 启动MySQL服务
service mysql start

检测

masterha_check_ssh --conf=/etc/mha/mysql_mha.cnf
masterha_check_repl --conf=/etc/mha/mysql_mha.cnf

检测没有报错，启动

nohup masterha_manager --conf=/etc/mha/mysql_mha.cnf &> /data1/mysql_mha/manager.log &
#停止
masterha_stop --conf=/etc/mha/mysql_mha.cnf
#查看master状态
masterha_check_status --conf=/etc/mha/mysql_mha.cnf

# 主节点配置
ip addr add  192.168.19.54/24 dev ens33

# 如果不生效，执行下面的
ifconfig ens33:1 192.168.19.54/24

1.3.2 问题

虚拟ip不生效

使用命令ifconfig ens33:1 192.168.19.54/24，但是ping不通

# 查看网络,有下面框中的就算成功
ip addr

mysqlbinlog没有这个文件

Mon Sep  4 16:45:06 2023 - [info]   Connecting to root@192.168.19.51(192.168.19.51:22).. 
Can't exec "mysqlbinlog": 没有那个文件或目录 at /usr/share/perl5/vendor_perl/MHA/BinlogManager.pm line 106.
mysqlbinlog version command failed with rc 1:0, please verify PATH, LD_LIBRARY_PATH, and client options
 at /usr/bin/apply_diff_relay_logs line 532.
Mon Sep  4 16:45:09 2023 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln208] Slaves settings check failed!
Mon Sep  4 16:45:09 2023 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln416] Slave configuration failed.
Mon Sep  4 16:45:09 2023 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations.  at /usr/bin/masterha_check_repl line 48.
Mon Sep  4 16:45:09 2023 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.
Mon Sep  4 16:45:09 2023 - [info] Got exit code 1 (Not master dead).

MySQL Replication Health is NOT OK!

解决方案

ln -s /usr/local/mysql-8.0.20/bin/mysqlbinlog /usr/local/bin/mysqlbinlog

二、MySQL主从复制

MySQL插件下载：MySQL :: MySQL 8.2 Reference Manual :: 17.4.10.1 Installing Semisynchronous Replication

MySQL半主从复制官网：MySQL :: MySQL 8.2 Reference Manual :: 17.4.10.1 Installing Semisynchronous Replication

2.1. 主从复制解释

将主数据库的增删改查等操作记录到二进制日志文件中，从库接收主库日志文件，根据最后一次更新的起始位置，同步复制到从数据库中，使得主从数据库保持一致。

2.2. 主从复制的作用

• 高可用性：主数据库异常可切换到从数据库
• 负载均衡：实现读写分离
• 备份：进行日常备份

2.3. Mysql主从复制过程

Binary log：主数据库的二进制日志；Relay log：从服务器的中继日志。

复制过程：
（1）主数据库在每次事务完成前，将该操作记录到binlog日志文件中；
（2）从数据库中有一个I/O线程，负责连接主数据库服务，并读取binlog日志变化，如果发现有新的变动，则将变动写入到relay-log，否则进入休眠状态；
（3）从数据库中的SQL Thread读取中继日志，并串行执行SQL事件，使得从数据库与主数据库始终保持一致。

注意事项：
（1）涉及时间函数时，会出现数据不一致。原因是，复制过程的两次IO操作和网络、磁盘效率等问题势必导致时间戳不一致；
（2）涉及系统函数时，会出现不一致。如：@@hostname，获取主机名称，主从数据库服务器名称不一致导致数据不一致；
（3）......

2.4. 一主一从配置

• 服务器划分

服务器IP	角色
192.168.19.50	Master1
192.168.19.51	Slave1

• 主数据库安装

# 进入目录
cd /opt

# 下载安装包
wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz

# 解压
tar -xvf mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz

# 拷贝到/usr/local
mv /opt/mysql-8.0.20-linux-glibc2.12-x86_64 /usr/local

# 进入/usr/local
cd /usr/local

# 修改名称为mysql-8.0.20
mv mysql-8.0.20-linux-glibc2.12-x86_64 mysql-8.0.20

# 创建存放数据文件夹
mkdir /usr/local/mysql-8.0.20/data

# 创建用户及用户组
groupadd mysql
useradd -g mysql mysql

# 授权
chown -R mysql.mysql /usr/local/mysql-8.0.20

# 初始化数据库(记录临时密码)
cd /usr/local/mysql-8.0.20/

./bin/mysqld --user=mysql --lower-case-table-names=1 --basedir=/usr/local/mysql-8.0.20/ --datadir=/usr/local/mysql-8.0.20/data/ --initialize ;

# 配置my.cnf
vi /etc/my.cnf

# 清空，使用下面内容
// 文件内容开始

[mysqld]
basedir=/usr/local/mysql-8.0.20
datadir=/usr/local/mysql-8.0.20/data
character-set-server=utf8
lower-case-table-names=1
default_authentication_plugin=mysql_native_password

# 主从复制-主机配置
# 主服务器唯一ID
server-id=1
# 启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=sys
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
# 设置需要复制的数据库(可设置多个)
# binlog-do-db=test
# 设置logbin格式
binlog_format=STATEMENT
default-time_zone = '+8:00'
// 文件内容结束

# 建立Mysql服务
cp -a ./support-files/mysql.server /etc/init.d/mysql
chmod +x /etc/init.d/mysql
chkconfig --add mysql

# 检查服务是否生效
chkconfig --list mysql

# 启动、停止、重启
service mysql start
service mysql stop
service mysql restart

# 创建软连接
ln -s /usr/local/mysql-8.0.20/bin/mysql /usr/bin 
ln -s /usr/local/mysql-8.0.20/bin/mysqlbinlog /usr/local/bin/mysqlbinlog
# 登录（使用临时密码）
mysql -uroot -p

# 修改密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

# 退出，使用新密码登录
quit
mysql -uroot -p

# 修改root权限，增加远程连接
use mysql
update user set host ='%' where user='root';
alter user 'root'@'%' identified with mysql_native_password by '123456';
flush privileges;

# 退出
quit

• 从数据库安装
  和主数据库安装一致，但配置文件内容不同。

# 配置my.cnf
vi /etc/my.cnf

# 清空，使用下面内容
// 文件内容开始

[mysqld]
basedir=/usr/local/mysql-8.0.20
datadir=/usr/local/mysql-8.0.20/data
character-set-server=utf8
lower-case-table-names=1
default_authentication_plugin=mysql_native_password

# 主从复制-从机配置
# 从服务器唯一ID
server-id=2
# 启用中继日志
relay-log=mysql-relay

// 文件内容结束

# 建立Mysql服务
cp -a ./support-files/mysql.server /etc/init.d/mysql
chmod +x /etc/init.d/mysql
chkconfig --add mysql

# 启动MySQL
service mysql start

• 关闭主从数据库服务器防火墙或开放3306端口

# 查看防火墙状态
systemctl status firewalld

# 关闭防火墙
systemctl stop firewalld

• 主从数据库测试是否已经可以远程访问

# 主数据库服务器测试从数据库
mysql -uroot -p -h192.168.133.130 -P3306

# 从数据库服务器测试主数据库
mysql -uroot -p -h192.168.133.129 -P3306

• 主数据库创建用户slave并授权

# 登录
mysql -uroot -p

# 创建用户
create user 'slave'@'%' identified with mysql_native_password by '123456';

# 授权
grant replication slave on *.* to 'slave'@'%';

# 刷新权限
flush privileges;

• 从数据库验证slave用户是否可用

mysql -uslave -p -h192.168.19.50 -P3306

• 主数据库查询服务ID及Master状态

# 登录
mysql -uroot -p

# 查询server_id是否可配置文件中一致
show variables like 'server_id';

# 若不一致，可设置临时ID（重启失效）
set global server_id = 1;

# 查询Master状态，并记录 File 和 Position 的值
show master status;

# 注意：执行完此步骤后退出主数据库，防止再次操作导致 File 和 Position 的值发生变化

• 从数据库中设置主数据库

# 登录
mysql -uroot -p

# 查询server_id是否可配置文件中一致
show variables like 'server_id';

# 若不一致，可设置临时ID（重启失效）
set global server_id = 2;

# 设置主数据库参数
change master to master_host='192.168.19.50',master_port=3306,master_user='slave',master_password='password',master_log_file='mysql-bin.000002',master_log_pos=156;

# 开始同步
start slave;

# 若出现错误，则停止同步，重置后再次启动
stop slave;
reset slave;
start slave;

# 查询Slave状态
show slave status\G

# 查看是否配置成功
# 查看参数 Slave_IO_Running 和 Slave_SQL_Running 是否都为yes，则证明配置成功。若为no，则需要查看对应的 Last_IO_Error 或 Last_SQL_Error 的异常值。

• 测试
  通过工具连接主从数据库或者在服务器连接。
  注意：主数据库的配置文件中配置了需要同步的数据库，因此只会同步配置的数据库，不配置则同步全部。

Copy# 在主数据库创建数据库test
create database test;

# 从数据库查看
show databases;

# 在主数据库创建表
use test;
create table t_user(id int, name varchar(20));

# 插入数据
insert into t_user values(1, 'C3Stones');

# 在从数据库查看
use test;
select * from t_user;

# 其他删改查操作请自行测试

2.5. 双主双从配置

双主双从即两台主机分别存在两台从机，每台从机只复制对应的主机，两台主机互为主备。

• 服务器划分

服务器IP	角色
192.168.19.50	Master1
192.168.19.51	Slave1
192.168.19.52	Master2
192.168.19.53	Slave2

• 安装数据库请参考上述安装主数据库
  四个配置文件替换如下：
  （1）Mater1

[mysqld]
basedir=/usr/local/mysql-8.0.20
datadir=/usr/local/mysql-8.0.20/data
character-set-server=utf8
lower-case-table-names=1
default_authentication_plugin=mysql_native_password

# 主从复制-主机1配置
# 主服务器唯一ID
server-id=1
# 启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=sys
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
# 设置需要复制的数据库(可设置多个)
binlog-do-db=test
# 设置logbin格式
binlog_format=STATEMENT
# 写入操作更新二进制日志文件
log-slave-updates
# 自增长字段起始值，默认值为1，取值范围：1 ~ 65535
auto-increment-increment=2
# 自增长字段递增量，取值范围：1 ~ 65535
auto-increment-offset=1

（2）Mater2

Copy[mysqld]
basedir=/usr/local/mysql-8.0.20
datadir=/usr/local/mysql-8.0.20/data
character-set-server=utf8
lower-case-table-names=1
default_authentication_plugin=mysql_native_password

# 主从复制-主机2配置
# 主服务器唯一ID
server-id=3
# 启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=sys
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
# 设置需要复制的数据库(可设置多个)
binlog-do-db=test
# 设置logbin格式
binlog_format=STATEMENT
# 写入操作更新二进制日志文件
log-slave-updates
# 自增长字段起始值，默认值为1，取值范围：1 ~ 65535
auto-increment-increment=2
# 自增长字段递增量，取值范围：1 ~ 65535
auto-increment-offset=2

（3）Slave1

Copy[mysqld]
basedir=/usr/local/mysql-8.0.20
datadir=/usr/local/mysql-8.0.20/data
character-set-server=utf8
lower-case-table-names=1
default_authentication_plugin=mysql_native_password

# 主从复制-从机1配置
# 从服务器唯一ID
server-id=2
# 启用中继日志
relay-log=mysql-relay

（4）Slave2

Copy[mysqld]
basedir=/usr/local/mysql-8.0.20
datadir=/usr/local/mysql-8.0.20/data
character-set-server=utf8
lower-case-table-names=1
default_authentication_plugin=mysql_native_password

# 主从复制-从机2配置
# 从服务器唯一ID
server-id=4
# 启用中继日志
relay-log=mysql-relay

• 双主双从数据库均重启

service  mysql restart

• 四台服务器均关闭防火墙

systemctl stop firewalld

• 两台主数据库分别创建用户slave并授权

# 登录
mysql -uroot -p

# 创建用户
create user 'slave'@'%' identified with mysql_native_password by 'password';

# 授权
grant replication slave on *.* to 'slave'@'%';

# 刷新权限
flush privileges;

• 主从数据库验证slave用户是否可用

# 主数据库1服务器测试
mysql -uslave -p -h192.168.19.50 -P3306
mysql -uslave -p -h192.168.19.52 -P3306

# 从数据库1服务器测试主数据库1
mysql -uroot -p -h192.168.19.50 -P3306

# 主数据库2服务器测试
mysql -uslave -p -h192.168.19.50 -P3306
mysql -uslave -p -h192.168.19.52 -P3306

# 从数据库1服务器测试主数据库1
mysql -uroot -p -h192.168.19.52 -P3306

• 两台主数据库查询服务ID及Master状态

Copy# 登录
mysql -uroot -p

# 查询server_id是否可配置文件中一致
show variables like 'server_id';

# 若不一致，可设置临时ID（重启失效）
# 主数据库1
set global server_id = 1;
# 主数据库2
set global server_id = 3;

# 查询Master状态，并记录 File 和 Position 的值
show master status;

# 注意：执行完此步骤后退出主数据库，防止再次操作导致 File 和 Position 的值发生变化

• 从数据库1中设置主数据库1

Copy# 登录
mysql -uroot -p

# 查询server_id是否可配置文件中一致
show variables like 'server_id';

# 若不一致，可设置临时ID（重启失效）
set global server_id = 2;

# 设置主数据库参数
change master to master_host='192.168.133.129',master_port=3306,master_user='slave',master_password='password',master_log_file='mysql-bin.000003',master_log_pos=156;

# 开始同步
start slave;

# 若出现错误，则停止同步，重置后再次启动
stop slave;
reset slave;
start slave;

# 查询Slave状态
show slave status\G

# 查看是否配置成功
# 查看参数 Slave_IO_Running 和 Slave_SQL_Running 是否都为yes，则证明配置成功。若为no，则需要查看对应的 Last_IO_Error 或 Last_SQL_Error 的异常值。

• 从数据库2中设置主数据库2

Copy# 登录
mysql -uroot -p

# 查询server_id是否可配置文件中一致
show variables like 'server_id';

# 若不一致，可设置临时ID（重启失效）
set global server_id = 4;

# 设置主数据参数
change master to master_host='192.168.133.131',master_port=3306,master_user='slave',master_password='password',master_log_file='mysql-bin.000001',master_log_pos=156;

# 开始同步
start slave;

# 若出现错误，则停止同步，重置后再次启动
stop slave;
reset slave;
start slave;

# 查询Slave状态
show slave status\G

# 查看是否配置成功
# 查看参数 Slave_IO_Running 和 Slave_SQL_Running 是否都为yes，则证明配置成功。若为no，则需要查看对应的 Last_IO_Error 或 Last_SQL_Error 的异常值。

• 主数据库1中设置主数据库2

Copy# 登录
mysql -uroot -p

# 设置主数据库参数
change master to master_host='192.168.133.131',master_port=3306,master_user='slave',master_password='password',master_log_file='mysql-bin.000001',master_log_pos=156;

# 开始同步
start slave;

# 若出现错误，则停止同步，重置后再次启动
stop slave;
reset slave;
start slave;

# 查询Slave状态
show slave status\G

# 查看是否配置成功
# 查看参数 Slave_IO_Running 和 Slave_SQL_Running 是否都为yes，则证明配置成功。若为no，则需要查看对应的 Last_IO_Error 或 Last_SQL_Error 的异常值。

• 主数据库2中设置主数据库1

Copy# 登录
mysql -uroot -p

# 设置主数据库参数
change master to master_host='192.168.133.129',master_port=3306,master_user='slave',master_password='password',master_log_file='mysql-bin.000003',master_log_pos=156;

# 开始同步
start slave;

# 若出现错误，则停止同步，重置后再次启动
stop slave;
reset slave;
start slave;

# 查询Slave状态
show slave status\G

# 查看是否配置成功
# 查看参数 Slave_IO_Running 和 Slave_SQL_Running 是否都为yes，则证明配置成功。若为no，则需要查看对应的 Last_IO_Error 或 Last_SQL_Error 的异常值。

• 测试
  通过工具连接双主双从数据库或者在服务器连接。
  注意：主数据库的配置文件中配置了需要同步的数据库，因此只会同步配置的数据库，不配置则同步全部。

Copy# 在主数据库1创建数据库test
create database test;

# 其他三个数据库查看
show databases;

# 在主数据库1创建表
use test;
create table t_user(id int, name varchar(20));

# 插入数据
insert into t_user values(1, 'C3Stones');

# 其他三个数据库查看
use test;
select * from t_user;

# 其他删改查操作请自行测试

2.6 卸载mysql

# 停止服务
service mysqld stop
# 删除文件
find / -name mysql -exec rm -r {} \;
# 删除用户
id mysql
userdel mysql

三、MySQL半同步复制

3.1 主库

mysql> select @@have_dynamic_loading; 
+------------------------+
| @@have_dynamic_loading |
+------------------------+
| YES                    |
+------------------------+
1 row in set (0.00 sec)
show plugins;  # 查看是否有rpl_semi_sync_master插件

install plugin rpl_semi_sync_master soname 'semisync_master.so';   # 安装rpl_semi_sync_master插件  自定义名字为semisync_master.so

uninstall plugin rpl_semi_sync_master   # 卸载插件 

查看安装信息

mysql> show variables like '%semi%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 3000       |
| 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.01 sec)

配置变量

set global rpl_semi_sync_master_enabled=1;
set global rpl_semi_sync_master_timeout=3000;

或者

vim /etc/mysql/my.cnf  （docker）
vim /etc/my.cnf

rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=3000


重启mysql
service mysql restart;

3.2 从库

IO线程关闭，如果复制 I/O（接收方）线程已在运行，并且 您不重新启动它，副本继续使用异步 复制。

STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

Or from MySQL 8.0.22:
STOP REPLICA IO_THREAD;
START REPLICA IO_THREAD;

# 查看动态加载
mysql> select @@have_dynamic_loading; 
+------------------------+
| @@have_dynamic_loading |
+------------------------+
| YES                    |
+------------------------+
1 row in set (0.00 sec)

安装插件

show plugins;  # 查看是否有rpl_semi_sync_slave插件
# 安装rpl_semi_sync_slave插件  自定义名字为semisync_master.so
install plugin rpl_semi_sync_slave soname 'semisync_slave.so'; 
# 卸载插件
uninstall plugin rpl_semi_sync_slave;

查看安装结果

mysql> show variables like '%semi%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | OFF        |
| rpl_semi_sync_master_timeout              | 10000      |
| 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 |
| rpl_semi_sync_slave_enabled               | ON         |
| rpl_semi_sync_slave_trace_level           | 32         |
+-------------------------------------------+------------+
8 rows in set (0.00 sec)

配置变量

set global rpl_semi_sync_slave_enabled=1;
或
vim /etc/mysql/my.cnf  (docker)
vim /etc/my.cnf
rpl_semi_sync_slave_enabled=1


# 重启mysql
service mysql restart;

3.3 测试

从库停止同步

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

主库向数据库中修改一条数据，3秒才能成功

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> UPDATE base_station_info SET station = '东四111' WHERE stationCode = '1003A';
Query OK, 0 rows affected (3.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0

从库开启同步

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

主库修改数据，快速成功并同步

mysql> UPDATE base_station_info SET station = '东四aaa' WHERE stationCode = '1003A';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM base_station_info WHERE stationCode = '1003A';
+---------+-----------+-----------+--------+----------+------------+-----------+-------------+----------+---------+-------+----------------------------------------------------------------+-------+
| stateCd | stateName | cityName  | citycd | countyCd | countyName | station   | stationCode | lng      | lat     | level | addr                                                           | state |
+---------+-----------+-----------+--------+----------+------------+-----------+-------------+----------+---------+-------+----------------------------------------------------------------+-------+
| 110000  | 北京市    | 北京市    | 110100 | 110101   | 东城区     | 东四aaa   | 1003A       | 116.4174 | 39.9289 | 0     | 东四北大街337-1号(东四地铁站B东北口步行490米)                  | 1     |
+---------+-----------+-----------+--------+----------+------------+-----------+-------------+----------+---------+-------+----------------------------------------------------------------+-------+
1 row in set (0.00 sec)

四、组复制（MGR）

4.1组复制背景

传统的 MySQL主从复制提供了一种简单的master到slave复制的方法。这里master是主要的，并且有一个或多个slave，slave是次要的。master事务提交，然后它们稍后发送到slave重新执行。

MySQL异步复制

MySQL半同步复制

组复制是一种可用于实现容错系统的技术。复制组是一组服务器，每个服务器都有自己的数据完整副本（无共享复制方案），并通过消息传递相互交互。通信层提供了一组保证，如原子消息和总订单消息传递。这些是非常强大的属性，可以转化为非常有用的抽象，用于构建更高级的数据库复制解决方案。

4.2 组复制搭建

组复制使您能够通过将系统状态复制到一组服务器来创建具有冗余的容错系统。即使某些服务器随后出现故障，只要不是全部或多数，系统仍然可用。根据出现故障的服务器数量，该组可能会降低性能或可扩展性，但它仍然可用。服务器故障是隔离和独立的。它们由组成员服务跟踪，该服务依赖于分布式故障检测器，当任何服务器自愿或由于意外停止离开组时，该检测器能够发出信号。有一个分布式恢复过程，以确保当服务器加入组时，它们会自动更新。不需要服务器故障转移，并且无处不在的多源更新特性确保即使在单个服务器故障的情况下，更新也不会被阻止。总而言之，MySQL组复制保证数据库服务持续可用。

组复制在单主模式或多主模式。组的模式是组范围的配置 设置，由指定 group_replication_single_primary_mode 系统变量，它必须在所有成员上相同。 ON表示单主模式，即 默认模式，OFF表示多主模式。 不可能将小组成员部署在不同的模式，例如配置一个成员 多主模式，而另一个成员处于单主模式。

不能更改的值 group_replication_single_primary_mode 运行组复制时手动。从MySQL8.0.13， 您可以使用 group_replication_switch_to_single_primary_mode() 和 group_replication_switch_to_multi_primary_mode() 函数将组从一种模式移动到另一种模式，而Group 复制仍在运行。这些函数管理进程 改变团队模式，确保安全和一致性 您的数据。在早期版本中，要更改组模式，您 必须停止组复制并更改的值 group_replication_single_primary_mode 对所有成员。然后对组进行完全重启（a 由服务器引导 group_replication_bootstrap_group=ON） 实现对新操作配置的更改。不需要重新启动服务器。

4.2.1 环境准备

需要将MySQL搭建起来，这里就不详细讲MySQL搭建了

服务器IP	镜像版本	MySQL版本
192.168.19.50	CentOS Linux release 7.9.2009 (Core)	mysql-8.0.20
192.168.19.51	CentOS Linux release 7.9.2009 (Core)	mysql-8.0.20
192.168.19.52	CentOS Linux release 7.9.2009 (Core)	mysql-8.0.20

192.168.19.50 s1
192.168.19.51 s2
192.168.19.52 s3

/etc/init.d/network restart

4.2.2 单组模式搭建

在单主模式下 （group_replication_single_primary_mode=ON） 该组有一个设置为读写的主服务器 模式。组中的所有其他成员都设置为只读 模式（super_read_only=ON）。 主服务器通常是第一个引导 组。加入组的所有其他服务器都了解 主服务器并自动设置为只读模式。

4.2.2.1 引导组搭建

vim /etc/my.cnf

# 追加以下内容
# 禁用不支持的存储引擎
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
# 每台机器保证唯一
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
plugin_load_add='group_replication.so'
# 可以使用select uuid();函数获取
group_replication_group_name="5c113ab1-4654-11ee-9956-000c29c74a99"
group_replication_start_on_boot=off
# 本机ip
group_replication_local_address= "s1:33061"
# 集群ip
group_replication_group_seeds= "s1:33061,s2:33061,s3:33061"
group_replication_bootstrap_group=off

log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64

分布式恢复用户凭证

service mysql start
mysql -uroot -p

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
GRANT CONNECTION_ADMIN ON *.* TO rpl_user@'%';
GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';
GRANT GROUP_REPLICATION_ADMIN ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery';

# 安装插件
INSTALL PLUGIN group_replication SONAME 'group_replication.so';

检查插件是否安装成功，请发出 SHOW PLUGINS;并检查输出。它应该 显示这样的东西：

mysql> SHOW PLUGINS;
+---------------------------------+----------+--------------------+----------------------+---------+
| Name                            | Status   | Type               | Library              | License |
+---------------------------------+----------+--------------------+----------------------+---------+
| group_replication               | ACTIVE   | GROUP REPLICATION  | group_replication.so | GPL     |
+---------------------------------+----------+--------------------+----------------------+---------+

# 重启MySQL
service mysql restart
# 登录
mysql -uroot -p

# 引导
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

# 检查是否成功
SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST           | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+-------------+----------------+
| group_replication_applier | c13141b0-464f-11ee-870b-000c29c88748 | localhost.localdomain |        3306 | ONLINE       | PRIMARY     | 8.0.20         |
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+-------------+----------------+
1 row in set (0.00 sec)

4.2.2.2 添加第二个实例

为了添加第二个实例2，首先创建 它的配置文件。配置类似于 一个用于服务器，除了诸如 server_id.这些不同的 下面的列表中突出显示了行。

[mysqld]

#
# Disable other storage engines
#
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"

#
# Replication configuration parameters
#
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE           # Not needed from 8.0.21
log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64
#
# Group Replication configuration
#
plugin_load_add='group_replication.so'
group_replication_group_name="5c113ab1-4654-11ee-9956-000c29c74a99"
group_replication_start_on_boot=off
group_replication_local_address= "s2:33061"
group_replication_group_seeds= "s1:33061,s2:33061,s3:33061"
group_replication_bootstrap_group= off

# 启动MySQL
service mysql start
# 登录
mysql -uroot -p

# 创建复制用户
SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
GRANT CONNECTION_ADMIN ON *.* TO rpl_user@'%';
GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';
GRANT GROUP_REPLICATION_ADMIN ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery';

# 启动组复制
START GROUP_REPLICATION;

# 检查配置结果
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST           | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+-------------+----------------+
| group_replication_applier | c13141b0-464f-11ee-870b-000c29c88748 | localhost.localdomain |        3306 | ONLINE       | PRIMARY     | 8.0.20         |
| group_replication_applier | c14bf4fc-464f-11ee-87ff-000c2921c2de | localhost.localdomain |        3306 | RECOVERING   | SECONDARY   | 8.0.20         |
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+-------------+----------------+

4.2.2.3 测试

停止主节点

service mysql stop

可以看到主节点已经切换到了s3

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | c13d0207-464f-11ee-88ee-000c29c74a99 | s3          |        3306 | ONLINE       | PRIMARY     | 8.0.20         |
| group_replication_applier | c14bf4fc-464f-11ee-87ff-000c2921c2de | s2          |        3306 | ONLINE       | SECONDARY   | 8.0.20         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
2 rows in set (0.00 sec)

原先节点重启之后不会自动加入

4.2.2.4 配置文件

s1

[mysqld]
basedir=/usr/local/mysql-8.0.20
datadir=/usr/local/mysql-8.0.20/data
character-set-server=utf8
lower-case-table-names=1
default_authentication_plugin=mysql_native_password

# 主从复制-主机配置
# 主服务器唯一ID
server-id=1
# 启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=sys
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
# 设置需要复制的数据库(可设置多个)
# binlog-do-db=test
# 设置logbin格式
binlog_format=STATEMENT
default-time_zone = '+8:00'
# 追加以下内容
# 禁用不支持的存储引擎
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
# 每台机器保证唯一
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
plugin_load_add='group_replication.so'
# 可以使用select uuid();函数获取
group_replication_group_name="5c113ab1-4654-11ee-9956-000c29c74a99"
group_replication_start_on_boot=off
# 本机ip
group_replication_local_address= "s1:33061"
# 集群ip
group_replication_group_seeds= "s1:33061,s2:33061,s3:33061"
group_replication_bootstrap_group=off

log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64

s2

[mysqld]
basedir=/usr/local/mysql-8.0.20
datadir=/usr/local/mysql-8.0.20/data
character-set-server=utf8
lower-case-table-names=1
default_authentication_plugin=mysql_native_password

# 主从复制-从机配置
# 从服务器唯一ID
server-id=2
# 启用中继日志
relay-log=mysql-relay
#
# Disable other storage engines
#
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"

#
# Replication configuration parameters
#
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE           # Not needed from 8.0.21
log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64
#
# Group Replication configuration
#
plugin_load_add='group_replication.so'
group_replication_group_name="5c113ab1-4654-11ee-9956-000c29c74a99"
group_replication_start_on_boot=off
group_replication_local_address= "s2:33061"
group_replication_group_seeds= "s1:33061,s2:33061,s3:33061"
group_replication_bootstrap_group= off

s3

[mysqld]
basedir=/usr/local/mysql-8.0.20
datadir=/usr/local/mysql-8.0.20/data
character-set-server=utf8
lower-case-table-names=1
default_authentication_plugin=mysql_native_password

# 主从复制-从机配置
# 从服务器唯一ID
server-id=3
# 启用中继日志
relay-log=mysql-relay
#
# Disable other storage engines
#
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"

#
# Replication configuration parameters
#
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE           # Not needed from 8.0.21
log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64
#
# Group Replication configuration
#
plugin_load_add='group_replication.so'
group_replication_group_name="5c113ab1-4654-11ee-9956-000c29c74a99"
group_replication_start_on_boot=off
group_replication_local_address= "s3:33061"
group_replication_group_seeds= "s1:33061,s2:33061,s3:33061"
group_replication_bootstrap_group= off

4.2.3 模式切换

4.2.3.1 单主模式切换多组模式

在主节点，使用group_replication_switch_to_multi_primary_mode() 将在单主模式下运行的组更改为 多主模式：

SELECT group_replication_switch_to_multi_primary_mode();

4.2.3.2 多主模式切换单主

随意一个节点，使用 group_replication_switch_to_single_primary_mode() 将以多主模式运行的组更改为 单主模式通过：

SELECT group_replication_switch_to_single_primary_mode();

# 指定成员为主节点
SELECT group_replication_switch_to_single_primary_mode(member_uuid);