MySQL MGR集群单主模式的自动搭建和自动化故障修(3)


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)

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/ad335d474e4dadec631e368ca9b0561e.html