Mysql MHA部署-07常见问题 一 Mysql MHA部署-01介绍 二 Mysql MHA部署-02主从复制 三 Mysql MHA部署-03MHA软件安装 四 Mysql MHA部署-04MHA配置 五 Mysql MHA部署-05故障转移 六 Mysql MHA部署-06手动切换 七 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]$ ls
mysql-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 status\G
mysql> show slave status\G
*************************** 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.log
Set read_only=0 on the new master.
Enabling the VIP 192.168.2.189 on the new master: rac2
SIOCSIFADDR: No such device
eth0:2: ERROR while getting interface flags: No such device
SIOCSIFNETMASK: No such device
arping: 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";
my $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.log
Sat 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 -rth
total 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 -rth
total 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
欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！
