class GetStruct:
def __init__(self,host=None,port=None,user=None,passwd=None,socket=None):
self.connection = InitMyDB(mysql_host=host,mysql_port=port,mysql_user=user,mysql_password=passwd,unix_socket=socket).Init()
self.cur = self.connection.cursor()
self.cur.execute('set sql_log_bin=0;')
def GetColumn(self,*args):
'''args顺序 database、tablename'''
column_list = []
column_type_list = []
sql = 'select COLUMN_NAME,COLUMN_KEY,COLUMN_TYPE from INFORMATION_SCHEMA.COLUMNS where table_schema=%s and table_name=%s order by ORDINAL_POSITION;'
self.cur.execute(sql,args=args)
result = self.cur.fetchall()
pk_idex = []
for idex,row in enumerate(result):
column_list.append(row['COLUMN_NAME'])
column_type_list.append(row['COLUMN_TYPE'])
if row['COLUMN_KEY'] == 'PRI':
pk_idex.append(idex)
return column_list,pk_idex,column_type_list
def CreateTmp(self):
self.cur.execute('CREATE DATABASE IF NOT EXISTS dump2db;') #创建临时库
self.cur.execute('DROP TABLE IF EXISTS dump2db.dump_status;') #删除表
self.cur.execute('CREATE TABLE dump2db.dump_status(id INT,exe_gtid VARCHAR(50),logname VARCHAR(100),at_pos BIGINT,next_pos BIGINT,PRIMARY KEY(id));') #创建临时表
def SaveStatus(self,logname,at_pos,next_pos,server_id,gtid=None):
if gtid:
sql = 'INSERT INTO dump2db.dump_status(id,exe_gtid,logname,at_pos,next_pos) VALUES(%s,%s,%s,%s,%s) ON DUPLICATE KEY UPDATE exe_gtid=%s,logname=%s,at_pos=%s,next_pos=%s;'
self.cur.execute(sql, (server_id, gtid, logname, at_pos, next_pos, gtid, logname, at_pos, next_pos))
else:
sql = 'INSERT INTO dump2db.dump_status(id,logname,at_pos,next_pos) VALUES(%s,%s,%s,%s) ON DUPLICATE KEY UPDATE logname=%s,at_pos=%s,next_pos=%s;'
self.cur.execute(sql,(server_id,logname,at_pos,next_pos,logname,at_pos,next_pos))
self.connection.commit()
def close(self):
self.cur.close()
self.connection.close()
class desthread(GetSql):
def __init__(self, **kwargs):
super(desthread,
self).__init__(table_pk_idex_list=kwargs['table_pk_idex_list'],
table_struct_list=kwargs['table_struct_list'],
map_conf=kwargs['map_conf'],
queal_struct=kwargs['queal_struct'])
self.thread_lock = kwargs['thread_lock']
self.thread_lock_queue = kwargs['thread_lock_queue']
self.chunk_list_status_th = kwargs['chunk_list_status_th']
self.table_struct_list = kwargs['table_struct_list']
self.table_pk_idex_list = kwargs['table_pk_idex_list']
self.thread_id = kwargs['thread_id']
self.queue = kwargs['queue']
self.dhost, self.dport, self.duser, self.dpasswd = kwargs[
'dhost'], kwargs['dport'], kwargs['duser'], kwargs[
'dpassword'] # 目标库连接相关信息
self.binlog = kwargs['binlog'] # 是否在目标库记录binlog的参数
self.destination_conn = None
self.destination_cur = None
self.server_id = kwargs['server_id']
self.trancaction_list = [] # 已执行的事务sql,用于重连之后重新执行
self.queue_list = [] #队列列表，一个队列对应一个线程,[queue1,queue2]
self.group_list = [
] #存储每个队列分配的db.tbl组合，对应队列列表索引关系[['db:tbl','db:tbl'],['db:tbl']]
self.status_row = [
] #存储每个对应库、表是否已存在于标签表，用于改善insert ....update语句，降低死锁风险
self.error_queue = kwargs['error_queue']
self.kwargs = None
self.__init_slave_conn()
def __init_slave_conn(self):
'''
初始化同步所需的状态库、目标库的链接
:return:
'''
for i in range(60):
try:
self.destination_conn = InitMyDB(mysql_host=self.dhost,
mysql_port=self.dport,
mysql_user=self.duser,
mysql_password=self.dpasswd,
auto_commit=False).Init()
self.destination_cur = self.destination_conn.cursor()
if self.binlog is None:
self.destination_cur.execute(
'set sql_log_bin=0;') # 设置binlog参数
self.destination_cur.execute(
'SET SESSION wait_timeout = 2147483;')
break
except pymysql.Error as e:
Logging(msg=e.args, level='error')
time.sleep(1)
else:
Logging(msg='retry 60 time on slave db is failed,exist thread now',
level='error')
self.error_queue.put(1)
sys.exit()
def __restart_trancaction_sql(self):
'''
在提交时链接断开的情况下需要重新执行所有事务操作
:return:
'''
#self.__retry_connection_destion()
for row in self.trancaction_list:
if self.__raise_sql(sql=row[0], args=row[1], tmp=True) is None:
return None
return True
def __raise_sql(self, sql, args=[], retry=None, type=None, tmp=None):
'''
追加binlog数据到目标库
:param sql:
:param args:
:param retry: 为True代表如果报错需要重新执行整个事务，用于commit
:param type: 为True代表报错时需要重新执行sql，用于select
:param tmp: 为True代表该sql为重试得sql，如果报错直接返回
:return:
'''
try:
args = self.escape_string(args) if args else []
except:
Logging(msg=traceback.format_exc(), level='error')
self.error_queue.put(1)
try:
if sql == 'commit':
self.destination_conn.commit()
else:
self.destination_cur.execute(sql, args)
except pymysql.Error as e:
Logging(msg=e.args, level='error')
if tmp:
return None
if e.args[0] in RetryErrorCode:
Logging(msg='sql: {} args:{}'.format(sql, args),
level='error')
self.__raise_sql(sql=sql, args=args, retry=retry, type=type)
elif e.args[0] in ErrorCode:
self.__rollback()
if ErrorCode[e.args[0]]:
if sql == 'commit':
self.__retry_execute(retry=retry)
else:
self.__retry_execute(sql=sql,
args=args,
retry=retry,
type=type)
return True
Logging(msg='sql:{},values:{}'.format(sql, args), level='error')
Logging(msg=traceback.format_exc(), level='error')
return None
except:
Logging(msg='sql:{},values:{}'.format(sql, args), level='error')
Logging(msg=traceback.format_exc(), level='error')
return None
return True
def __rollback(self):
'''
关闭链接，用于兼容中间件
:return:
'''
try:
Logging(msg='close connection for db now !!!', level='warning')
self.destination_cur.close()
self.destination_conn.close()
except pymysql.Error as e:
Logging(msg=e.args, level='warning')
except:
Logging(msg=traceback.format_exc(), level='warning')
def __retry_execute(self, sql=None, args=None, retry=None, type=None):
'''
异常重试
:param sql: sql语句
:param args: 参数列表
:param type: 报错时是否需要重新执行该sql
:param retry: 需要全事务重新执行
:return:
'''
self.__retry_connection_destion()
if type:
self.__raise_sql(sql=sql, args=args, type=type, retry=retry)
return
elif retry is None:
Logging(msg='sql={},args={},retry={},type={}'.format(
sql, args, retry, type),
level='info')
Logging(
msg='retry execute trancaction list, list length {}'.format(
len(self.trancaction_list)),
level='info')
for row in self.trancaction_list:
Logging(msg='retry sql : {} args:{}'.format(row[0], row[1]),
level='warning')
if self.__raise_sql(row[0], row[1], tmp=True) is None:
self.__rollback()
self.__retry_execute()
return
return
elif retry:
Logging(msg='sql={},args={},retry={},type={}'.format(
sql, args, retry, type),
level='info')
Logging(msg='retry all trancaction list, list length {}'.format(
len(self.trancaction_list)),
level='info')
if self.__restart_trancaction_sql() is None:
self.__retry_execute(retry=True)
self.__raise_sql('commit', retry=True)
return
self.__raise_sql('commit', retry=True)
return
def __retry_connection_destion(self):
'''
目标库链接丢失重试60次，如果60次都失败将退出整个程序
使用30次的原因是有可能目标数据在发生宕机切换，如果30
秒都无法完成重连那表示数据库已经宕机或无法链接
:return:
'''
import time
for i in range(60):
time.sleep(1)
Logging(msg='connection to destination db try agian!!!',
level='info')
try:
self.destination_conn = InitMyDB(mysql_host=self.dhost,
mysql_port=self.dport,
mysql_user=self.duser,
mysql_password=self.dpasswd,
auto_commit=False).Init()
self.destination_cur = self.destination_conn.cursor()
Logging(msg='connection success!!!', level='info')
if self.binlog is None:
self.destination_cur.execute(
'set sql_log_bin=0;') # 设置binlog参数
self.destination_cur.execute(
'SET SESSION wait_timeout = 2147483;')
return True
except:
Logging(msg=traceback.format_exc(), level='error')
else:
Logging(
msg=
'try 60 times to fail for conncetion destination db,exist now',
level='error')
self.error_queue.put(1)
sys.exit()
def __check_stat(self, state, only_state=None):
if state:
if only_state:
return
else:
Logging(msg='desthread failed!!!!', level='error')
self.error_queue.put(1)
sys.exit()
def __check_queue(self):
'''
检查所有队列是否有满的,如果有一个满的表示可能阻塞了
二是为了防止某一个表落后很多
:return:
'''
if self.queue.full():
return False
return True
def __set_mark(self, db_name, tbl_name, gtid, gno_id, at_pos, binlog):
'''
标签操作、也是状态记录,记得该表执行到的gtid、position位置
:param db_name:
:param tbl_name:
:param gtid:
:param gno_id:
:param at_pos:
:return:
'''
_name = '{}:{}:{}'.format(db_name, tbl_name, self.server_id)
if _name in self.status_row:
sql = 'UPDATE repl_mark.mark_status SET gno_uid=%s,gno_id=%s,at_pos=%s,binlog=%s where db_name=%s and tbl_name=%s and server_id = %s;'
args = [
gtid, gno_id, at_pos, binlog, db_name, tbl_name, self.server_id
]
else:
sql = 'select 1 from repl_mark.mark_status where db_name = %s and tbl_name = %s and server_id=%s;'
if self.__raise_sql(sql=sql,
args=[db_name, tbl_name, self.server_id],
type=True):
_s = self.destination_cur.fetchall()
if _s:
sql = 'UPDATE repl_mark.mark_status SET gno_uid=%s,gno_id=%s,binlog=%s,at_pos=%s where db_name=%s and tbl_name = %s and server_id=%s;'
args = [
gtid, gno_id, binlog, at_pos, db_name, tbl_name,
self.server_id
]
else:
sql = 'INSERT INTO repl_mark.mark_status(db_name,tbl_name,gno_uid,gno_id,binlog,at_pos,server_id) VALUES(%s,%s,%s,%s,%s,%s,%s);'
args = [
db_name, tbl_name, gtid, gno_id, binlog, at_pos,
self.server_id
]
self.status_row.append(_name)
else:
Logging(
msg='execute sql [{}] error , exit now!!!!'.format(sql),
level='error')
self.error_queue.put(1)
sys.exit()
self.trancaction_list.append([sql, args])
self.__check_stat(self.__raise_sql(sql=sql, args=args))
def __get_fetch_all(self):
'''
重起获取所有库、表已执行的游标
:return:
'''
sql = 'SELECT db_name,tbl_name,gno_uid,gno_id,binlog,at_pos,server_id FROM repl_mark.mark_status'
self.__check_stat(self.__raise_sql(sql=sql, type=True))
result = self.destination_cur.fetchall()
fetch_value = {}
for row in result:
fetch_value['{}:{}:{}'.format(row['db_name'], row['tbl_name'],
row['server_id'])] = [
row['gno_uid'], row['gno_id'],
row['at_pos'], row['binlog']
]
return fetch_value
def __check_lock(self, db_tbl_name, lock_state=None):
'''
同一库、表顺序锁
:param db_tbl_name:
:return:
'''
if db_tbl_name in self.thread_lock_queue:
if lock_state:
self.thread_lock.release()
if self.thread_id in self.thread_lock_queue[db_tbl_name]:
while 1:
if self.thread_lock_queue[db_tbl_name][
0] == self.thread_id:
return True
time.sleep(0.001)
else:
self.thread_lock_queue[db_tbl_name].append(self.thread_id)
self.__check_lock(db_tbl_name=db_tbl_name)
else:
if lock_state:
self.thread_lock_queue[db_tbl_name] = [self.thread_id]
self.thread_lock.release()
self.__check_lock(db_tbl_name=db_tbl_name)
self.thread_lock.acquire()
self.__check_lock(db_tbl_name=db_tbl_name, lock_state=True)
def __enter__(self):
fetch_value = self.__get_fetch_all()
__mark_status = None
while 1:
if not self.queue.empty():
queue_value = self.queue.get()
db_tbl_name = queue_value[0]
trancaction_all = queue_value[1]
_uuid = queue_value[2]
if trancaction_all:
self.__check_lock(db_tbl_name)
for trancaction in trancaction_all:
tran_len = len(trancaction_all)
value_list = trancaction['value_list']
if value_list:
db_name, tbl_name = value_list[1], value_list[2]
gno_uid, gno_id, binlog, at_pos = trancaction[
'gno_uid'], trancaction['gno_id'], trancaction[
'binlog'], value_list[3]
db_tbl_name_serverid = '{}:{}'.format(
db_tbl_name, self.server_id)
if db_tbl_name_serverid in fetch_value:
_state = self.__fetch_check(
fetch_value[db_tbl_name_serverid], gno_uid,
gno_id, at_pos, binlog)
if _state:
del fetch_value[db_tbl_name_serverid]
else:
continue
if tran_len >= 2:
if __mark_status is None:
self.__set_mark(db_name=db_name,
tbl_name=tbl_name,
gtid=gno_uid,
gno_id=gno_id,
at_pos=at_pos,
binlog=binlog)
__mark_status = True
else:
self.__set_mark(db_name=db_name,
tbl_name=tbl_name,
gtid=gno_uid,
gno_id=gno_id,
binlog=binlog,
at_pos=at_pos)
sql_list = self.GetSQL(_values=value_list[0],
event_code=value_list[4],
database_name=db_name,
table_name=tbl_name)
if sql_list:
for sql in sql_list:
self.trancaction_list.append(
[sql[0], sql[1]])
self.__check_stat(
self.__raise_sql(sql=sql[0],
args=sql[1]))
if __mark_status:
self.__set_mark(db_name=db_name,
tbl_name=tbl_name,
gtid=gno_uid,
gno_id=gno_id,
at_pos=at_pos,
binlog=binlog)
self.__raise_sql('commit', retry=True)
self.chunk_list_status_th[_uuid].append(self.thread_id)
self.trancaction_list = []
__mark_status = None
self.thread_lock_queue[db_tbl_name].pop(0)
else:
time.sleep(0.01)
def __fetch_check(self, fetch, gno_uid, gno_id, at_pos, binlog):
'''
重启或接管任务时通过游标库中相应库表保存的同步状态信息，比对获取到binlog的信息
如果游标库中的uid和获取到的binlog中不同，这种情况可能是由于mysql宕机切换导致，
由于游标库先于状态库落盘，所以以游标库为准
:param fetch: [gno_uid,gno_id,at_pos]
:param gno_uid:
:param gno_id:
:param at_pos:
:return:
'''
if gno_uid:
if str(gno_uid) == str(fetch[0]):
if int(fetch[1]) > int(gno_id):
return False
elif int(gno_id) == int(
fetch[1]) and int(fetch[2]) >= int(at_pos):
return False
else:
return True
elif str(gno_uid) != str(fetch[0]):
return False
else:
if fetch[-1] == binlog:
if int(fetch[2] >= int(at_pos)):
return False
else:
return True
else:
binlog_num_cur = int(fetch[-1].split('.')[-1])
binlog_num_sync = int(binlog.split('.')[-1])
if binlog_num_cur < binlog_num_sync:
return False
else:
return True
def __exit__(self, exc_type, exc_val, exc_tb):
self.destination_cur.close()
self.destination_conn.close()
class GetStruct:
def __init__(self,
host=None,
port=None,
user=None,
passwd=None,
socket=None,
binlog=None):
self.host = host
self.port = port
self.user = user
self.passwd = passwd
self.socket = socket
self.binlog = binlog
self.connection = None
self.cur = None
self.__init_status()
self.apply_conn = None
self.insert_sql_list = []
def __init_status(self):
'''
首次初始化链接
:return:
'''
for i in range(60):
try:
self.connection = InitMyDB(mysql_host=self.host,
mysql_port=self.port,
mysql_user=self.user,
mysql_password=self.passwd,
unix_socket=self.socket,
auto_commit=False).Init()
if self.connection:
self.cur = self.connection.cursor()
if self.binlog is None:
self.cur.execute('set sql_log_bin=0;')
break
except pymysql.Error as e:
Logging(msg=e.args, level='error')
time.sleep(1)
else:
Logging(
msg='retry 60 time on status db is failed,exist thread now',
level='error')
sys.exit()
def CreateTmp(self):
self.__raise('CREATE DATABASE IF NOT EXISTS dump2db;') #创建临时库
#self.__raise('DROP TABLE IF EXISTS dump2db.dump_status;')
self.__raise(
'CREATE TABLE IF NOT EXISTS dump2db.dump_status(id INT,excute_gtid json,logname VARCHAR(100),'
'at_pos BIGINT,next_pos BIGINT,gtid_uid varchar(64),PRIMARY KEY(id));'
) #创建临时表
def SaveStatus(self,
logname,
at_pos,
next_pos,
server_id,
gtid=None,
apply_conn=None,
gno_uid=None):
if apply_conn:
return self.__xa_transaction(apply_conn, server_id, logname,
at_pos, next_pos, gtid, gno_uid)
else:
if gtid:
sql = 'INSERT INTO dump2db.dump_status(id,excute_gtid,logname,at_pos,next_pos,gtid_uid) VALUES(%s,%s,%s,%s,%s,%s)' \
' ON DUPLICATE KEY UPDATE excute_gtid=%s,logname=%s,at_pos=%s,next_pos=%s,gtid_uid=%s;'
self.__raise(
sql, (server_id, gtid, logname, at_pos, next_pos, gno_uid,
gtid, logname, at_pos, next_pos, gno_uid))
return self.__raise('commit')
else:
sql = 'INSERT INTO dump2db.dump_status(id,logname,at_pos,next_pos,gtid_uid) VALUES(%s,%s,%s,%s,%s) ' \
'ON DUPLICATE KEY UPDATE logname=%s,at_pos=%s,next_pos=%s,gtid_uid=%s;'
self.__raise(sql,
(server_id, logname, at_pos, next_pos, gno_uid,
logname, at_pos, next_pos, gno_uid))
return self.__raise('commit')
def close(self):
self.cur.close()
self.connection.close()
def __raise(self, sql, args=None):
'''
一直尝试重新执行，直到成功或超过递归上限
如遇语法错误将直接退出整个程序
:param sql:
:return:
'''
try:
if sql == 'commit':
self.connection.commit()
else:
self.cur.execute(sql, args)
except pymysql.Error as e:
Logging(msg=traceback.format_exc(), level='error')
Logging(msg='error code {}'.format(e.args[0]), level='error')
if ErrorCode[e.args[0]]:
self.__retry_conn()
Logging(msg='connection success on status db', level='info')
if sql == 'commit':
self.__raise(self.insert_sql_list[0],
self.insert_sql_list[1])
self.__raise('commit')
else:
self.__raise(sql, args)
return True
else:
Logging(msg='error code test', level='error')
Logging(msg=e, level='error')
sys.exit()
except:
Logging(msg='error code test status db', level='error')
Logging(msg=traceback.format_exc(), level='error')
sys.exit()
return True
def __retry_conn(self):
'''
尝试重连60次，每次间隔1秒
:return:
'''
for i in range(60):
Logging(msg='retry connection for status db again!!',
level='error')
try:
self.connection = InitMyDB(mysql_host=self.host,
mysql_port=self.port,
mysql_user=self.user,
mysql_password=self.passwd,
unix_socket=self.socket,
auto_commit=False).Init()
if self.connection:
self.cur = self.connection.cursor()
if self.binlog:
pass
else:
self.cur.execute('set sql_log_bin=0;')
return True
except pymysql.Error:
Logging(msg=traceback.format_exc(), level='error')
time.sleep(1)
else:
Logging(
msg='retry 60 time on status db is failed,exist thread now',
level='error')
sys.exit()
def __xa_transaction(self,
apply_conn,
server_id,
logname,
at_pos,
next_pos,
gtid=None,
gno_uid=None):
'''
目标库数据提交和状态值在不同库上，完全保证都能提交很难，在次使用数据先提交，确保数据进去之后再
保存状态数据，控制所有表都有唯一约束或合适的主键的情况下，即使状态丢失也只会在丢失插入数据的事
务时产生报错，如果先提交状态值在数据提交时断开，这样在其他同步节点接手任务后会遗漏数据
:param apply_conn:
:param server_id:
:param logname:
:param at_pos:
:param next_pos:
:param gtid:
:return:
'''
self.apply_conn = apply_conn
if gtid:
sql = 'INSERT INTO dump2db.dump_status(id,excute_gtid,logname,at_pos,next_pos,gtid_uid) VALUES(%s,%s,%s,%s,%s,%s)' \
' ON DUPLICATE KEY UPDATE excute_gtid=%s,logname=%s,at_pos=%s,next_pos=%s,gtid_uid=%s;'
self.insert_sql_list = [
sql,
[
server_id, gtid, logname, at_pos, next_pos, gno_uid, gtid,
logname, at_pos, next_pos, gno_uid
]
]
self.__raise(sql,
(server_id, gtid, logname, at_pos, next_pos, gno_uid,
gtid, logname, at_pos, next_pos, gno_uid))
else:
sql = 'INSERT INTO dump2db.dump_status(id,logname,at_pos,next_pos,gtid_uid) VALUES(%s,%s,%s,%s,%s) ' \
'ON DUPLICATE KEY UPDATE logname=%s,at_pos=%s,next_pos=%s,gtid_uid=%s;'
self.insert_sql_list = [
sql,
[
server_id, logname, at_pos, next_pos, gno_uid, logname,
at_pos, next_pos, gno_uid
]
]
self.__raise(sql, (server_id, logname, at_pos, next_pos, gno_uid,
logname, at_pos, next_pos, gno_uid))
try:
apply_conn.commit()
self.__raise('commit')
return True
except:
Logging(msg=traceback.format_exc(), level='error')
return False
def get_daemon_info(self, server_id):
'''
重启获取已经读取的binlog信息
:return:
'''
state = self.__raise(
'select logname,next_pos,excute_gtid,gtid_uid from dump2db.dump_status where id = %s;',
server_id)
if state:
try:
result = self.cur.fetchall()
except pymysql.Error as e:
if ErrorCode[e.args[0]]:
self.get_daemon_info(server_id)
_gtid = []
gtid_uid = result[0]['gtid_uid']
if result[0]['excute_gtid']:
gtid = eval(result[0]['excute_gtid'])
#_gtid = ['{}:{}'.format(uuid,gtid[uuid]) for uuid in gtid]
#return result[0]['logname'],result[0]['next_pos'],','.join(_gtid),gtid_uid,gno_id
return result[0]['logname'], result[0]['next_pos'], gtid, gtid_uid
else:
sys.exit()
