mysql数据库中MHA配置出现的故障

最新推荐文章于 2024-07-03 16:09:10 发布

原创最新推荐文章于 2024-07-03 16:09:10 发布 · 895 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#MHA故障 #mysql

故障专栏收录该内容

6 篇文章

订阅专栏

在配置MHA时遇到了两个主要问题：1) 主从复制权限不足，需要在所有节点上为特定用户授予权限；2) mysqlbinlog命令找不到，解决方案是在所有节点创建软链接指向正确路径。通过执行相应的GRANT语句和ln -s命令，成功解决了这两个故障。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

[root@mha ~]# masterha_check_repl --conf=/etc/masterha/app1.cnf
问题一：
[error] [/usr/local/share/perl5/MHA/Server.pm, line 180] Got MySQL error when connecting 192.168.130.13(192.168.130.30:3306) :1130:Host ‘192.168.130.40’ is not allowed to connect to this MySQL server, but this is not a MySQL crash. Check MySQL server setting.

解决办法:mha服务器连接不上130.30这台服务器节点，需要在30这台节点上mysql数据库中给权限。是前面主从分离时，有授权用户忘记添加了
查看有没有一下授权用户（三台节点都要添加）

mysql> grant replication slave on . to myslave@‘192.168.130.%’ identified by ‘123456’;
mysql> grant all privileges on . to mha@‘192.168.130.%’ identified by ‘manager’;
mysql> grant all privileges on . to mha@‘master’ identified by ‘manager’;
mysql> grant all privileges on . to mha@‘slave1’ identified by ‘manager’;
mysql> grant all privileges on . to mha@‘slave2’ identified by ‘manager’;
mysql> flush privileges;

问题二：
Can’t exec “mysqlbinlog”: 没有那个文件或目录 at /usr/local/share/perl5/MHA/BinlogManager.pm line 106.mysqlbinlog version command failed with rc 1:0, please verify PATH,LD_LIBRARY_PATH, and client options at /usr/local/bin/apply_diff_relay_logs line 493.
解决办法:在所有节点上执行如下命令
[root@centos7-01 ~]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/local/bin/mysqlbinlog
[root@centos7-01 ~]# ln -s /usr/local/mysql/bin/mysql /usr/local/bin/mysql

[root@centos7-02 ~]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/local/bin/mysqlbinlog
[root@centos7-02 ~]# ln -s /usr/local/mysql/bin/mysql /usr/local/bin/mysql

[root@centos7-03 ~]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/local/bin/mysqlbinlog
[root@centos7-03 ~]# ln -s /usr/local/mysql/bin/mysql /usr/local/bin/mysql

问题三：
mysqlbinlog: unknown variable 'default-character-set=utf8’mysqlbinlog version command failed with rc 7:0, please verify PATH, LD_LIBRARY_PATH, and client options

解决办法：

在所有从服务器上注释掉clinet段default-character-set=utf8mb4

更改默认字符集UTF8到utf8mb4

[client]

#default-character-set = utf8mb4

[mysql]

default-character-set = utf8mb4

[mysqld]

character-set-client-handshake = FALSE

character-set-server = utf8mb4

collation-server = utf8mb4_unicode_ci

init_connect=‘SET NAMES utf8mb4’