MySQL MHA configuration FAQ

MySQL MHA configuration FAQ

MHA is widely used in MySQL databases. It is compact, easy to use, and powerful. It enables manual master-slave failover Based on the MySQL replication architecture, redirects from the database to the master database, and automatically synchronizes data. However, during the deployment and configuration process, it is difficult to avoid one or another errors due to negligence. This article is a summary of common problems in MHA configuration for your reference.

1. Non-root user equivalence Configuration
A. Add the host names and IP addresses of all nodes (including management nodes) to the host file and perform operations on all nodes.
B. Generate a symmetric key based on a non-root user (such as a mysql account) and use ssh-keygen
C. Copy the public key to other nodes (including management nodes) as follows and use non-default ssh ports
Ssh-copy-id-I ~ /. Ssh/id_rsa.pub "-p 50011 [email protected]"
D. Verify the equivalence (enter the password for the first time)
Ssh-p 50011 vdbsrv1 date; ssh-p 50011 vdbsrv2 date; ssh-p 50011 vdbsrv3 date; ssh-p 55555 vdbsrv4 date

 

2. MHA management node directory permission issues
### If a non-root user is used in the Management node for mha management, related permissions are required; otherwise, an error is returned.
### If you use mysql to manage mha, create a directory in the root account and grant permissions to mysql
# Mkdir-p/var/log/masterha/app1
# Chown-R mysql: mysql/var/log/masterha
# Su-mysql
$ Masterha_check_repl -- conf =/etc/app1.cnf ### use the mysql user to verify the replication relationship

 

3. You need to add an account to log on to the mysql server.
### You Need To stop slave, change master, reset slave, and other related permissions. This account must be added to the mha configuration file and used for MASTER-SLAVE switching.
### Run the command on each node of the mysql database. The following table lists the cross-network segments, so the cross-network segment account is assigned. Otherwise, you can grant permissions to one node.
### Author: Leshami
### Blog: www.bkjia.com
Mysql> grant all privileges on *. * to 'mha '@ '2017. 172. %' identified '***';
Query OK, 0 rows affected (0.00 sec)

Mysql> grant all privileges on *. * to 'mha '@ '2017. 192. %' identified '***';
Query OK, 0 rows affected (0.00 sec)

 

4. You need to add an account for establishing a replication relationship.
### Run the command on each node of the mysql database. The following table lists the cross-network segments, so the cross-network segment account is assigned. Otherwise, you can grant permissions to one node.
Mysql> grant replication slave on *. * to 'repl' @ '2017. 172.% 'identified '***';
Query OK, 0 rows affected (0.00 sec)

Mysql> grant replication slave on *. * to 'repl' @ '2017. 192.% 'identified '***';
Query OK, 0 rows affected (0.00 sec)

 

5. binlog should be enabled for each client in the slave database, that is, log_bin = on
### If not enabled, the following message is displayed:
Mon Apr 13 20:02:15 2015-[warning] log-bin is not set on slave SZ-DB-SLAVE01 (192.168.81.3: 3306 ).
This host cannot be a master.

 

6. Master/Slave databases should use the same replication filtering rules.
### Otherwise, the following error message is returned:
Mon Apr 13 20:02:15 2015-[error] [/usr/lib/perl5/site_perl/5.8.8/MHA/ServerManager. pm, ln546] Replication
Filtering check failed on dbsrv3 (192.168.1.3: 3306 )! All slaves must have same replication filtering rules.
Check show slave status output and set my. cnf correctly.
Mon Apr 13 20:02:15 2015-[warning] Bad Binlog/Replication filtering rules:

 

7. relay_log_purge = 0 should be set for each slave Database
### Otherwise, the following warning message is received ## mysql-e 'set global relay_log_purge = 0'. this parameter is dynamically modified because the slave will be upgraded to the master at any time. Add @ 150420
Mon Apr 13 20:02:15 2015-[warning] relay_log_purge = 0 is not set on slave vdbsrv2 (172.16.16.12: 3306 ).
 

8. Set read_only = 1 for each slave Database
### Otherwise, the following warning message is received ## mysql-e 'set global read_only = 1'. this parameter is dynamically modified because the slave will be upgraded to the master at any time. Add @ 150420
Mon Apr 13 20:19:54 2015-[info] read_only = 1 is not set on slave vdbsrv3 (172.16.16.13: 3306 ).

 

9. mysqlbinlog version is too low
### In most cases, we use the yum method to install perl-DBD-MySQL. This method will automatically install the mysql rpm package.
### MHA will call/usr/bin/mysqlbinlog when master_check_repl is executed. For mysql high versions whose source code is installed in different paths
### The following error message is returned:
Thu Apr 16 14:01:44 2015-[info] Connecting to [email protected] (vdbsrv2: 22 )..
Mysqlbinlog version is 3.2 (pinned ded in MySQL Client 5.0 or lower). This is not recommended.
Consider upgrading MySQL Client to 5.1 or higher.

### Solution: Specify the MySQL installation location when compiling and installing perl-DBD-mysql, or refer to: mysqlbinlog can not parse row based events
 

10. If the mysql client does not exist in the default path, the following prompt is displayed:
### MySQL is automatically installed by creating a soft link or yum install perl-DBD-mysql
Testing mysql connection and privileges .. sh: mysql: command not found
Mysql command failed with rc :0!
At/usr/bin/apply_diff_relay_logs line 375

This article permanently updates the link address: