python 备份与导入mysql脚本

本文实现了用python 备份与导入mysql脚本，其中get_cmd_result方法可以返回命令的执行结果，默认1800秒不结束就杀死进程（已在linux下测试成功），get_scmd_result方法直接调用system命令比较简单，但不能返回执行命令的输出结果，实践中建议使用get_cmd_result方法。注意在windwos下执行，mysql的命令需要带绝对路径，linux下不带路径。

#Config.py

HOST= "192.168.101.186"
PORT="3306"
USERNAME = "root"
PASSWORD = "root"
#DATABASE = "stockcn"
DATABASE = "stockcn"
DBCHARSET="utf8"

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

#!/usr/bin/env python
from  Config  import *
import logging  
import time 
import  datetime
import  os
import  signal
from subprocess import Popen, PIPE


yesterday=datetime.date.fromordinal(datetime.date.today().toordinal()-1).strftime("%Y-%m-%d")
report_date=datetime.date.today().strftime("%Y-%m-%d")
basedir=os.path.dirname(os.path.realpath(__file__))
logfile="%s\\log\\st_%s_stock.log" %(basedir,datetime.datetime.now().strftime("%Y-%m-%d"))  
logging.basicConfig(filename=logfile, level=logging.DEBUG,format='%(asctime)s  %(message)s')     


def runbackup(backupcmd):
    try:
        get_cmd_result(backupcmd)
        print("Backup done with command ", backupcmd)
    except:
        print("Backup failed with command", backupcmd) 
def runimportup(importcmd):
    try:
        get_cmd_result(importcmd)
        print("Backup done with command ", importcmd)
    except:
        print("Backup failed with command", importcmd)        
 
def get_cmd_result(command, timeout=1800):    
    try:
        start = datetime.datetime.now()
        process = Popen(command.split(" "), shell =True,  stdout=PIPE, stderr=PIPE)
        while process.poll() is None:
            time.sleep(0.2)
            now = datetime.datetime.now()
            if (now - start).seconds> timeout:
                os.kill(process.pid, signal.SIGKILL)
                os.waitpid(-1, os.WNOHANG)
                return "Timeout to get command result"
        return process.communicate()[0]
    except:
        return("run command  failed") 


def get_scmd_result(command, timeout=1800):
     
    return os.system(command)
 
sql_file =os.path.join( basedir,"data" , 'st_2018-06-06_stock.sql')


dump_cmd_mod="D:\xampps\mysql\bin\mysqldump -h%s -P%s -u%s -p%s %s --default_character-set=%s >%s"
dump_cmd=dum_cmd_mod%(HOST,PORT,USERNAME,PASSWORD,DATABASE,DBCHARSET,sql_file)
infor="Dump begins with cmd :  %s  ! " % dump_cmd
logging.info(infor) 
ret=runimportup(dumpcmd) 
infor="Import   sql data ended with return %s! " %ret
logging.info(infor)


import_cmd_mod="D:\\xampps\\mysql\\bin\mysql -h%s -P%s -u%s -p%s %s <%s"
import_cmd=import_cmd_mod%(HOST,PORT,USERNAME,PASSWORD,DATABASE,sql_file)
infor="Import begins with cmd :  %s  ! " % import_cmd
logging.info(infor)
ret=runimportup(import_cmd) 
infor="Import   sql data ended with return %s! " %ret
logging.info(infor)