def failover2primary(mysql_conn, db_type, group_id, db_name, s_conn, sta_id): logger.info("Failover database to primary in progress...") result=-1 # get database role str='''select m.mirroring_role from sys.database_mirroring m, sys.databases d where M.mirroring_guid is NOT NULL AND m.database_id = d.database_id AND d.name = '%s'; ''' %(db_name) role=sqlserver.GetSingleValue(s_conn, str) common.log_db_op_process(mysql_conn, db_type, group_id, 'FAILOVER', '获取数据库角色成功', 30, 2) logger.info("The current database role is: %s (1:PRIMARY; 2:STANDBY)" %(role)) if role==2: common.log_db_op_process(mysql_conn, db_type, group_id, 'FAILOVER', '验证数据库角色成功', 40, 2) logger.info("Now we are going to failover standby database %s to primary." %(sta_id)) #设置自动提交,否则alter database执行报错 s_conn.autocommit(True) common.log_db_op_process(mysql_conn, db_type, group_id, 'FAILOVER', '镜像库正在切换成主库...', 60, 2) str='''ALTER DATABASE %s SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS; ''' %(db_name) res=sqlserver.ExecuteSQL(s_conn, str) s_conn.autocommit(False) # 重新验证切换后数据库角色 str='''select m.mirroring_role from sys.database_mirroring m, sys.databases d where M.mirroring_guid is NOT NULL AND m.database_id = d.database_id AND d.name = '%s'; ''' %(db_name) new_role=sqlserver.GetSingleValue(s_conn, str) if new_role==1: common.log_db_op_process(mysql_conn, db_type, group_id, 'FAILOVER', '镜像库切换成主库成功', 90, 2) logger.info("Failover standby database to primary successfully.") result = 0 else: common.log_db_op_process(mysql_conn, db_type, group_id, 'FAILOVER', '镜像库切换成主库失败,请根据相关日志查看原因', 90, 2) logger.info("Failover standby database to primary failed.") result = -1 else: common.update_db_op_reason(mysql_conn, db_type, group_id, 'FAILOVER', '验证数据库角色失败,当前数据库不是镜像库,无法切换到主库') common.log_db_op_process(mysql_conn, db_type, group_id, 'FAILOVER', '验证数据库角色失败,当前数据库不是镜像库,无法切换到主库', 40) logger.error("You can not failover primary database to primary!") return result
def rebuild_replication(mysql_conn, db_type, group_id, p_conn, pri_id, s_conn, sta_id, s_host, s_port, s_username, s_password): result=-1 logger.info("Rebuild replication in progress...") # unlock tables logger.debug("Unlock tables for database: %s first" %(pri_id)) unlock_tables(p_conn, pri_id) # get master status master_info=mysql.GetSingleRow(s_conn, 'show master status;') if master_info: master_binlog_file=master_info[0] master_binlog_pos=master_info[1] str='''stop slave; ''' res=mysql.ExecuteSQL(p_conn, str) logger.debug("Stop slave") str='''change master to master_host='%s',master_port=%s,master_user='******',master_password='******',master_log_file='%s',master_log_pos=%s; '''%(s_host, s_port, s_username, s_password, master_binlog_file, master_binlog_pos) logger.debug("Change master command: %s" %(str)) res=mysql.ExecuteSQL(p_conn, str) str='''start slave; ''' res=mysql.ExecuteSQL(p_conn, str) logger.debug("Start slave") slave_info=mysql.GetSingleRow(p_conn, 'show slave status;') if slave_info: logger.info("Rebuild replication successfully !") common.log_db_op_process(mysql_conn, db_type, group_id, 'SWITCHOVER', '重建复制关系成功', 0, 2) result=0 else: common.update_db_op_reason(mysql_conn, db_type, group_id, 'SWITCHOVER', '重建复制关系失败') common.log_db_op_process(mysql_conn, db_type, group_id, 'SWITCHOVER', '重建复制关系失败', 0, 2) logger.error("Rebuild replication failed !") result=-1 return result
def switch2master(mysql_conn, db_type, group_id, p_conn, s_conn, sta_id): result=-1 logger.info("Switchover database to master in progress...") # get database role role=mysql.IsSlave(s_conn) common.log_db_op_process(mysql_conn, db_type, group_id, 'SWITCHOVER', '获取数据库角色成功', 0, 2) logger.info("The current database role is: %s (0:MASTER; 1:SLAVE)" %(role)) # get database version if role==1: common.log_db_op_process(mysql_conn, db_type, group_id, 'SWITCHOVER', '验证从库数据库角色成功', 0, 2) # get master status m_binlog_file="" m_binlog_pos=-1 master_info = mysql.GetSingleRow(p_conn, 'show master status;') if master_info: m_binlog_file=master_info[0] m_binlog_pos=master_info[1] logger.debug("Master: master_binlog_file: %s" %(m_binlog_file)) logger.debug("Master: master_binlog_pos: %s" %(m_binlog_pos)) # check slave status slave_info=mysql.GetSingleRow(s_conn, 'show slave status;') if slave_info: current_binlog_file=slave_info[9] current_binlog_pos=slave_info[21] master_binlog_file=slave_info[5] master_binlog_pos=slave_info[6] logger.debug("Slave: current_binlog_file: %s" %(current_binlog_file)) logger.debug("Slave: current_binlog_pos: %s" %(current_binlog_pos)) logger.debug("Slave: master_binlog_file: %s" %(master_binlog_file)) logger.debug("Slave: master_binlog_pos: %s" %(master_binlog_pos)) if (current_binlog_file == master_binlog_file and m_binlog_file==master_binlog_file and current_binlog_pos==master_binlog_pos and master_binlog_pos==m_binlog_pos): # can switch now logger.info("Now we are going to switch database %s to master." %(sta_id)) common.log_db_op_process(mysql_conn, db_type, group_id, 'SWITCHOVER', '正在将从库切换成主库...', 0, 0) str='''stop slave io_thread; ''' res=mysql.ExecuteSQL(s_conn, str) logger.debug("Stop slave io_thread.") str='''stop slave; ''' res=mysql.ExecuteSQL(s_conn, str) logger.debug("Stop slave.") str='''reset slave all; ''' res=mysql.ExecuteSQL(s_conn, str) logger.debug("Reset slave all.") logger.info("Switchover slave to master successfully.") common.log_db_op_process(mysql_conn, db_type, group_id, 'SWITCHOVER', '从库已经成功切换成主库', 0, 2) result=0 else: logger.error("Check binlog position failed.") common.log_db_op_process(mysql_conn, db_type, group_id, 'SWITCHOVER', '验证数据库binlog复制位置失败', 0, 2) result=-1 else: logger.info("Check slave status failed.") common.log_db_op_process(mysql_conn, db_type, group_id, 'SWITCHOVER', '从库切换主库失败', 0, 2) result=-1 else: common.update_db_op_reason(mysql_conn, db_type, group_id, 'SWITCHOVER', '验证数据库角色失败,当前数据库不是从库,不能切换到主库') common.log_db_op_process(mysql_conn, db_type, group_id, 'SWITCHOVER', '验证数据库角色失败,当前数据库不是从库,不能切换到主库', 0, 2) logger.error("You can not switchover a master database to master!") result=-1 return result
db_type = "mysql" try: common.db_op_lock(mysql_conn, db_type, group_id, 'SWITCHOVER') # 加锁 common.init_db_op_instance(mysql_conn, db_type, group_id, 'SWITCHOVER') #初始化切换实例 # connect to mysql p_conn = mysql.ConnectMysql_T(p_host,p_port,p_username,p_password) s_conn = mysql.ConnectMysql_T(s_host,s_port,s_username,s_password) if p_conn is None: common.log_db_op_process(mysql_conn, db_type, group_id, 'SWITCHOVER', '连接主库失败,请根据相应日志查看原因', 0, 3) logger.error("Connect to primary database error, exit!!!") common.update_db_op_reason(mysql_conn, db_type, group_id, 'SWITCHOVER', '连接主库失败') common.update_db_op_result(mysql_conn, db_type, group_id, 'SWITCHOVER', '-1') sys.exit(2) if s_conn is None: common.log_db_op_process(mysql_conn, db_type, group_id, 'SWITCHOVER', '连接从库失败,请根据相应日志查看原因', 0, 3) logger.error("Connect to standby database error, exit!!!") common.update_db_op_reason(mysql_conn, db_type, group_id, 'SWITCHOVER', '连接从库失败') common.update_db_op_result(mysql_conn, db_type, group_id, 'SWITCHOVER', '-1') sys.exit(2) # 正式开始切换 try:
s_nopass_str = mysql.GetSingleValue(mysql_conn, s_str) logger.info("The standby database is: " + s_nopass_str + ", the id is: " + str(sta_id)) db_type = "sqlserver" try: common.db_op_lock(mysql_conn, db_type, group_id, 'FAILOVER') # 加锁 common.init_db_op_instance(mysql_conn, db_type, group_id, 'FAILOVER') #初始化切换实例 # connect to sqlserver s_conn = sqlserver.ConnectMssql(s_host,s_port,s_username,s_password) if s_conn is None: common.log_db_op_process(mysql_conn, db_type, group_id, 'FAILOVER', '连接备库失败,请根据相应日志查看原因', 10, 3) logger.error("Connect to standby database error, exit!!!") common.update_db_op_reason(mysql_conn, db_type, group_id, 'FAILOVER', '连接备库失败') common.update_db_op_result(mysql_conn, db_type, group_id, 'FAILOVER', '-1') sys.exit(2) try: common.log_db_op_process(mysql_conn, db_type, group_id, 'FAILOVER', '准备执行灾难切换', 20, 2) res = failover2primary(mysql_conn, db_type, group_id, db_name, s_conn, sta_id) if res ==0: update_switch_flag(mysql_conn, group_id) common.gen_alert_sqlserver(sta_id, 1, db_name) # generate alert common.update_db_op_result(mysql_conn, db_type, group_id, 'FAILOVER', '0') else: common.update_db_op_result(mysql_conn, db_type, group_id, 'FAILOVER', res)
def switch_mirror(mysql_conn, db_type, group_id, db_name, p_conn, s_conn, pri_id): result = -1 logger.info("Switchover database to physical standby in progress...") # get database role str = '''select m.mirroring_role from sys.database_mirroring m, sys.databases d where M.mirroring_guid is NOT NULL AND m.database_id = d.database_id AND d.name = '%s'; ''' % (db_name) role = sqlserver.GetSingleValue(p_conn, str) common.log_db_op_process(mysql_conn, db_type, group_id, 'SWITCHOVER', '获取数据库角色成功', 20, 2) logger.info("The current database role is: %s (1:PRIMARY; 2:STANDBY)" % (role)) # get mirror status str = '''select m.mirroring_state from sys.database_mirroring m, sys.databases d where M.mirroring_guid is NOT NULL AND m.database_id = d.database_id AND d.name = '%s'; ''' % (db_name) mirror_status = sqlserver.GetSingleValue(p_conn, str) logger.info( "The current database mirror status is: %s (0:已挂起; 1:与其他伙伴断开; 2:正在同步; 3:挂起故障转移; 4:已同步; 5:伙伴未同步; 6:伙伴已同步;)" % (mirror_status)) # get database version #str="""SELECT @@VERSION""" #version=sqlserver.GetSingleValue(p_conn, str) if role == 1: common.log_db_op_process(mysql_conn, db_type, group_id, 'SWITCHOVER', '验证数据库角色成功', 30, 2) logger.info( "Now we are going to switch database %s to physical standby." % (pri_id)) if mirror_status == 4: common.log_db_op_process(mysql_conn, db_type, group_id, 'SWITCHOVER', '正在将主库切换成备库,可能会花费几分钟时间,请耐心等待...', 40, 0) #设置自动提交,否则alter database执行报错 p_conn.autocommit(True) logger.info("SET SAFETY FULL... ") #设置镜像传输模式为高安全模式 str = '''ALTER DATABASE %s SET SAFETY FULL; ''' % (db_name) res = sqlserver.ExecuteSQL(p_conn, str) common.log_db_op_process(mysql_conn, db_type, group_id, 'SWITCHOVER', '主库已经成功切换成高安全模式', 50, 2) logger.info("SET PARTNER FAILOVER begin... ") #切换镜像 str = '''ALTER DATABASE %s SET PARTNER FAILOVER;''' % (db_name) res = sqlserver.ExecuteSQL(p_conn, str) p_conn.autocommit(False) str = '''select m.mirroring_role from sys.database_mirroring m, sys.databases d where M.mirroring_guid is NOT NULL AND m.database_id = d.database_id AND d.name = '%s'; ''' % (db_name) new_role = sqlserver.GetSingleValue(p_conn, str) if new_role == 2: common.log_db_op_process(mysql_conn, db_type, group_id, 'SWITCHOVER', '主库已经成功切换成备库', 70, 2) logger.info("SET PARTNER FAILOVER successfully.") #设置镜像传输模式为高性能模式 s_conn.autocommit(True) str = '''ALTER DATABASE %s SET SAFETY OFF; ''' % (db_name) res = sqlserver.ExecuteSQL(s_conn, str) s_conn.autocommit(False) common.log_db_op_process(mysql_conn, db_type, group_id, 'SWITCHOVER', '主库已经成功切换成高性能模式', 90, 2) logger.info("SET SAFETY OFF successfully.") result = 0 else: common.log_db_op_process(mysql_conn, db_type, group_id, 'SWITCHOVER', '主库切换备库失败', 70, 2) logger.info("SET PARTNER FAILOVER failed.") result = -1 else: common.update_db_op_reason(mysql_conn, db_type, group_id, 'SWITCHOVER', '验证数据库角色失败,当前数据库不是主库,不能切换到备库') common.log_db_op_process(mysql_conn, db_type, group_id, 'SWITCHOVER', '验证数据库角色失败,当前数据库不是主库,不能切换到备库', 20, 2) logger.error("You can not switchover a standby database to standby!") return result