MySQL高可用方案之MHA

发表评论
786

A+

所属分类：Database

[root@DB1 ~]# yum install perl-DBD-MySQL ncftp perl-DBI
mysql> grant all privileges on *.* to mha@'1.1.%.%' identified by 'admin';
[root@DB1 ~]# /usr/local/mysql/bin/mysqldump --single-transaction --triggers --routines --all-databases --events --set-gtid-purged=OFF -uroot -p''>> all.sql                    
Enter password: 
[root@DB1 ~]# rm -f /tmp/all.sql
[root@DB1 ~]# scp all.sql root@1.1.1.133:/tmp/            
[root@DB1 ~]# scp all.sql root@1.1.1.132:/tmp/
[root@DB3 ~]# /usr/local/mysql/bin/mysql -uroot -p </tmp/all.sql
[root@DB3 ~]# yum install perl-Config-Tiny perl-Time-HiRes perl-Parallel-ForkManager perl-Log-Dispatch-Perl perl-DBD-MySQL ncftp

[root@DB1 ~]# yum install perl-DBD-MySQL ncftp perl-DBI

mysql> grant all privileges on *.* to mha@'1.1.%.%' identified by 'admin';

[root@DB1 ~]# /usr/local/mysql/bin/mysqldump --single-transaction --triggers --routines --all-databases --events --set-gtid-purged=OFF -uroot -p''>> all.sql

Enter password:

[root@DB1 ~]# rm -f /tmp/all.sql

[root@DB1 ~]# scp all.sql root@1.1.1.133:/tmp/

[root@DB1 ~]# scp all.sql root@1.1.1.132:/tmp/

[root@DB3 ~]# /usr/local/mysql/bin/mysql -uroot -p </tmp/all.sql

[root@DB3 ~]# yum install perl-Config-Tiny perl-Time-HiRes perl-Parallel-ForkManager perl-Log-Dispatch-Perl perl-DBD-MySQL ncftp

mysql> change master to master_host='1.1.1.131',
    -> MASTER_PORT=3306,
    -> master_user='repl',
    -> master_password='admin',
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

mysql> start slave;
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 1.1.1.131
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 154
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          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: 154
              Relay_Log_Space: 568
              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: 131
                  Master_UUID: a2396dd7-0c06-11e7-b5c1-000c290f01e1
             Master_Info_File: /usr/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           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: 264053d1-0b78-11e7-8d3b-000c2913ac14:1-262
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

mysql> change master to master_host='1.1.1.131',

-> MASTER_PORT=3306,

-> master_user='repl',

-> master_password='admin',

-> master_auto_position=1;

Query OK, 0 rows affected, 2 warnings (0.04 sec)

mysql> start slave;

mysql> show slave status \G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 1.1.1.131

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000002

Read_Master_Log_Pos: 154

Relay_Log_File: relay-bin.000002

Relay_Log_Pos: 367

Relay_Master_Log_File: mysql-bin.000002

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

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: 154

Relay_Log_Space: 568

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: 131

Master_UUID: a2396dd7-0c06-11e7-b5c1-000c290f01e1

Master_Info_File: /usr/local/mysql/data/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

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: 264053d1-0b78-11e7-8d3b-000c2913ac14:1-262

Auto_Position: 1

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

[root@DB1 ~]# ssh-keygen 
[root@DB1 ~]# ssh-copy-id -i /root/.ssh/id_rsa root@1.1.1.132
[root@DB1 ~]# ssh-copy-id -i /root/.ssh/id_rsa root@1.1.1.133
[root@DB1 ~]# ssh-copy-id -i /root/.ssh/id_rsa root@1.1.1.131
[root@DB1 ~]# rpm -ivh mha4mysql-node-0.57-0.el7.noarch.rpm 
[root@DB2 ~]# rpm -ivh mha4mysql-node-0.57-0.el7.noarch.rpm 
[root@DB3 ~]# rpm -ivh mha4mysql-node-0.57-0.el7.noarch.rpm 
[root@DB3 ~]# rpm -ivh mha4mysql-manager-0.57-0.el7.noarch.rpm

[root@DB1 ~]# ssh-keygen

[root@DB1 ~]# ssh-copy-id -i /root/.ssh/id_rsa root@1.1.1.132

[root@DB1 ~]# ssh-copy-id -i /root/.ssh/id_rsa root@1.1.1.133

[root@DB1 ~]# ssh-copy-id -i /root/.ssh/id_rsa root@1.1.1.131

[root@DB1 ~]# rpm -ivh mha4mysql-node-0.57-0.el7.noarch.rpm

[root@DB2 ~]# rpm -ivh mha4mysql-node-0.57-0.el7.noarch.rpm

[root@DB3 ~]# rpm -ivh mha4mysql-node-0.57-0.el7.noarch.rpm

[root@DB3 ~]# rpm -ivh mha4mysql-manager-0.57-0.el7.noarch.rpm

[root@DB3 ~]# masterha_check_ssh --conf=/etc/mha/mysql_mha.conf 
Sun Mar 19 02:18:59 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Mar 19 02:18:59 2017 - [info] Reading application default configuration from /etc/mha/mysql_mha.conf..
Sun Mar 19 02:18:59 2017 - [info] Reading server configuration from /etc/mha/mysql_mha.conf..
Sun Mar 19 02:18:59 2017 - [info] Starting SSH connection tests..
Sun Mar 19 02:19:02 2017 - [debug] 
Sun Mar 19 02:18:59 2017 - [debug]  Connecting via SSH from root@1.1.1.131(1.1.1.131:22) to root@1.1.1.132(1.1.1.132:22)..
Sun Mar 19 02:19:00 2017 - [debug]   ok.
Sun Mar 19 02:19:00 2017 - [debug]  Connecting via SSH from root@1.1.1.131(1.1.1.131:22) to root@1.1.1.133(1.1.1.133:22)..
Sun Mar 19 02:19:01 2017 - [debug]   ok.
Sun Mar 19 02:19:02 2017 - [debug] 
Sun Mar 19 02:18:59 2017 - [debug]  Connecting via SSH from root@1.1.1.132(1.1.1.132:22) to root@1.1.1.131(1.1.1.131:22)..
Sun Mar 19 02:19:01 2017 - [debug]   ok.
Sun Mar 19 02:19:01 2017 - [debug]  Connecting via SSH from root@1.1.1.132(1.1.1.132:22) to root@1.1.1.133(1.1.1.133:22)..
Sun Mar 19 02:19:02 2017 - [debug]   ok.
Sun Mar 19 02:19:03 2017 - [debug] 
Sun Mar 19 02:19:00 2017 - [debug]  Connecting via SSH from root@1.1.1.133(1.1.1.133:22) to root@1.1.1.131(1.1.1.131:22)..
Sun Mar 19 02:19:01 2017 - [debug]   ok.
Sun Mar 19 02:19:01 2017 - [debug]  Connecting via SSH from root@1.1.1.133(1.1.1.133:22) to root@1.1.1.132(1.1.1.132:22)..
Sun Mar 19 02:19:03 2017 - [debug]   ok.
Sun Mar 19 02:19:03 2017 - [info] All SSH connection tests passed successfully.

[root@DB3 ~]# chmod +x /usr/bin/master_ip_failover
[root@DB3 ~]# /usr/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=1.1.1.131 --orig_master_ip=1.1.1.131 --orig_master_port=3306


IN SCRIPT TEST====root|sudo /sbin/ifconfig eth0:1 down==root|sudo /sbin/ifconfig eth0:1 1.1.1.100===

Checking the Status of the script.. OK 
[root@DB3 ~]# masterha_check_repl --conf=/etc/mha/mysql_mha.conf                                                                     Sun Mar 19 02:46:37 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Mar 19 02:46:37 2017 - [info] Reading application default configuration from /etc/mha/mysql_mha.conf..
Sun Mar 19 02:46:37 2017 - [info] Reading server configuration from /etc/mha/mysql_mha.conf..
Sun Mar 19 02:46:37 2017 - [info] MHA::MasterMonitor version 0.57.
Sun Mar 19 02:46:37 2017 - [info] GTID failover mode = 1
Sun Mar 19 02:46:37 2017 - [info] Dead Servers:
Sun Mar 19 02:46:37 2017 - [info] Alive Servers:
Sun Mar 19 02:46:37 2017 - [info]   1.1.1.131(1.1.1.131:3306)
Sun Mar 19 02:46:37 2017 - [info]   1.1.1.132(1.1.1.132:3306)
Sun Mar 19 02:46:37 2017 - [info]   1.1.1.133(1.1.1.133:3306)
Sun Mar 19 02:46:37 2017 - [info] Alive Slaves:
Sun Mar 19 02:46:37 2017 - [info]   1.1.1.132(1.1.1.132:3306)  Version=5.7.16-log (oldest major version between slaves) log-bin:enabled
Sun Mar 19 02:46:37 2017 - [info]     GTID ON
Sun Mar 19 02:46:37 2017 - [info]     Replicating from 1.1.1.131(1.1.1.131:3306)
Sun Mar 19 02:46:37 2017 - [info]     Primary candidate for the new Master (candidate_master is set)
Sun Mar 19 02:46:37 2017 - [info]   1.1.1.133(1.1.1.133:3306)  Version=5.7.16-log (oldest major version between slaves) log-bin:enabled
Sun Mar 19 02:46:37 2017 - [info]     GTID ON
Sun Mar 19 02:46:37 2017 - [info]     Replicating from 1.1.1.131(1.1.1.131:3306)
Sun Mar 19 02:46:37 2017 - [info]     Not candidate for the new Master (no_master is set)
Sun Mar 19 02:46:37 2017 - [info] Current Alive Master: 1.1.1.131(1.1.1.131:3306)
Sun Mar 19 02:46:37 2017 - [info] Checking slave configurations..
Sun Mar 19 02:46:37 2017 - [info] Checking replication filtering settings..
Sun Mar 19 02:46:37 2017 - [info]  binlog_do_db= , binlog_ignore_db= 
Sun Mar 19 02:46:37 2017 - [info]  Replication filtering check ok.
Sun Mar 19 02:46:37 2017 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Sun Mar 19 02:46:37 2017 - [info] Checking SSH publickey authentication settings on the current master..
Sun Mar 19 02:46:37 2017 - [info] HealthCheck: SSH to 1.1.1.131 is reachable.
Sun Mar 19 02:46:37 2017 - [info] 
1.1.1.131(1.1.1.131:3306) (current master)
 +--1.1.1.132(1.1.1.132:3306)
 +--1.1.1.133(1.1.1.133:3306)

Sun Mar 19 02:46:37 2017 - [info] Checking replication health on 1.1.1.132..
Sun Mar 19 02:46:37 2017 - [info]  ok.
Sun Mar 19 02:46:37 2017 - [info] Checking replication health on 1.1.1.133..
Sun Mar 19 02:46:37 2017 - [info]  ok.
Sun Mar 19 02:46:37 2017 - [info] Checking master_ip_failover_script status:
Sun Mar 19 02:46:37 2017 - [info]   /usr/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=1.1.1.131 --orig_master_ip=1.1.1.131 --orig_master_port=3306 


IN SCRIPT TEST====root|sudo /sbin/ifconfig eth0:1 down==root|sudo /sbin/ifconfig eth0:1 1.1.1.100===

Checking the Status of the script.. OK 
Sun Mar 19 02:46:37 2017 - [info]  OK.
Sun Mar 19 02:46:37 2017 - [warning] shutdown_script is not defined.
Sun Mar 19 02:46:37 2017 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.

[root@DB3 ~]# masterha_manager --conf=/etc/mha/mysql_mha.conf &

[root@DB1 ~]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN 
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 00:0c:29:0f:01:e1 brd ff:ff:ff:ff:ff:ff
    inet 1.1.1.131/24 brd 1.1.1.255 scope global eth0
    inet 1.1.1.100/24 brd 1.1.1.255 scope global secondary eth0:1
    inet6 fe80::20c:29ff:fe0f:1e1/64 scope link 
       valid_lft forever preferred_lft forever

[root@DB3 ~]# masterha_check_ssh --conf=/etc/mha/mysql_mha.conf

Sun Mar 19 02:18:59 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.

Sun Mar 19 02:18:59 2017 - [info] Reading application default configuration from /etc/mha/mysql_mha.conf..

Sun Mar 19 02:18:59 2017 - [info] Reading server configuration from /etc/mha/mysql_mha.conf..

Sun Mar 19 02:18:59 2017 - [info] Starting SSH connection tests..

Sun Mar 19 02:19:02 2017 - [debug]

Sun Mar 19 02:18:59 2017 - [debug] Connecting via SSH from root@1.1.1.131(1.1.1.131:22) to root@1.1.1.132(1.1.1.132:22)..

Sun Mar 19 02:19:00 2017 - [debug] ok.

Sun Mar 19 02:19:00 2017 - [debug] Connecting via SSH from root@1.1.1.131(1.1.1.131:22) to root@1.1.1.133(1.1.1.133:22)..

Sun Mar 19 02:19:01 2017 - [debug] ok.

Sun Mar 19 02:19:02 2017 - [debug]

Sun Mar 19 02:18:59 2017 - [debug] Connecting via SSH from root@1.1.1.132(1.1.1.132:22) to root@1.1.1.131(1.1.1.131:22)..

Sun Mar 19 02:19:01 2017 - [debug] ok.

Sun Mar 19 02:19:01 2017 - [debug] Connecting via SSH from root@1.1.1.132(1.1.1.132:22) to root@1.1.1.133(1.1.1.133:22)..

Sun Mar 19 02:19:02 2017 - [debug] ok.

Sun Mar 19 02:19:03 2017 - [debug]

Sun Mar 19 02:19:00 2017 - [debug] Connecting via SSH from root@1.1.1.133(1.1.1.133:22) to root@1.1.1.131(1.1.1.131:22)..

Sun Mar 19 02:19:01 2017 - [debug] ok.

Sun Mar 19 02:19:01 2017 - [debug] Connecting via SSH from root@1.1.1.133(1.1.1.133:22) to root@1.1.1.132(1.1.1.132:22)..

Sun Mar 19 02:19:03 2017 - [debug] ok.

Sun Mar 19 02:19:03 2017 - [info] All SSH connection tests passed successfully.

[root@DB3 ~]# chmod +x /usr/bin/master_ip_failover

[root@DB3 ~]# /usr/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=1.1.1.131 --orig_master_ip=1.1.1.131 --orig_master_port=3306

IN SCRIPT TEST====root|sudo /sbin/ifconfig eth0:1 down==root|sudo /sbin/ifconfig eth0:1 1.1.1.100===

Checking the Status of the script.. OK

[root@DB3 ~]# masterha_check_repl --conf=/etc/mha/mysql_mha.conf Sun Mar 19 02:46:37 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.

Sun Mar 19 02:46:37 2017 - [info] Reading application default configuration from /etc/mha/mysql_mha.conf..

Sun Mar 19 02:46:37 2017 - [info] Reading server configuration from /etc/mha/mysql_mha.conf..

Sun Mar 19 02:46:37 2017 - [info] MHA::MasterMonitor version 0.57.

Sun Mar 19 02:46:37 2017 - [info] GTID failover mode = 1

Sun Mar 19 02:46:37 2017 - [info] Dead Servers:

Sun Mar 19 02:46:37 2017 - [info] Alive Servers:

Sun Mar 19 02:46:37 2017 - [info] 1.1.1.131(1.1.1.131:3306)

Sun Mar 19 02:46:37 2017 - [info] 1.1.1.132(1.1.1.132:3306)

Sun Mar 19 02:46:37 2017 - [info] 1.1.1.133(1.1.1.133:3306)

Sun Mar 19 02:46:37 2017 - [info] Alive Slaves:

Sun Mar 19 02:46:37 2017 - [info] 1.1.1.132(1.1.1.132:3306) Version=5.7.16-log (oldest major version between slaves) log-bin:enabled

Sun Mar 19 02:46:37 2017 - [info] GTID ON

Sun Mar 19 02:46:37 2017 - [info] Replicating from 1.1.1.131(1.1.1.131:3306)

Sun Mar 19 02:46:37 2017 - [info] Primary candidate for the new Master (candidate_master is set)

Sun Mar 19 02:46:37 2017 - [info] 1.1.1.133(1.1.1.133:3306) Version=5.7.16-log (oldest major version between slaves) log-bin:enabled

Sun Mar 19 02:46:37 2017 - [info] GTID ON

Sun Mar 19 02:46:37 2017 - [info] Replicating from 1.1.1.131(1.1.1.131:3306)

Sun Mar 19 02:46:37 2017 - [info] Not candidate for the new Master (no_master is set)

Sun Mar 19 02:46:37 2017 - [info] Current Alive Master: 1.1.1.131(1.1.1.131:3306)

Sun Mar 19 02:46:37 2017 - [info] Checking slave configurations..

Sun Mar 19 02:46:37 2017 - [info] Checking replication filtering settings..

Sun Mar 19 02:46:37 2017 - [info] binlog_do_db= , binlog_ignore_db=

Sun Mar 19 02:46:37 2017 - [info] Replication filtering check ok.

Sun Mar 19 02:46:37 2017 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.

Sun Mar 19 02:46:37 2017 - [info] Checking SSH publickey authentication settings on the current master..

Sun Mar 19 02:46:37 2017 - [info] HealthCheck: SSH to 1.1.1.131 is reachable.

Sun Mar 19 02:46:37 2017 - [info]

1.1.1.131(1.1.1.131:3306) (current master)

+--1.1.1.132(1.1.1.132:3306)

+--1.1.1.133(1.1.1.133:3306)

Sun Mar 19 02:46:37 2017 - [info] Checking replication health on 1.1.1.132..

Sun Mar 19 02:46:37 2017 - [info] ok.

Sun Mar 19 02:46:37 2017 - [info] Checking replication health on 1.1.1.133..

Sun Mar 19 02:46:37 2017 - [info] ok.

Sun Mar 19 02:46:37 2017 - [info] Checking master_ip_failover_script status:

Sun Mar 19 02:46:37 2017 - [info] /usr/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=1.1.1.131 --orig_master_ip=1.1.1.131 --orig_master_port=3306

IN SCRIPT TEST====root|sudo /sbin/ifconfig eth0:1 down==root|sudo /sbin/ifconfig eth0:1 1.1.1.100===

Checking the Status of the script.. OK

Sun Mar 19 02:46:37 2017 - [info] OK.

Sun Mar 19 02:46:37 2017 - [warning] shutdown_script is not defined.

Sun Mar 19 02:46:37 2017 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.

[root@DB3 ~]# masterha_manager --conf=/etc/mha/mysql_mha.conf &

[root@DB1 ~]# ip addr

1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN

link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00

inet 127.0.0.1/8 scope host lo

inet6 ::1/128 scope host

valid_lft forever preferred_lft forever

2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000

link/ether 00:0c:29:0f:01:e1 brd ff:ff:ff:ff:ff:ff

inet 1.1.1.131/24 brd 1.1.1.255 scope global eth0

inet 1.1.1.100/24 brd 1.1.1.255 scope global secondary eth0:1

inet6 fe80::20c:29ff:fe0f:1e1/64 scope link

valid_lft forever preferred_lft forever

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 1.1.1.131
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 638
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1396
                   Last_Error: Error 'Operation DROP USER failed for 'mha'@'1.1.%.%'' on query. Default database: ''. Query: 'drop user mha@'1.1.%.%''
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 194
              Relay_Log_Space: 1513
              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: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1396
               Last_SQL_Error: Error 'Operation DROP USER failed for 'mha'@'1.1.%.%'' on query. Default database: ''. Query: 'drop user mha@'1.1.%.%''
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 131
                  Master_UUID: a2396dd7-0c06-11e7-b5c1-000c290f01e1
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 170319 03:47:25
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: a2396dd7-0c06-11e7-b5c1-000c290f01e1:2-3
            Executed_Gtid_Set: a2396dd7-0c06-11e7-b5c1-000c290f01e1:1,
d4e4c35f-0b70-11e7-be1d-000c299824ca:1-262
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000003
         Position: 234
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: a2396dd7-0c06-11e7-b5c1-000c290f01e1:1,
d4e4c35f-0b70-11e7-be1d-000c299824ca:1-262
1 row in set (0.00 sec)

mysql> SET SESSION GTID_NEXT='d4e4c35f-0b70-11e7-be1d-000c299824ca:1-263';
ERROR 1774 (HY000): Malformed GTID specification 'd4e4c35f-0b70-11e7-be1d-000c299824ca:1-263'.
mysql> SET SESSION GTID_NEXT='d4e4c35f-0b70-11e7-be1d-000c299824ca:263';  
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> commit;      
Query OK, 0 rows affected (0.00 sec)
mysql> set gtid_next='AUTOMATIC'; 
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 1.1.1.131

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000007

Read_Master_Log_Pos: 638

Relay_Log_File: relay-bin.000002

Relay_Log_Pos: 367

Relay_Master_Log_File: mysql-bin.000007

Slave_IO_Running: Yes

Slave_SQL_Running: No

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 1396

Last_Error: Error 'Operation DROP USER failed for 'mha'@'1.1.%.%'' on query. Default database: ''. Query: 'drop user mha@'1.1.%.%''

Skip_Counter: 0

Exec_Master_Log_Pos: 194

Relay_Log_Space: 1513

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: 0

Last_IO_Error:

Last_SQL_Errno: 1396

Last_SQL_Error: Error 'Operation DROP USER failed for 'mha'@'1.1.%.%'' on query. Default database: ''. Query: 'drop user mha@'1.1.%.%''

Replicate_Ignore_Server_Ids:

Master_Server_Id: 131

Master_UUID: a2396dd7-0c06-11e7-b5c1-000c290f01e1

Master_Info_File: mysql.slave_master_info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State:

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp: 170319 03:47:25

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: a2396dd7-0c06-11e7-b5c1-000c290f01e1:2-3

Executed_Gtid_Set: a2396dd7-0c06-11e7-b5c1-000c290f01e1:1,

d4e4c35f-0b70-11e7-be1d-000c299824ca:1-262

Auto_Position: 1

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

mysql> show master status\G

*************************** 1. row ***************************

File: mysql-bin.000003

Position: 234

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set: a2396dd7-0c06-11e7-b5c1-000c290f01e1:1,

d4e4c35f-0b70-11e7-be1d-000c299824ca:1-262

1 row in set (0.00 sec)

mysql> SET SESSION GTID_NEXT='d4e4c35f-0b70-11e7-be1d-000c299824ca:1-263';

ERROR 1774 (HY000): Malformed GTID specification 'd4e4c35f-0b70-11e7-be1d-000c299824ca:1-263'.

mysql> SET SESSION GTID_NEXT='d4e4c35f-0b70-11e7-be1d-000c299824ca:263';

Query OK, 0 rows affected (0.00 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> set gtid_next='AUTOMATIC';

Query OK, 0 rows affected (0.00 sec)

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

[root@DB3 ~]# masterha_stop --conf=/etc/mha/mysql_mha.conf 
Stopped mysql_mha successfully.
[root@DB3 ~]# masterha_stop --conf=/etc/mha/mysql_mha.conf 
Stopped mysql_mha successfully.
[root@DB3 ~]# masterha_master_switch --conf=/etc/mha/mysql_mha.conf --master_state=alive  --new_master_host=1.1.1.132  --new_master_port=3306  --orig_master_is_new_slave --running_updates_limit=10000
Sun Mar 19 06:03:28 2017 - [info] MHA::MasterRotate version 0.57.
Sun Mar 19 06:03:28 2017 - [info] Starting online master switch..
Sun Mar 19 06:03:28 2017 - [info] 
Sun Mar 19 06:03:28 2017 - [info] * Phase 1: Configuration Check Phase..
Sun Mar 19 06:03:28 2017 - [info] 
Sun Mar 19 06:03:28 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Mar 19 06:03:28 2017 - [info] Reading application default configuration from /etc/mha/mysql_mha.conf..
Sun Mar 19 06:03:28 2017 - [info] Reading server configuration from /etc/mha/mysql_mha.conf..
Sun Mar 19 06:03:28 2017 - [info] GTID failover mode = 1
Sun Mar 19 06:03:28 2017 - [info] Current Alive Master: 1.1.1.131(1.1.1.131:3306)
Sun Mar 19 06:03:28 2017 - [info] Alive Slaves:
Sun Mar 19 06:03:28 2017 - [info]   1.1.1.132(1.1.1.132:3306)  Version=5.7.16-log (oldest major version between slaves) log-bin:enabled
Sun Mar 19 06:03:28 2017 - [info]     GTID ON
Sun Mar 19 06:03:28 2017 - [info]     Replicating from 1.1.1.131(1.1.1.131:3306)
Sun Mar 19 06:03:28 2017 - [info]     Primary candidate for the new Master (candidate_master is set)
Sun Mar 19 06:03:28 2017 - [info]   1.1.1.133(1.1.1.133:3306)  Version=5.7.16-log (oldest major version between slaves) log-bin:enabled
Sun Mar 19 06:03:28 2017 - [info]     GTID ON
Sun Mar 19 06:03:28 2017 - [info]     Replicating from 1.1.1.131(1.1.1.131:3306)
Sun Mar 19 06:03:28 2017 - [info]     Not candidate for the new Master (no_master is set)

It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 1.1.1.131(1.1.1.131:3306)? (YES/no): YES
Sun Mar 19 06:03:31 2017 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time..
Sun Mar 19 06:03:31 2017 - [info]  ok.
Sun Mar 19 06:03:31 2017 - [info] Checking MHA is not monitoring or doing failover..
Sun Mar 19 06:03:31 2017 - [info] Checking replication health on 1.1.1.132..
Sun Mar 19 06:03:31 2017 - [info]  ok.
Sun Mar 19 06:03:31 2017 - [info] Checking replication health on 1.1.1.133..
Sun Mar 19 06:03:31 2017 - [info]  ok.
Sun Mar 19 06:03:31 2017 - [info] 1.1.1.132 can be new master.
Sun Mar 19 06:03:31 2017 - [info] 
From:
1.1.1.131(1.1.1.131:3306) (current master)
 +--1.1.1.132(1.1.1.132:3306)
 +--1.1.1.133(1.1.1.133:3306)

To:
1.1.1.132(1.1.1.132:3306) (new master)
 +--1.1.1.133(1.1.1.133:3306)
 +--1.1.1.131(1.1.1.131:3306)

Starting master switch from 1.1.1.131(1.1.1.131:3306) to 1.1.1.132(1.1.1.132:3306)? (yes/NO): yes
Sun Mar 19 06:03:38 2017 - [info] Checking whether 1.1.1.132(1.1.1.132:3306) is ok for the new master..
Sun Mar 19 06:03:38 2017 - [info]  ok.
Sun Mar 19 06:03:38 2017 - [info] 1.1.1.131(1.1.1.131:3306): SHOW SLAVE STATUS returned empty result. To check replication filtering rules, temporarily executing CHANGE MASTER to a dummy host.
Sun Mar 19 06:03:38 2017 - [info] 1.1.1.131(1.1.1.131:3306): Resetting slave pointing to the dummy host.
Sun Mar 19 06:03:38 2017 - [info] ** Phase 1: Configuration Check Phase completed.
Sun Mar 19 06:03:38 2017 - [info] 
Sun Mar 19 06:03:38 2017 - [info] * Phase 2: Rejecting updates Phase..
Sun Mar 19 06:03:38 2017 - [info] 
Sun Mar 19 06:03:38 2017 - [info] Executing master ip online change script to disable write on the current master:
Sun Mar 19 06:03:38 2017 - [info]   /usr/bin/master_ip_online_change --command=stop --orig_master_host=1.1.1.131 --orig_master_ip=1.1.1.131 --orig_master_port=3306 --orig_master_user='mha' --new_master_host=1.1.1.132 --new_master_ip=1.1.1.132 --new_master_port=3306 --new_master_user='mha' --orig_master_ssh_user=root --new_master_ssh_user=root   --orig_master_is_new_slave --orig_master_password=xxx --new_master_password=xxx



***************************************************************
Disabling the VIP - 1.1.1.100/24 on old master: 1.1.1.131
***************************************************************



SIOCSIFFLAGS: Cannot assign requested address
Sun Mar 19 06:03:38 2017 - [info]  ok.
Sun Mar 19 06:03:38 2017 - [info] Locking all tables on the orig master to reject updates from everybody (including root):
Sun Mar 19 06:03:38 2017 - [info] Executing FLUSH TABLES WITH READ LOCK..
Sun Mar 19 06:03:38 2017 - [info]  ok.
Sun Mar 19 06:03:38 2017 - [info] Orig master binlog:pos is mysql-bin.000013:194.
Sun Mar 19 06:03:38 2017 - [info]  Waiting to execute all relay logs on 1.1.1.132(1.1.1.132:3306)..
Sun Mar 19 06:03:38 2017 - [info]  master_pos_wait(mysql-bin.000013:194) completed on 1.1.1.132(1.1.1.132:3306). Executed 0 events.
Sun Mar 19 06:03:38 2017 - [info]   done.
Sun Mar 19 06:03:38 2017 - [info] Getting new master's binlog name and position..
Sun Mar 19 06:03:38 2017 - [info]  mysql-bin.000011:234
Sun Mar 19 06:03:38 2017 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='1.1.1.132', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx';
Sun Mar 19 06:03:38 2017 - [info] Executing master ip online change script to allow write on the new master:
Sun Mar 19 06:03:38 2017 - [info]   /usr/bin/master_ip_online_change --command=start --orig_master_host=1.1.1.131 --orig_master_ip=1.1.1.131 --orig_master_port=3306 --orig_master_user='mha' --new_master_host=1.1.1.132 --new_master_ip=1.1.1.132 --new_master_port=3306 --new_master_user='mha' --orig_master_ssh_user=root --new_master_ssh_user=root   --orig_master_is_new_slave --orig_master_password=xxx --new_master_password=xxx



***************************************************************
Enabling the VIP - 1.1.1.100/24 on new master: 1.1.1.132 
***************************************************************



Sun Mar 19 06:03:39 2017 - [info]  ok.
Sun Mar 19 06:03:39 2017 - [info] 
Sun Mar 19 06:03:39 2017 - [info] * Switching slaves in parallel..
Sun Mar 19 06:03:39 2017 - [info] 
Sun Mar 19 06:03:39 2017 - [info] -- Slave switch on host 1.1.1.133(1.1.1.133:3306) started, pid: 2983
Sun Mar 19 06:03:39 2017 - [info] 
Sun Mar 19 06:03:40 2017 - [info] Log messages from 1.1.1.133 ...
Sun Mar 19 06:03:40 2017 - [info] 
Sun Mar 19 06:03:39 2017 - [info]  Waiting to execute all relay logs on 1.1.1.133(1.1.1.133:3306)..
Sun Mar 19 06:03:39 2017 - [info]  master_pos_wait(mysql-bin.000013:194) completed on 1.1.1.133(1.1.1.133:3306). Executed 0 events.
Sun Mar 19 06:03:39 2017 - [info]   done.
Sun Mar 19 06:03:39 2017 - [info]  Resetting slave 1.1.1.133(1.1.1.133:3306) and starting replication from the new master 1.1.1.132(1.1.1.132:3306)..
Sun Mar 19 06:03:39 2017 - [info]  Executed CHANGE MASTER.
Sun Mar 19 06:03:40 2017 - [info]  Slave started.
Sun Mar 19 06:03:40 2017 - [info] End of log messages from 1.1.1.133 ...
Sun Mar 19 06:03:40 2017 - [info] 
Sun Mar 19 06:03:40 2017 - [info] -- Slave switch on host 1.1.1.133(1.1.1.133:3306) succeeded.
Sun Mar 19 06:03:40 2017 - [info] Unlocking all tables on the orig master:
Sun Mar 19 06:03:40 2017 - [info] Executing UNLOCK TABLES..
Sun Mar 19 06:03:40 2017 - [info]  ok.
Sun Mar 19 06:03:40 2017 - [info] Starting orig master as a new slave..
Sun Mar 19 06:03:40 2017 - [info]  Resetting slave 1.1.1.131(1.1.1.131:3306) and starting replication from the new master 1.1.1.132(1.1.1.132:3306)..
Sun Mar 19 06:03:40 2017 - [info]  Executed CHANGE MASTER.
Sun Mar 19 06:03:41 2017 - [info]  Slave started.
Sun Mar 19 06:03:41 2017 - [info] All new slave servers switched successfully.
Sun Mar 19 06:03:41 2017 - [info] 
Sun Mar 19 06:03:41 2017 - [info] * Phase 5: New master cleanup phase..
Sun Mar 19 06:03:41 2017 - [info] 
Sun Mar 19 06:03:41 2017 - [info]  1.1.1.132: Resetting slave info succeeded.
Sun Mar 19 06:03:41 2017 - [info] Switching master to 1.1.1.132(1.1.1.132:3306) completed successfully.
[root@DB3 ~]#