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部署的常見問題都有哪些呢就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。