这篇文章将为大家详细讲解有关Mysql MHA部署的常见问题都有哪些呢，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。Mysql MHA部署-07常见问题架构说明：一：从库同步失败从库执行同步操作：mysql>change master to master_host=’192.168.2.187′, master_user=’repl’,master_password=’rpl’,master_log_file=’mysql-bin.000001′,master_log_pos= 34751569;查看同步状态show slave status G;错误如下：Last_IO_Error: Got fatal error 1236 from master when reading data from binary log:‘Client requested master to start replication from position > file size’查看主库187file和Position:mysql> show master status;+——————+———-+————–+——————+——————————————–+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+——————+———-+————–+——————+——————————————–+| mysql-bin.000004 | 34751569 | | | c1227971-65b3-11ea-bf67-080027839e5c:1-297 |+——————+———-+————–+——————+——————————————–+1 row in set (0.00 sec)查看主库end_log_pos也是34751569:[mysql@rac1 binlog]$ pwd/datalog/mysql/binlog[mysql@rac1 binlog]$ lsmysql-bin.000001 mysql-bin.000002 mysql-bin.000003 mysql-bin.000004 mysql-bin.index[mysql@rac1 binlog]$ mysqlbinlog mysql-bin.000004 > /home/mysql/0314bin.log[mysql@rac1 binlog]$ vim /home/mysql/0314bin.log…# at 34751434#200314 16:56:32 server id 1 end_log_pos 34751569 CRC32 0x95bd83f1 Query thread_id=3 exec_time=0 error_code=0问题原因：突然发现同步时错将master_log_file指定成’mysql-bin.000001’了，在mysql-bin.000001中肯定是找不到34751569。解决方案：从库重新同步mysql> stop slave;mysql>change master to master_host=’192.168.2.187′, master_user=’repl’,master_password=’rpl’,master_log_file=’mysql-bin.000004′,master_log_pos= 34751569;Query OK, 0 rows affected, 2 warnings (0.03 sec)mysql> start slave;Query OK, 0 rows affected (0.00 sec)mysql>show slave statusGmysql> show slave statusG*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.2.187 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 34751569 Relay_Log_File: mysql-relay.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes…… Seconds_Behind_Master: 0……二：自动切换VIP没有漂移问题：主库故障，将master切换到从库，但是VIP没有飘到从库[root@rac4 ~]# tail -f /etc/mha/manager/mha.logSet read_only=0 on the new master.Enabling the VIP 192.168.2.189 on the new master: rac2SIOCSIFADDR: No such deviceeth0:2: ERROR while getting interface flags: No such deviceSIOCSIFNETMASK: No such devicearping: Device eth0 not available.问题原因：发现脚本master_ip_failover和master_ip_online_change中网卡名写错了，不是eth0，应该改成解决方案：需要修改—master_ip_failover修改my $vip = ‘192.168.2.189’;my $key = “0”;my $ssh_start_vip = “/sbin/ifconfig enp0s3:$key $vip/24”;my $ssh_stop_vip = “/sbin/ifconfig enp0s3:$key down”;my $ssh_send_garp = “/sbin/arping -U $vip -I enp0s3 -c 1”;—master_ip_online_change修改my $vip = ‘192.168.2.189’;my $key = “0”;my $ssh_start_vip = “/sbin/ifconfig enp0s3:$key $vip/24”;my $ssh_stop_vip = “/sbin/ifconfig enp0s3:$key down”;m开发云主机域名y $ssh_send_garp = “/sbin/arping -U $vip -I enp0s3 -c 1”;三：自动切换后从库233连不上新master188问题现象：执行：mysql> show slave status G;报错：Last_IO_Error: error connecting to master ‘repl@192.168.2.188:3306’ – retry-time: 60 retries: 5问题原因：rel同步用户密码写错了，应该是rel，错写成123456了解决方案：修改密码或直接新建用户并授权mysql> drop user ‘repl’@’192.168.2.223’;mysql> CREATE USER ‘repl’@’192.168.2.223’ IDENTIFIED BY ‘rpl’;mysql> GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’192.168.2.223’;mysql> flush privileges;四：同步状态异常问题现象：执行：mysql> show slave status G;报错： Last_Error: Error ‘Operation CREATE USER failed for ‘repl’@’192.168.2.188” on query.Default database: ”. Query: ‘CREATE USER ‘repl’@’192.168.2.188’ IDENTIFIED WITH ‘mysql_native_password’AS ‘*624459C87E534A126498ADE1B12E0C66EDA035A3”问题原因：已经存在这个用户了，再次创建提示失败解决方案：删除用户，重新同步mysql> drop user ‘repl’@’192.168.2.188’;mysql> flush privileges;mysql> stop slave;mysql> start slave;mysql> show slave status G;五：启动mha报错错误：[root@rac4 ~]# tail -f /etc/mha/manager/mha.logSat Mar 14 21:50:07 2020 – [error][/usr/share/perl5/vendor_perl/MHA/MasterFailover.pm, ln309]Last failover was done at 2020/03/14 20:02:10. Current time is too early to do failover again.If you want to do failover, manually remove /etc/mha/manager/mha.failover.complete and run this script again.解决方案：[root@rac4 ~]# rm -rf /etc/mha/manager/mha.failover.complete六：从库重新同步错误执行：mysql>change master to master_host=’192.168.2.188′, master_user=’repl’,master_password=’rpl’,master_log_file=’mysql-bin.000006′,master_log_pos= 2709;错误：ERROR 1776 (HY000): Parameters MASTER_LOG_FILE, MASTER_LOG_POS,RELAY_LOG_FILE and RELAY_LOG_POS cannot be set when MASTER_AUTO_POSITION is active.解决方案 ：mysql> change master to master_auto_position=0;Query OK, 0 rows affected (0.02 sec)mysql>change master to master_host=’192.168.2.188′, master_user=’repl’,master_password=’rpl’,master_log_file=’mysql-bin.000006′,master_log_pos= 2709;Query OK, 0 rows affected, 2 warnings (0.00 sec)mysql> start slave;mysql> show slave status G;七：MHA检查复制报错问题：执行复制检查[root@rac4 script]# masterha_check_repl –conf=/etc/mha/mha.conf报错：MySQL Replication Health is NOT OK!Sat Mar 14 20:12:06 2020 – [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln424]Error happened on checking configurations. Can’t exec “/etc/mha/script/master_ip_failover”:Permission denied at /usr/share/perl5/vendor_perl/MHA/ManagerUtil.pm line 68.问题原因：相应脚本没有可执行权限解决方案：[root@rac4 script]# ll -rthtotal 24K-rw-r–r– 1 root root 2.4K Mar 14 19:56 send_report-rw-r–r– 1 root root 4.3K Mar 14 20:01 master_ip_failover-rw-r–r– 1 root root 11K Mar 14 20:01 master_ip_online_change[root@rac4 script]# chmod a+x *[root@rac4 script]# ll -rthtotal 24K-rwxr-xr-x 1 root root 2.4K Mar 14 19:56 send_report-rwxr-xr-x 1 root root 4.3K Mar 14 20:01 master_ip_failover-rwxr-xr-x 1 root root 11K Mar 14 20:01 master_ip_online_change关于Mysql MHA部署的常见问题都有哪些呢就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。
力学原理图是用于展示物体的受力以及运动情况的示意图，力学原理图一般是简化画法，会将主要的受力点进行标注，用于受力分析。力学原理图是物理学基础的一种图示，在教育行业被广泛运用。 力学原理图制作软件，助你轻松绘制教育绘图 亿图图示软件可以绘制简单易懂的原理图，因为…
免责声明：本站发布的图片视频文字，以转载和分享为主，文章观点不代表本站立场，本站不承担相关法律责任；如果涉及侵权请联系邮箱：360163164@qq.com举报，并提供相关证据，经查实将立刻删除涉嫌侵权内容。
微信扫一扫 
