MySQL：show slave status 關鍵值和MGRrelay log的清理策略

 

gaopengtttt

2018.11.27 16:13*   字數 437   閱讀 7 評論 0 喜歡 0

編輯文章

簡單記錄一下，有朋友問 @richard
一、show slave status關鍵值

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event (IO THREAD狀態)
Master_Host: 192.168.99.42(通道主庫IP)
Master_User: repl991(同步使用者名稱)
Master_Port: 3310(埠)
Connect_Retry: 60(IO thread 重試時間)
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(當前寫到relay log的位置)
Relay_Master_Log_File: binlog.000002(SQL thread應用到的binlog的檔名)
Slave_IO_Running: Yes (IO thread狀態是否正常)
Slave_SQL_Running: Yes (SQL THREAD狀態是否正常)
...
Last_Errno: 0 (錯誤號)
Last_Error: (錯誤資訊)
Skip_Counter: 0
Exec_Master_Log_Pos: 44688 (SQL thread應用到的binlog的位置)
Relay_Log_Space: 44599 (relay 日誌檔案總的的大小)
...
Seconds_Behind_Master: 0 (延遲)
...
Master_Server_Id: 9942 (主庫的server_id)
Master_UUID: 0e733bbb-a594-11e7-ab07-52540020afef (主庫的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(是否配置延時應用)
...
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates (sql thread狀態)
Master_Retry_Count: 86400 (可以重試的總次數)
...
Retrieved_Gtid_Set: 0e733bbb-a594-11e7-ab07-52540020afef:9-129 (收到的GTID)
Executed_Gtid_Set: 0e733bbb-a594-11e7-ab07-52540020afef:1-129 (應用的GTID)
Auto_Position: 1 (是否透過GTID自動尋找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

可以看出沒有什麼區別。實際上都是一樣的還是透過sql執行緒應用了event後刪除。當然有如下程式碼 受到引數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等命令不會影響MGR的repay log包括recovery通道和applier通道

作者微訊號碼：gp_22389860