Summary of several error logs about MySQL MHA setup and switching

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

Recommend

Use js in html to get the local system time

Copy code The code is as follows: <div id=&quo...

Example of how to build a Mysql cluster with docker

Docker basic instructions: Update Packages yum -y...

Several navigation directions that will be popular in the future

<br />This is not only an era of information...

Web designers also need to learn web coding

Often, after a web design is completed, the desig...

Overview of MySQL Statistics

MySQL executes SQL through the process of SQL par...

Implementation of Nginx configuration and access to local static resources in Mac environment

Sometimes local development requires debugging of...

Detailed explanation of common Docker commands

1. Help Command 1. View the current Docker versio...

Optimizing the slow query of MySQL aggregate statistics data

Written in front When we operate the database in ...

Example of using MySQL to count the number of different values ​​in a column

Preface The requirement implemented in this artic...

jQuery realizes the scrolling effect of table row data

This article example shares the specific code of ...

How to create a MySQL database and support Chinese characters

Let's first look at the MySQL official docume...

Detailed process of using Vscode combined with docker for development

Preface Using Docker and VS Code can optimize the...

How to center your HTML button

How to center your HTML button itself? This is ea...

Detailed steps for configuring Tomcat server in IDEA 2020

The steps for configuring Tomcat in IDEA 2020 are...

html opens a new window with a hyperlink and can control window properties

1. The window size opened by the HTML hyperlink C...