Shulou(Shulou.com)05/31 Report--

This article will explain in detail what are the common questions about Mysql MHA deployment. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have some understanding of the relevant knowledge after reading this article.

Mysql MHA deployment-07 FAQ

Architecture description:

One: synchronization from the library failed

Perform a synchronization operation from the library:

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

View synchronization status show slave status\ G

The error is as follows:

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'

Check the main library 187file and 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)

Check that the main library end_log_pos is also 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

The cause of the problem:

Suddenly found that during synchronization, master_log_file was mistakenly specified as' mysql-bin.000001'. You must not be able to find 34751569 in mysql-bin.000001.

Solution:

Resync from the library

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

.

Second: automatically switch VIP without drift

Problem: master library failure, switching master to slave library, but VIP does not float to slave library

[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.

The cause of the problem:

It is found that the name of the network card in the scripts master_ip_failover and master_ip_online_change is misspelled, not eth0, but should be changed to

Solution: need to modify

-master_ip_failover

Modify

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

Modify

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"

Three: after automatic switching, the new master188 cannot be connected from the library.

Problem phenomenon:

Execute: mysql > show slave status\ G

Error report: Last_IO_Error: error connecting to master 'repl@192.168.2.188:3306'-retry-time: 60 retries: 5

Cause of the problem: the rel synchronization user password was miswritten. It should be rel. It was miswritten as 123456.

Solution:

Change the password or directly create a new user and authorize

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

Fourth, synchronization status is abnormal

Problem phenomenon:

Execute: 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'* 624459C87E534A126498ADE1B12E0C66EDA035A3fold'

Cause of the problem: this user already exists. Failed to create prompt again.

Solution: delete users, resynchronize

Mysql > drop user 'repl'@'192.168.2.188'

Mysql > flush privileges

Mysql > stop slave

Mysql > start slave

Mysql > show slave status\ G

Five: start mha to report an error

Error:

[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.

Solution:

[root@rac4 ~] # rm-rf / etc/mha/manager/mha.failover.complete

Six: resynchronization error from library

Execute:

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:

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.

Solution:

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

Seven: MHA check replication error

Problem: perform replication check

[root@rac4 script] # masterha_check_repl-- conf=/etc/mha/mha.conf

Error report:

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.

The cause of the problem:

The corresponding script does not have executable permissions

Solution:

[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 axix *

[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

This is the end of the common questions about Mysql MHA deployment. I hope the above content can be helpful to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.