Show slave status\g Read_master_log_pos and Relay_log_pos (size) relationship

Just to clarify, there are three sets of file/position-coordinates in show SLAVE STATUS:

1) The position, on the master, from which the I/O thread is reading:master_log_file/read_master_log_pos.-----reading the master from the library relative to the main library The location of the library's binary log, which is 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 library, is from the Library of SQL Line to Location

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, is Where to execute from the SQL thread of the library

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----above two lines represent IO threads, relative to the main library binaries

relay_log_file:mysql-relay.003990

relay_log_pos:25077069----The top two lines represent the SQL thread, relative to the relay log file from the library

relay_master_log_file:mysql-bin-m.000329

.....

exec_master_log_pos:863936961---The top two lines represent the SQL thread, relative to the main library

(in order to facilitate the demo, I put the line up earlier.)

relay_log_space:25092264---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 will always be greater than the value of exec_master_log_pos (and possibly equal): Because a value represents an IO thread, a value represents a SQL thread, and the SQL thread is definitely behind the IO thread. (Of course, IO Threads and SQL threads have to read and write the same file, otherwise the comparison loses its meaning).

In Binlog, XID represents the transaction number that was committed. Now let's take a look at the master library, verify that Whether 863936961 of the mysql-bin-m.000329 file in the main library has the same SQL statement as 25077069 from the library's mysql-relay.003990 file.

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, 943 22428, 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/*!*/;

Then look at the Relaylog from 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, 943 22428, 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 above log, you can see that the contents of Binlog and Realy-log are the same, except for the offset at the beginning at position. Because the offset is always relative to the file itself, the main library is relative to the binlog itself, from the library to the Relay-log itself. You can also see that After each query statement, have a XID event that commits the transaction, also indicates that it is automatically submitted in MySQL, and the system is automatically submitted after each statement has been executed. Then in a row based replication, you might see multiple binlog statements that correspond to a commit, Nature shows that this is based on row replication.

Another point is that the main library and the corresponding location from the library event size is the same, such as the above:

25077342-25077069 (event size from library) = 863937234-863936961 (event size on main library)

Otherwise, the description of the Relay-log from the library is lost, it is possible that the operating system cache is missing, or the MySQL exception crash caused the loss of data in Relay-log buffer. Then you need to reset the master and slave synchronization.