Summary of several error logs about MySQL MHA setup and switching

1: masterha_check_repl replica set error replicates is not defined in the configuration file!

The specific information is as follows:

# /usr/local/bin/masterha_check_repl --conf=/etc/mha/app1.cnf
Thu Nov 21 15:33:15 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Nov 21 15:33:15 2018 - [info] Reading application default configuration from /etc/mha/app1.cnf..
Thu Nov 21 15:33:15 2018 - [info] Reading server configuration from /etc/mha/app1.cnf..
Thu Nov 21 15:33:15 2018 - [info] MHA::MasterMonitor version 0.56.
Thu Nov 21 15:33:16 2018- [error][/usr/local/share/perl5/MHA/ServerManager.pm, ln671] Master 179.179.19.179:3306 from which slave 179.179.19.180(179.179.19.180:3306) replicates is not defined in the configuration file!
Thu Nov 21 15:33:16 2018 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln424] Error happened on checking configurations. at /usr/local/share/perl5/MHA/MasterMonitor.pm line 326.
Thu Nov 21 15:33:16 2018 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln523] Error happened on monitoring servers.
Thu Nov 21 15:33:16 2018 - [info] Got exit code 1 (Not master dead).

MySQL Replication Health is NOT OK!

Analysis: After MHA drift, we know that the information of the master node in the configuration information is no longer there. We need to maintain it in time, otherwise /usr/local/bin/masterha_check_repl --conf=/etc/mha/XXX.cnf will report an error when checking the replica set status.

2. masterha_master_switch Online switching error We should not start online master switch when one of connections are running long updates on the current master

The specific information is as follows:

# /usr/local/bin/masterha_master_switch --master_state=alive --conf=/etc/mha/app1.cnf

It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 179.179.19.184(179.179.19.184:3306)? (YES/no): y

Tue Nov 19 17:19:09 2018 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time..
Tue Nov 19 17:19:09 2018 - [info] ok.
Tue Nov 19 17:19:09 2018 - [info] Checking MHA is not monitoring or doing failover..
Tue Nov 19 17:19:09 2018 - [info] Checking replication health on 179.179.19.185..
Tue Nov 19 17:19:09 2018 - [info] ok.
Tue Nov 19 17:19:09 2018 - [error][/usr/local/share/perl5/MHA/MasterRotate.pm, ln161] We should not start online master switch when one of connections are running long updates on the current master(179.179.19.184(179.179.19.184:3306)). Currently 1 update thread(s) are running.
Details:
{'Time' => '12815','db' => undef,'Id' => '1','User' => 'event_scheduler','State' => 'Waiting on empty queue','Command' => 'Daemon','Info' => undef,'Host' => 'localhost'}
Tue Nov 19 17:19:09 2018 - [error][/usr/local/share/perl5/MHA/ManagerUtil.pm, ln177] Got ERROR: at /usr/local/bin/masterha_master_switch line 53.

Analysis: set global event_scheduler=off; both the master and slave should be closed

3. masterha_master_switch Online switch error Got Error: DBI .....failed: Access denied for user

# /usr/local/bin/masterha_master_switch --master_state=alive --conf=/etc/mha/app1.cnf

Starting master switch from 179.179.19.185(179.179.19:3306) to 179.179.19.184(179.179.19.184:3306)? (yes/NO): yes

Tue Nov 19 18:52:04 2018 - [info] Checking whether 179.179.19.184(179.179.19.184:3306) is ok for the new master..
Tue Nov 19 18:52:04 2018 - [info] ok.
Tue Nov 19 18:52:04 2018 - [info] ** Phase 1: Configuration Check Phase completed.
Tue Nov 19 18:52:04 2018 - [info] 
Tue Nov 19 18:52:04 2018 - [info] * Phase 2: Rejecting updates Phase..
Tue Nov 19 18:52:04 2018 - [info] 
Tue Nov 19 18:52:04 2018 - [info] Executing master ip online change script to disable write on the current master:
Tue Nov 19 18:52:04 2018 - [info] /usr/local/bin/master_ip_online_change_appuanalysis --command=stop --orig_master_host=179.179.19.185 --orig_master_ip=179.179.19.185 --orig_master_port=3306--orig_master_user='weixinLX391P_xldbmha' --orig_master_password='weixinLX391P_xldbmha\)qlk' --new_master_host=179.179.19.184 --new_master_ip=179.179.19.184 --new_master_port=55988 --new_master_user='us_mha' --new_master_password='weixinLX391P_xldbmha\)qlk' --orig_master_ssh_user=root --new_master_ssh_user=root 
Got Error: DBI connect(';host=179.179.19.184;port=3306;mysql_connect_timeout=4','weixinLX391P_xldbmha',...) failed: Access denied for user 'weixinLX391P_xldbmha'@'179.179.19.166' (using password: YES) at /usr/local/share/perl5/MHA/DBHelper.pm line 205.
 at /usr/local/bin/master_ip_online_change_app1 line 119.

Tue Nov 19 18:52:04 2018 - [error][/usr/local/share/perl5/MHA/ManagerUtil.pm, ln177] Got ERROR: at /usr/local/bin/masterha_master_switch line 53.

Analysis: Some characters in the account and password need to be transferred. The password corresponding to the user account in the app1.cnf file cannot have characters to be escaped, such as ')' in this example, but the password corresponding to the repl_user account repl_password does not have this restriction.

4. If you are using xtrabackup, please note that the slave node will restore the event, which may cause data inconsistency and synchronization failure.

If the master node has an event, you need to manually close the event of the slave node. For example, if the master node has an event to archive and delete data, the slave node needs to be shut down, otherwise an error will be reported. Errors similar to the following:

Could not execute Delete_rows event on table ????DB.*****table; Can't find record in '*****', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log FIRST, end_log_pos XXXXXXX

5. After the GTID mode is converted to the traditional mode, the database master-slave check under the MHA mechanism reports an error.

Check the command:

/usr/local/bin/masterha_check_repl --conf=/etc/mha/qqweixinordb.cnf

Main error message

Can't exec "mysqlbinlog": No such file or directory at /usr/local/share/perl5/MHA/BinlogManager.pm line 106.
mysqlbinlog version command failed with rc 1:0, please verify PATH, LD_LIBRARY_PATH, and client options
 at /usr/local/bin/apply_diff_relay_logs line 493.
Fri Aug 28 04:38:22 2019 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln205] Slaves settings check failed!
Fri Aug 28 04:38:22 2019 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln413] Slave configuration failed.
Fri Aug 28 04:38:22 2019 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln424] Error happened on checking configurations. at /usr/local/bin/masterha_check_repl line 48.
Fri Aug 28 04:38:22 2019 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln523] Error happened on monitoring servers.
Fri Aug 28 04:38:22 2019 - [info] Got exit code 1 (Not master dead).

 MySQL Replication Health is NOT OK!

Solution - Execute the following command on each DB node

ln -s /usr/local/mysql/bin/mysqlbinlog /usr/local/bin/mysqlbinlog

 ln -s /usr/local/mysql/bin/mysql /usr/local/bin/mysql

Check again, the error message disappears, OK.

6. The root account password has expired

When the root account is used to log in without password through ssh, and the ssh has an expiration limit, the mha ssh check will report an error:

/usr/local/bin/masterha_check_ssh --conf=/etc/mha/qqorder.cnf
Thu Nov 5 10:09:09 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Nov 5 10:09:09 2018 - [info] Reading application default configuration from /etc/mha/pms20epime.cnf..
Thu Nov 5 10:09:09 2018 - [info] Reading server configuration from /etc/mha/pms20epime.cnf..
Thu Nov 5 10:09:09 2018 - [info] Starting SSH connection tests..
Thu Nov 5 10:09:09 2018 - [error][/usr/local/share/perl5/MHA/SSHCheck.pm, ln63]
Thu Nov 5 10:09:09 2018 - [debug] Connecting via SSH from [email protected](172.181.191.191:22) to [email protected](172.181.191.192:22)..

WARNING: Your password has expired.
Password change required but no TTY available.
Thu Nov 5 10:09:09 2018 - [error][/usr/local/share/perl5/MHA/SSHCheck.pm, ln111] SSH connection from [email protected](172.181.191.191:22) to [email protected](172.181.191.192:22) failed!
Thu Nov 5 10:09:10 2018 - [error][/usr/local/share/perl5/MHA/SSHCheck.pm, ln63]
Thu Nov 5 10:09:09 2018 - [debug] Connecting via SSH from [email protected](172.181.191.192:22) to [email protected](172.181.191.191:22)..

WARNING: Your password has expired.
Password change required but no TTY available.
Thu Nov 5 10:09:10 2018 - [error][/usr/local/share/perl5/MHA/SSHCheck.pm, ln111] SSH connection from [email protected](172.181.191.192:22) to [email protected](172.181.191.191:22) failed!
SSH Configuration Check Failed!
 at /usr/local/bin/masterha_check_ssh line 44.

Another manifestation is that an error message appears when you execute the account switch for the second time: sudo su -

Solution: Under the root account, execute the following command:

chage -M 99999 root

The above is the detailed content of several error logs about MySQL MHA construction and switching. For more information about MySQL MHA construction and switching, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:

How to use Python to collect MySQL MHA deployment and operation status information
A complete explanation of MySQL high availability architecture: MHA architecture
Detailed deployment steps for MySQL MHA high availability configuration and failover
Steps to build MHA architecture deployment in MySQL
Mysql GTID Mha configuration method
Super deployment tutorial of MHA high availability failover solution under MySQL
MHA implements manual switching of MySQL master-slave database
Introduction to MySQL MHA operation status monitoring

<<: Native JS to achieve digital table special effects

>>: Markup language - web application CSS style

Detailed explanation of various types of image formats such as JPG, GIF and PNG

Summary of several error logs about MySQL MHA setup and switching

1: masterha_check_repl replica set error replicates is not defined in the configuration file!

2. masterha_master_switch Online switching error We should not start online master switch when one of connections are running long updates on the current master

3. masterha_master_switch Online switch error Got Error: DBI .....failed: Access denied for user

4. If you are using xtrabackup, please note that the slave node will restore the event, which may cause data inconsistency and synchronization failure.

5. After the GTID mode is converted to the traditional mode, the database master-slave check under the MHA mechanism reports an error.

6. The root account password has expired

Detailed explanation of various types of image formats such as JPG, GIF and PNG

The 6 Most Effective Ways to Write HTML and CSS

TABLE tags (TAGS) detailed introduction

A simple way to put HTML footer at the bottom of the page

5 MySQL GUI tools recommended to help you with database management

Enable sshd operation in docker

Vue+Echart bar chart realizes epidemic data statistics

Docker's health detection mechanism

Do you know the common MySQL design errors?

How to install multiple mysql5.7.19 (tar.gz) files under Linux

Recommend

The role of nextTick in Vue and several simple usage scenarios

Get the calculated style in the CSS element (after cascading/final style)

Docker port mapping and external inaccessibility issues

Detailed explanation of fs module and Path module methods in Node.js

What are the core modules of node.js

Installation and use of mysql on Ubuntu (general version)

MySQL primary key naming strategy related

Modularity in Node.js, npm package manager explained

Detailed explanation of the wonderful uses of SUID, SGID and SBIT in Linux

A brief discussion on two methods to solve space-evenly compatibility issues

Detailed explanation of how to modify the style of el-select: popper-append-to-body and popper-class

CSS uses radial-gradient to implement coupon styles

Recommend 60 paging cases and good practices

Example of viewing and modifying MySQL transaction isolation level

Drop-down menu and sliding menu design examples