def update_switch_flag(mysql_conn, group_id):
logger.info(
"Update switch flag in db_cfg_mysql_dr for group %s in progress..." %
(group_id))
# get current switch flag
str = 'select is_switch from db_cfg_mysql_dr where id= %s' % (group_id)
is_switch = mysql.GetSingleValue(mysql_conn, str)
logger.info("The current switch flag is: %s" % (is_switch))
if is_switch == 0:
str = """update db_cfg_mysql_dr set is_switch = 1 where id = %s""" % (
group_id)
else:
str = """update db_cfg_mysql_dr set is_switch = 0 where id = %s""" % (
group_id)
is_succ = mysql.ExecuteSQL(mysql_conn, str)
if is_succ == 1:
common.log_db_op_process(mysql_conn, db_type, group_id, 'FAILOVER',
'容灾组更新状态成功', 100, 2)
logger.info(
"Update switch flag in db_cfg_mysql_dr for group %s successfully."
% (group_id))
else:
logger.info(
"Update switch flag in db_cfg_mysql_dr for group %s failed." %
(group_id))
def unlock_tables(p_conn, pri_id):
result=-1
logger.info("Unlock tables for database: %s" %(pri_id))
str='''unlock tables; '''
res=mysql.ExecuteSQL(p_conn, str)
if res == 1:
logger.info("Unlock tables successfully !")
common.log_db_op_process(mysql_conn, db_type, group_id, 'SWITCHOVER', '解锁数据库成功', 0, 2)
else:
logger.error("Unlock tables failed !")
common.log_db_op_process(mysql_conn, db_type, group_id, 'SWITCHOVER', '解锁数据库失败', 0, 2)
return result
def lock_tables(p_conn, pri_id):
result=-1
logger.info("Flush tables with read lock for database: %s" %(pri_id))
str='''flush tables with read lock; '''
res=mysql.ExecuteSQL(p_conn, str)
if res == 1:
logger.info("Flush tables with read lock successfully !")
common.log_db_op_process(mysql_conn, db_type, group_id, 'SWITCHOVER', '锁定数据库成功', 0, 2)
else:
logger.error("Flush tables with read lock failed !")
common.log_db_op_process(mysql_conn, db_type, group_id, 'SWITCHOVER', '锁定数据库失败', 0, 2)
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
logger.info("The master database is: " + p_nopass_str + ", the id is: " + str(pri_id))
logger.info("The slave database is: " + s_nopass_str + ", the id is: " + str(sta_id))
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)
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
#print s_host,s_port,s_username,s_password
s_str = """select concat(host, ':', port) from db_cfg_sqlserver where id=%s; """ %(sta_id)
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')
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
