接MYSQL高可用MHA一:GTID方式主从复制
主从复制目前一切正常,开始mha配置
主:10.121.151.234 port:3306
从1:10.121.151.235 port:3306
从2:10.121.151.236 port:3306
(1)安装依赖包
yum -y install perl-DBD-MySQL
(2)安装mha4mysql-node-0.58-0.el7.centos.noarch.rpm
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
#特别提示:3台MySQL都需要安装mha4mysql-node-0.56-0.el6.noarch.rpm
从2:10.121.151.236 port:3306
1.使用阿里云源+epel源
(1)备份
mv /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.backup
(2)下载新的 CentOS-Base.repo 到 /etc/yum.repos.d/
curl -o /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
(3)生成缓存
yum makecache
(4)epel源
curl -o /etc/yum.repos.d/epel-7.repo http://mirrors.aliyun.com/repo/epel-7.repo
2.安装manager依赖包(需要公网源)
yum -y install perl-Config-Tiny epel-release perl -Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
3.安装manager包
mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
4.编辑配置文件
mkdir -p /etc/mha
5.创建日志目录
mkdir -p /var/log/mha/mha
6.创建配置文件(默认没有)
vi /etc/mha/mha1.cnf
[server default]
manager_log=/var/log/mha/mha1/manager
manager_workdir=/var/log/mha/mha1
master_binlog_dir=/usr/local/mysql/data
password=123123
ping_interval=2
repl_password=123123
repl_user=rep
ssh_user=root
user=mha
[server1]
hostname=10.121.151.234
port=3306
[server2]
hostname=10.121.151.235
port=3306
[server3]
hostname=10.121.151.236
port=3306
配置ssh信任(所有)
从2:10.121.151.236 port:3306
1.创建密钥对
ssh-keygen -t dsa -P "" -f ~/.ssh/id_dsa >/dev/nul l 2>&1
2.发送10.121.151.236公钥,包括自己
ssh-copy-id -i /root/.ssh/id_dsa.pub root@10.121.151.234
ssh-copy-id -i /root/.ssh/id_dsa.pub root@10.121.151.235
ssh-copy-id -i /root/.ssh/id_dsa.pub root@10.121.151.236
从1:10.121.151.235 port:3306
发送10.121.151.236公钥,包括自己
ssh-copy-id -i /root/.ssh/id_dsa.pub root@10.121.151.234
ssh-copy-id -i /root/.ssh/id_dsa.pub root@10.121.151.235
ssh-copy-id -i /root/.ssh/id_dsa.pub root@10.121.151.236
主:10.121.151.234 port:3306
发送10.121.151.236公钥,包括自己
ssh-copy-id -i /root/.ssh/id_dsa.pub root@10.121.151.234
ssh-copy-id -i /root/.ssh/id_dsa.pub root@10.121.151.235
ssh-copy-id -i /root/.ssh/id_dsa.pub root@10.121.151.236
mha启动测试
从2:10.121.151.236 port:3306
1.ssh检查检测
masterha_check_ssh --conf=/etc/mha/mha1.cnf
[info] All SSH connection tests passed successfully. #成功
2.主从复制检测
masterha_check_repl --conf=/etc/mha/mha1.cnf
MySQL Replication Health is OK. #成功
3.启动MHA
nohup masterha_manager --conf=/etc/mha/mha1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/mha1/manager.log 2>&1 &
ps -ef | grep perl | grep -v grep
root 4961 4690 0 06:33 pts/2 00:00:00 perl /usr/bin/masterh a_manager --conf=/etc/mha/mha1.cnf --remove_dead_master_conf --ignore_l ast_failover
#说明:
nohup:启动命令
--conf:指定配置文件位置
--remove_dead_master_conf:如果有master down了,就去掉配置文件里该master的部分
进行mha自动切换master的测试
1.登陆从2:10.121.151.236 port:3306 查看信息状态(主库IP地址)
mysql -uroot -p123123 -S /data/mysql/mysql3306/mysql.sock
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.121.151.234
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 3887
Relay_Log_File: localhost-relay-bin.000003
Relay_Log_Pos: 3135
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
2.停掉主:10.121.151.234 port:3306 上的MySQL服务
cd /data/mysql/mysql3306/
./mysql stop
3.查看从2:10.121.151.236 port:3306 上的MySQL从库同步状态
mysql -uroot -p123123 -S /data/mysql/mysql3306/mysql.sock
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.121.151.235
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 4327
Relay_Log_File: bogon-relay-bin.000002
Relay_Log_Pos: 755
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
4.查看从1:10.121.151.235 port:3306 上的MySQL,主库同步状态。
mysql -uroot -p123123 -S /data/mysql/mysql3306/mysql.sock
mysql> show slave status\G
Empty set, 1 warning (0.00 sec) #正常现象,因为现在是主库
show master status; #参数可以和上面对的上
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set
| mysql-bin.000001 | 4327 | | | 80b472df-b67e-11ec-9463-000c290d7cca:1,
81210d08-b67e-11ec-b4af-000c29932170:1-17 |
5.查看从2:10.121.151.236 port:3306 上的mha进程状态
ps -ef | grep perl | grep -v grep #查询发现mha 进程已经没了
[1]+ 完成 nohup masterha_manager --conf=/etc/mha/mha1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/mha1/manager.log 2>&1
6.查看mha配置文件信息
cat /etc/mha/mha1.cnf (没有了[server1]的信息参数)
[server default]
manager_log=/var/log/mha/mha1/manager
manager_workdir=/var/log/mha/mha1
master_binlog_dir=/usr/local/mysql/data
password=123123
ping_interval=2
repl_password=123123
repl_user=rep
ssh_user=root
user=mha
[server2]
hostname=10.121.151.235
port=3306
[server3]
hostname=10.121.151.236
port=3306
将故障宕机的主:10.121.151.234 port:3306 的MySQL服务启动并授权进行从同步
cd /data/mysql/mysql3306/
./mysql start
mysql -uroot -p123123 -S /data/mysql/mysql3306/mysql.sock
CHANGE MASTER TO MASTER_HOST='10.121.151.235', MASTER_PORT=3306,MASTER_AUTO_POSITION=1, MASTER_USER='rep', MASTER_PASSWORD='123123';
start slave;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.121.151.235
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 4327
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 755
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
从2:10.121.151.236 port:3306
将mha配置文件里缺失的部分补全
*********************************************报错集锦*******************************************************
(1)报错 masterha_check_repl --conf=/etc/mha/mha1.cnf
Mon Apr 4 22:47:38 2022 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln188] There is no alive server. We can't do failover
Mon Apr 4 22:47:38 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.
Mon Apr 4 22:47:38 2022 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.
Mon Apr 4 22:47:38 2022 - [info] Got exit code 1 (Not master dead). M
ySQL Replication Health is NOT OK!
解决方法:
在主库 mysql>
+------------------+--------------+-----------------------+
| user | host | plugin |
+------------------+--------------+-----------------------+
| root | % | caching_sha2_password |
| mha | 192.168.31.% | caching_sha2_password |
| rep | 192.168.31.% | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session | localhost | caching_sha2_password |
| mysql.sys | localhost | caching_sha2_password |
+------------------+--------------+-----------------------+
ALTER USER 'mha'@'10.121.151.%' IDENTIFIED WITH mysql_native_password BY '123123';
FLUSH PRIVILEGES;
grant all on *.* to mha@'10.121.151.%';
FLUSH PRIVILEGES;
mysql> select user,host,plugin from mysql.user;
+------------------+--------------+-----------------------+
| user | host | plugin |
+------------------+--------------+-----------------------+
| root | % | caching_sha2_password |
| mha | 192.168.31.% | mysql_native_password |
| rep | 192.168.31.% | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session | localhost | caching_sha2_password |
| mysql.sys | localhost | caching_sha2_password |
+------------------+--------------+-----------------------+
(2)继续操作报错:
masterha_check_repl --conf=/etc/mha/mha1.cnf
[error][/usr/share/perl5/vendor_perl/MHA/Server.pm, ln265] Checking slave status failed on 192.168.31.134(192.168.31.134:3308). err=Got error when executing SHOW SLAVE STATUS. Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation
原因:
/etc/mha/mha1.cnf文件中
user=mha
在做此用户授权时出错.造成无法连接数据库检查状态
解决方法
此用户需要UPER, REPLICATION CLIENT 权限.最简单的方法就是赋予所有权限,次方法生产环境下需要谨慎.(这个文档我给了和主从复制账户一样的权限了,所以错误了:grant replication slave on *.* to 'mha'@'10.121.151.%';)
grant all on *.* to mha@'10.121.151.%';
(3)报错:(mha成了之后,把主库停顿,然后查看从库2,应该连接从库1,结果报错)
show slave status\G
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 8 failed executing transaction '378d3f91-b424-11ec-acbe-000c290d7cca:1' at master log mysql-bin.000001, end_log_pos 491. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
详细报错(错误说是root用户的加密方式,此时方式是caching_sha2_password):
select * from performance_schema.replication_applier_status_by_worker\G LAST_ERROR_MESSAGE: Worker 8 failed executing transaction '378d3f91-b424-11ec-acbe-000c290d7cca:1' at master log mysql-bin.000001, end_log_pos 491; Error 'Operation ALTER USER failed for 'root'@'localhost'' on query. Default database: ''. Query: 'ALTER USER 'root'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$ ie7G(byk9IuST61k=%ucLZ9SmuyEqEYqVeDW36W3cFDCcQ65ABSIGQfxxRb0D'
解决方法(从库1设置,因为要把它当作主库):误操作造成:
root的密码权限给改成了%
加密方式为mysql_native_password所以导致这个报错无法解决
在MySQL8.0之前,身份验证的插件是mysql_native_password,在MySQL 8.0中,caching_sha2_password 是默认的身份验证插件,安全性更高。
在MySQL中,系统状态变量Rsa_public_key,此值是sha256_password身份验证插件用于基于RSA密钥对的密码交换的公用密钥 。对于使用该sha256_password 插件的客户端,连接到服务器时,密码永远不会以明文形式公开。密码传输的方式取决于是否使用安全连接或RSA加密:
正确操作: select user,host,plugin from mysql.user where user='root';
| user | host | plugin |
| root |% | mysql_native_password |
#授权方式更改
mysql > update user set host='localhost' where user='root';
# 指定用户的授权
mysql > grant all privileges on *.* to root@'localhost'';
#刷新
flush privileges;
#更改密码加密方式
alter user 'root'@'localhost' identified with caching_sha2_password by '123123';
查询
select user,host,plugin from mysql.user where user='root';
+------+-----------+-----------------------+
| user | host | plugin |
+------+-----------+-----------------------+
| root | localhost | caching_sha2_password
| +------+-----------+-----------------------+
重新测试:需要把主库启动
从库1:stop slave;和start slave;
从库2:stop slave; CHANGE MASTER TO MASTER_HOST='192.168.31.134',MASTER_PORT=3308,MASTER_USER='rep',MASTER_PASSWORD='123123',master_auto_position=1;
最后start slave;继续进行mha启动:nohup masterha_manager --conf=/etc/mha/mha1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/mha1/manager.log 2>&1 &
然后重新实验:
关闭主库, ./mysql stop
从库2操作: show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event
Master_Host: 10.121.151.235
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 4327
Relay_Log_File: bogon-relay-bin.000002
Relay_Log_Pos: 755
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
(4)格外补充,增加root权限可以让别用户进行的登录
# 使用mysql 数据库
mysql > use mysql;
# 特定用户的host 修改
mysql > update user set host='%' where user='root';
# 指定用户的授权
mysql > grant all privileges on *.* to root@'%';
#刷新
flush privileges;