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权限

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

时间： 2024-11-03 13:08:11

MySQL MHA 安装方法的相关文章

MySQL手动安装方法与中文解决方案

    花了1天,在Debian Etch Linux上成功安装了MySQL 5.0.27 icc版本,并使用JDBC测试中文成功! 中文问题的关键是全部使用UTF-8编码,MySQL 5发布版支持UTF-8,但默认为latin1,Java在内部使用的全部是Unicode,因此要保证JSP页面以UTF-8编码,JDBC驱动采用官方Connector4J 5.0.4,在Resin 3.1/Spring 2.0/Hibernate 3.2环境下测试中文正常. 1. 下载"mysql-standard

基于Docker的mysql mha 的集群环境构建实践

12月2日,云计算高级工程师王佩老师,在[DBA+社群]中间件用户组进行了一次主题为"基于Docker的mysql mha 的集群环境构建实践"的线上分享.小编特别整理出其中精华内容,供大家学习交流.同时,也非常感谢王佩老师对DBA+社群给予的大力支持.    嘉宾简介   云计算高级工程师 目前主要研究docker 相关的云计算技术   演讲实录  关于docker 想必前面的各位大牛分享的演讲讲的非常多了,今天主要跟大家分享基于docker快速构建 mysql mha 的一个实战案

MySQL事务数据库(InnoDB类型)的安装方法

mysql|数据|数据库 MySQL数据库分二种类型,一种是传统的数据表格式,一种是支持事务处理的数据表格式(InnoDB,BDB,其中以InnoDB为主),下面我介绍一下关于MySQL事务处理数据库的安装及使用方法你先要去下载一下Mysql max版的安装程序,下载地址:www.mysql.com按常规的方法进行安装安装完成后,启动mysql\bin\WinMySQLadmin再退出运行mysql\bin\mydqld-nt --removemysql\bin\mysqld-max-nt --

【Vegas原创】Mysql绿色版安装方法

原文:[Vegas原创]Mysql绿色版安装方法 所谓的绿色版,就是没有installer的MySQL,完全需要靠人工来操作,好处是,重装系统后,只要再做一次本次配置,即可使用. 具体操作方法: 1,设置系统环境变量, 在Path中添加 D:\mysql\bin 2,修改my-small.ini文件内容,添加以下内容 basedir=D:\mysql datadir=D:\mysql\data 修改后,另存为my.ini     3.安装MySQL的服务,服务名自己定义为MySQL. 1).进入

centos6中 rpm安装mysql出错问题解决方法

[root@cch rpm]# rpm -ivh MySQL-server-5.5.29-1.rhel5.x86_64.rpm error: Failed dependencies:libaio.so.1()(64bit) is needed by MySQL-server-5.5.29-1.rhel5.x86_64 libaio.so.1(LIBAIO_0.1)(64bit) is needed by MySQL-server-5.5.29-1.rhel5.x86_64 libaio.so.1

Mysql的基础使用之MariaDB安装方法详解_Mysql

我首次用mysql是在ubuntu上,现在用的是linux 中的Red Hat 分支的centOS 7 ,安装时发现通常用的都是MariaDB 来代替mysql,通过资料查询发现Mariadb是mysql的其中的一种分支,由mysql的创始人带领的团队所开发的mysql分支的一种版本,因为mysql受到被Oracle收购后的日渐封闭与缓慢的更新,众多Linux发行版逐渐抛弃了这个人气开源数据库,使MySQL在各大Linux发行版中的失势由于不满MySQL被Oracle收购后的日渐封闭与缓慢的更新

MySQL 5.6.14 win32安装方法(zip版)_Mysql

1 从http://dev.mysql.com/downloads/mysql/ 下载MySQL Community Server的Windows 平台mysql-5.6.14-win32.zip压缩包 2 将文件解压至F:\mysql-5.6.14-win32 3 切换至目录F:\mysql-5.6.14-win32,将文件my-default.ini复制一份重命名为my.ini放到当前目录下 4 打开my.ini进行MySQL配置,主要设置以下几项: [mysqld] #这一句解决有IPV6

RHEL 6平台MySQL数据库服务器的安装方法_Mysql

MySQL数据库是Linux操作系统上用得最多的数据库系统,它可以非常方便的与其它服务器集成在一起,如Apache.Vsftpd.Postfix等.下面介绍RHEL 6平台MySQL数据库服务器的安装方法. 1.安装完整的MySQL数据库需要以下几个RPM包文件: perl-DBI-1.609-4.e16.i686.rpm:Perl语言的数据API perl-DBD-MySQL-4.013-3.e16.i686.rpm:MySQL与Perl语言的接口程序包 mysql-5.1.61-4.e16.

mysql免安装版的实际配置方法_Mysql

以下的文章主要向大家讲述的是MySQL 免安装版的实际配置方法,其免安装版本可在相关网站下载,下载之后可将其解压到c:\MySQL获取他任意位置,解压后在目录下,找到my-huge.ini,my-large.ini,my-medium.ini配置文件. 打开进行配置: 复制代码 代码如下: [MySQLd] # set basedir to your installation path basedir=c:/MySQL # set datadir to the location of your