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

ゝ一纸荒年。 2022-10-25 14:25 255阅读 0赞

\#!/usr/bin/env python3

\#-\*- coding:utf8 -\*-

\#author：zhanbin.liu

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

\#python3环境 pip3 install pymysql paramiko

import os

\#from pathlib import Path

import sys

import pymysql

import paramiko

\#每次只能迁移一个DB下的表，到指定DB

\#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'

desDataDir = os.path.join(desDataBase,desDbName)

\# for table in tableList:

\# desFile = Path("%s/%s.ibd" %(desDataDir,table))

\# print(desFile)

\# if desFile.is\_file():

\# print("ok")

\# 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')

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

sourceTables = \[\]

desTables = \[\]

cursor\_source = sourceTableExist.cursor()

cursor\_des = desTableExist.cursor()

for table in tableList:

\#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\])

sourceTableExist.close()

desTableExist.close()

s=d=0

if sourceTables != \[\]:

print('迁移源不存在将要迁移的表：',sourceIp,sourceDbName, sourceTables,' 请检查')

s=1

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()

cursor\_db\_des = db\_des.cursor()

for table in tableList:

print("正在同步表：",table)

cursor\_db\_source.execute("show create table %s;" % (table))

createTableSQL = cursor\_db\_source.fetchall()\[0\]\[1\]

print(createTableSQL)

try:

cursor\_db\_des.execute(createTableSQL)

except Exception as error:

print(error)

cursor\_db\_source.execute("flush table %s with read lock;" % (table))

cursor\_db\_des.execute("alter table %s discard tablespace;" % (table))

client.connect(sourceIp, 22, 'root')

stdin1, stdout1, stderr1 = client.exec\_command("scp %s %s:%s " % (sourceDataDir+"/"+table+".ibd", desIp, desDataDir))

stdin2, stdout2, stderr2 = client.exec\_command("scp %s %s:%s " % (sourceDataDir+"/"+table+".cfg", desIp, desDataDir))

a\_e\_1 = stderr1.readlines()

a\_e\_2 = stderr2.readlines()

if a\_e\_1 != \[\] or a\_e\_2 != \[\]:

print(a\_e\_1,a\_e\_2)

sys.exit()

client.close()

client.connect(desIp, 22, 'root')

stdin3, stdout3, stderr3 = client.exec\_command("chown -R mysql.mysql %s\*" % (desDataDir+"/"+table))

a\_e\_3 = stderr3.readlines()

if a\_e\_3 != \[\]:

print(a\_e\_1, a\_e\_2)

sys.exit()

client.close()

\#cursor\_db\_source.execute("select sleep(10);")

cursor\_db\_source.execute("unlock tables;")

cursor\_db\_des.execute("alter table %s import tablespace;" % (table))

print("同步完成")

cursor\_db\_source.close()

cursor\_db\_des.close()

def data\_checksum():

print('data\_checksum')

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()

cursor\_db\_des = db\_des.cursor()

for table in tableList:

print("正在校验表：", table)

cursor\_db\_source.execute("checksum table %s;" % (table))

ck\_s = cursor\_db\_source.fetchall()\[0\]\[1\]

cursor\_db\_des.execute("checksum table %s;" % (table))

ck\_d = cursor\_db\_des.fetchall()\[0\]\[1\]

if ck\_s != ck\_d:

print("表不一致：",table)

else:

print("表一致：",table)

cursor\_db\_source.close()

cursor\_db\_des.close()

if \_\_name\_\_ == "\_\_main\_\_":

table\_judge()

data\_sync()

data\_checksum()

print('haha')

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

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 MySQL数据迁移

相关 Linux MySQL数据迁移

相关 mysql大量数据迁移

相关 mysql 数据目录迁移

相关 mysql数据迁移不生效_MySQL数据迁移问题

相关 php实现mysql 数据迁移_数据迁移

相关 mysql数据迁移_Mysql数据迁移方法

相关 python做mysql数据迁移_python制作mysql数据迁移脚本

相关迁移mysql数据目录

相关 mysql迁移数据目录