Shulou(Shulou.com)06/01 Report--

In the environment of one master and two slaves, if the master library is down, how to choose a slave database as the master database?

As shown in the figure:

If M fails, how do you choose a slave library from S1 and S2 as the master library?

The solution of traditional replication

(1) View the slave database status:

S1:show slave status

S2:show slave status

Root@localhost [(none)] > show slave status\ G

* * 1. Row *

Slave_IO_State: Reconnecting after a failed master event read

Master_Host: 192.168.91.22

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000006

Read_Master_Log_Pos: 6227

Relay_Log_File: relay-bin.000004

Relay_Log_Pos: 414

Relay_Master_Log_File: mysql-bin.000006

Slave_IO_Running: Connecting

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

Relay_Log_Space: 875

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-after the master library service is stopped, it changes from 0 to null, so this value cannot be used as a criterion to determine whether the slave library is completed synchronously.

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 2003

Last_IO_Error: error reconnecting to master 'repl@192.168.91.22:3306'-retry-time: 60 retries: 12

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 330622

Master_UUID: 83373570-fe03-11e6-bb0a-000c29c1b8a9

Master_Info_File: mysql.slave_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: 170415 23:08:25

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set: 83373570-fe03-11e6-bb0a-000c29c1b8a9:1-33

B30cdc47-216a-11e7-95a8-000c29565380:1-3

Auto_Position: 1

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version

(2) determine whether each slave is completed synchronously:

Io_thread reads the binlog log and location of the main library:

Master_Log_File: mysql-bin.000006

Read_Master_Log_Pos: 6227

Which relay-log and location to which sql_thread executes:

Relay_Master_Log_File: mysql-bin.000006

Exec_Master_Log_Pos: 6227

When Master_Log_File = Relay_Master_Log_File & & Read_Master_Log_Pos = Exec_Master_Log_Pos indicates that the slave library is completed synchronously with the master library.

If Master_Log_File = Relay_Master_Log_File, but Read_Master_Log_Pos > Exec_Master_Log_Pos, and the status of sql_thread is Connecting, it means that the relay-log has not been replayed yet, and it will be completed synchronously after about 2-5 seconds.

(3) compare the synchronization of the two slave libraries:

When S1 and S2 are completed synchronously, who is in front and who is in charge. In most cases, S 1 and S 2 are the same.

When S1.Relay_Master_Log_File=S2.Relay_Master_Log_File but S1.Exec_Master_Log_Pos > S2.Exec_Master_Log_Pos, S1 is synchronized first, and S1 is selected as the new master.

Or compare:

When S1.Master_Log_File = S2.Master_Log_File but S1.Read_Master_Log_Pos > S2.Read_Master_Log_Pos, S1 is synchronized first and S1 is selected as the new master.

(4) what if the data of S1 and S2 are inconsistent?

If S1 is ahead of us and S2 has less data than S1, then after taking S1 as the new master, business reads and writes are first placed on S1, and then the data of S2 is repaired by pt-table-checksum and pt-table-sync tools, and then S2 is used to share the business.

The solution of GTID replication

(1) determine whether each slave is completed synchronously:

Retrieved_Gtid_Set: 83373570-fe03-11e6-bb0a-000c29c1b8a9:22-28

Executed_Gtid_Set: 83373570-fe03-11e6-bb0a-000c29c1b8a9:1-28

When Retrieved_Gtid_Set = Executed_Gtid_Set (that is, 2828) indicates that the slave library has completed synchronization with the master library.

(2) choose a slave database as the master database:

If S1. Executed_Gtid_Set = S2. Executed_Gtid_Set, randomly select one as the master

If S1. Executed_Gtid_Set > S2. Executed_Gtid_Set, then choose S1 as the master, and S2 can directly change master to to S1 as the slave library of S1.

What about the damaged main library?

(1) the former master database is re-change master to the new master, then the master-slave consistency is checked and the data is repaired.

(2) if the data of the original master database is damaged, it needs to be re-added to the new master as a slave database.

How to stop the write operation of the main library temporarily?

(1) change the password so that existing connections cannot be affected. Remember to kill all existing connections.

(2) flush table with read lock

(3) enable parameter super_read_only=on

(4) seal port 3306 through the firewall

Summary:

In an environment of one master and two slaves, if the master database is down, how to select a slave database as the switching process (the whole process is about 1-5 seconds if the whole process is fast):

(1) change the password of the main library and disconnect all connections

(2) judge the synchronization of S1 and S2

(3) Election new bank

(4) put the write traffic on the new master

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.