python制作mysql数据迁移脚本

python制作制作mysql数据迁移脚本数据迁移脚本

主要为大家详细介绍的是使用python写的mysql数据迁移的脚本,具有一定的参考价值,感兴趣的小伙伴们可以参考一下

用python写了个数据迁移脚本，主要是利用从库将大的静态表导出表空间，载导入到目标实例中。

#!/usr/bin/env python3

#-*- coding:utf8 -*-

#author：zhanbin.liu

#!!!!!DB必须同版本

#python3环境 pip3 install pymysql paramiko

#GRANT SELECT, CREATE, RELOAD, ALTER, LOCK TABLES ON *.* TO 'data_migration'@'192.168.%' IDENTIFIED BY 'data_migration@123';

tables='sqlauto_cluster,sqlauto_user' #以，分割的字符串，如a,b,c

tableList = tables.split(',')

sourceIp = '192.168.1.101'

sourceDataBase = '/data/mysql/3306/data'

sourceDbName = 'inception_web'

sourceDataDir = os.path.join(sourceDataBase,sourceDbName)

desIp = '192.168.1.102'

desDataBase = '/data/mysql/3306/data'

desDbName = 'inception_web'

# else:

# print("no")

comUser = 'data_migration'

comPwd = 'data_migration@123'

comPort = 3306

client = paramiko.SSHClient()

client.set_missing_host_key_policy(paramiko.AutoAddPolicy())

def table_judge():

print("table_judge")

sourceTableExist = pymysql.connect(sourceIp,comUser,comPwd,sourceDbName,comPort,charset='utf8')

#print(table)

cursor_source.execute("select TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA='%s' and TABLE_NAME='%s';" % (sourceDbName,table))

sourceTable_tmp = cursor_source.fetchall()

cursor_des.execute("select TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA='%s' and TABLE_NAME='%s';" % (desDbName,table))

desTable_tmp = cursor_des.fetchall()

#print(desTable_tmp)

if sourceTable_tmp is ():

sourceTables.append(table)

if desTable_tmp is not ():

desTables.append(desTable_tmp[0][0])

if desTables != []:

print('目标库存在将要迁移的表：',desIp,desDbName,desTables,' 请移除')

d=1

if s == 1 or d == 1:

sys.exit()

def data_sync():

print('data_sync')

db_source = pymysql.connect(sourceIp,comUser,comPwd,sourceDbName,comPort,charset='utf8')

db_des = pymysql.connect(desIp,comUser,comPwd,desDbName,comPort,charset='utf8')

cursor_db_source = db_source.cursor()