def skip_gtid_on_slave(conn_dict,gtid):
sql_1 = 'stop group_replication;'
sql_2 = '''set gtid_next='{0}';'''.format(gtid)
sql_3 = 'begin;'
sql_4 = 'commit;'
sql_5 = '''set gtid_next='automatic';'''
try:
execute_noquery(conn_dict, sql_1)
execute_noquery(conn_dict, sql_2)
execute_noquery(conn_dict, sql_3)
execute_noquery(conn_dict, sql_4)
execute_noquery(conn_dict, sql_5)
except:
raise
def get_group_replication_status(conn_dict):
sql = '''select MEMBER_STATE from performance_schema.replication_group_members
where (MEMBER_HOST = '{0}' or ifnull(MEMBER_HOST,'') = '')
AND (MEMBER_PORT={1} or ifnull(MEMBER_PORT,'') ='') ; '''.format(conn_dict["host"], conn_dict["port"])
result = execute_query(conn_dict,sql)
if result:
return result
else:
return None
def check_replication_group_members(conn_dict):
print('-------------------------------------------------------')
result = execute_query(conn_dict, " select * from performance_schema.replication_group_members; ")
if result:
column = result[0].keys()
current_row = ''
for key in column:
current_row += str(key) + " "
print(current_row)
for row in result:
current_row = ''
for key in row.values():
current_row += str(key) + " "
print(current_row)
print('-------------------------------------------------------')
def auto_fix_mgr_error(conn_master_dict,conn_slave_dict):
group_replication_status = get_group_replication_status(conn_slave_dict)
if(group_replication_status[0]["MEMBER_STATE"]=="ERROR" or group_replication_status[0]["MEMBER_STATE"] == "OFFLINE"):
print(conn_slave_dict["host"]+str(conn_slave_dict["port"])+'------>'+group_replication_status[0]["MEMBER_STATE"])
print("auto fixing......")
while 1 > 0:
master_gtid_list = get_gtid(conn_master_dict)
slave_gtid_list = get_gtid(conn_slave_dict)
master_executed_gtid_value = int((master_gtid_list[-1]["Executed_Gtid_Set"]).split("-")[-1])
slave_executed_gtid_value = int(slave_gtid_list[-1]["Executed_Gtid_Set"].split("-")[-1])
slave_executed_gtid_prefix = slave_gtid_list[-1]["Executed_Gtid_Set"].split(":")[0]
slave_executed_skiped_gtid = slave_executed_gtid_value + 1
if (master_executed_gtid_value > slave_executed_gtid_value):
print("skip gtid and restart group replication,skiped gtid is "
+ slave_gtid_list[-1]["Executed_Gtid_Set"].split(":")[-1].split("-")[0]
+ ":"+str(slave_executed_skiped_gtid))
slave_executed_skiped_gtid = slave_executed_gtid_prefix+":"+str(slave_executed_skiped_gtid)
skip_gtid_on_slave(conn_slave_dict,slave_executed_skiped_gtid)
time.sleep(10)
start_group_replication(conn_slave_dict)
if(get_group_replication_status(conn_slave_dict)[0]["MEMBER_STATE"]=="ONLINE"):
print("mgr cluster fixed,back to normal")
break
else:
start_group_replication(conn_slave_dict)
if(get_group_replication_status(conn_slave_dict)[0]["MEMBER_STATE"]=="ONLINE"):
print("mgr cluster fixed,back to normal")
break
elif (group_replication_status[0]['MEMBER_STATE'] == 'ONLINE'):
print("mgr cluster is normal,nothing to do")
check_replication_group_members(conn_slave_dict)