系列文章目录
提示：这里可以添加系列文章的所有文章的目录，目录需要自己手动添加
例如：第一章 Python 机器学习入门之pandas的使用
提示：写完文章后，目录可以自动生成，如何生成可参考右边的帮助文档
文章目录
MySQL高可用集群
环境架构：
|主机名称| IP | 角色 | 权限|
|Mysql00| 192.168.118.70 |Master | 读写 |
| Mysql01 | 192.168.118.71 |Slave1 |只读 |
|Mysql02 | 192.168.118.72| Slave2 | 只读 |
|Mysql_MHA | 192.168.118.73 | MHA Manager | 只读 |
关闭防火墙和SELINUX
systemctl stop firewalld
systemctl disable firewalld
sed -ri 's/^(SELINUX=).*/\1disabled/g' /etc/selinux/config
setenforce 0
在Mysql00的/etc/hosts中添加主机名
cat > /etc/hostname << EOF
Mysql_MHA
EOF
cat >> /etc/hosts << EOF
192.168.118.70 Mysql00
192.168.118.71 Mysql01
192.168.118.72 Mysql02
192.168.118.73 Mysql_MHA
EOF
ssh免秘钥登录
[root@hadoop00 ~]## ssh-keygen -t rsa
//复制到其他节点
# for i in { 01 02 _MHA }; do ssh-copy-id root@Mysql$i;done
Mysql主从搭建
安装MySQL5.7采用linux源包
下载安装源包
# wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
# yum install -y mysql57-community-release-el7-10.noarch.rpm
# rpm -qa | grep mysql
mysql57-community-release-el7-10.noarch
zabbix-web-mysql-scl-5.0.23-1.el7.noarch
zabbix-server-mysql-5.0.23-1.el7.x86_64
rh-php72-php-mysqlnd-7.2.24-1.el7.x86_64
//接下来安装mysql-server
# yum install -y mysql-server
依赖关系解决
==========================================================================================================================================================
Package 架构 版本 源 大小
==========================================================================================================================================================
正在安装:
mysql-community-libs x86_64 5.7.38-1.el7 mysql57-community 2.6 M
替换 mariadb-libs.x86_64 1:5.5.68-1.el7
mysql-community-libs-compat x86_64 5.7.38-1.el7 mysql57-community 1.2 M
替换 mariadb-libs.x86_64 1:5.5.68-1.el7
mysql-community-server x86_64 5.7.38-1.el7 mysql57-community 178 M
为依赖而安装:
mysql-community-client x86_64 5.7.38-1.el7 mysql57-community 28 M
mysql-community-common x86_64 5.7.38-1.el7 mysql57-community 311 k
事务概要
==========================================================================================================================================================
安装 3 软件包 (+2 依赖软件包)
总计：209 M
Downloading packages:
警告：/var/cache/yum/x86_64/7/mysql57-community/packages/mysql-community-libs-compat-5.7.38-1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 3a79bd29: NOKEY
从 file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql 检索密钥
源 "MySQL 5.7 Community Server" 的 GPG 密钥已安装，但是不适用于此软件包。请检查源的公钥 URL 是否配置正确。
失败的软件包是：mysql-community-libs-compat-5.7.38-1.el7.x86_64
GPG 密钥配置为：file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
到官网搜索GPG
查到密钥相关信息
https://dev.mysql.com/doc/refman/5.7/en/checking-gpg-signature.html
复制创建密钥文件,再导入个人公共 GPG 密钥环
# vim mysql_pubkey.asc
# gpg --import mysql_pubkey.asc
gpg: /root/.gnupg/trustdb.gpg：建立了信任度数据库
gpg: 密钥 3A79BD29：公钥“MySQL Release Engineering <mysql-build@oss.oracle.com>”已导入
gpg: 合计被处理的数量：1
gpg: 已导入：1 (RSA: 1)
要将密钥导入 RPM 配置以验证 RPM 安装包，您应该能够直接导入密钥：
#rpm --import mysql_pubkey.asc
将密钥文件复制到 01 02 。其他相同
# for i in { 01 02 }; do scp mysql_pubkey.asc root@Mysql$i:/root/ ;done
又过期20230627
警告：/var/cache/yum/x86_64/7/mysql57-community/packages/mysql-community-common-5.7.42-1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 3a79bd29: NOKEY
从 file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql 检索密钥
源 "MySQL 5.7 Community Server" 的 GPG 密钥已安装，但是不适用于此软件包。请检查源的公钥 URL 是否配置正确。
失败的软件包是：mysql-community-common-5.7.42-1.el7.x86_64
GPG 密钥配置为：file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
直接导入网站上的
#rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
再次安装
# yum install -y mysql-server
已安装:
mysql-community-libs.x86_64 0:5.7.38-1.el7 mysql-community-libs-compat.x86_64 0:5.7.38-1.el7 mysql-community-server.x86_64 0:5.7.38-1.el7
作为依赖被安装:
mysql-community-client.x86_64 0:5.7.38-1.el7 mysql-community-common.x86_64 0:5.7.38-1.el7
替代:
mariadb-libs.x86_64 1:5.5.68-1.el7
完毕！
顺利完成
启动服务
# systemctl start mysqld.service
# systemctl enable mysqld.service
# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since 日 2022-05-29 13:43:02 CST; 11s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 14526 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 14471 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 14529 (mysqld)
Tasks: 27
Memory: 318.5M
CGroup: /system.slice/mysqld.service
└─14529 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
查看默认密码并修改：
[root@Mysql00 ~]# grep "password" /var/log/mysqld.log
2022-06-24T09:52:34.620054Z 1 [Note] A temporary password is generated for root@localhost: WJ+yVida/6am
[root@Mysql00 ~]# mysql -uroot -p
Enter password: //输入默认密码
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.38
.....
//设置新密码
mysql> set password = Password('Admin@123');
Query OK, 0 rows affected, 1 warning (0.03 sec)
允许远程登录quit
mysql> grant all privileges on *.* to 'root'@'%' identified by 'Admin@123' with grant option;
mysql> flush privileges;
# netstat -tlnp | grep mysql
tcp6 0 0 :::3306 :::* LISTEN 1830/mysqld
修改默认语言
先退出，然后去修改 /etc/my.cnf ,在centos中所有的配置文件几乎都在 etc 目录下
# vim /etc/my.cnf
---增加以下行
[client]
default-character-set=utf8
----
[mysql]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
----下面新增
character-set-server=utf8
collation-server=utf8_unicode_ci
保存退出，再重启服务
service mysqld restart
再进去时就可以看看状态了
Mysql主从配置
Master节点
修改master节点的配置文件
# vim /etc/my.cnf
//在[mysqld] 下新增中如下信息
[mysqld]
# 指定server id
server-id=1
# innodb_buffer_pool_size = 128M
# 开启binlog功能，并指定日志文件名称
log_bin=mysql-bin
# 每次执行写入性操作就与磁盘同步
sync-binlog=1
# 指定哪些库排除掉不进行同步
binlog-ignore-db=performance_schema
binlog-ignore-db=information_schema
binlog-ignore-db=sys
# 日志中记录每一行数据被修改的情况
binlog-format=ROW
relay_log_purge=0
# 指定中继日志
relay_log=mysql-relay-bin
重启服务
systemctl restart mysqld
主库给从库授权
# mysql -uroot -p
mysql> grant replication slave on *.* to root@'%' identified by 'Admin@123';
mysql> grant all privileges on *.* to root@'%' identified by 'Admin@123';
mysql> flush privileges;
Slave1节点
修改Slave1节点配置文件
# vim /etc/my.cnf
//在[mysqld] 下新增中如下信息
[mysqld]
# 指定server id
server-id=2
read_only=1
# 后面要用 master_auto_position=1需要开启
gtid_mode=on
enforce_gtid_consistency=on
# innodb_buffer_pool_size = 128M
# 开启binlog功能，并指定日志文件名称
log_bin=mysql-bin
# 每次执行写入性操作就与磁盘同步
sync-binlog=1
# 指定哪些库排除掉不进行同步
binlog-ignore-db=performance_schema
binlog-ignore-db=information_schema
binlog-ignore-db=sys
# 日志中记录每一行数据被修改的情况
binlog-format=ROW
relay_log_purge=0
# 指定中继日志
relay_log=mysql-relay-bin
重启服务
systemctl restart mysqld
从库开启同步
//第二次补充时查询，主节点先查下
mysql> show master status;
+------------------+----------+--------------+-------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------+-------------------+
| mysql-bin.000001 | 869 | | performance_schema,information_schema,sys | |
+------------------+----------+--------------+-------------------------------------------+-------------------+
1 row in set (0.01 sec)
----
# mysql -uroot -p
Enter password:
mysql> change master to
master_host='192.168.118.70',master_port=3306,master_user='root',master_password
='Admin@123',master_log_file='mysql-bin.000002',master_log_pos=154;
//或者使用
mysql> change master to
master_host='192.168.118.70',master_port=3306,master_user='root',master_password
='Admin@123',master_auto_position=1;
//其中，master_auto_position=1就表示这个主备关系使用的是GTID协议。可以看到，前面让我们头疼不已的MASTER_LOG_FILE和MASTER_LOG_POS参数，已经不需要指定了。
mysql> start slave; // 开启同步
注意： 如果主从复制帐号不想用root，需要主从配置之前就设置账号，先分别建好管理 账号和主从复制账号
#创建主从复制帐号
create user 'gtid'@'192.168.118.%' identified by 'Gtid@123';
grant replication slave on *.* to 'gtid'@'192.168.118.%';
flush privileges;
#创建管理帐号
create user 'manage'@'192.168.118.%' identified by 'Admin@123';
grant all privileges on *.* to 'manage'@'192.168.118.%';
flush privileges;
//主从如已开始，从就不需要另外建用户了
show grants for 'manage'@'192.168.118.%';
Slave2节点
修改Slave2节点配置文件
# vim /etc/my.cnf
//在[mysqld] 下新增中如下信息
[mysqld]
# 指定server id
server-id=3
read_only=1
# 后面要用 master_auto_position=1需要开启
gtid_mode=on
enforce_gtid_consistency=on
# innodb_buffer_pool_size = 128M
# 开启binlog功能，并指定日志文件名称
log_bin=mysql-bin
# 每次执行写入性操作就与磁盘同步
sync-binlog=1
# 指定哪些库排除掉不进行同步
binlog-ignore-db=performance_schema
binlog-ignore-db=information_schema
binlog-ignore-db=sys
# 日志中记录每一行数据被修改的情况
binlog-format=ROW
relay_log_purge=0
# 指定中继日志
relay_log=mysql-relay-bin
其他同Slave1
配置半同步复制
Master节点
安装插件
install plugin rpl_semi_sync_master soname 'semisync_master.so';
show variables like '%semi%';
//修改半同步配置
set global rpl_semi_sync_master_enabled=1;
set global rpl_semi_sync_master_timeout=1000;
flush privileges;
重启服务
systemctl restart mysqld
Slave节点
两台Slave节点都执行以下步骤。
install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
//修改半同步配置
set global rpl_semi_sync_slave_enabled=1;
stop slave;
start slave;
重启服务
systemctl restart mysqld
测试半同步状态
首先通过MySQL命令行检查参数的方式，查看半同步是否开启。
Master节点
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 |
+-------------------------------------------+------------+
6 rows in set (0.04 sec)
Slave节点
mysql> show variables like '%semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.04 sec)
然后通过MySQL日志再次确认。
cat /var/log/mysqld.log
可以看到日志中已经启动半同步
....Start semi-sync binlog_dump to slave (server_id: 2), pos(mysql-bin.000002, 154)
....Start semi-sync binlog_dump to slave (server_id: 3), pos(mysql-bin.000002, 154)
-----采用Git后显示不一样，如下：
2022-10-23T10:21:23.637721Z 3 [Note] Start binlog_dump to master_thread_id(3) slave_server(2), pos(, 4)
2022-10-23T10:21:23.637749Z 3 [Note] Start asynchronous binlog_dump to slave (server_id: 2), pos(, 4)
2022-10-23T10:21:27.205692Z 4 [Note] Start binlog_dump to master_thread_id(4) slave_server(3), pos(, 4)
2022-10-23T10:21:27.205732Z 4 [Note] Start asynchronous binlog_dump to slave (server_id: 3), pos(, 4)
查看状态
mysql> show slave status\G
报错：
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
因三台主机用master安装好mysql后，再克隆出来的，所以UUID是一样的
mysql> show variables like '%server_uuid%';
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | 479c649b-f501-11ec-8ca1-5254002b8ecb |
+---------------+--------------------------------------+
1 row in set (0.01 sec)
修改UUID
[root@Mysql01 ~]# vim /var/lib/mysql/auto.cnf
[auto]
server-uuid=479c649b-f501-11ec-8ca1-5254002b8ecc
[root@Mysql02 ~]# vim /var/lib/mysql/auto.cnf
[auto]
server-uuid=479c649b-f501-11ec-8ca1-5254002b8ecd
之后重启
# systemctl restart mysqld
MHA高可用搭建
MHA介绍
MHA使用perl语言编写的一个脚本管理管理工具，用于维持master主库的高可用性。
MHA包含两个部分：MHA Manager（管理节点）和MHA Node（数据节点）
MHA原理：
MHA用于维持 MySQL Replication 中 master 库的高可用性，最大的特点是可以修复多个slave上的差异日志，最终使所有slave保持数据一致，然后从中选取一个充当新的 master，并让其他 slave 指向它。
当master出现故障时，通过对比 slave 之间的 I/O thread 读取主库的 binlog 的 position 号，选取最接近的slave作为备胎（被选主库），其它从库通过与备胎对比，生成差异的中继日志，在备胎上运用从原来的 master 保存的 binlig，同时将备胎提升为master。最后在其他 slave 上运用相应的差异中继日志，并从新的 master 开始复制。
优点：
故障切换时，自动判断哪个从库与主库离的最近，并切换到上面
支持binlog server，提高 binlog 的传送效率
结合半同步功能，确保故障切换时数据不丢失
四台机器ssh互通
在四台服务器上分别执行下面命令，生成公钥和私钥，换行回车采用默认值
ssh-keygen -t rsa
在三台MySQL服务器分别执行下面命令，将公钥拷到MHA Manager服务器上
ssh-copy-id 192.168.118.73
之后可以在MHA Manager服务器上检查下，看看.ssh/authorized_keys文件是否包含3个公钥
[root@Mysql_MHA ~]# cat /root/.ssh/authorized_keys
从MHA Manager服务器执行命令，向其他三台机器分发公钥。
# for i in { 00 01 02 }; do ssh-copy-id root@Mysql$i;done
for i in { 00 01 02 }; do ssh-copy-id root@Mysql$i;done
注意：4台都要对加外三台互相，不然后面要报错@_@
MHA下载安装
三台MySQL服务器需要安装node
MHA Manager服务器需要安装manager和node
MHA node安装
#node安装包
wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58-0.el7.centos.noarch.rpm
#安装依赖
yum install perl-DBD-MySQL -y
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
MHA manager安装
在MHA Manager服务器安装mha4mysql-node和mha4mysql-manager。
MHA的manager又依赖了perl-Config-Tiny、perl-Log-Dispatch、perl-Parallel-ForkManager，也分别进行安装。
wget http://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
rpm -ivh epel-release-latest-7.noarch.rpm
yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager -y
#node安装包
wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58-0.el7.centos.noarch.rpm
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
#manager安装包
wget https://github.com/yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
//本次没碰到
提示：由于perl-Log-Dispatch和perl-Parallel-ForkManager这两个被依赖包在yum仓库找不到，
因此安装epel-release-latest-7.noarch.rpm。在使用时，可能会出现下面异常：Cannot
retrieve metalink for repository: epel/x86_64。可以尝试使
用/etc/yum.repos.d/epel.repo，然后注释掉mirrorlist，取消注释baseurl。
注意：如果出现如上错误，修改完配置后执行如下命令
yum clean all
yum makecache
yum update
yum install -y lsof
MHA配置文件
MHA Manager服务器需要为每个监控的 Master/Slave 集群提供一个专用的配置文件，而所有的Master/Slave 集群也可共享全局配置。
初始化配置目录
mkdir -p /var/log/mha/app1
touch /var/log/mha/app1/manager.log
#目录说明
#/var/log (CentOS目录)
# /mha (MHA监控根目录)
# /app1 (MHA监控实例根目录)
# /manager.log (MHA监控实例日志文件)
配置监控全局配置文件
vim /etc/masterha_default.cnf
[server default]
#管理用户名
user=root
#管理密码
password=Admin@123
#ssh登录账号
ssh_user=root
#主从复制账号
repl_user=root
#主从复制密码
repl_password=Admin@123
#设置监控主库，发送ping包的时间间隔
ping_interval=1
#设置发生切换之后发送的报警脚本
#report_script=/usr/local/send_report
#二次检查的主机，实现多路监测master的可用性
secondary_check_script=masterha_secondary_check -s 192.168.118.70 -s 192.168.118.71 -s 192.168.118.72
注意：如果不想用root，可以在前面mysql配置时先分别建好管理 账号和主从复制账号
#创建主从复制帐号
create user 'gtid'@'192.168.118.%' identified by 'gtid@123';
grant replication slave on *.* to 'gtid'@'192.168.118.%';
flush privileges;
#创建管理帐号
create user 'manage'@'192.168.118.%' identified by 'Admin@123';
grant all privileges on *.* to 'manage'@'192.168.118.%';
flush privileges;
//主从如已开始，从就不需要另外建用户了
show grants for 'manage'@'192.168.118.%';
配置监控实例配置文件
mkdir -p /etc/mha
vim /etc/mha/app1.cnf
---
[server default]
#MHA监控实例根目录
manager_workdir=/var/log/mha/app1
#MHA监控实例日志文件
manager_log=/var/log/mha/app1/manager.log
#[serverx] 服务器编号
#hostname 主机名
#candidate_master 可以做主库
#master_binlog_dir binlog日志文件目录
[server1]
hostname=192.168.118.70
candidate_master=1
master_binlog_dir="/var/lib/mysql"
[server2]
hostname=192.168.118.71
candidate_master=1
master_binlog_dir="/var/lib/mysql"
[server3]
hostname=192.168.118.72
candidate_master=1
master_binlog_dir="/var/lib/mysql"
MHA 配置检测
执行ssh通信检测
在MHA Manager服务器上执行：
[root@Mysql_MHA ~]# masterha_check_ssh --conf=/etc/mha/app1.cnf
...
[info] All SSH connection tests passed successfully.
如有报错
Sat Jun 25 16:03:43 2022 - [debug] Connecting via SSH from root@192.168.118.70(192.168.118.70:22) to root@192.168.118.72(192.168.118.72:22)..
Sat Jun 25 16:03:43 2022 - [debug] ok.
Sat Jun 25 16:03:44 2022 - [error][/usr/share/perl5/vendor_perl/MHA/SSHCheck.pm, ln63]
Sat Jun 25 16:03:43 2022 - [debug] Connecting via SSH from root@192.168.118.72(192.168.118.72:22) to root@192.168.118.70(192.168.118.70:22)..
Permission denied (publickey,gssapi-keyex,gssapi-with-mic,password).
先检查互信，4台主机都要完全互信
前面只 mysql0~2对MHA互想，MHA也对 mysql0-2互相，就会这样报错
检测MySQL主从复制
在MHA Manager服务器上执行：
[root@Mysql_MHA ~]# masterha_check_repl --conf=/etc/mha/app1.cnf
如： 出现“MySQL Replication Health is OK.”证明MySQL复制集群没有问题。
报错：
1、
Sun Jun 26 15:49:12 2022 - [error][/usr/share/perl5/vendor_perl/MHA/Server.pm, ln180] Got MySQL error when connecting 192.168.118.72(192.168.118.72:3306) :1130:Host 'Mysql_MHA' is not allowed to connect to this MySQL server, but this is not a MySQL crash. Check MySQL server settings.
Sun Jun 26 15:49:12 2022 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln301] at /usr/share/perl5/vendor_perl/MHA/ServerManager.pm line 297.
Sun Jun 26 15:49:12 2022 - [error][/usr/share/perl5/vendor_perl/MHA/Server.pm, ln180] Got MySQL error when connecting 192.168.118.71(192.168.118.71:3306) :1130:Host 'Mysql_MHA' is not allowed to connect to this MySQL server, but this is not a MySQL crash. Check MySQL server settings.
Sun Jun 26 15:49:12 2022 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln301] at /usr/share/perl5/vendor_perl/MHA/ServerManager.pm line 297.
Sun Jun 26 15:49:13 2022 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln309] Got fatal error, stopping operations
Sun Jun 26 15:49:13 2022 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations. at /usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm line 329.
Sun Jun 26 15:49:13 2022 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.
Sun Jun 26 15:49:13 2022 - [info] Got exit code 1 (Not master dead).
-----
解决方式：
修改 /etc/masterha_default.cnf文件的管理用户名，不要用root，改成manage，需要在master上新建
2、
mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8'
mysqlbinlog version command failed with rc 7:0, please verify PATH, LD_LIBRARY_PATH, and client options
at /usr/bin/apply_diff_relay_logs line 532.
Sat Jun 25 16:25:12 2022 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln208] Slaves settings check failed!
Sat Jun 25 16:25:12 2022 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln416] Slave configuration failed.
Sat Jun 25 16:25:12 2022 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations. at /usr/bin/masterha_check_repl line 48.
Sat Jun 25 16:25:12 2022 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.
Sat Jun 25 16:25:12 2022 - [info] Got exit code 1 (Not master dead).
MySQL Replication Health is NOT OK!
# vim /etc/my.cnf
[client]
default-character-set=utf8 注释掉#,再检查后面就好了，但是可以不重启，这样既不会对线上业务有影响，也可以避免复制状态检测报错。
MHA Manager启动
在MHA Manager服务器上执行：
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover /var/log/mha/app1/manager.log 2>&1 &
查看监控状态命令如下：
# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:2557) is running(0:PING_OK), master:192.168.118.70
查看监控日志命令如下：
tail -f /var/log/mha/app1/manager.log
测试MHA故障转移
模拟主节点崩溃
在MHA Manager服务器执行打开日志命令：
tail -200f /var/log/mha/app1/manager.log
关闭Master MySQL服务器服务，模拟主节点崩溃
systemctl stop mysqld
查看MHA日志，可以看到哪台slave切换成了master
Started automated(non-interactive) failover.
The latest slave 192.168.118.71(192.168.118.71:3306) has all relay logs for recovery.
Selected 192.168.118.71(192.168.118.71:3306) as a new master.
192.168.118.71(192.168.118.71:3306): OK: Applying all logs succeeded.
192.168.118.72(192.168.118.72:3306): This host has the latest relay log events.
Generating relay diff files from the latest slave succeeded.
192.168.118.72(192.168.118.72:3306): OK: Applying all logs succeeded. Slave started, replicating from 192.168.118.71(192.168.118.71:3306)
192.168.118.71(192.168.118.71:3306): Resetting slave info succeeded.
Master failover to 192.168.118.71(192.168.118.71:3306) completed successfully.
mysql> show master status;
+------------------+----------+--------------+-------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------+-------------------+
| mysql-bin.000004 | 963 | | performance_schema,information_schema,sys | |
+------------------+----------+--------------+-------------------------------------------+-------------------+
1 row in set (0.00 sec)
//这个命令没怎么标出哪台为master，感觉没啥用
//在Mysql02上看
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.118.71
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 963
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
切换 master 后，MHA进程会自动关闭 ，并生成文件 mha.failover.complete
[root@Mysql_MHA ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 is stopped(2:NOT_RUNNING).
mha.failover.complete 文件：该文件生成后，将不在允许主库故障后自动切换
将原主启动切换回主
启动Mysql00的 MySQL服务
systemctl start mysqld
另外，要先在原主库上定义成从库slave，从新的主库同步后，才可以再次转换成主库，不然原主库查看主从状态会是空的。
mysql> show slave status\G
Empty set (0.01 sec)
-----
mysql> change master to
master_host='192.168.118.71',master_port=3306,master_user='root',master_password
='Admin@123',master_auto_position=1;
-----
mysql> start slave; // 开启同步
-----
mysql> stop slave; //----第二次处理时没有关，没这提示了；要切换回来需要关闭从属，不然会提示 ERROR 3021 (HY000): This operation cannot be performed with a running slave io thread; run STOP SLAVE IO_THREAD FOR CHANNEL '' first.
1）使用MHA在线切换命令将原主切换回来
masterha_master_switch --conf=/etc/mha/app1.cnf --master_state=alive --new_master_host=192.168.118.70 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000
无法切换回来，报错
Sat Jun 25 17:25:30 2022 - [info] Starting online master switch..
Sat Jun 25 17:25:30 2022 - [info]
Sat Jun 25 17:25:30 2022 - [info] * Phase 1: Configuration Check Phase..
Sat Jun 25 17:25:30 2022 - [info]
Sat Jun 25 17:25:30 2022 - [info] Reading default configuration from /etc/masterha_default.cnf..
Sat Jun 25 17:25:30 2022 - [info] Reading application default configuration from /etc/mha/app1.cnf..
Sat Jun 25 17:25:30 2022 - [info] Reading server configuration from /etc/mha/app1.cnf..
Sat Jun 25 17:25:31 2022 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln671] Master 192.168.118.70:3306 from which slave 192.168.118.71(192.168.118.71:3306) replicates is not defined in the configuration file!
Sat Jun 25 17:25:31 2022 - [error][/usr/share/perl5/vendor_perl/MHA/ManagerUtil.pm, ln177] Got ERROR: at /usr/share/perl5/vendor_perl/MHA/MasterRotate.pm line 86.
大概是说 Master 192.168.118.70没在配置文件中定义
检查 /etc/mha/app1.cnf，会发现少了server1
[server default]
manager_log=/var/log/mha/app1/manager.log
manager_workdir=/var/log/mha/app1
[server2]
candidate_master=1
hostname=192.168.118.71
master_binlog_dir="/var/lib/mysql"
[server3]
candidate_master=1
hostname=192.168.118.72
master_binlog_dir="/var/lib/mysql"
再添加上
[server1]
hostname=192.168.118.70
candidate_master=1
master_binlog_dir="/var/lib/mysql"
再次执行
masterha_master_switch --conf=/etc/mha/app1.cnf --master_state=alive --new_master_host=192.168.118.70 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000
出现 提示很多：
Mon Oct 24 16:56:31 2022 - [info] MHA::MasterRotate version 0.58.
Mon Oct 24 16:56:31 2022 - [info] Starting online master switch..
Mon Oct 24 16:56:31 2022 - [info]
Mon Oct 24 16:56:31 2022 - [info] * Phase 1: Configuration Check Phase..
Mon Oct 24 16:56:31 2022 - [info]
Mon Oct 24 16:56:31 2022 - [info] Reading default configuration from /etc/masterha_default.cnf..
Mon Oct 24 16:56:31 2022 - [info] Reading application default configuration from /etc/mha/app1.cnf..
Mon Oct 24 16:56:31 2022 - [info] Reading server configuration from /etc/mha/app1.cnf..
Mon Oct 24 16:56:32 2022 - [info] GTID failover mode = 1
Mon Oct 24 16:56:32 2022 - [info] Current Alive Master: 192.168.118.71(192.168.118.71:3306)
Mon Oct 24 16:56:32 2022 - [info] Alive Slaves:
Mon Oct 24 16:56:32 2022 - [info] 192.168.118.70(192.168.118.70:3306) Version=5.7.38-log (oldest major version between slaves) log-bin:enabled
Mon Oct 24 16:56:32 2022 - [info] GTID ON
Mon Oct 24 16:56:32 2022 - [info] Replicating from 192.168.118.71(192.168.118.71:3306)
Mon Oct 24 16:56:32 2022 - [info] Primary candidate for the new Master (candidate_master is set)
Mon Oct 24 16:56:32 2022 - [info] 192.168.118.72(192.168.118.72:3306) Version=5.7.38-log (oldest major version between slaves) log-bin:enabled
Mon Oct 24 16:56:32 2022 - [info] GTID ON
Mon Oct 24 16:56:32 2022 - [info] Replicating from 192.168.118.71(192.168.118.71:3306)
Mon Oct 24 16:56:32 2022 - [info] Primary candidate for the new Master (candidate_master is set)
It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 192.168.118.71(192.168.118.71:3306)? (YES/no): yes 《------输入，这里指是否要刷新binlog表，保存记录用，slave机是只读，其实可以不管的
。。。。。。
Starting master switch from 192.168.118.71(192.168.118.71:3306) to 192.168.118.70(192.168.118.70:3306)? (yes/NO): yes 《------输入
。。。。。。
master_ip_online_change_script is not defined. If you do not disable writes on the current master manually, applications keep writing on the current master. Is it ok to proceed? (yes/NO):yes 《------输入
。。。。。。
Mon Oct 24 16:59:12 2022 - [info] 192.168.118.70: Resetting slave info succeeded.
Mon Oct 24 16:59:12 2022 - [info] Switching master to 192.168.118.70(192.168.118.70:3306) completed successfully.
再查下mysql01和02机子上的状态，可以看到顺利完成切换了。
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.118.70
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 194
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.118.70
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 194
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
记得要恢复启动MHA Manager
在MHA Manager服务器上执行：
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover /var/log/mha/app1/manager.log 2>&1 &
方式2-）手动切换，挂到新主，做从库
注意：有时 为了确保不会自动切换，先停掉 MHA 管理进程：
masterha_stop --conf=/etc/mha/app1.cnf
先切换mysql01
mysql> change master to
master_host='192.168.118.70',master_port=3306,master_user='root',master_password
='Admin@123',master_auto_position=1;
start slave; // 开启同步
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 192.168.118.70
注意这里mysql01切换后，如果master主机没有stop slave; 则本机还会显示master是mysql01主机，mysql01已经指向mysql00了
注意：手动切换需要对mysql02节点也要切换下，不然还是主118.71的
mysql02切换报错：
mysql> change master to
master_host='192.168.118.70',master_port=3306,master_user='root',master_password
='Admin@123',master_auto_position=1;
报错
ERROR 3081 (HY000): This operation cannot be performed with running replication threads; run STOP SLAVE FOR CHANNEL '' first
mysql> stop slave;
Query OK, 0 rows affected (0.12 sec)
mysql> change master to
master_host='192.168.118.70',master_port=3306,master_user='root',master_password
='Admin@123',master_auto_position=1;
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)
这样也顺利完成切换了
测试SQL脚本—直接 测试
使用 MySQL Quer Browser-导入.sql表格
在master上依次导入 执行这两个练习脚 本；
create.sql
populate.sql
之用查看slave节点
顺利完成主从备份
630
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
