mysql高可用测试,MysqlMHA高可用实验测试

说明&＃xff1a; centos 6.5mysql 5.5.37 mha4mysql-manager-0.55 mha4mysql-node-0.54 manager 192.168.1.1 mha-manager管理端 db1 192.168.1.2 现有主库 db2 192.168.1.3 即将做双主的数据库 db3 192.168.1.4 现有从库 配置mysql主从 db1主库: server-id &＃61; 1 log-bi

说明&＃xff1a;

centos 6.5 mysql 5.5.37 mha4mysql-manager-0.55 mha4mysql-node-0.54

manager 192.168.1.1 mha-manager管理端

db1 192.168.1.2 现有主库

db2 192.168.1.3 即将做双主的数据库

db3 192.168.1.4 现有从库

配置mysql主从

db1主库:

server-id &＃61; 1log-bin&＃61;mysql-bin

binlog_format&＃61;mixed

db2主库:

server-id &＃61; 2log-bin&＃61;mysql-bin

binlog_format&＃61;mixed

relay_log_purge&＃61;0

db3从库:server_id &＃61; 3

log_bin &＃61; mysql-bin

relay_log &＃61; mysql-relay-bin

max_relay_log_size &＃61; 2G

log_slave_updates &＃61; 1read_only &＃61; 1

relay_log_purge&＃61;0

db1主 (现有的主库)grant replication slave on *.* to mharep&＃64;&＃39;192.168.1.%&＃39; identified by &＃39;passwd&＃39;;

flush privileges;

stop slave;

#设置复制权限帐号

GRANT ALL PRIVILEGES ON *.* TO &＃39;mha_manager&＃39;&＃64;&＃39;192.168.1.%&＃39; IDENTIFIED BY &＃39;123456&＃39;;

reset master;

show master status\G

db2备 (即将做双主的这台机)grant replication slave on *.* to mharep&＃64;&＃39;192.168.1.%&＃39; identified by &＃39;passwd&＃39;;

GRANT ALL PRIVILEGES ON *.* TO &＃39;mha_manager&＃39;&＃64;&＃39;192.168.1.%&＃39; IDENTIFIED BY &＃39;123456&＃39;;

flush privileges;

reset master;

slave stop;

change master to MASTER_HOST&＃61;&＃39;192.168.1.2&＃39;, MASTER_PORT&＃61;3306,MASTER_USER&＃61;&＃39;mharep&＃39;, MASTER_PASSWORD&＃61;&＃39;passwd&＃39;,master_log_file&＃61;&＃39;mysql-bin.000001&＃39;, master_log_pos&＃61;107;

slave start;

show slave status\G;

db3从 (现有的从库)reset master;

slave stop;

GRANT ALL PRIVILEGES ON *.* TO &＃39;mha_manager&＃39;&＃64;&＃39;192.168.1.%&＃39; IDENTIFIED BY &＃39;123456&＃39;;

flush privileges;

change master to MASTER_HOST&＃61;&＃39;192.168.1.2&＃39;, MASTER_PORT&＃61;3306,MASTER_USER&＃61;&＃39;mharep&＃39;, MASTER_PASSWORD&＃61;&＃39;passwd&＃39;,master_log_file&＃61;&＃39;mysql-bin.000001&＃39;, master_log_pos&＃61;107;

slave start;

show slave status\G;

配置双机互信

managerssh-keygen -t rsa

for i in db1 db2 db3;do ssh-copy-id -i ~/.ssh/id_rsa.pub root&＃64;$i;done

db1主ssh-keygen -t rsa

for i in manager db2 db3;do ssh-copy-id -i ~/.ssh/id_rsa.pub root&＃64;$i;done

db2备 和 db3从 配置略

/usr/sbin/ntpdate 132.163.4.101 请把时间同步

安装mysql-MHA

https://downloads.mariadb.com/files/MHAwget https://downloads.mariadb.com/files/MHA/mha4mysql-manager-0.55-0.el6.noarch.rpm

wget https://downloads.mariadb.com/files/MHA/mha4mysql-node-0.54-0.el6.noarch.rpm

db1主 db2备 db3从

yum install perl-DBD-MySQL

yum localinstall mha4mysql-node-0.54-0.el6.noarch.rpm

manageryum install perl cpan

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

#这两个yum没有安装上 perl-Log-Dispatch perl-Parallel-ForkManagerwget http://rpmfind.net/linux/dag/redhat/el6/en/x86_64/dag/RPMS/perl-Log-Dispatch-2.26-1.el6.rf.noarch.rpm

wget ftp://rpmfind.net/linux/dag/redhat/el6/en/x86_64/dag/RPMS/perl-Parallel-ForkManager-0.7.5-2.2.el6.rf.noarch.rpm

wget ftp://rpmfind.net/linux/dag/redhat/el6/en/x86_64/dag/RPMS/perl-Mail-Sender-0.8.16-1.el6.rf.noarch.rpm

wget ftp://rpmfind.net/linux/dag/redhat/el6/en/x86_64/dag/RPMS/perl-Mail-Sendmail-0.79-1.2.el6.rf.noarch.rpmrpm -ivh perl-Mail-Sender-0.8.16-1.el6.rf.noarch.rpm

rpm -ivh perl-Mail-Sendmail-0.79-1.2.el6.rf.noarch.rpm

yum localinstall perl-Log-Dispatch-2.26-1.el6.rf.noarch.rpm

yum localinstall perl-Parallel-ForkManager-0.7.5-2.2.el6.rf.noarch.rpm

yum localinstall mha4mysql-node-0.54-0.el6.noarch.rpm

yum localinstall mha4mysql-manager-0.55-0.el6.noarch.rpm

配置mysql-MHA[root&＃64;manager ~]# masterha_masterha_check_repl masterha_conf_host masterha_master_switch

masterha_check_ssh masterha_manager masterha_secondary_check

masterha_check_status masterha_master_monitor masterha_stop

tar -xzf mha4mysql-manager-0.55.tar.gz #

ls mha4mysql-manager-0.55/samples/conf #大家了解mha自带的配置文件,可忽略

masterha_default.cnf app1.cnf

mkdir -p /usr/local/mha/scripts

cp mha4mysql-manager-0.55/samples/scripts/* /usr/local/mha/scripts[root&＃64;manager ~]# vim /usr/local/mha/mha.conf #根据自己的mysql配置改动

[server default]

user&＃61;mha_manager #mha管理的用户名

password&＃61;123456

manager_workdir&＃61;/usr/local/mha

manager_log&＃61;/usr/local/mha/manager.log

remote_workdir&＃61;/usr/local/mha

ssh_user&＃61;root #上面双机互信的用户

repl_user&＃61;mharep #mysql复制(同步)的用户名

repl_password&＃61;passwd

ping_interval&＃61;1

#下面是mysql检测设置

secondary_check_script&＃61; masterha_secondary_check -s 192.168.1.2 -s 192.168.1.3 -s 192.168.1.4

#master_ip_failover_script&＃61;/usr/local/mha/scripts/master_ip_failover

#shutdown_script&＃61; /usr/local/mha/scripts/power_manager

report_script&＃61; /usr/local/mha/scripts/send_report

master_ip_online_change_script&＃61; /usr/local/mha/scripts/master_ip_online_change

[server1]

hostname&＃61;192.168.1.2 #写ip或者主机名都可以

ssh_port&＃61;22

master_binlog_dir&＃61;/var/mysql/log

candidate_master&＃61;1

[server2]

hostname&＃61;192.168.1.3

ssh_port&＃61;22

master_binlog_dir&＃61;/var/mysql/log

candidate_master&＃61;1

[server3]

hostname&＃61;192.168.1.4

ssh_port&＃61;22

master_binlog_dir&＃61;/var/mysql/log

no_master&＃61;1

#验证ssh通讯

masterha_check_ssh --conf&＃61;/usr/local/mha/mha.conf

#验证mysql复制wget ftp://rpmfind.net/linux/dag/redhat/el6/en/x86_64/extras/RPMS/perl-Net-Telnet-3.03-2.el6.rfx.noarch.rpm

yum localinstall perl-Net-Telnet-3.03-2.el6.rfx.noarch.rpm

#mysql必须都启动[root&＃64;manager ~]# masterha_check_repl --conf&＃61;/usr/local/mha/mha.conf

Thu Jan 8 18:05:40 2015 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.

Thu Jan 8 18:05:40 2015 - [info] Reading application default configurations from /usr/local/mha/mha.conf..

Thu Jan 8 18:05:40 2015 - [info] Reading server configurations from /usr/local/mha/mha.conf..

Thu Jan 8 18:05:40 2015 - [info] MHA::MasterMonitor version 0.55.

Thu Jan 8 18:05:40 2015 - [info] Dead Servers:

Thu Jan 8 18:05:40 2015 - [info] Alive Servers:

Thu Jan 8 18:05:40 2015 - [info] 192.168.1.2(192.168.1.2:3306)

Thu Jan 8 18:05:40 2015 - [info] 192.168.1.3(192.168.1.3:3306)

Thu Jan 8 18:05:40 2015 - [info] 192.168.1.4(192.168.1.4:3306)

Thu Jan 8 18:05:40 2015 - [info] Alive Slaves:

Thu Jan 8 18:05:40 2015 - [info] 192.168.1.3(192.168.1.3:3306) Version&＃61;5.5.37-log (oldest major version between slaves) log-bin:enabled

Thu Jan 8 18:05:40 2015 - [info] Replicating from 192.168.1.2(192.168.1.2:3306)

Thu Jan 8 18:05:40 2015 - [info] Primary candidate for the new Master (candidate_master is set)

Thu

Jan 8 18:05:40 2015 - [info] 192.168.1.4(192.168.1.4:3306)

Version&＃61;5.5.37-log (oldest major version between slaves) log-bin:enabled

Thu Jan 8 18:05:40 2015 - [info] Replicating from 192.168.1.2(192.168.1.2:3306)

Thu Jan 8 18:05:40 2015 - [info] Not candidate for the new Master (no_master is set)

Thu Jan 8 18:05:40 2015 - [info] Current Alive Master: 192.168.1.2(192.168.1.2:3306)

Thu Jan 8 18:05:40 2015 - [info] Checking slave configurations..

Thu Jan 8 18:05:40 2015 - [info] Checking replication filtering settings..

Thu Jan 8 18:05:40 2015 - [info] binlog_do_db&＃61; , binlog_ignore_db&＃61;

Thu Jan 8 18:05:40 2015 - [info] Replication filtering check ok.

Thu Jan 8 18:05:40 2015 - [info] Starting SSH connection tests..

Thu Jan 8 18:05:41 2015 - [info] All SSH connection tests passed successfully.

Thu Jan 8 18:05:41 2015 - [info] Checking MHA Node version..

Thu Jan 8 18:05:42 2015 - [info] Version check ok.

Thu Jan 8 18:05:42 2015 - [info] Checking SSH publickey authentication settings on the current master..

Thu Jan 8 18:05:42 2015 - [info] HealthCheck: SSH to 192.168.1.2 is reachable.

Thu Jan 8 18:05:42 2015 - [info] Master MHA Node version is 0.54.

Thu Jan 8 18:05:42 2015 - [info] Checking recovery script configurations on the current master..

Thu

Jan 8 18:05:42 2015 - [info] Executing command: save_binary_logs

--command&＃61;test --start_pos&＃61;4 --binlog_dir&＃61;/var/mysql/log

--output_file&＃61;/usr/local/mha/save_binary_logs_test

--manager_version&＃61;0.55 --start_file&＃61;mysql-bin.000002

Thu Jan 8 18:05:42 2015 - [info] Connecting to root&＃64;192.168.1.2(192.168.1.2)..

Creating /usr/local/mha if not exists.. ok.

Checking output directory is accessible or not..

ok.

Binlog found at /var/mysql/log, up to mysql-bin.000002

Thu Jan 8 18:05:42 2015 - [info] Master setting check done.

Thu

Jan 8 18:05:42 2015 - [info] Checking SSH publickey authentication and

checking recovery script configurations on all alive slave servers..

Thu

Jan 8 18:05:42 2015 - [info] Executing command :

apply_diff_relay_logs --command&＃61;test --slave_user&＃61;&＃39;mha_manager&＃39;

--slave_host&＃61;192.168.1.3 --slave_ip&＃61;192.168.1.3 --slave_port&＃61;3306

--workdir&＃61;/usr/local/mha --target_version&＃61;5.5.37-log

--manager_version&＃61;0.55 --relay_log_info&＃61;/var/mysql/data/relay-log.info

--relay_dir&＃61;/var/mysql/data/ --slave_pass&＃61;xxx

Thu Jan 8 18:05:42 2015 - [info] Connecting to root&＃64;192.168.1.3(192.168.1.3:22)..

Checking slave recovery environment settings..

Opening /var/mysql/data/relay-log.info ... ok.

Relay log found at /var/mysql/data, up to mysql-relay-bin.000007

Temporary relay log file is /var/mysql/data/mysql-relay-bin.000007

Testing mysql connection and privileges.. done.

Testing mysqlbinlog output.. done.

Cleaning up test file(s).. done.

Thu

Jan 8 18:05:42 2015 - [info] Executing command :

apply_diff_relay_logs --command&＃61;test --slave_user&＃61;&＃39;mha_manager&＃39;

--slave_host&＃61;192.168.1.4 --slave_ip&＃61;192.168.1.4 --slave_port&＃61;3306

--workdir&＃61;/usr/local/mha --target_version&＃61;5.5.37-log

--manager_version&＃61;0.55 --relay_log_info&＃61;/var/mysql/data/relay-log.info

--relay_dir&＃61;/var/mysql/data/ --slave_pass&＃61;xxx

Thu Jan 8 18:05:42 2015 - [info] Connecting to root&＃64;192.168.1.4(192.168.1.4:22)..

Checking slave recovery environment settings..

Opening /var/mysql/data/relay-log.info ... ok.

Relay log found at /var/mysql/data, up to mysql-relay-bin.000006

Temporary relay log file is /var/mysql/data/mysql-relay-bin.000006

Testing mysql connection and privileges.. done.

Testing mysqlbinlog output.. done.

Cleaning up test file(s).. done.

Thu Jan 8 18:05:43 2015 - [info] Slaves settings check done.

Thu Jan 8 18:05:43 2015 - [info]

192.168.1.2 (current master)

&＃43;--192.168.1.3

&＃43;--192.168.1.4

Thu Jan 8 18:05:43 2015 - [info] Checking replication health on 192.168.1.3..

Thu Jan 8 18:05:43 2015 - [info] ok.

Thu Jan 8 18:05:43 2015 - [info] Checking replication health on 192.168.1.4..

Thu Jan 8 18:05:43 2015 - [info] ok.

Thu Jan 8 18:05:43 2015 - [info] Checking master_ip_failover_script status:

Thu

Jan 8 18:05:43 2015 - [info]

/usr/local/mha/scripts/master_ip_failover --command&＃61;status

--ssh_user&＃61;root --orig_master_host&＃61;192.168.1.2

--orig_master_ip&＃61;192.168.1.2 --orig_master_port&＃61;3306

IN

SCRIPT TEST&＃61;&＃61;&＃61;&＃61;/sbin/ifconfig eth0:1 down&＃61;&＃61;/sbin/ifconfig eth0:1

192.168.1.100;/sbin/arping -I eth0 -c 3 -s 192.168.1.100 192.168.1.1

>/dev/null 2>&1&＃61;&＃61;&＃61;

Checking the Status of the script.. OK

Thu Jan 8 18:05:46 2015 - [info] OK.

Thu Jan 8 18:05:46 2015 - [warning] shutdown_script is not defined.

Thu Jan 8 18:05:46 2015 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.

启动MHA

mv /usr/local/mha/scripts/master_ip_failover /usr/local/mha/scripts/master_ip_failover.def

vim /usr/local/mha/scripts/master_ip_failover#!/usr/bin/env perl

use strict;

use warnings FATAL &＃61;> &＃39;all&＃39;;

use Getopt::Long;

my (

$command, $ssh_user, $orig_master_host, $orig_master_ip,

$orig_master_port, $new_master_host, $new_master_ip, $new_master_port

);

my $vip &＃61; &＃39;192.168.1.100&＃39;; # Virtual IP

my $gateway &＃61; &＃39;192.168.1.1&＃39;; #Gateway IP

my $interface &＃61; &＃39;eth0&＃39;;

my $key &＃61; "1";

my $ssh_start_vip &＃61; "/sbin/ifconfig $interface:$key $vip;/sbin/arping -I $interface -c 3 -s $vip $gateway >/dev/null 2>&1";

my $ssh_stop_vip &＃61; "/sbin/ifconfig $interface:$key down";

GetOptions(

&＃39;command&＃61;s&＃39; &＃61;> \$command,

&＃39;ssh_user&＃61;s&＃39; &＃61;> \$ssh_user,

&＃39;orig_master_host&＃61;s&＃39; &＃61;> \$orig_master_host,

&＃39;orig_master_ip&＃61;s&＃39; &＃61;> \$orig_master_ip,

&＃39;orig_master_port&＃61;i&＃39; &＃61;> \$orig_master_port,

&＃39;new_master_host&＃61;s&＃39; &＃61;> \$new_master_host,

&＃39;new_master_ip&＃61;s&＃39; &＃61;> \$new_master_ip,

&＃39;new_master_port&＃61;i&＃39; &＃61;> \$new_master_port,

);

exit &main();

sub main {

print "\n\nIN SCRIPT TEST&＃61;&＃61;&＃61;&＃61;$ssh_stop_vip&＃61;&＃61;$ssh_start_vip&＃61;&＃61;&＃61;\n\n";

if ( $command eq "stop" || $command eq "stopssh" ) {

# $orig_master_host, $orig_master_ip, $orig_master_port are passed.

# If you manage master ip address at global catalog database,

# invalidate orig_master_ip here.

my $exit_code &＃61; 1;

eval {

print "Disabling the VIP on old master: $orig_master_host \n";

&stop_vip();

$exit_code &＃61; 0;

};

if ($&＃64;) {

warn "Got Error: $&＃64;\n";

exit $exit_code;

}

exit $exit_code;

}

elsif ( $command eq "start" ) {

# all arguments are passed.

# If you manage master ip address at global catalog database,

# activate new_master_ip here.

# You can also grant write access (create user, set read_only&＃61;0, etc) here.

my $exit_code &＃61; 10;

eval {

print "Enabling the VIP - $vip on the new master - $new_master_host \n";

&start_vip();

$exit_code &＃61; 0;

};

if ($&＃64;) {

warn $&＃64;;

exit $exit_code;

}

exit $exit_code;

}

elsif ( $command eq "status" ) {

print "Checking the Status of the script.. OK \n";

&＃96;ssh $ssh_user\&＃64;$orig_master_host \" $ssh_start_vip \"&＃96;;

exit 0;

}

else {

&usage();

exit 1;

}

}

# A simple system call that enable the VIP on the new master

sub start_vip() {

&＃96;ssh $ssh_user\&＃64;$new_master_host \" $ssh_start_vip \"&＃96;;

}

# A simple system call that disable the VIP on the old_master

sub stop_vip() {

&＃96;ssh $ssh_user\&＃64;$orig_master_host \" $ssh_stop_vip \"&＃96;;

}

sub usage {

print

"Usage:

master_ip_failover --command&＃61;start|stop|stopssh|status

--orig_master_host&＃61;host --orig_master_ip&＃61;ip --orig_master_port&＃61;port

--new_master_host&＃61;host --new_master_ip&＃61;ip --new_master_port&＃61;port\n";

}

# sed -i &＃39;s/#master_ip_failover_script/master_ip_failover_script/&＃39; /usr/local/mha/mha.conf

配置文件的master_ip_failover_script前面注释#取消

# chmod 755 /usr/local/mha/scripts/master_ip_failover

# masterha_check_repl --conf&＃61;/usr/local/mha/mha.conf

启动mha

[root&＃64;manager ~]# masterha_manager --conf&＃61;/usr/local/mha/mha.conf &

或者

nohup masterha_manager --conf&＃61;/usr/local/mha/mha.conf > /tmp/mha_manager.log &1 &

查看MHA

[root&＃64;manager ~]# masterha_check_status --conf&＃61;/usr/local/mha/mha.conf

mha (pid:2686) is running(0:PING_OK), master:192.168.1.2

到mysql主库上看看ip信息&＃xff0c;自己的ip&＃xff0c;VIP

停止MHA

[root&＃64;manager ~]# masterha_stop --conf&＃61;/usr/local/mha/mha.conf

验证MHA&＃xff1a;

[root&＃64;db1 ~]# ip a #现在有VIP

[root&＃64;db1 ~]# service mysqld stop #mysql停止&＃xff0c;在这时候VIP会做转移

Shutting down MySQL... SUCCESS!

[root&＃64;db1 ~]# ip a #已经没有VIP

IP已经转移到db2&＃xff0c;并且在db3上 mysql> show slave status\G 查看 Master_Server_Id: 2

查看mysql_MHA日志

/usr/local/mha/manager.log

#检查到db1数据库出错

Fri Jan 9 10:09:39 2015 - [info] Executing seconary network check script: masterha_secondary_check -s 192.168.1.2 -s 192.168.1.3 -s 192.168.1.4 --user&＃61;root --master_host&＃61;192.168.1.2 --master_ip&＃61;192.168.1.2 --master_port&＃61;3306

Fri Jan 9 10:09:39 2015 - [info] Executing SSH check script: save_binary_logs --command&＃61;test --start_pos&＃61;4 --binlog_dir&＃61;/var/mysql/log --output_file&＃61;/usr/local/mha/save_binary_logs_test --manager_version&＃61;0.55 --binlog_prefix&＃61;mysql-bin

Fri Jan 9 10:09:39 2015 - [info] HealthCheck: SSH to 192.168.1.2 is reachable.

Monitoring server 192.168.1.2 is reachable, Master is not reachable from 192.168.1.2. OK.

Monitoring server 192.168.1.3 is reachable, Master is not reachable from 192.168.1.3. OK.

Monitoring server 192.168.1.4 is reachable, Master is not reachable from 192.168.1.4. OK.

Fri Jan 9 10:09:39 2015 - [info] Master is not reachable from all other monitoring servers. Failover should start.

Fri Jan 9 10:09:40 2015 - [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at &＃39;reading initial communication packet&＃39;, system error: 111)

Fri Jan 9 10:09:40 2015 - [warning] Connection failed 1 time(s)..

#转移Fri Jan 9 10:09:42 2015 - [info] Dead Servers:

Fri Jan 9 10:09:42 2015 - [info] 192.168.1.2(192.168.1.2:3306)

Fri Jan 9 10:09:42 2015 - [info] Alive Servers:

Fri Jan 9 10:09:42 2015 - [info] 192.168.1.3(192.168.1.3:3306)

Fri Jan 9 10:09:42 2015 - [info] 192.168.1.4(192.168.1.4:3306)

Fri Jan 9 10:09:42 2015 - [info] Alive Slaves:

Fri Jan 9 10:09:42 2015 - [info] 192.168.1.3(192.168.1.3:3306) Version&＃61;5.5.37-log (oldest major version between slaves) log-bin:enabled

Fri Jan 9 10:09:42 2015 - [info] Replicating from 192.168.1.2(192.168.1.2:3306)

Fri Jan 9 10:09:42 2015 - [info] Primary candidate for the new Master (candidate_master is set)

Fri Jan 9 10:09:42 2015 - [info] 192.168.1.4(192.168.1.4:3306) Version&＃61;5.5.37-log (oldest major version between slaves) log-bin:enabled

Fri Jan 9 10:09:42 2015 - [warning] relay_log_purge&＃61;0 is not set on slave 192.168.1.3(192.168.1.3:3306).

Fri Jan 9 10:09:42 2015 - [warning] relay_log_purge&＃61;0 is not set on slave 192.168.1.4(192.168.1.4:3306).

Disabling the VIP on old master: 192.168.1.2

Fri Jan 9 10:09:42 2015 - [info] done.

Fri Jan 9 10:09:42 2015 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.

Fri Jan 9 10:09:42 2015 - [info] The latest binary log file/position on all slaves is mysql-bin.000003:107

Fri Jan 9 10:09:42 2015 - [info] Latest slaves (Slaves that received relay log files to the latest):

Fri Jan 9 10:09:42 2015 - [info] 192.168.1.3(192.168.1.3:3306) Version&＃61;5.5.37-log (oldest major version between slaves) log-bin:enabled

Fri Jan 9 10:09:42 2015 - [info] Replicating from 192.168.1.2(192.168.1.2:3306)

Fri Jan 9 10:09:42 2015 - [info] Primary candidate for the new Master (candidate_master is set)

Fri Jan 9 10:09:42 2015 - [info] 192.168.1.4(192.168.1.4:3306) Version&＃61;5.5.37-log (oldest major version between slaves) log-bin:enabled

#转移后

Fri Jan 9 10:09:43 2015 - [info] Starting master failover..

Fri Jan 9 10:09:43 2015 - [info]

From:

192.168.1.2 (current master)

&＃43;--192.168.1.3

&＃43;--192.168.1.4

To:

192.168.1.3 (new master)

&＃43;--192.168.1.4

----- Failover Report -----

mha: MySQL Master failover 192.168.1.2 to 192.168.1.3 succeeded

Master 192.168.1.2 is down!

看这个日志就跟看电影一样&＃xff0c;高潮部分&＃xff0c;O(∩_∩)O哈哈~

在 db1 此目录 /usr/local/mha/ 会有binlog文件

saved_master_binlog_from_192.168.1.2_3306_20150109100942.binlog

当db1 mysql down后&＃xff0c;manager会停止运行&＃xff0c;

[root&＃64;manager ~]# masterha_check_status --conf&＃61;/usr/local/mha/mha.conf

mha is stopped(2:NOT_RUNNING).

[1]&＃43; Done nohup masterha_manager --conf&＃61;/usr/local/mha/mha.conf > /tmp/mha_manager.log &1

#恢复操作

1&＃xff0c;db1启动mysql

[root&＃64;db1 ~]# service mysqld start

Starting MySQL.. SUCCESS!

2&＃xff0c;mysqlbinlog 查看binlog文件&＃xff0c;然后把db1主做为备

# mysqlbinlog saved_master_binlog_from_192.168.1.2_3306_20150109100942.binlog > mysqlbinlog.sql #转换binlog为sql语句

3&＃xff0c;less mysqlbinlog.sql #找出同步的pos做从&＃xff0c;或者重新做mysql从

change master to MASTER_HOST&＃61;&＃39;192.168.1.3&＃39;, MASTER_PORT&＃61;3306,MASTER_USER&＃61;&＃39;mharep&＃39;, MASTER_PASSWORD&＃61;&＃39;passwd&＃39;,master_log_file&＃61;&＃39;mysql-bin.000001&＃39;, master_log_pos&＃61;107;

slave start;

show slave status\G;

4&＃xff0c;启动manager的管理

# nohup masterha_manager --conf&＃61;/usr/local/mha/mha.conf > /tmp/mha_manager.log &1 &

# masterha_check_status --conf&＃61;/usr/local/mha/mha.conf

如果有这个提示&＃xff0c;那么删除此文件/usr/local/mha/mha.failover.complete

[error][/usr/share/perl5/vendor_perl/MHA/MasterFailover.pm, ln298] Last failover was done at 2015/01/09 10:00:47. Current time is too early to do failover again. If you want to do failover, manually remove /usr/local/mha/mha.failover.complete and run this script again.

5&＃xff0c;MmysqlMHA的VIP手动切换

masterha_check_repl --conf&＃61;/usr/local/mha/mha.conf

nohup masterha_manager --conf&＃61;/usr/local/mha/mha.conf > /tmp/mha_manager.log &1 &

masterha_check_status --conf&＃61;/usr/local/mha/mha.conf

masterha_stop --conf&＃61;/usr/local/mha/mha.conf

masterha_master_switch --conf&＃61;/usr/local/mha/mha.conf --master_state&＃61;alive --new_master_host&＃61;192.168.(new master IP address) --new_master_port&＃61;3306 --orig_master_is_new_slave --running_updates_limit&＃61;10000

上面用到的配置下载地址&＃xff1a;http://down.51cto.com/data/2065725

本文原创发布php中文网&＃xff0c;转载请注明出处&＃xff0c;感谢您的尊重&＃xff01;