MySQL MHA 安装方法

1.先在各个节点安装node,包括mysql mastermysql slaveMHA 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

  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
©️2021 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值