Shulou(Shulou.com)05/31 Report--

This article shows you how to use Read_Master_Log_Pos and Relay_Log in show slave status\ G. the content is concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Ust to clarify, there are three sets of file/position coordinates in SHOW SLAVE STATUS:

1) The position, ON THE MASTER, from which the I The position O thread is reading:Master_Log_File/Read_Master_Log_Pos. -relative to the main library, the location where the binary log of the main library is read from the library is the IO thread

2) The position, IN THE RELAY LOGS, at which the SQL thread is executing:Relay_Log_File/Relay_Log_Pos-relative to the slave library, it is the location to which the SQL thread of the library executes

3) The position, ON THE MASTER, at which the SQL thread is executing:Relay_Master_Log_File/Exec_Master_Log_Pos-relative to the main library, it is the location where the SQL thread of the library executes

Numbers 2) and 3) are the same thing, but one is on the slave and the other is on the master.

Mysql > show slave status\ G

Master_Log_File: mysql-bin-m.000329

Read_Master_Log_Pos: 863952156-the above two lines represent IO threads, relative to the binaries of the main library

Relay_Log_File: mysql-relay.003990

Relay_Log_Pos: 25077069-the above two lines represent the SQL thread, relative to the secondary log file of the slave library

Relay_Master_Log_File: mysql-bin-m.000329

.

Exec_Master_Log_Pos: 863936961 Murray-the above two lines represent the SQL thread, relative to the main library

I advanced the above line in order to facilitate the demonstration.

Relay_Log_Space: 25092264 Murray-the size of the current relay-log file

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

As you can see from the above, read_master_log_pos is always greater than the value of exec_master_log_pos (or it may be equal): because one value represents iothread and one value represents SQL thread; sql thread must be after iothread. Of course, iothread and SQL thread have to read and write the same file, otherwise the comparison will be meaningless.

In binlog, Xid represents the transaction number submitted. Now let's go to the master and slave library to verify whether there is the same sql statement at 863936961 of the mysql-bin-m.000329 file of the master library and 25077069 of the mysql-relay.003990 file of the slave library.

First, look at the binlog of the main library:

# at 863936961

# 100111 20:11:39 server id 115000 end_log_pos 863937234 Query thread_id=515886 exec_time=0 error_code=0

Use mall00/!/

UPDATE mall00.t_item_sid88 SET item_end_time = 1263816699, item_is_online = 1, item_status = 1 WHERE iid IN (94322390, 94322428, 94322452,94322473,94322506,94322532,94322604,94322641,94322670,94322706) /! /

# at 863937234

# 100111 20:11:39 server id 115000 end_log_pos 863937261 Xid = 1225244590

COMMIT/!/

# at 863937261

# 100111 20:11:39 server id 115000 end_log_pos 863937457 Query thread_id=515886 exec_time=0error_code=0

SET timestamp 1263211899 /

Let's take a look at the relaylog of the library:

# at 25077069

# 100111 20:11:39 server id 115000 end_log_pos863937234 Query thread_id=515886 exec_time=0 error_code=0

Use mall00/!/

UPDATE mall00.t_item_sid88 SET item_end_time = 1263816699, item_is_online = 1, item_status = 1 WHERE iid IN (94322390, 94322428, 94322452,94322473,94322506,94322532,94322604,94322641,94322670,94322706) /! /

# at 25077342

# 100111 20:11:39 server id 115000 end_log_pos 863937261 Xid = 1225244590

COMMIT/!/

From the log above, you can see that the contents of binlog and realy-log are the same, except for the offset at the beginning of the at. Because the offset is always relative to the file itself, to the main library relative to the binlog itself, and from the library to the relay-log itself. You can also see that after each query statement, there is an event of Xid, which commits the transaction, which also indicates that it is automatically committed in mysql. After each statement is executed, the system commits automatically. Then in row-based replication, you may see that multiple binlog statements correspond to one commit, which naturally indicates that this is row-based replication.

Another thing is that the event size of the corresponding location of the master and slave libraries is the same, such as the one above:

25077342-25077069 (event size on the slave library) = 863937234-863936961 (event size on the primary library)

Otherwise, the relay-log of the slave library is lost, the operating system cache may be lost, or the mysql exception crash may cause the data loss in relay-log buffer. Then you need to reset the master-slave synchronization at this time.

The above is how Read_Master_Log_Pos and Relay_Log are used in show slave status\ G. have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.