MySQL中show slave status关键值和MGRrelay log的清理策略

2025-03-14 技术教程

这篇文章主要为大家展示了“MySQL中show slave status关键值和MGRrelay log的清理策略”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“MySQL中show slave status关键值和MGRrelay log的清理策略”这篇文章吧。

一、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文件

#0MYSQL_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#10x000000000185073finMYSQL_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#20x0000000001892224innext_event(rli=0x37e9b30)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/rpl_slave.cc:9299#30x0000000001886443inexec_relay_log_event(thd=0x7fff24000950,rli=0x37e9b30)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/rpl_slave.cc:5145#40x000000000188d092inhandle_slave_sql(arg=0x3790690)at/root/mysql5.7.14/percona-server-5.7.14-7/sql/rpl_slave.cc:7387#50x0000000001d7b4b0inpfs_spawn_thread(arg=0x7fff3c01b5f0)at/root/mysql5.7.14/percona-server-5.7.14-7/storage/perfschema/pfs.cc:2188#60x0000003f74807aa1instart_thread()from/lib64/libpthread.so.0#70x0000003f740e8bcdinclone()from/lib64/libc.so.6

MGR group_replication_applier通道的sql线程删除relay文件

#0MYSQL_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#10x0000000001870333inMYSQL_BIN_LOG::purge_first_log(this=0x7fff9c0562f0,rli=0x7fff9c0558a0,included=false)at/root/softm/percona-server-5.7.22-22/sql/binlog.cc:6259#20x00000000018b6bcfinnext_event(rli=0x7fff9c0558a0)at/root/softm/percona-server-5.7.22-22/sql/rpl_slave.cc:9480#30x00000000018aa5a6inexec_relay_log_event(thd=0x7fff80007e10,rli=0x7fff9c0558a0)at/root/softm/percona-server-5.7.22-22/sql/rpl_slave.cc:5193#40x00000000018b1980inhandle_slave_sql(arg=0x7fff9c04e850)at/root/softm/percona-server-5.7.22-22/sql/rpl_slave.cc:7543#50x0000000001932112inpfs_spawn_thread(arg=0x7fff9803ff60)at/root/softm/percona-server-5.7.22-22/storage/perfschema/pfs.cc:2190#60x00007ffff7bc6aa1instart_thread()from/lib64/libpthread.so.0#70x00007ffff6719bcdinclone()from/lib64/libc.so.6

可以看出没有什么区别。实际上都是一样的还是通过sql线程应用了event后删除。当然有如下代码受到参数relay_log_purge控制

if(relay_log_purge){/*purge_first_logwillproperlysetuprelaylogcoordinatesinrli.Ifthegroup'scoordinatesareequaltotheevent'scoordinates(i.e.therelaylogwasnotrotatedinthemiddleofagroup),wecanpurgethisrelaylogtoo.Wedoulonglongandstringcomparisons,thismaybeslowbut-purgingthelastrelaylogisnice(itcansave1GBofdisk),soweliketodetectthecasewherewecandoit,andgiventhis,-Iseenobetterdetectionmethod-purge_first_logisnotcalledthatoften*/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="Errorpurgingprocessedlogs";gotoerr;}DBUG_PRINT("info",("next_eventgroupmaster%s%lugrouprelay%s%luevent%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通道

以上是“MySQL中show slave status关键值和MGRrelay log的清理策略”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注亿速云行业资讯频道！