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

This article mainly shows you the "key values of show slave status and MGRrelay log cleaning strategy in MySQL", which is easy to understand and clear. I hope it can help you solve your doubts. Let me lead you to study and learn the cleaning strategy of show slave status key values and MGRrelay log in MySQL.

1. Show slave status key value

* * 1. Row *

Slave_IO_State: Waiting for master to send event (IO THREAD status)

Master_Host: 192.168.99.42 (channel master library IP)

Master_User: repl991 (synchronous user name)

Master_Port: 3310 (port)

Connect_Retry: 60 (IO thread retry time)

Master_Log_File: binlog.000002 (the name of the binlog file read)

Read_Master_Log_Pos: 44688 (binlog location read)

Relay_Log_File: test-relay-bin.000003 (name of this IO THREAD replay file)

Relay_Log_Pos: 24360 (currently written to relay log)

Relay_Master_Log_File: binlog.000002 (the file name of the binlog to which SQL thread is applied)

Slave_IO_Running: Yes (whether the IO thread status is normal)

Slave_SQL_Running: Yes (whether the SQL THREAD status is normal)

...

Last_Errno: 0 (error number)

Last_Error: (error message)

Skip_Counter: 0

Exec_Master_Log_Pos: 44688 (location of binlog to which SQL thread is applied)

Relay_Log_Space: 44599 (total size of relay log files)

...

Seconds_Behind_Master: 0 (delay)

...

Master_Server_Id: 9942 (server_id of the main library)

Master_UUID: 0e733bbb-a594-11e7-ab07-52540020afef (UUID of the main library)

Master_Info_File: / root/mysql5.7.14/percona-server-5.7.14-7/mysql-test/var/mysqld.1/data/master.info (master info file or table)

SQL_Delay: 0 (whether to configure delayed application)

...

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates (sql thread status)

Master_Retry_Count: 86400 (total number of times you can retry)

...

Retrieved_Gtid_Set: 0e733bbb-a594-11e7-ab07-52540020afef:9-129 (GTID received)

Executed_Gtid_Set: 0e733bbb-a594-11e7-ab07-52540020afef:1-129 (GTID of the application)

Auto_Position: 1 (whether to find the binlog location automatically through GTID)

...

Channel_Name: Channel name

...

II. MGR relaylog cleanup strategy

Normal sql thread deletes relay file

# 0 MYSQL_BIN_LOG::purge_logs (this=0x37ea570, to_log=0x7fff2400d1a0 ". / test-relay-bin.000004", included=false, need_lock_index=false, need_update_threads=false, decrease_log_space=0x37ec628, auto_purge=true) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:5950#1 0x000000000185073f in MYSQL_BIN_LOG::purge_first_log (this=0x37ea570, rli=0x37e9b30 Included=false) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:5818#2 0x0000000001892224 in next_event (rli=0x37e9b30) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/rpl_slave.cc:9299#3 0x0000000001886443 in exec_relay_log_event (thd=0x7fff24000950 Rli=0x37e9b30) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/rpl_slave.cc:5145#4 0x000000000188d092 in handle_slave_sql (arg=0x3790690) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/rpl_slave.cc:7387#5 0x0000000001d7b4b0 in pfs_spawn_thread (arg=0x7fff3c01b5f0) at / root/mysql5.7.14/percona-server-5.7.14-7/storage/perfschema/pfs.cc:2188# 6 0x0000003f74807aa1 in start_thread () from / lib64/libpthread.so.0#7 0x0000003f740e8bcd in clone () from / lib64/libc.so.6

The SQL thread of the MGR group_replication_applier channel deletes the relay file

# 0 MYSQL_BIN_LOG::purge_logs (this=0x7fff9c0562f0, to_log=0x7fff800160b0 ". / gp4-relay-bin-group_replication_applier.000006", included=false, need_lock_index=false, need_update_threads=false, decrease_log_space=0x7fff9c058320, auto_purge=true) at / root/softm/percona-server-5.7.22-22/sql/binlog.cc:6391#1 0x0000000001870333 in MYSQL_BIN_LOG::purge_first_log (this=0x7fff9c0562f0, rli=0x7fff9c0558a0 Included=false) at / root/softm/percona-server-5.7.22-22/sql/binlog.cc:6259#2 0x00000000018b6bcf in next_event (rli=0x7fff9c0558a0) at / root/softm/percona-server-5.7.22-22/sql/rpl_slave.cc:9480#3 0x00000000018aa5a6 in exec_relay_log_event (thd=0x7fff80007e10 Rli=0x7fff9c0558a0) at / root/softm/percona-server-5.7.22-22/sql/rpl_slave.cc:5193#4 0x00000000018b1980 in handle_slave_sql (arg=0x7fff9c04e850) at / root/softm/percona-server-5.7.22-22/sql/rpl_slave.cc:7543#5 0x0000000001932112 in pfs_spawn_thread (arg=0x7fff9803ff60) at / root/softm/percona-server-5.7.22-22/storage/perfschema/pfs.cc:2190#6 0x00007ffff7bc6aa1 in start_thread () from / lib64/ Libpthread.so.0#7 0x00007ffff6719bcd in clone () from / lib64/libc.so.6

You can see that there is no difference. In fact, they are all the same or deleted after event is applied through the sql thread. Of course, the following code is controlled by the parameter relay_log_purge

If (relay_log_purge) {/ * purge_first_log will properly set up relay log coordinates in rli. If the group's coordinates are equal to the event's coordinates (i.e. The relay log was not rotated in the middle of a group), we can purge this relay log too. We do ulonglong and string comparisons, this may be slow but-purging the last relay log is nice (it can save 1GB of disk), so we like to detect the case where we can do it, and given this,-I see no better detection method-purge_first_log is not called that often * / if (rli- > relay_log.purge_first_log (rli) Rli- > get_group_relay_log_pos () = = rli- > get_event_relay_log_pos () & &! strcmp (rli- > get_group_relay_log_name (), rli- > get_event_relay_log_name () {errmsg = "Error purging processed logs" Goto err } DBUG_PRINT ("info", ("next_event group master% s% lu group relay% s% lu event% s% lu\ n", rli- > get_group_master_log_name (), (ulong) rli- > get_group_master_log_pos (), rli- > get_group_relay_log_name (), (ulong) rli- > get_group_relay_log_pos () Rli- > get_event_relay_log_name (), (ulong) rli- > get_event_relay_log_pos ()) } else

Commands such as flush logs will not affect the repay log of MGR, including recovery channel and applier channel

These are all the contents of this article entitled "show slave status key values and MGRrelay log cleaning Strategies in MySQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, 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.