mysql多实例实现高可用出现的错误汇总

实例实现高可用出现的错误汇总"/>

mysql多实例实现高可用出现的错误汇总

问题一：
Wed Jul 12 20:31:36 2023 - [error][/usr/share/perl5/vendor_perl/MHA/Server.pm, ln398] localhost(127.0.0.1:3309): User repl does not exist or does not have REPLICATION SLAVE privilege! Other slaves can not start replication from this host.
Wed Jul 12 20:31:36 2023 - [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 1403.
Wed Jul 12 20:31:36 2023 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.
问题原因：没有在每个高可用的从库中创建repl用户。
解决方法：在每个从库中执行grant all privileges on . to repl@‘localhost’ identified by ‘repl’; 以创建repl用户.
问题二：
[debug] Connecting via SSH from root@localhost(127.0.0.1:22) to root@localhost(127.0.0.1:22)…
Permission denied (publickey,gssapi-keyex,gssapi-with-mic,password).
Wed Jul 12 17:39:47 2023 - [error][/usr/share/perl5/vendor_perl/MHA/SSHCheck.pm, ln111] SSH connection from root@localhost(127.0.0.1:22) to root@localhost(127.0.0.1:22) failed!
原因：这是因为在单机上创建多实例MySQL数据库做MHA时没有做ssh-keygen操作。
解决方法：（1）执行ssh-keygen命令
（2）cd /root/.ssh
（3）mv id_rsa.pub authorized_keys
问题三：Failed to save binary log: Binlog not found from /data/mysql/binlog! If you got this error at MHA Manager, please set “master_binlog_dir=/path/to/binlog_directory_of_the_master” correctly in the MHA Manager’s configuration file and try again.
原因：在单机多实例数据库中，从库的binlog文件名和主库的binlog文件名不一致
解决办法：把主和从库的binlog都复制到/data/mysql/binlog目录中

问题四： Slave I/O for channel ‘’: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it). Error_code: 1593
原因：主从的server-id一致
解决方法：在配置文件中修改从库的server-id之后，重启从库，进入从库之后从新构建主从关系

重新构建主从：stop slave;reset slave all;chage master to…;start slave;show slave status;

问题五：[error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln781] Multi-master configuration is detected, but two or more masters are either writable (read-only is not set) or dead! Check configurations for details. Master configurations are as below:
原因：一主二从的环境中，第一次模拟关闭主机，将第一从节点作为主节点来恢复一主二从环境。第二次模拟关闭主机，将关闭的主机作为从节点连接上次的主机上。因为上次的主机在第一次故障中作为从节点恢复一主二从。第二次恢复的时候，没有对作为从节点的主机执行：stop slave ;reset slave all; 导致无法识别究竟谁是主节点。
解决：对作为新主节点的主机执行：stop slave; reset slave all;然后启动MHA。