MySQL高可用之MHA

ret33

简单介绍一下MHA.MySQL高可用方案不少.前一篇提到的MMM也是一种.而MySQL-MHA是日本MySQL专家用Perl写的一套MySQL故障切换方案.它有Node、Manager两种角色.Node需要安装在所有MySQL服务器不管主还是从.Manager运行在独立服务器,如果资源不够也可以跟DB服务器上面跑.

本次实验是三台DB,Master1即是主库也是管理节点,Master2是Master备节点,Slave1就是从库.

目标:Master1宕机.Master2接管成为新主库,Slave1自动判断新主库恢复同步.

结构图如下:


Master1 && Manager Node:192.168.23.194
Master2:192.168.23.159
Slave1:192.168.23.125

配置SSH信任通信
[iyunv@localhost ~]$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
ae:65:d1:7f:24:f5:78:15:a2:21:78:52:8e:a0:02:51 root@localhost
The key's randomart image is:
+--[ RSA 2048]----+
|ooE  .  oo . . . |
|.   . .oo.. o . .|
| . .   .o. .  . .|
|  .      .   . o.|
|        S . . o o|
|       . . . o . |
|        +   . .  |
|       +     .   |
|      .          |
+-----------------+

[iyunv@localhost ~]$ ssh-copy-id  -i /root/.ssh/id_rsa.pub root@192.168.23.194
Address 192.168.23.194 maps to localhost, but this does not map back to the address - POSSIBLE BREAK-IN ATTEMPT!
root@192.168.23.194's password:
Now try logging into the machine, with "ssh 'root@192.168.23.194'", and check in:

  .ssh/authorized_keys

to make sure we haven't added extra keys that you weren't expecting.

[iyunv@localhost ~]$ ssh-copy-id  -i /root/.ssh/id_rsa.pub root@192.168.23.159
[iyunv@localhost ~]$ ssh-copy-id  -i /root/.ssh/id_rsa.pub root@192.168.23.125

同时还需要MASTER2、Slave1->Master1的通信配置过程同上

测试SSH信任通信
[iyunv@localhost ~]$ ssh 192.168.23.194 date
Mon Jun  9 14:49:11 CST 2014
[iyunv@localhost ~]$ ssh 192.168.23.159 date
Mon Jun  9 14:49:21 CST 2014
[iyunv@localhost ~]$ ssh 192.168.23.125 date
Mon Jun  9 14:49:29 CST 2014

修改Host文件(三台)
[mysql@localhost ~]$ cat /etc/hosts
127.0.0.1       localhost.localdomain   localhost
::1     localhost6.localdomain6 localhost6

192.168.23.194  Master1

192.168.23.159  Master2

192.168.23.125  Slave1

Master1 my.cnf

[mysql@localhost ~]$ cat /etc/my.cnf
[client]
port=3306
socket=/tmp/mysql.sock

[mysqld]
port=3306
server-id=2
datadir=/mysql/data
socket=/tmp/mysql.sock
user=mysql
default_storage_engine=innodb
character_set_server=utf8
slow_query_log=1
slow_query_log_file=/mysql/slowquery.log
long_query_time=2
log-queries-not-using-indexes
log-slow-admin-statements
innodb_buffer_pool_size=50M
innodb_flush_log_at_trx_commit=1
max_allowed_packet=10M
innodb_file_per_table=1
binlog_format=mixed
log-bin=/mysql/log/mysql-bin
log_bin_trust_function_creators = 1
innodb_fast_shutdown = 0
binlog-do-db=pre_test_market
binlog-do-db=test
binlog-do-db=ss_test
replicate-do-db=pre_test_market
replicate-do-db=test
replicate-do-db=ss_test
log-slave-updates=on
[mysqld_safe]
log-error=/mysql/mysqld.log
pid-file=/mysql/mysqld.pid

Master2 my.cnf

[client]
port=3306
socket=/tmp/mysql.sock

[mysqld]
port=3306
server-id=4
datadir=/mysql/data
socket=/tmp/mysql.sock
user=mysql
default_storage_engine=InnoDB
character_set_server=utf8
relay_log_purge=0
relay_log=/mysql/log/mysql-relay-bin
relay_log=/mysql/log/mysql-relay-bin-index
slow_query_log=1
slow_query_log_file=/mysql/slowquery.log
long_query_time=2
log-queries-not-using-indexes
log-slow-admin-statements
#log_bin_trust_function_creators = 1
log-bin=/mysql/log/mysql-bin
log_bin_index=/mysql/log/mysql-index
binlog_format=mixed
binlog-do-db=pre_test_market
binlog-do-db=test
binlog-do-db=ss_test
replicate-do-db=pre_test_market
replicate-do-db=test
replicate-do-db=ss_test
log-slave-updates=on
slave-skip-errors=1007,1050,1146,1051,1062
[mysqld_safe]
log-error=/mysql/mysqld.log
pid-file=/mysql/mysqld.pid

Slave1 my.cnf

[client]
socket=/tmp/mysql.sock
port=3306

[mysqld]
server-id=3
port=3306
read_only=1
relay_log_purge=0
basedir=/usr/local/mysql
datadir=/mysql/data
socket=/tmp/mysql.sock
user=mysql
default_storage_engine=innodb
character_set_server=utf8
log-bin=/mysql/log/mysql-bin
log-bin-index=/mysql/log/mysql-index
slave-skip-errors=1007,1050,1062
slow_query_log=1
slow_query_log_file=/mysql/slowquery.log
long_query_time=2
relay-log=relay-bin   
relay-log-index=relay-bin-index
binlog_format=mixed
log-slave-updates=on
binlog-do-db=pre_test_market
binlog-do-db=test
binlog-do-db=ss_test
replicate-do-db=pre_test_market
replicate-do-db=test
replicate-do-db=ss_test
[mysqld_safe]
log-error=/mysql/mysqld.log
pid-file=/mysql/mysqld.pid

注意:参数文件由于做过很多次实验有些参数本次实验用不到.不过不影响实验结果所以没有修改

在Master1、Master2中建立用户
grant replication slave ON *.* TO 'mha'@'%' identified by 'XXXXXX';flush privileges;

在Master1中查看POS位置
mysql> show master status;
+------------------+----------+--------------------------------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB                         | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------------------------------+------------------+-------------------+
| mysql-bin.000031 |      120 | pre_test_market,test,ss_test         |                  |                   |
+------------------+----------+--------------------------------------+------------------+-------------------+

在Master2、Slave1执行以下命令
change master to master_host='192.168.23.194',master_user='mha',master_password='XXXXXX',master_port=3306,master_log_file='mysql-bin.000031',master_pos=4;start slave;show slave status\G;

显示下面的状态表示主从正常
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.23.194
                  Master_User: mha
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000031
          Read_Master_Log_Pos: 120
               Relay_Log_File: mysql-relay-bin-index.000004
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000031
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: pre_test_market,test,ss_test
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 120
              Relay_Log_Space: 3145
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 2
                  Master_UUID: 69a73914-62ca-11e3-870f-080027dff846
             Master_Info_File: /mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.01 sec)

为了让MHA可以远程登录DB还需要赋以下权限(三个节点都要执行)
grant all privileges on *.* to 'root'@'%' identified by 'XXXXXX';

MySQL的配置已经完成,现在开始安装MySQL-MHA

安装MHA之前需要先安装下面的包

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

安装MHA
[mysql@localhost ~]$ ls
1          Desktop    Downloads                    mha4mysql-manager-0.56-0.el6.noarch.rpm  Music     Public    Templates
Documents  mha4mysql-node-0.56-0.el6.noarch.rpm     Pictures  Videos
[mysql@localhost ~]$ sudo rpm -ivh mha4mysql-*

注意:由于Master1即是主节点又是管理节点所以两个包都要安装,其余节点只安装node包即可.

编辑MHA配置文件
[server default]
user=root
password=XXXXXX
ssh_user=root
repl_user=mha
repl_password=XXXXXX
manager_workdir=/var/log/mha/node
manager_log=/var/log/mha/node/node.log
[server1]
hostname=192.168.23.194
master_binlog_dir=/mysql/log/
candidate_master=1
[server2]
hostname=192.168.23.159
master_binlog_dir=/mysql/log/
candidate_master=1
[server3]
hostname=192.168.23.125
no_master=1

MHA检查SSH信任关系
[iyunv@localhost ~]$ masterha_check_ssh --conf=/etc/masterha_default.cnf
Mon Jun  9 16:42:00 2014 - [info] Reading default configuration from /etc/masterha_default.cnf..
Mon Jun  9 16:42:00 2014 - [info] Reading application default configuration from /etc/masterha_default.cnf..
Mon Jun  9 16:42:00 2014 - [info] Reading server configuration from /etc/masterha_default.cnf..
Mon Jun  9 16:42:00 2014 - [info] Starting SSH connection tests..
Mon Jun  9 16:42:02 2014 - [debug]
Mon Jun  9 16:42:00 2014 - [debug]  Connecting via SSH from root@192.168.23.194(192.168.23.194:22) to root@192.168.23.159(192.168.23.159:22)..
Mon Jun  9 16:42:01 2014 - [debug]   ok.
Mon Jun  9 16:42:01 2014 - [debug]  Connecting via SSH from root@192.168.23.194(192.168.23.194:22) to root@192.168.23.125(192.168.23.125:22)..
Mon Jun  9 16:42:01 2014 - [debug]   ok.
Mon Jun  9 16:42:02 2014 - [debug]
Mon Jun  9 16:42:01 2014 - [debug]  Connecting via SSH from root@192.168.23.159(192.168.23.159:22) to root@192.168.23.194(192.168.23.194:22)..
Mon Jun  9 16:42:01 2014 - [debug]   ok.
Mon Jun  9 16:42:01 2014 - [debug]  Connecting via SSH from root@192.168.23.159(192.168.23.159:22) to root@192.168.23.125(192.168.23.125:22)..
Mon Jun  9 16:42:02 2014 - [debug]   ok.
Mon Jun  9 16:42:03 2014 - [debug]
Mon Jun  9 16:42:01 2014 - [debug]  Connecting via SSH from root@192.168.23.125(192.168.23.125:22) to root@192.168.23.194(192.168.23.194:22)..
Mon Jun  9 16:42:02 2014 - [debug]   ok.
Mon Jun  9 16:42:02 2014 - [debug]  Connecting via SSH from root@192.168.23.125(192.168.23.125:22) to root@192.168.23.159(192.168.23.159:22)..
Mon Jun  9 16:42:03 2014 - [debug]   ok.
Mon Jun  9 16:42:03 2014 - [info] All SSH connection tests passed successfully.

MHA检查MySQL同步
[iyunv@localhost ~]$ masterha_check_repl --conf=/etc/masterha_default.cnf
Mon Jun  9 17:29:27 2014 - [info] Reading default configuration from /etc/masterha_default.cnf..
Mon Jun  9 17:29:27 2014 - [info] Reading application default configuration from /etc/masterha_default.cnf..
Mon Jun  9 17:29:27 2014 - [info] Reading server configuration from /etc/masterha_default.cnf..
Mon Jun  9 17:29:27 2014 - [info] MHA::MasterMonitor version 0.56.
Mon Jun  9 17:29:28 2014 - [info] GTID failover mode = 0
Mon Jun  9 17:29:28 2014 - [info] Dead Servers:
Mon Jun  9 17:29:28 2014 - [info] Alive Servers:
Mon Jun  9 17:29:28 2014 - [info]   192.168.23.194(192.168.23.194:3306)
Mon Jun  9 17:29:28 2014 - [info]   192.168.23.159(192.168.23.159:3306)
Mon Jun  9 17:29:28 2014 - [info]   192.168.23.125(192.168.23.125:3306)
Mon Jun  9 17:29:28 2014 - [info] Alive Slaves:
Mon Jun  9 17:29:28 2014 - [info]   192.168.23.159(192.168.23.159:3306)  Version=5.6.19-log (oldest major version between slaves) log-bin:enabled
Mon Jun  9 17:29:28 2014 - [info]     Replicating from 192.168.23.194(192.168.23.194:3306)
Mon Jun  9 17:29:28 2014 - [info]     Primary candidate for the new Master (candidate_master is set)
Mon Jun  9 17:29:28 2014 - [info]   192.168.23.125(192.168.23.125:3306)  Version=5.6.19-log (oldest major version between slaves) log-bin:enabled
Mon Jun  9 17:29:28 2014 - [info]     Replicating from 192.168.23.194(192.168.23.194:3306)
Mon Jun  9 17:29:28 2014 - [info]     Not candidate for the new Master (no_master is set)
Mon Jun  9 17:29:28 2014 - [info] Current Alive Master: 192.168.23.194(192.168.23.194:3306)
Mon Jun  9 17:29:28 2014 - [info] Checking slave configurations..
Mon Jun  9 17:29:28 2014 - [info]  read_only=1 is not set on slave 192.168.23.159(192.168.23.159:3306).
Mon Jun  9 17:29:28 2014 - [info] Checking replication filtering settings..
Mon Jun  9 17:29:28 2014 - [info]  binlog_do_db= test,pre_test_market,test, binlog_ignore_db=
Mon Jun  9 17:29:28 2014 - [info]  Replication filtering check ok.
Mon Jun  9 17:29:28 2014 - [info] GTID (with auto-pos) is not supported
Mon Jun  9 17:29:28 2014 - [info] Starting SSH connection tests..
Mon Jun  9 17:29:30 2014 - [info] All SSH connection tests passed successfully.
Mon Jun  9 17:29:30 2014 - [info] Checking MHA Node version..
Mon Jun  9 17:29:31 2014 - [info]  Version check ok.
Mon Jun  9 17:29:31 2014 - [info] Checking SSH publickey authentication settings on the current master..
Mon Jun  9 17:29:31 2014 - [info] HealthCheck: SSH to 192.168.23.194 is reachable.
Mon Jun  9 17:29:32 2014 - [info] Master MHA Node version is 0.56.
Mon Jun  9 17:29:32 2014 - [info] Checking recovery script configurations on 192.168.23.194(192.168.23.194:3306)..
Mon Jun  9 17:29:32 2014 - [info]   Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/mysql/log/ --output_file=/var/tmp/save_binary_logs_test --manager_version=0.56 --start_file=mysql-bin.000030
Mon Jun  9 17:29:32 2014 - [info]   Connecting to root@192.168.23.194(192.168.23.194:22)..
  Creating /var/tmp if not exists..    ok.
  Checking output directory is accessible or not..
   ok.
  Binlog found at /mysql/log/, up to mysql-bin.000034.log
Mon Jun  9 17:29:32 2014 - [info] Binlog setting check done.
Mon Jun  9 17:29:32 2014 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Mon Jun  9 17:29:32 2014 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user='root' --slave_host=192.168.23.159 --slave_ip=192.168.23.159 --slave_port=3306 --workdir=/var/tmp --target_version=5.6.19-log --manager_version=0.56 --relay_log_info=/mysql/data/relay-log.info  --relay_dir=/mysql/data/  --slave_pass=xxx
Mon Jun  9 17:29:32 2014 - [info]   Connecting to root@192.168.23.159(192.168.23.159:22)..
  Checking slave recovery environment settings..
    Opening /mysql/data/relay-log.info ... ok.
    Relay log found at /mysql/log, up to mysql-relay-bin-index.000002
    Temporary relay log file is /mysql/log/mysql-relay-bin-index.000002
    Testing mysql connection and privileges..Warning: Using a password on the command line interface can be insecure.
done.
    Testing mysqlbinlog output.. done.
    Cleaning up test file(s).. done.
Mon Jun  9 17:29:33 2014 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user='root' --slave_host=192.168.23.125 --slave_ip=192.168.23.125 --slave_port=3306 --workdir=/var/tmp --target_version=5.6.19-log --manager_version=0.56 --relay_log_info=/mysql/data/relay-log.info  --relay_dir=/mysql/data/  --slave_pass=xxx
Mon Jun  9 17:29:33 2014 - [info]   Connecting to root@192.168.23.125(192.168.23.125:22)..
  Checking slave recovery environment settings..
    Opening /mysql/data/relay-log.info ... ok.
    Relay log found at /mysql/data, up to relay-bin.000012
    Temporary relay log file is /mysql/data/relay-bin.000012
    Testing mysql connection and privileges..Warning: Using a password on the command line interface can be insecure.
done.
    Testing mysqlbinlog output.. done.
    Cleaning up test file(s).. done.
Mon Jun  9 17:29:35 2014 - [info] Slaves settings check done.
Mon Jun  9 17:29:35 2014 - [info]
192.168.23.194(192.168.23.194:3306) (current master)
+--192.168.23.159(192.168.23.159:3306)
+--192.168.23.125(192.168.23.125:3306)

Mon Jun  9 17:29:35 2014 - [info] Checking replication health on 192.168.23.159..
Mon Jun  9 17:29:35 2014 - [info]  ok.
Mon Jun  9 17:29:35 2014 - [info] Checking replication health on 192.168.23.125..
Mon Jun  9 17:29:35 2014 - [info]  ok.
Mon Jun  9 17:29:35 2014 - [warning] master_ip_failover_script is not defined.
Mon Jun  9 17:29:35 2014 - [warning] shutdown_script is not defined.
Mon Jun  9 17:29:35 2014 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.

在MHA检查MySQL复制的时候有可能会出现以下错误:

1、SSH信任通信一定要用ROOT普通用户会出现错误,如果用普通用户有时会出现SSH Configure File Failed错误.(暂时没查到什么原因引起)
2、mysql、mysqlbinlog提示not found.可以ln -s做链接解决.
3、binlog需要在MHA配置文件中指定master_log_dir否则报错
4、my.cnf的binlog参数也要一样否则也会报错

开启MHA

[mysql@localhost ~]$ masterha_manager --conf=/etc/masterha_default.cnf

查看MHA状态
[mysql@localhost ~]$ sudo masterha_check_status --conf=/etc/masterha_default.cnf
[sudo] password for mysql:
masterha_default (pid:6525) is running(0:PING_OK), master:192.168.23.194

正常会显示(PING:OK),否则显示(NOT_RUNNING)

下面开始测试.

Master1:shutdown.查看Master2是否接管,Slave1是否跟着切换

Master1:

[mysql@localhost ~]$ mysqladmin -u root -pXXXXXX shutdown
Warning: Using a password on the command line interface can be insecure.
  Creating /var/tmp if not exists..    ok.
  Checking output directory is accessible or not..
   ok.
  Binlog found at /mysql/log/, up to mysql-bin.000034.log
140609 20:36:11 mysqld_safe mysqld from pid file /mysql/mysqld.pid ended
[mysql@localhost ~]$ Mon Jun  9 20:36:18 2014 - [info] Reading default configuration from /etc/masterha_default.cnf..
Mon Jun  9 20:36:18 2014 - [info] Reading application default configuration from /etc/masterha_default.cnf..
Mon Jun  9 20:36:18 2014 - [info] Reading server configuration from /etc/masterha_default.cnf..

Master2:

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.23.194
                  Master_User: mha
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000031
          Read_Master_Log_Pos: 120
               Relay_Log_File: mysql-relay-bin-index.000004
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000031
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: pre_test_market,test,ss_test
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 120
              Relay_Log_Space: 3145
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 2
                  Master_UUID: 69a73914-62ca-11e3-870f-080027dff846
             Master_Info_File: /mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)

mysql> show slave status\G
Empty set (0.00 sec)

mysql> show slave status\G
Empty set (0.00 sec)

mysql>

Slave1:

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.23.194
                  Master_User: mha
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000031
          Read_Master_Log_Pos: 120
               Relay_Log_File: relay-bin.000015
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000031
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test,ss_test,pre_test_market
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 120
              Relay_Log_Space: 4385
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 2
                  Master_UUID: 69a73914-62ca-11e3-870f-080027dff846
             Master_Info_File: /mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Reconnecting after a failed master event read
                  Master_Host: 192.168.23.194
                  Master_User: mha
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000031
          Read_Master_Log_Pos: 120
               Relay_Log_File: relay-bin.000015
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000031
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
              Replicate_Do_DB: pre_test_market,test,ss_test
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 120
              Relay_Log_Space: 4385
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 2003
                Last_IO_Error: error reconnecting to master 'mha@192.168.23.194:3306' - retry-time: 10  retries: 1
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 2
                  Master_UUID: 69a73914-62ca-11e3-870f-080027dff846
             Master_Info_File: /mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp: 140609 20:36:07
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.23.159
                  Master_User: mha
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000050
          Read_Master_Log_Pos: 1821
               Relay_Log_File: relay-bin.000008
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000050
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: pre_test_market,test,ss_test
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1821
              Relay_Log_Space: 450
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 4
                  Master_UUID: a7e4c60d-62ca-11e3-8710-080027e08a30
             Master_Info_File: /mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)

至此切换成功.不过在MHA切换的过程中还是有时间间隔,数据无法写入的情况还是需要配合KeepAlived等用虚拟IP来实现真正的高可用.此时就需要在配置MHA时加入master_ip_failover_script参数,指定脚本位置.还有一个参数master_ip_online_change_script脚本需要自己编写,如果要在线切换那么MHA就会调用此参数,命令:masterha_master_switch.强行关闭添加参数shutdown_script.

值得关注的是从MHA 0.56这个版本开始支持MySQL5.6新增的GTID故障转移,英文原文如下:

Starting from MHA 0.56, MHA supported both GTID based failover and traditional relay log based failover. MHA automatically distinguishes which failover to choose. To do GTID based failover, all of the following is needed.
Using MySQL 5.6 (or later)
All MySQL instances use gtid_mode=1
At least one of the instances has Auto_Position enabled

今天先到此吧..MHA很给力.^_^