小编给大家分享一下MySQL中slave端Retrieved_Gtid_Set读取改进的示例分析，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！

今天朋友问我这样一个问题@K.I.S.S，在官方文档中有这样一段描述：

WhenusingGTIDs,theslavetellsthemasterwhichtransactionsithasalreadyreceived,executed,orboth.Tocomputethisset,itreadstheglobalvalueofgtid_executedandthevalueoftheRetrieved_gtid_setcolumnfromSHOWSLAVESTATUS.TheGTIDofthelasttransmittedtransactionisincludedinRetrieved_gtid_setonlywhenthefulltransactionisreceived.Theslavecomputesthefollowingset:UNION(@@global.gtid_executed,Retrieved_gtid_set)PriortoMySQL5.7.5,theGTIDofthelasttransmittedtransactionwasincludedinRetrieved_gtid_setevenifthetransactionwasonlypartiallytransmitted,andthelastreceivedGTIDwassubtractedfromthisset.(Bug#17943188)Thus,theslavecomputedthefollowingset:UNION(@@global.gtid_executed,Retrieved_gtid_set-last_received_GTID)

问为什么要减去last_received_GTID。

对于这个问题我先查看了一下这个bug到底修复了什么问题如下：

BUG#17943188SHOWSLAVESTATUS/RETRIEVED_GTID_SETMAYHAVEPARTIALTRXORMISSCOMPLETETRXProblem:=======TheSHOWSLAVESTATUScommandcontainsthecolumnRETRIEVED_GTID_SET.ThisissupposedtocontainthesetofGTIDsthatexistintherelaylog.However,thefieldisupdatedwhentheslavereceiverthread(I/Othread)receivesaGtid_log_event,whichhappensatthebeginningofthetransaction.IftheI/Othreadgetsdisconnectedinthemiddleofatransaction,RETRIEVED_GTID_SETcancontainaGTIDforatransactionthatisonlypartiallyreceivedintherelaylog.Thistransactionwillsubsequentlyberolledback,soitiswrongtopretendthatthetransactionisthere.Typicalfail-overalgorithmsuseRETRIEVED_GTID_SETtodeterminewhichslavehasreceivedthemosttransactionstopromotetheslavetoamaster.Thisistruefore.g.themysqlfailoverutility.WhenRETRIEVED_GTID_SETcancontainpartiallytransmittedtransactions,thefail-overutilitycanchoosethewrongslavetopromote.Thiscanleadtodatacorruptionlater.Thismeansthatevenifsemi-syncisenabled,transactionsthathavebeenacknowledgedbyoneslavecanbelost.Fix:===Itwasimplementedatransactionboundariesparserthatwillgiveinformationabouttransactionboundariesofaneventstreambasedontheeventtypesandtheirqueries(whentheyareQuery_log_event).AseventsarequeuedbytheI/Othread,itfeedstheMaster_infotransactionboundaryparser.TheslaveI/OrecoveryalsousesthetransactionparsertodetermineifagivenGTIDcanbeaddedtotheRetrieved_Gtid_Setornot.WhentheeventparserisinGTIDstatebecauseaGtid_log_eventwasqueued,theevent'sGTIDisn'taddedtotheretrievedlistyet.ItisstoredinanauxiliaryGTIDvariable.Afterflushinganeventintotherelaylog,theIOthreadverifiesifthetransactionparserisnotinsideatransactionanymore(meaningthatthelasteventofthetransactionhasbeenflushed).Iftransactionparserisoutsideatransaction,theI/OthreadverifiesifaGTIDwasstoredinthestartofthetransaction,addingittotheretrievedlist,ensuringthatallthetransactionhasarrivedandwasflushedtotherelaylog.Also,beforethispatch,aftertheI/Othreadflushedasinglereceivedeventintotherelaylog,itwaspossibletorotatetherelaylogifthecurrentrelaylogfilesizeexceededmax_binlog_size/max_relaylog_size.Afterthispatch,whenGTIDsareenabledweonlyallowthisrotationbysizeifthetransactionparserisnotinthemiddleofatransaction.Note:ThecurrentpatchremovedthechangesforBUG#17280176,asitalsodealtwithsimilarprobleminadifferentway.

大概就是说对于某些I/O线程并没有完整传输的Gtid事物记录到了RETRIEVED_GTID_SET中这会导致比较严重问题，因为某些监控工具根据这个来判断是否切换之类的，因此我们加入了一个事物边界分析器来判断事物是否完整传输，如果完整传输才记录到RETRIEVED_GTID_SET中这是5.7.5过后加入的。总的说来为了进行两个问题的修复：

1、最后一个gtid 事物是否完整。

2、跨越多个relay log的binlog 得到正确的gtid集合。

其实修改得非常多，不一一列举，有兴趣可以自己看看commit 9dab9dad975d09b8f37f33bf3c522d36fdf1d0f9，这里列举几个我看了的地方。

1、在 MYSQL_BIN_LOG::init_gtid_sets加入了如下逻辑

/*IfweuseGTIDsandhavepartialtransactionsontherelaylog,mustcheckifitendsonnextrelaylogfiles.WealsoneedtofeedtheboundaryparserwiththerestoftherelaylogtoputitinthecorrectstatebeforereceivingneweventsfromthemasterinthecaseofGTIDautopositioningbedisabled.*/if(is_relay_log){/*Supposethefollowingrelaylog:rl-bin.000001|rl-bin.000002|rl-bin.000003|rl-bin-000004---------------+---------------+---------------+---------------PREV_GTIDS|PREV_GTIDS|PREV_GTIDS|PREV_GTIDS(empty)|(UUID:1)|(UUID:1)|(UUID:1)---------------+---------------+---------------+---------------GTID(UUID:1)|QUERY(INSERT)|QUERY(INSERT)|XID---------------+---------------+---------------+---------------QUERY(CREATE|TABLEt1...)|---------------+GTID(UUID:2)|---------------+QUERY(BEGIN)|---------------+AsitisimpossibletodeterminethecurrentRetrieved_Gtid_SetbyonlylookingtothePREVIOUS_GTIDSonthelastrelaylogfile,andscanningeventsonit,wetriedtofindarelaylogfilethatcontainsatleastoneGTIDeventduringthebackwardssearch.Intheexample,wewillfindaGTIDonlyinrl-bin.000001,astheUUID:2transactionwasspannedacross4relaylogfiles.Thetransactionspanningcanbecausedby"FLUSHRELAYLOGS"commandsonslavewhileitisqueuingthetransaction.So,inordertocorrectlyaddUUID:2intoRetrieved_Gtid_Set,weneedtoparsetherelaylogstartingonthefilewefoundthelastGTIDqueuedtoknowifthetransactionwasfullyretrievedornot.*//*Adjustthereverseiteratortopointtotherelaylogfileweneedtostartparsing,asitwasincrementedaftergeneratingtherelaylogfilename.*/rit--;//回退一个文件因为前面为rit++做操作exp:1<2<3<{4}<5<6<7--->1<2<3<4<{5}<6<7/*Resetthetransactionparserbeforefeedingitwithevents*/trx_parser->reset();gtid_partial_trx->clear();DBUG_PRINT("info",("Iteratingforwardsthroughrelaylogs,""updatingtheRetrieved_Gtid_Setandupdating""IOthreadtrxparserbeforestart."));for(it=find(filename_list.begin(),filename_list.end(),*rit);it!=filename_list.end();it++)//从匹配的位置继续向后{constchar*filename=it->c_str();DBUG_PRINT("info",("filename='%s'",filename));if(read_gtids_and_update_trx_parser_from_relaylog(filename,all_gtids,true,trx_parser,gtid_partial_trx)){error=1;gotoend;}}}}

其实这一块也说明了解决的什么问题，我们发现一个事物的binlog event 在relay log中是可以跨文件的。而在bin log中是不能跨文件的。仅仅判断最后一个gtid priv event 是不正确的。因此需要这样修改。

2、其次加入了边界分析器Transaction_boundary_parser类

这个类是完全新加入的，这里是其中的一些状态：

numenum_event_boundary_type{EVENT_BOUNDARY_TYPE_ERROR=-1,/*Gtid_log_event*/EVENT_BOUNDARY_TYPE_GTID=0,/*Query_log_event(BEGIN),Query_log_event(XASTART)*/EVENT_BOUNDARY_TYPE_BEGIN_TRX=1,/*Xid,Query_log_event(COMMIT),Query_log_event(ROLLBACK),XA_Prepare_log_event*/EVENT_BOUNDARY_TYPE_END_TRX=2,/*Query_log_event(XAROLLBACK)*/EVENT_BOUNDARY_TYPE_END_XA_TRX=3,/*User_var,IntvarandRand*/EVENT_BOUNDARY_TYPE_PRE_STATEMENT=4,/*AllotherQuery_log_eventsandallotherDMLevents(Rows,Load_data,etc.)*/EVENT_BOUNDARY_TYPE_STATEMENT=5,/*AllnonDDL/DMLevents:Format_desc,Rotate,Incident,Previous_gtids,Stop,etc.*/EVENT_BOUNDARY_TYPE_IGNORE=6};/*Internalstatesforparsingastreamofevents.DDLhastheformat:DDL-1:[GTID]DDL-2:[User][Intvar][Rand]DDL-3:QueryDMLhastheformat:DML-1:[GTID]DML-2:Query(BEGIN)DML-3:StatementsDML-4:(Query(COMMIT)|Query([XA]ROLLBACK)|Xid|Xa_prepare)*/enumenum_event_parser_state{/*NONEissetafterDDL-3orDML-4*/EVENT_PARSER_NONE,/*GTIDissetafterDDL-1orDML-1*/EVENT_PARSER_GTID,/*DDLissetafterDDL-2*/EVENT_PARSER_DDL,/*DMLissetafterDML-2*/EVENT_PARSER_DML,/*ERRORissetwhenevertheabovepatternisnotfollowed*/EVENT_PARSER_ERROR};

3、read_gtids_and_update_trx_parser_from_relaylog函数新增

这个函数是完全新加入的就是为了完成所说的功能，在read_gtids_and_update_trx_parser_from_relaylog中我看到对文件所有event进行了读取，并且用switch进行了不同event类型的处理，但是具体没有细看。但是最后看到对于对于是否加入retrieve gtid的判断如下：

/*Ifwereachedtheendofatransactionafterstoringit'sGTIDingtid_partial_trxvariable,itistimetoaddthisGTIDtotheretrieved_gtidssetbecausethetransactioniscompleteandthereisnoneedforaskingthistransactionagain.*/if(trx_parser->is_not_inside_transaction()){if(!gtid_partial_trx->is_empty()){DBUG_PRINT("info",("AddingGtidtoRetrieved_Gtid_Setasthe""transactionwascompletedat""relaylogfile'%s':Gtid(%d,%lld).",filename,gtid_partial_trx->sidno,gtid_partial_trx->gno));retrieved_gtids->_add_gtid(gtid_partial_trx->sidno,gtid_partial_trx->gno);gtid_partial_trx->clear();}}四、初始化的时候retrieve gtid到底如何计算

实际上就在现在看来应该就是读取 relay_log的最后一个gtid事物(gtid event或者gtid priv event)同时需要判断此gtid事物是否完整。对于官方文档给出的UNION(@@global.gtid_executed, Retrieved_gtid_set - last_received_GTID)，个人觉得这里的 last_received_GTID应该是经过判断的，如果完整则不减，如果不完整则减去。

以上是“MySQL中slave端Retrieved_Gtid_Set读取改进的示例分析”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注亿速云行业资讯频道！