MySQL MHA 安装方法

2022-02-03 04:50:23

1.先在各个节点安装node，包括mysql master及mysql slave及MHA Master

https://code.google.com/p/mysql-master-ha/wiki/Installation#Installation

Node安装：

apt-get install libdbd-mysql-perl

tar -zxf mha4mysql-node-X.Y.tar.gz

perl Makefile.PL

make

sudo make install

Manager安装：

在mha master安装manager

apt-get install libdbd-mysql-perl

apt-get install libconfig-tiny-perl

apt-get install liblog-dispatch-perl

apt-get install libparallel-forkmanager-perl

tar -zxf mha4mysql-manager-X.Y.tar.gz

perl Makefile.PL

make

sudo make install

安装完毕，检查ssh互联时出错

root@mysql-test3:~# masterha_check_ssh --conf=/etc/app1.cnf
Thu Jan 21 09:53:24 2016 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Jan 21 09:53:24 2016 - [info] Reading application default configuration from /etc/app1.cnf..
Thu Jan 21 09:53:24 2016 - [info] Reading server configuration from /etc/app1.cnf..
Thu Jan 21 09:53:24 2016 - [info] Starting SSH connection tests..
Thu Jan 21 09:53:24 2016 - [error][/usr/local/share/perl/5.14.2/MHA/SSHCheck.pm, ln63] 
Thu Jan 21 09:53:24 2016 - [debug]  Connecting via SSH from root@mysql-test(192.168.6.197:22) to root@mysql-test2(192.168.6.130:22)..
Permission denied (publickey,password).
Thu Jan 21 09:53:24 2016 - [error][/usr/local/share/perl/5.14.2/MHA/SSHCheck.pm, ln111] SSH connection from root@mysql-test(192.168.6.197:22) to root@mysql-test2(192.168.6.130:22) failed!
Thu Jan 21 09:53:25 2016 - [error][/usr/local/share/perl/5.14.2/MHA/SSHCheck.pm, ln63] 
Thu Jan 21 09:53:24 2016 - [debug]  Connecting via SSH from root@mysql-test2(192.168.6.130:22) to root@mysql-test(192.168.6.197:22)..
Permission denied (publickey,password).
Thu Jan 21 09:53:25 2016 - [error][/usr/local/share/perl/5.14.2/MHA/SSHCheck.pm, ln111] SSH connection from root@mysql-test2(192.168.6.130:22) to root@mysql-test(192.168.6.197:22) failed!
Thu Jan 21 09:53:25 2016 - [error][/usr/local/share/perl/5.14.2/MHA/SSHCheck.pm, ln63] 
Thu Jan 21 09:53:25 2016 - [debug]  Connecting via SSH from root@mysql-test3(127.0.1.1:22) to root@mysql-test(192.168.6.197:22)..
Permission denied (publickey,password).
Thu Jan 21 09:53:25 2016 - [error][/usr/local/share/perl/5.14.2/MHA/SSHCheck.pm, ln111] SSH connection from root@mysql-test3(127.0.1.1:22) to root@mysql-test(192.168.6.197:22) failed!
SSH Configuration Check Failed!
原因分析，程序需要从manage管理ssh连接，所以会从mysql-test3 ssh到 mysql-test 再ssh到 mysql-test2，问题出在第二次连接，需要输入key的密码，导致测试失败。
root@mysql-test3:~# ssh root@mysql-test
Linux mysql-test 3.2.0-4-amd64 #1 SMP Debian 3.2.73-2+deb7u2 x86_64

The programs included with the Debian GNU/Linux system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.

Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.
Last login: Thu Jan 21 09:45:29 2016 from mysql-test3
root@mysql-test:~# ssh root@mysql-test2
Enter passphrase for key '/root/.ssh/id_rsa':

解决方法，取消key密码

root@mysql-test3:~# masterha_check_repl --conf=/etc/app1.cnf
Thu Jan 21 10:54:38 2016 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Jan 21 10:54:38 2016 - [info] Reading application default configuration from /etc/app1.cnf..
Thu Jan 21 10:54:38 2016 - [info] Reading server configuration from /etc/app1.cnf..
Thu Jan 21 10:54:38 2016 - [info] MHA::MasterMonitor version 0.57.
Thu Jan 21 10:54:39 2016 - [error][/usr/local/share/perl/5.14.2/MHA/Server.pm, ln265] Checking slave status failed on mysql-test(192.168.6.197:3306). err=Got error when executing SHOW SLAVE STATUS. Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation
Thu Jan 21 10:54:39 2016 - [error][/usr/local/share/perl/5.14.2/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations.  at /usr/local/share/perl/5.14.2/MHA/ServerManager.pm line 315
Thu Jan 21 10:54:39 2016 - [error][/usr/local/share/perl/5.14.2/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.
Thu Jan 21 10:54:39 2016 - [info] Got exit code 1 (Not master dead).

MySQL Replication Health is NOT OK!

复制检测还需要 SUPER, REPLICATION CLIENT 权限

Thu Jan 21 10:58:32 2016 - [error][/usr/local/share/perl/5.14.2/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations. Got MySQL error when checking replication privilege. 1142: SELECT command denied to user 'repl'@'mysql-test3' for table 'user' query:SELECT Repl_slave_priv AS Value FROM mysql.user WHERE user = ?
 at /usr/local/share/perl/5.14.2/MHA/Server.pm line 397

需要SELECT权限

can't exec "mysqlbinlog": No such file or directory at /usr/local/share/perl/5.14.2/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
Thu Jan 21 12:46:41 2016 - [error][/usr/local/share/perl/5.14.2/MHA/MasterMonitor.pm, ln208] Slaves settings check failed!
Thu Jan 21 12:46:41 2016 - [error][/usr/local/share/perl/5.14.2/MHA/MasterMonitor.pm, ln416] Slave configuration failed.
Thu Jan 21 12:46:41 2016 - [error][/usr/local/share/perl/5.14.2/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations.  at /usr/local/bin/masterha_check_repl line 48
Thu Jan 21 12:46:41 2016 - [error][/usr/local/share/perl/5.14.2/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.
Thu Jan 21 12:46:41 2016 - [info] Got exit code 1 (Not master dead)

需要使用mysqlbinlog命令

vim /etc/profile 适当的位置增加

export PATH=$PATH:/usr/local/mysql/bin

执行source /etc/profile，重新load profile

之后还是无法成功，手动执行如下命令

apply_diff_relay_logs --command=test --slave_user='repl' --slave_host=mysql-test2 --slave_ip=192.168.6.130 --slave_port=3306 --workdir=/var/log/masterha/app1 --target_version=5.6.23-log --manager_version=0.57 --relay_log_info=/vobiledata/mysqldata/relay-log.info  --relay_dir=/vobiledata/mysqldata/  --slave_pass=xxx

结果显示需要CREATE INSERT UPDATE DELETE DROP权限

再尝试，还是不成功

必须要将mysqlbinlog放在/usr/bin目录下

ln -s `which mysqlbinlog` /usr/bin/mysqlbinlog

到此，安装初步测试完毕，待开启整套程序

使用power_manager需要安装libnet-telnet-perl依赖库

关闭relay_log_purge，交由mha负责处理

user需要有STOP SLAVE, CHANGE MASTER, RESET SLAVE权限

repl_user需要有REPLICATION SLAVE权限

恢复成功后必须要将$manager_workdir/failover.complete文件删除，否则下次恢复会不成功，报错

MHA最好配合MySQL半同步复制，以保证切换时数据不丢失

mysql5.5开始支持半同步复制，开启方法如下

master配置

mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
mysql> show variables like '%semi%';
    +------------------------------------+-------+
    | Variable_name                      | Value |
    +------------------------------------+-------+
    | rpl_semi_sync_master_enabled       | OFF   |
    | rpl_semi_sync_master_timeout       | 10000 |
    | rpl_semi_sync_master_trace_level   | 32    |
    | rpl_semi_sync_master_wait_no_slave | ON    |
    +------------------------------------+-------+
mysql> set global rpl_semi_sync_master_enabled=1;
mysql> set global rpl_semi_sync_master_timeout=1000;

slave配置

mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
    mysql> show variables like '%semi%';
    +---------------------------------+-------+
    | Variable_name                   | Value |
    +---------------------------------+-------+
    | rpl_semi_sync_slave_enabled     | OFF   |
    | rpl_semi_sync_slave_trace_level | 32    |
    +---------------------------------+-------+
mysql> set global rpl_semi_sync_slave_enabled=1;

可以将配置写入my.cnf [mysqld]以永久化

Manager下载地址：https://72003f4c60f5cc941cd1c7d448fc3c99e0aebaa8.googledrive.com/host/0B1lu97m8-haWeHdGWXp0YVVUSlk/mha4mysql-manager-0.57.tar.gz

Node下载地址：https://72003f4c60f5cc941cd1c7d448fc3c99e0aebaa8.googledrive.com/host/0B1lu97m8-haWeHdGWXp0YVVUSlk/mha4mysql-node-0.57.tar.gz

码农公寓

相关文章