MySQL5.7集群MGR启动报ERROR 3092 (HY000)该怎么办,针对这个问题,这篇文章详细介绍了相对应的分析和解答,希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。

MySQL5.7版本MGR启动时报:

ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.

查看告警日志:

2020-03-04T07:48:58.797180Z574478[Warning]Plugingroup_replicationreported:'[GCS]AutomaticallyaddingIPv4localhostaddresstothewhitelist.Itismandatorythatitisadded.'2020-03-04T07:48:58.844857Z574714[ERROR]SlaveSQLforchannel'group_replication_applier':Error'ThisfunctionhasnoneofDETERMINISTIC,NOSQL,orREADSSQLDATAinitsdeclarationandbinaryloggingisenabled(you*might*wanttousethelesssafelog_bin_trust_function_creatorsvariable)'onquery.Defaultdatabase:''.Query:'CREATEDEFINER=`root`@`192.168.187.%`FUNCTION`GetDistance`(`lat1`float,`lng1`float,`lat2`float,`lng2`float)RETURNSfloatbeginDECLAREearth_padiusfloatDEFAULT6378.137;DECLAREradLat1floatDEFAULT0;DECLAREradLat2floatDEFAULT0;DECLAREafloatDEFAULT0;DECLAREbfloatDEFAULT0;DECLAREsfloatDEFAULT0;setradLat1=Radian(lat1);setradLat2=Radian(lat2);seta=radLat1-radLat2;setb=Radian(lng1)-Radian(lng2);sets=2*Asin(Sqrt(power(sin(a/2),2)+cos(radLat1)*cos(radLat2)*power(sin(b/2),2)));sets=s*earth_padius;sets=Round(s*10000)/10000;re2020-03-04T07:48:58.844919Z574714[Warning]Slave:ThisfunctionhasnoneofDETERMINISTIC,NOSQL,orREADSSQLDATAinitsdeclarationandbinaryloggingisenabled(you*might*wanttousethelesssafelog_bin_trust_function_creatorsvariable)Error_code:14182020-03-04T07:48:58.844938Z574714[ERROR]Plugingroup_replicationreported:'Theapplierthreadexecutionwasaborted.Unabletoprocessmoretransactions,thismemberwillnowleavethegroup.'2020-03-04T07:48:58.844963Z574714[ERROR]Errorrunningquery,slaveSQLthreadaborted.Fixtheproblem,andrestarttheslaveSQLthreadwith"SLAVESTART".Westoppedatlog'FIRST'position0.2020-03-04T07:48:58.844984Z574711[ERROR]Plugingroup_replicationreported:'FatalerrorduringexecutionontheApplierprocessofGroupReplication.Theserverwillnowleavethegroup.'2020-03-04T07:48:58.845030Z574711[ERROR]Plugingroup_replicationreported:'[GCS]Thememberisalreadyleavingorjoiningagroup.'2020-03-04T07:48:58.845073Z574711[ERROR]Plugingroup_replicationreported:'Unabletoconfirmwhethertheserverhasleftthegroupornot.Checkperformance_schema.replication_group_memberstocheckgroupmembershipinformation.'2020-03-04T07:49:03.586818Z0[ERROR]Plugingroup_replicationreported:'Therewasapreviouspluginerrorwhilethememberjoinedthegroup.Thememberwillnowexitthegroup.'2020-03-04T07:57:00.047106Z574478[Warning]Plugingroup_replicationreported:'[GCS]AutomaticallyaddingIPv4localhostaddresstothewhitelist.Itismandatorythatitisadded.'2020-03-04T07:57:01.992027Z575211[Warning]StoringMySQLusernameorpasswordinformationinthemasterinforepositoryisnotsecureandisthereforenotrecommended.PleaseconsiderusingtheUSERandPASSWORDconnectionoptionsforSTARTSLAVE;seethe'STARTSLAVESyntax'intheMySQLManualformoreinformation.

从监控告警日志内容可以看出来,是由于函数操作不符合规范造成的MGR集群掉线,具体日志描述如下:

This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *mi

ght* want to use the less safe log_bin_trust_function_creators variable) Error_code: 1418

遇到这种问题可以从两个方面去解决。第一种方法就是开发人员按照规范创建符合标准的函数;第二种方法就是配置数据库参数,将log_bin_trust_function_creators配置为开启。

此处,开发人员也是选择了将log_bin_trust_function_creators参数配置为开启,但是在一个节点开启了,其他节点未开启,最终导致其他节点出现上述错误信息。

以下是部分处理过程,仅供参考:

mysql>select*fromperformance_schema.replication_group_members;+---------------------------+--------------------------------------+-------------+-------------+--------------+|CHANNEL_NAME|MEMBER_ID|MEMBER_HOST|MEMBER_PORT|MEMBER_STATE|+---------------------------+--------------------------------------+-------------+-------------+--------------+|group_replication_applier|48713421-9261-11e9-9799-005056851cf9|db2|3306|ERROR|+---------------------------+--------------------------------------+-------------+-------------+--------------+1rowinset(0.00sec)mysql>mysql>mysql>mysql>stopgroup_replication;QueryOK,0rowsaffected(1.01sec)mysql>startgroup_replication;ERROR3092(HY000):Theserverisnotconfiguredproperlytobeanactivememberofthegroup.Pleaseseemoredetailsonerrorlog.mysql>showvariableslike'%func%';+---------------------------------+-------+|Variable_name|Value|+---------------------------------+-------+|log_bin_trust_function_creators|OFF|+---------------------------------+-------+1rowinset(0.01sec)mysql>mysql>mysql>setgloballog_bin_trust_function_creators=1;QueryOK,0rowsaffected(0.00sec)mysql>showvariableslike'%func%';+---------------------------------+-------+|Variable_name|Value|+---------------------------------+-------+|log_bin_trust_function_creators|ON|+---------------------------------+-------+1rowinset(0.00sec)mysql>mysql>mysql>stopgroup_replication;QueryOK,0rowsaffected(0.00sec)mysql>startgroup_replication;QueryOK,0rowsaffected(2.90sec)mysql>select*fromperformance_schema.replication_group_members;+---------------------------+--------------------------------------+-------------+-------------+--------------+|CHANNEL_NAME|MEMBER_ID|MEMBER_HOST|MEMBER_PORT|MEMBER_STATE|+---------------------------+--------------------------------------+-------------+-------------+--------------+|group_replication_applier|2ff8ea98-9128-11e9-91dd-0819a62578bd|db1|3306|ONLINE||group_replication_applier|48713421-9261-11e9-9799-005056851cf9|db2|3306|ONLINE||group_replication_applier|9c245a8c-9262-11e9-82d6-005056853a0e|db3|3306|ONLINE|+---------------------------+--------------------------------------+-------------+-------------+--------------+3rowsinset(0.00sec)mysql>

关于MySQL5.7集群MGR启动报ERROR 3092 (HY000)该怎么办问题的解答就分享到这里了,希望以上内容可以对大家有一定的帮助,如果你还有很多疑惑没有解开,可以关注亿速云行业资讯频道了解更多相关知识。