MySQL中show slave status關(guān)鍵值和MGRrelay log的清理策略

這篇文章主要為大家展示了“MySQL中show slave status關(guān)鍵值和MGRrelay log的清理策略”，內(nèi)容簡(jiǎn)而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領(lǐng)大家一起研究并學(xué)習(xí)一下“MySQL中show slave status關(guān)鍵值和MGRrelay log的清理策略”這篇文章吧。

一、show slave status關(guān)鍵值

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event (IO THREAD狀態(tài))
Master_Host: 192.168.99.42(通道主庫(kù)IP)
Master_User: repl991(同步用戶名)
Master_Port: 3310(端口)
Connect_Retry: 60(IO thread 重試時(shí)間)
Master_Log_File: binlog.000002(讀取到的binlog文件名)
Read_Master_Log_Pos: 44688(讀取到的binlog位置)
Relay_Log_File: test-relay-bin.000003(本IO THREAD replay文件名)
Relay_Log_Pos: 24360(當(dāng)前寫到relay log的位置)
Relay_Master_Log_File: binlog.000002(SQL thread應(yīng)用到的binlog的文件名)
Slave_IO_Running: Yes (IO thread狀態(tài)是否正常)
Slave_SQL_Running: Yes (SQL THREAD狀態(tài)是否正常)
...
Last_Errno: 0 (錯(cuò)誤號(hào))
Last_Error: (錯(cuò)誤信息)
Skip_Counter: 0
Exec_Master_Log_Pos: 44688 (SQL thread應(yīng)用到的binlog的位置)
Relay_Log_Space: 44599 (relay 日志文件總的的大小)
...
Seconds_Behind_Master: 0 (延遲)
...
Master_Server_Id: 9942 (主庫(kù)的server_id)
Master_UUID: 0e733bbb-a594-11e7-ab07-52540020afef (主庫(kù)的UUID)
Master_Info_File: /root/mysql5.7.14/percona-server-5.7.14-7/mysql-test/var/mysqld.1/data/master.info (master info的文件或者表)
SQL_Delay: 0(是否配置延時(shí)應(yīng)用)
...
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates (sql thread狀態(tài))
Master_Retry_Count: 86400 (可以重試的總次數(shù))
...
Retrieved_Gtid_Set: 0e733bbb-a594-11e7-ab07-52540020afef:9-129 (收到的GTID)
Executed_Gtid_Set: 0e733bbb-a594-11e7-ab07-52540020afef:1-129 (應(yīng)用的GTID)
Auto_Position: 1 (是否通過GTID自動(dòng)尋找binlog位置)
...
Channel_Name: 通道名
...

二、MGR relaylog 清理策略

普通sql線程刪除relay文件

#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

MGR group_replication_applier通道的sql線程刪除relay文件

#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

可以看出沒有什么區(qū)別。實(shí)際上都是一樣的還是通過sql線程應(yīng)用了event后刪除。當(dāng)然有如下代碼 受到參數(shù)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

• flush logs等命令不會(huì)影響MGR的repay log包括recovery通道和applier通道

以上是“MySQL中show slave status關(guān)鍵值和MGRrelay log的清理策略”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道！