python做mysql数据迁移_python+mysqldump交互式数据迁移

mysql_data_migrate.py

#!/usr/bin/env python3

# -*- coding: utf-8 -*-

__author__ = "Jason Li"

import os

import time

"""

数据迁移:

Tips: 执行脚本前先安装python3

python3 mysql_data_migrate.py

"""

print("*" * 50,"请输入源数据库信息", "*" * 50)

src_db_host = input("Enter source db host:")

src_db_user = input("Enter source db user:")

src_db_pwd = input("Enter source db passwd:")

src_db_name = input("Enter source db name:")

print("*" * 50,"请输入目标数据库信息", "*" * 50)

dest_db_host = input("Enter dest db host:")

dest_db_user = input("Enter dest db user:")

dest_db_pwd = input("Enter dest db passwd:")

dest_db_name = input("Enter dest db name:")

filestamp = time.strftime('%Y-%m-%d-%I:%M')

filename = src_db_name + '-' + filestamp + '.sql'

def export_data():

"""

数据导出

:return:

"""

print(">>> Exporting...")

os.system("mysqldump --single-transaction -u%s -p'%s' -h%s %s > %s"% (src_db_user, src_db_pwd, src_db_host, src_db_name, filename))

def import_data():

"""

数据导入

:return:

"""

print(">>> Creating dest database...")

os.system("mysql -u%s -p'%s' -h%s -e 'create database %s'" % (dest_db_user, dest_db_pwd, dest_db_host, dest_db_name))

print(">>> Importing...")

os.system("mysql -u%s -p'%s' -h%s %s < %s"% (dest_db_user, dest_db_pwd, dest_db_host, dest_db_name, filename))

os.remove(filename)

def check_result():

"""

检查是否导入成功

:return:

"""

print(">>> Show tables from dest db...")

check_result = os.system("mysql -u%s -p'%s' -h%s %s -e 'show tables from %s'" % (dest_db_user, dest_db_pwd, dest_db_host, dest_db_name, dest_db_name))

print(check_result)

if __name__ == '__main__':

export_data()

import_data()

check_result()

mysql_structure_migrate.py

#!/usr/bin/env python3

# -*- coding: utf-8 -*-

import os

import time

"""

数据库表结构迁移:

python3 mysql_structure_migrate.py

"""

print("*" * 50,"请输入源数据库信息", "*" * 50)

src_db_host = input("Enter source db host:")

src_db_user = "adminroot"

src_db_pwd = input("Enter source db passwd:")

src_db_name = input("Enter source db name:")

print("*" * 50,"请输入目标数据库信息", "*" * 50)

dest_db_host = input("Enter dest db host:")

dest_db_user = "adminroot"

dest_db_pwd = input("Enter dest db passwd:")

dest_db_name = input("Enter dest db name:")

filestamp = time.strftime('%Y-%m-%d-%I:%M')

filename = src_db_name + '-' + filestamp + '.sql'

def export_structure():

"""

数据导出

:return:

"""

print(">>> Exporting...")

os.system("mysqldump --single-transaction -u%s -p'%s' -h%s -d %s > %s" % (src_db_user, src_db_pwd, src_db_host, src_db_name, filename))

def import_structure():

"""

数据导入

:return:

"""

print(">>> Creating dest database...")

os.system("mysql -u%s -p'%s' -h%s -e 'create database %s'" % (dest_db_user, dest_db_pwd, dest_db_host, dest_db_name))

print(">>> Importing...")

os.system("mysql -u%s -p'%s' -h%s %s < %s" % (dest_db_user, dest_db_pwd, dest_db_host, dest_db_name, filename))

os.remove(filename)

def check_result():

"""

检查是否导入成功

:return:

"""

print(">>> Show tables from dest db...")

check_result = os.system("mysql -u%s -p'%s' -h%s %s -e 'show tables from %s'" % (dest_db_user, dest_db_pwd, dest_db_host, dest_db_name, dest_db_name))

print(check_result)

if __name__ == '__main__':

export_structure()

import_structure()

check_result()