import pymysql
import time
from warnings import filterwarnings
error_message=u"Error 'Unknown table 'fandb.t_error_maker'' on query. Default database: 'fandb'. Query: 'DROP TABLE `t_error_maker` /* generated by server */'"

class Fandb:
    def __init__(self,host,port,user,password,db,charset='utf8mb4'):
        self.host = host
        self.port = int(port)
        self.user = user
        self.password = password
        self.db = db
        self.charset = charset
        try:
            self.conn=pymysql.connect(host=self.host, port=self.port, user=self.user,password=self.password,db=self.db,charset=self.charset)
            self.cursor=self.conn.cursor()
            self.diccursor=self.conn.cursor(pymysql.cursors.DictCursor)
        except pymysql.Error as e:
            print("MySQL Error %d: %s" %(e.args[0],e.args[1]))

    #制造复制异常函数
    def error_maker(self):
        self.cursor.execute("set session sql_log_bin=0;")
        self.cursor.execute("create table t_error_maker(id int)")
        self.cursor.execute("set session sql_log_bin=1")
        self.cursor.execute("drop table t_error_maker")
    #获取slave status
    def get_slave_status(self):
        self.diccursor.execute("show slave status;")
        result = self.diccursor.fetchone()
        return result
    #获取master status
    def get_master_status(self):
        self.diccursor.execute("show master status;")
        result = self.diccursor.fetchone()
        return result
    #执行change master语句
    def change_master(conn,change_string):
        self.cursor.execute("stop slave;")
        self.cursor.execute(change_string)
        self.cursor.execute("start slave;")
    #修复slave错误
    def repair_slave(conn):
        self.cursor.execute("set global sql_slave_skip_counter=1;")
        self.cursor.execute("start slave sql_thread;")
    #设置read only
    def set_read_only(conn,switch):
        if switch == 'on':
            self.cursor.execute("set global read_only=on;")
        elif switch == 'off':
            self.cursor.execute("set global read_only=off;")
    #启停 sql_thread
    def set_sql_thread(conn,switch):
        if switch == 'off':
            self.cursor.execute("stop slave sql_thread;")
        elif switch == 'on':
            self.cursor.execute("start slave sql_thread;")

    def commit(self):
        self.conn.commit()

    def close(self):
        self.cursor.close()
        self.diccursor.close()
        self.conn.close()

if __name__ == '__main__':
    filterwarnings('ignore',category=pymysql.Warning)
    db_35 = Fandb('172.16.65.35','3306','root','mysql','fandb','performance_schema')
    db_36 = Fandb('172.16.65.36','3306','root','mysql','fandb','performance_schema')
    db_39 = Fandb('10.0.1.39','3306','root','mysql','fandb','performance_schema')
    db_40 = Fandb('10.0.1.40','3306','root','mysql','fandb','performance_schema')
    db_41 = Fandb('10.0.1.41','3306','root','mysql','fandb','performance_schema')

    print(u"连接36 制造复制异常函数")
    db_36.error_maker()
    print(u"判断 39 40 41是否都因为drop t_error_maker停止")
    while True:
        Last_SQL_Error_39 = db_39.get_slave_status()['Last_SQL_Error']
        Last_SQL_Error_40 = db_40.get_slave_status()['Last_SQL_Error']
        Last_SQL_Error_41 = db_41.get_slave_status()['Last_SQL_Error']
        if Last_SQL_Error_39 == Last_SQL_Error_40 == Last_SQL_Error_41 == error_message:
            break
        else:
            time.sleep(1)

    #获取40 master status 以供39 41切换
    print(u"获取40 master status 以供39 41切换")
    master_status_40 = db_40.get_master_status()
    File_40,Position_40 = master_status_40['File'],master_status_40['Position']

    change_string = """
    change master to
    master_host='10.0.1.40',
    master_port=3306,
    master_user='repl',
    master_password='repl',
    master_log_file='%s',
    master_log_pos=%d;
    """ % (File_40,Position_40)
    #39 41切换到40
    print(u"39,41切换到40")
    db_39.change_master(change_string)
    print(u"39切换到40成功")
    db_41.change_master(change_string)
    print(u"41切换到40成功")
    #修复40 slave
    print(u"修复40 slave")
    db_40.repair_slave()

    #35 设置read only
    db_35.set_read_only(switch='on')

    #判断35 36 40 是否同步
    while True:
      res35 = db_35.get_slave_status();
      res36 = db_36.get_slave_status();
      res40 = db_40.get_slave_status();
      File_35,Position_35 = res35['File'],res35['Position']
      File_36,Position_36 = res36['Relay_Master_Log_File'],res36['Exec_Master_Log_Pos']
      File_40,Position_40 = res40['Relay_Master_Log_File'],res40['Exec_Master_Log_Pos']
      if File_35 == File_36 == File_40 and Position_35 == Position_36 == Position_40:
          break
      else:
          time.sleep(1)

    #停40 sql_thread
    db_40.set_sql_thread('off')
    #写接入36
    #35 read_only=off
    db_35.set_read_only(switch='off')
    master_status_40 = db_40.get_master_status()
    File_40,Position_40 = master_status_40['File'],master_status_40['Position']
    change_string = """
    change master to
    master_host='10.0.1.40',
    master_port=3306,
    master_user='repl',
    master_password='repl',
    master_log_file='%s',
    master_log_pos=%d;
    """ % (File_40,Position_40)
    db_35.change_master(change_string)
    #起40 sql_thread
    db_40.set_sql_thread('on')



:
