Mysql MHA部署的常见问题都有哪些呢

这篇文章将为大家详细讲解有关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]$ 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

关于Mysql MHA部署的常见问题都有哪些呢就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。