MySQL MHA 安装方法
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权限
cant 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
MySQL高可用之MHA集群(下) 一、MHA概述 1.1 什么是 MHA MHA(MasterHigh Availability)是一套优秀的MySQL高可用环境下故障切换和主从复制的软件。 MHA 的出现就是解决MySQL 单点故障的问题。
MySQL高可用之MHA集群(中) 一、MHA概述 1.1 什么是 MHA MHA(MasterHigh Availability)是一套优秀的MySQL高可用环境下故障切换和主从复制的软件。 MHA 的出现就是解决MySQL 单点故障的问题。
MySQL高可用之MHA集群(上) 一、MHA概述 1.1 什么是 MHA MHA(MasterHigh Availability)是一套优秀的MySQL高可用环境下故障切换和主从复制的软件。 MHA 的出现就是解决MySQL 单点故障的问题。
相关文章
- MySQL安装常见报错处理方法总结大全
- 【排障】编译安装Mysql并使用自启动脚本mysqld后报错详解数据库
- MySQL中判断字段是否为空的方法(mysql判断字段是否为空)
- MySQL中如何处理空值(mysql空值判断)
- MySQL数据库备份的途径与方法(如何备份mysql数据库)
- MySQL:更新数据库的简单方法(mysql替换内容)
- MySQL 主从切换:实现容错备份(mysql主主切换)
- 的操作MySQL中操作二进制数据的简单方法(mysql二进制数据)
- MySQL表如何添加到数据库表中(mysql表添加数据库表)
- MySQL:深入理解页结构(mysql页结构)
- MySQL数据库中的性别类型探索(mysql性别类型)
- 如何解决 MySQL 无法启动的问题(mysql启动不了)
- MySQL中实现字段联合的简易方法(mysql 字段连接)
- 拥抱MySQL正版,安全享受智慧生活(mysql 正版)
- MySQL SQL优化,提高查询效率。(mysql中的sql优化)
- 微信应用开发:MySQL数据库的管理(微信 开发 mysql)
- 读取MySQL数据库:MSSQL的实现方法(mssql读取mysql)
- MySQL:批量修改字段值的一种新方法(mysql批量修改字段值)
- MySQL数据库两表主外键关联的完整实现方法(mysql两表主外键关联)
- 用CMD精准查找MySQL安装位置(cmd 找到mysql)
- 7zip压缩的MySQL数据库极致效率的安装运行(5.7zip版mysql)
- MySQL 修改忘记的密码实现方法(5.5mysql忘记密码)
- 妙用CMD命令迅速调出MySQL(cmd命令调出mysql)
- MySQL中如何使用序号进行排序和查询(mysql中加序号)
- MySQL中的分区高效管理海量数据(mysql中分区)
- MySQL函数e的使用方法及实例解析(mysql中函数e)
- 格与使用方法详解MySQL内置表的使用方法(mysql中内置表表)
- MySQL链接异常,问题求解(mysql一链接就停止)
- MySQL如何进行一对多导入(mysql一对多导入)
- 探索MySQL的复制架构一主最多可以拥有几从(mysql一主最多几从)
- MySQL服务器打不开,如何解决(mysql不能打开服务器)
- MySQL查询上一个月数据的方法(mysql 上一月)
- MySQL不支持数组定义(mysql不能定义数组吗)
- MySQL端口未打开的问题解决方法(mysql不打开端口)
- MySQL安装下载安装包详细步骤(mysql 下载安装包)