# !/usr/bin/env python3
# -*- coding:utf-8 -*-
import pymysql
import time
from datetime import datetime
import os
import shutil
dbUser='db_order'
dbPasswd='db_order'
dbHost='118.89.139.104'
remoteDBUser='db_order'
remoteDBPasswd='db_order'
remoteDBHost='192.168.16.125'
dbCharset = 'utf8'
backupDir = '/home/developer/mysqlbackup'
backupDate = time.strftime("%Y%m%d")
backupDir = '/home/developer/mysqlbackup/' + backupDate
conn = None
cur = None
try:
if not os.path.exists(backupDir):
os.makedirs(backupDir)
print("backupDir exists:%s"%os.path.exists(backupDir))
conn = pymysql.connect(host=dbHost,port= 3306,user = dbUser,passwd=dbPasswd,db='db_order')
cur = conn.cursor()
cur.execute("show databases like 'db_%'")
databases = cur.fetchall()
print("databases:%s" %databases)
print('The database backup to start! %s' %time.strftime('%Y-%m-%d %H:%M:%S'))
for db in databases:
dbName = db[0]
print('databaseName:%s'%dbName)
fileName = '%s/%s_%s.sql' %(backupDir,dbName,backupDate)
print('fileName:%s'%fileName)
if os.path.exists(fileName):
os.remove(fileName)
os.system("mysqldump -h%s -u%s -p%s %s --default_character-set=%s > %s/%s_%s.sql" %(dbHost,dbUser,dbPasswd,dbName,dbCharset,backupDir,dbName,backupDate))
print('The database backup success! %s' %time.strftime('%Y-%m-%d %H:%M:%S'))
cur.close()
conn.close()
writeFileName = '%s/%s_online_%s.sql' %(backupDir,dbName,backupDate)
print("writeFileName:%s" %writeFileName)
if os.path.exists(writeFileName):
os.remove(writeFileName)
with open(fileName, 'r', encoding='utf-8') as f:
with open(writeFileName, 'a', encoding='utf-8') as f2:
for line in f:
line = line.replace("DROP TABLE IF EXISTS `ord_order_detail`", "DROP TABLE IF EXISTS `ord_order_detail_online`").replace("DROP TABLE IF EXISTS `ord_order_info`", "DROP TABLE IF EXISTS `ord_order_info_online`").replace("DROP TABLE IF EXISTS `pay_flow`","DROP TABLE IF EXISTS `pay_flow_online`").replace("CREATE TABLE `ord_order_detail`","CREATE TABLE `ord_order_detail_online`").replace("CREATE TABLE `ord_order_info`", "CREATE TABLE `ord_order_info_online`").replace("CREATE TABLE `pay_flow`", "CREATE TABLE `pay_flow_online`").replace("LOCK TABLES `ord_order_info`", "LOCK TABLES `ord_order_info_online`").replace("ALTER TABLE `ord_order_info`", "ALTER TABLE `ord_order_info_online`").replace("INSERT INTO `ord_order_info`", "INSERT INTO `ord_order_info_online`").replace("LOCK TABLES `ord_order_detail`", "LOCK TABLES `ord_order_detail_online`").replace("ALTER TABLE `ord_order_detail`", "ALTER TABLE `ord_order_detail_online`").replace("INSERT INTO `ord_order_detail`", "INSERT INTO `ord_order_detail_online`").replace("LOCK TABLES `pay_flow`", "LOCK TABLES `pay_flow_online`").replace("ALTER TABLE `pay_flow`", "ALTER TABLE `pay_flow_online`").replace("INSERT INTO `pay_flow`", "INSERT INTO `pay_flow_online`")
f2.writelines(line)
f2.flush()
f2.close()
f.close()
print("rewrite file success!")
baseFileName = backupDir + "/base_" + backupDate + ".sql"
print("baseFileName:%s" % baseFileName)
if os.path.exists(baseFileName):
os.remove(baseFileName)
os.system("mysqldump -h%s -u%s -p%s --opt -d %s --tables ord_order_info ord_order_detail pay_flow ord_member_activity ord_table_shards --default_character-set=%s > %s" %("192.168.16.124",dbUser,dbPasswd,"db_order_sharding",dbCharset,baseFileName))
print("dump shard table base success!")
remoteConn = pymysql.connect(host=remoteDBHost,port= 3306,user = remoteDBUser,passwd=remoteDBPasswd,db='db_wh_sharding')
remoteCur = remoteConn.cursor()
remoteCur.execute("create database if not exists db_order_sharding")
os.system("mysql -h%s -u%s -p%s %s < %s" % (remoteDBHost, remoteDBUser, remoteDBPasswd, "db_order_sharding", writeFileName))
print("import online data success!")
os.system("mysql -h%s -u%s -p%s db_order_sharding < %s" % (remoteDBHost, remoteDBUser, remoteDBPasswd, baseFileName))
print("import shard table base success!")
remoteCur.execute("use db_order_sharding")
remoteCur.execute("select distinct store_id from ord_order_info_online")
storeIds = remoteCur.fetchall()
print(storeIds)
for storeIdTuple in storeIds:
storeId = storeIdTuple[0]
print("storeId:%s, type:%s" %(storeId, type(storeId)))
remoteCur.execute("create table if not exists ord_order_info_" + str(storeId) + " like ord_order_info")
remoteCur.execute("select count(1) from ord_table_shards where shard_name = %s" ,"ord_order_info_%s" % str(storeId))
ordInfoCnt = remoteCur.fetchone()
print(ordInfoCnt)
if ordInfoCnt[0] == 0:
remoteCur.execute("insert into ord_table_shards(shard_name, add_time) values(%s, %s)", ("ord_order_info_%s" % str(storeId), datetime.now()))
remoteCur.execute("create table if not exists ord_order_detail_" + str(storeId) + " like ord_order_detail")
remoteCur.execute("select count(1) from ord_table_shards where shard_name = %s" ,"ord_order_detail_%s" % str(storeId))
ordDetailCnt = remoteCur.fetchone()
print(ordDetailCnt)
if ordDetailCnt[0] == 0:
remoteCur.execute("insert into ord_table_shards(shard_name, add_time) values(%s, %s)", ("ord_order_detail_%s" % str(storeId), datetime.now()))
remoteCur.execute("create table if not exists pay_flow_" + str(storeId) + " like pay_flow")
remoteCur.execute("select count(1) from ord_table_shards where shard_name = %s" ,"pay_flow_%s" % str(storeId))
payFlowCnt = remoteCur.fetchone()
print(payFlowCnt)
if payFlowCnt[0] == 0:
remoteCur.execute("insert into ord_table_shards(shard_name, add_time) values(%s, %s)", ("pay_flow_%s" % str(storeId), datetime.now()))
remoteCur.execute("create table if not exists ord_member_activity_" + str(storeId) + " like ord_member_activity")
remoteCur.execute("select count(1) from ord_table_shards where shard_name = %s" ,"ord_member_activity_%s" % str(storeId))
ordMemberCnt = remoteCur.fetchone()
print(ordMemberCnt)
if ordMemberCnt[0] == 0:
remoteCur.execute("insert into ord_table_shards(shard_name, add_time) values(%s, %s)", ("ord_member_activity_%s" % str(storeId), datetime.now()))
remoteCur.execute("INSERT into ord_order_info_%s select * from ord_order_info_online where store_id = %s", (storeId, storeId))
remoteCur.execute("insert into ord_order_detail_%s(id,order_id,product_sku,product_name,product_format,product_unit,retail_price,product_barcode,number,rate,store_Id) select d.id,d.order_id,d.product_sku,d.product_name,d.product_format,d.product_unit,d.retail_price,d.product_barcode,d.number,d.rate,i.store_Id from ord_order_info_online i, ord_order_detail_online d where i.id = d.order_id and i.store_id = %s", (storeId, storeId))
remoteCur.execute("insert into pay_flow_%s(id,pay_flow,pay_type,business_code,order_id,pay_status,pay_amount,create_by,create_time,store_Id) select p.id,p.pay_flow,p.pay_type,p.business_code,p.order_id,p.pay_status,p.pay_amount,p.create_by,p.create_time,i.store_id from ord_order_info_online i, pay_flow_online p where i.id = p.order_id and i.store_id = %s", (storeId, storeId))
remoteCur.execute("drop table ord_order_info_online")
remoteCur.execute("drop table ord_order_detail_online")
remoteCur.execute("drop table pay_flow_online")
remoteCur.close()
remoteConn.close()
except Exception as e:
print(e)
网友评论