注意看此篇文章的(我是自己有做本地仓库,所以可以yum也会教如何拉去myql官网仓库作为内网仓库,如果没有就参考他人的编译安装)!!!
本地仓库真的能省很多事情不止放mysql,所以按个人所好
环境:
cenos7.9
主机4台:
192.26.100.4 mysql1 主
192.26.100.5 mysql2 备
192.26.100.6 mysql3 备
192.26.100.7 manager 管理
VIP:
192.26.100.8 #这个只要在相同网段或者网络能互相ping通即可
mysql8.0安装包:
mysql-community-common-8.0.38-1.el7.x86_64
mysql-community-icu-data-files-8.0.38-1.el7.x86_64
mysql-community-client-plugins-8.0.38-1.el7.x86_64
mysql-community-libs-8.0.38-1.el7.x86_64
mysql-community-server-8.0.38-1.el7.x86_64
mysql80-community-release-el7-5.noarch
mysql-community-client-8.0.38-1.el7.x86_64
mysql-community-libs-compat-8.0.38-1.el7.x86_64
mha安装包:
mha4mysql-node-0.58-0.el7.centos.noarch
mha4mysql-manager-0.58-0.el7.centos.noarch
准备环境
配置hosts(四台互相配置)
192.26.100.4 mysql1
192.26.100.5 mysql2
192.26.100.6 mysql3
192.26.100.7 manager
systemctl stop firewalld
systemctl disable firewalldsetenforce 0
互配通信(列举四台中一台)
ssh-keygen
ssh-copy-id mysql1
ssh-copy-id mysql2
ssh-copy-id mysql3ssh-copy-id manager
做mysql本地仓库(在自己虚机或者可以联通外网的主机上操作)!!!
我们下载这个mysql8.0的包默认之开启8.0其他的5.7全部是默认关闭
368 cd /etc/yum.repos.d/
370 rm -rf *
372 wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
373 ls
374 mysql80-community-release-el7-3.noarch.rpm
111 rpm -ivh mysql80-community-release-el7-3.noarch.rpm
警告:mysql80-community-release-el7-3.noarch.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY
准备中... ################################# [100%]
软件包 mysql80-community-release-el7-5.noarch (比 mysql80-community-release-el7-3.noarch 还要新) 已经安装
file /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql from install of mysql80-community-release-el7-3.noarch conflicts with file from package mysql80-community-release-el7-5.noarch
file /etc/yum.repos.d/mysql-community-source.repo from install of mysql80-community-release-el7-3.noarch conflicts with file from package mysql80-community-release-el7-5.noarch
file /etc/yum.repos.d/mysql-community.repo from install of mysql80-community-release-el7-3.noarch conflicts with file from package mysql80-community-release-el7-5.noarch
111 ls
mysql-community.repo mysql-community-source.repo
111 yum install yum-utils createrepo -y
111 mkdir /mysql
302 reposync -np /mysql #同步yum.repo下的所有仓库 下载到本地/mysql
333 ls /mysql
333 mysql80-community mysql-tools-preview mysql-tools-community mysql-connectors-community
333 如果大家的仓库比我少了几个不用在意直接往下做,这时我们就可以tar打包传到内网仓库,或者做ftp仓库
ftp仓库点击传送
阿里云依赖仓库点击传送
我的mysql-community.repo文件
[mysql-connectors-community]
name=MySQL Connectors Community
baseurl=ftp://10.111.111.11/mysql/mysql/mysql-connectors-community
enabled=1
gpgcheck=0[mysql-tools-community]
name=MySQL Tools Community
baseurl=ftp://10.111.111.11/mysql/mysql/mysql-tools-community
enabled=1
gpgcheck=0[mysql80-community]
name=MySQL Tools Community
baseurl=ftp://10.111.111.11/mysql/mysql/mysql80-community
enabled=1
gpgcheck=0[mysql-tools-preview]
name=MySQL Tools Community
baseurl=ftp://10.111.111.11/mysql/mysql/mysql-tools-preview
enabled=1
gpgcheck=0
在四台机下载(这一步是把做好本地仓库repo文件下载到客户端库)
curl -o /etc/yum.repos.d/CentOS-Base.repo ftp://10.111.111.11/repo/CentOS-Base.repo
curl -o /etc/yum.repos.d/mysql-community.repo ftp://10.111.111.11/repo/mysql-community.repo
主从部署(一主两从)
三个节点统一操作
yum install mysql-community-server mysql -y
systemctl start mysqld.service #这一步是为了在/var/lib/mysql/产生文件
grep "password" /var/log/mysqld.log #记住密码
mysql -u root -p
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'hagdh123';
mysql> FLUSH PRIVILEGES;
mysql> exit
00 mysql -u root -p'hagdh123' #尝试登录如果成功退出mysql
99 systemctl stop mysqld
88 yum -y install rsync
87 rsync -av /var/lib/mysql /data/ #做这一步是因为我/data/单独挂500g盘符作为数据盘
88 ll /data/ #查看所属组
90 chown mysql:mysql /data/
mysql的主节点(配置文件)
vi /etc/my.cnf
datadir=/data/mysql #指向新的data地址
socket=/data/mysql/mysql.socklog-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id = 1
log-bin=/data/mysql/bin_log/mysql-bin #
binlog_format=mixed
character-set-server=utf8mb4
log-slave-updates=true[client]
socket=/data/mysql/mysql.sock
port=3306
#default-character-set=UTF8MB4
mysql的从1节点(配置文件)
datadir=/data/mysql
socket=/data/mysql/mysql.socklog-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id = 2
log_bin=/data/mysql/bin_log/mysql-bin
character-set-server=utf8mb4
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index[client]
socket=/data/mysql/mysql.sock
port=3306
#default-character-set=UTF8MB4
mysql的从2节点(配置文件)
datadir=/data/mysql
socket=/data/mysql/mysql.socklog-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id = 3
log_bin=/data/mysql/bin_log/mysql-bin
character-set-server=utf8mb4
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index[client]
socket=/data/mysql/mysql.sock
port=3306
#default-character-set=UTF8MB4
三个节点操作
systemctl start mysqld
mysql -u root -p
create user 'slave'@'%' identified with mysql_native_password by 'passwd';
create user 'mha'@'%' identified with mysql_native_password by 'passwd';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
GRANT ALL PRIVILEGES ON *.* TO 'mha'@'%';
在主库查看二进制文件和偏移量(master节点)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000009 | 157 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
slave1,slave2 执行同步操作,
\e
change master to
master_host='mysql1',
master_user='slave',
master_password='passwd',
master_log_file='mysql-bin.000001',
master_log_pos=157;
start slave;
#两个slave节点都需要 IO线程和 SQL 线程为yes状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: hrmysql1
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 1503
Relay_Log_File: hrmysql2-relay-bin.000002
Relay_Log_Pos: 326
Relay_Master_Log_File: mysql-bin.000002
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: 1503
Relay_Log_Space: 539
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: 07da4c90-4388-11ef-bd44-005056b23465
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica 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:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
在两个从节点操作(只读模式)
mysql> set global read_only=1;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW GLOBAL VARIABLES LIKE 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | ON |
+---------------+-------+
1 row in set (0.01 sec)
1)所有节点安装MHA node软件
#创建相关目录(所有节点)
mkdir -p /opt/mysql-mha/mha-node
# manager节点
mkdir -p /opt/mysql-mha/mhacd /opt/mysql-mha
#注意,所有节点都需要安装MHA node
#1、先安装相关依赖:yum install -y gcc gcc-c++
yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes -y
yum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker -y
yum -y install perl-CPAN mysql-devel
yum install -y perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-YAML-Tiny perl-PAR-Dist perl-Module-ScanDeps perl-Module-CoreList perl-Module-Build perl-CPAN perl-CPANPLUS perl-File-Remove perl-Module-Install
# 下载(在可以通外网机子或者在自己电脑下载)
wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58-0.el7.centos.noarch.rpm
#2、安装mha:
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
1、安装mha manager
yum install -y gcc gcc-c++
yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes -y
yum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker -y
yum -y install perl-CPAN mysql-devel
yum install -y perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-YAML-Tiny perl-PAR-Dist perl-Module-ScanDeps perl-Module-CoreList perl-Module-Build perl-CPAN perl-CPANPLUS perl-File-Remove perl-Module-Install
# 下载(在可以通外网机子或者在自己电脑下载)
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
在manager主机编写mysql_mha.cnf脚本
[root@manager ~]# cat /opt/mysql-mha/mysql_mha.cnf
[server default]
manager_log=/mysql/mha/manager.log
manager_workdir=/mysql/mha
master_binlog_dir=/data/mysql/bin_log
master_ip_failover_script=/usr/local/bin/master_ip_failover
password="Luxshare@2024#%HR"
ping_interval=1
port=3306
remote_workdir=/mysql/mha
repl_password="Luxshare@2024#%HR"
repl_user=slave
secondary_check_script=/usr/bin/masterha_secondary_check -s hrmysql1 -s hrmysql2 -s hrmysql3
user=mha[server1]
hostname=hrmysql1
port=3306
ssh_user=root
candidate_master=1
check_repl_delay=0[server2]
candidate_master=1
check_repl_delay=0
hostname=hrmysql2
port=3306
ssh_user=root[server3]
candidate_master=1
check_repl_delay=0
hostname=hrmysql3
port=3306
ssh_user=root
在manager主机编写master_ip_failover脚本
[root@manager ~]# cat /usr/local/bin/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.26.100.8/24';
my $key = '1';
# 这里的网卡名称 “ens192” 需要根据你机器的网卡名称进行修改
# 如果多台机器直接的网卡名称不统一,有两种方式,一个是改脚本,二是把网卡名称修改成统一
# 我这边实际情况是修改成统一的网卡名称
my $ssh_start_vip = "sudo /sbin/ifconfig ens192:$key $vip";
my $ssh_stop_vip = "sudo /sbin/ifconfig ens192:$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 {
"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";
}
在主服务器设置vip
/sbin/ifconfig ens192:1 192.26.100.8/24
在manager 节点测试ssh 无密认证
masterha_check_ssh -conf=/opt/mysql-mha/mysql_mha.cnf
------------------------------------------
Thu Jul 18 11:18:24 2024 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Jul 18 11:18:24 2024 - [info] Reading application default configuration from /opt/mysql-mha/mysql_mha.cnf..
Thu Jul 18 11:18:24 2024 - [info] Reading server configuration from /opt/mysql-mha/mysql_mha.cnf..
Thu Jul 18 11:18:24 2024 - [info] Starting SSH connection tests..
Thu Jul 18 11:18:26 2024 - [debug]
Thu Jul 18 11:18:24 2024 - [debug] Connecting via SSH from root@hrmysql1(10.180.248.39:22) to root@hrmysql2(10.180.248.40:22)..
Thu Jul 18 11:18:25 2024 - [debug] ok.
Thu Jul 18 11:18:25 2024 - [debug] Connecting via SSH from root@hrmysql1(10.180.248.39:22) to root@hrmysql3(10.180.248.41:22)..
Thu Jul 18 11:18:26 2024 - [debug] ok.
Thu Jul 18 11:18:27 2024 - [debug]
Thu Jul 18 11:18:25 2024 - [debug] Connecting via SSH from root@hrmysql2(10.180.248.40:22) to root@hrmysql1(10.180.248.39:22)..
Thu Jul 18 11:18:26 2024 - [debug] ok.
Thu Jul 18 11:18:26 2024 - [debug] Connecting via SSH from root@hrmysql2(10.180.248.40:22) to root@hrmysql3(10.180.248.41:22)..
Thu Jul 18 11:18:26 2024 - [debug] ok.
Thu Jul 18 11:18:27 2024 - [debug]
Thu Jul 18 11:18:25 2024 - [debug] Connecting via SSH from root@hrmysql3(10.180.248.41:22) to root@hrmysql1(10.180.248.39:22)..
Thu Jul 18 11:18:26 2024 - [debug] ok.
Thu Jul 18 11:18:26 2024 - [debug] Connecting via SSH from root@hrmysql3(10.180.248.41:22) to root@hrmysql2(10.180.248.40:22)..
Thu Jul 18 11:18:27 2024 - [debug] ok.
Thu Jul 18 11:18:27 2024 - [info] All SSH connection tests passed successfully.
在manager 节点上测试mysql主从情况
masterha_check_repl -conf=/opt/mysql-mha/mysql_mha.cnf
-------------------------------
IN SCRIPT TEST====root|sudo /sbin/ifconfig ens192:1 down==root|sudo /sbin/ifconfig ens192:1 10.180.248.54/24===
Checking the Status of the script.. OK
Thu Jul 18 11:19:01 2024 - [info] OK.
Thu Jul 18 11:19:01 2024 - [warning] shutdown_script is not defined.
Thu Jul 18 11:19:01 2024 - [info] Got exit code 0 (Not master dead).MySQL Replication Health is OK.
我只粘贴一点点
echo $?
0
证明就是成功了
在manage上启动mha
nohup masterha_manager \
--conf=/opt/mysql-mha/mysql_mha.cnf \
--remove_dead_master_conf \
--ignore_last_failover < /dev/null > /var/log/mha_manager.log 2>&1 &
查看MHA状态
masterha_check_status --conf=/opt/mysql-mha/mysql_mha.cnf
查看MHA日志文件
cat /opt/mysql-mha/manager.log | grep "current master"
manager节点关闭manager服务
masterha_stop --conf=/opt/mysql-mha/mysql_mha.cnf
故障模拟与恢复
停掉mysql master
systemctl stop mysqld
查看两个从看vip漂移到哪台机子了
[root@hrmysql2 ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: ens192: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000
link/ether 00:50:56:b2:34:65 brd ff:ff:ff:ff:ff:ff
inet 192.26.100.5/24 brd 192.26.100.255 scope global noprefixroute ens192
valid_lft forever preferred_lft forever
inet 192.26.100.8/24 brd 192.26.100.255 scope global secondary ens192:1
valid_lft forever preferred_lft forever
inet6 fe80::250:56ff:feb2:3465/64 scope link
valid_lft forever preferred_lft forever
进入MySQL
mysql -u root -p
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 1503
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
另外一台从节点已设置mysql2为主节点
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: mysql2
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 1503
Relay_Log_File: relay-log-bin.000002
Relay_Log_Pos: 326
Relay_Master_Log_File: mysql-bin.000002
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: 1503
Relay_Log_Space: 534
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: 07da4c90-4388-11ef-bd44-005056b23465
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica 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:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
恢复
再在原master服务器上执行同步操作
systemctl start mysqld
mysql -u -p
\e
change master to
master_host='mysql1',
master_user='slave',
master_password='passwd',
master_log_file='mysql-bin.000001',
master_log_pos=157;
start slave;
#两个slave节点都需要 IO线程和 SQL 线程为yes状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: mysql2
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 1503
Relay_Log_File: mysql1-relay-bin.000002
Relay_Log_Pos: 326
Relay_Master_Log_File: mysql-bin.000002
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: 1503
Relay_Log_Space: 539
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: 07da4c90-4388-11ef-bd44-005056b23465
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica 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:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
遇到mha问题以及解决办法
Wed Jul 17 09:49:28 2024 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Jul 17 09:49:28 2024 - [info] Reading application default configuration from /opt/mysql-mha/mysql_mha.cnf..
Wed Jul 17 09:49:28 2024 - [info] Reading server configuration from /opt/mysql-mha/mysql_mha.cnf..
Wed Jul 17 09:49:28 2024 - [info] MHA::MasterMonitor version 0.58.
Wed Jul 17 09:49:29 2024 - [error][/usr/share/perl5/vendor_perl/MHA/Server.pm, ln308] Getting relay log directory or current relay logfile from replication table failed on 10.180.248.40(10.180.248.40:3306)!
Wed Jul 17 09:49:29 2024 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations. at /usr/share/perl5/vendor_perl/MHA/ServerManager.pm line 315.
Wed Jul 17 09:49:29 2024 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.
Wed Jul 17 09:49:29 2024 - [info] Got exit code 1 (Not master dead).
[root@mysql-manager .ssh]# masterha_check_repl -conf=/opt/mysql-mha/mysql_mha.cnf
Mon Jul 15 17:01:21 2024 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Jul 15 17:01:21 2024 - [info] Reading application default configuration from /opt/mysql-mha/mysql_mha.cnf..
Mon Jul 15 17:01:21 2024 - [info] Reading server configuration from /opt/mysql-mha/mysql_mha.cnf..
Mon Jul 15 17:01:21 2024 - [info] MHA::MasterMonitor version 0.58.
Mon Jul 15 17:01:22 2024 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln188] There is no alive server. We can't do failover
Mon Jul 15 17:01:22 2024 - [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.
Mon Jul 15 17:01:22 2024 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.
Mon Jul 15 17:01:22 2024 - [info] Got exit code 1 (Not master dead).
MySQL Replication Health is NOT OK!
这两个报错都是MHA这个用户没有权限真的是排死我了
执行下方sql就可以了
GRANT ALL PRIVILEGES ON *.* TO 'mha'@'%';