MHA to realize MySQL master-Slave manual handoff method

First, preparatory work

1, respectively in master and slave implementation of the following, convenient MHA check replication:

Grant all privileges in *.* to ' root ' @ ' 10.1.1.231 ' identified by ' rootpass ';
Grant all privileges in *.* to ' root ' @ ' 10.1.1.234 ' identified by ' rootpass ';
Grant replication slave on *.* to ' Jpsync ' @ ' 10.1.1.231 ' identified by ' jppasswd ';
Grant replication slave on *.* to ' Jpsync ' @ ' 10.1.1.234 ' identified by ' jppasswd ';
Flush privileges;
2. Set Master to read-only

mysql> set global Read_only=1;
Query OK, 0 rows Affected (0.00 sec)
Mysql> Show variables like ' read_only ';
+---------------+-------+
| variable_name | Value |
+---------------+-------+
| Read_Only | On |
+---------------+-------+
1 row in Set (0.00 sec)
Interactive mode:

#masterha_master_switch--master_state=alive--conf=/etc/masterha/app1.cnf--new_master_host=10.1.1.231--new_ master_port=63306
or non-interactive mode:

#masterha_master_switch--master_state=alive--conf=/etc/masterha/app1.cnf--new_master_host=10.1.1.231--new_ Master_port=63306-interactive=0
Second, after switching over, how to let 10.1.1.231, 10.1.1.234 for from, Operation steps:

1, the Lord Executive:

Mysql> Show master status;
+-------------------------+----------+--------------+--------------------------------------+------------------- +
| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |
+-------------------------+----------+--------------+--------------------------------------+------------------- +
|      mysql-master-bin.000013 | 120 | Denovo_ng |                   Mysql,denovo,test,information_schema | |
+-------------------------+----------+--------------+--------------------------------------+------------------- +
1 row in Set (0.00 sec)
2, execute the following SQL command on the 10.1.1.234;

Change Master to master_host= ' 10.1.1.231 ', master_port=63306,master_user= ' Jpsync ',
Master_password= ' jppasswd ', master_log_file= ' mysql-master-bin.000013 ', master_log_pos=120;

Mysql> show Slave status\g;
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:10.1.1.231
Master_user:jpsync
master_port:63306
Connect_retry:60
master_log_file:mysql-master-bin.000013
read_master_log_pos:120
relay_log_file:compute-0-52-relay-bin.000002
relay_log_pos:290
relay_master_log_file:mysql-master-bin.000013
Slave_io_running:yes
Slave_sql_running:yes
3, view master status, and test

Mysql> show Slave hosts;
+-----------+------+-------+-----------+--------------------------------------+
| server_id | Host | Port | master_id | Slave_uuid |
+-----------+------+-------+-----------+--------------------------------------+
|      1052 | |      63306 | 1025 | E25a3e4a-39c0-11e4-80cb-00259086c4b6 |
+-----------+------+-------+-----------+--------------------------------------+
1 row in Set (0.00 sec)
Insert record on Main library 10.1.1.231

mysql> INSERT into test_slave_002 values (555551111,1,55555,99999,44.11,2222,91919);
Query OK, 1 row Affected (0.00 sec)
Query record from library already exists

Mysql> SELECT * from test_slave_002 where id=555551111;
+-----------+-----+-----------+--------------+----------+----------------+--------------+
| ID | Tag | ticket_id | candidate_id | Duration | source_file_id | Source_start |
+-----------+-----+-----------+--------------+----------+----------------+--------------+
|   555551111 |     1 |        55555 |    99999 |           44.11 |        2222 | 91919 |
+-----------+-----+-----------+--------------+----------+----------------+--------------+
1 row in Set (0.00 sec)
4, update the configuration file:

Update Master Library MY.CNF configuration add

1
Skip_slave_start
Note: Prevent restart of the database, start the slave process, resulting in inconsistent data.

Updates are added from the library my.cnf configuration, setting the slave library to read-only:

1
2
Read_only=1
Relay_log_purge=0
Then restart the main library and from the library, observe the library information:

Main Library Information:

Mysql> show Processlist;
+----+--------+------------------+------+-------------+------+------------------------------------------------- ----------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+--------+------------------+------+-------------+------+------------------------------------------------- ----------------------+------------------+
| 1 | Jpsync | 10.1.1.234:49085 | NULL |   Binlog Dump | 17 | Master has sent all binlog to slave; Waiting for Binlog to is updated | NULL |
| 2 | Root | localhost | NULL |    Query | 0 | init | Show Processlist |
+----+--------+------------------+------+-------------+------+------------------------------------------------- ----------------------+------------------+
2 rows in Set (0.00 sec)


Mysql> Show master status;
+-------------------------+----------+--------------+--------------------------------------+------------------- +
| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |
+-------------------------+----------+--------------+--------------------------------------+------------------- +
|      mysql-master-bin.000014 | 120 | Denovo_ng |                   Mysql,denovo,test,information_schema | |
+-------------------------+----------+--------------+--------------------------------------+------------------- +
1 row in Set (0.00 sec)
From library information:

Mysql> show Slave status\g;
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:10.1.1.231
Master_user:jpsync
master_port:63306
Connect_retry:60
master_log_file:mysql-master-bin.000014
read_master_log_pos:120
relay_log_file:compute-0-52-relay-bin.000005
relay_log_pos:290
relay_master_log_file:mysql-master-bin.000014
Slave_io_running:yes
Slave_sql_running:yes

Mysql> show Processlist;
+----+-------------+-----------+------+---------+------+------------------------------------------------------- ----------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+------------------------------------------------------- ----------------------+------------------+
| 1 |           System user | | NULL |   Connect | 58 | Waiting for Master to send event | NULL |
| 2 |           System user | | NULL |   Connect | 58 | Slave has read all relay log; Waiting for the slave I/O thread to update it | NULL |
| 3 | Root | localhost | NULL |    Query | 0 | init | Show Processlist |
+----+-------------+-----------+------+---------+------+------------------------------------------------------- ----------------------+------------------+
3 Rows in Set (0.00 sec)