Test verifies the difference between opening and closing log_slave_updates parameters from the library

1. Basic Information

Main Library:
ip:10.16.24.107 port:3376
Server-id = 1073377
data_dir:/data/mysql/mysql3376/data/
Base_dir:/usr/local/mysql
Version: MySQL 5.6.29-log
Binlog_format:row

From library:
ip:10.16.24.108 port:3376
Server-id = 1083376
data_dir:/data/mysql/mysql3376/data/
Base_dir:/usr/local/mysql
Version: MySQL 5.6.29-log
Binlog_format:row

2. Scene One: From the library has set log_slave_updates parameters

To view from the library:
(product) Root@localhost [lots]> show variables like ' log_slave_updates ';
+-------------------+-------+
| variable_name | Value |
+-------------------+-------+
| Log_slave_updates | On |
+-------------------+-------+
1 row in Set (0.01 sec)

operation on Main Library:
INSERT into test values (4, ' Zeng4 '), (5, ' Zeng5 '), (6, ' zeng6 ');

View from library:
Product) Root@localhost [(none)]> show Slave status\g
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:10.16.24.107
Master_user:repl
master_port:3376
Connect_retry:60
master_log_file:mysql-bin.000064
read_master_log_pos:761
relay_log_file:relay-bin.000007
relay_log_pos:504
relay_master_log_file:mysql-bin.000064
Slave_io_running:yes
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:761
relay_log_space:1254
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:0
Master_ssl_verify_server_cert:no
last_io_errno:0
Last_io_error:
last_sql_errno:0
Last_sql_error:
Replicate_ignore_server_ids:
master_server_id:1073376
Master_uuid:36af7e42-f4fc-11e5-8b08-0050568a0bcb
Master_info_file:/data/mysql/mysql3376/data/master.info
sql_delay:0
Sql_remaining_delay:null
Slave_sql_running_state:slave has read all relay log; Waiting for the slave I/O thread to update it
master_retry_count:86400
Master_bind:
Last_io_error_timestamp:
Last_sql_error_timestamp:
MASTER_SSL_CRL:
Master_ssl_crlpath:
Retrieved_gtid_set:
Executed_gtid_set:
auto_position:0
1 row in Set (0.00 sec)

Analysis Log on Main library:
MYSQLBINLOG-VV mysql-bin.000064--stop-position=761--base64-output=decode-rows

#160429 14:50:39 Server ID 1073376 end_log_pos 730 CRC32 0xab87e600 the write_rows:table ID flags:stmt_end_f
### INSERT into ' lots '. ' Test '
### SET
### @1=4/* INT meta=0 nullable=1 is_null=0 * *
### @2= ' Zeng4 '/* varstring meta=60 nullable=1
### INSERT into ' lots '. ' Test '
### SET
### @1=5/* INT meta=0 nullable=1 is_null=0 * *
### @2= ' zeng5 '/* varstring meta=60 nullable=1
### INSERT into ' lots '. ' Test '
### SET
### @1=6/* INT meta=0 nullable=1 is_null=0 * *
### @2= ' zeng6 '/* varstring meta=60 nullable=1
# at 730

Confirm that the insert operation location for the main library is mysql-bin.000064 730.

We then analyze the relay logs from the library:
MYSQLBINLOG-VV relay-bin.000007--stop-position=504--base64-output=decode-rows
#160429 14:50:39 Server ID 1073376 end_log_pos 730 CRC32 0xab87e600 the write_rows:table ID flags:stmt_end_f
### INSERT into ' lots '. ' Test '
### SET
### @1=4/* INT meta=0 nullable=1 is_null=0 * *
### @2= ' Zeng4 '/* varstring meta=60 nullable=1
### INSERT into ' lots '. ' Test '
### SET
### @1=5/* INT meta=0 nullable=1 is_null=0 * *
### @2= ' zeng5 '/* varstring meta=60 nullable=1
### INSERT into ' lots '. ' Test '
### SET
### @1=6/* INT meta=0 nullable=1 is_null=0 * *
### @2= ' zeng6 '/* varstring meta=60 nullable=1
# at 473

The above view has two position:730 and 473 in the insert Operation location, where 730 is read from Master Binlog record insert operation position, 473 is insert operation in relay log position.

Then analyze the Binlog from the library:

MYSQLBINLOG-VV--base64-output=decode-rows mysql-bin.000011--start-datetime= ' 2016-04-29 14:50:00 '

#160429 14:50:39 Server ID 1073376 end_log_pos 927 CRC32 0x2c41c940 the write_rows:table ID flags:stmt_end_f
### INSERT into ' lots '. ' Test '
### SET
### @1=4/* INT meta=0 nullable=1 is_null=0 * *
### @2= ' Zeng4 '/* varstring meta=60 nullable=1
### INSERT into ' lots '. ' Test '
### SET
### @1=5/* INT meta=0 nullable=1 is_null=0 * *
### @2= ' zeng5 '/* varstring meta=60 nullable=1
### INSERT into ' lots '. ' Test '
### SET
### @1=6/* INT meta=0 nullable=1 is_null=0 * *
### @2= ' zeng6 '/* varstring meta=60 nullable=1
# at 927

after setting the log_slave_updates parameter, the insert operation is recorded from the Binlog in the library, and the position is 927.

Scenario Two: No log_slave_updates parameters are set from the library

(product) Root@localhost [(none)]> show variables like ' log_slave_updates ';
+-------------------+-------+
| variable_name | Value |
+-------------------+-------+
| Log_slave_updates | Off |
+-------------------+-------+
1 row in Set (0.00 sec)

Main Library execution: delete from Test where id=6;

View from library Status:

1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:10.16.24.107
Master_user:repl
master_port:3376
Connect_retry:60
master_log_file:mysql-bin.000064
read_master_log_pos:960
relay_log_file:relay-bin.000009
relay_log_pos:482
relay_master_log_file:mysql-bin.000064
Slave_io_running:yes
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:960
relay_log_space:649
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:0
Master_ssl_verify_server_cert:no
last_io_errno:0
Last_io_error:
last_sql_errno:0
Last_sql_error:
Replicate_ignore_server_ids:
master_server_id:1073376
Master_uuid:36af7e42-f4fc-11e5-8b08-0050568a0bcb
Master_info_file:/data/mysql/mysql3376/data/master.info
sql_delay:0
Sql_remaining_delay:null
Slave_sql_running_state:slave has read all relay log; Waiting for the slave I/O thread to update it
master_retry_count:86400
Master_bind:
Last_io_error_timestamp:
Last_sql_error_timestamp:
MASTER_SSL_CRL:
Master_ssl_crlpath:
Retrieved_gtid_set:
Executed_gtid_set:
auto_position:0
1 row in Set (0.00 sec)

Analysis Log on Main library:
MYSQLBINLOG-VV mysql-bin.000064--stop-position=960--base64-output=decode-rows
#160429 17:24:25 server ID 1073376 end_log_pos 929 CRC32 0x15e16d68 the delete_rows:table ID flags:stmt_end_f
### DELETE from ' lots '. ' Test '
### WHERE
### @1=6/* INT meta=0 nullable=1 is_null=0 * *
### @2= ' zeng6 '/* varstring meta=60 nullable=1
# at 929
#160429 17:24:25 Server ID 1073376 end_log_pos 960 CRC32 0x489880cc = 54
commit/*!*/;
Confirm that the main library's delete operation location is mysql-bin.000064 929.

We then analyze the relay logs from the library:
MYSQLBINLOG-VV--base64-output=decode-rows relay-bin.000009--start-datetime= ' 2016-04-29 17:24:00 '

#160429 17:24:25 server ID 1073376 end_log_pos 929 CRC32 0x15e16d68 the delete_rows:table ID flags:stmt_end_f
### DELETE from ' lots '. ' Test '
### WHERE
### @1=6/* INT meta=0 nullable=1 is_null=0 * *
### @2= ' zeng6 '/* varstring meta=60 nullable=1
# at 451
#160429 17:24:25 Server ID 1073376 end_log_pos 960 CRC32 0x489880cc = 54
commit/*!*/;

The above view has two position:929 and 451 in the insert Operation location, where 929 is read from master Binlog record insert operation position, 451 is insert operation in relay log position.

Then analyze the Binlog from the library:
MYSQLBINLOG-VV--base64-output=decode-rows mysql-bin.000012--start-datetime= ' 2016-04-29 17:24:00 '

[mysql@mvxl0783 logs]$ mysqlbinlog-vv--base64-output=decode-rows mysql-bin.000012
/*!50530 SET @ @SESSION. pseudo_slave_mode=1*/;
/*!40019 SET @ @session. max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE =@ @COMPLETION_TYPE, completion_type=0*/;
DELIMITER/*!*/;
# at 4
#160429 17:21:05 server ID 1083376  end_log_pos CRC32 0xa8d9d152    start:binlog V 4, Server v 5.6.29-log created 160429 17:21:05 at startup
# Warning:this Binlog be either in-use or were not closed properly.
rollback/*!*/;
DELIMITER;
# End of log file
ROLLBACK/* Added by Mysqlbinlog/
/*!50003 SET completion_type= @OLD_COMPLETION_TYPE */;/*!50530 SET @ @SESSION. pseudo_slave_mode=0*/;
from the above analysis view, when the Log_slave_updates parameter from the library is not turned on, the Binlog from the library does not record the operation records from the main library. only the log_slave_updates is turned on, and the operation log of the main library synchronization is logged from the library Binlog.