MHA implements manual switching between mysql master databases and slave databases _ MySQL

This article mainly introduces how MHA implements manual switching between mysql master databases and slave databases. it describes in detail the steps and methods for switching between master and slave databases, which is very practical, for more information about how to manually switch the mysql master/slave database using MHA, see the following example. The specific method is as follows:

I. preparations

1. execute the following commands on the Master and Slave respectively to facilitate mha to check replication:

The code is as follows:

Grant all privileges on *. * to 'root' @ '10. 1.1.231 'identified by 'rootpass ';
Grant all privileges on *. * 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 the master to read-only

The code is as follows:

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)

Interaction Mode:

The code is as follows:

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

The code is as follows:

# Masterha_master_switch -- master_state = alive -- conf =/etc/masterha/app1.cnf -- new_master_host = 10.1.1.231 -- new_master_port = 63306-interactive = 0

II. how to set 10.1.1.231 as the master node and 10.1.1.234 as the Slave node after switching:

1. execute on the master:

The code is as follows:

Mysql> show master status;
+ ------------------------- + ---------- + ---------------- + ------------------------------------ + ------------------- +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+ ------------------------- + ---------- + ---------------- + ------------------------------------ + ------------------- +
| Mysql-master-bin.000013 | 120 | denovo_ng | mysql, denion, test, information_schema |
+ ------------------------- + ---------- + ---------------- + ------------------------------------ + ------------------- +
1 row in set (0.00 sec)

2. execute the following SQL command on 10.1.1.234;

The code is as follows:

Change master to master_host = '10. 1.1.231 ', master_port = 63306, master_user = 'psync ',
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 the master status and test

The code is as follows:

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 records on the master database 10.1.1.231

The code is as follows:

Mysql> insert into test_slave_002 values (555538511,1, 55555, 99999, 44.11, 2222,91919 );
Query OK, 1 row affected (0.00 sec)

The slave database query record already exists.

The code is as follows:

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 database my. cnf configuration add

The code is as follows:

Skip_slave_start

Note: Avoid database restart and slave process startup, resulting in data inconsistency.
Update the slave database my. cnf configuration and set the slave database to read-only:

The code is as follows:

Read_only = 1
Relay_log_purge = 0

Restart the master database and slave database to observe the database information:
Master database information:

The code is as follows:

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 be 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, denion, test, information_schema |
+ ------------------------- + ---------- + ---------------- + ------------------------------------ + ------------------- +
1 row in set (0.00 sec)

Slave Database information:

The code is as follows:

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)

I hope this article will help you design MySQL database programs.