MySQL:MHA安装配置

清疚 2022-07-19 02:14 219阅读 0赞

MHA结构如下  
IP MySQL  MHA  
192.168.100.42 主 node  
192.168.100.241 从(备主) master

192.168.100.121从 node

1：三台服务器配互信

三台服务器执行ssh-keygen -t rsa
    把三台服务器的~/.ssh/id_rsa.pub全部追加进~/.ssh/authorized_keys然后分发
    cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys

1：ssh-keygen -t rsa
    生成密钥
    2：ssh-copy-id -i ~/.ssh/id_rsa.pub jifeng@IP
    把本机的公钥追到IP的 .ssh/authorized_keys 里  
    3：如果ssh的端口不是22，可用下面命令
    ssh-copy-id -i ~/.ssh/id_rsa.pub "-p 23 jifeng@jifeng03"

2：安装依赖

yum install perl-DBD-MySQL 
    yum install perl-Config-Tiny 
    yum install perl-Log-Dispatch 
    yum install perl-Parallel-ForkManager

3：安装node及master

tar -zxf mha4mysql-node-X.Y.tar.gz
    perl Makefile.PL
    make
    sudo make install
    (需安装node在安装master)
    tar -zxf mha4mysql-master-X.Y.tar.gz
    perl Makefile.PL
    make
    sudo make install

4：修改配置文件

[root@dbo_100_241 mha]# cat app1.cnf 
    [server default]
    user=root
    password=Qe091cs74
    ssh_user=root
    repl_user=repl
    repl_password=repl
    ping_interval=1
    
    manager_workdir=/etc/mha/data
    manager_log=/etc/mha/data/app1.log
    remote_workdir=/etc/mha/data
    
    master_ip_failover_script=/etc/mha/scripts/master_ip_failover
    master_ip_online_change_script=/etc/mha/scripts/master_ip_online_change
    #shutdown_script=/usr/bin/power_manager
    report_script =/etc/mha/scripts/send_report
    secondary_check_script= /usr/local/bin/masterha_secondary_check -s dbo_100_241 -s db121 -s db42 --user=root --master_host=db42 --master_ip=192.168.100.42 --master_port=3307   
    
    [server1]
    hostname=db42
    master_binlog_dir=/oracle/mysqllog/mysql3307
    candidate_master=1
    check_repl_delay=0
    port=3307
    
    [server2]
    hostname=dbo_100_241 
    master_binlog_dir=/oracle/mysqllog/mysql3307
    candidate_master=1
    check_repl_delay=0
    port=3307
    
    [server3]
    hostname=db121
    master_binlog_dir=/oracle/mysqllog/mysql3307
    #candidate_master=1
    port=3307
    ignore_fail=1
    no_master=1

5：开启前准备测试

[root@dbo_100_241 mha]# masterha_check_ssh -conf=./app1.cnf
    [root@dbo_100_241 mha]# masterha_check_repl -conf=./app1.cnf

6：启动MHA

第5步检查都OK后，开始MHA
    masterha_manager -conf=/etc/mha/app1.cnf --ignore_last_failover > /tmp/app.log 2>&1 &
    MHA切换命令
    masterha_master_switch --conf=/etc/mha/app1.cnf --master_state=alive --new_master_host=db42 --new_master_port=3307 --orig_master_is_new_slave --running_updates_limit=10000 --interactive=0
    
    相关：
    masterha_check_ssh              检查MHA的SSH配置状况
    masterha_check_repl             检查MySQL复制状况
    masterha_manger                 启动MHA
    masterha_check_status           检测当前MHA运行状态
    masterha_master_monitor         检测master是否宕机
    masterha_master_switch          控制故障转移（自动或者手动）
    masterha_conf_host              添加或删除配置的server信息

7：删除relay\_log脚本

定时删除relay_log脚本，可放在crontab定时执行
    [root@dbo_100_241 scripts]# cat purge_relay_log.sh 
    #!/bin/bash
    user=root
    passwd=Qe091cs74
    port=3307
    log_dir='/etc/mha/data'
    work_dir='/etc/mha/data'
    purge='/usr/local/bin/purge_relay_logs'
    
    if [ ! -d $log_dir ]
    then
       mkdir $log_dir -p
    fi
    
    $purge --user=$user --password=$passwd --disable_relay_log_purge --port=$port --workdir=$work_dir >> $log_dir/purge_relay_logs.log 2>&1

问题1：

[root@dbo_100_241 mha]# masterha_check_repl --conf=./app1.cnf          
    Tue Feb 23 11:55:55 2016 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
    Tue Feb 23 11:55:55 2016 - [info] Reading application default configuration from ./app1.cnf..
    Tue Feb 23 11:55:55 2016 - [info] Reading server configuration from ./app1.cnf..
    Tue Feb 23 11:55:55 2016 - [info] MHA::MasterMonitor version 0.57.
    Tue Feb 23 11:55:55 2016 - [error][/usr/local/share/perl5/MHA/ServerManager.pm, ln671] Master 172.16.10.45:3306 from which slave db42(192.168.100.42:3307) replicates is not defined in the configuration file!
    Tue Feb 23 11:55:55 2016 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations.  at /usr/local/share/perl5/MHA/MasterMonitor.pm line 329
    Tue Feb 23 11:55:55 2016 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.
    Tue Feb 23 11:55:55 2016 - [info] Got exit code 1 (Not master dead).
    
    MySQL Replication Health is NOT OK!
    
    解决办法：
    确保主库没有slave信息，即
    (root@127.0.0.1) [(none)]> show slave status\G
    Empty set (0.00 sec)

问题2：

Tue Feb 23 13:47:18 2016 - [info]  read_only=1 is not set on slave dbo_100_241(192.168.100.241:3307).
    Tue Feb 23 13:47:18 2016 - [warning]  relay_log_purge=0 is not set on slave dbo_100_241(192.168.100.241:3307).
    Tue Feb 23 13:47:18 2016 - [info]  read_only=1 is not set on slave db121(192.168.100.121:3307).
    Tue Feb 23 13:47:18 2016 - [warning]  relay_log_purge=0 is not set on slave db121(192.168.100.121:3307).
    
    解决办法：
    备库执行
    set global read_only=1;
    set global relay_log_purge=0;

问题3：

[root@dbo_100_241 mha]# masterha_check_repl -conf=./app1.cnf
    Tue Feb 23 13:43:35 2016 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln208] Slaves settings check failed!
    Tue Feb 23 13:43:35 2016 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln416] Slave configuration failed.
    Tue Feb 23 13:43:35 2016 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations.  at /usr/local/bin/masterha_check_repl line 48
    Tue Feb 23 13:43:35 2016 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.
    
    解决办法：
    ln -s /usr/local/mysql55/bin/mysqlbinlog /usr/bin/mysqlbinlog
    ln -s /usr/local/mysql55/bin/mysql /usr/bin/mysql

参考：

MHA下载
    https://code.google.com/p/mysql-master-ha/
    epel下载
    https://fedoraproject.org/wiki/EPEL
    MHA参考资料
    http://www.tuicool.com/articles/EBjmy2
    http://www.cnblogs.com/gomysql/p/3675429.html
    http://www.cnblogs.com/kissdb/p/4009620.html
    
    mha切换资料
    http://www.slideshare.net/matsunobu/automated-master-failover/17

参数说明参考：
    http://wubx.net/mha-parameters/
    http://isadba.com/upload/mha_Parameters.htm